Oracle Local Dynamic SQL

Local dynamic SQL

First we should understand what dynamic SQL is, the SQL that we use in the Oracle database Development PL BLOCK

Divided into: Static SQL statements and dynamic SQL statements. The so-called static SQL refers to the SQL statements used in the PL

The translation is clear, and execution is determined by the object. While dynamic SQL refers to SQL statements that are not deterministic at the time of the PL block compilation

, such as different actions depending on the parameters entered by the user. The compiler does not process the dynamic statement parts,

The statement is created dynamically when the program is run, parsing the statement, and executing the statement.

Dynamic SQL in Oracle can be executed by local dynamic SQL or through the Dbms_sql package.

The following two cases are described separately:

First, local dynamic SQL

Local dynamic SQL is implemented using the EXECUTE immediate statement.

1. Local Dynamic SQL Execution DDL statement:

Requirements: Dynamically build tables based on parameters such as table names and field names 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; --Dynamic execution of DDL statements

exception

When others then

Null

End;

These are the stored procedure code that is compiled by. The following executes a stored procedure dynamic build table.

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 (+) Y

Sql>

Here, we have achieved our needs, using local dynamic SQL based on the table name and field name entered by the user,

Parameters such as field types to implement dynamic execution of DDL statements.

2. Local dynamic SQL executes DML statements.

Requirement: Insert the user input value into the Dinya_test table built in the example above.

Create or replace procedure Proc_insert

(

ID in number,--Enter ordinal

Name in Varchar2--Enter name

) as

Str_sql VARCHAR2 (500);

Begin

str_sql:= ' INSERT into dinya_test values (: 1,:2) ';

Execute immediate str_sqlusing id,name; --Dynamic execution of insert operations

exception

When others then

Null

End;

Executes the stored procedure and inserts the data into 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 example above, local dynamic SQL executes DML statements with a using clause that binds the entered values sequentially

variable, if output parameters are required, you can use the returning into clause when executing dynamic SQL.

Such as:

Declare

P_ID number:=1;

V_count number;

Begin

V_string:= ' SELECT COUNT (*) from table_name a where a.id=:id ';

Execute immediate v_stringinto v_count using p_id;

End;

For more information about the return values in dynamic SQL and the execution of parameter patterns for output input binding variables, please

The reader does its own testing.

Second, the use of dbms_sql bag

The steps to implement dynamic SQL using the Dbms_sql package are as follows: A, the SQL statement to be executed first, or a statement

The block is placed into a string variable. B. Use the parse procedure of the Dbms_sql package to parse the string. C, use

Dbms_sql the bind_variable process of the package to bind the variable. D, use the Execute function of the Dbms_sql package to

Line statement.

1. Executing DDL statements with the Dbms_sql package

Requirements: 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

) as

V_cursor number; --Define Cursor

V_string VARCHAR2 (200); --Define string variables

V_row number; --Number of lines

Begin

V_cursor:=dbms_sql.open_cursor; --Open 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); --Analytic statements

V_row:=dbms_sql.execute (V_cursor); --EXECUTE statement

Dbms_sql.close_cursor (V_cursor); --Close cursor

exception

When others then

Dbms_sql.close_cursor (V_cursor); --Close cursor

Raise

End

After the above process is compiled, the execution process creates 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 defaultcomments

---- ------------- -------- ---------------

ID Number (8)

NAME VARCHAR2 (+) Y

Sql>

2. Using the Dbms_sql package to execute DML statements

Requirement: Use the Dbms_sql package to update the corresponding records in the table based on the values entered by the user.

To view an existing record in a table:

Sql> select * from Dinya_test2;

ID NAME

1 Oracle

2 CSDN

3 ERP

Sql>

Build the stored procedure and compile it by:

Create or replace procedure Proc_dbms_sql_update

(

ID number,

Name Varchar2

) as

V_cursor number; --Define Cursor

V_string VARCHAR2 (200); --String variables

V_row number; --Number of lines

Begin

V_cursor:=dbms_sql.open_cursor; --Open 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); --Analytic statements

Dbms_sql.bind_variable (V_cursor, ':p _name ', name); --Binding variables

Dbms_sql.bind_variable (V_cursor, ':p _id ', id); --Binding variables

V_row:=dbms_sql.execute (V_cursor); --Execute Dynamic SQL

Dbms_sql.close_cursor (V_cursor); --Close cursor

exception

When others then

Dbms_sql.close_cursor (V_cursor); --Close cursor

Raise

End

Perform the procedure to update the data in the table 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 executing the procedure, update the data for the name field of the second article to the new value Csdn_dinya. This completes the use of the

Dbms_sql package to execute DML statement functionality.

Using 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, if the dynamic statement to be executed is a query statement, use the

Dbms_sql.define_column define the output variable, and then use Dbms_sql. Execute,

Dbms_sql. Fetch_rows, Dbms_sql. Column_value and Dbms_sql. Variable_value to execute the query

and get the results.

Oracle Local Dynamic SQL