SQL> create or replace type myvarray_list as varray(10) of varchar2(50); 2 /类型已创建。SQL> create or replace procedure show_list( 2 p_varlist in myvarray_list 3 ) 4 is 5 str varchar2(50); 6 begin 7 for x in 1..p_varlist.count loop 8 dbms_output.put_line('p_varlist('||x||')='||p_varlist(x)); 9 end loop; 10 end; 11 /过程已创建。SQL> exec show_list(myvarray_list('Oracle','DB2','Sql Server','mySql','DBA')); p_varlist(1)=Oracle p_varlist(2)=DB2 p_varlist(3)=Sql Server p_varlist(4)=mySql p_varlist(5)=DBAPL/SQL 过程已成功完成。
Warning: Procedure created with compilation errors
create or replace type myvarray_list as varray(10) of varchar2(50); 这个一定要创建吗? 有啥查询语句来查询数据库中是否有创建这样的数组吗?
可以使用索引表、嵌套表、变长数组 使用索引表最简单,只需要在代码块中声明 下面是一个索引表的例子: SQL> SET SERVEROUTPUT ON; SQL> SQL> DECLARE 2 TYPE atype IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER; 3 a atype; 4 BEGIN 5 FOR i IN 1 .. 10 LOOP 6 a(i) := 'number: ' || to_char(i); 7 END LOOP; 8 9 FOR i IN 1 .. 10 LOOP 10 dbms_output.put_line(a(i)); 11 END LOOP; 12 END; 13 / number: 1 number: 2 number: 3 number: 4 number: 5 number: 6 number: 7 number: 8 number: 9 number: 10PL/SQL 过程已成功完成。
我学到时候使用的简单的例子, declare code varchar2(20); danjia varchar2(20); cursor cur is select c.vend_code,c.licence_q from tb_ls75 c where rownum<20; begin open cur; loop fetch cur into code,danjia; DBMS_OUTPUT.put_line(code); exit when cur%notfound; end loop; close cur; end; 你看看
2 /类型已创建。SQL> create or replace procedure show_list(
2 p_varlist in myvarray_list
3 )
4 is
5 str varchar2(50);
6 begin
7 for x in 1..p_varlist.count loop
8 dbms_output.put_line('p_varlist('||x||')='||p_varlist(x));
9 end loop;
10 end;
11 /过程已创建。SQL> exec show_list(myvarray_list('Oracle','DB2','Sql Server','mySql','DBA'));
p_varlist(1)=Oracle
p_varlist(2)=DB2
p_varlist(3)=Sql Server
p_varlist(4)=mySql
p_varlist(5)=DBAPL/SQL 过程已成功完成。
这个一定要创建吗? 有啥查询语句来查询数据库中是否有创建这样的数组吗?
使用索引表最简单,只需要在代码块中声明
下面是一个索引表的例子:
SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
2 TYPE atype IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
3 a atype;
4 BEGIN
5 FOR i IN 1 .. 10 LOOP
6 a(i) := 'number: ' || to_char(i);
7 END LOOP;
8
9 FOR i IN 1 .. 10 LOOP
10 dbms_output.put_line(a(i));
11 END LOOP;
12 END;
13 /
number: 1
number: 2
number: 3
number: 4
number: 5
number: 6
number: 7
number: 8
number: 9
number: 10PL/SQL 过程已成功完成。
内嵌表?还是....
declare
code varchar2(20);
danjia varchar2(20);
cursor cur is select c.vend_code,c.licence_q from tb_ls75 c where rownum<20;
begin
open cur;
loop
fetch cur into code,danjia;
DBMS_OUTPUT.put_line(code);
exit when cur%notfound;
end loop;
close cur;
end;
你看看