专栏首页乐沙弥的世界PL/SQL 集合的初始化与赋值

PL/SQL 集合的初始化与赋值

    对于集合类型,与单一的数据类型相比较而言,应该以一个整体的观念来考虑集合,即是一批类型相同的数据组合而非单一的数据。因此集 合类型集合的声明、赋值、初始化较之单一类型而言,有很大的不同。尤其是嵌套表与变长数组,在赋值之前必须先初始化。当嵌套表和变长数 组在声明时,它们都会自动地被设置成NULL值。也就是嵌套表和变长数组中集合不存在任何元素,并不是针对它所拥有的元素。可以使用系统定 义的与集合类型同名的函数来初始化集合。我们必须显式地调用构造函数为每一个变长数组和嵌套表变量进行初始化操作(对于关联数组来说, 是不需要使用构造函数进行初始化的)。         有关集合类型的描述请参考:

  • PL/SQL 联合数组与嵌套表
  • PL/SQL 变长数组
  • PL/SQL --> PL/SQL记录 一、联合数组的赋值     联合数组的不需要初始化,直接赋值即可。(后续讲到的集合的初始化均指嵌套表与变长数组)
DECLARE
   TYPE idx_loc_type IS TABLE OF VARCHAR2( 13 )
                           INDEX BY BINARY_INTEGER;

   loc_tab   idx_loc_type;
BEGIN
   loc_tab( 1 ) := 'NEW YORK';                        -->联合数组不需要初始化,直接赋值即可
   loc_tab( 2 ) := 'DALLAS';
   DBMS_OUTPUT.put_line( ' loc_tab(1) value is ' || loc_tab( 1 ) );
   DBMS_OUTPUT.put_line( ' loc_tab(2) value is ' || loc_tab( 2 ) );
END;

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

DECLARE
   TYPE idx_loc_type IS TABLE OF VARCHAR2( 13 )
                           INDEX BY BINARY_INTEGER;

   loc_tab     idx_loc_type;
   v_counter   INTEGER := 0;
BEGIN
   FOR x IN ( SELECT loc FROM dept )       -->这里通过for 循环得到loc的值
   LOOP
      v_counter   := v_counter + 1;        -->使用一个v_counter变量来控制联合数组的下标
      loc_tab( v_counter ) := x.loc;       -->将得到的loc的值赋值给联合数组中对应的一个下标位
      DBMS_OUTPUT.put_line( ' loc_tab(' || v_counter || ') value is ' || loc_tab( v_counter ) );
   END LOOP;
END;

二、集合的初始化与赋值 1、初始化的方法      集合类型主要分为三步来完成,一是声明,二是初始化,三是赋值。初始化和赋值可以在声明块中完成,也可以在执行块中完成。         collection_name collection_type:=collection_type();   -->初始化集合为空(empty)      集合的初始化主要是通过构造函数(构造函数即是声明类型是的类型名)来进行初始化,下面常用的初始化方法包括:         a、在声明块声明集合,且在声明块中使用构造函数初始化为空(empty)但非NULL,在执行块中使用extend方式后进行赋值         b、在声明块声明集合,在执行块中使用构造函数初始化为空(empty)但非NULL,在执行块中使用extend方式后赋值         c、在声明块声明集合,在执行块中使用构造函数初始化时一并赋值         d、在声明块声明集合,同时使用构造函数初始化并赋值,即三步合为一步来完成      对于初始化为空的集合(empty),后续需要使用extend方式来扩展容量,除非使用bulk collect into方式 2、集合赋值的方法         collection_name(subscript) := expression; 3、赋值时可能引起的异常      在下面几种给集合元素赋值的情况下,可能会引起多种异常。         a、如果下标索引不存在或无法转换成正确的数据类型,PL/SQL就会抛出预定义异常VALUE_ERROR。                 通常,下标是一个整数。但关联数组的下标也可以是VARCHAR2类型。         b、如果所给下标索引指向了一个未初始化的元素时,PL/SQL就会抛出SUBSCRIPT_BEYOND_COUNT异常。         c、如果集合被自动初始化为空值并且程序引用了其中的一个元素,PL/SQL会抛出COLLECTION_IS_NULL异常。 4、元素的引用         collection_name(subscript)

可以把其中的表元素作为参数传递。如verify_loc(nest_loc_tab(i)),verify_loc为函数或过程。

三、集合的初始化与赋值引用示例

1、未初始化集合的情形
DECLARE
   TYPE nest_loc_type IS TABLE OF VARCHAR2( 13 );

   loc_tab   nest_loc_type;
