Oracle procedure 存储过程

比眉伴天荒 2022-12-10 11:29 284阅读 0赞

Oracle procedure 基本语法：

CREATE \[OR REPLACE\] PROCEDURE 存储过程名\[(参数 \[IN|OUT|INOUT\] 数据类型...)\]  
\{IS|AS\}  
\[说明部分，例如：变量声明\]  
BEGIN   
可执行部分  
\[EXCEPTION  
错误处理部分\]  
END \[存储过程名\];  
/

\-- 创建存储过程  
\-- create procedure 'testproc'  
create or replace procedure testproc  
as  
  cnt number(38);  
begin  
  select count(\*) into cnt from dual;  
  dbms\_output.put\_line('cnt: '||cnt);  
end;  
/

注意：  
1.在 PROCEDURE 和 FUNCTION 中使用 IS/AS 并没有太大区别。但在 VIEW 中只能用 AS 而不能用 IS ，在 CURSOR 中只能用 IS 而不能用AS。IS/AS 后面一般跟变量声明。

\-- 查询创建的存储过程  
col name for a30;  
col type for a20;

\-- show all  
select \* from user\_source;

\-- show some  
select \* from user\_source where name like upper('%test%');

\-- show one  
select \* from user\_source where name = upper('testproc');

\-- 查询存储过程状态（包括编译成功与否状态）  
col object\_name for a20;  
col subobject\_name for a20;  
select object\_name, status, last\_ddl\_time, created, timestamp from user\_objects where object\_name = upper('testproc');

\-- 调用存储过程  
\-- call procedure 'testproc'  
set serveroutput on;  
begin  
  testproc;  
end;  
/

直接在SQL命令行执行命令调用存储过程  
set serveroutput on;  
execute testproc;

\-- 重新编译存储过程  
alter procedure testproc compile;  
select object\_name, status, last\_ddl\_time, created, timestamp from user\_objects where object\_name = upper('testproc');

删除存储过程  
drop procedure testpro;

使用游标查询多行结果集

SQL Server支持存储过程中使用 select \* from  student 来查询结果集，但Oracle不支持，会报错Warning: Procedure  created  with  compilation  errors。  
Oracle中可以使用游标来返回结果集合。

\-- 查询结果为多行结果集。student表三个字段：id,name,age  
create or replace procedure testproc2  
is  
  cursor cur\_all is select id,name,age from student;  --定义游标 cur\_all  
  c\_id student.id%type;     --声明变量分别用来保存查询表的各列  
  c\_name student.name%type;  
  c\_age student.age%type;  
begin  
open cur\_all;        --打开游标  
  loop                    --循环处理  
    fetch cur\_all into c\_id,c\_name,c\_age;    --操作数据  
    exit when cur\_all%NOTFOUND;            --循环退出条件：最近的FETCH语句返回一行数据则为FALSE  
    if cur\_all%FOUND then                          --最近的FETCH语句返回一行数据则为TRUE  
      dbms\_output.put\_line(c\_id||' '||c\_name||' '||c\_age);  
    end if;  
  end loop;  
close cur\_all;     --关闭游标  
end;  
/

execute testproc2;  
1 jack 20  
2 lucy 21  
3 happy 20  
4 white 21

\--带入参(IN)的存储过程，普通循环1  
create or replace procedure testproc2(var1 in varchar2 default 'id name age')  
is  
  cursor cur\_all is select id,name,age from student;  --定义游标 cur\_all  
  c\_id student.id%type;                               --声明变量分别用来保存查询表的各列  
  c\_name student.name%type;  
  c\_age student.age%type;  
begin  
dbms\_output.put\_line(var1);  
open cur\_all;                                         --打开游标  
  loop                                                --循环处理  
    fetch cur\_all into c\_id,c\_name,c\_age;             --操作数据  
    exit when cur\_all%NOTFOUND;                       --循环退出条件：最近的FETCH语句返回一行数据则为FALSE  
    if cur\_all%FOUND then                             --最近的FETCH语句返回一行数据则为TRUE  
      dbms\_output.put\_line(c\_id||' '||c\_name||' '||c\_age);  
    end if;  
  end loop;  
close cur\_all;                                        --关闭游标  
end;  
/

\--带入参(IN)的存储过程，普通循环2  
create or replace procedure testproc2(var1 in varchar2 default 'id name age')  
is  
  cursor cur\_all is select \* from student;  --定义游标 cur\_all  
  cur\_line student%rowtype;                 --定义rowtype  
