首页 > 代码库 > 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
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。