如何在具有数组参数的exec Oracle过程中传递参数

Question

我是Java背景，是oracle SQL的新手1.我正在创建自定义类型，不确定是否有其他方法？2.创建过程UPSERT3.在SQL Developer中使用exec命令执行时抛出错误

自定义类型：

create or replace type temperature_ARRAY as table of number;                
create or replace type pressure_ARRAY as table of number;

程序：

create or replace PROCEDURE flange_derating_upsert(i_flangecode IN VARCHAR2,i_product IN VARCHAR2,i_product_inch IN VARCHAR2,i_product_meterstyle IN VARCHAR2,i_pressure in pressure_ARRAY,i_temperature in temperature_ARRAY)
AS
begin
for i in i_temperature.FIRST..i_temperature.LAST loop
update xxcz_flange_ratings_mmi
set pressure = i_pressure(i),
LAST_UPDATED_by = 107009
where PRODUCT_NAME like '%'||i_product_inch||'%'||i_product_meterstyle||'%' and PC_MODEL_CODE = i_flangecode and temperature = i_temperature(i);
DBMS_OUTPUT.PUT_LINE(sql%rowcount||' '||i_temperature(i));
if sql%rowcount = 0 then
-- no rows were updated, so the record does not exist
insert into xxcz_flange_ratings_mmi (PRODUCT_NAME,PC_MODEL_CODE,TEMPERATURE,PRESSURE,LAST_UPDATED_BY) values(i_product,i_flangecode,i_temperature(i),i_pressure(i),107009);
end if;
end loop;
end flange_derating_upsert;

执行时：exec flange_derating_upsert('A1','2 inch Quad High Pressure','2','Quad High Pressure',(275,235,215,195,170,140,125,110,95,80,65),(100,200,300,400,500,600,650,700,750,800,850));

获取以下错误：

Error starting at line : 32 in command -
BEGIN flange_derating_upsert('A1','2 inch Quad High Pressure','2','Quad High Pressure',(275,235,215,195,170,140,125,110,95,80,65),(100,200,300,400,500,600,650,700,750,800,850)); END;
Error report -
ORA-06550: line 1, column 1386:
PLS-00306: wrong number or types of arguments in call to 'FLANGE_DERATING_UPSERT'
ORA-06550: line 1, column 1386:
PLS-00306: wrong number or types of arguments in call to 'FLANGE_DERATING_UPSERT'
ORA-06550: line 1, column 1386:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

Answer 1

在将数组传递到过程时缺少类型引用

试试下面，

BEGIN
flange_derating_upsert('A1'
,'2 inch Quad High Pressure'
,'2'
,'Quad High Pressure'
,pressure_array(275, 235, 215, 195, 170, 140, 125, 110, 95, 80, 65)
,temperature_array(100, 200, 300, 400, 500, 600, 650, 700, 750, 800, 850));
END;
/

或

exec flange_derating_upsert('A1','2 inch Quad High Pressure','2','Quad High Pressure',pressure_array(275,235,215,195,170,140,125,110,95,80,65),temperature_array(100,200,300,400,500,600,650,700,750,800,850));