Dynamic SQL detailed

Dynamic SQL

One of the biggest features of a PL/SQL program that was written by a user is that the database object being manipulated (for example, a table) must exist, otherwise the created subroutine will be problematic, and such operations are called Static SQL operations in development. Dynamic SQL operations allow the user to define a program without specifying a specific action object, and dynamically pass in the required database objects at execution time, thus making the program more flexible.

Create a function, enter the table name, how many rows are in the output table, and if not, create this table.

First Disable all triggers

GRANT CREATE any TABLE to SCOTT

Create or Replace function Get_table_count_fun (p_table_name VARCHAR2) return number as

V_sql_statement VARCHAR2 (200);--The SQL statement that defines the operation

V_count number;--record in Save table

Begin

Select COUNT (*) into V_count from User_tables where Table_name=upper (P_table_name);

If v_count=0 then--data sheet does not exist

v_sql_statement:= ' CREATE TABLE ' | | P_table_name | | ' (ID number, name VARCHAR2 (+) not null) ';--Create a data table, note that the space is not ignored here, in accordance with the normal write CREATE TABLE statement format and space, otherwise unrecognized

Execute immediate v_sql_statement;--execute dynamic SQL

End If;

V_sql_statement:= ' SELECT COUNT (*) from ' | | p_table_name;--query data table record, note plus space

Execute immediate v_sql_statement into v_count;--execute dynamic SQL and save data Records

return v_count;

End

/

Declare

V1 VARCHAR2 (100);

Begin

V1:=get_table_count_fun (' T100 ');--View T100 table

Dbms_output.put_line (v1);

End

/

EXECUTE Immediate Statement

Execute immediate is the most important execution command in dynamic SQL, which makes it easy to execute DML (insert/update/delete/single-column Select), DDL (create/alter/) in a PL/SQL program. DROP), DCL (Grant/revoke) statement, the EXECUTE immediate syntax is defined as follows:

