Oracle dynamic SQL

Dynamic SQL syntax

Only when running can Oracle check whether its format is correct

The INTO and USING clauses are optional.

If the SQL statement is a query statement, we can use the INTO clause.

The INTO statement is used to receive the record value selected by the SELECT statement. It can be a variable sequence or a record-type variable, that is, a record-type variable.

The sequence of this variable corresponds to the sequence of the record values in the query result set.

If parameters need to be dynamically determined, we can use the USING clause.

Dynamic table Creation

Example

1. -- Dynamic SQL statement
2. Begin
3. ExecuteImmediate'Create table bonus (id number, ant number )';
4. End;
6. -- Dynamically query users' phone numbers
7.  Declare
8. SQL _stmt varchar2 (200 );-- Store query statements
9. Emp_id number (10): ='& Emp_id';
10. Emp_rec employees % rowtype;
12.  Begin
13. SQL _stmt: ='Select * from employees where id =: id';
14. ExecuteImmediate SQL _stmtIntoEmp_rec using emp_id;
15. Dbms_output.put_line (emp_rec.phone );
17.  End;
18. ">-- Dynamic insert
19.  Declare
20. SQL _stmtVarchar(200 );
21. Emp_id number (10): ='& Emp_id';
22. Emp_rec employees % rowtype;
23.  Begin
24. SQL _stmt: ='Insert into employees (id) values (: id )';
25. ExecuteImmediate SQL _stmt using emp_id;
27.  End;

The execute immediate statement can only be executed to return one or no results. If you want to write an SQL statement that returns multiple rows, you must use the REF dynamic cursor.

Example:

1. -- Dynamic SQL and Dynamic Cursor
2.  Declare
3. E_id number (10 );
4. E_name varchar2 (50 );
5. S_salary number (8 );
6. Type c_typeIsRefCursor;
7. Cur c_type;
8. P_salaty number: ='& P_id';
9.  Begin
10. OpenCurFor
11. 'SelectE. id, e.Name, S. salaryvalueFromEmployees e, salary s
12. WhereE. id = s. employeeidAndS. salaryvalue>: salOrder ByIdAsc'
13. Using p_salry;
14. Dbms_output.put_line ('Salary higher'| P_salary |'Employees :');
15. Loop
16. FetchCurIntoE_id, e_name, s_salary;
17. ExitWhenCur % notfound;
18. Dbms_output.put_line ('No :'| E_id |'Name :'| E.Name|'Pause'| S_salary );
19. EndLoop;
20. CloseCur;
21.  End;