动态sql的执行及 调用其它存储过程

1
What Is Dynamic SQL?

some programs must build and process a variety of SQL statements at
run time. For example, a general-purpose report writer must build different
SELECT statements for the various reports it generates. In this case, the full text of
the statement is unknown until run time. Such statements can, and probably will,
change from execution to execution. So, they are called dynamic SQL statements.

2
The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a
dynamic SQL statement or an anonymous PL/SQL block. The syntax is

EXECUTE IMMEDIATE dynamic_string
[INTO {define_variable[, define_variable]... | record}]
[USING [IN | OUT | IN OUT] bind_argument
[, [IN | OUT | IN OUT] bind_argument]...]
[{RETURNING | RETURN} INTO bind_argument[, bind_argument]...];

3
parameter model

When appropriate, you must specify the OUT or IN OUT mode for bind arguments
passed as parameters. For example, suppose you want to call the following
standalone procedure:
CREATE PROCEDURE create_dept (
deptno IN OUT NUMBER,
dname IN VARCHAR2,
loc IN VARCHAR2) AS
BEGIN
SELECT deptno_seq.NEXTVAL INTO deptno FROM dual;
INSERT INTO dept VALUES (deptno, dname, loc);
END;
To call the procedure from a dynamic PL/SQL block, you must specify the IN OUT
mode for the bind argument associated with formal parameter deptno, as follows:
DECLARE
plsql_block VARCHAR2(500);
new_deptno NUMBER(2);
new_dname VARCHAR2(14) := ’ADVERTISING’;
new_loc VARCHAR2(13) := ’NEW YORK’;
BEGIN
plsql_block := ’BEGIN create_dept(:a, :b, :c); END;’;
EXECUTE IMMEDIATE plsql_block
USING IN OUT new_deptno, new_dname, new_loc;
IF new_deptno > 90 THEN ...
END;

阅读全文 | 回复(1) | 引用通告 | 编辑

Re:动态sql的执行及 调用其它存储过程

interesting
2007-1-28 10:49:00

个人主页 | 引用 | 返回 | 删除 | 回复