begin  
dbms\_output.put\_line(var1);  
open cur\_all;                                         --打开游标  
  loop                                                --循环处理  
    fetch cur\_all into cur\_line;                      --操作数据  
    exit when cur\_all%NOTFOUND;                       --循环退出条件：最近的FETCH语句返回一行数据则为FALSE  
    if cur\_all%FOUND then                             --最近的FETCH语句返回一行数据则为TRUE  
      dbms\_output.put\_line(cur\_line.id||' '||cur\_line.name||' '||cur\_line.age);  
    end if;  
  end loop;  
close cur\_all;                                        --关闭游标  
end;  
/

execute testproc2  
id name age  
1 jack 20  
2 lucy 21  
3 happy 20  
4 white 21

execute testproc2('id2, name2, age2')  
id2, name2, age2  
1 jack 20  
2 lucy 21  
3 happy 20  
4 white 21

call testproc2();  
id name age  
1 jack 20  
2 lucy 21  
3 happy 20  
4 white 21

call testproc2('id3 name3 age3');  
id3 name3 age3  
1 jack 20  
2 lucy 21  
3 happy 20  
4 white 21

\--带入参(IN)的存储过程，用"for in"使用cursor(游标)  
create or replace procedure testproc2(var1 in varchar2 default 'id name age')  
is  
  cursor cur\_all is select \* from student;  --定义游标 cur\_all  
  cur\_line student%rowtype;                 --定义rowtype  
begin  
dbms\_output.put\_line(var1);  
  for cur\_line in cur\_all loop              --循环处理，隐式打开游标  
    dbms\_output.put\_line(cur\_line.id||' '||cur\_line.name||' '||cur\_line.age); --隐式执行一个fetch  
  end loop;  
\--隐式关闭游标  
end;  
/

\--带入参(IN)的存储过程，用 bulk collect 批量检索，效率更高  
create or replace procedure testproc2(var1 in varchar2 default 'id name age')  
is  
  cursor cur\_all is select \* from student;      --定义游标 cur\_all  
  type student\_tab is table of student%rowtype; --定义类型 student\_tab  
  student\_rd student\_tab;                       --定义变量 student\_rd  
begin  
  dbms\_output.put\_line(var1);  
  open cur\_all;  
  loop                   --循环处理，隐式打开游标  
    fetch cur\_all bulk collect into student\_rd limit 500;  
      for i in 1..student\_rd.count loop             --循环处理  
        dbms\_output.put\_line(student\_rd(i).id||' '||student\_rd(i).name||' '||student\_rd(i).age);  
      end loop;  
    exit when cur\_all%NOTFOUND;       --循环退出条件：最近的FETCH语句返回一行数据则为FALSE  
  end loop;  
  close cur\_all;  
end;  
/

\--带出参(OUT)的存储过程，简单样例  
create or replace procedure testproc2(ret out varchar2)  
is  
begin  
  ret := 'test002';  
end;  
/

declare  
ret varchar2(255):='test001';  
begin  
  dbms\_output.put\_line(ret); --ret 原值  
  testproc2(ret);  
  dbms\_output.put\_line(ret); --ret 新值  
end;  
/

输出结果：  
test001  
test002

\--带入参(IN)、出参(OUT)的存储过程，用"for in"使用cursor(游标)  
create or replace procedure testproc2(var1 in varchar2 default 'id name age', var2 out varchar2)  
is  
  cursor cur\_all is select \* from student;  --定义游标 cur\_all  
  cur\_line student%rowtype;                 --定义rowtype  
begin  
  dbms\_output.put\_line(var1);  
  for cur\_line in cur\_all loop              --循环处理，隐式打开游标  
    --dbms\_output.put\_line(cur\_line.id||' '||cur\_line.name||' '||cur\_line.age); --隐式执行一个fetch  
    var2 := var2||cur\_line.id||' '||cur\_line.name||' '||cur\_line.age||chr(13)||chr(10);  
  end loop;  
\--隐式关闭游标  
end;  
/

执行存储过程  
declare  
  ret varchar2(8000);  
begin  
  testproc2('id name age', ret);  
  dbms\_output.put\_line(ret);  
end;  
/

结果如下：

id name age  
1 jack 20  
2 lucy 21  
3 happy 20  
4 white 21