Oracle_pl/sql (8) Dynamic SQL

Dynamic SQL
Limitations of 0.pl/sql Blocks
Cannot perform DDL operations (create, drop, alter);
Partial DCL Operations (GRANT, REVOKE) cannot be performed.

1. Syntax
Dynamic SQL: The SQL statement to execute can be determined at execution time.
When you write a dynamic SQL statement in a PL/SQL block, you need to store it in a string variable.
And SQL can contain placeholders.
Execute Immediate dynamic_string
Dynamic_string: is a string variable that holds the SQL statement to be executed.

2. Use
Can be processed:
DDL statement (Create,alter,drop),
DCL Statement (Grant,revoke),
DML statement (Insert,update,delete)
Single-Line SELECT statement
Multi-line SELECT statements need to be used in conjunction with cursors.

3.DDL operation
Execute immediate only need to have DDL statements behind
Create or Replace procedure drop_table (table_name VARCHAR2)
Is
Sql_statement varchar2 (100);
Begin
sql_statement:= ' drop table ' | | table_name;
Execute immediate sql_statement;
End
/
exec drop_table (' Emp_log ');

4.DCL operation (Grant, REVOKE)
Conn sys/123 as SYSDBA;
Create or Replace procedure Grant_sys_priv (priv varchar2,username varchar2)
Is
Sql_stat varchar2 (100);
Begin
sql_stat:= ' Grant ' | | priv| | ' to ' | | Username
Execute immediate sql_stat;
End
/
-Pre-authorization check
SELECT * from User_sys_privs;
--Authorization
EXEC grant_sys_priv (' Create any table ', ' Scott ')
--grant Create any View,drop any view to Scott;

Create or Replace procedure Revoke_sys_priv (priv varchar2,username varchar2)
Is
Sql_stat varchar2 (100);
Begin
sql_stat:= ' Revoke ' | | priv| | ' From ' | | Username
Execute immediate sql_stat;
End
/

5.DML operation
5.1 SQL statement does not require parameters
Declare
Sql_stat varchar2 (100);
Begin
--update EMP set sal=sal*1.1 where deptno=30;
sql_stat:= ' update emp set sal=sal*1.1 where deptno=30 ';
Execute immediate sql_stat;
End

5.2 SQL statement requires parameters
: Placeholder--bind variable
using pass parameter
& receive parameters from client
Declare
Sql_stat varchar2 (+);
Begin
Sql_stat:= ' update emp set sal=sal* (1+:p ercent/100) where deptno=:d no ';
Execute immediate sql_stat using &am p;percent,&dno;
End;
Retrofit:
Create or replace procedure Proc_emp_addsal (p_dno number,p_percent number)
is
begin
Update EMP Set sal=sal* (1+p_percent/100) where Deptno=p_dno;
End;

6. Single-line SELECT statement
needs to use the INTO clause to accept the return data
Syntax:
Execute immediate dynamic_string
[into (Define_variable|record)]
[using bind_argument]

Declare
sql_stat varchar2 (+);
V_ename Emp.ename%type;
Begin
Sql_stat:= ' select ename from emp where EMPNO=:ENP ';
Execute immediate sql_stat into V_ename using &enp;< br> Dbms_output.put_line (' Employees: ' | | V_ename);
End;
Retrofit:
Create or Replace function Func_emp_ename (p_empno number) return VARCHAR2
is
V_ename emp.ename%type;< Br>begin
Select ename to V_ename from EMP where empno=p_empno;
return v_ename;
End;

7. Multi-Line SELECT statement
7.1 Using Cursors
In order to handle dynamic multiline query operations, you must use the OPEN-FOR statement to open the cursor.
Fetching data using fetch loops, close cursors
Declare
Type Empcurtyp is REF CURSOR;
EMP_CV Empcurtyp;
Emp_record Emp%rowtype;
Sql_stat varchar2 (100);
Begin
Sql_stat:= ' SELECT * from emp where deptno=:d no ';
Open EMP_CV for Sql_stat using &dno;
Loop
Fetch EMP_CV into Emp_record;
Exit when Emp_cv%notfound;
Dbms_output.put_line (' Employee Name: ' | | emp_record.ename| | ', wages: ' | | Emp_record.sal);
End Loop;
Close EMP_CV;
End

