Oracle笔记 、PL/SQL存储过程

2018-01-20 11:04:44来源:oschina作者:晨曦艾伯特人点击

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

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台