-- 1.程序设计申明与赋值
declare
-- 申明部分
begin
-- 执行部分
end;
-- 1.1 写一个空的程序块
declare
begin
null;
end;
-- 1.2 输出 hello world
declare
begin
dbms_output.put_line('hello world');
end;
-- 1.3 求周长
declare
v_long number := 4; -- 申明的时候可以赋值
v_wide number := 3;
begin
dbms_output.put_line(v_long) ;
v_long := 5;
v_wide := 3;
dbms_output.put_line('长:'||v_long||',宽:'||v_wide||'则周长为:'|| (v_long + v_wide)*2 );
end;
-- 1.4 定义常量 constant 常量不可变
declare
v_pi constant number:=3.14;
v_r number:=3;
v_area number;
begin
v_area:= v_pi*v_r*v_r;
dbms_output.put_line ('area:'|| v_area);
end;
--1.5 字符、日期数据类型的变量
declare
v_name varchar2(20) := '张三';
v_date date:=sysdate;
begin
v_date := to_date('20210101','yyyymmdd');
dbms_output.put_line('v_name:'||v_name);
dbms_output.put_line('v_date:'||v_date);
end;
--1.6 隐式游标及其特性 只能返回一行数据，返回多行或者没有数据会报错
-- 防止报错 加聚合函数
declare
v_name varchar2(20);
begin
select ename into v_name from emp where empno = 7499;
dbms_output.put_line('v_name： '|| v_name) ;
end;
--1.7 %type / % rowtype 定义数据类型
--%rowtype 引用数据库中某一行（所有字段）作为数据类型
--%type 引用数据库中某列的数据或者某个变量的数据类型
declare
v_name emp.ename%type;
begin
select ename into v_name from emp where empno = 7499;
dbms_output.put_line('v_name： '|| v_name) ;
end;
declare
v_all emp%rowtype;
begin
select ename,sal into v_all.ename,v_all.sal from emp where empno = 7499;
dbms_output.put_line('v_all.ename: '|| v_all.ename || ',v_all.sal: '||v_all.sal) ;
end;
--2 判断
-- 2.1 if判断(尽量少写 else)
declare
v_sal number;
begin
select sal into v_sal from emp where ename = 'SMITH';
if v_sal < 500
then dbms_output.put_line('努力');
elsif v_sal >= 500 and v_sal <2000
then dbms_output.put_line('可以');
elsif v_sal >= 2000
then dbms_output.put_line('很棒');
end if;
end;
-- 2.2 case判断
declare
v_sal number;
begin
select sal into v_sal from emp where ename = 'SMITH';
case when v_sal < 500 then dbms_output.put_line('努力');
when v_sal >= 500 and v_sal <2000 then dbms_output.put_line('可以');
when v_sal >= 2000 then dbms_output.put_line('很棒');
end case;
end;
--3 循环
-- 3.1 for 循环 自然数求和 1.. 100
declare
v_a number :=0;
v_sum number :=0;
begin
for i in 1..100 loop
v_a := v_a + 1;
v_sum := v_sum + v_a;
dbms_output.put_line(v_sum);
end loop;
dbms_output.put_line(v_sum);
end;
--简化一下
declare
v_sum number :=0;
begin
for i in 1..100 loop
v_sum := v_sum + i;
dbms_output.put_line(i);
dbms_output.put_line(v_sum);
end loop;
end;
-- 3.2 while 循环
declare
i number :=1;
v_sum number :=0;
begin
while i <= 100 loop
v_sum := v_sum + i;
dbms_output.put_line(i);
i := i + 1;
dbms_output.put_line(v_sum);
end loop;
end;
-- 3.3 无条件循环 exit
declare
i number :=1;
v_sum number :=0;
begin
loop
v_sum := v_sum + i;
dbms_output.put_line(i);
i := i + 1;
dbms_output.put_line(v_sum);
exit when i >100; -- 退出语句在循环体内
end loop;
end;
-- 4 两数交换
declare
v_a number :=10;
v_b number :=5;
v_c number;
begin
v_c := v_a;
v_a := v_b;
v_b := v_c;
dbms_output.put_line('v_a:'||v_a||',v_b:'||v_b);
end;
--4.1 两表交换
create table emp_1207 as select * from emp;
create table emp_1207_bak as select * from emp;
select * from emp_1207;
select * from emp_1207_bak;
delete from emp_1207_bak where empno= 7934;
-- 4.1.1 交换表明
alter table emp_1207 rename to emp_1207_tmp;
alter table emp_1207_bak rename to emp_1207;
alter table emp_1207_tmp rename to emp_1207_bak;
--6 水仙花数
--方式1：
declare
v_a number;
v_b number;
v_c number;
begin
for i in 100..999 loop
v_a := power(trunc(i/100),3);
v_b := power(trunc(mod(i,100)/10),3);
v_c := power(mod(i,10),3);
if v_a+v_b+v_c=i then dbms_output.put_line(i);
end if;
end loop;
end;
--方式2
with temp as(
select level-1 as rn from dual connect by level <=10)
select a.rn||b.rn||c.rn
from temp a
inner join temp b on 1=1
inner join temp c on 1=1
and power(a.rn,3) + power(b.rn,3) + power(c.rn,3) = a.rn*100+b.rn*10+c.rn
and a.rn*100+b.rn*10+c.rn > 100;
-- 7 乘法口诀
declare
v_str varchar2(1000);
begin
for i in 1..9 loop
v_str:=null;
for j in 1..9 loop
if i>=j then v_str := v_str|| j||'*'|| i||'='||i*j || ' ';
end if;
end loop;
dbms_output.put_line(v_str);
end loop;
end;
--8 冒泡排序 --973482615
declare
v_num number := 9734826105;
v_min number ;
v_result number;
begin
for i in 1..length(v_num) loop
v_min := substr(v_num,1,1);
for j in 1..length(v_num) loop
if v_min > substr(v_num,j+1,1) then v_min := substr(v_num,j+1,1);
end if;
end loop;
v_result := v_result||v_min;
v_num := replace(v_num,v_min,'');
end loop;
dbms_output.put_line(v_result);
end;
-- 一条sql 解决 冒泡
with tmp as (
select substr(9734826105,rownum,1) as rn from dual connect by level <= length(9734826105))
select Listagg(rn, '') Within Group (Order by rn) from tmp ;
--9 游标 Cursor
-- 隐式游标
-- 显示游标 处理多行数据
declare
cursor c_emp is
select ename,sal from emp;
v_row c_emp%rowtype;
begin
open c_emp;
loop
fetch c_emp into v_row;
exit when c_emp%notfound;
dbms_output.put_line(rpad(v_row.ename,10,' ')||rpad(v_row.sal,10,' '));
end loop;
close c_emp;
end;
-- 简写
declare
cursor c_emp is
select ename,sal from emp;
begin
for x in c_emp loop
dbms_output.put_line(rpad(x.ename,10,' ')||rpad(x.sal,10,' '));
end loop;
end;
-- 把取出来的数据放到新表里面
drop table emp_cursor;
create table emp_cursor as select * from emp where 1=2;
declare
cursor c_emp is
select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp;
begin
for x in c_emp loop
insert into emp_cursor values(x.empno,x.ename,x.job,x.mgr,x.hiredate,x.sal,x.comm,x.deptno);
end loop;
commit;
end;
-- 如果薪水小于1200 奖金则加100
declare
cursor c_emp is
select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp_cursor;
begin
for x in c_emp loop
if x.sal <1200 then
update emp_cursor set comm = nvl(x.comm,0) + 1000 where empno=x.empno;
elsif x.sal>=1200 and x.sal <= 2800 then
update emp_cursor set comm = nvl(x.comm,0) + 2000 where empno=x.empno;
elsif x.sal>=2800 then
update emp_cursor set comm = nvl(x.comm,0) + 3000 where empno=x.empno;
end if;
end loop;
commit;
end;
-- 10 动态sql execute immdiate
--ddl,truncate 不能直接用，要用动态sql包装起来
declare
begin
execute immediate 'truncate table emp_cursor';
end;
--11.存储过程
create table emp_bak as select * from emp where 1=2;
select * from emp_bak;
create or replace procedure proc_test as
begin
insert into emp_bak select * from emp where deptno = 10;
commit;
end;
--11.1全量抽数
--把一个表的数据全部放到另外一个表；并支持重跑
create or replace procedure proc_test as
begin
execute immediate'truncate table emp_bak';
insert into emp_bak select * from emp ;
commit;
end;
--11.2 传参数使其增量抽数
--公司里一个表的数据量非常大，不适合做全量
--建表造数
create table ods_emp as select a.* ,trunc(sysdate) as create_time from emp a;
update ods_emp set create_time = create_time -1 where deptno = 20;
update ods_emp set create_time = create_time -2 where deptno = 10;
select * from ods_emp;
--造一个目标表
create table ods_emp_bak as select * from ods_emp where 1=2;
select * from ods_emp_bak;
--写过程
create or replace procedure proc_ods_emp_bak(p_begin_time varchar2,p_end_time varchar2) is
v_begin_time varchar2(20):=p_begin_time;
v_end_time varchar2(20):=p_end_time;
begin
delete from ods_emp_bak
where create_time>to_date(v_begin_time,'yyyy-mm-dd')
and create_time<=to_date(v_end_time,'yyyy-mm-dd') ;
insert into ods_emp_bak
select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, CREATE_TIME
from ods_emp e
where e.create_time>to_date(v_begin_time,'yyyy-mm-dd')
and e.create_time<=to_date(v_end_time,'yyyy-mm-dd') ;
commit;
end;
--11.2.2 性能优化，delete 改造成truncate ，分区表改造
--建表造数
select * from ods_emp;
drop table ods_partition_emp_bak
select * from ods_partition_emp_bak;
create table ods_partition_emp_bak(
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2),
create_time VARCHAR2(30)
)
partition by list(create_time)
(
partition p_10 values (20220301)
,partition p_20 values (20220302)
,partition p_30 values (20220303)
);
-----
create or replace procedure proc_ods_partition_emp_bak(p_begin_time varchar2,p_end_time varchar2) is
v_begin_time varchar2(20):=p_begin_time;
v_end_time varchar2(20):=p_end_time;
v_str varchar2(500);
begin
v_str := 'alter table ods_partition_emp_bak truncate partition for ('||v_begin_time||')';
execute immediate v_str;
insert into ods_partition_emp_bak
select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO,to_char(e.create_time,'yyyymmdd')
from ods_emp e
where e.create_time=to_date(v_begin_time,'yyyymmdd') ;
commit;
end;
--12 merge into 匹配则更新，不匹配则插入
merge into 目标表
using （增量）
on（匹配字段）
when matched then update set
when not matched then insert values;
--建表造数
drop table ods_merge_emp ;
create table ods_merge_emp as select * from emp ;
create table ods_merge_emp_target as select * from emp where deptno = 10;
select * from ods_merge_emp;
select * from ods_merge_emp_target;
update ods_merge_emp set comm=999 where deptno = 10;
---
create or replace procedure proc_ods_merge_emp(p_begin_time varchar2,p_end_time varchar2) is
v_begin_time varchar2(20):= p_begin_time;
v_end_time varchar2(20) := p_end_time;
begin
merge into ods_merge_emp_target t
using (select * from ods_merge_emp) s
on(t.empno=s.empno)
when matched then update set
t.ename = s.ename
,t.job = s.job
,t.mgr = s.mgr
,t.hiredate= s.hiredate
,t.sal = s.sal
,t.comm = s.comm
,t.deptno = s.deptno
when not matched then insert values (
s.empno
,s.ename
,s.job
,s.mgr
,s.hiredate
,s.sal
,s.comm
,s.deptno
);
end;
--12.2工作中的场景
--建表造数
drop table ods_merge_emp_target_02 ;
create table ods_merge_emp_02 as select e.*,trunc(sysdate,'dd')as create_date from emp e;
create table ods_merge_emp_target_02 as select * from ods_merge_emp_02 where 1=2;
update ods_merge_emp_02 set create_date = trunc(sysdate-1,'dd')where deptno=20;
update ods_merge_emp_02 set create_date = trunc(sysdate-2,'dd')where deptno=30;
select * from ods_merge_emp_02;
select * from ods_merge_emp_target_02 ;
---
create or replace procedure proc_ods_merge_emp(p_begin_time varchar2,p_end_time varchar2) is
v_begin_time date:= to_date(p_begin_time,'yyyy-mm-dd');
v_end_time date:= to_date(p_end_time,'yyyy-mm-dd');
begin
merge into ods_merge_emp_target_02 t
using (select * from ods_merge_emp_02 where create_date = v_begin_time) s
on(t.empno=s.empno)
when matched then update set
t.ename = s.ename
,t.job = s.job
,t.mgr = s.mgr
,t.hiredate= s.hiredate
,t.sal = s.sal
,t.comm = s.comm
,t.deptno = s.deptno
,t.create_date=s.create_date
when not matched then insert values (
s.empno
,s.ename
,s.job
,s.mgr
,s.hiredate
,s.sal
,s.comm
,s.deptno
,s.create_date
);
end;
-- 13 传入传出参数
create or replace procedure proc_ods_emp_bak(p_begin_time in varchar2,p_end_time in varchar2, p_out_sqlcode out varchar2) is
v_begin_time varchar2(20):=p_begin_time;
v_end_time varchar2(20):=p_end_time;
begin p_out_sqlcode:=0;
delete from ods_emp_bak
where create_time>to_date(v_begin_time,'yyyy-mm-dd')
and create_time<=to_date(v_end_time,'yyyy-mm-dd') ;
insert into ods_emp_bak
select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, CREATE_TIME
from ods_emp e
where e.create_time>to_date(v_begin_time,'yyyy-mm-dd')
and e.create_time<=to_date(v_end_time,'yyyy-mm-dd') ;
commit;
end;
-- 14日志
--作用： 看报错信息，看每一步执行时间，优化sql
create table dml_log
(
id varchar2(50)
,proc_name varchar2(30)
,domin varchar2(20)
,subject varchar2(20)
,step_no varchar2(20)
,begin_time date
,end_time date
,work_time date
,row_num number(10)
,elapsed number(10)
,all_elapsed number(10)
,sql_code varchar2(75)
,sql_errm varchar2(768)
,setp_desc varchar2(768)
);
comment on table dml_log is '日志表';
comment on column dml_log.id is '代理主键';
comment on column dml_log.proc_name is 'sp名称';
comment on column dml_log.domin is '作用域';
comment on column dml_log.subject is '主题';
comment on column dml_log.step_no is '步骤编号';
comment on column dml_log.begin_time is '开始时间';
comment on column dml_log.end_time is '结束时间';
comment on column dml_log.work_time is '工作日期';
comment on column dml_log.row_num is '影响行数';
comment on column dml_log.elapsed is '执行时间';
comment on column dml_log.all_elapsed is '总执行时间';
comment on column dml_log.sql_code is '错误代码';
comment on column dml_log.sql_errm is '错误描述';
comment on column dml_log.setp_desc is '步骤描述';
---举例 日志调试程序
create or replace procedure proc_inc_ods_emp_bak(p_begin_time in varchar2,p_end_time in varchar2, p_out_sqlcode out varchar2) is
v_begin_time varchar2(20):= p_begin_time;
v_end_time varchar2(20) := p_end_time;
v_sp_name varchar2(100);
v_sp_domain varchar2(100);
v_sp_subject varchar2(100);
v_log_stp_no varchar2(100);
v_log_begin_time date ;
v_setp_desc varchar2(100);
v_log_rowcount number(20);
begin
p_out_sqlcode :=0;
v_sp_name:='proc_inc_ods_emp_bak';
v_sp_domain:='测试作用域';
v_sp_subject:='测试主题';
---------- 第一步
v_log_stp_no:='step_01';
v_log_begin_time :=sysdate;
v_setp_desc :='清楚目标表数据 ods_emp_bak';
insert into dml_log(id,proc_name,domin,subject,step_no,begin_time,work_time,setp_desc)
values(sys_guid(),v_sp_name,v_sp_domain,v_sp_subject,v_log_stp_no,v_log_begin_time,v_log_begin_time,v_setp_desc);
commit;
delete from ods_emp_bak
where create_time>=to_date(v_begin_time,'yyyy-mm-dd')
and create_time<to_date(v_end_time,'yyyy-mm-dd') ;
v_log_rowcount:=sql%rowcount; -- 必须在commit 前面
commit;
--修改日志表
update dml_log set end_time=sysdate
,row_num=v_log_rowcount
,elapsed=(sysdate - v_log_begin_time)*86400
,all_elapsed=(sysdate - v_log_begin_time)*86400
where proc_name= v_sp_name
and domin = v_sp_domain
and subject=v_sp_subject
and step_no=v_log_stp_no
and begin_time= v_log_begin_time;
commit;
------------ 第二步
v_log_stp_no:='step_02';
v_log_begin_time:=sysdate;
v_setp_desc :='目标表插入数据 ods_emp_bak';
insert into dml_log(id,proc_name,domin,subject,step_no,begin_time,work_time,setp_desc)
values(sys_guid(),v_sp_name,v_sp_domain,v_sp_subject,v_log_stp_no,v_log_begin_time,v_log_begin_time,v_setp_desc);
commit;
insert into ods_emp_bak
select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, CREATE_TIME
from ods_emp e
where e.create_time>=to_date(v_begin_time,'yyyy-mm-dd')
and e.create_time<to_date(v_end_time,'yyyy-mm-dd');
v_log_rowcount:=sql%rowcount; -- 必须在commit 前面
commit;
update dml_log set end_time=sysdate
,row_num=v_log_rowcount
,elapsed=(sysdate - v_log_begin_time)*86400
,all_elapsed=(sysdate - v_log_begin_time)*86400
where proc_name= v_sp_name
and domin = v_sp_domain
and subject=v_sp_subject
and step_no=v_log_stp_no
and begin_time= v_log_begin_time;
commit;
end;
--15 异常 exception
create or replace procedure proc_excp_ods_emp_bak( p_start_time in varchar2
,p_end_time in varchar2
,p_out_sqlcode out varchar2
,p_out_errmsg out varchar2 ) is
v_start_time varchar2(20) := p_start_time;
v_end_time varchar2(20) := p_end_time;
v_sp_name varchar2(100); --存储名字
v_sp_domain varchar2(100); --作用域
v_sp_subject varchar2(100); --主题
v_begin_time date; --程序开始时间
v_log_stp_no varchar2(100); --步骤
v_log_begin_time date; --步骤开始时间
v_setp_desc varchar2(100); --步骤描述
v_log_rowcount number(20); --影响行数
v_log_desc varchar2(100); --错误信息
begin
p_out_sqlcode :=0;
v_sp_name:='proc_inc_ods_emp_bak';
v_sp_domain:='测试作用域';
v_sp_subject:='测试主题';
v_begin_time :=sysdate;
---------- 第一步
v_log_stp_no:='step_01';
v_log_begin_time :=sysdate;
v_setp_desc :='清楚目标表数据 ods_emp_bak';
insert into dml_log(id,proc_name,domin,subject,step_no,begin_time,work_time,setp_desc)
values(sys_guid(),v_sp_name,v_sp_domain,v_sp_subject,v_log_stp_no,v_log_begin_time,v_log_begin_time,v_setp_desc);
commit;
delete from ods_emp_bak
where create_time>=to_date(v_start_time,'yyyy-mm-dd')
and create_time<to_date(v_end_time,'yyyy-mm-dd') ;
v_log_rowcount:=sql%rowcount; -- 必须在commit 前面
commit;
--修改日志表
update dml_log set end_time = sysdate
,row_num = v_log_rowcount
,elapsed = (sysdate - v_log_begin_time)*86400
,all_elapsed = (sysdate - v_begin_time)*86400
where proc_name = v_sp_name
and domin = v_sp_domain
and subject = v_sp_subject
and step_no = v_log_stp_no
and begin_time = v_log_begin_time;
commit;
------------ 第二步
v_log_stp_no :='step_02';
v_log_begin_time :=sysdate;
v_setp_desc :='目标表插入数据 ods_emp_bak';
insert into dml_log(id,proc_name,domin,subject,step_no,begin_time,work_time,setp_desc)
values(sys_guid(),v_sp_name,v_sp_domain,v_sp_subject,v_log_stp_no,v_log_begin_time,v_log_begin_time,v_setp_desc);
commit;
insert into ods_emp_bak
select empno
, ename
, job
, mgr
, hiredate
, sal
, comm
, deptno
, create_time
from ods_emp e
where e.create_time>=to_date(v_start_time,'yyyy-mm-dd')
and e.create_time<to_date(v_end_time,'yyyy-mm-dd');
v_log_rowcount:=sql%rowcount; -- 必须在commit 前面
commit;
update dml_log set end_time=sysdate
,row_num=v_log_rowcount
,elapsed=(sysdate - v_log_begin_time)*86400
,all_elapsed=(sysdate - v_begin_time)*86400
where proc_name= v_sp_name
and domin = v_sp_domain
and subject=v_sp_subject
and step_no=v_log_stp_no
and begin_time= v_log_begin_time;
commit;
----异常
exception
when others then
p_out_sqlcode := sqlcode; --sqlcode 数据库操作返回码，其中0成功，非0失败
v_log_desc := sqlerrm ; -- sqlerrm是一则函数，返回指定错误代码信息
p_out_errmsg := p_out_sqlcode || ':'|| v_log_desc;
rollback; --必须加上，不然易造成锁表
v_log_stp_no :='step_99';
insert into dml_log(id,proc_name,domin,subject,step_no,begin_time,end_time,work_time,elapsed,all_elapsed,sql_code,sql_errm,setp_desc)
values(sys_guid(),v_sp_name,v_sp_domain,v_sp_subject,v_log_stp_no,v_log_begin_time,sysdate,v_log_begin_time
,(sysdate-v_log_begin_time)*86400,(sysdate-v_begin_time)*86400,p_out_sqlcode,v_log_desc,v_setp_desc);
commit;
end;
--16 自定义函数
create or replace function func_abs(f_num in number)
return number
is
v_num number := f_num;
begin
if v_num >= 0 then v_num:=v_num;
else v_num:= -v_num;
end if;
return v_num;
end;
--17包 package
--主要作用： 把作用相同或者相同类型的sp/func包装到一起
--1.包头
create or replace package pack_func
is
function func_abs(f_num in number) return number;
procedure proc_excp_ods_emp_bak( p_start_time in varchar2
,p_end_time in varchar2
,p_out_sqlcode out varchar2
,p_out_errmsg out varchar2 );
end pack_func ;
--2.包体
create or replace package body pack_func
is
--第一个函数
function func_abs(f_num in number)
return number
is
v_num number := f_num;
begin
if v_num >= 0 then v_num:=v_num;
else v_num:= -v_num;
end if;
return v_num;
end;
--第一个过程
procedure proc_excp_ods_emp_bak( p_start_time in varchar2
,p_end_time in varchar2
,p_out_sqlcode out varchar2
,p_out_errmsg out varchar2 ) is
v_start_time varchar2(20) := p_start_time;
v_end_time varchar2(20) := p_end_time;
v_sp_name varchar2(100); --存储名字
v_sp_domain varchar2(100); --作用域
v_sp_subject varchar2(100); --主题
v_begin_time date; --程序开始时间
v_log_stp_no varchar2(100); --步骤
v_log_begin_time date; --步骤开始时间
v_setp_desc varchar2(100); --步骤描述
v_log_rowcount number(20); --影响行数
v_log_desc varchar2(100); --错误信息
begin
p_out_sqlcode :=0;
v_sp_name:='proc_inc_ods_emp_bak';
v_sp_domain:='测试作用域';
v_sp_subject:='测试主题';
v_begin_time :=sysdate;
---------- 第一步
v_log_stp_no:='step_01';
v_log_begin_time :=sysdate;
v_setp_desc :='清楚目标表数据 ods_emp_bak';
insert into dml_log(id,proc_name,domin,subject,step_no,begin_time,work_time,setp_desc)
values(sys_guid(),v_sp_name,v_sp_domain,v_sp_subject,v_log_stp_no,v_log_begin_time,v_log_begin_time,v_setp_desc);
commit;
delete from ods_emp_bak
where create_time>=to_date(v_start_time,'yyyy-mm-dd')
and create_time<to_date(v_end_time,'yyyy-mm-dd') ;
v_log_rowcount:=sql%rowcount; -- 必须在commit 前面
commit;
--修改日志表
update dml_log set end_time = sysdate
,row_num = v_log_rowcount
,elapsed = (sysdate - v_log_begin_time)*86400
,all_elapsed = (sysdate - v_begin_time)*86400
where proc_name = v_sp_name
and domin = v_sp_domain
and subject = v_sp_subject
and step_no = v_log_stp_no
and begin_time = v_log_begin_time;
commit;
------------ 第二步
v_log_stp_no :='step_02';
v_log_begin_time :=sysdate;
v_setp_desc :='目标表插入数据 ods_emp_bak';
insert into dml_log(id,proc_name,domin,subject,step_no,begin_time,work_time,setp_desc)
values(sys_guid(),v_sp_name,v_sp_domain,v_sp_subject,v_log_stp_no,v_log_begin_time,v_log_begin_time,v_setp_desc);
commit;
insert into ods_emp_bak
select empno
, ename
, job
, mgr
, hiredate
, sal
, comm
, deptno
, create_time
from ods_emp e
where e.create_time>=to_date(v_start_time,'yyyy-mm-dd')
and e.create_time<to_date(v_end_time,'yyyy-mm-dd');
v_log_rowcount:=sql%rowcount; -- 必须在commit 前面
commit;
update dml_log set end_time=sysdate
,row_num=v_log_rowcount
,elapsed=(sysdate - v_log_begin_time)*86400
,all_elapsed=(sysdate - v_begin_time)*86400
where proc_name= v_sp_name
and domin = v_sp_domain
and subject=v_sp_subject
and step_no=v_log_stp_no
and begin_time= v_log_begin_time;
commit;
----异常
exception
when others then
p_out_sqlcode := sqlcode; --sqlcode 数据库操作返回码，其中0成功，非0失败
v_log_desc := sqlerrm ; -- sqlerrm是一则函数，返回指定错误代码信息
p_out_errmsg := p_out_sqlcode || ':'|| v_log_desc;
rollback; --必须加上，不然易造成锁表
v_log_stp_no :='step_99';
insert into dml_log(id,proc_name,domin,subject,step_no,begin_time,end_time,work_time,elapsed,all_elapsed,sql_code,sql_errm,setp_desc)
values(sys_guid(),v_sp_name,v_sp_domain,v_sp_subject,v_log_stp_no,v_log_begin_time,sysdate,v_log_begin_time
,(sysdate-v_log_begin_time)*86400,(sysdate-v_begin_time)*86400,p_out_sqlcode,v_log_desc,v_setp_desc);
commit;
end;
end pack_func ;
--测试调用 函数
select pack_func.func_abs(-6) from dual;
--测试调用 存储
declare
result1 varchar2(100);
result2 varchar2(100);
begin
-- Call the procedure
scott.pack_func.proc_excp_ods_emp_bak('2022-03-02'
,'2022-03-03'
,result1
,result2);
dbms_output.put_line(result1||result2);
end;
--18.触发器
-- 触发事件 insert、update、delete
create table emp_source as select e.*,sysdate as update_date from emp e where deptno <> 10;
create table emp_target as select * from emp_source where 1=2;
select * from emp_source;
select * from emp_target;
--验证
insert into emp_source select e.*,sysdate as update_date from emp e where deptno = 30;
update emp_source set ename='yan1',empno=778 where empno=7782;
delete from emp_source where deptno =30;
-- 举例
create or replace trigger tr_emp_target
after insert or update or delete
on emp_source
for each row
declare
begin
case when inserting then
insert into emp_target(empno,ename,job,mgr,hiredate,sal,comm,deptno,update_date)
values(:new.empno
,:new.ename
,:new.job
,:new.mgr
,:new.hiredate
,:new.sal
,:new.comm
,:new.deptno
,:new.update_date);
when updating then
update emp_target set
empno = :new.empno
, ename = :new.ename
,job = :new.job
,mgr = :new.mgr
,hiredate = :new.hiredate
,comm = :new.comm
,deptno = :new.deptno
,update_date = :new.update_date
where empno = :old.empno ;
when deleting then
delete from emp_target where empno = :old.empno;
end case;
end;
-- 19 递归查询
--找上级
select level,e.* from emp e
connect by prior e.mgr = e.empno
start with e.mgr = 7788
order by level;
--找下级
select level,e.* from emp e
connect by e.mgr = prior e.empno
start with e.mgr = 7839
order by level;
--举例
with tmp as
(select level as rn ,e.* from emp e
connect by e.mgr = prior e.empno
start with e.mgr = 7839
order by level)
select t1.rn ,t1.empno ,t1.ename ,t1.mgr,
t2.rn ,t2.empno ,t2.ename ,t2.mgr,
t3.rn ,t3.empno ,t3.ename ,t3.mgr,
t4.rn ,t4.empno ,t4.ename ,t4.mgr
from tmp t1
left join tmp t2 on t1.empno = t2.mgr and t2.rn = 2
left join tmp t3 on t2.empno = t3.mgr and t3.rn = 3
left join tmp t4 on t3.empno = t4.mgr and t4.rn = 4
where t1.rn = 1
--20 正则表达式
--- 待续 ...
DBA 成长随笔 --- Oracle 11g, 程序设计
于 2022-04-13 16:10:48 首次发布
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
