oracle - 存储过程

oracle 的存储过程，有一定实战价值，oracle 作为曾经的老大哥，有不少老公司会使用它。

如果是新系统，就不要使用了吧，计算交给 Java 程序，数据库做好存储就够了。

测试用函数

首先会遇到的问题：用带 OUT 参数的存储过程，该怎么把值打印到控制台。

-- 基本格式
DECLARE
    P1 NUMBER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE(P1);
END;

-- 声明一个变量，接受 OUT 参数值，并最终打印到控制台
DECLARE
    P1 NUMBER:=0;
BEGIN
    -- 调用存储过程，使用 P1 接受 OUT 参数值
    "ABCDE"(3,P1);
    -- 打印 OUT 参数
    DBMS_OUTPUT.PUT_LINE(P1);
END;

存储过程以及游标的使用

-- 简单的存储过程
CREATE OR REPLACE PROCEDURE ABCDE
    (P1 IN VARCHAR,P2 OUT NUMBER)
AS BEGIN
    SELECT COUNT(0) INTO P2 FROM TABLE1;
END;

-- 游标和变量的简单应用
CREATE OR REPLACE PROCEDURE ABCDE
    (P1 IN VARCHAR,P2 OUT NUMBER)
AS
    -- 游标和变量的定义
    CURSOR CURSOR1 IS SELECT COUNT(0) FROM TABLE1;
    C1 NUMBER;
BEGIN
    OPEN CURSOR1;
    FETCH CURSOR1 INTO P2;
    CLOSE CURSOR1;
END;

-- for 循环遍历游标 
CREATE OR REPLACE PROCEDURE ABCDE
    (P1 IN VARCHAR,P2 OUT NUMBER)
AS
    CURSOR CURSOR1 IS SELECT T_AGE FROM TABLE1;
BEGIN
    P2:=0;
    -- 感觉 for 循环是最适合遍历游标的，打开关闭游标都自动处理了
    FOR CS IN CURSOR1 LOOP
        P2:=P2+CS.T_AGE;
    END LOOP;
END;

-- do...while...循环遍历游标
CREATE OR REPLACE
    PROCEDURE ABCDE
    (P1 IN VARCHAR,P2 OUT NUMBER)
AS
    CURSOR CURSOR1 IS SELECT T_AGE FROM TABLE1;
    C1 NUMBER;
BEGIN
    P2:=0;
    OPEN CURSOR1;

    -- 循环体
    LOOP FETCH CURSOR1 INTO C1;
    EXIT WHEN CURSOR1%NOTFOUND;
    P2:=P2+C1;
    END LOOP;

    CLOSE CURSOR1;
END;


-- while...循环遍历游标
CREATE OR REPLACE
    PROCEDURE ABCDE
    (P1 IN VARCHAR,P2 OUT NUMBER)
AS
    CURSOR CURSOR1 IS SELECT T_AGE FROM TABLE1;
    C1 NUMBER;
BEGIN
    P2:=0;
    OPEN CURSOR1;

    FETCH CURSOR1 INTO C1;
    WHILE CURSOR1%FOUND LOOP
            P2:=P2+C1;
            FETCH CURSOR1 INTO C1;
        END LOOP;

    CLOSE CURSOR1;
END;


-- 游标 ROWCOUNT 的使用
CREATE OR REPLACE
    PROCEDURE ABCDE
    (P1 IN VARCHAR,P2 OUT NUMBER)
AS
    CURSOR CURSOR1 IS SELECT T_AGE FROM TABLE1;
BEGIN
    OPEN CURSOR1;
    LOOP FETCH CURSOR1 INTO P2;
    
    -- ROWCOUNT 类似于 for 循环中的 i
    -- ROWCOUNT 记录了游标的行数，此案例 P2 记录了每一次的行号，因此最后的结果是记录总条数
    P2:=CURSOR1%ROWCOUNT;
    EXIT WHEN CURSOR1%NOTFOUND;
    END LOOP;
    CLOSE CURSOR1;
END;

/**
从游标中取值，%FOUND用于判断本次取值是否有值，进行%FOUND判断可以有效地避免no_data_found的问题；
FETCH直译为取，从游标取值的意思，在打开游标之后，在FETCH之前，%FOUND在程序中一直是false的状态，
要先FETCH，再%FOUND判断。

too_many_rows异常，不仅在存储过程中，在代码中也存在这个问题，需要考虑结果是数组还是一个对象，
代码中通常只取第一个，而游标因为是逐行取值，结合%FOUND判断可以完全避免此异常。
*/

CREATE OR REPLACE
    PROCEDURE ABCDE
    (P2 OUT VARCHAR)
AS
    CURSOR CURSOR1 IS SELECT T.T_AGE FROM(SELECT T_AGE,ROWNUM RNO FROM TABLE1) T WHERE RNO BETWEEN 0 AND 1;
    C1 NUMBER;
BEGIN
    P2:='';
    OPEN CURSOR1;
    -- 不用循环，手动 fetch 多次
    IF CURSOR1%FOUND THEN P2:=P2||'FOUND; ';ELSE P2:=P2||'NOTFOUND; '; END IF;
    FETCH CURSOR1 INTO C1;
    IF CURSOR1%FOUND THEN P2:=P2||'FOUND; ';ELSE P2:=P2||'NOTFOUND; '; END IF;
    FETCH CURSOR1 INTO C1;
    IF CURSOR1%FOUND THEN P2:=P2||'FOUND; ';ELSE P2:=P2||'NOTFOUND; '; END IF;
    CLOSE CURSOR1;
END;