How to use dynamic SQL in PL/SQL development _ MySQL

Abstract: In the PLSQL development process, the use of SQL and PLSQL can meet most of the requirements, but in some special circumstances, using standard SQL statements or DML statements in PLSQL cannot meet your needs. for example, dynamic table creation or an uncertain operation must be performed dynamically. This requires dynamic SQL. This article describes PLSQL in detail through several examples.

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: windows 2000 Professional (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 EXECUTE IMMEDIATE 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.

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
)
Str_ SQL varchar2 (500 );
Begin
Str_ SQL: = 'create table' | table_name | '(' | field1 | ''| datatype1 | ', '| field2 | ''| datatype2 | ')';
Execute immediate str_ SQL; -- dynamically execute DDL statements
Exception
When others then
Null;
End;

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

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 (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.

Create or replace procedure proc_insert
(
Id in number, -- enter the serial number
Name in varchar2 -- Input name
)
Str_ SQL varchar2 (500 );
Begin
Str_ SQL: = 'Insert into dinya_test values (: 1,: 2 )';
Execute immediate str_ SQL using id, name; -- dynamically execute the insert operation
Exception
When others then
Null;
End;

Execute the stored procedure and insert the data to 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 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 returning into clause, for example:

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

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
)
V_cursor number; -- defines the cursor
V_string varchar2 (200); -- defines string variables
V_row number; -- number of rows
Begin
V_cursor: = dbms_ SQL .open_cursor; -- open the 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); -- Analysis statement
V_row: mongodbms_ SQL .exe cute (v_cursor); -- execute the 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 above process is compiled, execute the process to create 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 (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 * from dinya_test2;
ID NAME
1 Oracle
2 CSDN
3 ERP
SQL>

Create a stored procedure and compile it as follows:

Create or replace procedure proc_dbms_ SQL _update
(
Id number,
Name varchar2
)
V_cursor number; -- defines the cursor
V_string varchar2 (200); -- string variable
V_row number; -- number of rows
Begin
V_cursor: = dbms_ SQL .open_cursor; -- open the 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); -- 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
When others then
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> 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 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.