"Go" Oracle executes dynamic statements

1. static sqlsql and dynamic SQL
The Oracle compiled PL/SQL program block is divided into two types: one for the pre-assembly (early binding), that is, the SQL statement is determined during program compilation, most of the compilation is of this type, and the other is the late-linking (late binding), That is, SQL statements can only be established at run time, for example, when the query condition is user input, then Oracle's SQL engine can not be determined at compile time for the program statement, only after the user entered a certain query criteria to be submitted to the SQL engine for processing. Typically, static SQL takes the previous compilation, whereas dynamic SQL uses the latter one.

2. Dynamic SQL program Development
Understanding the principle of dynamic SQL compilation, but also mastered its basic development ideas. Since dynamic SQL is an "indeterminate" SQL, its execution has its corresponding characteristics. The execute immediate statement is provided in Oracle to execute dynamic SQL with the following syntax:

 into output parameter list;

1) Dynamic SQL refers to DDL and indeterminate DML (that is, DML with parameters)
2) The binding parameter list is the input parameter list, which is the type in type, which is bound at run time with the parameters in the dynamic SQL statement (actually placeholders, which can be understood as the formal parameters inside the function).
3) The output parameter list is the list of parameters returned after the dynamic SQL statement executes.
4) Since dynamic SQL is determined at runtime, it can lose some system performance in exchange for its flexibility relative to static.

Set the database EMP table with the following data:

Id	NAME	SALARY
100	Jacky	5600
101	Rose	3000
102	John	4500

Requirements :
1. Create the table and enter the appropriate data.
2. Information about their name and salary can be queried based on a specific ID.
3. The employee information is based on a query that is larger than the specific salary.
Depending on the previous requirements, you can create three procedures, each using dynamic SQL, to achieve:
Process One :

1 Create or Replace procedureCreate_table is 2 begin 3 ExecuteImmediate'CREATE TABLE EMP (4 ID number,5 name Varchar2 (Ten),6 salary number)';--Dynamic SQL is a DDL statement
7 Endcreate_table;

procedure two :

1 Create or Replace procedureFind_info (p_id Number) Is 2V_namevarchar2(Ten); 3V_salary Number; 4 begin 5 ExecuteImmediate' 6 Select Name,salary from emp7 where Id=:1' 8 using p_id9Returning intoV_name,v_salary;--Dynamic SQL is a query statementTenDbms_output.put_line (v_name||'the income is:'||To_char (v_salary)); One Exception A  whenOthers Then  -Dbms_output.put_line ('no corresponding data found'); - EndFind_info;

Process three :

1 Create or Replace procedureFind_emp (p_salary Number) Is 2R_emp EMP%RowType;3Type C_type isRefcursor; 4 C1 C_type;5 begin 6 OpenC1 for ' 7 SELECT * from emp8 where salary >:1' 9 using P_salary;Ten Loop One FetchC1 intor_emp; A Exit  whenC1%NotFound; -Dbms_output.put_line ('salary greater than ' | | To_char (p_salary) | |'employees are: ');  -Dbms_output.put_line ('ID is'To_char (r_emp)||'the name is:'||r_emp.name);  the EndLoop; - CloseC1; - Endcreate_table;

Note : The dynamic SQL statement in procedure two uses the placeholder ": 1", which is actually equivalent to the formal parameter of the function, uses ":" As the prefix, and then uses the using statement to replace the p_id at run time: 1, where p_id is equivalent to the argument in the function. In addition, the cursor opened in the process three is a dynamic cursor, it also belongs to the category of dynamic SQL, its entire compilation and development process is similar to execute immediate execution, here is not to repeat.
3. Dynamic SQL statement Development tips
In the previous analysis, the execution of dynamic SQL is to lose the performance of the system in exchange for its flexibility, so it is necessary to some degree of optimization, the author according to the actual development experience to give some development skills, it should be pointed out that many of the experience here is not limited to dynamic SQL, some also apply to static SQL, Annotations are given in the description.
Tip One : Use similar SQL statements as much as possible, so that Oracle itself caches the SQL statement directly through the shared pool in the SGA, so the next time a similar statement is executed, the parsed statements in the cache are called directly to improve execution efficiency.
Tip Two : when it comes to assembly units, use the batch-linking as much as possible. For example, a salary increase of 10% for an employee with ID 100 and 101 is required, usually in the following form:

