The Oracle compiled PL/SQL program block is divided into two types: static SQL usually uses the previous compilation method, and dynamic SQL uses the latter one to understand the friend can refer to the following
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.
This paper mainly discusses the development of dynamic SQL, and finally gives some practical development techniques.
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:
Copy CodeThe code is as follows:
Excute immediate Dynamic SQL statement using-bound parameter list returning into output parameter list; This statement is described as follows:
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.
To better illustrate the process of its development, here is an example:
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：
Copy CodeThe code is as follows:
Create or replace procedure create_table as
Begin
Execute Immediate '
CREATE TABLE EMP (ID number,
Name VARCHAR2 (10),
Salary number) '; --Dynamic SQL for DDL statements
INSERT INTO EMP
VALUES (+, ' Jacky ', 5600);
INSERT INTO EMP
VALUES (101, ' Rose ', 3000);
INSERT INTO EMP
VALUES (102, ' John ', 4500);
End create_table;
procedure Two：
Copy CodeThe code is 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 | | ' The income is: ' | | To_char (v_salary));
exception
When others then
Dbms_output.put_line (' No corresponding data found ');
End Find_info;
Process Three：
Copy CodeThe code is 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) | | ' whose name is: ' | | R_emp.name);
End Loop;
Close C1;
End create_table;
Note: The dynamic SQL statement in procedure two uses the placeholder ": 1", which is 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.
Skill One: Use similar SQL statements as much as possible, so that Oracle itself caches the SQL statement directly through a shared pool in the SGA, so that 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 the assembly unit, use the batch-linked compilation 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:
Copy CodeThe code is as follows:
Declare
Type num_list is Varray (a) 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 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:
Copy CodeThe code is as follows:
Declare
Type num_list is Varray (a) 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 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:
Copy CodeThe code is 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. 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.
Detailed introduction to Dynamic SQL usage in Oracle
