1. static sqlsql and dynamic SQL
Oracle compiled PL/SQL program block is divided into two kinds: first, the previous early binding, that is, the SQL statement was established during the program compilation, most of the compilation is this type, and the other is the late-stage (late binding), That is, the SQL statement can only be established at run time, for example, when the query condition is entered by the user, the Oracle SQL engine cannot determine the program statement at compile time, and can submit it to the SQL engine for processing only after the user has entered a certain query condition. In general, static SQL takes the previous form of compilation, and dynamic SQL takes the latter form of compilation.
This paper mainly discusses the development of dynamic SQL, and finally gives some practical development techniques.
2. Dynamic SQL program Development
The principle of dynamic SQL compilation is understood, and the basic development idea is mastered. Since dynamic SQL is an "indeterminate" SQL, its execution has its own characteristics. The Execute immediate statement is provided in Oracle to execute dynamic SQL, as follows:
Copy Code code as follows:
Excute immediate Dynamic SQL statement the using-bound parameter list returning into the output parameter list, which is described as follows:
1 dynamic SQL refers to DDL and indeterminate DML (that is, DML with arguments)
2 The binding parameter list is the input parameter list, which is of type in type, and is bound at runtime with the arguments in the dynamic SQL statement (actually placeholders, which can be understood as the form parameters inside the function).
3 The output parameter list is the list of parameters returned after the execution of the dynamic SQL statement.
4 because dynamic SQL is determined at run time, it will lose some system performance in exchange for its flexibility relative to static.
To better illustrate the process of its development, here is an example:
Set the EMP table for the database with the following data:
| Id |
NAME |
SALARY |
| 100 |
Jacky |
5600 |
| 101 |
Rose |
3000 |
| 102 |
John |
4500 |
Required：
1. Create the table and enter the appropriate data.
2. Information about its name and salary can be queried based on a specific ID.
3. The employee information is based on a query that is larger than a specific salary.
According to the previous requirements, you can create three separate processes (all using dynamic SQL) to achieve:
Process One：
Copy Code code as follows:
Create or replace procedure create_table as
Begin
Execute Immediate '
CREATE TABLE EMP (ID number,
Name VARCHAR2 (10),
Salary number) '; --Dynamic SQL is a DDL statement
INSERT INTO EMP
VALUES (+, ' Jacky ', 5600);
INSERT INTO EMP
VALUES (' Rose ', 3000);
INSERT INTO EMP
VALUES (102, ' John ', 4500);
End create_table;
Process Two：
Copy Code code as follows:
Create or Replace procedure Find_info (p_id number) as
V_name VARCHAR2 (10);
V_salary number;
Begin
Execute Immediate '
Select Name,salary from emp
where Id=:1 '
Using p_id
returning into V_name,v_salary; --Dynamic SQL for Query statements
Dbms_output.put_line (V_name | | ') 's income is: ' | | To_char (v_salary));
exception
When others then
Dbms_output.put_line (' cannot find the corresponding data ');
End Find_info;
Process Three：
Copy Code code as follows:
Create or Replace procedure Find_emp (p_salary number) as
R_emp Emp%rowtype;
Type c_type is REF CURSOR;
C1 C_type;
Begin
Open C1 for '
SELECT * FROM emp
Where salary >:1 '
Using P_salary;
Loop
Fetch C1 into r_emp;
Exit when C1%notfound;
Dbms_output.put_line (' salary greater than ' | | To_char (p_salary) | | Employees are: ');
Dbms_output.put_line (' id ' to_char (r_emp) | | ' Its name is: ' | | R_emp.name);
End Loop;
Close C1;
End create_table;
Attention: The dynamic SQL statement in procedure two uses the placeholder ": 1", which is equivalent to the form 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 process Three is a dynamic cursor, and it belongs to the category of dynamic SQL, and its entire compilation and development process is similar to that executed by execute immediate.
3. Techniques for developing dynamic SQL statements
In the previous analysis, dynamic SQL was executed in exchange for its flexibility in the loss of system performance. So it is necessary to optimize it to a certain extent, 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.
Tips a: Use similar SQL statements as much as possible, so that Oracle itself caches the SQL statement directly through a shared pool in the SGA, and then executes the parsed statement directly in the cache the next time a similar statement is executed, to improve execution efficiency.
Skill Two: When it comes to the collection unit, use the batch-linked compilation as much as possible. For example, a salary increase of 10% for employees with IDs of 100 and 101 is required, usually in the following form:
Copy Code code as follows:
Declare
Type num_list is varray of number;
v_id num_list: =num_list (100,101);
Begin
...
For I in V_id.first. V_id.last Loop
...
Execute immediate ' update emp
Set =salary*1.2
where Id=:1 '
Using v_id (i);
End Loop;
End
For the above processing, when the volume of data will appear relatively slow, if the use of batch-linked, then the entire collection of the first one-time incoming to the SQL engine processing, which is more efficient than the processing efficiency, the batch of the code to process the following:
Copy Code code as follows:
Declare
Type num_list is varray of number;
v_id num_list: =num_list (100,101);
Begin
...
ForAll i in V_id.first. V_id.last Loop
...
Execute immediate ' update emp
Set =salary*1.2
where Id=:1 '
Using v_id (i);
End Loop;
End
Here is the use of forall to carry out the batch of the series, where the context of the batch process is summarized as follows:
1 if a insert,delete,update such as a loop executes a reference to a collection element, it can be moved to a forall statement.
2 If the select Into,fetch into or returning into clause references a collection, it should be merged using the bulk collect clause.
3 if possible, host arrays should be used to implement 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. But for the large object type or the collection type parameter transmission, its hope loss will be very big, in order to reduce the loss, may use the reference transmission way, namely when carries on the parameter declaration the reference Nocopy keyword to explain can arrive this kind of effect. For example, to create a process:
Copy Code code as follows:
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. It is clear that such treatment also improves efficiency.
4. Summary
This paper discusses the compiling principle, development process and development techniques of dynamic SQL, and after introducing this article, I believe that readers have a general understanding of the development of dynamic SQL program and lay a good foundation for further work in the future.