EXECUTE IMMEDIATE Dynamic SQL string [[[BULK COLLECT]] into custom variable, ... | Record Type |

[USING [In | Out | In out| binding parameter,...]

[[Returning | Return][bulk COLLECT] into binding parameter,...];

The EXECUTE immediate consists of the following three main words:

into: Save dynamic SQL execution results, if the return of multi-line records can be set by bulk collect batch save;

USING: Used to set content for dynamic SQL settings placeholders;

Returning | RETURN: Both use the same effect, is to obtain the Update table record is affected data, through the bulk collect to set up the bulk binding.

Creating tables and Plsql blocks with dynamic SQL

Declare

V_sql_statement VARCHAR2 (200);

V_count number;--Save Search results

Begin

Select COUNT (*) into V_count from user_tables where table_name= ' vdata_tab ';

If v_count=0 then--data sheet does not exist

v_sql_statement:= ' CREATE TABLE vdata_tab (

ID number primary Key,

URL varchar2 (not null) ';--Define dynamic SQL

Execute immediate v_sql_statement;

else--data table exists

v_sql_statement:= ' TRUNCATE TABLE vdata_tab ';

Execute immediate v_sql_statement;

End If;

V_sql_statement:= ' begin

For x in 1: Ten loops

Insert into Vdata_tab (Id,url) VALUES (x, "www.vdata.com.cn" | | x);

End Loop;

end; ';

Execute immediate v_sql_statement;

commit;--Submitting Things

End

/

Using bound variables

Declare

V_sql_statement VARCHAR2 (200);

V_deptno dept.deptno%type:=60;

V_dname dept.dname%type:= ' VDATA ';

V_loc dept.loc%type:= ' Beijing ';

Begin

v_sql_statement:= ' INSERT INTO dept (DEPTNO,DNAME,LOC) VALUES (:d No,:d na,:d l) ';

Execute immediate v_sql_statement using V_deptno,v_dname,v_loc;

Commit;

End;

/

If there is a null field, you cannot bind null directly, and you need to set it through the variable

If the requested additional department position in this program is NULL, the following settings are incorrect: EXECUTE IMMEDIATE v_sql_statement using V_deptno,v_dname,null; If you want the contents of LOC to be NULL now , you can set the V_LOC variable to null, and the other execution parts do not change.

Querying data

Declare

V_sql_statement1 VARCHAR2 (2000);

V_empno emp.empno%type:=7369;

V_emprow Emp%rowtype;

Begin

V_sql_statement1:= ' SELECT * from emp where empno=:eno';

Execute immediate v_sql_statement1 into V_emprow using V_empno;

Dbms_output.put_line (' ename: ' | | v_emprow.empno| | ', ename: ' | | v_emprow.ename| | ', Position: ' | | V_emprow.job);

End

/

As you can see, all code that uses bound variables is only for basic data types, such as strings, numbers, and so on, but this is not possible for DDL operations, such as using bound variables for table names that will be created or staged, or an error occurs.

Using bound variables when creating tables

DECLARE

V_sql_statement VARCHAR2 (200);

V_table_name VARCHAR2 (+): = ' mldn ';

V_id_column VARCHAR2 (+): = ' id ';

BEGIN

V_sql_statement: = ' CREATE TABLE ' | | V_table_name | | ' (' | | | v_id_column | | ' Number PRIMARY KEY) ';

EXECUTE IMMEDIATE v_sql_statement;

END;

Update the data to get the updated results

Declare

V_sql_statement VARCHAR2 (200);--Define SQL action statements

V_empno emp.empno%type:=7369;--the employee number to update

V_salary emp.sal%type;--Save updated Sal content

V_job emp.job%type;--Save updated job content

Begin

v_sql_statement:= ' update emp set sal=sal*1.2,job= ' Developer ' | | ' where Empno=:eno returning Sal.job into:salary,:job ';

Execute immediate v_sql_statement using v_empno returning into V_salary,v_job;

Dbms_output.put_line (' Salary: ' | | v_salary| | ', new position: ' | | V_job);

End;

/

You can also use return to receive data that affects data rows.

When you receive data that affects data rows, you can also use the return operation, as follows:

v_sql_statement:= ' update emp set sal=sal*12,job= ' Developer ' | | ' where Empno=:eno return sal,job into:salary,:job ';

Execute immediate v_sql_statement using v_empno return into v_salary,v_job;

Using return is no different than returning

Delete data to get the result before deletion

Declare

V_sql_statement VARCHAR2 (200);--Define SQL action statements

V_emprow emp%rowtype;--Saving EMP type

V_empno emp.empno%type:=7369;--deleted employee number

V_ename emp.ename%type;--deleted employee Name

V_sal emp.sal%type;--deleted employee wages

Begin

v_sql_statement:= ' Delete from emp where Empno=:eno returning ename,sal into:name,:sal;

Execute immediate v_sql_statement using v_empno returning into V_ename,v_sal;

Dbms_output.put_line (' empno: ' | | v_empno| | ', ename: ' | | v_ename| | ', Salary: ' | | V_sal);

End;

/

You can set the parameter pattern (in/out/in out) when using a using or returning statement, where the using clause is primarily the content defined by the variable, so the default mode is in mode. When you use the returning clause, you do not need to set the content, just receive the returned content, so its pattern is out.

Writing a departmental increase process

Create or replace procedure Dept_insert_proc (

P_deptno in Out Dept.deptno%type,--here you can return the contents of P_deptno

P_dname dept.dname%type,--default to In mode

P_loc Dept.loc%type) As--default to In mode

Begin

Select Max (DEPTNO) to P_deptno from dept;--to get the most deptno content

p_deptno:=p_deptno+1;--let the maximum department number +1, where more than two digits are not considered

Insert into Dept (Deptno,dname,loc) VALUES (P_DEPTNO,P_DNAME,P_LOC);

End;

/

Bulk binding

When a query or update operation is made through dynamic SQL, a single action statement is submitted to the database, and if you now want the database to receive multiple SQL at one time, and the database can return the results of the operation to a collection at a time, it can be done in bulk processing operations, Note rely on bulk collect to operate.

Update with bulk collect

DECLARE

TYPE Ename_index is TABLE of Emp.ename%type index by Pls_integer;

TYPE Job_index is TABLE of Emp.job%type index by Pls_integer;

TYPE Sal_index is TABLE of Emp.sal%type index by Pls_integer;

V_ename Ename_index;

V_job Job_index;

V_sal Sal_index;

V_sql_statement VARCHAR2 (200); --Define dynamic SQL

V_deptno Emp.deptno%type: = 10; --Query 10 Department Door

BEGIN

V_sql_statement: = ' UPDATE emp SET sal=sal*1.2 WHERE deptno=:d No ' | |

' Returning Ename,job,sal Into:ena,: EJ,: Es ';--returns multiple rows of update results (note space, otherwise unrecognized statement)

EXECUTE IMMEDIATE v_sql_statement USING V_deptno

Returning BULK COLLECT into V_ename,v_job,v_sal;

For x in 1: V_ename. COUNT LOOP

Dbms_output.put_line (' Employee name ' | | v_ename (x) | | ', Position: ' | | V_job (x) | | ', Wages: ' | | V_sal (x));

END LOOP;

END;

Employee Name: CLARK, Job: MANAGER, Salary: 3528

Employee Name: KING, Position: President, Salary: 7200

Employee Name: MILLER, Position: Clerk, Salary: 1872

Use bulk COLLECT when querying

Declare

Type Ename_index is table of Emp.ename%type index by Pls_integer;

Type Job_index is table of Emp.job%type index by Pls_integer;

Type Sal_index is table of Emp.sal%type index by Pls_integer;

V_ename Ename_index;

V_job Job_index;

V_sal Sal_index;

V_sql_statement VARCHAR2 (200);--Define dynamic SQL

V_deptno emp.deptno%type:=10;--Query 10 Department Door

Begin

v_sql_statement:= ' select Ename,job,sal from emp where deptno=:d no ';--returns multiple rows of update results

Execute Immediate V_sql_statement

Bulk Collect into V_ename,v_job,v_sal

Using V_deptno;

For x in 1..v_ename.count loop

Dbms_output.put_line (' ename: ' | | V_ename (x) | | ', Position: ' | | V_job (x) | | ', Salary: ' | | V_sal (x));

End Loop;

End;

/

FORALL

If you want to set multiple binding parameters to dynamic SQL, you must take advantage of the ForAll statement, which has the following syntax.

ForAll index variable in parameter collection minimum value: Parameter Collection Maximum value

EXECUTE IMMEDIATE Dynamic SQL string

[USING Binding Parameters | Binding parameters (Index), ...]

[[Returning | RETURN] BULK COLLECT into binding parameter collection, ...];

Setting multiple parameters with ForAll

Declare

Type empno_nested is table of emp.empno%type;--defines a nested table

Type Ename_index is table of Emp.ename%type index by pls_integer;--

Type Job_index is table of Emp.job%type index by pls_integer;--

Type Sal_index is table of Emp.sal%type index by pls_integer;--

V_ename ename_index;--Save the deleted name

V_job job_index;--save post after deletion

V_sal sal_index;--Save the deleted payroll

V_empno empno_nested:=empno_nested (7369,7566,7788);--Define the employee number to be deleted

V_sql_statement VARCHAR2 (200);--Dynamic SQL

Begin

v_sql_statement:= ' Delete from emp where Empno=:eno ' | | '

Returning Ename,job,sal Into:ena,: EJ,: Es ';--delete data sql

FORALL x in 1.. V_empno.count--forall Binding Multiple variables

EXECUTE IMMEDIATE v_sql_statement using V_empno (x)

Returning bulk collect into v_ename,v_job,v_sal;

For x in 1: V_ename.count Loop

Dbms_output.put_line (' ename: ' | | V_ename (x) | | ', Position: ' | | ', Salary: ' | | V_sal (x));

End Loop;

End;

/

Handling cursor Operations

In a dynamic SQL operation, in addition to handling single-row query operations, cursors can be used to perform multiple rows of data, and in the case of cursor definitions It is also possible to use dynamically bound variables, which requires an additional using clause operation when opening a cursor variable.

Using dynamic SQL in Cursors

DECLARE
Emp_cur Sys_refcursor; --Define cursor variables
V_emprow Emp%rowtype; --Define the EMP line type
V_deptno Emp.deptno%type: = 10; --Define the department number to query the employee
BEGIN
OPEN emp_cur for ' SELECT * from emp WHERE deptno=:d No '
USING V_deptno;
LOOP
FETCH emp_cur into V_emprow; --Get Cursor data
EXIT when Emp_cur%notfound; --Exit if no data is left
Dbms_output.put_line (' Employee Name: ' | | v_emprow.ename | | ', employee position: ' | | V_emprow.job);
END LOOP;
CLOSE emp_cur;
END;

FETCH

In the FETCH statement, using bulk collect to save multiple data to the collection type at once, the syntax is as follows.

FETCH dynamic cursor BULK COLLECT into set variable ...;

Save query results with fetch

Declare

Emp_cur sys_refcursor;--defining a cursor variable

Type Emp_index is table of Emp%rowtype index by pls_integer;--

V_emprow emp_index;--defining the EMP line type

V_deptno emp.deptno%type:=10;--Define the department number to query the employee

Begin

Open emp_cur for ' select * from emp where deptno=:d no '

Using V_deptno;

Fetch emp_cur bulk collect into V_emprow;

Close Emp_cur

For x in 1: V_emprow.count Loop

Dbms_output.put_line (' empno: ' | | V_emprow (x). empno| | ', ename: ' | | V_emprow (x). ename| | ', Position: ' | | V_emprow (x). Job);

End Loop;

End;

/

Use dynamic SQL to create subroutines when a dependent object does not exist;

Dynamic SQL mainly uses execute immediate statement to execute DML/DDL/DCL and other statement operations;

If you use a bound variable, you must use the Using clause in the EXECUTE immediate to set the required binding variable;

Use the returning or return statement to receive a query or an updated return result;

Batch processing allows you to save multiple data retrieved from a database in a collection at once, or use ForAll to set multiple binding parameters to dynamic SQL.

Dynamic Display Cursors

Output the number of records for each table under HR or Scott

Declare

Emp_cur Sys_refcursor;

V_emprow Emp%rowtype;

V_deptno emp.deptno%type:=10;

Begin

Open emp_cur for ' select * from emp where deptno=:d no '

Using V_deptno;

Loop

Fetch emp_cur into V_emprow;

Exit when Emp_cur%notfound;

Dbms_output.put_line (' ename: ' | | v_emprow.ename| | ' position: ' | | V_emprow.job);

End Loop;

Close emp_cur;

End

/

Dynamic SQL detailed