存储过程中如何得到返回值,如何得到我的这个msg是no或者ok
比如我就要得到msg是no还是ok
CREATE OR REPLACE procedure CCDBUSER.creatanswer(ywtypeid in varchar)
as
asknum number;
answerNum number;
a number;
b number;
c number;
countsum number;
beginselect count(ID) a into asknum from T_OUT_EXAM_INFO where CAMPAIGNBATCHID=ywtypeid;
select count(id) b into answerNum from T_OUT_EXAMRESULTTYPE where YWID=ywtypeid;if answerNum>0 then
delete from T_OUT_EXAMRESULTTYPE where YWID=ywtypeid;
end if;
a:=0;
b:=0;
c:=0;
countsum:=0;
CASE asknum
WHEN 1 THEN
for a in 1..3 loop
insertsql:='insert into T_OUT_EXAMRESULTTYPE (id,ANSWER0,YWID) values (askid.NEXTVAL,' || a || ','||'''' || ywtypeid || ''''||')';
for countsum in 1..2 loop
execute immediate insertsql; --动态执行插入操作
msg:='ok';
end loop;
end loop;WHEN 2 THEN
for b in 1..3 loop
for c in 1..3 loop
insertsql:='insert into T_OUT_EXAMRESULTTYPE (id,ANSWER0,ANSWER1,YWID) values (askid.NEXTVAL,' || b||','|| c || ','||'''' || ywtypeid || ''''||')';
for countsum in 1..2 loop
execute immediate insertsql; --动态执行插入操作
msg:='ok';
end loop;
end loop;
end loop;
else
msg:='no';
return;
commit;
end case
end
比如我就要得到msg是no还是ok
CREATE OR REPLACE procedure CCDBUSER.creatanswer(ywtypeid in varchar)
as
asknum number;
answerNum number;
a number;
b number;
c number;
countsum number;
beginselect count(ID) a into asknum from T_OUT_EXAM_INFO where CAMPAIGNBATCHID=ywtypeid;
select count(id) b into answerNum from T_OUT_EXAMRESULTTYPE where YWID=ywtypeid;if answerNum>0 then
delete from T_OUT_EXAMRESULTTYPE where YWID=ywtypeid;
end if;
a:=0;
b:=0;
c:=0;
countsum:=0;
CASE asknum
WHEN 1 THEN
for a in 1..3 loop
insertsql:='insert into T_OUT_EXAMRESULTTYPE (id,ANSWER0,YWID) values (askid.NEXTVAL,' || a || ','||'''' || ywtypeid || ''''||')';
for countsum in 1..2 loop
execute immediate insertsql; --动态执行插入操作
msg:='ok';
end loop;
end loop;WHEN 2 THEN
for b in 1..3 loop
for c in 1..3 loop
insertsql:='insert into T_OUT_EXAMRESULTTYPE (id,ANSWER0,ANSWER1,YWID) values (askid.NEXTVAL,' || b||','|| c || ','||'''' || ywtypeid || ''''||')';
for countsum in 1..2 loop
execute immediate insertsql; --动态执行插入操作
msg:='ok';
end loop;
end loop;
end loop;
else
msg:='no';
return;
commit;
end case
end
as
begin
....
p_msg := msg;
end;
(pa in number,pb in number,pc in number,pd out number) --改CREATE OR REPLACE PROCEDURE CCDBUSER.AAAA (pa in number,pb in number,pc in number)
as
a number;
b number;
c number;
d number;
BEGIN
a:=pa;
b:=pb;
c:=pc;
insert into test1111 (a,b,c) values (a,b,c);
pd:=10; 加
commit;
end aaaa;
这样这个返回值得不到,而且程序还出错
ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'AAAA' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
pb in out number
pb 入参
pd 输入值(pa in number,pb in number,pc in number,pd out number)
比如:
CREATE OR REPLACE procedure CCDBUSER.creatanswer(ywtypeid in varchar)
as
asknum in out number;
answerNum in out number;
a in out number;
b in out number;
c in out number;
countsum in out number;
我想得到out值,好判断这个过程有没有执行成功!!谢谢
CREATE OR REPLACE PROCEDURE CCDBUSER.AAAA (pa in number,pb in number,pc in number,pdc in out number)
as
a number;
b number;
c number;
d number;
pd number;
BEGIN
a:=pa;
b:=pb;
c:=pc;
insert into test1111 (a,b,c) values (a,b,c);
pd:=10;
pdc:=pd;
commit;
end aaaa;
execute aaaa(7,8,9)
ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'AAAA' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
OPER@tl> create or replace procedure xxx(in_var number,out_var out varchar2)
2 as
3 begin
4 if in_var>=60 then
5 out_var:='Good!';
6 else
7 out_var:='Bad';
8 end if;
9 end;
10 /Procedure created.OPER@tl> var aaa varchar2(10)
OPER@tl> exec xxx(70,:aaa)PL/SQL procedure successfully completed.OPER@tl> print :aaaAAA
----------
Good!OPER@tl> exec xxx(30,:aaa)PL/SQL procedure successfully completed.OPER@tl> print :aaaAAA
-----
BadOPER@tl>