PL/SQL learning gift 1 Bulk_Collect call method collection, plbulk_collect

PL/SQL learning gift 1 Bulk_Collect call method collection, plbulk_collect

All examples in this article are derived from Chapter 6 of the ebook "Expert pl SQL Practices. Xiao Chen thinks that it may be used in the future in the process of learning PLSQL. Take notes here.

The text is as follows:

First, prepare the basic data in the HARDWARE table.

Table Structure:

 

Then insert 1,000,000 data records. Here I have to say that in PLSQL, the dual table does help a lot, of course, you can also create a secondary table in the T-SQL. The formatting function provided by Oracle SQL Developer is weak.

INSERT INTO HARDWARESELECT TRUNC(rownum/1000)+1 aisle,  rownum item,  'Description '  ||rownum descrFROM  ( SELECT 1 FROM dual CONNECT BY level <= 1000  ),  ( SELECT 1 FROM dual CONNECT BY level <= 1000  );

Here we will talk about the oldest usage:

SET serveroutput ON;cl scr;DECLARE  l_cursor INT := dbms_sql.open_cursor;  l_num_row dbms_sql.number_table;  l_exec         INT;  l_fetched_rows INT;BEGIN  dbms_sql.parse( l_cursor, 'select item from hardware where item <= 1200', dbms_sql.native);  dbms_sql.define_array(l_cursor,1,l_num_row,500,1);  l_exec := dbms_sql.execute(l_cursor);  LOOP    l_fetched_rows := dbms_sql.fetch_rows(l_cursor);    dbms_sql.column_value(l_cursor, 1, l_num_row);    dbms_output.put_line('Fetched '||l_fetched_rows||' rows');    EXIT  WHEN l_fetched_rows < 500;  END LOOP;  dbms_sql.close_cursor(l_cursor);END; 

 

The above call is not recommended. Go to the topic.

1.Implicit Cursor

DECLARE  l_descr hardware.descr%type;BEGIN  SELECT  descr  INTO    l_descr  FROM    hardware  WHERE   aisle = 1          AND item = 1;END;

2.Explicit Fetch CILS 

DECLARE  CURSOR c_tool_list  IS    SELECT descr FROM hardware WHERE aisle = 1 AND item BETWEEN 1 AND 500;  l_descr hardware.descr%type;BEGIN  OPEN c_tool_list;  LOOP    FETCH c_tool_list INTO l_descr;    EXIT  WHEN c_tool_list%notfound;  END LOOP;  CLOSE c_tool_list;END;

3.Implicit Fetch CILS 

BEGIN  FOR i IN  ( SELECT descr FROM hardware WHERE aisle = 1 AND item BETWEEN 1 AND 500  )  LOOP    pl(i.descr);--<processing code FOR EACH row>  END LOOP;END;BEGIN  FOR i IN  ( SELECT descr FROM hardware WHERE aisle = 1 AND item BETWEEN 1 AND 500  )  LOOP    --<processing code for each row>  END LOOP;END;

4.Implicit Cursor BULK Mode 

DECLAREtype t_descr_listIS  TABLE OF hardware.descr%type;  l_descr_list t_descr_list;BEGIN  SELECT descr bulk collect  INTO l_descr_list  FROM hardware  WHERE aisle = 1  AND item BETWEEN 1 AND 100;END;

5.Explicit Fetch cils bulk Mode 

DECLARE  CURSOR c_tool_list  IS    SELECT descr FROM hardware WHERE aisle = 1 AND item BETWEEN 1 AND 500;type t_descr_listIS  TABLE OF c_tool_list%rowtype;  l_descr_list t_descr_list;BEGIN  OPEN c_tool_list;  FETCH c_tool_list bulk collect INTO l_descr_list;  CLOSE c_tool_list;END;

 

Finally, give me some eggs, or the content in the book. For example, T-SQL processing XML, ORACLE does not know how many streets it dumped it. 

CREATE OR REPLACE type COMING_FROM_XMLAS  object  (    COL1 INT,    COL2 INT)

DECLARE  source_xml xmltype;  target_obj coming_from_xml;BEGIN  source_xml := xmltype('<DEMO><COL1>10</COL1><COL2>20</COL2></DEMO>');  source_xml.toObject(target_obj);  dbms_output.put_line( target_obj.COL1  || ',' || target_obj.COL2);END;

DECLARE  l_refcursor SYS_REFCURSOR;  l_xmltype XMLTYPE;BEGIN  OPEN l_refcursor FOR SELECT aisle , item,descr FROM HARDWARE WHERE aisle = 1 AND item BETWEEN 1 AND 10;  l_xmltype                                                               := XMLTYPE(l_refcursor);  dbms_output.put_line(l_xmltype.getClobVal);END;DECLARE  l_xmltype XMLTYPE;BEGIN  l_xmltype := dbms_xmlgen.getxmltype('SELECT aisle , item,descr FROM HARDWARE WHERE aisle = 1 AND item BETWEEN 1 AND 10' );  dbms_output.put_line(l_xmltype.getClobVal);END;DECLARE   l_xmltype XMLTYPE;   l_ctx dbms_xmlgen.ctxhandle;BEGIN   l_ctx := dbms_xmlgen.newcontext('SELECT aisle , item,descr FROM HARDWARE WHERE aisle = 1 AND item BETWEEN 1 AND 10'                                  );   dbms_xmlgen.setrowsettag(l_ctx, 'HARDWARE');    dbms_xmlgen.setrowtag(l_ctx, 'Store');   l_xmltype := dbms_xmlgen.getXmlType(l_ctx) ;   dbms_xmlgen.closeContext(l_ctx);   dbms_output.put_line(l_xmltype.getClobVal);End;

Conclusion: Chen learning ORCALE time soon, and some are T-SQL skills. Simply put, I still think T-SQL is much better. For example, MS and ORACLE are described as loving mothers and fathers. The former is definitely a good mother, and the latter is a bad father.