1、基本语法

-- 存储过程   procedure  sp
-- 建表造数
drop table emp_bak;
create table emp_bak as select * from emp where 1 = 2;
select * from emp_bak;
-- 最简单的程序过程
create procedure sp_emp_bak is
begin
insert into emp_bak
select * from emp where deptno = 10;
commit;
end;
-- 如果有就替换
create or replace procedure sp_emp_bak is
begin
insert into emp_bak
select * from emp where deptno = 10;
commit;
end;
-- 测试过程
-- 第一步 编译  执行这个sp的代码  仅仅是把这个代码存到我们的数据库 
-- 第二步 调用
1.在对象这先找到要测试的sp>选择对应的sp右键>测试
2.sql窗口选择对应的sp右键>测试
3.直接sql窗口写调用语句
begin sp_emp_bak; end;

2、全量抽数

-- 存储过程   procedure  sp
-- 全量抽数  把一个表的数据全部放到另外一张表 并支持重跑



-- 建表造数
drop table emp_bak;
create table emp_bak as select * from emp where 1 = 2;
select * from emp_bak;


-- 全量抽数
create or replace procedure sp_emp_bak
is -- as
begin
  -- 先删除后插入
 -- truncate table emp_bak;
   execute immediate 'truncate table emp_bak';
  -- 后插入
  insert into emp_bak select * from emp;
  commit;
end;



-- 标准化一下 不允许写*  insert表后面字段也不能省略  字段注释
create or replace procedure sp_emp_bak
is -- as
begin
  -- 先删除后插入
 -- truncate table emp_bak;
   execute immediate 'truncate table emp_bak';
  -- 后插入
  insert into emp_bak (
   empno       -- 员工编号
  ,ename       -- 姓名
  ,job         -- 工作
  ,mgr         -- 上级编号
  ,hiredate    -- 入职日期
  ,sal         -- 薪水
  ,comm        -- 奖金
  ,deptno      -- 部门编号 
  )   
  select  empno    -- 员工编号
         ,ename    -- 姓名
         ,job      -- 工作
         ,mgr      -- 上级编号
         ,hiredate -- 入职日期
         ,sal      -- 薪水
         ,comm     -- 奖金
         ,deptno   -- 部门编号
    from emp;
  commit;
end;

3、merge into 匹配则更新不匹配则插入

-- 语法

merge into 目标表

using (增量)

on (匹配字段)

when matched then update set   --update和set之间不需要加表名

when not matched then insert values

--insert和values之间不需要加into 表名





-- 建表造数

select * from ods_merge_emp;

select * from ods_merge_emp_target;



create table ods_merge_emp        as select * from emp ;

create table ods_merge_emp_target as select * from emp where deptno = 10;

update ods_merge_emp set comm = 999 where deptno = 10;

commit;







--

create or replace procedure sp_ods_merge_emp_target(

 p_start_time varchar2

,p_end_time   varchar2

)

is -- as

v_start_time varchar2(30):= p_start_time;

v_end_time   varchar2(30):= 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.empno    = s.empno     -- 员工编号   匹配字段是不能被更新的

       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    -- 部门编号 

       )

     ;

    commit;  

end; 





-- 工作中的场景  增量

-- 建表造数

select * from ods_merge_emp_02;

select * from ods_merge_emp_target_02;



create table ods_merge_emp_02        as select a.*,trunc(sysdate,'dd') as create_time from emp a;

update ods_merge_emp_02 set create_time = trunc(sysdate-1,'dd') where deptno = 20;

update ods_merge_emp_02 set create_time = trunc(sysdate-2,'dd') where deptno = 30;

commit;



truncate table ods_merge_emp_target_02;

create table ods_merge_emp_target_02 as select * from ods_merge_emp_02 where 1 = 2;



update ods_merge_emp_02 set create_time = trunc(sysdate-1,'dd'),comm = 9999 where empno = 7698;

commit;



-----

create or replace procedure sp_ods_merge_emp_target_02(

 p_start_time varchar2

,p_end_time   varchar2

)

is -- as

v_start_time date:= to_date(p_start_time,'yyyymmdd');

v_end_time   date:= to_date(p_end_time,'yyyymmdd');



begin



    merge into ods_merge_emp_target_02 t

    using (select * from ods_merge_emp_02 where create_time = v_start_time) s

       on ( t.empno = s.empno )

     when matched then update set   

      -- t.empno    = s.empno     -- 员工编号   匹配字段是不能被更新的

       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_time   = s.create_time

     when not matched then insert values(

      s.empno     -- 员工编号

     ,s.ename     -- 姓名

     ,s.job       -- 工作

     ,s.mgr       -- 上级编号

     ,s.hiredate  -- 入职日期

     ,s.sal       -- 薪水

     ,s.comm      -- 奖金

     ,s.deptno    -- 部门编号 

     ,s.create_time  )

     ;

    commit;  

end;