Execute dynamic SQL in Oracle

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 ) as 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 stored procedure compiledCode. 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>

at this point, our requirements are met, use local dynamic SQL statements to 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 -- enter the name ) as 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. 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 preceding process is compiled, run the following command to create a 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 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, run the command dbms_ SQL .execute or statement. If you want to run a dynamic statement as a query statement, use dbms_ SQL .define_column to define the output variable, and then use dbms_ SQL .execute, locate, dbms_ SQL .column_value and values to execute the query and.

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.