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;
解决方案 »
- 请问学习Oracle有什么不错的书,推荐一下。
- 关于启动 DBconsole 的问题,用start命令,已经显示启动成功后,马上用status查询状态却显示not running
- 如何在linux下远程反问oracle
- 关于 oracle 分页问题
- 不存在列问题,请来点破一下,死活没看出来
- What statement would you issue on the preceding EMPLOYEE_TABLE to allow the MIDDLE_NAME column to accept NULL values?
- oracle9的安装问题,再次请高手指教
- 一个有关oracle分页sql的问题,在线等候。
- 难道就没有高手了吗?版主大哥去哪了啊!小弟在次跪求各位帮帮忙!!最后30救命分
- PL/SQL脚本程序问题
- 一个sql语句,折磨了我半天.
- 【急求】oracle 11g database安装后没有OracleMTSRecoveryService
ORA-06550: 第 1 行, 第 7 列: