【PL/SQL系列】Oracle預存程序使用動態SQL

【PL/SQL系列】Oracle預存程序使用動態SQL

Oracle預存程序相信大家都比較瞭解，下面就為您介紹Oracle預存程序使用動態SQL的方法，希望對您能夠有所協助。

CREATE OR REPLACE PROCEDURE P_STAT_SCORE01
(
      PARA_EXAMGUID VARCHAR2,  -- 考試ID  A9093AE714AC47758A367B8813B99D1D,3216885E3B3148E3904908BD30BF9413,F8254D3E50F64819A996D1E369BBF053
      CUR OUT SYS_REFCURSOR
)
 
/****************************************************************************
名稱：P_STAT_SCORE02
功能描述：根據考試ID,取出班級,考試科目資訊
 
歷程記錄：
版本號碼  編輯時間  編輯人  修改描述
1.0.0    2016-4-7 CHEQNP    1.建立此預存程序,修改動態SQL語句，主要包括變數的使用和WHERE語句的書寫
 
注釋
 
入參出參描述：
PARA_EXAMGUID 考試ID
CUR            輸出遊標
 
*****************************************************************************/
 
AS
    -- 定義變數
    VAR_SQL CLOB;                -- 拼接SQL
    VAR_TEACHER_SQL CLOB;        -- 科任老師
    VAR_COURSENAME VARCHAR(200);  -- 科目名稱
   
BEGIN
 
    VAR_SQL := 'SELECT
              (CASE WHEN CLASSROOM_NAME IS NULL THEN ''合計'' ELSE MIN(CLASSROOM_NAME) END) AS 班級 ';
   
    -- 取應考人數,實考人數
    VAR_SQL := VAR_SQL || ' ,NVL2(CLASSROOM_NAME,
                    (SELECT COUNT(1) FROM TPRG_STUDENT a
                        LEFT JOIN TPRG_CLASSROOM b
                        ON a.CLASSROOM_ID = b.CLASSROOM_ID
                        WHERE b.CLASSROOM_NAME = T.CLASSROOM_NAME ),
                    (SELECT COUNT(1) FROM TPRG_STUDENT
                    WHERE CLASSROOM_ID
                    IN (SELECT CLASSROOM_ID
                    FROM V_FULL_SCORE
                    WHERE EXAMGUID = '''||PARA_EXAMGUID||'''
                    GROUP BY CLASSROOM_ID)))  應考人數
              ,COUNT(DISTINCT STUDID) AS 實考人數 ';
 
    -- 定義遊標myCusor
    -- 根據考試ID查詢考試科目,並按照COURSEID產生科目順序
    DECLARE CURSOR myCusor IS SELECT COURSENAME FROM V_FULL_SCORE  WHERE EXAMGUID = ''||PARA_EXAMGUID||''
    GROUP BY COURSEID,COURSENAME
    ORDER BY COURSEID ASC;
      -- 開始使用遊標取資料
      BEGIN
            OPEN myCusor;
            LOOP
                FETCH myCusor INTO VAR_COURSENAME;
                -- 遊標取不到資料則退出
                EXIT WHEN myCusor%NOTFOUND;
               
                -- 動態拼接各科目SQL語句
                VAR_SQL := VAR_SQL || ' , ROUND(SUM(CASE COURSENAME WHEN '''||VAR_COURSENAME||''' THEN SCORE_SUM ELSE 0 END) / SUM(CASE COURSENAME WHEN '''||VAR_COURSENAME||''' THEN 1 ELSE NULL END),2) '||VAR_COURSENAME||'平均分
              , MAX(CASE COURSENAME WHEN '''||VAR_COURSENAME||''' THEN SCORE_SUM ELSE 0 END) '||VAR_COURSENAME||'最高分
              , SUM(CASE WHEN COURSENAME = '''||VAR_COURSENAME||''' AND SCORE_SUM >= FULLSCORE_SUM * 0.6 THEN 1 ELSE 0  END) '||VAR_COURSENAME||'_及格人數
              , ROUND(SUM(CASE WHEN COURSENAME = '''||VAR_COURSENAME||''' AND SCORE_SUM >= FULLSCORE_SUM * 0.6 THEN 1 ELSE 0 END) / SUM(CASE COURSENAME WHEN '''||VAR_COURSENAME||''' THEN 1 ELSE NULL END) * 100,2) '||VAR_COURSENAME||'_及格率
              , SUM(CASE WHEN COURSENAME = '''||VAR_COURSENAME||''' AND SCORE_SUM >= FULLSCORE_SUM * 0.8 THEN 1 ELSE 0 END) '||VAR_COURSENAME||'_優分人數
              , ROUND(SUM(CASE WHEN COURSENAME = '''||VAR_COURSENAME||''' AND SCORE_SUM >= FULLSCORE_SUM * 0.8 THEN 1 ELSE 0 END) / SUM(CASE COURSENAME WHEN '''||VAR_COURSENAME||''' THEN 1 ELSE NULL END) * 100,2) '||VAR_COURSENAME||'_優分率';
               
              VAR_TEACHER_SQL := VAR_TEACHER_SQL || ' NULL AS ' || VAR_COURSENAME||'_科任老師 , ';
               
            END LOOP;
            CLOSE myCusor;
      END;
       
      -- 科任教師部分字串去掉逗號
      VAR_TEACHER_SQL := SUBSTR(TRIM(VAR_TEACHER_SQL),1,LENGTH(TRIM(VAR_TEACHER_SQL))-1);
 
      -- 拼接科任教師部分
      VAR_SQL := VAR_SQL || ',' || VAR_TEACHER_SQL;
     
    --  按照班級名稱,科目名稱,學生得到每個學生總分
    --  根據班級進行分組
    VAR_SQL := VAR_SQL || ' FROM (
            SELECT CLASSROOM_NAME
                    ,COURSENAME
                    ,STUDID
                    ,STUD_NAME
                    ,SUM(SCORE) AS SCORE_SUM
                    ,SUM(FULLSCORE) AS FULLSCORE_SUM
                FROM V_FULL_SCORE
              WHERE EXAMGUID = '''||PARA_EXAMGUID||'''
                  GROUP BY
                      CLASSROOM_NAME 
                      ,COURSENAME
                      ,STUDID
                      ,STUD_NAME) T
        GROUP BY ROLLUP(CLASSROOM_NAME)';
 
    DBMS_OUTPUT.PUT_LINE(VAR_SQL);
     
    OPEN CUR FOR VAR_SQL;
 
END ;

Oracle資料庫之PL/SQL程式基礎設計 

PL/SQL Developer實用技巧分享

使用PL/Scope分析PL/SQL代碼 

在PL/SQL中使用日期類型

從一個案例看PL/SQL代碼片的編譯與運行