slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Collectors PowerPoint Presentation
Download Presentation
Collectors

Loading in 2 Seconds...

play fullscreen
1 / 5

Collectors - PowerPoint PPT Presentation


  • 157 Views
  • Uploaded on

Collectors. Varying array allows you to store repeating attributes of a record in a single row example: all part numbers can be stored in ORDERS table as a varray. 1* create or replace type parts99_va as varray(5) of varchar2(4) SQL> / Type created.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Collectors' - shannon-christian


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

Collectors

  • Varying array
    • allows you to store repeating attributes of a record in a single row
    • example: all part numbers can be stored in ORDERS table as a varray

1* create or replace type parts99_va as varray(5) of varchar2(4)

SQL> /

Type created.

create table orders99_o (ordnumb number(5), orddte date,

customer ref customer_ty,

parts parts_va)

SQL> /

Table created.

  • inserting records into a varying array

insert into orders99_o

select 12491, to_date('09/02/94', 'mm/dd/yy'), ref(c),

parts_va('BT04','BZ66') from customer_o c where c.custnumb=311;

  • selecting data from varying arrays
    • cannot be queried directly via a select command
slide2

Collectors cont’d.

  • selecting data from varying arrays

set serveroutput on

1 declare

2 cursor order_cursor is

3 select o.customer.custname,o.orddte, o.parts from orders99_o o;

4 order_rec order_cursor%rowtype;

5 begin

6 for order_rec in order_cursor

7 loop

8 dbms_output.put_line('Customer Name: '||order_rec.custname||' '||'Date: '||order_rec.orddte

9 dbms_output.put_line('-----------------------------------------');

10 for i in 1..order_rec.parts.count

11 loop

12 dbms_output.put_line(order_rec.parts(i));

13 end loop;

14 end loop;

15* end;

SQL> /

Customer Name: SALLY ADAMS Date: 02-SEP-94

-----------------------------------------

AX12

Customer Name: DON CHARLES Date: 02-SEP-94

-----------------------------------------

BT04

BZ66

slide3

Nested tables

  • Limitations of varying arrays
    • can only contain one column
    • must declare the maximum number of members at the time of definition
    • cannot be used in select statement of SQL
  • Nested table
    • a table represented as a column within another table
    • no limit on the number of entries per row
  • Example: create a nested table within ORDERS corresponding to line items
  • (ordlne)
  • Step 1: Create a type corresponding to ORDLNE

CREATE OR REPLACE TYPE ORDLNE_TY AS OBJECT

(PARTNUMB VARCHAR2(4),

NUMBORD NUMBER(3),

QUOTPRCE NUMBER(7,2)

)

/

  • Step 2: Use the above data type as the basis for a nested table

CREATE TYPE ORDLNE_NT AS TABLE OF ORDLNE_TY;

/

slide4

Nested tables cont’d

  • Step 3: Create the ORDERS table using the ORDNLE_NT data type

CREATE TABLE ORDERS

(ORDNUMB NUMBER(6),

ORDDTE DATE,

CUSTOMER REF CUSTOMER_TY,

ORDLNE ORDLNE_NT)

NESTED TABLE ORDLNE STORE AS ORDLNE_NT_TAB;

/

  • The above command creates a nested table called ORDLNE_NT_TAB
  • to store line item data within orders
  • The nested table’s data is stored “out-of-line” with the rest of the table’s
  • data
  • Although the nested table is stored as a separate table, you cannot access
  • it directly
  • Inserting data in nested table

INSERT INTO ORDERS

SELECT 12489, TO_DATE('09/02/94', 'MM/DD/YY'),

REF(C),ORDLNE_NT(ORDLNE_TY('AX12',1,14.95))

FROM CUSTOMER_O C

WHERE CUSTNUMB=124;

slide5

Querying nested tables

  • Since a nested table is a column within a table, querying a nested table
  • requires a special key word THE
  • Example: select all line items of orders placed by DON CHARLES
  • Step 1: select the nested table column from the main table

select ordlne from orders o where o.customer.custname='DON CHARLES'

  • Step 2: enclose this query within the THE function
  • Step 3: query the nested table’s columns using the clause in Step 2 as the
  • table name in the query’s from clause

1 select nt.partnumb, nt.numbord, nt.quotprce

2* from the (select ordlne from orders o where o.customer.custname='DON CHARLES') NT

SQL> /

PART NUMBORD QUOTPRCE

---- --------- ---------

BT04 1 402.99

BZ66 1 311.95