oracle open for using的用法,oracle open for using怎么用

一、无参程序过程语法

create or replace procedure NoParPro

as  ;

begin

;

exception     //存储过程异常

;

end;二、带参存储过程实例

create or replace procedure queryempname(sfindno emp.empno%type) as

sName emp.ename%type;

sjob emp.job%type;

begin

....

exception

....

end;  三、 带参数存储过程含赋值方式

create or replace procedure runbyparmeters  (isal in emp.sal%type,  sname out varchar,sjob in out varchar)

as icount number;

begin

select count(*) into icount from emp where sal>isal and job=sjob;

if icount=1 then

....

else

....

end if;

exception

when too_many_rows then

DBMS_OUTPUT.PUT_LINE('返回值多于1行');

when others then

DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');

end;

在Oracle中对存储过程的调用

过程调用方式一

declare

realsal emp.sal%type;

realname varchar(40);

realjob varchar(40);

begin   //存储过程调用开始

realsal:=1100;

realname:='';

realjob:='CLERK';

runbyparmeters(realsal,realname,realjob);     －－必须按顺序

DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);

END;  //过程调用结束过程调用方式二

declare

realsal emp.sal%type;

realname varchar(40);

realjob varchar(40);

begin    //过程调用开始

realsal:=1100;

realname:='';

realjob:='CLERK';

runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob);  －－指定值对应变量顺序可变

DBMS_OUTPUT.PUT_LINE(REALNAME||'   '||REALJOB);

END;  //过程调用结束

给你参考一下，希望对你有所帮助祝你好运

◆◆

评论读取中....

请登录后再发表评论!

◆◆

修改失败，请稍后尝试