varry oracle,RECORD、VARRY、TABLE示例

问题：varry和table类型不能对其直接查询，只能对其遍历

一、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]);

二、VARRY(数组)

DECLARE

TYPE TYPE_VARRY IS VARRAY(50) OF VARCHAR2(25); ----申明VARRY类型

V_VARRAY TYPE_VARRY;

BEGIN

V_VARRAY := TYPE_VARRY('1','2','3','4','5');   ----初始化

dbms_output.put_line('count值:'||V_VARRAY.COUNT());

V_VARRAY.extend(1);    ----扩展数组，在初始化数组基础上扩展多少数量

dbms_output.put_line('count值2:'||V_VARRAY.count());

V_VARRAY(6) := '6';    ----对数组的值赋值，未赋值前为空

DBMS_OUTPUT.PUT_LINE('输出6：' || V_VARRAY(6));

DBMS_OUTPUT.PUT_LINE('------------------------------------------------');

FOR I IN 1..V_VARRAY.COUNT() LOOP     ---遍历数组中的值

DBMS_OUTPUT.PUT_LINE('V_VARRAY('||I||')=' || V_VARRAY(I));

end loop;

DBMS_OUTPUT.PUT_LINE('------------------------------------------------');

FOR I IN V_VARRAY.FIRST()..V_VARRAY.LAST() LOOP  ---遍历数组中的值

DBMS_OUTPUT.PUT_LINE('V_VARRAY('||I||')=' || V_VARRAY(I));

end loop;

DBMS_OUTPUT.PUT_LINE('------------------------------------------------');

V_VARRAY.TRIM(2);  -----对数组尾部缩减2

dbms_output.put_line('缩减后count值:'||V_VARRAY.COUNT());

--dbms_output.put_line('缩减后下标5值:'||V_VARRAY(5));    ---超过下表即会报错

V_VARRAY.DELETE(); ----清空数组

dbms_output.put_line('清空后count值:'||V_VARRAY.COUNT());

END;

三、TABLE类型

TYPE TABLE NAME IS TABLE OF ELEMENT_TYPE

[NOT NULL]

INDEX BY

[BINARY_INTEGER|PLS_INTEGER|VARRAY2];

create table T1 as select empno,ename,job,sal from emp;

通过源表定义表类型

declare

type emp_type is table of T1%rowtype;    ----通过表T3的rowtype来定义

ty2 emp_type;

begin

select empno,ename,job,sal BULK COLLECT INTO  ty2 from T1 where rownum <=5;

-----BULK COLLECT

INTO，可以把查询到的结果一次性加载

for i in ty2.first()..ty2.last()

loop

dbms_output.put_line(ty2(i).empno||'---'||ty2(i).ename);

end loop;

end;

通过记录申明表类型

DECLARE

TYPE type_record_t3 IS RECORD

(

C1 t3.empno%TYPE,

C2 t3.ename%TYPE

);

TYPE type_table_t3 IS TABLE OF type_record_t3;

v_table_1

type_table_t3;

BEGIN

SELECT empno,ename BULK COLLECT INTO v_table_1

FROM t3

WHERE rownum <= 3;

FOR V_INDEX IN v_table_1.FIRST .. v_table_1.LAST LOOP

DBMS_OUTPUT.PUT_LINE(v_table_1(V_INDEX).C1 || ' ' || v_table_1(V_INDEX).C2);

END LOOP;

END;

表函数

CREATE OR REPLACE TYPE "T_VC" is table of varchar2(100); ---表类型

/

create or replace function f_test(i_str varchar2)return t_vc  ---返回表类型

as

v_str_table

t_vc;

begin

v_str_table := t_vc();

for i in 1..length(i_str) loop

v_str_table.extend;

v_str_table(i):=substr(i_str,i,1);

end loop;

return v_str_table;

end;

/

SQL> select * from table(f_test('aw298fdf'));

管道表函数

示例一

CREATE OR REPLACE PACKAGE pkg1 AS

-- Purpose :对表函数的应用实例

TYPE ty_rec_user IS record (--定义一个record类型的TYPE

id number(20),

name varchar2(60)

);

TYPE out_rec_set is table of ty_rec_user;--定义一个嵌套表集合类型out_rec_set,作为表函数的返回类型

--定义返回集合类型的管道表函数

FUNCTION f1(x NUMBER) RETURN out_rec_set PIPELINED;

END pkg1;

CREATE OR REPLACE PACKAGE BODY pkg1 AS

-- Purpose :对表函数的应用实例

FUNCTION f1(x NUMBER) RETURN out_rec_set PIPELINED IS

user_rec ty_rec_user;

BEGIN

FOR i IN 1..x LOOP

--user_rec:=ty_rec_user(i,'user'||i);--ty_rec_user定义为record类型时不能这样赋值，只有定义成obj时才可以

user_rec.id:=i;

user_rec.name:='user'||i;

--PIPE ROW(1, 'user'||1);

pipe row(user_rec);

END LOOP;

RETURN;

END;

END pkg1;

--外部自定义的object类型

create or replace type TY_OBJ_USER as object

(

-- Purpose :测试

id number(20),

name varchar2(60)

);

select * from table(pkg1.f1(4))--直接在plsql中执行

示例二

CREATE OR REPLACE PACKAGE refcur_pkg IS

TYPE refcur_t IS REF CURSOR RETURN emp%ROWTYPE;

TYPE outrec_typ IS RECORD (

var_num

NUMBER(6),

var_char1

VARCHAR2(30),

var_char2

VARCHAR2(30));

TYPE outrecset IS TABLE OF outrec_typ;

FUNCTION f_trans(p refcur_t)

RETURN outrecset PIPELINED;

END refcur_pkg;

CREATE OR REPLACE PACKAGE BODY refcur_pkg IS

FUNCTION f_trans(p refcur_t)

RETURN outrecset PIPELINED IS

out_rec outrec_typ;

in_rec

p%ROWTYPE;

BEGIN

LOOP

FETCH p INTO in_rec;

EXIT WHEN p%NOTFOUND;

-- first row

out_rec.var_num := in_rec.empno;

out_rec.var_char1 := in_rec.ename;

out_rec.var_char2 := in_rec.mgr;

PIPE ROW(out_rec);

-- second row

out_rec.var_num := in_rec.deptno;

out_rec.var_char1 := in_rec.deptno;

out_rec.var_char2 := in_rec.job;

PIPE ROW(out_rec);

END LOOP;

CLOSE p;

RETURN;

END;

END refcur_pkg;

SELECT * FROM TABLE(

refcur_pkg.f_trans(CURSOR(SELECT * FROM emp WHERE empno=7782)));