有一表 有 sign1,sign2....sign20;20个字段,我想写一个存储过程,出入ID,传出一个数字报错这20个字段,我自己写的如下:
create or replace procedure getSign(id in TEST.USERPHONE%TYPE, signList out array) is
flag number;begin
flag:=1;
for i in 1..10 loop
select sign||flag into signList(flag) from TEST where userphone=id; flag:=flag+1;
end loop
end;
不过有错误,哪位大侠帮帮忙
create or replace procedure getSign(id in TEST.USERPHONE%TYPE, signList out array) is
flag number;begin
flag:=1;
for i in 1..10 loop
select sign||flag into signList(flag) from TEST where userphone=id; flag:=flag+1;
end loop
end;
不过有错误,哪位大侠帮帮忙
create or replace procedure getSign(id in TEST.USERPHONE%TYPE, signList out array)
is
begin
for i in 1..10 loop
execute immediate 'select sign'||to_char(i)||' from TEST where userphone='||to_char(id)
into signList(flag);
end loop
end getSign;
返回的那个数组好像不是这么定义的,真要用应该在外面定义一个用户类型,然后这里引用,如:
type tp_a is table of binary_integer;
create or replace procedure getSign(id in TEST.USERPHONE%TYPE, signList out tp_a) is
flag number;
signName varchar2(15);
begin
signList:=new tp_a();
for i in 1..10 loop
execute immediate 'select sign'||to_char(i)||' from TEST where userphone='||to_char(id) into tp_a(i)
end loopend getSign;
报这个错误
Compilation errors for PROCEDURE Error: PLS-00103: Encountered the symbol "END" when expecting one of the following: . ( , ; return returning using Line: 9 Text: end getSign;