Hey, Oracle, "4."

Use of dynamic SQL

Oracle is actually better than SQL Server, but it's definitely not as strong as it is legendary. This is the feeling of Oracle in these days. It seems that a lot of things are so much better to listen to than to see the perfect. And then the first sentence, Oracle's dynamic SQL is great, I learned a little bit about it. The records are as follows.

Let's say what dynamic SQL is, and look at this familiar:
SELECT * from a_table where a_variable=a_declarevalue;
To write a dynamic sentence again:
SELECT * from a_table where a_variable=:a_dynamicvalue;
The difference between the two sentences is obvious, and the latter is a placeholder, and a variable starting with a colon can flexibly execute a where statement of different conditions.
This is the advantage of a dynamic SQL statement, and the next feature is its own effort--executing the DDL,DCL statement.

Execution of Dynamic SQL
1 EXECUTE Immediate Statement

EXECUTE IMMEDIATE dynamic_string

[Into {define_variable[,define_variable]...| record}]

[USING [In | Out | In Out] bind_argument[,[in | Out/In out] bind_argumnet]

[{returning | return} into bing_argument[,bind_argument];

Here's what it's used for

Handling DDL Operations (CREATE,ALTER,DROP)

CREATE OR REPLACE PROCEDURE drop_table (table_name VARCHAR2)

Is

Sql_statemet VARCHAR2 (100);

BEGIN

sql_statement:= ' DROP TABLE ' | | table_name;

EXECUTE IMMEDIATE sql_statement;

End;

/

After establishing the process drop_table, the following is called:

sql> exec drop_table (' worker ')



Handling DCL Operations (GRANT REVOKE)

Sql> Conn System/manager

CREATE OR REPLACE PROCEDURE grant_sys_priv (priv varchar2,username VARCHAR2)

Is

Sql_stat VARCHAR2 (100);

BEGIN

sql_stat:= ' GRANT ' | | priv| | ' To ' | | Username

EXECUTE IMMEDIATE Sql_stat;

End;

/

Call

sql> exec Grant_sys_priv (' CREATE session ', ' SCOTT ')



Handling DML Operations (INSERT UPDATE DELETE)

If the DML statement has a placeholder, then the using clause is in the E i statement
If the DML statement has a returning clause, then the e i statement should have a returninginto clause

example, processing a single-line query:
DECLARE
Sql_stat VARCHAR2 (100);
Emp_record Tbl%rowtype;
BEGIN
Sql-stat:= ' SELECT * from tbl WHERE tblno=:no ';
EXECUTE IMMEDIATE sql_stat into Emp_record USING &1;
Dbms_output.put_line (emp_record.ename| | Emp_record.sal);
End;
/

2 using Open-for,fetch and close statements to handle multiple-line queries

To dynamically process a SELECT statement step: Define cursor-> open cursor-> loop FETCH data-> close cursor
Defined:
TYPE CursorType is REF CURSOR;
Cursor_variable CursorType;
Open it:
OPEN cursor_variable for Dynamic_string
[USING bind_argument[,bing_argument] ...];
Extraction:
FETCH cursor_variable into {var1[,var2]...| Recor_var};
Shut down:
Close cursor_variable;

Show names and wages for specific department employees
DECLARE
TYPE Empcurtype is REF CURSOR;
Emp_cs Empcurtype;
Emp_record Emptable%rowtype;
Sql_stat VARCHAR2 (100);
BEGIN
Sql_stat:= ' select * from emptable where deptno=:d no ';
OPEN Emp_cs for Sql_stat USING &dno;
LOOP
FETCH Emp_cs into Emp_record;
EXIT when Emp_cs%notfound;
Dbms_output.put_line (emp_record.ename| | Emp_record.sal);
End LOOP;
Close Emp_cs;
End;
/

3 using Batch dynamic SQL (9i)
The bulk clause can speed up the processing of bulk data. There are three ways in which statements support bulk clauses.
1 Using Execute IMMEDIATE, the syntax is:
EXECUTE IMMEDIATE dynamic_string
[BULK COLLECT into define_variable[,define_variable ...]]
[USING [In | Out | In Out] bind_argument[,[in | Out/In out] bind_argumnet]
[{returning | return}
BULK COLLECT into return_variable[,return_variable ...];

For DML handling Multiline clauses, example: increase the percentage of the employee's salary for all employees in a department
DECLARE ...
BEGIN
sql_stat:= ' UPDATE emptbl SET sal=sal* (1+:p ercent/100) ' | |
' WHERE deptno=:d no ' | |
' Returning Ename,sal into:name,:salary ';

EXECUTE IMMEDIATE sql_stat USING &percent,&dno
Returning BULK COLLECT into ename_table,sal_table;

For I in 1.ename_table. COUNT LOOP
Dbms_output.put_line (ename_table (i) | | Sal_table (i));
End LOOP;

End;
/

2 FETCH statement, syntax for
FETCH Dynamic_cursor
BULK COLLECT into define_variable[,dyfine_variable ...];

3 ForAll statement. Applies to DML and does not apply to dynamic SELECT statements. The ForAll statement should be used in conjunction with E I. Syntax is

ForAll index in lower bound. Upper bound

EXECUTE IMMEDIATE dynamic_string

USING Bind_argument | Bind_argumnet (Index)

[, Bind_argument | bind_argumnet (index)] ...

[{returning | return} BULK COLLECT

Into Bind_argument[,bind_argument ...]];