如果是表里数据,没有必要传参数,直接使用 insert into ...
select ... 语法结构:如
'insert into BILLING_V10.IMP_SMS@msg(...)
select DEVICE_NUMBER||''','''||v_sms_content||'''from FACT_DAILY_MSG_USER
where ....)';
select ... 语法结构:如
'insert into BILLING_V10.IMP_SMS@msg(...)
select DEVICE_NUMBER||''','''||v_sms_content||'''from FACT_DAILY_MSG_USER
where ....)';
15:41:11 SQL> create type code as varray(3) of int;
15:41:16 2 /类型已创建。已用时间: 00: 00: 00.31
15:41:16 SQL> create or replace procedure tes(a in code)
15:41:16 2 as
15:41:16 3 i int;
15:41:16 4 begin
15:41:16 5 for i in 1..3 loop
15:41:16 6 dbms_output.put_line(a(i));
15:41:16 7 end loop;
15:41:16 8 end;
15:41:16 9 /过程已创建。已用时间: 00: 00: 00.15
15:41:16 SQL>
15:41:16 SQL> declare
15:41:16 2 b code:=code(0,0,0);
15:41:16 3 begin
15:41:16 4 b(1):=1;
15:41:16 5 b(2):=2;
15:41:16 6 b(3):=3;
15:41:16 7 tes(b);
15:41:16 8 end;
15:41:16 9 /
1
2
3PL/SQL 过程已成功完成。已用时间: 00: 00: 00.16
/CREATE TYPE device_values AS TABLE OF varchar2(30);
/PROCEDURE SMS_SEND(device_number device_values,....)
AS
....
BEGIN
.... FORALL i IN 1..device_number.COUNT
..........
INSERT INTO BILLING_V10.IMP_SMS@msg
VALUES (device_number(i),....);
end loop;
end;
/CREATE TYPE device_values AS TABLE OF varchar2(30);
/PROCEDURE SMS_SEND(device_number device_values,....)
AS
....
BEGIN
.... FOR i IN 1..device_number.COUNT
..........
INSERT INTO BILLING_V10.IMP_SMS@msg
VALUES (device_number(i),....);
end loop;
end;