首页 > 代码库 > PLSQL_动态语句的解析(概念)

PLSQL_动态语句的解析(概念)

2014-06-02 BaoXinjian

1. 最简单例子

(1).SQL 

 1 DECLARE
 2 
 3     l_sql_text VARCHAR (1000);
 4 
 5     TYPE c_type_wip_entity IS REF CURSOR;
 6 
 7     c_wip_entity c_type_wip_entity;
 8 
 9     r_wip_entity wip_entities%ROWTYPE;
10 
11 BEGIN
12 
13     l_sql_text :=select * from wip_entities
14 
15                    ||  where wip_entity_id in (|| ‘‘‘‘|| 2363 || ‘‘‘,‘‘ || 2462|| ‘‘‘);
16 
17     DBMS_OUTPUT.put_line (l_sql_text);
18 
19     OPEN c_wip_entity FOR l_sql_text;
20 
21     LOOP
22 
23    FETCH c_wip_entity INTO r_wip_entity;
24 
25         EXIT WHEN c_wip_entity%NOTFOUND;
26 
27         DBMS_OUTPUT.put_line (Job Name--> || r_wip_entity.wip_entity_name);
28 
29     END LOOP;
30 
31 END;

 

 

(2).DBMS Output

 

 

2. 动态语句结合批处理

(1).SQL 

 1 DECLARE
 2 
 3     i NUMBER;
 4 
 5     l_sql_text VARCHAR (1000);
 6 
 7     TYPE c_type_wip_entity IS TABLE OF wip_entities%ROWTYPE;
 8 
 9     c_wip_entity c_type_wip_entity;
10 
11     p_wip_entity_id NUMBER := 2363;
12 
13 BEGIN
14 
15     l_sql_text := select * from wip_entities where wip_entity_id = :wip_entity_id;
16 
17     EXECUTE IMMEDIATE l_sql_text
18 
19     BULK COLLECT INTO c_wip_entity
20 
21         USING p_wip_entity_id;
22 
23     FOR i IN 1 .. c_wip_entity.COUNT
24 
25     LOOP
26 
27         DBMS_OUTPUT.put_line (c_wip_entity (i).wip_entity_name);
28 
29     END LOOP;
30 
31 END;

 

(2).DBMS Output

 

 

3. 动态更新语句

(1).SQL 

 1 DECLARE
 2 
 3     l_sql_text VARCHAR (1000);
 4 
 5 BEGIN
 6 
 7     l_sql_text := update cux_wf_demo_documents set note=  || ‘‘‘Test‘‘‘ ||  where document_id= :document_id;
 8 
 9     EXECUTE IMMEDIATE l_sql_text
10 
11         USING 1;
12 
13     DBMS_OUTPUT.put_line (l_sql_text);
14 
15 END;

 

(2).DBMS Output

 

 

Thanks and Regarads