Oracle 存储过程数组一维数组二维数组

最新推荐文章于 2023-09-05 08:41:29 发布

Alex许恒

最新推荐文章于 2023-09-05 08:41:29 发布

阅读量5.8k

点赞数 1

分类专栏： Oracle 存储过程

Oracle 同时被 2 个专栏收录

101 篇文章 6 订阅

订阅专栏

存储过程

61 篇文章 2 订阅

订阅专栏

Oracle存储过程一维数组二维数组 (范例)

/*
在PL/SQL中是没有数组(Array)概念的。但是如果程序员想用Array的话，就得变通一下，用TYPE 和Table of Record来代替多维数组，一样挺好用的。

emp_type 就好象一个table 中的一条record 一样，里面有id, name,gender等。emp_type_array 象个table, 里面含有一条条这样的record (emp_type)，就象多维数组一样。
*/

--单维数组
/*
DECLARE
TYPE emp_ssn_array IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;

best_employees emp_ssn_array;
worst_employees emp_ssn_array;

BEGIN
best_employees(1) := '123456';
best_employees(2) := '888888';

worst_employees(1) := '222222';
worst_employees(2) := '666666';

FOR i IN 1..best_employees.count LOOP
DBMS_OUTPUT.PUT_LINE('i='|| i || ', best_employees= ' ||best_employees(i)
|| ', worst_employees= ' ||worst_employees(i));
END LOOP;

END;
*/

--多维数组
DECLARE

TYPE emp_type IS RECORD
(
emp_id scott.emp.empno%TYPE,
emp_name scott.emp.ename%TYPE,
emp_job scott.emp.job%TYPE );

TYPE emp_type_array IS TABLE OF emp_type INDEX BY BINARY_INTEGER;

emp_rec_array emp_type_array;
emp_rec emp_type;

BEGIN
emp_rec.emp_id := 7788;
emp_rec.emp_name := 'Barbara';
emp_rec.emp_job := 'Female';

emp_rec_array(1) := emp_rec;

emp_rec.emp_id := 7900;
emp_rec.emp_name := 'Rick';
emp_rec.emp_job := 'Male';

emp_rec_array(2) := emp_rec;

FOR i IN 1..emp_rec_array.count LOOP
DBMS_OUTPUT.PUT_LINE('i='||i
||', emp_id ='||emp_rec_array(i).emp_id
||', emp_name ='||emp_rec_array(i).emp_name
||', emp_job = '||emp_rec_array(i).emp_job);
END LOOP;

END;

https://blog.csdn.net/huanyingfengxing/article/details/41008143

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

2017年07月28日 09:51:59 芒果炖牛肉阅读数：5876 标签：存储过程 ORACLE 集合更多

个人分类： Oracle

--转自:http://blog.csdn.net/liangweiwei130/article/details/38223319#comments

1 说明

1.1 RECORD

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

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

TYPE RECORD_NAME IS RECORD(

V1 DATA_TYPE1 [NOT NULL][:=DEFAULT_VALUE],

V2 DATA_TYPE2 [NOT NULL][:=DEFAULT_VALUE],

VN DATA_TYPEN [NOT NULL][:=DEFAULT_VALUE]);

1.2 VARRAY

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

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

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

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

1.3 TABLE

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

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

TYPE TABLE NAME IS TABLE OF ELEMENT_TYPE [NOT NULL]

INDEX BY [BINARY_INTEGER|PLS_INTEGER|VARRAY2];

关键字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。

2 举例

2.1 创建表结构以及数据准备

[sql] view plain copy

--组织机构结构表
CREATE TABLE SF_ORG
(
ORG_ID INT NOT NULL, --组织机构主键ID
ORG_NAME VARCHAR2(50),--组织机构名称
PARENT_ID INT--组织机构的父级
)
--一级组织机构
INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(1, '一级部门1',0);
--二级部门
INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(2, '二级部门2',1);
INSERT INTO SF_ORG(ORG_ID, ORG_NAME, PARENT_ID) VALUES(3, '二级部门3',1);
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数据类型的变量时要用“.”运算符指定记录变量名限定词。

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

[sql] view plain copy

