Parsing of Plsql_ dynamic statements (concept)

2014-06-02 Baoxinjian

1. The simplest example

(1). Sql

1 DECLARE2 3L_sql_textVARCHAR( +);4 5TYPE c_type_wip_entity isREFCURSOR;6 7 c_wip_entity c_type_wip_entity;8 9R_wip_entity wip_entities%ROWTYPE;Ten  One BEGIN A  -L_sql_text:='SELECT * from Wip_entities' -  the                    || 'where wip_entity_id in ('|| " '"|| '2363' || " ","' '|| '2462'|| " ")'; -  - Dbms_output.put_line (l_sql_text); -  +     OPENC_wip_entity forL_sql_text; -  + LOOP A  at    FETCHC_wip_entity intor_wip_entity; -  -         EXIT  whenC_wip_entity%NOTFOUND; -  -Dbms_output.put_line ('Job name-->' ||r_wip_entity.wip_entity_name); -  in     ENDLOOP; -  to END;

(2). DBMS Output

2. dynamic statements combined with batch processing

(1). Sql

1 DECLARE2 3I Number;4 5L_sql_textVARCHAR( +);6 7TYPE c_type_wip_entity is TABLE  ofWip_entities%ROWTYPE;8 9 c_wip_entity c_type_wip_entity;Ten  Onep_wip_entity_id Number:= 2363; A  - BEGIN -  theL_sql_text:= 'SELECT * from wip_entities where wip_entity_id =: wip_entity_id'; -  -     EXECUTEIMMEDIATE L_sql_text -  +     BULKCOLLECT intoc_wip_entity -  + USING p_wip_entity_id; A  at      forIinch 1.. C_wip_entity.COUNT -  - LOOP -  - Dbms_output.put_line (c_wip_entity (i). Wip_entity_name); -  in     ENDLOOP; -  to END;

(2). DBMS Output

3. Dynamic Update statements

(1). Sql

1 DECLARE2 3L_sql_textVARCHAR( +);4 5 BEGIN6 7L_sql_text:= 'Update cux_wf_demo_documents Set note=' || " "Test" " || 'where document_id=:d ocument_id';8 9     EXECUTEIMMEDIATE L_sql_textTen  OneUSING1; A  - Dbms_output.put_line (l_sql_text); -  the END;

(2). DBMS Output

Thanks and Regarads