How to perform simple dynamic queries in Oracle stored procedures

Do a simple dynamic query code in a stored procedure, such as:

CREATE OR REPLACE Procedure Zxm_sb_gz_get

(P_table in Varchar2,

P_name in Varchar2,

P_value in Varchar2,

Outpara out Lntxdba.zxm_pag_cs_power.c_type

）

As

Begin

Declare

Wherevalue VARCHAR2 (200);

Begin

Wherevalue:=select * from | | p_table| | where | | p_name| | =|| P_value;

Open Outpara for

Wherevalue;

End

End

In general Pl/sql programming, SQL can be used directly in DML and transaction control statements, but DDL statements and system control statements cannot be used directly in Pl/sql, and in order to realize the use of DDL statements and system control statements in Pl/sql, they can be implemented by using dynamic SQL.

First we should understand what is dynamic SQL, in the Oracle database development Pl/sql Block We use the SQL is divided into: Static SQL statements and dynamic SQL statements. The so-called static SQL refers to the SQL statement used in the Pl/sql block is clear at compile time, and the execution is to determine the object. Dynamic SQL means that SQL statements are indeterminate when pl/sql blocks are compiled, such as performing different operations depending on the parameters entered by the user. The compiler does not process the dynamic statement part, simply creates the statement dynamically when the program is run, parsing the statement, and executing the statement.

Dynamic SQL in Oracle can be executed either through local dynamic SQL or through a dbms_sql package. The following two situations are described separately:

One, local dynamic SQL

Local dynamic SQL is implemented using the EXECUTE IMMEDIATE statement.

1. Local Dynamic SQL Execution DDL statement:

Requirements: Based on the user Input table name and field name and other parameters dynamic table.

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 executing DDL statements

exception

When others then

Null

End;