oracle杂记_PLSQL

-- 输出
DECLARE
ret VARCHAR2(20);

BEGIN
    ret := 'hello world!';
    DBMS_OUTPUT.PUT_LINE(ret);
END;


-- select...into
DECLARE
ret VARCHAR2(20);

BEGIN
    select "ts_code" INTO ret from "code" where "f1"=1;
    DBMS_OUTPUT.PUT_LINE(ret);
END;


-- 常量
DECLARE
    PI CONSTANT NUMBER := 3.14;
    R NUMBER DEFAULT 3; 
    AREA NUMBER;
BEGIN
  AREA := PI * R * R;
  DBMS_OUTPUT.PUT_LINE(AREA);
END;


-- 类型
DECLARE
t_col "code"."ts_code"%TYPE;
t_row "code"%ROWTYPE;

type tp_myrd is record
(
    v_code VARCHAR2(10),
    v_close NUMBER(10,2)
);
myrd tp_myrd;

BEGIN
select "ts_code" into t_col from "code" where ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE(t_col);

select * into t_row from "code" where ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE(t_row."ts_code");

select "ts_code","close" into myrd from "code" where ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE(myrd.v_code);
END;


--- 整列赋值
--- 多行多列赋值 bulk collect
declare
type tp_code is table of "lday"."ts_code"%type;
type tp_date is table of "lday"."trade_date"%type;

code tp_code;
dates tp_date := tp_date();

BEGIN
    --- 内存不受限制
    DBMS_OUTPUT.ENABLE(buffer_size => null);
    
    select "ts_code","trade_date" bulk collect into code,dates from "lday" where "trade_date" > TO_DATE('2020', 'yyyy');
    
    -- for idx in 1..code.count loop
    for idx in code.first..code.last loop
        dbms_output.put_line(code(idx));
        dbms_output.put_line(dates(idx));
    end loop;
END;


--- 整行赋值
declare
type tp_lday is table of "lday"%rowtype index by binary_integer;
row_lday tp_lday;

BEGIN
    DBMS_OUTPUT.ENABLE(buffer_size => null);

    select * bulk collect into row_lday from "lday" where "trade_date" > TO_DATE('2020', 'yyyy');

    for idx in 1..row_lday.count loop
        dbms_output.put_line(row_lday(idx)."ts_code");
    end loop;
END;


--- IF语句
DECLARE
    ret NUMBER DEFAULT 100;
BEGIN
    IF ret > 100 THEN
        DBMS_OUTPUT.PUT_LINE(200); 
    ELSIF ret < 100 THEN
        DBMS_OUTPUT.PUT_LINE(0); 
    ELSE
        DBMS_OUTPUT.PUT_LINE(100);
    END IF;
END;


--- CASE
DECLARE
    ret NUMBER DEFAULT 100;
BEGIN
    CASE ret
    WHEN 100 THEN
        DBMS_OUTPUT.PUT_LINE(100); 
    WHEN 200 THEN
        DBMS_OUTPUT.PUT_LINE(200); 
    ELSE
        DBMS_OUTPUT.PUT_LINE(300);
    END CASE;
END;
--
DECLARE
    ret NUMBER DEFAULT 100;
BEGIN
    CASE
    WHEN ret<100 THEN
        DBMS_OUTPUT.PUT_LINE(100); 
    WHEN ret>200 THEN
        DBMS_OUTPUT.PUT_LINE(200); 
    ELSE
        DBMS_OUTPUT.PUT_LINE(300);
    END CASE;
END;
--
select 
    case 
        when "pct_chg" between 1 and 3 then 'low'
        when "pct_chg" between 4 and 6 then 'low'
        when "pct_chg" between 7 and 11 then 'low'
        else 'other'
    end 
from "code"


-- LOOP
DECLARE
    cnt NUMBER(3) DEFAULT 0;
    sumret NUMBER(5) DEFAULT 0;
BEGIN
    LOOP
        cnt := cnt+1;
        sumret := sumret+cnt;
        DBMS_OUTPUT.PUT_LINE(sumret);
        --EXIT WHEN cnt >=100;
        IF cnt >= 100 THEN
            EXIT;
        END IF;
    END LOOP;
END;


-- WHILE
DECLARE
    cnt NUMBER(3) DEFAULT 0;
    sumret NUMBER(5) DEFAULT 0;
BEGIN
    WHILE cnt < 100 LOOP
        cnt := cnt+1;
        sumret := sumret+cnt;
        DBMS_OUTPUT.PUT_LINE(sumret);
    END LOOP;
END;


--FOR
BEGIN
    FOR cnt IN 1..100 LOOP
        DBMS_OUTPUT.PUT_LINE(cnt);
    END LOOP;
END;


--游标
--属性 %FOUND | %NOTFOUND
DECLARE
    CURSOR c_code IS
        SELECT "ts_code","trade_date","close" FROM "code" WHERE "trade_date" > TO_DATE('20191001','yyyymmdd');
    t_row c_code%ROWTYPE;
    ret VARCHAR2(255);
BEGIN
    OPEN c_code;  
    LOOP
        FETCH c_code INTO t_row;
        EXIT WHEN c_code%NOTFOUND;
        ret := t_row."ts_code"||' in '||TO_CHAR(t_row."trade_date",'yyyy/mm/dd')||' close is '||t_row."close";
        DBMS_OUTPUT.PUT_LINE(ret);
    END LOOP;
    CLOSE c_code;
END;

---
DECLARE
    CURSOR c_code IS
        SELECT "ts_code","trade_date","close" FROM "code" WHERE "trade_date" > TO_DATE('20191001','yyyymmdd');
    t_row c_code%ROWTYPE;
    ret VARCHAR2(255);
BEGIN
    FOR row in c_code LOOP
        ret := row."ts_code"||' in '||TO_CHAR(row."trade_date",'yyyy/mm/dd')||' close is '||row."close";
        DBMS_OUTPUT.PUT_LINE(ret);
    END LOOP;
END;


--动态SQL
EXECUTE IMMEDIATE 动态语句字符串
[INTO 变量列表]：把查询的结果保存到INTO后面的变量中
[USING 参数列表]：USING为语句中的参数传值，格式是[:参数名]

BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE YYY AS SELECT * FROM EMP';
END;


-- 存储过程
-- 创建
CREATE OR REPLACE PROCEDURE SP_MYPRO(
    P_f1 IN NUMBER,
    P_ts_code OUT VARCHAR2
) AS
V_close NUMBER;
BEGIN
    SELECT "ts_code","close" INTO P_ts_code,V_close FROM "code"
    WHERE "f1"=P_f1;
    DBMS_OUTPUT.PUT_LINE(P_ts_code||' close is '||V_close);
END;
--调用
DECLARE
    P_ts_code VARCHAR2(10);
BEGIN
    SP_MYPRO(1, P_ts_code);
END;
--删除
DROP PROCEDURE SP_MYPRO;


-- 自定义函数（可以在查询语句中直接调用）
CREATE OR REPLACE FUNCTION FC_MYFUN(p1 IN NUMBER)
RETURN NUMBER 
AS 
ret NUMBER;
BEGIN
    ret := p1 + p1;
    RETURN ret;
END FC_MYFUN;
--调用
BEGIN
    DBMS_OUTPUT.PUT_LINE(FC_MYFUN(10));
END;
--删除
DROP FUNCTION FC_MYFUN;