动态SQL执行的几种方法

①最简单常用的方法

v_sql := '
    INSERT INTO TB1
    (
        COL1,
        COL2,
        COL3,
        COL4
    )
    SELECT COL1
         , COL2
         , COL3
         , ' || '''' || V_NAME ||'''' || '

        , TO_DATE(''' || TO_CHAR(in_hatsurei_ymd, 'YYYYMMDD') || ''', ''YYYYMMDD'')
    FROM (
      SELECT COL1          
           , COL2          
           , COL3          
      FROM   TB2   -- 個人社員区分歴情報
      ) A
      , TB2
    WHERE  A.COL1      = TB2.COL1
    AND    TB2.COL2    = ' || n_num  || '
    ';
EXECUTE IMMEDIATE v_sql;

②

DECLEAR

　　ANK_YMD  CONSTANT varchar2(8) := '20120401';

BEGIN

 v_update_sql := 'INSERT INTO TB1

                     (COL1,
                      COL2)
                    SELECT 
                      'AA',
                      TO_DATE(:ANK_YMD,''YYYYMMDD'')
                       ; 
  EXECUTE IMMEDIATE v_update_sql USING ANK_YMD;

 

③

EXECUTE IMMEDIATE
         'BEGIN '
      || ' :1 := PGB_SAMPLE.FCB_FUN'

      || '(:2,:3); '
      || 'END;'
    USING IN OUT on_rtn,
      in_flg1,
      in_flg2;