本文主要是介绍Oracle procedure 存储过程，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
Oracle procedure 存储过程
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
这篇关于Oracle procedure 存储过程的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
