时间戳

首页 > 解决方案 > 如何在具有数组参数的 exec Oracle 过程中传递参数

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

问题描述

我是 Java 背景，并且是 oracle SQL 1 的新手。我正在创建自定义类型，但不确定是否有其他方法？2.创建过程UPSERT 3.在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:

标签： sqloraclestored-proceduresoracle-sqldeveloper

解决方案

将数组传递给过程时缺少类型引用，

试试下面，

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));

推荐阅读