Oracle dynamic SQL syntax

Dynamic SQL syntax
 
Only at run time 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 INTO statement 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 the variable sequence corresponds to the sequence of the record values in the query result set. If a parameter needs to be dynamically determined, we can use the USING clause to dynamically create a table.
 
Example [SQL]

1. <Strong> <span style = "font-size: 18px;"> -- dynamic SQL statement
2. Begin
3. Execute immediate '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. Execute immediate SQL _stmt into emp_rec using emp_id;
15. Dbms_output.put_line (emp_rec.phone );
17. End; </span> </strong>

[SQL]

1. <Strong> <span style = "font-size: 18px;"> -- Dynamic insert
2. Declare
3. SQL _stmt varchar (200 );
4. Emp_id number (10): = '& emp_id ';
5. Emp_rec employees % rowtype;
6. Begin
7. SQL _stmt: = 'insert into employees (id) values (: id )';
8. Execute immediate SQL _stmt using emp_id;
10. End; </span> </strong>

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: [SQL]

1. <Strong> <span style = "font-size: 18px;"> -- 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_type is ref cursor;
7. Cur c_type;
8. P_salaty number: = '& p_id ';
9. Begin
10. Open cur
11. 'Select e. id, e. name, s. salaryvalue from employees e, salary s
12. Where e. id = s. employeeid and s. salaryvalue>: sal order by id asc'
13. Using p_salry;
14. Dbms_output.put_line ('salary is higher than '| p_salary |' employees :');
15. Loop
16. Fetch cur into e_id, e_name, s_salary;
17. Exit when cur % notfound;
18. Dbms_output.put_line ('No.:' | e_id | 'name: '| e. name | 'salary' | s_salary );
19. End loop;
20. Close cur;
21. End; </span> </strong>
22.