Oracle存儲過程

簡單入門
- - 建立
  - 執行
- for循環
- 循環數值
插入資料
删除資料
日志
用于自動執行的存儲過程

簡單入門

簡單建了個表。

Oracle存儲過程簡單入門插入資料删除資料日志用于自動執行的存儲過程

建立

-- 建立不帶參數的存儲過程
create or replace procedure demo as 

v_num1			number;  		--定義變量，要在begin以外
v_num2			number; 
sql_1			varchar2(1000);  
table_name		varchar2(50); 

begin -- 指派要在begin内
	-- 給變量指派1
	table_name := 'TEMP';
	sql_1 := 'SELECT NAME, SCORE FROM TEMP';
	dbms_output.put_line(table_name);
	dbms_output.put_line(sql_1);
	-- 給變量指派2
	select count(*) into v_num1 from temp where id=1;
	select count(*) into v_num2 from temp;
	dbms_output.put_line(v_num1);
	dbms_output.put_line(v_num2);
end;

執行

begin
 demo;
end;

for循環

create or replace procedure demo as 
-- 這樣的變量得到的是一個資料表，可直接建立不用定義。但位置也是在begin以外
cursor yihang  is (select * from temp where id=1);
cursor duohang is (select * from temp);

begin 

	for i in yihang loop
		dbms_output.put_line(i.id);
		dbms_output.put_line(i.name);
		dbms_output.put_line(i.class);
		dbms_output.put_line(i.score);
		dbms_output.put_line('-----------');
	end loop;
	
	-- 若不用變量，可以直接放查詢語句
	for i in (select * from temp) loop
		dbms_output.put_line(i.id);
		dbms_output.put_line(i.name);
		dbms_output.put_line(i.class);
		dbms_output.put_line(i.score);
		dbms_output.put_line('===============');
	end loop;
	
end;

Oracle存儲過程簡單入門插入資料删除資料日志用于自動執行的存儲過程

循環數值

for i in 01..09 loop
	dbms_output.put_line('20221012'||i);
end loop;

for i in 10..12 loop
	dbms_output.put_line('20221012'||i);
end loop;

Oracle存儲過程簡單入門插入資料删除資料日志用于自動執行的存儲過程

插入資料

create or replace procedure temp_table_insert(v_date in varchar) AS
v_table_name    varchar2(100);
v_dest_table    varchar2(100);
v_sql_1         varchar2(10000);
v_num1          number;
v_num2          number;
v_exec_name     varchar2(100);
v_oper_type     varchar2(100);
v_database      varchar2(20);

begin	
	v_table_name  :='STUDENTS';
	v_dest_table  :='TEMP';
	v_exec_name   :='TEMP_TABLE_INSERT';
	v_oper_type   :='TEMP_TABLE';
	v_database    :='VOLTE';
	
	select count(1) into v_num1 from all_tables where TABLE_NAME = ''||v_dest_table||'' and OWNER=''||v_database||'';
	select count(1) into v_num2 from all_tables where TABLE_NAME = ''||v_table_name||'' and OWNER=''||v_database||'';
	
	if v_num1 = 1 then
		if v_num2 = 1 then
			-- 當兩個表都存在時，執行SQL
			cfg_logs_insert(v_exec_name,v_oper_type,v_date,'0',v_table_name,v_dest_table,'START');
		
			v_sql_1 :='insert into '||v_dest_table||'
				select a.id,a.name,a.class,
				sum(a.score) score 
				from '||v_table_name||' a
				where a.sdate=to_date('''||v_date||''',''yyyymmddhh24'')
				group by a.id,a.name,a.class 
			';
			execute immediate v_sql_1;
			commit;
			
			cfg_logs_insert(v_exec_name,v_oper_type,v_date,'1',v_table_name,v_dest_table,'END');
		else
			cfg_logs_insert(v_exec_name,v_oper_type,v_date,'0',v_table_name,v_dest_table,v_table_name||' DOES NOT EXITS');
		end if;
	else
		cfg_logs_insert(v_exec_name,v_oper_type,v_date,'0',v_table_name,v_dest_table,v_dest_table||' DOES NOT EXITS');
	end if;
end;

删除資料

create or replace procedure del_volte_data as

