How to use dynamic SQL in PL/SQL Development

Abstract: In the PL/SQL development process, SQL, PL/SQL can be used to meet most of the requirements. However, in some special cases, standard SQL statements or DML statements in PL/SQL cannot meet your needs. For example, dynamic table creation or an uncertain operation must be performed dynamically. This requires dynamic SQL. This article describes how to use dynamic SQL through several examples.

This article is intended for: Oracle elementary and intermediate

System Environment:

OS: windows2000Professional (English version)

Oracle: 8.1.7.1.0

Body:

In general PL/SQL programming, SQL can be directly used in DML and transaction control statements, but DDL statements and system control statements cannot be directly used in PL/SQL, to use DDL statements and system control statements in PL/SQL, you can use dynamic SQL.

First, we should know what dynamic SQL is. in Oracle Database Development, PL/SQL blocks use static SQL statements and dynamic SQL statements. Static SQL indicates that the SQL statements used in PL/SQL blocks are clear during compilation and the objects are determined. Dynamic SQL is an SQL statement that is uncertain during PL/SQL block compilation. For example, you can perform different operations based on the parameters you enter. The Compiling Program does not process the dynamic statement, but dynamically creates a statement, performs syntax analysis on the statement, and executes the statement when the program is running.

Dynamic SQL statements in Oracle can be executed either through local dynamic SQL statements or through the DBMS_ SQL package. The two cases are described as follows:

I. Local dynamic SQL

Local dynamic SQL statements are implemented using the EXECUTEIMMEDIATE statement.

1. Execute DDL statements using local dynamic SQL:

Requirement: dynamically create a table based on parameters such as the table name and field name entered by the user (the user must have the create any tables permission ).

Createorreplaceprocedureproc_test
(
Table_nameinvarchar2, -- table name
Field1invarchar2, -- field name
Datatype1invarchar2, -- field type
Field2invarchar2, -- field name
Datatype2invarchar2 -- field type
)
Str_sqlvarchar2 (500 );
Begin
Str_ SQL: = 'createtable' | table_name | '(' | field1 | ''| datatype1 | ', '| field2 | ''| datatype2 | ')';
Executeimmediatestr_ SQL; -- dynamically execute DDL statements
Exception
Whenothersthen
Null;
End;

The above is the compiled stored procedure code. The following describes how to dynamically create a table in the stored procedure.

SQL> executeproc_test ('dinya _ test', 'id', 'Number (8) notnull ', 'name', 'varchar2 (100 )');

PL/SQLproceduresuccessfullycompleted

SQL> descdinya_test;
NameType nullabledefacommcomments
----------------------------------------
Id number (8)

NAMEVARCHAR2 (100) Y

SQL>

By now, we have achieved our needs. Using local dynamic SQL statements, we can dynamically execute DDL statements based on the table name, field name, field type, and other parameters you enter.

2. Execute DML statements using local dynamic SQL statements.

Requirement: insert the value entered by the user into the dinya_test table created in the preceding example.

Createorreplaceprocedureproc_insert
(
Idinnumber, -- enter the serial number
Nameinvarchar2 -- input name
)
Str_sqlvarchar2 (500 );
Begin
Str_ SQL: = 'insertintodinya _ testvalues (: 1,: 2 )';
Executeimmediatestr_sqlusingid, name; -- dynamically execute the insert operation
Exception
Whenothersthen
Null;
End;

Execute the stored procedure and insert the data to the test table.

SQL> executeproc_insert (1, 'dinya ');
PL/SQLproceduresuccessfullycompleted
SQL> select * fromdinya_test;
ID NAME
1 dinya

In the preceding example, the using clause is used when the local dynamic SQL statement executes the DML statement, and the input values are bound to the variables in order. If you need to output parameters, You can execute the dynamic SQL statement, use the RETURNINGINTO clause, for example:

Declare
P_idnumber: = 1;
V_countnumber;
Begin
V_string: = 'selectcount (*) fromtable_nameawherea.id =: id ';
Executeimmediatev_string1_v_countusingp_id;
End;

For more information about the dynamic SQL statements about the return value and the mode of binding variables to output input to execute the parameters, please test it on your own.

2. Use the DBMS_ SQL package

Follow these steps to use the DBMS_ SQL package to implement dynamic SQL: A. Place the SQL statement or block to A string variable. B. Use the parse process of the DBMS_ SQL package to analyze the string. C. bind_variable of the DBMS_ SQL package is used to bind variables. D. Run the statement using the execute function of the DBMS_ SQL package.

