в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:04:30Z

1 версия

Новая страница

== 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>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Collections/Table Collection Attributes - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия