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;