Oracle中几个需要用动态语句执行的情况

Oracle中几个需要用动态语句执行的情况

1.一般的在存储过程或函数中 
要执行一个SQL数据定义语句(例如：create   table  ..,alter   table  ,drop  table); 
或 执 行一 个 数据控制語句（例如：grant...）; 
或一个会话控制（例如：alter  session）; 
时要使用动态语句； 
2.得到更多的灵活性，例如可能要在select语句中的where子句创建不同的搜索条件等等。

execute immediate"sql语句";

也可以使用dbms_sql执行动态语句。

--------------------------------------------------------------------------------------------------

一个存储过程的例子：

create orreplace procedure PRO_RECEIPTSTOCK_5D19A IS

v_STOCKID NUMBER(13);

v_STARTNO VARCHAR2(10);

v_ENDNO VARCHAR2(10);

v_RECEIPTTYPE NUMBER(13);

v_STATUS CHAR(1);

v_COUNT NUMBER(13);

v_UNITID NUMBER(13);

V_RELATEUSERNAME VARCHAR2(150);

V_GLIDENUM NUMBER(20);

V_FLAG VARCHAR2(2);

V_PROCTM DATE;

V_SPROCTM VARCHAR2(30);

V_REGISTERTM DATE;

V_SREGISTERTM VARCHAR2(30);

V_DBUSER VARCHAR2(20);

V_CURSQL VARCHAR2(4000);

V_MYCOUNT NUMBER;

CURSOR C_RECEIPTSTOCK_TMP IS

SELECT

STOCKID,

STARTNO,

ENDNO,

RECEIPTTYPE,

STATUS,

COUNT,

UNITID,

RELATEUSERNAME,

GLIDENUM,

FLAG,

PROCTM,

REGISTERTM,

DBUSER

FROM GFMIS_ALL.RECEIPTSTOCK_5D19A_TMP

WHERE

ZTOF_STATUS = '1'

AND DBUSER IS NOT NULL;

