oracle存储过程

基本语法

create or replace procedure 存储过程名（param1 in type，param2 out type）

as

变量1 类型（值范围）; --vs_msg VARCHAR2(4000);

变量2 类型（值范围）;

Begin

Select count(*) into 变量1 from 表A where列名=param1；

 

If (判断条件) then

Select 列名 into 变量2 from 表A where列名=param1；

Dbms_output。Put_line(‘打印信息’);

Elsif (判断条件) then

Dbms_output。Put_line(‘打印信息’);

Else

Raise 异常名（NO_DATA_FOUND）;

End if;

Exception

When others then

Rollback;

End;

1.if

create or replace procedure test(x in number,y out number)
as
begin
if x>0 then
y:=0-x;
dbms_output.put_line(y);
end if;
if x=0 then
y:=1;
dbms_output.put_line(y);
end if;
end test;

2.cursor

create or replace procedure test1 as
Cursor cur is select menu_id from cfg_menu;na number;
begin
for na in cur LOOP
dbms_output.put_line(na.menu_id);
end LOOP;
end test1;

3.可传递cursor

create or replace procedure test3(sCursor out SYS_REFCURSOR) as
cur Sys_Refcursor;
begin
open cur for select * from cfg_menu;
sCursor:=cur;
end test3;

4.while

create or replace procedure test2(x in number,y out number) as
num number;
begin
num:=x;
y:=0;
while num>0 LOOP
num:=num-1;
y:=y+num;
end LOOP;
end;