Using method of dynamic SQL in PL/SQL development

Dynamic Content Summary: In the Pl/sql development process, the use of sql,pl/sql can achieve most of the requirements, but in some special cases, the use of standard SQL statements or DML statements in Pl/sql can not achieve their own needs, For example, the need to dynamically build a table or an indeterminate operation requires dynamic execution. This needs to be implemented using dynamic SQL. This article through several examples to explain the use of dynamic SQL in detail.

This article is suitable for reader scope: Oracle Primary, Intermediate

System environment:

Os:windows Professional (English edition)

oracle:8.1.7.1.0

Body:

In general Pl/sql programming, SQL can be used directly in DML and transaction control statements, but DDL statements and system control statements cannot be used directly in Pl/sql, and in order to realize the use of DDL statements and system control statements in Pl/sql, they can be implemented by using dynamic SQL.

First we should understand what is dynamic SQL, in the Oracle database development Pl/sql Block We use the SQL is divided into: Static SQL statements and dynamic SQL statements. The so-called static SQL refers to the SQL statement used in the Pl/sql block is clear at compile time, and the execution is to determine the object. Dynamic SQL means that SQL statements are indeterminate when pl/sql blocks are compiled, such as performing different operations depending on the parameters entered by the user. The compiler does not process the dynamic statement part, simply creates the statement dynamically when the program is run, parsing the statement, and executing the statement.

Dynamic SQL in Oracle can be executed either through local dynamic SQL or through a dbms_sql package. The following two situations are described separately:

One, local dynamic SQL

Local dynamic SQL is implemented using the EXECUTE IMMEDIATE statement.

1. Local Dynamic SQL Execution DDL statement:

Requirements: Based on the user Input table name and field name and other parameters dynamic table.

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; --Dynamically executing DDL statements
exception
When others then
Null
End;

These are the stored procedure codes that are compiled through. 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 requirements, using local dynamic SQL to implement the dynamic execution of DDL statements based on parameters such as the table name and field name entered by the user, field type, and so on.

2, local dynamic SQL execution DML statement.

Requirements: Inserts the value entered by the user into the Dinya_test table built in the previous example.

Create or replace procedure Proc_insert
(
ID in number,--input 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; --Perform insert operations dynamically
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 a DML statement using a using clause, which binds the input value 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 value in dynamic SQL and the mode of executing parameters for the output input binding variable, ask the reader to do the test on their own.

Second, the use of Dbms_sql package

The steps for implementing dynamic SQL using the Dbms_sql package are as follows: A, the first SQL statement to execute or a block of statements is placed in a string variable. B, use the parse procedure of the Dbms_sql package to parse the string. C, use the Dbms_sql package bind_variable process to bind variables. D, use the Execute function of the Dbms_sql package to execute the statement.

1. Use Dbms_sql package to execute DDL statement

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

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 the cursor
V_string VARCHAR2 (200); --Define string variables
V_row number; --Number of rows
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); --Parsing statements
V_row:=dbms_sql.execute (V_cursor); --EXECUTE statement
Dbms_sql.close_cursor (V_cursor); --Close the cursor
exception
When others then
Dbms_sql.close_cursor (V_cursor); --Close the cursor
Raise
End

After the process has been 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. Use Dbms_sql package to execute DML statements

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

To view existing records in a table:

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

Build the stored procedure and compile it through:

Create or replace procedure Proc_dbms_sql_update
(
ID number,
Name Varchar2
) as
V_cursor number; --Define the cursor
V_string VARCHAR2 (200); --String variable
V_row number; --Number of rows
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); --Parsing 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 the cursor
exception
When others then
Dbms_sql.close_cursor (V_cursor); --Close the cursor
Raise
End

Executes 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>

Updates the Name field of the second article to the new value Csdn_dinya after the procedure is executed. This completes the function of using the Dbms_sql package to perform 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 you want to execute a dynamic statement that 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. It should be noted, however, that the use of dynamic SQL to execute DDL statements in a pl/sql block is different from the use of binding variables in DDL (bind_variable (v_cursor, ':p _name ', name), and does not need to be performed after parsing Dbms_ Sql. Bind_variable, add the input variable directly to the string. In addition, the DDL is invoked in the Dbms_sql. Parse when executed, so dbms_sql. Execute also can not use, namely in the example of the V_row:=dbms_sql.execut