Dynamic execution of SQL statements in PL/SQL (v)/PL

In PL/SQL program development, DML statements and transaction control statements can be used, but many statements (such as DDL statements) cannot be executed directly in PL/SQL. These statements can be implemented using dynamic SQL.

PL/SQL blocks are compiled and then executed, and dynamic SQL statements are not determined at compile time, but are executed by a dynamic SQL command as a string when the program executes. In the compile-time SQL statement exists as a string, the program does not compile the contents of the string, and then compiles and executes the SQL statements in the string at run time, with the syntax of the dynamic SQL:

Syntax format: Dynamic SQL

EXECUTE IMMEDIATE Dynamic Statement string

[into variable list]

[USING parameter list]

Syntax parsing:

If the dynamic statement is a SELECT statement, you can save the result of the query to the variable that follows into. If a parameter exists in a dynamic statement, the using parameter is passed as a value in the statement.

The parameter format in dynamic SQL is: [: Parameter name], which requires a using pass value at run time.

Case 9: Dynamic SQL

Code Demo: Dynamic SQL

DECLAREsql_stmtVARCHAR2( $);--Dynamic SQL statementsemp_id Number(4) := 7566; Salary Number(7,2); dept_id Number(2) :=  -; Dept_nameVARCHAR2( -) := 'Personnel'; LocationVARCHAR2( -) := 'DALLAS'; Emp_rec EMP%ROWTYPE;BEGIN --execute immediate with no clausesEXECUTEIMMEDIATE'CREATE TABLE Bonus1 (ID number, AMT number)'; ①--the execute immediate of the--using clauseSQL_STMT:= 'INSERT into Dept VALUES (: 1,: 2,: 3)'; EXECUTEIMMEDIATE sql_stmt USING dept_id, dept_name, Location;②--the execute immediate of the--into clauseSQL_STMT:= 'SELECT * from emp WHERE empno =: ID'; EXECUTEIMMEDIATE sql_stmt intoEmp_rec USING Emp_id;③--execute immediate--returning into clauseSQL_STMT:= 'UPDATE emp SET sal = Empno =: 1 returning sal Into:2'; EXECUTEIMMEDIATE sql_stmt USING emp_id returning intoSalary;④EXECUTEIMMEDIATE'DELETE from dept WHERE deptno =: num'USING Dept_id;⑤END;

Code parsing:

① executes a full SQL statement dynamically.
There are 3 parameters in the ②sql statement that are identified as: [: 1,: 2,: 3], so you need to assign a value to three parameters with the Using keyword, respectively.
③ to a dynamic query statement, you can use the INTO clause to save the result of the query to a variable, requiring that the result be a single row only.
④ the Insert,update,delete statement in Oracle can use the returning clause to return data from the rows affected by the operation, and when a returning clause exists in the SQL statement, it can be received using returning into when dynamic execution occurs.
⑤ Dynamic execution parameters can be: [: number] can also be [: string].

Dynamic execution of SQL statements in PL/SQL (v)/PL