1 Declare 2Type Num_list isVarray ( -) of  Number; 3v_id num_list:=Num_list ( -,101); 4 begin 5 ... 6  forIinchv_id.first. V_id.last Loop7 ... 8 ExecuteImmediate'Update emp9 Set =salary*1.2Ten where Id=:1'  One using v_id (i); A EndLoop; - End;

For the above processing, when the amount of data is large, it will appear relatively slow, then if the use of batch-linked, then the entire set of first-time incoming into the SQL engine for processing, which is more efficient than the individual processing, the code for the batch processing is as follows:

1 Declare 2Type Num_list isVarray ( -) of  Number; 3v_id num_list:=Num_list ( -,101); 4 begin 5 ... 6ForAll Iinchv_id.first. V_id.last Loop7 ... 8 ExecuteImmediate'Update emp9 Set =salary*1.2Ten where Id=:1'  One using v_id (i); A EndLoop; - End;

Here is the use of forall for the approval of the series, which will be a summary of the situation of the batch processing:
1) If a statement such as Insert,delete,update within a loop references a collection element, you can move it to a forall statement.
2) If a select Into,fetch into or returning into clause references a collection, it should be merged using the bulk collect clause.
3) If possible, use a host array to pass parameters between the program and the database server.
Tip Three: Use the nocopy compiler to improve PL/SQL performance. By default, parameters for out types and in-out types are passed by value. However, for large object types or collection types of parameter passing, it would be a great loss, in order to reduce the loss, you can use the method of reference passing, that is, when the parameter declaration to refer to the Nocopy keyword to illustrate the effect can be reached. For example, create a process:

Create or Replace procedure test (P_object in Nocopy Square) ... end;

Where square is a large object type. This simply passes an address instead of passing the entire object. Obviously this kind of treatment also improves the efficiency.
4. Summary
This paper discusses the compiling principle, development process and development technique of dynamic SQL, and through the introduction of this paper, I believe that readers have a general understanding of dynamic SQL program development, and lay a good foundation for the further work in the future.

First, why use dynamic execution statements?

Because only DML statements and control-flow statements are supported in PL/SQL blocks or stored procedures , and DDL statements are not supported, Oracle dynamic execution statements are answered. For the differences between DDL and DML, see: DDL statements with DML statements and DCL and Tcl.

Second, how to use dynamic execution statements?

The dynamic execution statement replaces the DBMS_SQL package in Oracle 8i.

1) run the SQL statement in PL/SQL , for example:

Example one:

BEGIN
EXECUTE IMMEDIATE ' SELECT COUNT (username) from user_users '; --DML( Each statement must end with a semicolon )
END;

Example two:

BEGIN
EXECUTE IMMEDIATE ' ALTER TABLE a RENAME to EXAMPLE '; --DDL
END;

You might ask if a DDL statement needs to be executed with a dynamic statement? Yes, you are absolutely right. However, DML statements can also be executed with dynamic statements.

That is, DDL statements can only be executed with dynamic execution statements, and DML statements can also be executed with dynamic statements.

2) use the using to transmit values to the dynamic statement, for example:

DECLARE
E_name VARCHAR2 (10); --Declaring variable E_name
E_age INT; --Declaring variable E_age
BEGIN
E_name: = ' Sillylaura '; --Assigning values to variable E_name
E_age: = 21; --Assigning values to variable E_age
EXECUTE IMMEDIATE ' insert into Example values (seq_add_by_one.nextval,:2,:3) using e_name,e_age; --DMLEND;

3) Assign a value using dynamic statement (select column name into variable from ...) )

DECLARE
Temp INT;
BEGIN
EXECUTE IMMEDIATE ' SELECT COUNT (*) from dual ' into temp; --DML

Dbms_output.put_line (temp);
END;

4) Pass and retrieve values: into is used before using.

DECLARE
Temp INT;
Test VARCHAR2 (10);
BEGIN
Test: = ' OK ';
EXECUTE Immediate ' SELECT COUNT (*) from dual where dummy =: 1 GROUP by dummy ' into temp USING test;
Dbms_output.put_line (Temp | | ' ' | | Test);
EXCEPTION when OTHERS Then
Dbms_output.put_line (' It has no data! ');
END;

III. Summary of dynamic statements

1. DDL statements can only be executed with dynamic execution statements, and DML statements can also be executed with dynamic statements.
2. when assigning a value to a variable using the Select......into clause, the INTO clause must be written outside the single quotation mark.
3. when using both the Select......into clause and the using, note the order of the two: into is used before using, and both are outside the single quotation mark.
4. Note Write the necessary exception error handling.

"Go" Oracle executes dynamic statements