Oracle笔记 、PL/SQL存储过程

2019独角兽企业重金招聘Python工程师标准>>>

Oracle笔记 十、PL/SQL存储过程

--create or replace 创建或替换，如果存在就替换，不存在就创建

create or replace procedure p

is

cursor c

is

select * from dept2 for update;

begin

for row_record in c loop

if (row_record.deptno = 30) then

update dept2 set dname = substr(dname, 0, length(dname) - 3) where current of c;

end if;

end loop;

end;

exec p;

begin

p;

end;

--带参存储过程

--in 输入参数，不带in out 默认输入参数

--out 输出参数

--in out 同时带的是输入输入参数

create or replace procedure p2(

a in number,

b number,

s_result out number,

s_temp in out number

)

is

begin

if (a > b) then

s_result := a;

else

s_result := b;

end if;

s_temp := s_temp + 3;

end;

--调用存储过程

declare

v_a number := 4;

v_b number := 6;

v_result number;

v_temp number := 5;

begin

p2(v_a, v_b, v_result, v_temp);

dbms_output.put_line(v_a);

dbms_output.put_line(v_b);

dbms_output.put_line(v_result);

dbms_output.put_line(v_temp);

end;

---删除一个表的过程

create or replace procedure drop_table(tname varchar2)

as

total int := 0;

begin

select count(*) into total from user_tables

where table_name = upper(tname);

if total >= 1 then

execute immediate 'drop table '||tname; --此处必须用动态sql

end if;

end;

select * from user_tables;

--递归存储过程

create or replace procedure pro_emp(sEmpno emp.empno%type, sLevel integer)

is

cursor c is select * from emp where mgr = sEmpno;

prefixStr varchar(255);

begin

for i in 1..sLevel loop

prefixStr := prefixStr || '----';

end loop;

for row_data in c loop

dbms_output.put_line(prefixStr || row_data.ename);

pro_emp(row_data.empno, sLevel + 1);

end loop;

end;

select * from emp;

begin

pro_emp(7839, 0);

end;

转载于:https://my.oschina.net/zhanggc/blog/1609700