begin

  --把票据类型为5D的缴款书在库存中更新成一般缴款书

  UPDATE GFMIS_ALL.RECEIPTSTOCK T SETT.RECEIPTTYPE=4765 WHERE EXISTS (SELECT 1 FROMGFMIS_ALL.RECEIPTSTOCK_5D19A_TMP TT WHERE TT.STOCKID =substr(T.STOCKID, 4, length(TRIM(T.STOCKID))) AND TT.ZTOF_STATUS ='1' AND TT.RECEIPTTYPE=11533);

  OPEN C_RECEIPTSTOCK_TMP;

  LOOP

    FETCH C_RECEIPTSTOCK_TMPINTO v_STOCKID,

                                v_STARTNO,

                                v_ENDNO,

                                v_RECEIPTTYPE,

                                v_STATUS,

                                v_COUNT,

                                v_UNITID,

                                V_RELATEUSERNAME,

                                V_GLIDENUM,

                                V_FLAG,

                                V_PROCTM,

                                V_REGISTERTM,

                                V_DBUSER;

    EXIT WHENC_RECEIPTSTOCK_TMP%NOTFOUND;

    V_CURSQL := 'SELECT COUNT(1) FROM ' || V_DBUSER ||'.RECEIPTSTOCK WHERE STOCKID = ' || v_STOCKID;

    EXECUTE IMMEDIATE V_CURSQL INTO V_MYCOUNT;

    IF (V_MYCOUNT = 0)THEN

       

          V_SPROCTM:=TO_CHAR(V_PROCTM,'YYYY-MM-DD HH24:MI:SS');

          V_SREGISTERTM:=TO_CHAR(V_REGISTERTM,'YYYY-MM-DD HH24:MI:SS');

          IF (v_RECEIPTTYPE = 11533)THEN

              v_RECEIPTTYPE := 4765;

          END IF;

           

          V_CURSQL :='INSERT INTO ' ||V_DBUSER || '.RECEIPTSTOCK(STOCKID,STARTNO,ENDNO,RECEIPTTYPE,STATUS,COUNT,UNITID,RELATEUSERNAME,GLIDENUM,FLAG,PROCTM,REGISTERTM)VALUES (' || v_STOCKID || ',''' || v_STARTNO || ''',''' || v_ENDNO|| ''',' || v_RECEIPTTYPE || ',''' || v_STATUS || ''',' || v_COUNT|| ',' || 1 || ',''' || V_RELATEUSERNAME || ''',' ||NVL(V_GLIDENUM, 0) || ',''' || V_FLAG || ''', TO_DATE(''' ||V_SPROCTM || ''',''YYYY-MM-DD HH24:MI:SS''), TO_DATE(''' ||V_SREGISTERTM || ''',''YYYY-MM-DD HH24:MI:SS''))';

          EXECUTE IMMEDIATEV_CURSQL;

          UPDATEGFMIS_ALL.RECEIPTSTOCK_5D19A_TMP T SET T.ZTOF_STATUS = '0' WHERET.STOCKID = v_STOCKID AND T.ZTOF_STATUS = '1';

          DELETE FROMGFMIS.RECEIPTSTOCK WHERE STOCKID = v_STOCKID;

       

    END IF;

  END LOOP;

  UPDATE GFMIS_ALL.RECEIPTSTOCK_5D19A_TMP TSET T.ZTOF_STATUS = '0' WHERE T.ZTOF_STATUS = '1' ANDT.RECEIPTTYPE=11533;

  COMMIT;

  EXCEPTION

    WHEN NO_DATA_FOUND THEN

      NULL;

    WHEN OTHERS THEN

      CLOSEC_RECEIPTSTOCK_TMP;

       dbms_output.put_line(SQLERRM);

        IFC_RECEIPTSTOCK_TMP%ISOPEN THEN

          CLOSE C_RECEIPTSTOCK_TMP;

        ENDIF;

      RAISE;

end PRO_RECEIPTSTOCK_5D19A;

--------------------------------------------------------------------------------------------------

一个触发器的例子：

CREATE OR REPLACE TRIGGER TRI_RECEIPTSTOCK_CHG_AFT

AFTER DELETE OR INSERT OR UPDATE

ON RECEIPTSTOCK

REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW

DECLARE

curTable varchar2(50);

curType char(1);

curTime TimeStamp;

id number(13);

curID number(13);

curSQL varchar2(4000);

curUser varchar2(30);

v_distno varchar2(30);

v_dbuser varchar2(30);

v_mycount number(13);

BEGIN

IF ( INSERTING)THEN 

      --把票据类型为5D的缴款书在库存中更新成一般缴款书 或者把指定的19个单位的库存移到分库里面

      SELECT COUNT(1) INTO v_mycount FROM GFMIS_ALL.RECEIPTSTOCK_DISTRICTT WHERE T.UNITID = :NEW.UNITID;

      IF(v_mycount > 0) THEN 

          SELECT T.DBUSER INTO v_dbuserFROM GFMIS_ALL.RECEIPTSTOCK_DISTRICT T WHERE T.UNITID =:NEW.UNITID;

         

          curSQL := 'SELECT COUNT(1)FROM ' || v_dbuser || '.RECEIPTSTOCK WHERE UNITID = ' ||substr(:NEW.UNITID, 4, length(TRIM(:NEW.UNITID))) || ' ANDRECEIPTTYPE = ' || :NEW.RECEIPTTYPE || ' AND ENDNO = ''' ||:NEW.ENDNO || '''';  --注意结尾的这个地方单引号个数

          EXECUTE IMMEDIATE curSQL INTOv_mycount;

          if (v_mycount = 0) then

            INSERTINTO GFMIS_ALL.RECEIPTSTOCK_5D19A_TMP(STOCKID,STARTNO,ENDNO,RECEIPTTYPE,STATUS,COUNT,UNITID,RELATEUSERNAME,GLIDENUM,FLAG,PROCTM,REGISTERTM,DBUSER,ZTOF_STATUS)VALUES (substr(:NEW.STOCKID, 4,length(TRIM(:NEW.STOCKID))),:NEW.STARTNO,:NEW.ENDNO,:NEW.RECEIPTTYPE,:NEW.STATUS,:NEW.COUNT,substr(:NEW.UNITID,4,length(TRIM(:NEW.UNITID))),:NEW.RELATEUSERNAME,:NEW.GLIDENUM,:NEW.FLAG,:NEW.PROCTM,:NEW.REGISTERTM,v_dbuser,'1'); 

          end if;

         

           

      ENDIF;

       

      IF(:NEW.RECEIPTTYPE = 11533) THEN

        INSERT INTO GFMIS_ALL.RECEIPTSTOCK_5D19A_TMP(STOCKID,STARTNO,ENDNO,RECEIPTTYPE,STATUS,COUNT,UNITID,RELATEUSERNAME,GLIDENUM,FLAG,PROCTM,REGISTERTM,DBUSER,ZTOF_STATUS)VALUES (substr(:NEW.STOCKID, 4,length(TRIM(:NEW.STOCKID))),:NEW.STARTNO,:NEW.ENDNO,:NEW.RECEIPTTYPE,:NEW.STATUS,:NEW.COUNT,substr(:NEW.UNITID,4,length(TRIM(:NEW.UNITID))),:NEW.RELATEUSERNAME,:NEW.GLIDENUM,:NEW.FLAG,:NEW.PROCTM,:NEW.REGISTERTM,NULL,'1');

      ENDIF;

    END IF;

   

    selectseq_exchange_temp.nextval into id from dual;

    curUser := lower( SYS_CONTEXT(' userenv', ' session_user'));

    if (curUser !='all_exchange_user') then

        SELECT CURRENT_TIMESTAMP INTO curTime FROMDUAL;

        curTable := 'RECEIPTSTOCK';

        if (deleting) then

            v_distno:=substr(:OLD.STOCKID,2,2);

            selectdbuser into v_dbuser from GFMIS_ALL.DISTRICT wheredistno=v_distno;

            curType :='3';

            curID :=:OLD.stockid;

    if ( curUser = ' gfmis_all') then

            curSQL :='SP_EX_RECEIPTSTOCK_DEL(''' || :OLD.STOCKID || ''')';

    else

            curSQL :='SP_EX_RECEIPTSTOCK_DEL(''' ||  substr(:OLD.STOCKID,4) || ''')';

    end if;

        end if;

        if (updating) then

            v_distno:=substr(:OLD.STOCKID,2,2);

            selectdbuser into v_dbuser from GFMIS_ALL.DISTRICT wheredistno=v_distno;

            curType :='2';

            curID :=:OLD.stockid;

    if (curUser ='gfmis_all') then

            curSQL :='SP_EX_RECEIPTSTOCK_UPD(''' || :OLD.STOCKID || ''', ''' ||:NEW.COUNT || ''', ''' || FN_FMT_SQL(:NEW.ENDNO) || ''', ''' ||FN_FMT_SQL(:NEW.FLAG) || ''', ''' || :NEW.GLIDENUM || ''', ''' ||TO_CHAR(:NEW.PROCTM,'YYYY-MM-DD HH24:MI:SS') || ''', ''' ||:NEW.RECEIPTTYPE || ''', ''' || TO_CHAR(:NEW.REGISTERTM,'YYYY-MM-DDHH24:MI:SS') || ''', ''' || FN_FMT_SQL(:NEW.RELATEUSERNAME) || ''',''' || FN_FMT_SQL(:NEW.STARTNO) || ''', ''' ||FN_FMT_SQL(:NEW.STATUS) || ''', ''' || :NEW.STOCKID || ''', ''' ||:NEW.UNITID ||   ''')';

    else

            curSQL :='SP_EX_RECEIPTSTOCK_UPD(''' ||  substr(:OLD.STOCKID,4) || ''', ''' || :NEW.COUNT|| ''', ''' || FN_FMT_SQL(:NEW.ENDNO) || ''', ''' ||FN_FMT_SQL(:NEW.FLAG) || ''', ''' || :NEW.GLIDENUM || ''', ''' ||TO_CHAR(:NEW.PROCTM,'YYYY-MM-DD HH24:MI:SS') || ''', ''' ||:NEW.RECEIPTTYPE || ''', ''' || TO_CHAR(:NEW.REGISTERTM,'YYYY-MM-DDHH24:MI:SS') || ''', ''' || FN_FMT_SQL(:NEW.RELATEUSERNAME) || ''',''' || FN_FMT_SQL(:NEW.STARTNO) || ''', ''' ||FN_FMT_SQL(:NEW.STATUS) || ''', ''' ||  substr(:NEW.STOCKID ,4) || ''', ''' ||  substr(:NEW.UNITID ,4) ||  ''')';

    end if;

        end if;

        if (inserting) then

            v_distno:=substr(:NEW.STOCKID,2,2);

            selectdbuser into v_dbuser from GFMIS_ALL.DISTRICT wheredistno=v_distno;

            curType :='1';

        curID := :NEW.stockid;

    if (curUser ='gfmis_all') then

            curSQL :='SP_EX_RECEIPTSTOCK_INS(''' || :NEW.COUNT || ''', ''' ||FN_FMT_SQL(:NEW.ENDNO) || ''', ''' || FN_FMT_SQL(:NEW.FLAG) || ''',''' || :NEW.GLIDENUM || ''', ''' || TO_CHAR(:NEW.PROCTM,'YYYY-MM-DDHH24:MI:SS') || ''', ''' || :NEW.RECEIPTTYPE || ''', ''' ||TO_CHAR(:NEW.REGISTERTM,'YYYY-MM-DD HH24:MI:SS') || ''', ''' ||FN_FMT_SQL(:NEW.RELATEUSERNAME) || ''', ''' ||FN_FMT_SQL(:NEW.STARTNO) || ''', ''' || FN_FMT_SQL(:NEW.STATUS) ||''', ''' || :NEW.STOCKID || ''', ''' || :NEW.UNITID ||  ''')';

    else

            curSQL :='SP_EX_RECEIPTSTOCK_INS(''' || :NEW.COUNT || ''', ''' ||FN_FMT_SQL(:NEW.ENDNO) || ''', ''' || FN_FMT_SQL(:NEW.FLAG) || ''',''' || :NEW.GLIDENUM || ''', ''' || TO_CHAR(:NEW.PROCTM,'YYYY-MM-DDHH24:MI:SS') || ''', ''' || :NEW.RECEIPTTYPE || ''', ''' ||TO_CHAR(:NEW.REGISTERTM,'YYYY-MM-DD HH24:MI:SS') || ''', ''' ||FN_FMT_SQL(:NEW.RELATEUSERNAME) || ''', ''' ||FN_FMT_SQL(:NEW.STARTNO) || ''', ''' || FN_FMT_SQL(:NEW.STATUS) ||''', ''' ||   substr(:NEW.STOCKID ,4) || ''', '''||   substr(:NEW.UNITID ,4) ||  ''')';

    end if;

        end if;

    if (curUser ='gfmis_all') then

        INSERT INTO exchange_temp (ID,SEQID, TABLENAME,GENSQL, TYPE, DT, STATUS,ZKSTATUS,DBUSER)

        VALUES (id, curID,curTable, curSQL, curType,curTime, '2','1',v_dbuser );

    else

        INSERT INTO exchange_temp (ID,SEQID, TABLENAME,GENSQL, TYPE, DT, STATUS,ZKSTATUS,DBUSER)

        VALUES (id, curID,curTable, curSQL, curType,curTime, '2','0',v_dbuser );

    end if;

    end if;

    EXCEPTION

        WHEN OTHERS THEN

            RAISE;

end TRI_RECEIPTSTOCK_CHG_AFT;

--------------------------------------------------------------------------------------------------

把单引号替换成双引号的函数：

CREATE OR REPLACE FUNCTION "FN_FMT_SQL" (

  myrrsql in char

)

return char

AS

rtnRctNO varchar2(4000);

BEGIN

  rtnRctNO:=myrrsql;

  if myrrsql is null THEN

    return myrrsql;

  ELSE

    rtnRctNO :=REPLACE(rtnRctNO, '''', '''''');

    return rtnRctNO;

  END IF;

end FN_FMT_SQL;