create or replace function get_match_status_flg (
baitaisyubetsuid in varchar2
, repairflg in number
, frameid in number
, soukouflg in number
, kyoriid in number
, soukouflg in number
, soukou in number
, soukounum in number
, kanteiupdate in date
, kanteiflg in number
, stockid in varchar2
, serino in varchar2
, nenshikiflg in number
, clientid in varchar2
)
return number
is
v_cursor number;
rCheck number; status varchar2(1000) := '';
aaa_match_select_sql varchar2(4000);
sqlstmt varchar2(4000); frame_id number;
processing_flg number;
match_wait_flg number := 0;
dist_flg varchar2(1000);
rep_flg varchar2(1000);begin -- 崌抳偡傞嵼屔幵鐀偼懚嵼偟側偄応崌
if stockid is null then
status := '奩摉暔審側偟';
else
aaa_match_select_sql := '';
aaa_match_select_sql := aaa_match_select_sql || ' SELECT PROCESSING_FLG FROM AAA_MATCH_DB WHERE CLIENT_ID = ' || clientid;
aaa_match_select_sql := aaa_match_select_sql || ' AND SERI_NO = ' || serino;
aaa_match_select_sql := aaa_match_select_sql || ' UNION ';
aaa_match_select_sql := aaa_match_select_sql || ' SELECT PROCESSING_FLG FROM AAA_NO_MATCHED_DB WHERE CLIENT_ID = ' || clientid;
aaa_match_select_sql := aaa_match_select_sql || ' AND SERI_NO = ' || serino; sqlstmt := aaa_match_select_sql; --SQL暥惗惉
v_cursor := dbms_sql.open_cursor; --cursor id 庢摼
dbms_sql.parse(v_cursor, sqlstmt, dbms_sql.V7); -- SQL暥夝愅
dbms_sql.define_column(v_cursor, 1, processing_flg);
rCheck := dbms_sql.execute(v_cursor); --SQL幚峴 loop
rCheck := dbms_sql.fetch_rows(v_cursor);
dbms_sql.column_value(v_cursor, 1, processing_flg);
exit when rCheck != 2;
end loop; dbms_sql.close_cursor(v_cursor); -- 僇乕僜儖僋儘乕僘 if processing_flg = 1 or processing_flg = 8 then
match_wait_flg := 2;
elseif processing_flg = 2 then
match_wait_flg := 0;
else
match_wait_flg := 1;
end if; if match_wait_flg = 0 then
if kanteiflg = 1 then
status := '嵪';
end if;
elseif match_wait_flg = 1 then
if (TO_CHAR(kanteiupdate) < TO_CHAR(add_months(sysdate,-6),'yymmdd'))then
status := '婜尷愗傟';
else
status := '張棟懸偪';
end if;
else
-- 憱峴嫍棧傪敾抐偡傞
if (not(nenshikiflg = 5 or (soukouflg = 5 and kyoriid = 2) or ((soukouflg = 1 or soukouflg = 2) and kyoriid != 2 and round(soukou/10000, 1) <= round(soukou_num/10000,1)))) then
dist_flg = true;
end if; --廋暅楌傪敾抐偡傞 if frameid > 2 then
frame_id = 2;
else
frame_id = frameid;
end if; if (frame_id > repairflg) then
rep_flg = true;
end if; if dist_flg and rep_flg then
status := '憱峴嫍棧亊廋暅楌亊';
elseif (dist_flg) then
status := '憱峴嫍棧亊';
elseif (rep_flg) then
status := '廋暅楌亊';
end if; -- 桳岠婜尷(6儠寧)傪敾抐偡傞
if (status is null and TO_CHAR(up_date) < TO_CHAR(add_months(sysdate,-6),'yymmdd')) then
status := '婜尷愗傟';
end if; if status is null then
status := '張棟懸偪';
end if;
end if;
end if;return status;exception
when OTHERS then
if dbms_sql.is_open(v_cursor) then
dbms_sql.close_cursor(v_cursor); -- 僇乕僜儖僋儘乕僘
end if;
return -1; -- 僄儔乕end get_match_status_flg;
/
show error;
/
commit
/
exit
baitaisyubetsuid in varchar2
, repairflg in number
, frameid in number
, soukouflg in number
, kyoriid in number
, soukouflg in number
, soukou in number
, soukounum in number
, kanteiupdate in date
, kanteiflg in number
, stockid in varchar2
, serino in varchar2
, nenshikiflg in number
, clientid in varchar2
)
return number
is
v_cursor number;
rCheck number; status varchar2(1000) := '';
aaa_match_select_sql varchar2(4000);
sqlstmt varchar2(4000); frame_id number;
processing_flg number;
match_wait_flg number := 0;
dist_flg varchar2(1000);
rep_flg varchar2(1000);begin -- 崌抳偡傞嵼屔幵鐀偼懚嵼偟側偄応崌
if stockid is null then
status := '奩摉暔審側偟';
else
aaa_match_select_sql := '';
aaa_match_select_sql := aaa_match_select_sql || ' SELECT PROCESSING_FLG FROM AAA_MATCH_DB WHERE CLIENT_ID = ' || clientid;
aaa_match_select_sql := aaa_match_select_sql || ' AND SERI_NO = ' || serino;
aaa_match_select_sql := aaa_match_select_sql || ' UNION ';
aaa_match_select_sql := aaa_match_select_sql || ' SELECT PROCESSING_FLG FROM AAA_NO_MATCHED_DB WHERE CLIENT_ID = ' || clientid;
aaa_match_select_sql := aaa_match_select_sql || ' AND SERI_NO = ' || serino; sqlstmt := aaa_match_select_sql; --SQL暥惗惉
v_cursor := dbms_sql.open_cursor; --cursor id 庢摼
dbms_sql.parse(v_cursor, sqlstmt, dbms_sql.V7); -- SQL暥夝愅
dbms_sql.define_column(v_cursor, 1, processing_flg);
rCheck := dbms_sql.execute(v_cursor); --SQL幚峴 loop
rCheck := dbms_sql.fetch_rows(v_cursor);
dbms_sql.column_value(v_cursor, 1, processing_flg);
exit when rCheck != 2;
end loop; dbms_sql.close_cursor(v_cursor); -- 僇乕僜儖僋儘乕僘 if processing_flg = 1 or processing_flg = 8 then
match_wait_flg := 2;
elseif processing_flg = 2 then
match_wait_flg := 0;
else
match_wait_flg := 1;
end if; if match_wait_flg = 0 then
if kanteiflg = 1 then
status := '嵪';
end if;
elseif match_wait_flg = 1 then
if (TO_CHAR(kanteiupdate) < TO_CHAR(add_months(sysdate,-6),'yymmdd'))then
status := '婜尷愗傟';
else
status := '張棟懸偪';
end if;
else
-- 憱峴嫍棧傪敾抐偡傞
if (not(nenshikiflg = 5 or (soukouflg = 5 and kyoriid = 2) or ((soukouflg = 1 or soukouflg = 2) and kyoriid != 2 and round(soukou/10000, 1) <= round(soukou_num/10000,1)))) then
dist_flg = true;
end if; --廋暅楌傪敾抐偡傞 if frameid > 2 then
frame_id = 2;
else
frame_id = frameid;
end if; if (frame_id > repairflg) then
rep_flg = true;
end if; if dist_flg and rep_flg then
status := '憱峴嫍棧亊廋暅楌亊';
elseif (dist_flg) then
status := '憱峴嫍棧亊';
elseif (rep_flg) then
status := '廋暅楌亊';
end if; -- 桳岠婜尷(6儠寧)傪敾抐偡傞
if (status is null and TO_CHAR(up_date) < TO_CHAR(add_months(sysdate,-6),'yymmdd')) then
status := '婜尷愗傟';
end if; if status is null then
status := '張棟懸偪';
end if;
end if;
end if;return status;exception
when OTHERS then
if dbms_sql.is_open(v_cursor) then
dbms_sql.close_cursor(v_cursor); -- 僇乕僜儖僋儘乕僘
end if;
return -1; -- 僄儔乕end get_match_status_flg;
/
show error;
/
commit
/
exit
--------------------------------------------------------------------------------
ERROR
--------------------------------------------------------------------------------
60/31
PLS-00103: 記号"="が見つかりました。 次のうちの1つが入るとき:
:= . ( @
% ;
警告: ファンクションが作成されましたが、コンパイル・エラーがあります。
frame_id = 2;
应该是
frame_id := 2
--------------------------------------------------------------------------------
ERROR
--------------------------------------------------------------------------------
61/19
PLS-00103: 記号"PROCESSING_FLG"が見つかりました。 次のうちの1つが入るとき: := . ( @ % ;71/19
PLS-00103: 記号"MATCH_WAIT_FLG"が見つかりました。 次のうちの1つが入るとき: := . ( @ % ;LINE/COL
--------------------------------------------------------------------------------
ERROR
--------------------------------------------------------------------------------97/34
PLS-00103: 記号"THEN"が見つかりました。 次のうちの1つが入るとき:
:= . (
% ;99/33
PLS-00103: 記号"THEN"が見つかりました。 次のうちの1つが入るとき:
:= . (LINE/COL
--------------------------------------------------------------------------------
ERROR
--------------------------------------------------------------------------------
% ;116/1
PLS-00103: 記号"EXCEPTION"が見つかりました。 次のうちの1つが入るとき: begin case declare end exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollbackLINE/COL
--------------------------------------------------------------------------------
ERROR
--------------------------------------------------------------------------------
savepoint set sql execute commit forall merge pipe123/25
PLS-00103: 記号"end-of-file"が見つかりました。 次のうちの1つが入るとき: end not pragma final instantiable order overriding static
member constructor map
警告: ファンクションが作成されましたが、コンパイル・エラーがあります。修改完后还报错,谢谢 shiyiwan
elseif不是这么写的,应该写成elsif
--------------------------------------------------------------------------------
ERROR
--------------------------------------------------------------------------------
0/0
PL/SQL: Compilation unit analysis terminated1/1
PLS-00410: RECORDまたはTABLE、引数リスト内で重複するフィールドは許可されません。
警告: ファンクションが作成されましたが、コンパイル・エラーがあります。改完之后又有问题了,谢谢
这个错是有变量重复定义了
输入参数里面有两个soukouflg,所以报错了
--------------------------------------------------------------------------------
ERROR
--------------------------------------------------------------------------------
78/16
PL/SQL: Statement ignored78/165
PLS-00201: 識別子SOUKOU_NUMを宣言してください。91/20
PL/SQL: Statement ignored
LINE/COL
--------------------------------------------------------------------------------
ERROR
--------------------------------------------------------------------------------
91/31
PLS-00382: 式の型が正しくありません。94/16
PL/SQL: Statement ignored94/19
PLS-00382: 式の型が正しくありません。
LINE/COL
--------------------------------------------------------------------------------
ERROR
--------------------------------------------------------------------------------
103/16
PL/SQL: Statement ignored103/47
PLS-00201: 識別子UP_DATEを宣言してください。
警告: ファンクションが作成されましたが、コンパイル・エラーがあります。改完之后又有问题了,真的非常感谢 shiyiwan
rep_flg
这两个本地参数,声明时候用的是varchar2类型,但是在赋值的时候你使用的是true Boolean类型。比如第80,92行。可能还会有其他错误,如果还有的话把最新修改的代码贴一下吧.
第79行,SOUKOU_NUM这个变量未申明