create or replace function ensbill_check(v_ensbill_id number)
return varchar2 is
ensbill_su varchar2(30);
begin
v_ensbill_id:=0
v_ensbill_su:=""
return v_ensbill_su;
end;8/1 PLS-00103: 出现符号 "V_ENSBILL_SU"在需要下列之一时: * & = - + ; < / > at in is mod not rem <an exponent (**)> <> or != or ~= >= <= <> and or like between ||
return varchar2 is
ensbill_su varchar2(30);
begin
v_ensbill_id:=0
v_ensbill_su:=""
return v_ensbill_su;
end;8/1 PLS-00103: 出现符号 "V_ENSBILL_SU"在需要下列之一时: * & = - + ; < / > at in is mod not rem <an exponent (**)> <> or != or ~= >= <= <> and or like between ||
return varchar2 is
ensbill_su varchar2(30);
begin
v_ensbill_id:=0
v_ensbill_su:=""
return v_ensbill_su;
end;
create or replace function ensbill_check(v_ensbill_id out number) --默认in参数不能赋值
return varchar2 is
v_ensbill_su varchar2(30);--变量声明与下面名称不一致
begin
v_ensbill_id:=0;--语句结束没有';'号
--v_ensbill_su:="";--字符串应用单引号括起,并且变量没有声明
v_ensbill_su:='';
return v_ensbill_su;
end;
SQL> create or replace function ensbill_check(v_ensbill_id in number)
2 return varchar2
3 as
4 ensbill_su varchar2(100);
5 begin
6 ensbill_su:='您好,请您书写规范的oracle函数,记得声明变量,每个语句后面有分号';
7 return ensbill_su;
8 end ensbill_check;
9 /
Function created
SQL> select ensbill_check(101) from dual;
ENSBILL_CHECK(101)
--------------------------------------------------------------------------------
您好,请您书写规范的oracle函数,记得声明变量,每个语句后面有分号
as、is什么时候用,比较晕
最后的end要不要加ensbill_check?我看有的书上说要加有的说不需要
定义变量的时候前面要不要加declare?
这个 可以加 也可以不加
这里 你已经有as 了 不用再 declare最后的end要不要加ensbill_check?我看有的书上说要加有的说不需要 如果是定义在 package里面的话,好像是需要的。
return varchar2 is
ensbill_su varchar2(30);
begin
v_ensbill_id:=0-----------此处少了一个“;”
v_ensbill_su:=""
return v_ensbill_su;
end;
2 return varchar is
3 enbill_sg varchar1(50);
4 enbill_check enteistore_head.ens_test_id%type;
5 begin
6 select ens_test_id into enbill_check from enteistore_head where end_id=ensbill_id;
7 if enbill_check='0' then
8
9 enbill_sg:='该入库单未验收或未存盘,入库失败!';
10 end if;
11 rerurn enbill_sg;
12 end;
13 /Warning: Function created with compilation errorsSQL> show error
Errors for FUNCTION SMARTCOMPANY.ENSBILL_CHECK:LINE/COL ERROR
-------- -----------------------------------------------------------------------------------------------------------
11/8 PLS-00103: 出现符号 "ENBILL_SG"在需要下列之一时: := . ( @ % ; 符号 ":=" 被替换为 "ENBILL_SG" 后继续。
2 return varchar2
3 is
4 enbill_sg varchar2(50);
5 enbill_checked enteistore_head.ens_test_id%type;
6 begin
7 select ens_test_id into enbill_checked from enteistore_head where ens_id=ensbill_id;
8 if enbill_checked='0' then
9
10 enbill_sg:='该入库单未验收或未存盘,入库失败!';
11 end if;
12 return enbill_sg;
13 end ensbill_check;
14 /Function created
oracle 自定义函数简介
CREATE OR REPLACE FUNCTION ENSBILL_CHECK(V_ENSBILL_ID NUMBER)
RETURN VARCHAR2 IS
ENSBILL_SU VARCHAR2(30);
ENSBILL_ID NUMBER;
BEGIN
IF V_ENSBILL_ID IS NULL THEN
ENSBILL_ID := 0;
END IF;
ENSBILL_SU := '';
RETURN ENSBILL_SU;
END;
return varchar2
isenbill_sg varchar2(50);
enbill_checked enteistore_head.ens_test_id%type;number_ensd number(10,0);
type bill_cursor is ref cursor;
enbill_cursor bill_cursor;
v_test_qty number(10,0)/*enterstore_detail.test_qty%type*/;
v_nmber_sn number(10,0);
v_item_snm char(1);
v_item_id item_basic.item_id%type;
v_ensd_id enterstore_detail.ensd_id%type;
begin
enbill_sg:='ok';
select ens_test_id into enbill_checked from enteistore_head where ens_id=ensbill_id;
if enbill_checked='0' then enbill_sg:='该入库单未验收或未存盘,入库失败!';
else
select count(*) into number_ensd from enterstore_detail where ens_id=ensbill_id and test_sign='1'; if number_ensd<1 then
enbill_sg:='没有任何物料验收入库,可能相关验收物料没有存盘!';
else
open enbill_cursor for select test_qty,item_id,ensd_id from enterstore_detail where ens_id=ensbill_id and test_sign='1';
loop
fetch enbill_cursor into v_test_qty,v_item_id,v_ensd_id;
select item_serialed into v_item_snm from item_basic where item_id=v_item_id;
if v_item_snm='1' then
select count(*) into v_nmber_sn from sn_manage where (testok_id='1' and sn_bill_id=ensbill_id and sn_op_id=v_ensd_id);
if v_test_qty<>v_nmber_sn then
enbill_sg:='入库失败,相关产品序列号登记数量与入库数量不符,请检查!';
end if;
end if;
exit when enbill_cursor%notfound;
end loop;
end if;
end if;
return enbill_sg;
end;
第三种情况怎么也得不到不知道为啥