143.Oracle数据库SQL开发之 集合——在PLSQL中使用集合

143.Oracle数据库SQL开发之 集合——在PLSQL中使用集合

欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/50180853

1.  操作变长数组

创建如下包规范和包体：

CREATE PACKAGE varray_package AS

  TYPEt_ref_cursor IS REF CURSOR;

  FUNCTIONget_customers RETURN t_ref_cursor;

  PROCEDUREinsert_customer(

    p_id         IN customers_with_varray.id%TYPE,

    p_first_nameIN customers_with_varray.first_name%TYPE,

   p_last_name  INcustomers_with_varray.last_name%TYPE,

   p_addresses  INcustomers_with_varray.addresses%TYPE

  );

END varray_package;

/

CREATE PACKAGE BODY varray_package AS

  --get_customers() function returns a REF CURSOR

  -- thatpoints to the rows in customers_with_varray

  FUNCTIONget_customers

  RETURNt_ref_cursor IS

    --declarethe REF CURSOR object

   v_customers_ref_cursor t_ref_cursor;

  BEGIN

    -- get theREF CURSOR

    OPEN v_customers_ref_cursorFOR

      SELECT *

      FROMcustomers_with_varray;

    -- returnthe REF CURSOR

    RETURNv_customers_ref_cursor;

  ENDget_customers;

 

  --insert_customer() procedure adds a row to

  --customers_with_varray

  PROCEDUREinsert_customer(

    p_id         IN customers_with_varray.id%TYPE,

   p_first_name IN customers_with_varray.first_name%TYPE,

   p_last_name  INcustomers_with_varray.last_name%TYPE,

   p_addresses  INcustomers_with_varray.addresses%TYPE

  ) IS

  BEGIN

    INSERT INTOcustomers_with_varray

    VALUES(p_id, p_first_name, p_last_name, p_addresses);

    COMMIT;

  EXCEPTION

    WHEN OTHERSTHEN

      ROLLBACK;

  ENDinsert_customer;

END varray_package;

/

调用insert_customer()如下：

collection_user@PDB1>call varray_package.insert_customer(3,'James','Red',t_varray_address(

    '10 MainStreet,Green Town, CA,22212',

   '20 StateStreet,Blue Town,FL,22213'))

 

Call completed.

然后调用get_products()，从customers_with_varray中检索行，如下：

collection_user@PDB1> select varray_package.get_customersfrom dual;

 

GET_CUSTOMERS

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

CURSOR STATEMENT : 1

 

CURSOR STATEMENT : 1

 

         IDFIRST_NAME LAST_NAME

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

ADDRESSES

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

          1 Steve     Brown

T_VARRAY_ADDRESS('2 State Street, Beantown,MA, 12345', '4 Hill Street, Lost Town, CA, 54321')

 

          2 John      Smith

