Oracle PL/SQL Tutorial/Collections/Table Collection Attributes
Содержание
Collection methods: First, Last, Next
<source lang="sql">
SQL> SQL> SQL> create table employee
2 ( 3 empl_no integer primary key 4 ,lastname varchar2(20) not null 5 ,firstname varchar2(15) not null 6 ,midinit varchar2(1) 7 ,street varchar2(30) 8 ,city varchar2(20) 9 ,state varchar2(2) 10 ,zip varchar2(5) 11 ,zip_4 varchar2(4) 12 ,area_code varchar2(3) 13 ,phone varchar2(8) 14 ,company_name varchar2(50));
Table created. SQL> SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(1,"Jones","Joe","J","10 Ave","New York","NY","11111","1111","111", "111-1111","A Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(2,"Smith","Sue","J","20 Ave","New York","NY","22222","2222","222", "222-111","B Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(3,"Anderson","Peggy","J","500 St","New York","NY","33333","3333","333", "333-3333","C Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(4,"Andy","Jill", null,"930 St","New York","NY","44444","4444","212", "634-7733","D Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(5,"OK","Carl","L","19 Drive","New York","NY","55555","3234","212", "243-4243","E Company");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(6,"Peter","Jee","Q","38 Ave","New York","NY","66666","4598","212", "454-5443","F Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(7,"Baker","Paul","V","738 St.","Queens","NY","77777","3842","718", "664-4333","G Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(8,"Young","Steve","J","388 Ave","New York","NY","88888","3468","212", "456-4566","H Associates Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(9,"Mona","Joe","T","9300 Ave","Kansas City","MO","99999","3658","415", "456-4563","J Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(10,"Hackett","Karen","S","Kings Rd. Apt 833","Bellmore","NY","61202","3898","516", "767-5677","AA Inc");
1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(11,"Bob","Jack","S","12 Giant Rd.","Newark","NJ","27377","3298","908", "123-7367","Z Associates");
1 row created. SQL> SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE loadarray IS
2 3 TYPE cust_table_type IS TABLE OF VARCHAR2(100) 4 INDEX BY BINARY_INTEGER; 5 6 cust_table cust_table_type; 7 indx NUMBER := 0; 8 BEGIN 9 10 FOR crec IN (select empl_no, 11 firstname ||" "|| lastname 12 AS name 13 from employee) LOOP 14 cust_table(crec.empl_no) := crec.name; 15 END LOOP; 16 17 indx := cust_table.FIRST; 18 WHILE indx <= cust_table.LAST LOOP 19 dbms_output.put_line( cust_table(indx) ); 20 indx := cust_table.NEXT(indx); 21 END LOOP; 22 END; 23 /
Procedure created. SQL> exec loadarray Joe Jones Sue Smith Peggy Anderson Jill Andy Carl OK Jee Peter Paul Baker Steve Young Joe Mona Karen Hackett Jack Bob PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table employee; Table dropped.</source>
Extend Table collection
<source lang="sql">
SQL> SQL> SQL> create table ord(
2 order_no integer primary key 3 ,empl_no integer 4 ,order_date date not null 5 ,total_order_price number(7,2) 6 ,deliver_date date 7 ,deliver_time varchar2(7) 8 ,payment_method varchar2(2) 9 ,emp_no number(3,0) 10 ,deliver_name varchar2(35) 11 ,gift_message varchar2(100) 12 );
Table created. SQL> SQL> SQL> insert into ord(order_no,empl_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
2 values(1,1,"14-Feb-2002", 23.00, "14-Feb-2002", "12 noon", "CA",1, null, "Gift for wife");
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
2 values(2,1,"14-Feb-2003", 510.98, "14-feb-2003", "5 pm", "NY",7, "Rose Ted", "Happy Valentines Day to Mother");
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(3, 2,"14-Feb-2004", 315.99, "14-feb-2004", "3 pm", "VS",2, "Ani Forest", "Happy Valentines Day to Father");
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(4, 2,"14-Feb-1999", 191.95, "14-feb-1999", "2 pm", "NJ",2, "O. John", "Happy Valentines Day");
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(5, 6,"4-mar-2002", 101.95, "5-mar-2002", "2:30 pm", "MO" , 2, "Cora", "Happy Birthday from John");
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(6, 9,"7-apr-2003", 221.95, "7-apr-2003", "3 pm", "MA", 2, "Sake Keith", "Happy Birthday from Joe" );
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(7, 9,"20-jun-2004", 315.95, "21-jun-2004", "12 noon", "BC", 2, "Jessica Li", "Happy Birthday from Jessica");
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (8, 12, "31-dec-1999", 135.95, "1-jan-2000", "12 noon", "DI", 3, "Larry", "Happy New Year from Lawrence");
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (9, 12, "26-dec-2003", 715.95, "2-jan-2004", "12 noon", "SK",7, "Did", "Happy Birthday from Nancy" );
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(10, 4, sysdate-1, 119.95, sysdate+2, "6:30 pm", "VG",2, "P. Jing", "Happy Valentines Day to Jason");
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(11, 2, sysdate, 310.00, sysdate+2, "3:30 pm", "DC",2, "C. Late", "Happy Birthday Day to Jack");
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(12, 7, sysdate-3, 121.95, sysdate-2, "1:30 pm", "AC",2, "W. Last", "Happy Birthday Day to You");
1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(13, 7, sysdate, 211.95, sysdate-4, "4:30 pm", "CA",2, "J. Bond", "Thanks for hard working");
1 row created. SQL> SQL> SQL> create or replace type sqlMONTH_TABLEtype as table of date;
2 /
Type created. SQL> SQL> create or replace function month_generator
2 (p_num_months in number) 3 RETURN sqlMONTH_TABLEtype 4 AS 5 month_table sqlMONTH_TABLEtype := sqlMONTH_TABLEtype(); 6 BEGIN 7 8 for i in 1..p_num_months loop 9 month_table.extend(1); 10 month_table(i) := add_months(sysdate, -i); 11 end loop; 12 return(month_table); 13 14 END; 15 /
Function created. SQL> SQL> select to_Char(x.column_value, "mm/yyyy") , nvl(avg(total_order_price),0) as avg_Sales
2 from TABLE( month_generator(12) ) x LEFT OUTER JOIN ord 3 ON to_Char(x.column_value, "mm/yyyy") = to_Char(order_date, "mm/yyyy") 4 group by to_Char(x.column_value, "mm/yyyy") ;
TO_CHAR AVG_SALES
----------
04/2008 0 03/2008 0 02/2008 0 10/2007 0 01/2008 0 05/2008 0 06/2008 0 12/2007 0 11/2007 0 07/2007 0 09/2007 0 TO_CHAR AVG_SALES
----------
08/2007 0 12 rows selected. SQL> SQL> drop table ord; Table dropped.</source>
Reference elements in table collection of varchar2 by index
<source lang="sql">
SQL> SQL> SQL> DECLARE
2 TYPE CAR_TABLE_TYPE IS TABLE OF VARCHAR2(20); 3 CAR_TABLE CAR_TABLE_TYPE; 4 BEGIN 5 CAR_TABLE := CAR_TABLE_TYPE(); 6 CAR_TABLE.EXTEND(10); 7 CAR_TABLE(1) := "DODGE"; 8 CAR_TABLE(2) := "FORD"; 9 CAR_TABLE(3) := "MUSTANG"; 10 CAR_TABLE(4) := "EDSEL"; 11 CAR_TABLE(5) := "STUDEBAKER"; 12 13 CAR_TABLE.DELETE(4); 14 CAR_TABLE(4):= "ROLLS"; 15 16 END; 17 /
PL/SQL procedure successfully completed. SQL> SQL></source>
Table of numbers: count, last, first
<source lang="sql">
SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE random
2 ( p_array_size in number)
3 AS
4
5 TYPE t_values_array IS TABLE OF NUMBER
6 INDEX BY BINARY_INTEGER;
7
8 random_value_array t_values_array;
9
10 BEGIN
11 FOR x IN 1..p_array_size LOOP
12 random_value_array(x) := dbms_random.random();
13 END LOOP;
14
15 DBMS_OUTPUT.PUT_LINE ("Total rows stored: " || random_value_array.count);
16
17 DBMS_OUTPUT.PUT_LINE ("First row stored: " || random_value_array(random_value_array.FIRST) );
18 DBMS_OUTPUT.PUT_LINE ("Last row stored: " || random_value_array(random_value_array.LAST) );
19
20 END;
21 /
Procedure created. SQL> SQL> show errors No errors. SQL> SQL> execute random(500); PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL></source>