DECLARE
TYPE TYPE_ORG_RECORD IS RECORD(
V_ORG_NAME SF_ORG.ORG_NAME%TYPE,
V_PARENT_ID SF_ORG.PARENT_ID%TYPE);
V_ORG_RECORD TYPE_ORG_RECORD;
BEGIN
SELECT ORG_NAME,PARENT_ID INTO V_ORG_RECORD
FROM SF_ORG SO
WHERE SO.ORG_ID=&ORG_ID;
DBMS_OUTPUT.PUT_LINE('部门名称：' || V_ORG_RECORD.V_ORG_NAME);
DBMS_OUTPUT.PUT_LINE('上级部门编码：' || TO_CHAR(V_ORG_RECORD.V_PARENT_ID));
END;

2.3 VARRAY的使用举例

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

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

[sql] view plain copy

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

2.4 TABLE使用举例

2.4.1 存储单列多行

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

[sql] view plain copy

DECLARE
TYPE ORG_TABLE_TYPE IS TABLE OF VARCHAR2(25)
INDEX BY BINARY_INTEGER;
V_ORG_TABLE ORG_TABLE_TYPE;
BEGIN
V_ORG_TABLE(1) := '1';
V_ORG_TABLE(2) := '2';
V_ORG_TABLE(3) := '3';
V_ORG_TABLE(4) := '4';
V_ORG_TABLE(5) := '5';
DBMS_OUTPUT.PUT_LINE('输出1：' || V_ORG_TABLE(1) || '、'|| V_ORG_TABLE(2) || '、'|| V_ORG_TABLE(3) || '、'|| V_ORG_TABLE(4));
DBMS_OUTPUT.PUT_LINE('输出2：' || V_ORG_TABLE(5));
END;

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

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

[sql] view plain copy

DECLARE
TYPE T_TYPE IS TABLE OF SF_ORG%ROWTYPE;
V_TYPE T_TYPE;
BEGIN
SELECT ORG_ID,ORG_NAME,PARENT_ID BULK COLLECT INTO V_TYPE
FROM SF_ORG
WHERE SF_ORG.ORG_ID <= 3;
FOR V_INDEX IN V_TYPE.FIRST .. V_TYPE.LAST LOOP
DBMS_OUTPUT.PUT_LINE(V_TYPE(V_INDEX).C1 || ' ' || V_TYPE(V_INDEX).C2);
END LOOP;
END;

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

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

[sql] view plain copy

DECLARE
TYPE TEST_EMP IS RECORD
(
C1 SF_ORG.ORG_NAME%TYPE,
C2 SF_ORG.PARENT_ID%TYPE
);
TYPE T_TYPE IS TABLE OF TEST_EMP;
V_TYPE T_TYPE;
BEGIN
SELECT ORG_NAME, PARENT_ID BULK COLLECT INTO V_TYPE
FROM SF_ORG
WHERE SF_ORG.ORG_ID <= 3;
FOR V_INDEX IN V_TYPE.FIRST .. V_TYPE.LAST LOOP
DBMS_OUTPUT.PUT_LINE(V_TYPE(V_INDEX).C1 || ' ' || V_TYPE(V_INDEX).C2);
END LOOP;
END;

https://blog.csdn.net/redBeefNoodles/article/details/76228472

Oracle存储过程例子：运用了正则表达式、数组等

代码

Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/-->CREATE OR REPLACE PROCEDURE "SP_GET_ARTICLE_DATA"
(article_detail_id_$ number,
Record_Md5_ID_$ varchar2,
content_text_$ clob,
ReturnValue out number
) is
begin
declare
--自定义数组
TYPE StringArray IS table OF VARCHAR2(1000);

Offset_URL number;
Offset_Email number;
Offset_Phone number;
Offset_Account number;

--数据下标
URL_V_Num number:=1;
Email_V_Num number:=1;
Phone_V_Num number:=1;
Account_V_Num number:=1;

--数据元素存在标志
Is_Exsit_Flag number;

URL varchar2(3000);
Email varchar2(200);
Phone varchar2(100);
Bank_Account varchar2(100);

URL_Array StringArray:=StringArray();
Email_Array StringArray:=StringArray();
Phone_Array StringArray:=StringArray();
Account_Array StringArray:=StringArray();

