How to use dynamic SQL in PL/SQL development (1)

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 in local dynamic SQL statements

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.