oracle pl/sql 小知识

1. procedure 或者 function 中 对 null 和 ” 的判断

    CREATE OR REPLACE PROCEDURE TEST(PARA IN VARCHAR2)
    IS
    BEGIN
        IF PARA = '' THEN
            DBMS_OUTPUT.PUT_LINE('I AM '''' !');
        ELSIF PARA IS NULL THEN
            DBMS_OUTPUT.PUT_LINE('I AM NULL');
        ELSE
            DBMS_OUTPUT.PUT_LINE('NO TEST');
        END IF;
    END;

测试结果：

exec TEST('');
I AM NULL

PL/SQL procedure successfully completed.

SQL> exec TEST(' ');
NO TEST

PL/SQL procedure successfully completed.

结论：
PARA = ” 没有什么实际效果，如果是赋值还可，比较就算了

2. 变量赋值

请使用 := 而不是 =

   CREATE OR REPLACE PROCEDURE TEST2(OPER_TYPE IN VARCHAR2)
    IS
     v_staff_str varchar2(50) ;
    BEGIN
         v_staff_str := ' and staff_id= 100000 '  ;
        DBMS_OUTPUT.PUT_LINE(v_staff_str||' 1');
    END;   

    CREATE OR REPLACE PROCEDURE TEST2(OPER_TYPE IN VARCHAR2)
    IS
     v_staff_str varchar2(50) := ' and staff_id= 100000 '  ;
    BEGIN
        -- v_staff_str  ;
        DBMS_OUTPUT.PUT_LINE(v_staff_str||' 1');
    END;   

    CREATE OR REPLACE PROCEDURE TEST2(OPER_TYPE IN VARCHAR2)
    IS
    BEGIN
        -- v_staff_str  ;
         declare v_staff_str varchar2(50)  ;
        begin
         v_staff_str := ' and staff_id = 100000 ' ;
        DBMS_OUTPUT.PUT_LINE(v_staff_str||' 1');
        end;
    END;   

    CREATE OR REPLACE PROCEDURE TEST2(OPER_TYPE IN VARCHAR2)
    IS
    BEGIN
        -- v_staff_str  ;
         declare v_staff_str varchar2(50) := ' and staff_id = 100000 '  ;
        begin
        DBMS_OUTPUT.PUT_LINE(v_staff_str||' 1');
        end;
    END;   

3.传入参数和表列名相同

传入参数将会被覆盖

CREATE OR REPLACE PROCEDURE TEST2(OPER_TYPE IN VARCHAR2)
    IS
     v_staff_str    varchar2(50);
    BEGIN
        DBMS_OUTPUT.PUT_LINE(OPER_TYPE||' 1');
        insert into tmp_s 
        select OPER_TYPE from table1 log
        inner join table2 staff  staff.staff_id=log.staff_id
        where log.seqid=37968813;
    END;

测试结果：

SQL> exec TEST2(‘haha2’);
haha2 1

PL/SQL procedure successfully completed.

SQL> select * from tmp_s;

STAFF_CODE
---------------
hehe
haha
3PD

结论：
haha2 没有插入，被表列覆盖