[PL/SQL series] use dynamic SQL for Oracle stored procedures

[PL/SQL series] use dynamic SQL for Oracle stored procedures

I believe everyone knows about the Oracle stored procedure. The following describes how to use dynamic SQL in the Oracle stored procedure. I hope this will help you.

Create or replace procedure P_STAT_SCORE01
(
PARA_EXAMGUID VARCHAR2, -- Test ID a9093ae714ac47758a415b8813b99d1d, 3216885E3B3148E3904908BD30BF9413, plural
Cur out SYS_REFCURSOR
)
 
/*************************************** *************************************
Name: P_STAT_SCORE02
Function Description: obtains information about the class and test subjects based on the Test ID.

Revision history:
Version Number editing time modifier modify description
1.0.0 CHEQNP 1. Create this stored procedure and modify dynamic SQL statements, mainly including variable usage and WHERE statement writing
 
Note
 
Description of input and output parameters:
PARA_EXAMGUID Test ID
CUR output cursor
 
**************************************** *************************************/
 
AS
-- Define variables
VAR_ SQL CLOB; -- concatenate SQL
VAR_TEACHER_ SQL CLOB; -- a teacher of science
VAR_COURSENAME VARCHAR (200); -- Subject name

BEGIN

VAR_ SQL: = 'select
(Case when CLASSROOM_NAME is null then ''total'' else min (CLASSROOM_NAME) END) AS class ';

-- Obtain the number of trainees and the number of trainees
VAR_ SQL: = VAR_ SQL | ', NVL2 (CLASSROOM_NAME,
(Select count (1) FROM TPRG_STUDENT
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) number of candidates
, COUNT (distinct studid) AS number of students ';
 
-- Define the cursor myCusor
-- Query the exam subjects based on the Exam ID and generate the order of the subjects according to the COURSEID
Declare cursor myCusor is select coursename from V_FULL_SCORE where examguid = ''| PARA_EXAMGUID |''
Group by courseid, COURSENAME
Order by courseid asc;
-- Start to use a cursor to retrieve data
BEGIN
OPEN myCusor;
LOOP
FETCH myCusor INTO VAR_COURSENAME;
-- Exit if the cursor fails to retrieve data
Exit when myCusor % NOTFOUND;

-- Dynamically concatenate SQL statements for each subject
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 | 'average score
, MAX (case coursename when ''' | VAR_COURSENAME | '''then SCORE_SUM ELSE 0 END) '| VAR_COURSENAME | 'highest score
, SUM (case when coursename = ''' | VAR_COURSENAME | ''' AND SCORE_SUM> = FULLSCORE_SUM * 0.6 THEN 1 ELSE 0 END) '| VAR_COURSENAME |' _ number of passing students
, 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) *) '| VAR_COURSENAME |' _ pass rate
, SUM (case when coursename = ''' | VAR_COURSENAME | ''' AND SCORE_SUM> = FULLSCORE_SUM * 0.8 THEN 1 ELSE 0 END) '| VAR_COURSENAME |' _ number of excellent scores
, 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) *) '| VAR_COURSENAME |' _ Excellent score ';

VAR_TEACHER_ SQL: = VAR_TEACHER_ SQL | 'null AS '| VAR_COURSENAME |' _ Mr. Ke ,';

End loop;
CLOSE myCusor;
END;

-- Remove commas (,) from the instructor strings
VAR_TEACHER_ SQL: = SUBSTR (TRIM (VAR_TEACHER_ SQL), 1, LENGTH (TRIM (VAR_TEACHER_ SQL)-1 );
 
-- Splicing the instructor Section
VAR_ SQL: = VAR_ SQL | ',' | VAR_TEACHER_ SQL;

-- According to the class name and subject name, the students get the total score of each student
-- Group by class
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
CLASSROOM_NAME
, COURSENAME
, STUDID
, STUD_NAME) T
Group by rollup (CLASSROOM_NAME )';
 
DBMS_OUTPUT.PUT_LINE (VAR_ SQL );

Open cur for VAR_ SQL;
 
END;

Basic Design of PL/SQL programs for Oracle databases

PL/SQL Developer Practical Skills

Use PL/Scope to analyze PL/SQL code

Use the date type in PL/SQL

How to compile and run PL/SQL code snippets from a Case Study