How to use dynamic SQL in oracle Development

How to use dynamic SQL in oracle development in general PL/SQL programming, SQL can be directly used in DML and transaction control statements, however, 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 statements. 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 following two cases are described respectively: 1. Local dynamic SQL local dynamic SQL is implemented using the EXECUTE IMMEDIATE statement. 1. Execute DDL statements in a local dynamic SQL statement: required: 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 Statement exception when others then null; end; the above is the Stored Procedure Code compiled. 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> here, the local dynamic SQL statement is used 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 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 to execute DML statements in local dynamic SQL statements, and the input values are bound to variables in sequence. If you need to output parameters, you can use the returning into clause when executing dynamic SQL statements, 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 returning into v_count using p _ Id; end; 2. Use the DBMS_ SQL package to use the DBMS_ SQL package to implement dynamic SQL as follows: 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: 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 partition (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; -- defines the cursor v_string varchar2 (200); -- defines the string variable v_row number; -- the number of rows begin v_cursor: = dbms_ SQL .open_cursor; -- opens the cursor v_string for processing: = 'create table' | ''| table_name | '( '| Field_name1 | ''| datatype1 |', '| field_name2 |'' | datatype2 |') '; dbms_ SQL .parse (v_cursor, v_string, dbms_ SQL .native); -- Analysis Statement v_row: = dbms_ SQL. execute (v_cursor); -- execute the statement. when a dynamic SQL statement is executed, dbms_ SQL .close_cursor (v_cursor) can be left empty; -- close the cursor exception when others then dbms_ SQL .close_cursor; 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. Use the DBMS_ SQL package to execute DML statements: use the DBMS_ SQL package to update the corresponding records in the table based on user input values. View the existing records in the Table: SQL> select * from dinya_test2; id name 1 Oracle 2 CSDN 3 ERPSQL> create a stored procedure and compile it using: create or replace procedure proc_dbms_ SQL _update (id number, name varchar2) as 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; -- opens the cursor v_string for processing: = 'Update dinya_test2 a set. name =: p_name where. id =: p_id '; dbms_ SQL .parse (v_cu Rsor, v_string, dbms_ SQL .native); -- Analysis Statement dbms_ SQL .bind_variable (v_cursor, ': p_name', name); -- Bind Variable dbms_ SQL .bind_variable (v_cursor, ': p_id', id ); -- bind the variable 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; Execution Process, update table data based on user input parameters: 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 ERPSQL> update the data of the second name field to the new value csdn_dinya after execution. 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.