欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

procedure存储过程 返回数组 博客分类: database oracle 

程序员文章站 2024-03-22 20:00:22
...
文章转自:http://oracle.chinaitlab.com/induction/788759.html


1.建立包



  create or replace package test is
  TYPE filename_array IS TABLE OF varchar2(1);
  filename filename_array;
  end test;


  2. 建立存储过程



  create or replace procedure test_array(v_cfjg out test.filename_array ) is
  begin DECLARE i number;
  D_cfjg dic_cfjg%rowTYPE;
  -- D_nr dic_cfjg%rowTYPE;
  cursor c1 is SELECT * FROM dic_cfjg;
  BEGIN
  i:=0;
  v_cfjg := test.filename_array(); --数组初始化
  open c1;
  LOOP fetch c1 into D_cfjg;
  EXIT WHEN c1%NOTFOUND ;
  i:=i+1;
  v_cfjg.EXTEND;
  -- DBMS_OUTPUT.PUT_LINE(TO_CHAR(D_cfjg.dm));
  v_cfjg(v_cfjg.count):=D_cfjg.dm;
  DBMS_OUTPUT.PUT_LINE(v_cfjg(v_cfjg.count));
  -- 测试
  -- FETCH C1 INTO D_cfjg;
  -- EXIT WHEN c1%NOTFOUND ;
  END LOOP;
  end;
  EXCEPTION
  WHEN TOO_MANY_ROWS THEN
  DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS');
  WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(sqlerrm);
  end test_array;
相关标签: oracle