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
[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
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.