7.2 Bulk collect into collection
Bulk Collect bulk operation
Declare
Type Ename_table_type is table of Emp%rowtype;
Ename_table Ename_table_type;
Sql_stat varchar2 (100);
Begin
Sql_stat:= ' SELECT * from emp where deptno=:d no ';
Execute immediate sql_stat bulk collect into ename_table using &dno;
For I in 1..ename_table.count loop
Dbms_output.put_line (' Employee Name: ' | | Ename_table (i). ename| | ', wages: ' | | Ename_table (i). Sal);
End Loop;
End

Reform:
Create or Replace procedure Proc_emp_enamesal (P_dno number)
Is
Begin
For RS in (SELECT * from emp where deptno=p_dno) loop
Dbms_output.put_line (' Employee Name: ' | | rs.ename| | ', wages: ' | | Rs.sal);
End Loop;
End
/

EXEC proc_emp_enamesal (20);

8. Use in the actual work
8.1 For sub-table processing
Create or Replace procedure Proc_stu (p_tab varchar2,p_id number)
Is
Type Ename_table_type is table of Stu_oracle%rowtype;
Ename_table Ename_table_type;
Sql_stat varchar2 (100);
Begin
Sql_stat:= ' select * from ' | | p_tab| | ' where id= ' | | p_id;
Execute immediate sql_stat bulk collect into ename_table;
For I in 1..ename_table.count loop
Dbms_output.put_line (' Student name: ' | | Ename_table (i). sname);
End Loop;
End
/
Show err;

Begin
Proc_stu (' Stu_java ', 11);
End

8.2 Implementation of common functions for splicing functions
Create or Replace function Func_concat (P_expectfield varchar2,
p_expecttable varchar2,p_ Expectcause varchar2) return VARCHAR2
is
/*
Function function: Get a single property to be spelled one line
parameter description: P_expectfield query field
p_expecttable Query Main Table
P_expectcause query Criteria
*/
V_sql VARCHAR2 (4000): = ';
type type_cursor is REF CURSOR;
V_cur type_cursor ;
V_per varchar2 (4000): = ';
V_cnt number:=0;
V_return varchar2 (4000): = ';
Begin
v_sql:= ' SELECT ' | | p_expectfield| | ' From ' | | p_expecttable| | ' WHERE ' | | P_expectcause;
Open v_cur for V_sql;
Loop
Fetch v_cur into V_per,
exit when V_cur%notfound,
if v_cnt=0 then
V_return:=v_per;
Else
v_return:=v_return| | ', ' | | V_per;
End If;
V_cnt:=v_cnt+1;
End Loop;
Close v_cur;
return V_return;
End Func_concat;
/
Show err;

Select Func_concat (' ename ', ' emp ', ' 1=1 ') from dual;
Select Func_concat (' distinct job ', ' emp ', ' deptno=20 ') from dual;
Select Func_concat (' dname ', ' dept ', ' 1=1 ') from dual;
--select ename from Emp,dept where Emp.deptno=dept.deptno and dept.deptno=20;
Select Func_concat (' ename ', ' emp,dept ', ' Emp.deptno=dept.deptno and Dept.deptno=20 ') from dual;
--select job from EMP where ename= ' scott1 ';
Select Func_concat (' Job ', ' emp ', ' ename= ' ' scott1 ') from dual;
--select ename from Emp,dept where Emp.deptno=dept.deptno and lower (dname) = ' Sales ';
Select Func_concat (' ename ', ' emp,dept ', ' Emp.deptno=dept.deptno and lower (dname) = ' sales ') from dual;

Homework:
1. Create a table with dynamic SQL, add columns to the table, delete columns, modify the column length;
2. Create a table with dynamic SQL, add primary KEY constraint, unique key constraint, FOREIGN KEY constraint to the table;
3. Truncate the table with dynamic SQL;
4. Using dynamic SQL to implement the INSERT and delete functions of the departmental table

Oracle_pl/sql (8) Dynamic SQL