过程

create or replace procedure out_time is
begin
  dbms_output.put_line(systimestamp);
end out_time;

create or replace procedure add_employee(eno number,sal number,dno number,name varchar2 default 'clerk') is
  e_integerity exception;
  pragma exception_init(e_integerity,-2291);
begin
  insert into emp(empno,salary,deptno,ename) values(eno,sal,dno,name);
  exception
    when dup_val_on_index then
      raise_application_error(-20000,'雇员号不能重复');
    when e_integerity then
      raise_application_error(-20001,'部门号不存在');  
end add_employee;

create or replace procedure query_employee1(eno number,name out varchar2,sal out number)
is
begin
  select ename,salary into name,sal from emp where deptno = eno;
  exception when no_data_found then
    raise_application_error(-20000,'该雇员不存在');
end;

create or replace procedure compute(num1 in out number,num2 in out number)
is
  v1 number;
  v2 number; 
begin
  v1 := num1/num2;
  v2 := mod(num1,num2);
  num1 := v1;
  num2 := v2;
end;

 
SQL> exec :num1 := 100;
 
PL/SQL procedure successfully completed
num1
---------
100
 
SQL> exec :num2 := 30;
 
PL/SQL procedure successfully completed
num2
---------
30
 
SQL> exec compute(:num1,:num2);
 
PL/SQL procedure successfully completed
num1
---------
3.33333333333333
num2
---------
10

create or replace procedure add_dept
(dno number, dname varchar2 default null,salary number,loc varchar2 default null) is
begin
  insert into emp values(dno,dname,loc,salary);
  exception when dup_val_on_index then
    raise_application_error(-20000,'部门号不能重复');
end add_dept;

 

 

create or replace procedure coutnum(y number, m number) is
num number;
begin
select count(*) into num from t_userinfo
   where to_date(to_char(REGIDATE,'yyyy-mm'),'yyyy-mm')=to_date((to_char(y)||'-'||to_char(m)),'yyyy-mm');
  dbms_output.put_line(num);
end coutnum;

 

1.返回结果集的过程

 

create or replace procedure test_pro(vid tj_test.id%type,curtest out SYS_REFCURSOR) as
   vdata tj_test%rowtype;
begin
  open curtest for select id,name,age from tj_test where id <= vid;
  loop
    fetch curtest into vdata;
     exit when curtest%notfound;
    dbms_output.put_line(vdata.id);
  end loop;
  close curtest; 
end test_pro;

 

 

SQL> create or replace procedure FYRK_SUM(table1 out sys_refcursor, zl in varchar2, kf in varchar2) 
is
  2 str_sql varchar2(5000);  
  3 begin
  4 str_sql := 'select * from TJ_TEST1 where 1=1 ';  
  5 IF(zl IS NOT NULL)
  6 THEN
  7 str_sql := str_sql || 'and name = :zl';
  8 END IF;  
  9 IF(kf IS NOT NULL)
 10 THEN
 11 str_sql := str_sql || ' and address = :kf1';
 12 END IF;  
 13 dbms_output.put_line(str_sql);  
 14 open table1 for str_sql using zl,kf;
 15 end FYRK_SUM;
 16 /

过程已创建。

SQL> exec FYRK_SUM(:tcur,'joe','sh');
select * from TJ_TEST1 where 1=1 and name = :zl and address = :kf1

PL/SQL 过程已成功完成。

SQL> print :tcur;

  ID NAME AGE ADDRESS
---------- -------------------- ---------- --------------------
  3 joe 27 sh

 

2.大数据作为参数

 

CREATE OR REPLACE PROCEDURE test_pro(v_a CLOB)
AS
BEGIN
  INSERT INTO t(id,content)VALUES(1,empty_clob());
  UPDATE t SET content=v_a WHERE id=1;
END;

 

3.动态调用不同存储过程

CREATE OR REPLACE PROCEDURE pro_test

(

    ainput1  in varchar2,

    ainput2  in varchar2,

    Returns1 in out varchar2,

    Returns2 in out varchar2   

)

IS

  dates date:=sysdate;

BEGIN

    

    Returns1:='-->'||ainput1;

    Returns2:='-->'||ainput2;

   

END;

定义好了后就可以测试了；

用下面的方法动态调用

declare

    vproname varchar2(200);

    output1 varchar2(200);

    output2 varchar2(200);

    input1  varchar2(200);

    input2  varchar2(200);

begin

    input1:='input1';

    input2:='input2';

    vproname:='pro_test';--过程名

    execute immediate 'call '||vproname||'(:V1,:V2,:V3, :V4)' using in input1,in input2, in out output1,in out output2;

    dbms_output.put_line(output1);   

    dbms_output.put_line(output2);           

end;