BEGIN
   loc_tab( 1 ) := 'NEW YORK';
   loc_tab( 2 ) := 'DALLAS';
   DBMS_OUTPUT.put_line( ' loc_tab(1) value is ' || loc_tab( 1 ) );
   DBMS_OUTPUT.put_line( ' loc_tab(2) value is ' || loc_tab( 2 ) );
END;

DECLARE
*
ERROR at line 1:
ora-06531: Reference to uninitialized collection  -->收到了ora-06531错误提示,变长数组未初始化时会收到同样的错误提示
ora-06512: at line 6
--------------------------------------------------------------------------------------------------------

2、集合为NULL的判断
DECLARE
   TYPE nest_loc_type IS TABLE OF VARCHAR2( 13 );

   loc_tab   nest_loc_type;
BEGIN
   IF loc_tab IS NULL THEN
      DBMS_OUTPUT.put_line( 'Before initialization, the loc_tab is null.' );
   -- While the collection  is null, we cannot check its COUNT attribute.
   --   DBMS_OUTPUT.PUT_LINE('It has ' || loc_tab.COUNT || ' elements.');
   ELSE
      DBMS_OUTPUT.put_line( 'Before initialization, the loc_tab is not null.' );
   END IF;

   loc_tab     := nest_loc_type( );         --> initialize empty nest table

   IF loc_tab IS NULL THEN
      DBMS_OUTPUT.put_line( 'After initialization, the loc_tab is null.' );
   ELSE
      DBMS_OUTPUT.put_line( 'After initialization, the loc_tab is not null.' );
      DBMS_OUTPUT.put_line( 'It has ' || loc_tab.COUNT || ' elements.' );
   END IF;
END;

Before initialization, the loc_tab is null.
After initialization, the loc_tab is not null.
It has 0 elements.

PL/SQL procedure successfully completed.
--------------------------------------------------------------------------------------------------------

3、使用空构造函数在声明时进行初始化
-->使用该方法初始化之后,表明嵌套表或变成数组是空的,但是非NULL,在执行块再对其赋值
-->下面对变长数组进行初始化
DECLARE
   TYPE varry_loc_type IS VARRAY( 10 ) OF scott.dept.loc%TYPE;

   varry_loc_tab   varry_loc_type := varry_loc_type( );                 -->仅仅是在集合变量之后使用空构造函数
BEGIN
   varry_loc_tab( 1 ) := 'NEW YORK';                                    -->尽管变长数组被初始化,但仍然不能直接赋值        
   varry_loc_tab( 2 ) := 'DALLAS';                                      -->这是由变长数组和嵌套表特性决定需要先做extend   
   DBMS_OUTPUT.put_line( ' varry_loc_tab(1) value is ' || varry_loc_tab( 1 ) );
   DBMS_OUTPUT.put_line( ' varry_loc_tab(2) value is ' || varry_loc_tab( 2 ) );
END;

DECLARE
*
ERROR at line 1:
ora-06533: subscript beyond count
ora-06512: at line 6
--------------------------------------------------------------------------------------------------------

4、使用空构造函数在声明时进行初始化,执行块使用extend方式扩展后赋值
DECLARE
   TYPE varry_loc_type IS VARRAY( 10 ) OF scott.dept.loc%TYPE;

   varry_loc_tab   varry_loc_type := varry_loc_type( );              -->仅仅是在集合变量之后使用空构造函数
BEGIN
   varry_loc_tab.EXTEND;                                             -->需要使用extend方式扩展集合容量
   varry_loc_tab( 1 ) := 'NEW YORK';
   varry_loc_tab.EXTEND;                                             -->需要使用extend方式扩展集合容量
   varry_loc_tab( 2 ) := 'DALLAS';
   DBMS_OUTPUT.put_line( ' varry_loc_tab(1) value is ' || varry_loc_tab( 1 ) );
   DBMS_OUTPUT.put_line( ' varry_loc_tab(2) value is ' || varry_loc_tab( 2 ) );
END;
--------------------------------------------------------------------------------------------------------

5、嵌套表的初始化,使用构造函数在执行块直接初始化并赋值
DECLARE
   TYPE nest_loc_type IS TABLE OF VARCHAR2( 13 );

   loc_tab   nest_loc_type;
BEGIN
   loc_tab     :=
      nest_loc_type( 'NEW YORK'           -->使用声明时的类型nest_loc_type函数来进行初始化
                    ,'DALLAS'
                    ,'CHICAGO'
                    ,'BOSTON' );

   FOR i IN 1 .. loc_tab.COUNT
   LOOP
      DBMS_OUTPUT.put_line( 'loc_tab(' || i || ') value is ' || loc_tab( i ) );
   END LOOP;
