Oracle傳入SP參數，返回Sql

前輩寫的，不知出處

SP需要的package以及SP

CREATE OR REPLACE PACKAGE MES1.MYTEST_PACKAGE
IS
   TYPE TESTCURSOR IS REF CURSOR;
END MYTEST_PACKAGE;
/

 
CREATE OR REPLACE PROCEDURE MES1.QUERY (
   var_trantype      IN     VARCHAR2,
   var_fixturetype   IN     VARCHAR2,
   var_fixturesn     IN     VARCHAR2,
   var_mfrsn         IN     VARCHAR2,
   var_location      IN     VARCHAR2,
   var_version       IN     VARCHAR2,
   var_stock         IN     VARCHAR2,
   var_qty           IN     VARCHAR2,
   var_emp           IN     VARCHAR2,
   var_line          IN     VARCHAR2,
   var_station       IN     VARCHAR2,
   var_wo            IN     VARCHAR2,
   var_loginemp      IN     VARCHAR2,
   var_shift         IN     VARCHAR2,
   var_dutyarea      IN     VARCHAR2,
   var_defectcode    IN     VARCHAR2,
   var_data1         IN     VARCHAR2,
   var_data2         IN     VARCHAR2,
   var_data3         IN     VARCHAR2,
   user_cusor           OUT mytest_package.testcursor)
AS
   l_exit        EXCEPTION;
   l_o_message   VARCHAR2 (300);
   l_strquery    VARCHAR2 (30000);
   l_res_start   VARCHAR2 (500);
   l_res_end     VARCHAR2 (1000);
   l_count       NUMBER;
BEGIN
   IF UPPER (var_trantype) = 'SHOW_BY'
   THEN
      --  分頁統計參數
      --  var_line        TOTAL  判斷條件
      --  var_station     rows
      --  var_wo          page
      --  l_res_start
      --  l_res_end

      IF var_data3 = 'TOTAL'
      THEN
         l_res_start := 'select count(*) total  from (';
         l_res_end := ' )';
      ELSIF var_data3 = 'DOWNLOAD'
      THEN
         l_res_start := '';
         l_res_end := ''; 

      ELSE
         l_res_start :=
            '
        SELECT  *
          FROM (SELECT *
                  FROM (
                  SELECT ROW_NUMBER () OVER (ORDER BY fixture_no) AS rownumber, t.*
                          FROM (    ';
         l_res_end :=
               ' 
                        ) t
                           ) p
                 WHERE p.rownumber > '
            || var_wo * var_station
            || ')
         WHERE ROWNUM <= '
            || var_station;
      END IF;

      l_strquery := ' select * from mes4.r_fixture_online_detail  ';

      l_strquery := l_res_start || l_strquery || l_res_end;

      OPEN user_cusor FOR l_strquery;
   END IF;

   DBMS_OUTPUT.put_line (l_strquery);
   COMMIT;
   RETURN;
EXCEPTION
   WHEN l_exit
   THEN
      ROLLBACK;
      l_o_message := 'ERROR-' || l_o_message;
      l_strquery :=
            'SELECT '''
         || l_o_message
         || ''' AS STRRESULT FROM DUAL WHERE ROWNUM=1';

      OPEN user_cusor FOR l_strquery;
   WHEN OTHERS
   THEN
      ROLLBACK;
      l_o_message := 'ERROR-' || l_o_message;
      l_o_message :=
            l_o_message
         || SQLERRM
         || CHR (10)
         || SUBSTR (DBMS_UTILITY.format_error_backtrace, 1, 1000)
         || ',SYSTEM ERROR IN [MES1.FIXTURE_QUERY]';
      --獲取異常信息及異常拋出的最后位置 
      l_strquery :=
            'SELECT '''
         || l_o_message
         || ''' AS STRRESULT FROM DUAL WHERE ROWNUM=1';

      DBMS_OUTPUT.put_line (l_o_message);

      OPEN user_cusor FOR l_strquery;
END;
/