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.htmPL/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更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址