About dynamic SQL use
Author: dinya
Summary: During the PL / SQL development process, use SQL, PL / SQL can achieve most of the needs, but in some special cases, using standard SQL statements or DML statements in PL / SQL can not implement themselves Demand, such as dynamic building forms or an uncertain operation requires dynamic execution. This needs to be implemented using dynamic SQL. This article uses several examples to explain the use of dynamic SQL.
This article is suitable for readers: Oracle primary, intermediate
System environment:
OS: Windows 2000 Professional (English)
Oracle: 8.1.7.1.0
Text:
In a general PL / SQL programming, SQL can be used directly in DML and transaction-controlled statements, but the DDL statement and system control statements cannot be used directly in PL / SQL. To implement DDL statements in PL / SQL And system control statements can be implemented by using dynamic SQL.
First we should know what is dynamic SQL, in the Oracle Database Development PL / SQL block we use SQL divided into: static SQL statement and dynamic SQL statements. The so-called static SQL refers to the SQL statement used in the PL / SQL block, which is clear when compiling, and executes the identifiable object. Dynamic SQL means that the SQL statement is uncertain when the PL / SQL block is compiled, such as performing different operations depending on the parameters input by the user. The compiler does not process the dynamic statement section, just dynamically created a statement when the program is run, and the statement is syntax and executes the statement.
Dynamic SQL in Oracle can be performed by local dynamic SQL, or can be performed through the DBMS_SQL package. Below, these two situations are described separately:
First, local dynamic SQL
Local dynamic SQL is implemented using an Execute Immediate statement.
1. Local dynamic SQL execute DDL statement:
Demand: Dynamic Scheduling table according to the parameters such as the table name and field name of the user.
Create or Replace Procedure Proc_test (Table_name In VARCHAR2, - Table name Field1 in varcha2, - field name DataType1 in varcha2, field type FIELD2 in varcha2, - field name DataType2 in varchar2 - field type) AS STR_SQL VARCHAR2 ( 500); begin str_sql: = 'create table' || Table_name || '(' || Field1 || '|| Dattype1 ||', '|| Field2 ||' || Dattype2 || ')'; Execute immediate str_sql; - Dynamic execution DDL statement Exception when others the null;
The above is the stored procedure code that is compiled. The stored procedure dynamic score table is performed below.
SQL> execute proc_test ( 'dinya_test', 'id', 'number (8) not null', 'name', 'varchar2 (100)'); PL / SQL procedure successfully completedSQL> desc dinya_test; Name Type Nullable Default Comments - --- -------------------- -------------- ID Number (8) Name varchar2 (100) ysql >
Here, we have realized our needs, using local dynamic SQL according to the table name and field name, field type, etc., and the field type and other parameters are dynamically executed DDL statement.
2, local dynamic SQL perform DML statements.
Demand: Insert the value of the user input into the DINYA_TEST table built in the above example.
create or replace procedure proc_insert (id in number, - number entry 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; - Dynamic Perform Insert Exception When Others Ten Null; END; Execute a stored procedure, insert data into the test table.
SQL> EXECUTE PROC_INSERT (1, 'Dinya'); PL / SQL Procedure SuccessFully CompletedSql> Select * from Dinya_test; ID Name 1 Dinya
In the above example, the local dynamic SQL uses the USING clause when the DML statement is executed, and the input value will be bound to the variable in order. If the output parameter is required, it can be used when performing dynamic SQL, using the return INTO clause, 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_STRING INTO V_COUNT Using P_ID; END;
For more questions about the return value in dynamic SQL and perform parameter mode for output input, please do your test.
Second, use dbms_sql package
The steps to implement dynamic SQL using the DBMS_SQL package are as follows: a, first place the SQL statement or a statement block to be executed in a string variable. B, use the PARSE process of the DBMS_SQL package to analyze the string. C, bind variables using the bind_variable process of the dbms_sql package. D, use the execute function of the dbms_sql package to perform the statement.
1. Use the DBMS_SQL package to execute the DDL statement.
Demand: Use the dbms_sql package to build a table according to the table name, field name, and field type of the user.
create or replace procedure proc_dbms_sql (table_name in varchar2, - table field_name1 in varchar2, - the field name datatype1 in varchar2, - field type field_name2 in varchar2, - the field name datatype2 in varchar2 - field type) as v_cursor number; - Define the cursor v_string varchar2 (200); - Define string variable v_row number; - Route begin v_cursor: = dbms_sql.open_cursor; - Top Open Cursor v_string: = 'CREATE TABLE' || Table_name || ' ('|| Field_name1 ||' '|| Dattype1 ||', '|| Field_name2 ||' '|| Dattype2 ||') '; DBMS_SQL.PARSE (v_cursor, v_string, dbms_sql.native); - Analysis Schemrate v_row: = dbms_sql.execute (v_cursor); - Execute statement dbms_sql.close_cursor (v_cursor); - Turn off the cursor exception when others the dbms_sql.close_cursor (v_cursor); - Turn off the cursor Raise; END; After compiling the above process, Execution process creation table structure:
SQL> execute proc_dbms_sql ( 'dinya_test2', 'id', 'number (8) not null', 'name', 'varchar2 (100)'); PL / SQL procedure successfully completedSQL> desc dinya_test2; Name Type Nullable Default Comments - --- -------------------- -------------- ID Number (8) Name varchar2 (100) y SQL>
2. Use the DBMS_SQL package to execute the DML statement.
Demand: Use the dbms_sql package to update the record corresponding to the table according to the value input by the user.
Viewing the record in the table:
SQL> Select * from dinya_test2; id name 1 Oracle 2 9CBS 3 ERPSQL>
Built the storage process and compile:
create or replace procedure proc_dbms_sql_update (id number, name varchar2) as v_cursor number; - defined cursor v_string varchar2 (200); - a string variable v_row number; - the number of rows begin v_cursor: = dbms_sql.open_cursor; - to process Open the cursor 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); - Analyze statement dbms_sql.bind_variable (v_cursor, ': p_name ', name); - Binding variable dbms_sql.bind_variable (v_cursor,': p_id ', id); - Binding variable v_row: = dbms_sql.execute (v_cursor); - Execute dynamic SQL DBMS_SQL.CLOSE_CURSOR (v_cursor ); - Turn off the cursor exception when othersor (v_cursor); - Turn off the cursor raise; end; execution process, update the data in the table according to the parameter input by the user:
SQL> EXECUTE PROC_DBMS_SQL_UPDATE (2, '9CBS_DINYA'); PL / SQL Procedure SuccessFully CompletedSql> Select * from Dinya_test2; ID Name 1 Oracle 2 9CBS_DINYA 3 ERPSQL>
Update the data of the Name field of the second Name field to the new value 9cbs_dinya after the execution process. This completes the function of executing the DML statement using the DBMS_SQL package.
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 you want to perform a dynamic statement is query statement, you should 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 perform queries and get results.
Summary Description:
During Oracle Development, we can use dynamic SQL to perform DDL statements, DML statements, transaction control statements, and system control statements. However, it should be noted that when using dynamic SQL blocks in the PL / SQL block, when using DDL statements, the use of bind variables in DDL is illegal (bind_variable, ': p_name', name), after analysis You don't need to execute dbms_sql.bind_variable, you can directly add the input variables to the string. In addition, DDL is executed when the dbms_sql.parse is called, so dbms_sql.execute can also not be used, that is, the V_ROW: = dbms_sql.execute (v_cursor) portion in the above example may not. This article you can find on the author's Blog, please visit the author's Blog.
Author
:
Dinya20@tom.com
Author blog: http://blog.9cbs.net/dinya2003/
For reprint, keep the author's Blog address information.
