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 名
begin
d_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;



declare
rs sys_refcursor;
r_emp emp%rowtype;
begin
p_a(6,10,rs);
loop
fetch rs into r_emp;
exit when rs%notfound;
dbms_output.put_line(r_emp.ename||chr(9)||r_emp.empno);
end loop;
end;
/