PL/SQL(四)

存储过程

create or replace procedure 名 is

begin

end 名;

 create or replace procedure d_v is cursor rs is select * from dept; begin for r in rs loop dbms_output.put_line(r.dname||chr(9)||r.loc||chr(9)||r.deptno); end loop; end d_v;

 

执行 execute 名begind_v;end;/

 

输入部门编号 10 然后删除 此部门 然后显示 删除多少条记录 create or replace procedure p_c(v_deptno in number,v_num out number) is begin delete from emp where deptno=v_deptno; v_num:=sql%rowcount; dbms_output.put_line('删除了'||v_num||'行'); end p_c;调用SQL> declare  2  v_num int;  3  begin  4  p_c(10,v_num);  5  end;  6  .SQL> /删除了3行

 

输入 表明 输出 内容行数 create or replace procedure v_main(v_name varchar2) is rs sys_refcursor; r_num int; v_sql varchar2(200):='select count(*) from '||v_name; begin open rs for v_sql; fetch rs into r_num; dbms_output.put_line(r_num); close rs; end v_main;

 

传参 与 返回值 create or replace procedure p_a(v_nu in int,v_ename out varchar2) is v_outstr varchar2(200); v_name varchar2(20); begin select ename into v_name from emp where empno=v_nu; v_ename:=v_name; end p_a;执行SQL> declare  2  v_ename varchar2(20);  3  begin  4  p_a(&num,v_ename);  5  dbms_output.put_line(v_ename);  6  end;  7  /输入 num 的值:  7369原值    4: p_a(&num,v_ename);新值    4: p_a(7369,v_ename);SMITH

 

返回结果集SQL> create or replace procedure p_test(out_cursor out sys_refcursor) is  2  begin  3  open out_cursor for select * from emp;  4  end p_test;  5  /过程已创建。SQL> declare  2  mycursor sys_refcursor;  3  r_emp emp%rowtype;  4  begin  5  p_Test(mycursor);  6  loop  7  fetch mycursor into r_emp;  8  exit when mycursor%notfound;  9  dbms_output.put_line(r_emp.ename); 10  end loop; 11  end; 12  /

 

输入 行数 比如 6-10 然后显示信息create or replace procedure p_a(v_first in int,v_second in int,v_rs out sys_refcursor) is v_sql varchar2(200):='select empno,ename,job,mgr,hiredate,sal,comm,deptno from (select f.*,rownum s from emp f) e where e.s between '||v_first||' and '||v_second; begin open v_rs for v_sql; end p_a;declarers sys_refcursor;r_emp emp%rowtype;beginp_a(6,10,rs);loopfetch rs into r_emp;exit when rs%notfound;dbms_output.put_line(r_emp.ename||chr(9)||r_emp.empno);end loop;end;/