END;
--------------------------------------------------------------------------------------------------------

6、含有NOT NULL嵌套表的初始化
DECLARE
   TYPE loc_type IS TABLE OF VARCHAR2( 13 ) NOT NULL;      -->定义了NOT NULL约束条件

   loc_tab   loc_type;
BEGIN
   loc_tab     :=
      loc_type( 'NEW york'
               ,NULL                                       -->构造时传递了NULL值
               ,NULL
               ,'boston' );

   FOR i IN 1 .. loc_tab.COUNT
   LOOP
      DBMS_OUTPUT.put_line( 'loc_tab(' || i || ') value is ' || loc_tab( i ) );
   END LOOP;
END;

-->由于存在not null约束,初始化传递null值则收到错误提示
ERROR at line 8:
ora-06550: line 8, column 17:
pls-00567: cannot pass NULL to a NOT NULL constrained formal parameter
ora-06550: line 9, column 17:
pls-00567: cannot pass NULL to a NOT NULL constrained formal parameter
ora-06550: line 6, column 4:
pl/SQL: Statement ignored
--------------------------------------------------------------------------------------------------------

7、变长数组的初始化,使用构造函数直接初始化并赋值
-->变长数组的初始化与嵌套表一样,可以使用构造函数直接初始化并赋值
DECLARE
   TYPE varry_loc_type IS VARRAY( 10 ) OF scott.dept.loc%TYPE;

   varry_loc_tab   varry_loc_type;
BEGIN
   varry_loc_tab :=
      varry_loc_type( 'NEW YORK'
                     ,'DALLAS'
                     ,'CHICAGO'
                     ,'BOSTON' );

   FOR i IN varry_loc_tab.FIRST .. varry_loc_tab.LAST   -->注意此处使用了集合方法中的函数first和last来控制循环步长
   LOOP
      DBMS_OUTPUT.put_line( 'varry_loc_tab(' || i || ') value is ' || varry_loc_tab( i ) );
   END LOOP;
END;
--------------------------------------------------------------------------------------------------------

8、声明时初始化(构造)、并赋值
DECLARE
   TYPE nest_loc_type IS TABLE OF VARCHAR2( 13 ) NOT NULL;

   nest_loc_tab   nest_loc_type := nest_loc_type( 'NEW YORK', 'DALLAS', 'CHICAGO' ); -->在声明时直接初始化并赋值
BEGIN
   FOR i IN 1 .. nest_loc_tab.COUNT    -->注意此处调用了集合操作方法中的count函数
   LOOP
      DBMS_OUTPUT.put_line( 'nest_loc_tab(' || i || ') value is ' || nest_loc_tab( i ) );
   END LOOP;
END;
--------------------------------------------------------------------------------------------------------

9、SQL语句中使用构造函数
CREATE OR REPLACE TYPE mail_type IS TABLE OF VARCHAR2( 100 );   -->创建一个嵌套表类型

CREATE TABLE tb_tmp                   -->创建表tb_tmp
(
   empno   NUMBER( 4 )
  ,ename   VARCHAR2( 10 )
  ,mail    mail_type                  -->列mail的类型为mail_type
)
NESTED TABLE mail                     -->注意此处需要指定嵌套表的存储方式
   STORE AS mail_tab;

INSERT INTO tb_tmp
   SELECT 8888, 'Jack', mail_type( 'Jack@yahoo.com', 'Jack@163.com' ) FROM dual;  -->插入数据时需要使用构造函数

10、集合与集合之间的赋值
-->下面的例子声明了两个变长数组last_name_type和surname_type
DECLARE
   TYPE last_name_type IS VARRAY( 3 ) OF VARCHAR2( 64 );

   TYPE surname_type IS VARRAY( 3 ) OF VARCHAR2( 64 );

   -->下面声明了两个相同类型的变长数组并为其赋值,group1和group2使用了相同的构造函数
   group1   last_name_type := last_name_type( 'Jones', 'Wong', 'Marceau' );
   group2   last_name_type := last_name_type( 'Klein', 'Patsos', 'Singh' );
   -->下面的group3使用了surname_type作为类型
   group3   surname_type := surname_type( 'Trevisi', 'Macleod', 'Marquez' );
BEGIN
   group1      := group2;                           -- >group1 和group2之间可以相互赋值
