威尼斯wns.9778官网 > 计算机教程 > 数组 类型 在 存储过程中 使用

原标题:数组 类型 在 存储过程中 使用

浏览次数:133 时间:2020-04-30

前言

本文主要介绍的是关于sql Oracle匿名TABLE/VARRAY类型的相关内容,在Oracle中,我有时会创建一些这样的结构

oracle 之定义数组类型

注:本文来源:《oracle 之定义数组类型

oracle 数组类型,没有现成的类型,但是可以自己随意定义,很方便。

Oracle 数组可以分为定长数组和可变长的数组两类。以下主要是一维数组介绍:

SELECT * FROM TABLE(STRINGS('a', 'b', 'c'))SELECT * FROM TABLE(NUMBERS(1, 2, 3))

1:定长数组:

  1 /*定长字符数组,数组大小为10*/
  2 declare
  3 type v_arr is varray(10) of varchar2(30);
  4 my_arr v_arr;
  5 my_arr:=v_arr('1','2','3');
  6 begin
  7   for i in 1..my_arr.count
  8   loop
  9       dbms_output_line(my_arr(i));
 10   end loop;
 11 end;
 12 

显然,我可以为上述声明我自己的类型.我可以在TABLE和VARRAY之间进行选择.例如:

2:变长数组:

  1 /*可变长字符数组,元素大小30,索引标号integer类型自增长*/
  2 declare
  3 type v_table is table of varchar2(30) index by binary_integer;
  4 --类型可以是前面的类型定义,index by binary_integer子句代表以符号整数为索引,   
  5 --这样访问表类型变量中的数据方法就是“表变量名(索引符号整数)”。   
  6 my_table v_table;
  7 begin
  8       for i in 1..20
  9      loop
 10           my_table(i):=i;
 11           dbms_output.put_line(my_table(i));
 12       end loop;
 13 end;
 14 
CREATE TYPE STRINGS AS TABLE OF VARCHAR2(100);CREATE TYPE NUMBERS AS VARRAY(100) OF NUMBER(10);

Oracle存储过程 数组集合的使用

注:本文来源《 Oracle存储过程 数组集合的使用  》

在这种特殊情况下,另一个解决方案是写东西

1 说明

1.1 RECORD

定义记录数据类型。它类似于C语言中的结构数据类型(STRUCTURE),PL/SQL提供了将几个相关的、分离的、基本数据类型的变量组成一个整体的方法,即RECORD复合数据类型。在使用记录数据类型变量时,需要在声明部分先定义记录的组成、记录的变量,然后在执行部分引用该记录变量本身或其中的成员。

定义记录数据类型的语法如下:

  1 TYPE RECORD_NAME IS RECORD(
  2 
  3 V1 DATA_TYPE1 [NOT NULL][:=DEFAULT_VALUE],
  4 
  5 V2 DATA_TYPE2 [NOT NULL][:=DEFAULT_VALUE],
  6 
  7 VN DATA_TYPEN [NOT NULL][:=DEFAULT_VALUE]);
  8 
1.2 VARRAY

数组是具有相同数据类型的一组成员的集合。每个成员都有一个唯一的下标,它取决于成员在数组中的位置。在PL/SQL中,数组数据类型是VARRAY(variable array,即可变数组)。

定义VARRAY数据类型的语法如下:

  1 TYPE VARRAY_NAMEIS VARRAY(SIZE) OF ELEMENT_TYPE [NOT NULL];

其中,varray_name是VARRAY数据类型的名称,size是正整数,表示可以容纳的成员的最大数量,每个成员的数据类型是element_typeo默认时,成员可以取空值,否则需要使用NOT NULL加以限制。

1.3 TABLE

定义记录表(或索引表)数据类型。它与记录类型相似,但它是对记录类型的扩展。它可以处理多行记录,类似于C语言中的二维数组,使得可以在PL/SQL中模仿数据库中的表。

定义记录表类型的语法如下:

  1 TYPE TABLE NAME IS TABLE OF ELEMENT_TYPE [NOT NULL]
  2 
  3 INDEX BY [BINARY_INTEGER|PLS_INTEGER|VARRAY2];
  4 

关键字INDEX BY表示创建一个主键索引,以便引用记录表变量中的特定行。

BINARY_INTEGER的说明

如语句:TYPE NUMBERS  IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;其作用是,加了”INDEX BYBINARY_INTEGER ”后,NUMBERS类型的下标就是自增长,NUMBERS类型在插入元素时,不需要初始化,不需要每次EXTEND增加一个空间。