--常量
URL_RegExp constant varchar2(300):='(((file|gopher|news|nntp|telnet|http|ftp|https|ftps|sftp)://)|(www\.))+(([a-zA-Z0-9\._-]+\.[a-zA-Z]{2,6})|([0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}))(/[a-zA-Z0-9\&'||'%_\./-~-]*)?';
Email_RegExp constant varchar2(200):='[A-Za-z0-9_]+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*';
Phone_RegExp constant varchar2(300):='[+[:digit:]]{1,2}[-\.,[:blank:]]*\d{3}[-\.,[:blank:]]*\d{3}[-\.,[:blank:]]*\d{4}|[+[:digit:]]?[-\.,[:blank:]]*\d{1,2}[-\.,[:blank:]]*\d{3}[-\.,[:blank:]]+\d{4}|\d{3}[-\.,[:blank:]]+\d{4}';
Bank_Account_RegExp constant varchar2(200):='(SA)?[[:digit:]]{22}|(SA)?[[:blank:]]*([[:digit:]]{4}|\d{2})[-\.,[:blank:]]*[[:digit:]]{4}[-\.,[:blank:]]*[[:digit:]]{4}[-\.,[:blank:]]*[[:digit:]]{4}[-\.,[:blank:]]*[[:digit:]]{4}[-\.,[:blank:]]*(\d{4}|\d{2})';
URL_Escape constant varchar2(100):='\.jpg|\.rar|\.zip|\.png|\.gif|\.pdf|\.doc|\.excel|\.swf|\.mpg|\.\.|\.rmvb';

DUP_VAL_INDEX exception;
pragma exception_init(DUP_VAL_INDEX,-0001);

begin
if article_detail_id_$ is not null and article_detail_id_$<>0 then
Offset_URL:=1;
Offset_Email:=1;
Offset_Phone:=1;
Offset_Account:=1;

while Offset_URL<>0 or Offset_Email<>0 or Offset_Phone<>0 or Offset_Account<>0
loop
--获取URL
if Offset_URL<>0 then
select trim(to_char(REGEXP_SUBSTR(content_text_$,URL_RegExp, Offset_URL, 1,'i')))
into URL from dual;
--获取从offset位置开始的第一个符合URL表达式的字符串位置，并赋给offset
select REGEXP_INSTR(content_text_$, URL_RegExp, Offset_URL, 1, 1,'i')
into Offset_URL from dual;
end if;
--获取Email
if Offset_Email<>0 then
select trim(to_char(REGEXP_SUBSTR(content_text_$,Email_RegExp, Offset_Email, 1,'i')))
into Email from dual;
--获取从offset位置开始的第一个符合Email表达式的字符串位置，并赋给offset
select REGEXP_INSTR(content_text_$, Email_RegExp, Offset_Email, 1, 1,'i')
into Offset_Email from dual;
end if;
--获取Phone
if Offset_Phone<>0 then
select trim(to_char(REGEXP_SUBSTR(content_text_$,Phone_RegExp, Offset_Phone, 1,'i')))
into Phone from dual;
--获取从offset位置开始的第一个符合Phone表达式的字符串位置，并赋给offset
select REGEXP_INSTR(content_text_$, Phone_RegExp, Offset_Phone, 1, 1,'i')
into Offset_Phone from dual;
end if;
--获取Bank_Account
if Offset_Account<>0 then
select trim(to_char(REGEXP_SUBSTR(content_text_$,Bank_Account_RegExp, Offset_Account, 1,'i')))
into Bank_Account from dual;
--获取从offset位置开始的第一个符合Phone表达式的字符串位置，并赋给offset
select REGEXP_INSTR(content_text_$, Bank_Account_RegExp, Offset_Account, 1, 1,'i')
into Offset_Account from dual;
end if;

