How to use dynamic SQL in PL/SQL development

In general PL/SQL programming, SQL can be used directly in DML and transaction-controlled statements, but DDL statements and system control statements cannot be used directly in PL/SQL, and it is possible to implement DDL statements and system control statements in PL/SQL by using dynamic SQL.

First, we should understand what dynamic SQL is, and the SQL we use in the Oracle database Development PL block is divided into: Static SQL statements and dynamic SQL statements. The so-called static SQL refers to SQL statements that are used in a PL/S block that are explicit at compile time and are executed to determine the object. Dynamic SQL refers to SQL statements that are not deterministic at the time of the PL block compilation, such as performing different operations depending on the parameters entered by the user. The compiler does not process the dynamic statement part, but creates the statement dynamically, parses the statement, and executes the statement while the program is running.

Dynamic SQL in Oracle can be executed by local dynamic SQL or through the Dbms_sql package. The following two cases are described separately:

first, the local dynamic SQL

Local dynamic SQL is implemented using the EXECUTE IMMEDIATE statement.

1 , local dynamic SQL Execute DDL statement:

Requirements: Dynamically build tables based on parameters such as table names and field names entered by the user.

Create or replace procedure Proc_test
(
TABLE_NAME in VARCHAR2,--table name
Field1 in Varchar2,--field name
Datatype1 in Varchar2,--field type
Field2 in Varchar2,--field name
Datatype2 in Varchar2--field type
) as
Str_sql VARCHAR2 (500);
Begin
str_sql:= ' CREATE table ' | | table_name| | ' (' | | field1| | ' ' | | datatype1| | ', ' | | field2| | ' ' | | datatype2| | ') ';
Execute immediate str_sql; --Dynamic execution of DDL statements
exception
When others then
Null
End;

These are the stored procedure code that is compiled by. The following executes a stored procedure dynamic build table.

Sql> Execute proc_test (' dinya_test ', ' id ', ' number (8) NOT null ', ' name ', ' varchar2 (100) ');

PL/SQL procedure successfully completed

sql> desc dinya_test;
Name Type Nullable Default Comments
---- ------------- -------- ------- --------
ID Number (8)

NAME VARCHAR2 (+) Y

Sql>

Here, we have achieved our needs by using local dynamic SQL to implement DDL statements dynamically based on the table name and field name, field type, and other parameters entered by the user.

2 , local dynamic SQL Execute DML statement.

Requirement: Insert the user input value into the Dinya_test table built in the example above.

Create or replace procedure Proc_insert
(
ID in number,--Enter ordinal
Name in Varchar2--Enter name
) as
Str_sql VARCHAR2 (500);
Begin
str_sql:= ' INSERT into dinya_test values (: 1,:2) ';
Execute immediate str_sql using Id,name; --Dynamic execution of insert operations
exception
When others then
Null
End;

Executes the stored procedure and inserts the data into the test table.

Sql> Execute Proc_insert (1, ' Dinya ');
PL/SQL procedure successfully completed
Sql> select * from Dinya_test;
ID NAME
1 Dinya

In the example above, local dynamic SQL executes DML statements using a using clause that binds the input values to a variable sequentially, and if an output parameter is required, you can use the returning into clause when executing dynamic SQL, such as:

Declare
P_ID number:=1;
V_count number;
Begin
V_string:= ' SELECT COUNT (*) from table_name a where a.id=:id ';
Execute immediate v_string into V_count using p_id;
End;

For more information about the return values in dynamic SQL and the execution of parameter patterns for output input binding variables, the reader should do their own testing.

Second, use Dbms_sql Package

The steps to implement dynamic SQL using the Dbms_sql package are as follows: A, the SQL statement to be executed, or a block of statements to be placed in a string variable. B. Use the parse procedure of the Dbms_sql package to parse the string. C. Use the bind_variable procedure of the Dbms_sql package to bind the variable. D. Execute the statement using the Execute function of the Dbms_sql package.

1 , using Dbms_sql Package Execution DDL statement

Requirements: Use the Dbms_sql package to create a table based on the table name, field name, and field type entered by the user.

