【PL/SQL系列】Oracle存储过程使用动态SQL

125次阅读

共计 3829 个字符，预计需要花费 10 分钟才能阅读完成。

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 ;