v_exec_name   varchar2(50);
v_oper_type   varchar2(50);
v_dest_table  varchar2(50);
v_owner       varchar2(50);
v_table       varchar2(50);
v_table_name  varchar2(50);
v_table2      varchar2(50);
v_log_table   varchar2(50);
v_parat       varchar2(50);
v_dates       varchar2(50);
v_date1       varchar2(50);
v_database    varchar2(20);
v_sql2        varchar2(500);
v_sql1        varchar2(500);
v_num1        number;
v_num2        number;

--擷取接口小時表名字首
cursor v_interface_table is (
	select table_name from cfg_delete_tables where table_type='INTERFACE_TABLE'
	);

--擷取分區表表名及分區名
cursor v_converge_table is (
	select a.OWNER,a.OBJECT_NAME,a.SUBOBJECT_NAME,to_char(a.CREATED,'yyyymmddhh24') CREATED
	from sys.dba_objects a,cfg_delete_tables b
	where a.object_name=b.table_name
	and b.table_type='CONVERGE_TABLE'
	and a.object_type ='TABLE PARTITION'
	and a.GENERATED ='Y' --建立表時候建立的第一個分區（GENERATED ='N'）是不允許被删除
	and a.created<=TRUNC(SYSDATE-b.keep_days,'hh24')
	);

--擷取普通表名
cursor v_pt_table is (
	select table_name,keep_days from cfg_delete_tables where table_type='ORDINARY_TABLE'
	);

begin

	-- 删除接口小時表
	v_exec_name  :='DEL_VOLTE_DATA';
	v_oper_type  :='DELETE_INTERFACE_DATA';
	v_dest_table :='OTHER';
	v_database   :='VOLTE';

	for i in v_interface_table loop
		v_date1 :=to_char(sysdate-2,'yyyymmddhh24');
		--擷取要删除的表名
		v_table_name :=i.table_name||v_date1;
		--查詢表是否存在
		select count(*) into v_num1 from all_tables where TABLE_NAME = ''||v_table_name||'' and OWNER=''||v_database||'';
		if v_num1 = 1 then
			v_sql1 :='truncate table '||v_table_name;
			execute immediate v_sql1;
			commit;
			v_sql2 :='drop table '||v_table_name||' CASCADE CONSTRAINTS PURGE';
			execute immediate v_sql2;
			commit;
			--插入LOG
			cfg_logs_insert(v_exec_name,v_oper_type,v_date1,'0',v_table_name,v_dest_table,'DELETED');
		else
			cfg_logs_insert(v_exec_name,v_oper_type,v_date1,'0',v_table_name,v_dest_table, ''||v_table_name||' DOES NOT EXITS');
		end if;
	end loop;


	-- 删除過期分區表
	v_oper_type  :='DELETE_CONVERGE_DATA';

	for s in v_converge_table loop
		v_owner :=s.OWNER;
		v_table :=s.OBJECT_NAME;
		v_parat :=s.SUBOBJECT_NAME;
		v_dates :=s.CREATED;
		
		select count(*) into v_num2 from all_tables where TABLE_NAME = ''||v_table||'' and OWNER=''||v_owner||'';
		if v_num2 = 1 then
			v_sql3:='alter table '|| v_owner ||'.'||v_table||' DROP PARTITION '||v_parat;
			execute immediate v_sql3;
			cfg_logs_insert(v_exec_name,v_oper_type,v_dates,'0',v_table,v_dest_table,'DELETED');
		else
			cfg_logs_insert(v_exec_name,v_oper_type,v_dates,'0',v_table,v_dest_table,''||v_table||' DOES NOT EXITS');
		end if;
	end loop;


	-- 删除普通表過期資料
	v_oper_type  :='DELETE_CONVERGE_DATA';

	for j in v_pt_table loop
		v_table2 :=j.table_name;
		v_time2  :=j.keep_days;
		v_date2  :=to_char(sysdate-v_time2,'yyyymmddhh24');

		select count(*) into v_num3 from all_tables where TABLE_NAME = ''||v_table2||'' and OWNER=''||v_database||'';
		if v_num3 = 1 then
			v_sql4 :='delete from '||v_table2||' where sdate<to_char(sysdate-'||v_time2||',''yyyymmddhh24'')';
			execute immediate v_sql4;
			cfg_logs_insert(v_exec_name,v_oper_type,v_date2,'0',v_table2,v_dest_table,'DELETED');
		else
			cfg_logs_insert(v_exec_name,v_oper_type,v_date2,'0',v_table2,v_dest_table, ''||v_table2||' DOES NOT EXITS');
		end if;
	end loop
	

	-- 删除LOG(30天)
	v_oper_type :='DELETE_LOGS_DATA';
	v_log_table :='CFG_LOGS';
	v_date3     :=to_char(sysdate-30,'yyyymmddhh24');

	select count(*) into v_num4 from all_tables where TABLE_NAME = ''||v_log_table||'' and OWNER=''||v_database||'';
	if v_num4 = 1 then
		v_sql5 :='delete from '||v_log_table||' where sys_date<=to_char(sysdate-30,''yyyy/mm/dd hh24:mi:ss'')';
		execute immediate v_sql5;
		cfg_logs_insert(v_exec_name,v_oper_type,v_date3,'0',v_log_table,v_dest_table,'LOG DELETED');
	else
		cfg_logs_insert(v_exec_name,v_oper_type,v_date3,'0',v_log_table,v_dest_table, ''||v_log_table||' DOES NOT EXITS');
	end if;