而如果没有这句话“INDEXBY BINARY_INTEGER”,那就得要显示对初始化,且每插入一个元素到NUMBERS类型的TABLE中时,都需要先EXTEND。

SELECT 'a' FROM DUAL UNION ALLSELECT 'b' FROM DUAL UNION ALLSELECT 'c' FROM DUAL

2 举例

2.1 创建表结构以及数据准备
  1 --组织机构结构表  
  2 CREATE TABLE SF_ORG
  3 (
  4 ORG_ID INT NOT NULL, --组织机构主键ID  
  5 ORG_NAME VARCHAR2(50),--组织机构名称  
  6 PARENT_ID INT--组织机构的父级  
  7 )
  8 
  9 --一级组织机构  
 10 INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(1, '一级部门1',0);
 11 
 12 --二级部门  
 13 
 14 INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(2, '二级部门2',1);
 15 INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(3, '二级部门3',1);
 16 INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(4, '二级部门4',1);

2.2 RECORD的使用举例

先定义一个只与SF_ORG表中某几个列的数据类型相同的记录数据类型TYPE_ORG_RECORD,然后声明一个该数据类型的记录变量V_ORG_RECORD,最后用替换变量&ORG_ID接受输入的雇员编码,查询并显示该雇员的这几列中的信息。注意,在使用RECORD数据类型的变量时要用“.”运算符指定记录变量名限定词。

一个记录类型的变量只能保存从数据库中查询出的一行记录,如果查询出了多行记录,就会出现错误。

  1 DECLARE
  2   TYPE TYPE_ORG_RECORD IS RECORD(
  3   V_ORG_NAME SF_ORG.ORG_NAME%TYPE,
  4   V_PARENT_ID SF_ORG.PARENT_ID%TYPE);
  5   V_ORG_RECORD TYPE_ORG_RECORD;
  6 BEGIN
  7   SELECT ORG_NAME,PARENT_ID INTO V_ORG_RECORD
  8   FROM SF_ORG SO
  9   WHERE SO.ORG_ID=&ORG_ID;
 10   DBMS_OUTPUT.PUT_LINE('部门名称:' || V_ORG_RECORD.V_ORG_NAME);
 11   DBMS_OUTPUT.PUT_LINE('上级部门编码:' || TO_CHAR(V_ORG_RECORD.V_PARENT_ID));
 12 END;

2.3 VARRAY的使用举例

先定义一个能保存5个VARCHAR2(25)数据类型的成员的VARRAY数据类型ORG_VARRAY_TYPE,然后声明一个该数据类型的VARRAY变量V_ORG_VARRAY,最后用与ORG_VARRAY_TYPE数据类型同名的构造函数语法给V_ORG_VARRAY变量赋予初值并显示赋值结果。

注意,在引用数组中的成员时.需要在一对括号中使用顺序下标,下标从1开始而不是从0开始。

  1 DECLARE
  2   TYPE ORG_VARRAY_TYPE IS VARRAY(5) OF VARCHAR2(25);
  3   V_ORG_VARRAY ORG_VARRAY_TYPE;
  4 BEGIN
  5   V_ORG_VARRAY := ORG_VARRAY_TYPE('1','2','3','4','5');
  6   DBMS_OUTPUT.PUT_LINE('输出1:' || V_ORG_VARRAY(1) || '、'|| V_ORG_VARRAY(2) || '、'|| V_ORG_VARRAY(3) || '、'|| V_ORG_VARRAY(4));
  7   DBMS_OUTPUT.PUT_LINE('输出2:' || V_ORG_VARRAY(5));
  8   V_ORG_VARRAY(5) := '5001';
  9   DBMS_OUTPUT.PUT_LINE('输出3:' || V_ORG_VARRAY(5));
 10 END;

2.4 TABLE使用举例
2.4.1 存储单列多行

这个和VARRAY类似。但是赋值方式稍微有点不同,不能使用同名的构造函数进行赋值。具体的如下:

  1 DECLARE
  2   TYPE ORG_TABLE_TYPE IS TABLE OF VARCHAR2(25)
  3   INDEX BY BINARY_INTEGER;
  4   V_ORG_TABLE ORG_TABLE_TYPE;
  5 BEGIN
  6   V_ORG_TABLE(1) := '1';
  7   V_ORG_TABLE(2) := '2';
  8   V_ORG_TABLE(3) := '3';
  9   V_ORG_TABLE(4) := '4';
 10   V_ORG_TABLE(5) := '5';
 11   DBMS_OUTPUT.PUT_LINE('输出1:' || V_ORG_TABLE(1) || '、'|| V_ORG_TABLE(2) || '、'|| V_ORG_TABLE(3) || '、'|| V_ORG_TABLE(4));
 12   DBMS_OUTPUT.PUT_LINE('输出2:' || V_ORG_TABLE(5));
 13 END;

