Linux ·

【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程序基础设计  http://www.linuxidc.com/Linux/2015-06/119013.htm

PL/SQL Developer实用技巧分享 http://www.linuxidc.com/Linux/2014-09/107391.htm

使用PL/Scope分析PL/SQL代码  http://www.linuxidc.com/Linux/2015-07/120607.htm

在PL/SQL中使用日期类型 http://www.linuxidc.com/Linux/2015-07/120608.htm

从一个案例看PL/SQL代码片的编译与运行 http://www.linuxidc.com/Linux/2016-03/128904.htm

参与评论