--   group3 := group2;                             -->raises an error    PLS-00382: expression is of wrong type
END;
-- >group3和group2则不能赋值,因为两者为不同的数据类型
-->尽管last_name_type与surname_type类型定义是相同的,但其实例化后,其集合变量不能互相赋值
--------------------------------------------------------------------------------------------------------

11、使用NULL值集合为集合赋值
DECLARE
   TYPE nest_loc_type IS TABLE OF VARCHAR2( 30 );

   nest_loc_tab         nest_loc_type := nest_loc_type( 'NEW YORK', 'DALLAS', 'CHICAGO' );
   empty_nest_loc_tab   nest_loc_type;                --> 嵌套表empty_nest_loc_tab没有初始化,此时被自动置为NULL
BEGIN
   IF nest_loc_tab IS NOT NULL THEN
      DBMS_OUTPUT.put_line( 'OK, at first nest_loc_tab is not null.' );
   END IF;

   nest_loc_tab := empty_nest_loc_tab;                -->将empty_nest_loc_tab的值(NULL)嵌套表赋值给nest_loc_tab

   IF nest_loc_tab IS NULL THEN                       --> 此时nest_loc_tab被置为NULL,相当于没有初始化
      DBMS_OUTPUT.put_line( 'OK, now nest_loc_tab has become null.' );
   END IF;

   nest_loc_tab := nest_loc_type( 'NEW YORK', 'DALLAS', 'CHICAGO' );  -->此时如果后续需要使用该嵌套表,应重新初始化它
END;

12、记录类型的变长数组的初始化、赋值与元素引用
DECLARE
   TYPE emp_name_rec IS RECORD                             -->声明一个基于用户定义的记录类型
   (
      firstname   employees.first_name%TYPE
     ,lastname    employees.last_name%TYPE
     ,hiredate    employees.hire_date%TYPE
   );

   TYPE emplist_arr IS VARRAY( 10 ) OF emp_name_rec;       -->声明一个基于记录的变长数组,且最大尺寸为10

   seniorsalespeople   emplist_arr;                        -->声明基于记录的变长数组变量

   CURSOR c1 IS                                            -->声明游标,其列前面的记录类型相对照
      SELECT first_name, last_name, hire_date FROM employees;

   TYPE nameset IS TABLE OF c1%ROWTYPE;                    -->声明基于游标的记录类型

   seniorten           nameset;                            -->声明基于游标记录类型的变量
   endcounter          NUMBER := 10;                       -->变量endcounter计数器
BEGIN
   seniorsalespeople := emplist_arr( );                    -->初始化集合

   SELECT first_name, last_name, hire_date                 -->从表中提取数据,且使用了BULK COLLECT INTO方式
   BULK   COLLECT INTO seniorten
   FROM   employees
   WHERE  job_id = 'SA_REP'
   ORDER BY hire_date;

   IF seniorten.LAST > 0 THEN
      IF seniorten.LAST < 10 THEN                          -->如果小于10,则缩小变长数组的最大尺寸
         endcounter  := seniorten.LAST;
      END IF;

      FOR i IN 1 .. endcounter     -->使用循环将游标类型变量中的元素逐条赋值给记录的变长数组变量seniorsalespeople并输出
      LOOP
         seniorsalespeople.EXTEND( 1 );
         seniorsalespeople( i ) := seniorten( i );
         DBMS_OUTPUT.
         put_line(seniorsalespeople(i).lastname||', '||seniorsalespeople(i).firstname||', '||seniorsalespeople(i).hiredate);
      END LOOP;
   END IF;
END;

	上面的这个例子是一复合的数据类型,比单一的集合类型更为复杂。我们知道集合通常是针对单列多行数据而言,而记录则是单行多列。两
者的综合,则此时就等同于数据库中的一张二维表。示例中首先声明用户定义的记录类型以及变长数组,接下来基于这两者声明变量。后面使用
基于游标的记录类型来申明变量seniorten与前面的变量seniorsalespeople相对应,seniorten变量用于存储后面的SQL语句批量提取的数据集。
后面使用了一个for循环来从seniorten变量取出数据并赋值为seniorsalespeople。注:在这个例子中变量seniorten存储的记录超出了变长数组
的最大尺寸,因此后续的被丢弃。

13、记录类型的嵌套表的初始化、赋值与元素引用
DECLARE
   TYPE emp_name_rec IS RECORD
   (
      firstname   employees.first_name%TYPE
     ,lastname    employees.last_name%TYPE
     ,hiredate    employees.hire_date%TYPE
   );

   TYPE emplist_tab IS TABLE OF emp_name_rec;

   seniorsalespeople   emplist_tab;
   endcounter          NUMBER := 10;

   TYPE empcurtyp IS REF CURSOR;    -->声明游标变量类型 

   emp_cv              empcurtyp;   -->声明游标变量类型的变量emp_cv
