Oracle存储过程可以有无参数存储过程和带参数存储过程。 一、无参程序过程语法 1 create or replace procedure NoParPro 2 as ; 3 begin 4 ; 5 exception //存储过程异常 6 ; 7 end; 8 二、带参存储过程实例 1 create or replace procedure queryempname(sfindno emp.empno%type) as 2 sName emp.ename%type; 3 sjob emp.job%type; 4 begin 5 .... 7 exception .... 14 end; 15 三、 带参数存储过程含赋值方式 1 create or replace procedure runbyparmeters (isal in emp.sal%type, sname out varchar,sjob in out varchar) 2 as icount number; 3 begin 4 select count(*) into icount from emp where sal>isal and job=sjob; 5 if icount=1 then 6 .... 9 else 10 .... 12 end if; 13 exception 14 when too_many_rows then 15 DBMS_OUTPUT.PUT_LINE('返回值多于1行'); 16 when others then 17 DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！'); 18 end; 19 四、在Oracle中对存储过程的调用 过程调用方式一 1 declare 2 realsal emp.sal%type; 3 realname varchar(40); 4 realjob varchar(40); 5 begin //存储过程调用开始 6 realsal:=1100; 7 realname:=''; 8 realjob:='CLERK'; 9 runbyparmeters(realsal,realname,realjob); －－必须按顺序 10 DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB); 11 END; //过程调用结束 12 过程调用方式二 1 declare 2 realsal emp.sal%type; 3 realname varchar(40); 4 realjob varchar(40); 5 begin //过程调用开始 6 realsal:=1100; 7 realname:=''; 8 realjob:='CLERK'; 9 runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob); －－指定值对应变量顺序可变 10 DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB); 11 END; //过程调用结束 |
/6 
|手机版|小黑屋|梦想之都-俊月星空
( 粤ICP备18056059号 )|网站地图
GMT+8, 2025-7-2 00:58 , Processed in 0.028719 second(s), 18 queries .
Powered by Mxzdjyxk! X3.5
© 2001-2025 Discuz! Team.
