oracle动态编译存储,【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 ;