PL/SQL(四)

きおくてつづき
义齿
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;
/