2.4.2 存储多列多行和ROWTYPE结合使用

采用bulkcollect可以将查询结果一次性地加载到collections中。而不是通过cursor一条一条地处理。

  1 DECLARE
  2    TYPE T_TYPE IS TABLE OF SF_ORG%ROWTYPE;
  3    V_TYPE  T_TYPE;
  4  BEGIN
  5     SELECT ORG_ID,ORG_NAME,PARENT_ID BULK COLLECT INTO V_TYPE
  6     FROM SF_ORG
  7     WHERE SF_ORG.ORG_ID <= 3;
  8 
  9     FOR V_INDEX IN V_TYPE.FIRST .. V_TYPE.LAST LOOP
 10         DBMS_OUTPUT.PUT_LINE(V_TYPE(V_INDEX).C1 || ' ' || V_TYPE(V_INDEX).C2);
 11     END LOOP;
 12  END;

2.4.3 存储多列多行和RECORD结合使用

采用bulkcollect可以将查询结果一次性地加载到collections中。而不是通过cursor一条一条地处理。

  1 DECLARE
  2    TYPE TEST_EMP IS RECORD
  3    (
  4     C1 SF_ORG.ORG_NAME%TYPE,
  5     C2 SF_ORG.PARENT_ID%TYPE
  6    );
  7    TYPE T_TYPE IS TABLE OF TEST_EMP;
  8    V_TYPE  T_TYPE;
  9  BEGIN
 10     SELECT ORG_NAME,  PARENT_ID BULK COLLECT INTO V_TYPE
 11     FROM SF_ORG
 12     WHERE SF_ORG.ORG_ID <= 3;
 13 
 14     FOR V_INDEX IN V_TYPE.FIRST .. V_TYPE.LAST LOOP
 15         DBMS_OUTPUT.PUT_LINE(V_TYPE(V_INDEX).C1 || ' ' || V_TYPE(V_INDEX).C2);
 16     END LOOP;
 17  END;

威尼斯wns.9778官网,

但是我可能会有更复杂的例子,我真的需要一个TABLE / VARRAY类型.那么如果我的SQL在一个未知的系统上运行,我不能创建类型,因为我可能没有必要的资助?

Oracle存储过程自定义数组定义与使用

最近为公司项目数据库通过存储过程做归档,需要用到自定义数组

百度结果中很多写的都不是很清晰,可变长数组定义好后,如何使用。

在此做个记录:

定义:

type id_array is table of number(15) index by binary_integer;

acb_ids   id_array;

这个定义方式适用在package,如果是纯粹在存储过程中自定义类型请百度 。

使用:

acb_ids(acb_ids.count 1) := c_account_books_cbs_rec.acb_id;

网上很多实例是这样的:acb_ids(acb_ids.count) := c_account_books_cbs_rec.acb_id;这样写是没有用的,因为这个时候acb_ids是空的,那么acb_ids.count也是无效的,acb_ids在使用时回会报"未找到任何数据",因此需要acb_ids.count 1。

c_account_books_cbs_rec为游标遍历中的一个对象,上述代码意思是将游标数据中每行记录的ID放到自定义数组中,方便存储过程的值返回或者游标遍历外程序体中使用。

for x in 1 .. acb_ids.count loop
        do something;
end loop;

所以我的问题是:Oracle是否知道任何Oracle实例上可用的“匿名”TABLE / VARRAY类型?类似于Postgres / H2 / HSQLDB的简单ARRAY类型?

存储过程中定义参数类型为数组

注意:本文来源:《存储过程中定义参数类型为数组

更新:我主要从Java运行这个SQL,如果这是相关的.不需要向我解释PL / SQL,我只是在寻找匿名SQL数组类型(即“匿名”独立存储类型).如果根本不存在,答案是否定的

1:存储过程

  1 Procedure Update_Batch_Id(p_entity_id in Number,
  2                             p_vdr_id    In fnd_table_of_number) is
  3   begin
  4 
  5     For i In 1 .. p_vdr_id.COUNT Loop
  6       update cux_table_header cvs
  7          set cvs.attribute10 = p_entity_id
  8        where cvs.header_id = p_vdr_id(i);
  9     End Loop;
 10   end;

本文由威尼斯wns.9778官网发布于计算机教程,转载请注明出处:数组 类型 在 存储过程中 使用

关键词:

上一篇:mysql 5.6.23 安装配置环境变量教程

下一篇:没有了