BEGIN
   OPEN emp_cv FOR
      SELECT first_name, last_name, hire_date
      FROM   employees
      WHERE  job_id = 'SA_REP'
      ORDER BY hire_date;

   FETCH emp_cv
   BULK   COLLECT INTO seniorsalespeople;    -->使用BULK   COLLECT INTO 方式一次将数据加载到seniorsalespeople变量

   CLOSE emp_cv;

   IF seniorsalespeople.LAST > 0 THEN
      IF seniorsalespeople.LAST < 10 THEN
         endcounter  := seniorsalespeople.LAST;
      END IF;

      FOR i IN 1 .. endcounter
      LOOP
         DBMS_OUTPUT.
         put_line(seniorsalespeople(i).lastname||', '||seniorsalespeople(i).firstname||', '||seniorsalespeople(i).hiredate);
      END LOOP;
   END IF;
END;
-->Author : Robinson Cheng
-->Blog   : http://blog.csdn.net/robinson_0612

	上面的这个例子稍有不同于前面的例子,使用的基于用户定义记录的嵌套表方式,且使用了游标变量类型。在fetch时直接将数据fetch 到
集合变量seniorsalespeople中,此时不需要使用extend方式来扩展。	

四、总结 1、对于集合类型在为其赋值之前,需要对集合进行初始化。而联合数组不需要初始化而直接进行赋值。 2、在声明嵌套表与变长数组时,这些集合类型会被自动置为NULL,即集合不存在任何元素。而不是集合中的元素为NULL。 3、集合类型的初始化方法是是直接使用声明时的同名类型构造器来对集合进行初始化。 4、集合类型的初始化方法有多种,可以在声明时初始化,也可以在执行块初始化。 5、集合类型的赋值可以在声明块声明时赋值,也可以在执行块执行时使用extend方式扩展后再赋值。 6、集合类型的初始化过程连同赋值可以在声明集合的同时使用构造函数直接进行初始化并赋值,从而一步完成。 7、SQL语句中也需要使用构造函数来操作集合数据。 8、注意本文描述中的集合初始化后为空的理解。初始化后为空表示的是一个空(empty)集合,而未初始化时是NULL(UNKNOWN)值。 9、集合与集合之间的赋值需要声明的为同一类型的变量之间才可以赋值,否则收到错误提示。

10、注意理解复合类型之间(嵌套表和变长数组中嵌有PL/SQL记录)的变量元素间的传递以及集合方法BULK COLLECT INTO,LAST,EXTEND等。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • mysql explain type连接类型示例

    Leshami
  • 对参数FAST_START_MTTR_TARGET = 0 的误解及设定

    --===============================================

    Leshami
  • 收集统计信息导致索引被监控

          对于索引的调整,我们可以通过Oracle提供的索引监控特性来跟踪索引是否被使用。尽管该特性并未提供索引使用的频度,但仍不失为我们参考的方式之一。然而...

    Leshami
  • MySQl 事务测试

    5、测试2:当使用read committed的时候,就不会出现脏读的情况了,当时会出现不可重复读的问题

    Parker
  • 达梦数据库适配问题

    达梦数据库和Oracle同样,对字段的长度有严格的规范,当然Mysql也是有的,但是默认是不启用的,哪怕超出了,也会自动扩容,但是Oracle和达梦是不会的;

    彼岸舞
  • 数据分析实战—北京二手房房价分析

    目的:本篇给大家介绍一个数据分析的初级项目,目的是通过项目了解如何使用Python进行简单的数据分析。

    用户2769421
  • 网络通信 & 初识socket

    即Client/Server (C/S) 结构,是大家熟知的软件系统体系结构,通过将任务合理分配到Client端和Server端,降低了系统的通讯开销,需要安装...

    py3study
  • 利用 Pandas 的 transform 和 apply 来处理组级别的丢失数据

    根据 Businessbroadway 的一项分析,数据专业人员将会花高达 60% 的时间用于收集、清理和可视化数据。

    AI研习社
  • 10个高效的pandas技巧

    原题 | 10 Python Pandas tricks that make your work more efficient

    材ccc
  • 【技巧】11 个 Python Pandas 小技巧让你更高效

    Pandas是一个在Python中广泛应用的数据分析包。市面上有很多关于Pandas的经典教程,但本文介绍几个隐藏的炫酷小技巧,我相信这些会对你有所帮助。

    昱良

扫码关注云+社区

领取腾讯云代金券