这样的存储过程怎么写? 在PL/SQL内部可以用TABLE类型或者VARRAY类型传递数组类型变量 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 先定义个数组type arr IS VARRAY OF VARCHAR2(255);t_description arr; 数组定义:type arr Is VArray(2) Of VarChar2(8); SQL> create or replace type myobjecttype as object (id number,description varchar2(50)); 2 /Type created.Elapsed: 00:00:00.51SQL> create or replace type mytabletype as table of myobjecttype 2 /Type created.Elapsed: 00:00:01.62SQL> commit;Commit complete.Elapsed: 00:00:00.20SQL> create or replace procedure sp_test (insertvalue mytabletype) 2 is 3 i number :=0; 4 BEGIN 5 6 loop 7 i :=i+1; 8 INSERT INTO bb (id, name) VALUES (insertvalue(i).id, insertvalue(i).description); 9 exit when i=insertvalue.count; 10 end loop; 11 COMMIT; 12 END sp_test; 13 /Procedure created.Elapsed: 00:00:00.51SQL> commit;Commit complete.Elapsed: 00:00:00.20SQL> select * from bb;no rows selectedElapsed: 00:00:00.20SQL> set serveroutput onSQL> declare 2 v_tabletype mytabletype :=mytabletype(); 3 i number :=0; 4 iname varchar2(50) :='asd'; 5 begin 6 for i in 1..10 loop 7 v_tabletype.extend; 8 v_tabletype(v_tabletype.count) :=myobjecttype(i,iname); 9 end loop; 10 sp_test(v_tabletype); 11 i:=0; 12 select count(*) into i from bb ; 13 dbms_output.put_line('table bb count is ' || i); 14 end; 15 /table bb count is 10PL/SQL procedure successfully completed.Elapsed: 00:00:00.30SQL> column name format a10SQL> select * from bb; ID NAME---------- ---------- 2 asd 3 asd 4 asd 5 asd 6 asd 7 asd 8 asd 9 asd 10 asd 1 asd10 rows selected.Elapsed: 00:00:00.61 我必须在vc ado调用存储过程的。必须考虑把参数传递进去的。而不是直接调用pl/sql。 DB2 向ORACLE 迁移 一个SQL语句问题 怎么审计oracle的执行计划啊 oracle 登陆主机失败 关于impdp/expdp oracle在unix下内存问题 数据转换? 请问,OPEN_CURSORS一般设为多少比较合理?或者有什么标准来设这个数字? sqlldr导入数据库,为何向clob字段导入数据时会出现“数据文件的字段超出最大长度”的错误? 请教PL/SQL DEVELOPER使用方面的一个问题 创建数据库问题 类型转换的错误是不是不允许存储过程继续往下执行? 高分请求解决9i下监听器自动停止!!!!
type arr IS VARRAY OF VARCHAR2(255);t_description arr;
type arr Is VArray(2) Of VarChar2(8);
r2(50));
2 /Type created.Elapsed: 00:00:00.51
SQL> create or replace type mytabletype as table of myobjecttype
2 /Type created.Elapsed: 00:00:01.62
SQL> commit;Commit complete.Elapsed: 00:00:00.20SQL> create or replace procedure sp_test (insertvalue mytabletype)
2 is
3 i number :=0;
4 BEGIN
5
6 loop
7 i :=i+1;
8 INSERT INTO bb (id, name) VALUES (insertvalue(i).id, insertvalue(i).des
cription);
9 exit when i=insertvalue.count;
10 end loop;
11 COMMIT;
12 END sp_test;
13 /Procedure created.Elapsed: 00:00:00.51
SQL> commit;Commit complete.Elapsed: 00:00:00.20SQL> select * from bb;no rows selectedElapsed: 00:00:00.20SQL> set serveroutput on
SQL> declare
2 v_tabletype mytabletype :=mytabletype();
3 i number :=0;
4 iname varchar2(50) :='asd';
5 begin
6 for i in 1..10 loop
7 v_tabletype.extend;
8 v_tabletype(v_tabletype.count) :=myobjecttype(i,iname);
9 end loop;
10 sp_test(v_tabletype);
11 i:=0;
12 select count(*) into i from bb ;
13 dbms_output.put_line('table bb count is ' || i);
14 end;
15 /
table bb count is 10PL/SQL procedure successfully completed.Elapsed: 00:00:00.30SQL> column name format a10
SQL> select * from bb; ID NAME
---------- ----------
2 asd
3 asd
4 asd
5 asd
6 asd
7 asd
8 asd
9 asd
10 asd
1 asd10 rows selected.Elapsed: 00:00:00.61
必须考虑把参数传递进去的。
而不是直接调用pl/sql。