--URL插入到数组中
if (length(URL)>0 and length(URL)<800) and REGEXP_INSTR(URL, URL_Escape, 1, 1, 0, 'i')=0 then
Is_Exsit_Flag :=0;
FOR I IN 1..URL_Array.COUNT LOOP
if URL=URL_Array(I) then
Is_Exsit_Flag:=1;
end if;
END LOOP;
--如果数组中不存在当前值，则插入到数组
if Is_Exsit_Flag=0 then
URL_Array.EXTEND(1);
URL_Array(URL_V_Num):=URL;
URL_V_Num:=URL_V_Num+1;
end if;
end if;
--Email插入到数组中
if (length(Email)>0 and length(Email)<100) then
Is_Exsit_Flag :=0;
FOR I IN 1..Email_Array.COUNT LOOP
if Email=Email_Array(I) then
Is_Exsit_Flag:=1;
end if;
END LOOP;
--如果数组中不存在当前值，则插入到数组
if Is_Exsit_Flag=0 then
Email_Array.EXTEND(1);
Email_Array(Email_V_Num):=Email;
Email_V_Num:=Email_V_Num+1;
end if;
end if;
--Phone插入到数组中
if (length(Phone)>6 and length(Phone)<100) then
Is_Exsit_Flag :=0;
FOR I IN 1..Phone_Array.COUNT LOOP
if Phone=Phone_Array(I) then
Is_Exsit_Flag:=1;
end if;
END LOOP;
--如果数组中不存在当前值，则插入到数组
if Is_Exsit_Flag=0 then
Phone_Array.EXTEND(1);
Phone_Array(Phone_V_Num):=Phone;
Phone_V_Num:=Phone_V_Num+1;
end if;
end if;
--Bank_Account插入到数组中
if (length(Bank_Account)>0 and length(Bank_Account)<100) then
Is_Exsit_Flag :=0;
FOR I IN 1..Account_Array.COUNT LOOP
if Bank_Account=Account_Array(I) then
Is_Exsit_Flag:=1;
end if;
END LOOP;
--如果数组中不存在当前值，则插入到数组
if Is_Exsit_Flag=0 then
Account_Array.EXTEND(1);
Account_Array(Account_V_Num):=Bank_Account;
Account_V_Num:=Account_V_Num+1;
end if;
end if;
end loop;

-- Dbms_Output.put_line('Array is:'||URL_Array(1));

--插入获取到的URL
FOR I IN 1..URL_Array.COUNT LOOP
insert into article_data(article_detail_id,article_record_md5_id,data_type,data_is_url,data_value)
values(article_detail_id_$,Record_Md5_ID_$,'URL','1',URL_Array(I));
END LOOP;
--插入获取到的Email
FOR I IN 1..Email_Array.COUNT LOOP
insert into article_data(article_detail_id,article_record_md5_id,data_type,data_is_url,data_value)
values(article_detail_id_$,Record_Md5_ID_$,'Email','0',Email_Array(I));
END LOOP;
--插入获取到的Phone
FOR I IN 1..Phone_Array.COUNT LOOP
insert into article_data(article_detail_id,article_record_md5_id,data_type,data_is_url,data_value)
values(article_detail_id_$,Record_Md5_ID_$,'Phone','0',Phone_Array(I));
END LOOP;
--插入获取到的Bank_Account
FOR I IN 1..Account_Array.COUNT LOOP
insert into article_data(article_detail_id,article_record_md5_id,data_type,data_is_url,data_value)
values(article_detail_id_$,Record_Md5_ID_$,'Bank Account','0',Account_Array(I));
END LOOP;

end if;
Exception
when NO_DATA_FOUND then
ReturnValue:=-1000;
when DUP_VAL_INDEX then
ReturnValue:=-1001;
when VALUE_ERROR then
ReturnValue:=-1002;
when TOO_MANY_ROWS then
ReturnValue:=-1003;
when OTHERS then
ReturnValue:=-1004;
--Dbms_Output.put_line('The SQLCode is: '||SQLCODE);
--Dbms_Output.put_line('The SQLERRM is: '||SQLERRM);
end;
end SP_GET_ARTICLE_DATA;

分类: Oracel

好文要顶关注我收藏该文

Jason_liu
关注 - 1
粉丝 - 19

+加关注

« 上一篇：XCode6.0的iOS免证书真机测试方法（MAC及黑苹果均有效）[转]
» 下一篇：INSERT INTO 语句的语法错误【 OLE报错，office终端执行SQL没有问题】

posted on 2015-01-26 10:19 Jason_liu 阅读(800) 评论(0) 编辑收藏

https://www.cnblogs.com/jason-liu-blogs/p/4249567.html

Alex许恒

关注

1
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
Oracle 存储过程数组一维数组二维数组

Oracle存储过程一维数组二维数组 (范例)/*在PL/SQL中是没有数组(Array)概念的。但是如果程序员想用Array的话，就得变通一下，用TYPE 和Table of Record来代替多维数组，一样挺好用的。emp_type 就好象一个table 中的一条record 一样，里面有id, name,gender等。emp_type_array 象个table, 里面含有一...
复制链接

扫一扫