PLSQL(2)

游标                     

[1] 不带参数的游标

-- 取出EMP表中的所有人名字
DECLARE
    CURSOR C IS SELECT * FROM EMP;
    V_EMP C%ROWTYPE;      -- V_EMP : 带有游标C的EMP中所有数据
BEGIN
    OPEN C;    -- 打开游标
        FETCH C INTO V_EMP;  -- 把当前游标指向的数据拿出来
        DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
        CLOSE C;            -- 关闭游标
END;



-- 循环读取
DECLARE
    CURSOR C IS SELECT * FROM EMP;   -- 声明游标
    V_EMP C%ROWTYPE;
BEGIN
    OPEN C；
        LOOP
            FETCH C INTO V_EMP;
            EXIT_WHEN (C%NOTFOUND);
            DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
            END LOOP;
    CLOSE C;
END;

[2] 带参数的游标

DECLARE
    CURSOR C(V_DEPTNO EMP.DEPTNO%TYPE, V_JOB EMP.JOB%TYPE)
    IS
    SELECT ENAME, SAL FROM EMP WHERE DEPTNO = V_DEPTNO AND JOB = V_JOB;
BEGIN
    FOR V_EMP IN C(30, 'CLERK') LOOP
        DBMS_OUTPUT.PUT_LINE(V_TEMP.ENAME);
    END LOOP;
END;

[3] 可更新的游标

DECLARE
    CURSOR C IS SELECT * FROM EMP FOR UPDATE;  -- 只要写for update: 为了更新才使用的游标
BEGIN
    FOR V_TEMP IN C LOOP
        IF(V_TEMP.SAL < 2000) THEN
            UPDATE EMP SET SAL = SAL*2 WHERE CURRENT OF C;  -- CURRENT OF C表示游标C当前位置
        ELSIF(V_TEMP.SAL = 3000) THEN
            DELETE FROM EMP WHERE CURRENT OF C;
        END IF;
    END LOOP;
END;

存储过程                        

[1] 不带参存储过程

-- 存储过程关键字: PROCEDURE    名字: PPP
CREATE OR REPLACE PROCEDURE PPP
IS
    CURSOR C IS SELECT * FROM EMP FOR UPDATE;   -- 为了更新才使用的游标
BEGIN
    FOR V_TEMP IN C LOOP
        IF(V_TEMP.SAL < 2000) THEN
            UPDATE EMP SET SAL = SAL*2 WHERE CURRENT OF C;
        ELSIF(V_TEMP.SAL = 3000) THEN
            DELETE FROM EMP WHERE CURRENT OF C;
        END IF;
    END LOOP;
END;

-- 执行存储过程
BEGIN
    PPP;
END;

[2] 带参的存储过程

 1 -- IN: 传入参数   OUT: 传出参数   IN,OUT: 即可传入也可传出
 2 CREATE OR REPLACE PROCEDURE P(V_A IN NUMBER, V_B  IN NUMBER, V_C OUT NUMBER, V_TEMP IN OUT NUMBER)   
 3 IS
 4 BEGIN
 5     IF(V_A > V_B) THEN
 6         V_C ;= V_A;
 7     ELSE
 8         V_C ;= V_B;
 9     END IF;
10     V_TEMP := V_TEMP + 1;
11 END;
12 
13 -- 调用存储过程
14 DECLARE
15     V_A NUMBER := 3;
16     V_B NUMBER := 4;
17     V_C NUMBER;
18     V_TEMP NUMBER := 5;
19 BEGIN
20     P(V_A, V_B, V_C, V_TEMP);
21     DBMS_OUTPUT.PUT_LINE(V_C);
22     DBMS_OUTPUT.PUT_LINE(V_TEMP);
23 END;

函数                   

-- 关键字： FUNCTION
CREATE OR REPLACE FUNCTION SAL_TAX
    (V_SAL NUMBER)    -- 形参
    RETURN NUMBER    -- 返回类型
IS
BEGIN
    IF(V_SAL < 2000) THEN
        RETURN 0.10;
    ELSIF(V_SAL < 2750) THEN
        RETURN 0.15;
    ELSE
        RETURN 0.20;
    END IF;
END;

-- 使用
SELECT LOWER(ENAME), SAL_TAX(SAL) FROM EMP;

序列                    

-- 序列的名字为SEQ_AA,序列的第一值是1,每次执行增加1;
CREATE SEQUENCE SEQ_AA
START WITH 1
INCREMENT BY 1;

-- 查看当前值
SELECT SEO_AA.CURRENT FROM DUAL;
-- 查看下一个值
SELECT SEQ_AA.NEXTVAL FROM DUAL;

-- 查看序列
SELECT SEQUENCE_NAME FROM USER_SEQUENCE;

-- 删除序列
DROP SEQUENCE SEQ_AA;

-- 序列的值如何在想表中插入数据的时候使用
INSERT INTO AA VALUES (SEQ_AA.NEXTVAL,'色彩');  -- SEQ_AA.NEXTVAL将该序列的值与 '色彩' 栏匹配


-- 序列参数
CREATE SEQUENCE SEQ_IDD
START WITH 100      -- 序列从一百开始
INCREMENT BY -1    -- 每次减一
MAXVALUE 100        -- 序列最大值100
MINVALUE 90          -- 序列最小值90
CYCLE                     -- 缓存为10
CACHE 10;