1. Use the DBMS_ SQL package to execute DDL statements

Requirement: Use the DBMS_ SQL package to create a table based on the table name, field name, and field type entered by the user.

Createorreplaceprocedureproc_dbms_ SQL
(
Table_nameinvarchar2, -- table name
Field_name1invarchar2, -- field name
Datatype1invarchar2, -- field type
Field_name2invarchar2, -- field name
Datatype2invarchar2 -- field type
)
V_cursornumber; -- Define the cursor
V_stringvarchar2 (200); -- defines string variables
V_rownumber; -- number of rows
Begin
V_cursor: = dbms_ SQL .open_cursor; -- open the cursor for processing
V_string: = 'createtable' | table_name | '(' | field_name1 | ''| datatype1 | ', '| field_name2 | ''| datatype2 | ')';
Dbms_ SQL .parse (v_cursor, v_string, dbms_ SQL .native); -- Analysis Statement
V_row: mongodbms_ SQL .exe cute (v_cursor); -- execute the statement
Dbms_ SQL .close_cursor (v_cursor); -- close the cursor
Exception
Whenothersthen
Dbms_ SQL .close_cursor (v_cursor); -- close the cursor
Raise;
End;

After the above process is compiled, execute the process to create the table structure:

SQL> executeproc_dbms_ SQL ('dinya _ test2', 'id', 'Number (8) notnull ', 'name', 'varchar2 (100 )');

PL/SQLproceduresuccessfullycompleted

SQL> descdinya_test2;
NameType nullabledefacommcomments
----------------------------------------
Id number (8)
NAMEVARCHAR2 (100) Y

SQL>

2. Run the DML statement using the DBMS_ SQL package

Requirement: Use the DBMS_ SQL package to update the corresponding records in the table based on user input values.

View existing records in the table:

SQL> select * fromdinya_test2;
IDNAME
1 Oracle
2 CSDN
3ERP
SQL>

Create a stored procedure and compile it as follows:

Createorreplaceprocedureproc_dbms_ SQL _update
(
Idnumber,
Namevarchar2
)
V_cursornumber; -- Define the cursor
V_stringvarchar2 (200); -- string variable
V_rownumber; -- number of rows
Begin
V_cursor: = dbms_ SQL .open_cursor; -- open the cursor for processing
V_string: = 'updatedinya _ test2aseta. name =: p_namewherea.id =: p_id ';
Dbms_ SQL .parse (v_cursor, v_string, dbms_ SQL .native); -- Analysis Statement
Dbms_ SQL .bind_variable (v_cursor, ': p_name', name); -- bind a variable
Dbms_ SQL .bind_variable (v_cursor, ': p_id', id); -- bind a variable
V_row: mongodbms_ SQL .exe cute (v_cursor); -- execute dynamic SQL
Dbms_ SQL .close_cursor (v_cursor); -- close the cursor
Exception
Whenothersthen
Dbms_ SQL .close_cursor (v_cursor); -- close the cursor
Raise;
End;

During the execution process, data in the table is updated based on the parameters entered by the user:

SQL> executeproc_dbms_ SQL _update (2, 'csdn _ dinya ');

PL/SQLproceduresuccessfullycompleted

SQL> select * fromdinya_test2;
IDNAME
1 Oracle
2csdn_dinya
3ERP
SQL>

After the execution, update the data of the second name field to the new value csdn_dinya. This completes the function of using the dbms_ SQL package to execute DML statements.

In 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 it. To execute a dynamic statement, use DBMS_ SQL .define_column to define the output variable, then use DBMS_ SQL .Execute, DBMS_ SQL .Fetch_Rows, DBMS_ SQL .Column_Value, and DBMS_ SQL .Variable_Value to execute the query and obtain the result.
 
Summary:

During Oracle development, we can use dynamic SQL to execute DDL statements, DML statements, transaction control statements, and system control statements. However, when dynamic SQL statements are used in PL/SQL blocks to execute DDL statements, the Bind Variable in DDL statements is invalid (bind_variable (v_cursor ,': p_name ', name), after analysis, you do not need to execute DBMS_ SQL .Bind_Variable, directly add the input variable to the string. In addition, DDL is executed when DBMS_ SQL .PARSE is called, so DBMS_ SQL .EXECUTE can also be used, that is, the v_row: mongodbms_ SQL .exe cute (v_cursor) section in the preceding example can be omitted.