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)));

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9515241/viewspace-1809254/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9515241/viewspace-1809254/