Oracle 错误: ORA-06533: 下标超出计数

askTom 2018-04-07

1020

问题描述

请帮我编辑下面的Pl/SQL代码:

create or replace
Function user_score_scurve (scurve_in varchar2, inpt IN number)
   RETURN number
IS
   params_number number;
   TYPE type_params IS table OF number;         
   Params type_params;
   output number;
   output1 number;
   output2 number;
   rand number;
BEGIN
   --Set the random number DBMS_RANDOM.VALUE (0, 1)
   rand := 0; 
   -- Bulk collect into collection 
   select value bulk collect into params
     from user_score_scurve_params
    where 1=1
      and scurve = scurve_in;
   -- Calculating the formula: E6+(F6-E6)*1/(1+EXP((G6-(C24-C6)/(D6-C6))/(H6+RAND()/10^6)))
   output1 := params(7) - (inpt - params(2))/(params(3) - params(2)); 
   output2 := params(6) + rand/(power(10,6)); 
   output  := params(4) + (params(5) - params(4))/(1 + exp(output1/output2)); 

return output;
END;复制

专家解答

您正在进行的计算是引用参数1到7中的值。因此，如果你不批量收集7个或更多的值到你的阵列，你会得到这个错误。例如，此查询将仅收集1行... 因此引用2或更高版本是一个问题

SQL> declare
  2    TYPE type_params IS table OF number;
  3    p type_params;
  4  begin
  5    select rownum bulk collect into p
  6    from dual;
  7    p(1) := p(1)+10;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> declare
  2    TYPE type_params IS table OF number;
  3    p type_params;
  4  begin
  5    select rownum bulk collect into p
  6    from dual;
  7    p(2) := p(2) + 10;
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 7
复制