Create or replace procedure Proc_dbms_sql
(
TABLE_NAME in VARCHAR2,--table name
Field_name1 in Varchar2,--field name
Datatype1 in Varchar2,--field type
Field_name2 in Varchar2,--field name
Datatype2 in Varchar2--field type
) as
V_cursor number; --Define Cursor
V_string VARCHAR2 (200); --Define string variables
V_row number; --Number of lines
Begin
V_cursor:=dbms_sql.open_cursor; --Open cursor for processing
v_string:= ' CREATE table ' | | table_name| | ' (' | | field_name1| | ' ' | | datatype1| | ', ' | | field_name2| | ' ' | | datatype2| | ') ';
Dbms_sql.parse (v_cursor,v_string,dbms_sql.native); --Analytic statements
V_row:=dbms_sql.execute (V_cursor); --EXECUTE statement
Dbms_sql.close_cursor (V_cursor); --Close cursor
exception
When others then
Dbms_sql.close_cursor (V_cursor); --Close cursor
Raise
End

After the above process is compiled, the execution process creates the table structure:

Sql> Execute proc_dbms_sql (' dinya_test2 ', ' id ', ' number (8) NOT null ', ' name ', ' varchar2 (100) ');

PL/SQL procedure successfully completed

sql> desc Dinya_test2;
Name Type Nullable Default Comments
---- ------------- -------- ------- --------
ID Number (8)
NAME VARCHAR2 (+) Y

Sql>

2 , using Dbms_sql Package execution DML statement

Requirement: Use the Dbms_sql package to update the corresponding records in the table based on the values entered by the user.

To view an existing record in a table:

Sql> select * from Dinya_test2;
ID NAME
1 Oracle
2 CSDN
3 ERP
Sql>

Build the stored procedure and compile it by:

Create or replace procedure Proc_dbms_sql_update
(
ID number,
Name Varchar2
) as
V_cursor number; --Define Cursor
V_string VARCHAR2 (200); --String variables
V_row number; --Number of lines
Begin
V_cursor:=dbms_sql.open_cursor; --Open cursor for processing
v_string:= ' Update Dinya_test2 a set a.name=:p _name where a.id=:p _id ';
Dbms_sql.parse (v_cursor,v_string,dbms_sql.native); --Analytic statements
Dbms_sql.bind_variable (V_cursor, ':p _name ', name); --Binding variables
Dbms_sql.bind_variable (V_cursor, ':p _id ', id); --Binding variables
V_row:=dbms_sql.execute (V_cursor); --Execute Dynamic SQL
Dbms_sql.close_cursor (V_cursor); --Close cursor
exception
When others then
Dbms_sql.close_cursor (V_cursor); --Close cursor
Raise
End

Perform the procedure to update the data in the table based on the parameters entered by the User:

Sql> Execute proc_dbms_sql_update (2, ' Csdn_dinya ');

PL/SQL procedure successfully completed

Sql> select * from Dinya_test2;
ID NAME
1 Oracle
2 Csdn_dinya
3 ERP
Sql>

After executing the procedure, update the data for the name field of the second article to the new value Csdn_dinya. This completes the ability to use the Dbms_sql package to execute DML statements.

Using Dbms_sql, if the dynamic statement to be executed is not a query statement, use Dbms_sql. Execute or Dbms_sql. Variable_value to execute, if the dynamic statement to execute is a query statement, use Dbms_sql.define_column to define the output variable, and then use Dbms_sql. Execute, Dbms_sql. Fetch_rows, Dbms_sql. Column_value and Dbms_sql. Variable_value to execute the query and get the results.

Summary description:

During the Oracle development process, we can use dynamic SQL to execute DDL statements, DML statements, transaction control statements, and system control statements. However, it is important to note that when using dynamic SQL to execute DDL statements in PL/SQL blocks, it is not necessary to use a binding variable in the DDL (bind_variable (v_cursor, ':p _name ', name)), which does not need to be executed after parsing dbms_ Sql. Bind_variable, add the input variable directly to the string. In addition, the DDL is called Dbms_sql. Parse when executed, so dbms_sql. Execute can also be used, i.e. in the V_row:=dbms_sql.execute (v_cursor) section of the example above.

How to use dynamic SQL in PL/SQL development