procedure getCardInfo(p_cardno varchar2,
p_cardtype varchar2,
P_debitcardno varchar2,
p_password varchar2,
p_userId varchar2,
out_grInfo out REF_CURSOR,
errorcode out number,
errormsg out varchar2) is
num number;
begin
if p_cardno is null then
errorcode := 1;
errormsg := '卡号不能为空!';
return;
--RAISE_APPLICATION_ERROR(-20000, '卡号不能为空!');
end if;
if p_userId is null then
errorcode:=1;
errormsg:='123';
return;
--RAISE_APPLICATION_ERROR(-20003, '123');
end if;
select count(*)into num
from mzcard.tpersoninfo t
inner join mzcard.tcardinfo t1
on t.fmzno = t1.fmzno
and t1.fcardno = p_cardno;
if num > 0 then
open out_grInfo for
select t.fmzno patientid,t.fname name,
t.fsex sex,to_char(t.fbirthday,'yyyy-MM-dd')birthday,decode(t1.fcardstate,0,0,1,2,3,3)cardstatus
from mzcard.tpersoninfo t
inner join mzcard.tcardinfo t1
on t.fmzno = t1.fmzno
and t1.fcardno = p_cardno;
errorcode := 0;
errormsg := ' ';
/*if out_grInfo%isopen = true then
close out_grInfo;
end if;*/
end if;
EXCEPTION
WHEN OTHERS THEN
errorcode := 1;
errormsg := '数据库操作异常';
ROLLBACK;
--关闭游标
if out_grInfo%isopen = true then
close out_grInfo;
end if;
end;
p_cardtype varchar2,
P_debitcardno varchar2,
p_password varchar2,
p_userId varchar2,
out_grInfo out REF_CURSOR,
errorcode out number,
errormsg out varchar2) is
num number;
begin
if p_cardno is null then
errorcode := 1;
errormsg := '卡号不能为空!';
return;
--RAISE_APPLICATION_ERROR(-20000, '卡号不能为空!');
end if;
if p_userId is null then
errorcode:=1;
errormsg:='123';
return;
--RAISE_APPLICATION_ERROR(-20003, '123');
end if;
select count(*)into num
from mzcard.tpersoninfo t
inner join mzcard.tcardinfo t1
on t.fmzno = t1.fmzno
and t1.fcardno = p_cardno;
if num > 0 then
open out_grInfo for
select t.fmzno patientid,t.fname name,
t.fsex sex,to_char(t.fbirthday,'yyyy-MM-dd')birthday,decode(t1.fcardstate,0,0,1,2,3,3)cardstatus
from mzcard.tpersoninfo t
inner join mzcard.tcardinfo t1
on t.fmzno = t1.fmzno
and t1.fcardno = p_cardno;
errorcode := 0;
errormsg := ' ';
/*if out_grInfo%isopen = true then
close out_grInfo;
end if;*/
end if;
EXCEPTION
WHEN OTHERS THEN
errorcode := 1;
errormsg := '数据库操作异常';
ROLLBACK;
--关闭游标
if out_grInfo%isopen = true then
close out_grInfo;
end if;
end;
ORA-06550: 第 1 行, 第 7 列: