普通的数组
语法:TYPE year_type IS TABLE OF number INDEX BY BINARY_INTEGER;
The above single statement containsa lot of meaning. First of all, we aresimply defining our own data type,“year_type.”Make sure that “year_type” is not a variable. It is a user defined data type. According to the above statement, “year_type” is a data type which can hold a set (or table) of values (typically of type “number”),organized with a BINARY_INTEGER index. The BINARY_INTEGER in this scenario simply acts as a location number or position of the memory location within the table (or simply called an index).
The word "table" here has nothing to do with database tables, confusingly. The methods create in-memory arrays
Sample 1:数组的游标可以是任何数字,数组的长度随时可以增加
declare
type year_type is table of number index by binary_integer;
year_sales year_type;
tot_sales number;
begin
year_sales(1990) := 34000;
year_sales(1991) := 45000;
year_sales(1992) := 43000;
tot_sales := year_sales(1990) + year_sales(1991) +
year_sales(1992);
dbms_output.put_line('Total sales: ' || tot_sales);
end;
Sample 2:数组一般用在循环中
declare
type year_type is table of number index by binary_integer;
year_sales year_type;
tot_sales number := 0;
i number;
begin
year_sales(1990) := 34000;
year_sales(1991) := 45000;
year_sales(1992) := 43000;
year_sales(1993) := 13000;
year_sales(1994) := 53000;
for i in 1990..1994
loop
tot_sales := tot_sales + year_sales(i);
dbms_output.put_line('Sales of ' || i || ': ' ||
year_sales(i));
end loop;
dbms_output.put_line('Total sales: ' || tot_sales);
end;
Sample 3: 判断下数组中是否存在这个游标值
declare
type year_type is table of number index by binary_integer;
year_sales year_type;
tot_sales number := 0;
i number;
begin
year_sales(1990) := 34000;
year_sales(1991) := 45000;
year_sales(1992) := 43000;
year_sales(1996) := 13000;
year_sales(1998) := 53000;
for i in 1990..2000
loop
if year_sales.exists(i) then
tot_sales := tot_sales + year_sales(i);
dbms_output.put_line('Sales of ' || i || ': '
|| year_sales(i));
end if;
end loop;
dbms_output.put_line('Total sales: ' || tot_sales);
end;
VARRAY(动态数组)
If we know the data size of data that we are operate we can use VARRAYs that are lenght fixed, this is Oracle environment so the subscripts start from 1,Alternative is using VARRAY, where array subscript starts from 1 and the length of VARRAYs is fixed.
语法:TYPE VarrayType is VARRAY(size) of ElementType;
* A varray stores an ordered set of elements.
* Each element has an index associated with it.
* A varray has a maximum size that you can change dynamically.
You create a varray type using the SQL DDL CREATE TYPE statement.You specify the maximum size and the type of elements stored in the varray when creating the
The basic Oracle syntax for the CREATE TYPE statement for a VARRAY type definition would be:CREATE OR REPLACE TYPE name-of-type IS VARRAY(nn)of type
Where name-of-type is a valid attribute name, nn is the number of elements (maximum) in the array, and type is the data type of the elements of the array.
You can change the maximum size of a varray using the ALTER TYPE statement.
SQL> CREATE Or Replace TYPE addressVarray AS VARRAY(2) OF VARCHAR2(50);
2 /
Type created.
SQL>
SQL> desc addressVarray;
addressVarray VARRAY(2) OF VARCHAR2(50)
SQL>
Sample 1:
example
declare
type NumberVarray is varray(100) of NUMERIC(10);
myArray NumberVarray;
BEGIN
myArray := NumberVarray(1,10,100,1000,10000);
myArray(1) = 2;
for i in myArray.first..myArray.last
loop
DBMS_OUTPUT.put_line('myArray('||i||') :'||myArray(i));
end loop;
end;
END;
OUTPUT:
myArray(1) : 2
myArray(2) : 10
myArray(3) : 100
myArray(4) : 1000
myArray(5) : 10000
Sample 2:
declare
type array_t is varray(3) of varchar2(10);
array array_t := array_t('Matt', 'Joanne', 'Robert');
begin
for i in 1..array.count loop
dbms_output.put_line(array(i));
end loop;
end;
More VARRAY ARRAY Example Refer:http://www.java2s.com/Tutorial/Oracle/0520__Collections/CreatingaVarrayType.htm
分享到:
相关推荐
PL/SQL有三种集合 联合数组 嵌套表 可变数组 联合数组详解: 什么是数组?数组有什么特点 数据是线性存放的,在内存中地址是连续的 可以用索引来访问 定义联合数组? 联合数组不需要定义长度,他能容纳的元素最大...
Explore new SQL and PL/SQL features in Oracle Database 12c Build control structures, cursors, and loop statements Work with collections, varrays, tables, and associative array collections Locate and ...
§1.3 SQL、SQL*Plus及 PL/SQL 25 §1.3.1 SQL和SQL*PLUS的差别 25 §1.3.2 PL/SQL语言 27 §1.4 登录到SQL*PLUS 27 §1.4.1 UNIX环境 27 §1.4.2 Windows NT和WINDOWS/2000环境 29 §1.5 常用SQL*PLUS 附加命令简介...
§1.3 SQL、SQL*Plus及 PL/SQL 25 §1.3.1 SQL和SQL*PLUS的差别 25 §1.3.2 PL/SQL语言 27 §1.4 登录到SQL*PLUS 27 §1.4.1 UNIX环境 27 §1.4.2 Windows NT和WINDOWS/2000环境 29 §1.5 常用SQL*PLUS 附加命令简介...
适用于 Oracle 的 Go 驱动程序是一个包,它是一个用于连接到 Oracle DB 的 ,使用 Anthony Tuininga 的优秀 OCI 包装器 。... 例如,返回的 PL/SQL 数组的数组大小可以通过godror.ArraySize(2000) (默认
w Use PL/SQL blocks for server logic in your application w Increase batch performance with Array DML or Direct Path Loading w Easily execute SQL scripts similar to SQL*Plus through the TOracleScript ...
而该组件集还支持Array Fetching, Array DML, PL/SQL Blocks, PL/SQL Tables、本地语句缓存和直接路径装载引擎,进一步优化了您程序的性能关键函数。 程序发布方便 ——Direct Oracle Access程序无需BDE或ODBC等...
§1.3 SQL、SQL*Plus及 PL/SQL 25 §1.3.1 SQL和SQL*PLUS的差别 25 §1.3.2 PL/SQL语言 26 §1.4 登录到SQL*PLUS 27 §1.4.1 UNIX环境 27 §1.4.2 Windows NT和WINDOWS/2000环境 29 §1.5 常用SQL*PLUS 附加命令简介...
1、首先用PL/SQL创建package 1 create or replace package excuteBatchOperate 2 as 3 type sqlStr_Array is table of varchar2(1000) index by binary_integer; 4 procedure excuteBulkData(sqlStr in sqlStr...
这两种类型自9i引入,11R1前,程序员只能通过PL/SQL操作。 6.高级队列支持。11R1提供了访问AQ的高性能接口。 7.支持数据库变更通知。 8.Thin和OCI的数据库启动和关闭。11R1提供了这样的方法来启动和关闭...
这两种类型自9i引入,11R1前,程序员只能通过PL/SQL操作。 6.高级队列支持。11R1提供了访问AQ的高性能接口。 7.支持数据库变更通知。 8.Thin和OCI的数据库启动和关闭。11R1提供了这样的方法来启动和关闭...
近期公司用到了Timesten内存数据库,但是Timesten暂不支持json,所以自己写了个包,用户数据库与json的数据转换,没什么高深的,但是要在Timesten里... 下面是pl\sql里写的包,弄了一个星期,测试通过,可以放心使用。
Oracle中PL/SQL单行函数和组函数详解 mssql+oracle Oracle编程的编码规范及命名规则 Oracle数据库字典介绍 0RACLE的字段类型 事务 CMT DEMO(容器管理事务演示) 事务隔离性的一些基础知识 在组件之间实现事务和异步...
8.5.3 使用array_search函数进行查找 129 8.5.4 线性表的入栈与出栈 129 8.5.5 数组的合并 131 8.5.6 数组的拆分 133 8.5.7 随机排序 134 8.6 小结 135 第9章 PHP程序调试 136 9.1 PHP中的错误类型 136 9.1.1 语法...
{12.25}PL/SQL}{189}{section.12.25} {13}JDBC}{191}{chapter.13} {13.1}forName}{191}{section.13.1} {13.2}JDBC}{191}{section.13.2} {13.3}连接Oracle数据库及操作}{192}{section.13.3} {13.4}批处理模式}{...