set serveroutput on create or replace function getgzbh(mbbh in varchar2,yhlx in varchar2,yxfz in varchar2) return varchar2 is Result varchar2(4); t_num number; BEGIN result:='0000'; begin select count(1) into t_num from sys_jsgz where yhlx=yhlx and yxfz=yxfz and mbbh=mbbh; DBMS_OUTPUT.PUT_LINE('rows:'t_num); exception when NO_DATA_FOUND then result:='B201'; end; return(Result); END getgzbh; / 运行一下看看输出为多少。
mbbh in varchar2,yhlx in varchar2,yxfz in varchar2 --> avibbh in varchar2,aviyhlx in varchar2,aviyxfz in varchar2select gzbh into result from sys_jsgz where yhlx=aviyhlx and yxfz=aviyxfz and mbbh=avimbbh
以下此句有问题:select gzbh into result from sys_jsgz where yhlx=yhlx and yxfz=yxfz and mbbh=mbbh; 建义把变量修改成以下形式: select gzbh into result from sys_jsgz where yhlx=lc_yhlx and yxfz=lc_yxfz and mbbh=lc_mbbh;
输入的参数名不应该与表中的字段名重复,否则你的select语句中的yhlx=yhlx and yxfz=yxfz and mbbh=mbbh,Oracle会把等号右边的当作是字段名,也就等于是没有任何条件,表中的所有记录都满足!
create or replace function getgzbh(mbbh in varchar2,yhlx in varchar2,yxfz in varchar2) return varchar2 is
Result varchar2(4);
t_num number;
BEGIN
result:='0000';
begin
select count(1) into t_num from sys_jsgz where yhlx=yhlx and yxfz=yxfz and mbbh=mbbh;
DBMS_OUTPUT.PUT_LINE('rows:'t_num);
exception
when NO_DATA_FOUND then
result:='B201';
end;
return(Result);
END getgzbh;
/
运行一下看看输出为多少。
avibbh in varchar2,aviyhlx in varchar2,aviyxfz in varchar2select gzbh into result
from sys_jsgz where yhlx=aviyhlx and yxfz=aviyxfz and mbbh=avimbbh
建义把变量修改成以下形式:
select gzbh into result from sys_jsgz where yhlx=lc_yhlx and yxfz=lc_yxfz and mbbh=lc_mbbh;