• Oracle存储过程

    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);
exception when others then   --处理异常
    dbms_output.put_line('error');
end;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23

    执行

    begin
 demo;
end;

    • 1
    • 2
    • 3

    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27

    循环数值

    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    在这里插入图片描述

    插入数据

    
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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44

    删除数据

    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||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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122

    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25

    用于自动执行的存储过程

    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;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20

    查询定时任务执行情况

    select * from dba_jobs 

    • 1
  • 相关阅读:
    有哪些公共管理或行政管理学习帮助较大的外文期刊？
    C++——string类
    SCS【13】单细胞转录组之识别细胞对“基因集”的响应 (AUCell)
    设计模式-建造者模式
    Simulink电机控制代码生成-----关于PI控制器参数整定的一点总结
    借助VScode将 Docker 容器用作开发环境
    C++入门必备基础知识（上篇）
    Elasticsearch 在bool查询中使用分词器
    【Linux】一个小故事让你秒懂shell外壳程序
    CSDN21天学习挑战赛——计划导航
  • 原文地址：https://blog.csdn.net/qq_55342245/article/details/127806244