T_VARRAY_ADDRESS('6 any street,lost town,ga,33347', '3 new street,anytown,mi,54323', '7 martket str

eet,main town,ma,54323')

 

          3 James     Red

T_VARRAY_ADDRESS('10 Main Street,GreenTown, CA,22212', '20 State Street,Blue Town,FL,22213')

2.  操作嵌套表

创建下列包规范和包体如下：

CREATE PACKAGE nested_table_package AS

  TYPEt_ref_cursor IS REF CURSOR;

  FUNCTIONget_customers RETURN t_ref_cursor;

  PROCEDUREinsert_customer(

    p_id         INcustomers_with_nested_table.id%TYPE,

   p_first_name IN customers_with_nested_table.first_name%TYPE,

   p_last_name  INcustomers_with_nested_table.last_name%TYPE,

   p_addresses  INcustomers_with_nested_table.addresses%TYPE

  );

END nested_table_package;

/

CREATE PACKAGE BODY nested_table_package AS

  --get_customers() function returns a REF CURSOR

  -- thatpoints to the rows in customers_with_nested_table

  FUNCTIONget_customers

  RETURNt_ref_cursor IS

    -- declarethe REF CURSOR object

   v_customers_ref_cursor t_ref_cursor;

  BEGIN

    -- get theREF CURSOR

    OPENv_customers_ref_cursor FOR

      SELECT *

      FROMcustomers_with_nested_table;

    -- returnthe REF CURSOR

    RETURNv_customers_ref_cursor;

  ENDget_customers;

 

  --insert_customer() procedure adds a row to

  --customers_with_nested_table

  PROCEDUREinsert_customer(

    p_id         INcustomers_with_nested_table.id%TYPE,

    p_first_nameIN customers_with_nested_table.first_name%TYPE,

   p_last_name  INcustomers_with_nested_table.last_name%TYPE,

   p_addresses  INcustomers_with_nested_table.addresses%TYPE

  ) IS

  BEGIN

    INSERT INTOcustomers_with_nested_table

    VALUES(p_id, p_first_name, p_last_name, p_addresses);

    COMMIT;

  EXCEPTION

    WHEN OTHERSTHEN

      ROLLBACK;

  ENDinsert_customer;

END nested_table_package;

/

调用insert_customer向customers_with_nested_table中添加一新行：

collection_user@PDB1> call nested_table_package.insert_customer(

   3,'James','Red',t_nested_table_address(

   t_address('10 Main Street','Green Town','CA','22212'),

   t_address('20 State Street','Blue Town','FL','22213')));

 

Call completed.

调用get_products函数从customers_with_nested_table中检索行如下：

collection_user@PDB1> selectnested_table_package.get_customers from dual;

 

GET_CUSTOMERS

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

CURSOR STATEMENT : 1

 

CURSOR STATEMENT : 1

 

         IDFIRST_NAME LAST_NAME

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

ADDRESSES(STREET, CITY, STATE, ZIP)

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

          1 Steve     Brown

T_NESTED_TABLE_ADDRESS(T_ADDRESS('2 StateStreet', 'Beantown', 'MA', '12345'), T_ADDRESS('4 Hill Str

eet', 'Lost Town', 'CA', '54321'))

 

          2 John      Smith

T_NESTED_TABLE_ADDRESS(T_ADDRESS('9 AnySteet', 'Lost Town', 'VA', '74321'), T_ADDRESS('7 Market Str

eet', 'Main Town', 'MA', '54323'),T_ADDRESS('5 main street', 'uptown', 'ny', '55512'))

 

          3 James     Red

T_NESTED_TABLE_ADDRESS(T_ADDRESS('10 MainStreet', 'Green Town', 'CA', '22212'), T_ADDRESS('20 State

 Street', 'Blue Town', 'FL', '22213'))

3.  PLSQL集合方法

在下面提供了很多用于集合的方法。

3.1             COUNT

COUNT返回集合中的元素数目。因为嵌套表可能会有个别空元素，当COUNT用于嵌套表时，就返回嵌套表中非空元素的数目。

CREATE PACKAGE collection_method_examples AS

  FUNCTIONget_addresses(

    p_idcustomers_with_nested_table.id%TYPE

  ) RETURNt_nested_table_address;

  PROCEDUREdisplay_addresses(

    p_addressest_nested_table_address

  );

  PROCEDUREdelete_address(

   p_address_num INTEGER

  );

  PROCEDURE exist_addresses;

  PROCEDUREextend_addresses;

  PROCEDUREfirst_address;

  PROCEDURElast_address;

  PROCEDUREnext_address;

  PROCEDUREprior_address;

  PROCEDUREtrim_addresses;

END collection_method_examples;

/

Get_addresses函数如下：

  FUNCTION get_addresses(

    p_idcustomers_with_nested_table.id%TYPE

  ) RETURNt_nested_table_address IS

    -- declareobject named v_addresses to store the

    -- nestedtable of addresses

    v_addressest_nested_table_address;

  BEGIN

    -- retrievethe nested table of addresses into v_addresses

    SELECTaddresses

    INTOv_addresses

    FROMcustomers_with_nested_table

    WHERE id =p_id;

 

    -- displaythe number of addresses using v_addresses.COUNT

   DBMS_OUTPUT.PUT_LINE(

      'Numberof addresses = '|| v_addresses.COUNT

    );

 

    -- returnv_addresses

    RETURNv_addresses;

  ENDget_addresses;

调用get_addresses如下：

collection_user@PDB1> set serveroutput on

collection_user@PDB1> selectcollection_method_examples.get_addresses(1) addresses from dual;

 

ADDRESSES(STREET, CITY, STATE, ZIP)

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

T_NESTED_TABLE_ADDRESS(T_ADDRESS('2 StateStreet', 'Beantown', 'MA', '12345'), T_ADDRESS('4 Hill Str

eet', 'Lost Town', 'CA', '54321'))

 

 

Number of addresses = 2

3.2             DELETE

DELETE用于删除集合中的元素。

Delete_address函数如下：

PROCEDURE delete_address(

   p_address_num INTEGER

  ) IS

    v_addressest_nested_table_address;

  BEGIN

    v_addresses:= get_addresses(1);

   display_addresses(v_addresses);

   DBMS_OUTPUT.PUT_LINE('Deleting address #' || p_address_num);

 

    -- deletethe address specified by p_address_num

   v_addresses.DELETE(p_address_num);

 

   display_addresses(v_addresses);

  END delete_address;

调用delete_address(2)删除顾客#1的地址#2

collection_user@PDB1> callcollection_method_examples.delete_address(2);

Number of addresses = 2

Current number of addresses = 2

Address #1:

2 State Street, Beantown, MA, 12345

Address #2:

4 Hill Street, Lost Town, CA, 54321

Deleting address #2

Current number of addresses = 1

Address #1:

2 State Street, Beantown, MA, 12345

 

Call completed.

3.3             EXISTS

如果集合的第N个元素存在，则EXISTS返回true；对于非空元素，EXISTS返回TRUE。

过程如下：

PROCEDUREexist_addresses IS

    v_addresses t_nested_table_address;

  BEGIN

    v_addresses := get_addresses(1);

    DBMS_OUTPUT.PUT_LINE('Deleting address#1');

    v_addresses.DELETE(1);

 

    -- use EXISTS to check if the addressesexist

    IF v_addresses.EXISTS(1) THEN

      DBMS_OUTPUT.PUT_LINE('Address #1 doesexist');

    ELSE

      DBMS_OUTPUT.PUT_LINE('Address #1 does notexist');

    END IF;

    IF v_addresses.EXISTS(2) THEN

      DBMS_OUTPUT.PUT_LINE('Address #2 doesexist');

    END IF;

  END exist_addresses;

调用exist_addresses()如下：

collection_user@PDB1> callcollection_method_examples.exist_addresses();

Number of addresses = 2

Deleting address #1

Address #1 does not exist

Address #2 does exist

 

Call completed.

3.4             EXTEND

EXTEND用于在集合末尾添加元素。

函数extend_addresses如下：

PROCEDURE extend_addresses IS

    v_addressest_nested_table_address;

  BEGIN

    v_addresses:= get_addresses(1);

   display_addresses(v_addresses);

   DBMS_OUTPUT.PUT_LINE('Extending addresses');

 

    -- copyaddress #1 twice to the end of v_addresses

   v_addresses.EXTEND(2, 1);

 

   display_addresses(v_addresses);

  ENDextend_addresses;

调用extend_addresses()

collection_user@PDB1> callcollection_method_examples.extend_addresses();

Number of addresses = 2

Current number of addresses = 2

Address #1:

2 State Street, Beantown, MA, 12345

Address #2:

4 Hill Street, Lost Town, CA, 54321

Extending addresses

Current number of addresses = 4

Address #1:

2 State Street, Beantown, MA, 12345

Address #2:

4 Hill Street, Lost Town, CA, 54321

Address #3:

2 State Street, Beantown, MA, 12345

Address #4:

2 State Street, Beantown, MA, 12345

 

Call completed.

3.5             FIRST

使用FIRST可以得到集合中第一个元素的索引。

First_address过程如下：

  PROCEDURE first_address IS

    v_addresses t_nested_table_address;

  BEGIN

    v_addresses := get_addresses(1);

 

    -- display the FIRST address

    DBMS_OUTPUT.PUT_LINE('First address = ' ||v_addresses.FIRST);

    DBMS_OUTPUT.PUT_LINE('Deleting address#1');

    v_addresses.DELETE(1);

 

    -- display the FIRST address again

    DBMS_OUTPUT.PUT_LINE('First address = ' ||v_addresses.FIRST);

  END first_address;

调用first_address如下：

collection_user@PDB1> callcollection_method_examples.first_address();

Number of addresses = 2

First address = 1

Deleting address #1

First address = 2

 

Call completed.

3.6             LAST

LAST返回集合中最后一个元素的索引。

过程last_address如下：

PROCEDURE last_address IS

    v_addressest_nested_table_address;

  BEGIN

    v_addresses:= get_addresses(1);

 

    -- displaythe LAST address

   DBMS_OUTPUT.PUT_LINE('Last address = ' || v_addresses.LAST);

   DBMS_OUTPUT.PUT_LINE('Deleting address #2');

    v_addresses.DELETE(2);

 

    -- displaythe LAST address again

   DBMS_OUTPUT.PUT_LINE('Last address = ' || v_addresses.LAST);

  ENDlast_address;

调用last_address如下：

collection_user@PDB1> callcollection_method_examples.last_address();

Number of addresses = 2

Last address = 2

Deleting address #2

Last address = 1

 

Call completed.

3.7             NEXT

NEXT（n）返回n后面的元素的索引。

Next_address过程如下：

  PROCEDUREnext_address IS

    v_addressest_nested_table_address;

  BEGIN

    v_addresses:= get_addresses(1);

 

    -- useNEXT(1) to get the index of the address

    -- afteraddress #1

   DBMS_OUTPUT.PUT_LINE(

     'v_addresses.NEXT(1) = ' || v_addresses.NEXT(1)

    );

 

    -- useNEXT(2) to attempt to get the index of

    -- theaddress after address #2 (there isn't one,

    -- so nullis returned)

   DBMS_OUTPUT.PUT_LINE(

     'v_addresses.NEXT(2) = ' || v_addresses.NEXT(2)

    );

  ENDnext_address;

调用next_address过程如下：

collection_user@PDB1> callcollection_method_examples.next_address();

Number of addresses = 2

v_addresses.NEXT(1) = 2

v_addresses.NEXT(2) =

 

Call completed.

3.8             PRIOR

PRIOR(n)返回n前面的元素的索引。

过程prior_address如下：

  PROCEDUREprior_address IS

    v_addressest_nested_table_address;

  BEGIN

    v_addresses:= get_addresses(1);

 

    -- usePRIOR(2) to get the index of the address

    -- beforeaddress #2

   DBMS_OUTPUT.PUT_LINE(

     'v_addresses.PRIOR(2) = ' || v_addresses.PRIOR(2)

    );

 

    -- usePRIOR(1) to attempt to get the index of

    -- theaddress before address #1 (there isn't one,

    -- so nullis returned)

    DBMS_OUTPUT.PUT_LINE(

     'v_addresses.PRIOR(1) = ' || v_addresses.PRIOR(1)

    );

  ENDprior_address;

调用prior_address如下：

collection_user@PDB1> callcollection_method_examples.prior_address();

Number of addresses = 2

v_addresses.PRIOR(2) = 1

v_addresses.PRIOR(1) =

 

Call completed.

3.9             TRIM

TRIM用于删除集合末尾的元素。

  PROCEDUREtrim_addresses IS

    v_addressest_nested_table_address;

  BEGIN

    v_addresses:= get_addresses(1);

   display_addresses(v_addresses);

   DBMS_OUTPUT.PUT_LINE('Extending addresses');

    v_addresses.EXTEND(3,1);

   display_addresses(v_addresses);

   DBMS_OUTPUT.PUT_LINE('Trimming 2 addresses from end');

 

    -- remove 2addresses from the end of v_addresses

    -- usingTRIM(2)

   v_addresses.TRIM(2);

 

   display_addresses(v_addresses);

  ENDtrim_addresses;

END collection_method_examples;

/

调用trim_addresses如下：

collection_user@PDB1> callcollection_method_examples.trim_addresses();

Number of addresses = 2

Current number of addresses = 2

Address #1:

2 State Street, Beantown, MA, 12345

Address #2:

4 Hill Street, Lost Town, CA, 54321

Extending addresses

Current number of addresses = 5

Address #1:

2 State Street, Beantown, MA, 12345

Address #2:

4 Hill Street, Lost Town, CA, 54321

Address #3:

2 State Street, Beantown, MA, 12345

Address #4:

2 State Street, Beantown, MA, 12345

Address #5:

2 State Street, Beantown, MA, 12345

Trimming 2 addresses from end

Current number of addresses = 3

Address #1:

2 State Street, Beantown, MA, 12345

Address #2:

4 Hill Street, Lost Town, CA, 54321

Address #3:

2 State Street, Beantown, MA, 12345

 

Call completed.