create or replace package pk_bank as
type bankref is ref cursor; //包头已建成
type cardrecord is record(
cardid cardinfo.cardid%type,
username userinfo.username%type,
savetype cardinfo.savetype%type,
openmoney cardinfo.openmoney%type,
statename cardstate.statename%type,
moneytype cardinfo.moneytype%type,
accountmoney cardinfo.accountmoney%type
);
procedure searchCard(
v_cardid in varchar2,
v_district in varchar,
v_statename in char,
v_username in varchar2,
v_opendate in varchar2,
v_cardref out bankref
);
end pk_bank;create or replace package body pk_bank as
procedure searchCard(
v_cardid in varchar2, //包体编辑无错误提示,但是包体内没有东东
v_district in varchar,
v_statename in char,
v_username in varchar2,
v_opendate in varchar2,
v_cardref out bankref
) is
v_sql varchar2(1000);
begin
v_sql:='select c.cardid,u.username,decode(c.savetype,0,'活期',1,'定期'),c.openmoney,s.statename,
decode(c.moneytype,0,'人民币',1,'美金'),c.accountmoney from cardinfo c,userinfo u,cardstate s where c.userid=u.userid and c.cardstate=s.stateid';
if v_cardid is not null then
v_sql:=v_sql+' and c.cardid='||v_cardid;
end if
if v_distrit is not null then
v_sql:=v_sql+' and c.district='''||v_district||'';
end if
if v_statename is not null then
v_sql:=v_sql+' and s.statename='''||v_statename|'';
end if
if v_username is not null then
v_sql:=v_sql+' and u.username like''%'||v_username||'%''';
end if
if v_opendate is not null then
v_sql:=v_sql+' and to_char(c.opendate,yyyymm)='||v_opendate;
end if
open v_cardref for v_sql;
end searchCard;
end pk_bank;
declare
vmyref pk_bank.bankref;
myrecord pk_bank.cardrecord;
begin
pk_bank.searchCard(v_cardid=>'',v_district=>'',v_statename=>'',v_username =>'',v_opendate =>'',v_cardref=>vmyref);
loop
fetch vmyref into myrecord;
exit when vmyref%notfound;
dbms_output.put_line(myrecord.username);
end loop;
end;
type bankref is ref cursor; //包头已建成
type cardrecord is record(
cardid cardinfo.cardid%type,
username userinfo.username%type,
savetype cardinfo.savetype%type,
openmoney cardinfo.openmoney%type,
statename cardstate.statename%type,
moneytype cardinfo.moneytype%type,
accountmoney cardinfo.accountmoney%type
);
procedure searchCard(
v_cardid in varchar2,
v_district in varchar,
v_statename in char,
v_username in varchar2,
v_opendate in varchar2,
v_cardref out bankref
);
end pk_bank;create or replace package body pk_bank as
procedure searchCard(
v_cardid in varchar2, //包体编辑无错误提示,但是包体内没有东东
v_district in varchar,
v_statename in char,
v_username in varchar2,
v_opendate in varchar2,
v_cardref out bankref
) is
v_sql varchar2(1000);
begin
v_sql:='select c.cardid,u.username,decode(c.savetype,0,'活期',1,'定期'),c.openmoney,s.statename,
decode(c.moneytype,0,'人民币',1,'美金'),c.accountmoney from cardinfo c,userinfo u,cardstate s where c.userid=u.userid and c.cardstate=s.stateid';
if v_cardid is not null then
v_sql:=v_sql+' and c.cardid='||v_cardid;
end if
if v_distrit is not null then
v_sql:=v_sql+' and c.district='''||v_district||'';
end if
if v_statename is not null then
v_sql:=v_sql+' and s.statename='''||v_statename|'';
end if
if v_username is not null then
v_sql:=v_sql+' and u.username like''%'||v_username||'%''';
end if
if v_opendate is not null then
v_sql:=v_sql+' and to_char(c.opendate,yyyymm)='||v_opendate;
end if
open v_cardref for v_sql;
end searchCard;
end pk_bank;
declare
vmyref pk_bank.bankref;
myrecord pk_bank.cardrecord;
begin
pk_bank.searchCard(v_cardid=>'',v_district=>'',v_statename=>'',v_username =>'',v_opendate =>'',v_cardref=>vmyref);
loop
fetch vmyref into myrecord;
exit when vmyref%notfound;
dbms_output.put_line(myrecord.username);
end loop;
end;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货