procedure概述
存储过程（ Stored Procedure ）是一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中。
用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
存储过程是由流控制和 SQL 语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，应用程序使用时只要调用即可。
在Oracle 中，若干个有联系的过程可以组合在一起构成程序包。
procedure优点
存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
当对数据库进行复杂操作时(如对多个表进行 Update、Insert、Query、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
存储过程可以重复使用,可减少数据库开发人员的工作量。
安全性高,可设定只有某用户才具有对指定存储过程的使用权。
和function的区别
procedure例子
CREATE OR REPLACE procedure proc_trade(
v_tradeid in number, --交易id
v_third_ip in varchar2, --第三方ip
v_third_time in date, --第三方完成时间
v_thire_state in number, --第三方状态
o_result out number, --返回值
o_detail out varchar2 --详细描述
) as
-- 定义变量
v_error varchar2(500);
begin
--对变量赋值
o_result := 0;
o_detail := '验证失败';
--业务逻辑处理
if v_tradeid > 100 then
insert into table_name (.. .) values (.. .);
commit;
elsif v_tradeid < 100 and v_tradeid > 50 then
insert into table_name (.. .) values (.. .);
commit;
else
goto log;
end if;
--跳转标志符，名称自己指定
<<log>>
o_result := 1;
--捕获异常
exception
when no_data_found then
result := 2;
when dup_val_on_index then
result := 3;
when others then
result := -1;
end proc_trade;
参数类型可以自己指定，这种写法可行，但是最好使用%type 来获取参数的类型(table_name.column_name%TYPE)。 这样就不会出现参数类型的错误。
存储过程中的循环
for … in … loop 循环
循环遍历游标
示例1：
CREATE OR REPLACE PROCEDURE proc_test AS
CURSOR c1 IS
SELECT * FROM dat_trade;
BEGIN
FOR x IN c1 LOOP
DBMS_OUTPUT.put_line(x.id);
END LOOP;
END proc_test;
示例 2：
CREATE OR REPLACE PROCEDURE proc_test AS
BEGIN
FOR x IN (SELECT power_id FROM sys_power) LOOP
DBMS_OUTPUT.put_line(x.power_id);
END LOOP;
END proc_test;
根据数值进行循环
栗子一
CREATE OR REPLACE PROCEDURE proc_test() AS
BEGIN
for x in 1 .. 100 loop
dbms_output.put_line(x);
end loop;
END proc_test;
栗子2：在过程里指定输入参数 v_num. 在调用过程时指定循环次数。
CREATE OR REPLACE PROCEDURE proc_test(v_num IN NUMBER) AS
BEGIN
FOR x IN 1 .. v_num LOOP
DBMS_OUTPUT.put_line(x);
END LOOP;
END proc_test;
loop 循环
LOOP
DELETE FROM orders
WHERE senddate < TO_CHAR (ADD_MONTHS (SYSDATE, -3),
'yyyy-mm-dd')
AND ROWNUM < 1000;
EXIT WHEN SQL%ROWCOUNT < 1;
COMMIT;
END LOOP;
这 里 的 SQL%ROWCOUNT 是 隐 士 游 标 。 除 了 这 个 ， 还 有 其 他 几
个： %found， %notfound， %isopen。
while 循环
CREATE OR REPLACE PROCEDURE proc_test(v_num IN NUMBER)
AS
i NUMBER := 1;
BEGIN
WHILE i < v_num LOOP
BEGIN
i := i + 1;
DBMS_OUTPUT.put_line(i);
END;
END LOOP;
END proc_test;
存储过程中的判断
if … elsif … else … 判断
CREATE OR REPLACE PROCEDURE proc_test(v_num IN NUMBER) AS
BEGIN
IF v_num < 10 THEN
DBMS_OUTPUT.put_line(v_num);
ELSIF v_num > 10 AND v_num < 50 THEN
DBMS_OUTPUT.put_line(v_num - 10);
ELSE
DBMS_OUTPUT.put_line(v_num - 50);
END IF;
END proc_test;
case … when … end case 判断
CREATE OR REPLACE PROCEDURE proc_test(v_num IN NUMBER) AS
BEGIN
case v_num
when 1 then
DBMS_OUTPUT.put_line(v_num);
when 2 then
DBMS_OUTPUT.put_line(v_num);
when 3 then
DBMS_OUTPUT.put_line(v_num);
else
null;
end case;
END proc_test;
游标
之前整理的游标的知识
Cursor 型游标(不能用于参数传递)
CREATE OR REPLACE PROCEDURE proc_test AS
CURSOR c1 IS
SELECT * FROM dat_trade;
BEGIN
FOR x IN c1 LOOP
DBMS_OUTPUT.put_line(x.id);
END LOOP;
END proc_test;
SYS_REFCURSOR 型游标
该游标是 Oracle 预先定义的游标，可作出参数进行传递。
SYS_REFCURSOR 只能通过 OPEN 方法来打开和赋值
我们可以使用这种类似的游标来返回一个结果集：
CREATE OR REPLACE procedure proc_test(
checknum in number, --每次返回的数据量
ref_cursor out sys_refcursor --返回的结果集，游标
) as
begin
open ref_cursor for
select *
from (select * from dat_trade where state = 41 order by id)
where rownum < checknum;
end proc_test;
SYS_REFCURSOR 中可使用四个状态属性：
（ 1） . %NOTFOUND(未找到记录信息)
（ 2） . %FOUND(找到记录信息)
（ 3） . %ROWCOUNT(然后当前游标所指向的行位置)
（4）. %ISOPEN（是否打开）
CREATE OR REPLACE PROCEDURE proc_test(
checknum IN NUMBER, --每次返回的数据量
ref_cursor OUT sys_refcursor --返回的结果集，游标
) AS
t_tmp table_name%ROWTYPE;
BEGIN
OPEN ref_cursor FOR
SELECT *
FROM (SELECT * FROM table_name WHERE state = 41 ORDER BY id)
WHERE ROWNUM < checknum;
--循环游标
LOOP
FETCH ref_cursor
INTO t_tmp;
EXIT WHEN ref_cursor%NOTFOUND;
-- DBMS_OUTPUT.put_line (t_tmp.id);
UPDATE table_name SET state = 53 WHERE id = t_tmp.id;
COMMIT;
END LOOP;
CLOSE ref_cursor;
END proc_test;