存储过程2

来源：互联网发布：手机淘宝满减怎么设置编辑：程序博客网时间：2024/06/05 20:29

1. 创建三张表 dept10,dept20,dept30，表结构和 dept 一致（不拷贝数据）
create table dept10 as select * from dept where 1=2;
create table dept20 as select * from dept where 1=2;
create table dept30 as select * from dept where 1=2;
2. 编写一个存储过程 mypro，
i. 把 dept 表中 depto=10 的数据，存到 dept10,
ii. 把 dept 表中 depto=20 的数据，存到 dept20

iii. 把 dept 表中 depto=30 的数据，存到 dept30

写法1

create or replace procedure myproc
as
begin
insert into dept10 select * from dept where deptno=10;
insert into dept20 select * from dept where deptno=20;
insert into dept30 select * from dept where deptno=30;
end;

写法2

备注存储过程中 if 。。。。。elsif 的写法

CREATE OR REPLACE PROCEDURE myproc2
AS   CURSOR mycur IS SELECT * FROM dept;
deptInfo dept%ROWTYPE;
BEGIN
FOR deptInfo IN mycur LOOP
    if deptInfo.deptno =10 then   insert into dept10(deptno,dname,loc)
     values (deptInfo.deptno,deptInfo.dname,deptInfo.loc);
    elsif deptInfo.deptno =20 then   insert into dept20(deptno,dname,loc)
    values (deptInfo.deptno,deptInfo.dname,deptInfo.loc);
    elsif deptInfo.deptno =30 then   insert into dept30(deptno,dname,loc)
    values (deptInfo.deptno,deptInfo.dname,deptInfo.loc);
    end if;
END LOOP;
END;

写法3

CREATE OR REPLACE PROCEDURE myproc2 AS
CURSOR mycur IS   SELECT * FROM dept;
deptInfo dept%ROWTYPE;
BEGIN
OPEN mycur;
FETCH mycur INTO deptInfo;
WHILE (mycur%FOUND) LOOP
     if deptInfo.deptno =10 then   insert into dept10(deptno,dname,loc)
     values (deptInfo.deptno,deptInfo.dname,deptInfo.loc);
     END IF ;
    if deptInfo.deptno ='20' then   insert into dept20(deptno,dname,loc)
    values (deptInfo.deptno,deptInfo.dname,deptInfo.loc);
     END IF ;
    if deptInfo.deptno ='30' then   insert into dept30(deptno,dname,loc)
    values (deptInfo.deptno,deptInfo.dname,deptInfo.loc);
     END IF ;
    FETCH mycur INTO deptInfo;
END LOOP;
END;
-----select * from dept30

0 0