DB2 数组

CREATE TYPE IDList AS INTEGER ARRAY[1000];
CREATE TYPE Projects AS VARCHAR(100) ARRAY[50];
CREATE TYPE Resumes AS XML ARRAY[];

评论

7 楼 luogen33 2012-05-05  

data_studio.zip

6 楼 luogen33 2012-05-03  

http://lindows.iteye.com/blog/216559

5 楼 luogen33 2012-05-03  

#!/bin/sh
# published time : 2004/07/23 18:00

# Define the username and password used for logging in to the Oracle.
ACCOUNT=mmsc
PASSWORD=mmsc
SID=oracle9

# System path
APPHOME=`dirname $0`
if [ $APPHOME = "." ]; then
   APPHOME=`pwd`
fi
cd $APPHOME

# Execute the import operation
cat createdb.sql|sqlplus $ACCOUNT/$PASSWORD@$SID

4 楼 luogen33 2012-05-02  

#2楼 得分：0回复于：2008-10-28 13:36:33--这是sql server的字符串分解函数
SQL code/*
功能：实现split功能的函数
*/

create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as

begin
declare @i int

set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)

while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))

set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end

if @inputstr <> '\'
insert @temp values(@inputstr)

return
end
go

--调用

declare @s varchar(1000)

set @s='1,2,3,4,5,6,7,8,55'

select * from dbo.fn_split(@s,',')

drop function dbo.fn_split

3 楼 luogen33 2012-05-02  

Example 2

这个例子，我们使用2个数组类型(intArray 和 stringArray)，使用一个包含列(id 和 name)的 persons 表。 存储过程 processPersons 添加3个人到 persons表, 并返回person名字的数组，按id排序，名字中包含字母 'o'。 3个persons 的 id 和 name 作为数组元素(ids 和 names)添加到数组中。这些数组使用UNNEST 函数，将数组数据以包含2列数据的表的表达形式表达，并插入到persons 表中。最后使用ARRAY_AGG函数将数据汇集返回给输出参数。

create type intArray as integer array[100] @

create type stringArray as varchar(10) array[100] @


create table persons (id integer, name varchar(10)) @

insert into persons values(2, 'Tom') @

insert into persons values(4, 'Jill') @

insert into persons values(1, 'Joe') @

insert into persons values(3, 'Mary') @


create procedure processPersons(out witho stringArray)

begin

declare ids intArray;

declare names stringArray;

set ids = ARRAY[5,6,7];

set names = ARRAY['Bob', 'Ann', 'Sue'];

insert into persons(id, name) (select T.i, T.n from UNNEST(ids, names) as T(i, n));

set witho = (select array_agg(name order by id)

from persons

where name like '%o%');

end @

1

2 楼 luogen33 2012-05-02  

Example 1

这个例子包含2个存储过程, sum 和 main. 存储过程 main 建立一个含有6个元素（integer）的数组。传递这个数组给存储过程sum, 计算出所有元素值的和并返回给调用存储过程 main. 存储过程 sum 说明了array subindexing 和CARDINALITY 函数的用法.

create type intArray as integer array[100] @


create procedure sum(in numList intArray, out total integer)

begin

declare i, n integer;

set n = CARDINALITY(numList);

set i = 1;

set total = 0;

while (i < n) do

set total = total + numList[i];

set i = i + 1;

end while;

end @


create procedure main(out total integer)

begin

declare numList intArray;

set numList = ARRAY[1,2,3,4,5,6];

call sum(numList, total);

end @

1 楼 luogen33 2012-05-02  

清单 9. 数组应用典范




//Create a table "bonus_temp" to store employee ID and corresponding bonus.
CREATE TABLE bonus_temp （empno varchar（6）， bonus double）
//Create ARRAY types to store the values for employee ID， bonus and projects.
CREATE TYPE projects AS VARCHAR（20） ARRAY[10]
//Create the ARRAY type "employee"
CREATE TYPE employees AS VARCHAR（6） ARRAY[20]
//Create the ARRAY type "bonus"
CREATE TYPE bonus AS DOUBLE ARRAY[20]

//Create a stored procedure to calculate the bonus
CREATE PROCEDURE bonus_calculate （IN projs projects， IN percentage integer）
BEGIN
DECLARE emp_array employees;
DECLARE bonus_array bonus;

-- Select the IDs and corresponding bonus in corresponding ARRAY type
-- "employees" and "bonus" using aggregate function
-- ARRAY_AGG.
SELECT cast（array_agg（employee.empno） AS employees），
       cast（array_agg（.10*salary） AS bonus） INTO emp_array，bonus_array 
  FROM vempprojact， unnest（projs） AS P（id）， employee
  WHERE P.id=vempprojact.projno AND employee.empno=vempprojact.empno;

-- Use UNNEST function to  the ARRAY elements  ARRAY
-- variables and  the same in "bonus_temp" table.
INSERT INTO bonus_temp
   SELECT T.empno， T.bonus
     FROM unnest（emp_array， bonus_array）
     WITH ORDINALITY AS T（empno，bonus， idx）;
END

--Call the stored procedure to calculate the bonus.Input to this stored procedure is the
--ARRAY of all projects which are applicable for bonus

Call bonus_calculate（ARRAY[""AD3111""， ""IF1000""， ""MA2111""]， 10）

--Select the data  the table "bonus_temp"