end;

STU_ODS_2022101200

STU_ODS_2022101201

STU_ODS_2022101202

STU_ODS_2022101203

STU_ODS_2022101204

STU_ODS_2022101205

STU_ODS_2022101206

STU_ODS_2022101207

對于這類每小時都在更新的小時接口表，上面的存儲過程是每小時删一次，一次删一個。也可以選擇每天删一次，而删的時候就是一次删24個表。那語句大概像下面這樣。(這還是沒添加判斷和寫入日志，如果加上語句會有點多。)

select keep_days into v_num1 from cfg_delete_tables where table_name='STU_ODS_';
for i in interface_table_name loop
	v_date := to_char(sysdate-v_num1,'yyyymmdd');
	for j in 00..09 loop
		v_table_name := i.table_name||v_date||'0'||j;
		v_sql1 :='drop table '||v_table_name||' CASCADE CONSTRAINTS PURGE';
		execute immediate v_sql1;
	end loop;
	for j in 10..23 loop
		v_table_name := i.table_name||v_date||j;
		v_sql1 :='drop table '||v_table_name||' CASCADE CONSTRAINTS PURGE';
		execute immediate v_sql1;
	end loop;
end loop;

CFG_DELETE_TABLES 表資訊如下：

table_type，表類型（分區表，接口表或者普通表）

table_name，表名字

keep_days，資料保留天數

table_info，備注表的作用

日志

create or replace procedure cfg_logs_insert (
v_exec_name in varchar,
v_oper_type in varchar,
v_oper_date in varchar,
v_oper_num in varchar,
v_source_table in varchar,
v_dest_table in varchar,
v_status in varchar) as 

v_sysdate       varchar(50);
v_sql1          varchar(10000);

begin
  v_sysdate :=to_char(sysdate,'yyyy/mm/dd hh24:mi:ss');

  v_sql1 :='insert into cfg_logs values (
	  '''||log_id.nextval||''','''||v_exec_name||''',
	  '''||v_oper_type||''','''||v_oper_date||''',
	  '''||v_oper_num||''','''||v_source_table||''',
	  '''||v_dest_table||''','''||v_status||''','''||v_sysdate||'''
	  )';

  execute immediate v_sql1;
  commit;
end;

用于自動執行的存儲過程

create or replace procedure data_kpi_insert  as

v_date          varchar2(20);
v_days          varchar2(20);
v_hour          varchar2(20);

begin

	v_date  :=to_char(sysdate-2/24,'yyyymmddhh24');
	v_days  :=to_char(sysdate-1,'yyyymmdd');
	v_hour  :=to_char(sysdate-2/24,'hh24');
	
	del_volte_data;
	temp_table_h_insert(v_date);
	
	if v_hour = 00 then
		temp_table_d_insert(v_days);
	end if;
	
end;

查詢定時任務執行情況

select * from dba_jobs

Oracle存儲過程簡單入門插入資料删除資料日志用于自動執行的存儲過程

Oracle存儲過程

簡單入門

建立

執行

for循環

循環數值

插入資料

删除資料

日志

用于自動執行的存儲過程

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List