如題
請大家幫忙了!!
謝謝!!!
SqlServer SP代碼如下
請幫忙，謝謝!!!


ALTER PROCEDURE dbo.SP_GETSERNO
(
@vstrCompID VARCHAR(20),
@vstrApID VARCHAR(50),
@vstrSERCLASS_ID VARCHAR(20),
@vstrDEPT_ID VARCHAR(50),
@vstrUSE_DEPT_ID AS VARCHAR(50),
@vstrUSR_ID VARCHAR(50),
@vstrCount INT,
@vstrSERNO_SYS VARCHAR(20),
@vstrISOldDay VARCHAR(1),
@RETURNS NVARCHAR(100) OUTPUT
)
AS
BEGIN TRANSACTION
DECLARE @SER_NO VARCHAR(50) --文號
DECLARE @SER_NO_DESC VARCHAR(50) --顯示的文號
DECLARE @V1 VARCHAR(50) --SERNO_ID字段
DECLARE @V2 VARCHAR(50) --NO_LEN　字段
DECLARE @V3 VARCHAR(50) --IS_FILL_SYM　字段
DECLARE @V4 VARCHAR(50) --IS_SHOW字段
DECLARE @V5 VARCHAR(50) --RESET_KIND字段
DECLARE @V6 VARCHAR(50) --YEAR_TYPE　字段
DECLARE @V7 VARCHAR(50) --FILL_SYM　字段
DECLARE @V8 VARCHAR(50) --SERNO_PART　字段
DECLARE @STR1 VARCHAR(50) --SERNO_ID='1'時獲取的值
DECLARE @STR2 VARCHAR(50) --SERNO_ID='2'時獲取的值
DECLARE @STR3 VARCHAR(50) --SERNO_ID='3'時獲取的值
DECLARE @STR4 VARCHAR(50) --SERNO_ID='4'時獲取的值
DECLARE @STR5 VARCHAR(50) --SERNO_ID='5'時獲取的值
DECLARE @STR6 VARCHAR(50) --SERNO_ID='6'時獲取的值
DECLARE @MONTH VARCHAR(50) --系統的月份
DECLARE @STRSQL VARCHAR(500) --SQL語句字符串
DECLARE @STRSQL_INSERT VARCHAR(500) --SQL語句字符串
DECLARE @SERNO_ID VARCHAR(50)
DECLARE @RESET_KIND VARCHAR(50)
DECLARE @YEAR_CODE VARCHAR(50)
DECLARE @MONTH_CODE VARCHAR(50)
DECLARE @DAY_CODE VARCHAR(50)
DECLARE @STR6_IS_SHOW VARCHAR(50)
DECLARE @STR6_IS_FILL_SYM VARCHAR(50)
DECLARE @STR6_NO_LEN VARCHAR(50)
DECLARE @STR6_COPY VARCHAR(50)
DECLARE @COUNT AS INT --插入次數
SELECT @SER_NO=''
SELECT @SER_NO_DESC=''
SELECT @STRSQL = ''
SELECT @SERNO_ID = ''
SELECT @RESET_KIND = ''
SELECT @COUNT = @vstrCount

DECLARE Employee_Cusor CURSOR FOR
SELECT SERNO_ID,NO_LEN,IS_FILL_SYM,IS_SHOW,RESET_KIND,YEAR_TYPE,FILL_SYM,SERNO_PART
FROM ODMSERNOASMINF
WHERE COMP_ID= @vstrCompID AND AP_ID = @vstrAPID AND SERCLASS_ID=@vstrSERCLASS_ID
ORDER BY SERNO_PART

OPEN Employee_Cusor

FETCH NEXT FROM Employee_Cusor INTO @V1,@V2,@V3,@V4,@V5,@V6,@V7,@V8
SELECT @SERNO_ID =@SERNO_ID + @V1 --SERNO_ID累加
SELECT @RESET_KIND = @RESET_KIND + @V5 --@RESET_KIND累加

WHILE @@FETCH_STATUS=0
BEGIN

SELECT @SERNO_ID =@SERNO_ID + @V1 --SERNO_ID累加
SELECT @RESET_KIND = @RESET_KIND + @V5 --@RESET_KIND累加

--****取得系統年度****
IF @V1='1'
BEGIN
SELECT @STR1=YEAR(GETDATE())
IF @V6='1'
SELECT @STR1=YEAR(GETDATE())-1911
IF @vstrISOldDay='Y'
SELECT @STR1=CONVERT(INT,@STR1)-1

IF @V3='Y'
SELECT @STR1=(SELECT RIGHT('000'+CONVERT(VARCHAR,@STR1),@V2))
IF @V4='N'
SELECT @SER_NO=@SER_NO+@STR1
SELECT @SER_NO_DESC=@SER_NO_DESC+@STR1
END

--****取得系統月份****
IF @V1='2'
BEGIN
SELECT @STR2=MONTH(GETDATE())
IF @vstrISOldDay='Y'
BEGIN
IF @STR2='1'
SELECT @STR2='12'
ELSE SELECT @STR2=@STR2-1
END
IF @V3='Y'
SELECT @STR2=(SELECT RIGHT('000000'+CONVERT(VARCHAR,@STR2),@V2))
IF @V4='N'
SELECT @SER_NO=@SER_NO+@STR2
SELECT @SER_NO_DESC=@SER_NO_DESC+@STR2
END

--****取得系統日期****
IF @V1='3'
BEGIN
SELECT @STR3=DAY(GETDATE())
IF @vstrISOldDay='Y'
BEGIN
IF @STR3='1'
BEGIN
SELECT @MONTH=MONTH(GETDATE())
IF @MONTH='1' OR @MONTH='3' OR @MONTH='5' OR @MONTH='7' OR @MONTH='8' OR @MONTH='10' OR @MONTH='12'
SELECT @STR3='31'
ELSE IF @MONTH='2'
SELECT @STR3='28'
ELSE SELECT @STR3='30'
DECLARE @LEAP_YEAR AS INT --在此判斷是不是閏年
SELECT @LEAP_YEAR=YEAR(GETDATE())%4
IF @LEAP_YEAR=0
SELECT @STR3='29'
END
ELSE SELECT @STR3=@STR3-1
END
IF @V3='Y'
SELECT @STR3=(SELECT RIGHT('000000'+CONVERT(VARCHAR,@STR3),@V2))
IF @V4='N'
SELECT @SER_NO=@SER_NO+@STR3
SELECT @SER_NO_DESC=@SER_NO_DESC+@STR3
END

IF @V1='4'
BEGIN
SELECT @STR4= ( SELECT OU_NO_IDENT FROM ODMADDOUINF WHERE OU_ID=@vstrDEPT_ID AND OU_COMP_ID=@vstrCompID AND OU_DOMAIN_ID='GSS' )
IF @V4='N'
SELECT @SER_NO=@SER_NO+@STR4
SELECT @SER_NO_DESC=@SER_NO_DESC+@STR4
END
IF @V1='5'
BEGIN
SELECT @STR5= @V7
IF @V4='N'
SELECT @SER_NO=@SER_NO+@STR5
SELECT @SER_NO_DESC=@SER_NO_DESC+@STR5
END

IF @V1='6'
BEGIN
SELECT @STRSQL='SELECT * FROM ODMSERNOCTL WHERE COMP_ID ='''+ @vstrCompID + ''' AND AP_ID = ''' + @vstrAPID +''' AND SERCLASS_ID='''+@vstrSERCLASS_ID+''''
IF ''+@SERNO_ID +'' LIKE '%0%'
BEGIN
IF @RESET_KIND LIKE '%1%'
SELECT @STRSQL=@STRSQL+' AND DEPT_IDENT=''' + @vstrDEPT_ID+''''

IF @RESET_KIND LIKE '%2%'
BEGIN
SELECT @STRSQL=@STRSQL+' AND YEAR_CLASS='''+@STR1 +''''
SELECT @YEAR_CODE=@STR1
END
ELSE SELECT @YEAR_CODE=' '

IF @RESET_KIND LIKE '%3%'
BEGIN
SELECT @STRSQL=@STRSQL+' AND MONTH_CLASS='+@STR2
SELECT @MONTH_CODE=@STR2
END
ELSE SELECT @MONTH_CODE=' '

IF @RESET_KIND LIKE '%4%'
BEGIN
SELECT @STRSQL=@STRSQL+' AND DAY_CLASS='+@STR3
SELECT @DAY_CODE=@STR3
END
ELSE SELECT @DAY_CODE=' '
END
exec (@STRSQL)
if @@rowcount>0
BEGIN
SELECT @STR6=(SELECT ISNULL(CONVERT(INT,MAX_NO),0)+1 AS A
FROM ODMSERNOCTL
WHERE COMP_ID =@vstrCompID
AND AP_ID = @vstrAPID
AND SERCLASS_ID=@vstrSERCLASS_ID
AND DEPT_IDENT=@vstrDEPT_ID
AND YEAR_CLASS=@YEAR_CODE
AND MONTH_CLASS=@MONTH_CODE
AND DAY_CLASS=@DAY_CODE)
IF @V3='Y'
SELECT @STR6=(SELECT RIGHT('0000000000'+CONVERT(VARCHAR,@STR6),@V2))
IF @V4='N'
SELECT @SER_NO=@SER_NO+@STR6
SELECT @SER_NO_DESC=@SER_NO_DESC+@STR6
SELECT @STR6_IS_SHOW=@V4
SELECT @STR6_IS_FILL_SYM=@V3
SELECT @STR6_NO_LEN =@V2
UPDATE ODMSERNOCTL SET MAX_NO=(MAX_NO*1+CONVERT(INT,@vstrCount))
WHERE COMP_ID =@vstrCompID
AND AP_ID = @vstrAPID
AND SERCLASS_ID=@vstrSERCLASS_ID
AND DEPT_IDENT=@vstrDEPT_ID
AND YEAR_CLASS=@YEAR_CODE
AND MONTH_CLASS=@MONTH_CODE
AND DAY_CLASS=@DAY_CODE
END
ELSE
BEGIN
SELECT @STR6='1'
INSERT INTO ODMSERNOCTL (COMP_ID,AP_ID,SERCLASS_ID,DEPT_IDENT,YEAR_CLASS,MONTH_CLASS,DAY_CLASS,MAX_NO)
VALUES (@vstrCompID,@vstrAPID,@vstrSERCLASS_ID,@vstrDEPT_ID,@YEAR_CODE,@MONTH_CODE,@DAY_CODE,@vstrCount)
IF @V3='Y'
SELECT @STR6=(SELECT RIGHT('0000000000'+CONVERT(VARCHAR,@STR6),@V2))
IF @V4='N'
SELECT @SER_NO=@SER_NO+@STR6
SELECT @SER_NO_DESC=@SER_NO_DESC+@STR6
SELECT @STR6_IS_SHOW=@V4
SELECT @STR6_IS_FILL_SYM=@V3
SELECT @STR6_NO_LEN =@V2
END
END
FETCH NEXT FROM Employee_Cusor INTO @V1,@V2,@V3,@V4,@V5,@V6,@V7,@V8
END
CLOSE Employee_Cusor
DEALLOCATE Employee_Cusor

WHILE(@vstrCount>0)
BEGIN
INSERT INTO ODRBARCODE (COMP_ID,AP_ID,DEPT_IDENT,USR_ID,SERCLASS_ID,SER_NO,SER_NO_DESC,SER_NO_MARK)
VALUES (@vstrCompID,@vstrAPID,@vstrDEPT_ID,@vstrUSR_ID,@vstrSERCLASS_ID,@SER_NO,@SER_NO_DESC,'*'+@SER_NO+'*')

INSERT INTO ODMCODEDET (COMP_ID,AP_ID,SER_NO,SER_NO_DESC,SERNO_TYPE,SERNO_KIND,USE_DEPT_IDENT,SERNO_YEAR,SERNO_DEPT_IDENT,SERNO_USR_IDENT,SERNO_DT,SERNO_SYS)
VALUES (@vstrCompID,@vstrApID,@SER_NO,@SER_NO_DESC,@vstrSERCLASS_ID,LTRIM(RTRIM(@vstrSERCLASS_ID))+'0',@vstrUSE_DEPT_ID,@STR1,@vstrDEPT_ID,@vstrUSR_ID,GETDATE(),@vstrSERNO_SYS)

IF @STR6 <> ''
BEGIN
SELECT @STR6_COPY=(SELECT CONVERT(INT,@STR6)+1)
IF @STR6_IS_FILL_SYM = 'Y'
SELECT @STR6_COPY=(SELECT RIGHT('0000000000'+CONVERT(VARCHAR,@STR6_COPY),@STR6_NO_LEN))
IF @STR6_IS_SHOW='N'
SELECT @SER_NO=REPLACE(@SER_NO,@STR6,@STR6_COPY)
SELECT @SER_NO_DESC=REPLACE(@SER_NO_DESC,@STR6,@STR6_COPY)
SELECT @STR6=@STR6_COPY
END
SELECT @vstrCount=@vstrCount -1
END

PRINT @SER_NO
PRINT @SER_NO_DESC

IF @COUNT =1
BEGIN
IF @SER_NO <> ''
BEGIN
SELECT @RETURNS ='1'
SELECT @RETURNS=@RETURNS + '#'
SELECT @RETURNS=@RETURNS + @SER_NO
SELECT @RETURNS=@RETURNS + '#'
SELECT @RETURNS=@RETURNS + @SER_NO_DESC
END
IF @SER_NO = ''
BEGIN
SELECT @RETURNS ='0'
SELECT @RETURNS=@RETURNS + '# '
--SELECT @RETURNS=@RETURNS + @SER_NO
SELECT @RETURNS=@RETURNS + '# '
--SELECT @RETURNS=@RETURNS + @SER_NO_DESC

END
END

IF @COUNT >1
BEGIN
IF @SER_NO <> ''
BEGIN
SELECT @RETURNS ='1'
SELECT @RETURNS=@RETURNS + '# '
SELECT @RETURNS=@RETURNS + '# '

END
IF @SER_NO = ''
BEGIN
SELECT @RETURNS ='0'
SELECT @RETURNS=@RETURNS + '# '
SELECT @RETURNS=@RETURNS + '# '
END
END
COMMIT TRANSACTION
GO

已經解決
謝謝大家
代碼如下：


CREATE or replace PROCEDURE SP_GETSERNO
(
vstrCompID IN VARCHAR2,
vstrApID IN VARCHAR2,
vstrSERCLASS_ID IN VARCHAR2,
vstrDEPT_ID IN VARCHAR2,
vstrUSE_DEPT_ID IN VARCHAR2,
vstrUSR_ID IN VARCHAR2,
vstrCount IN INTEGER,
vstrSERNO_SYS IN VARCHAR2,
vstrISOldDay IN VARCHAR2
,RETURNS OUT VARCHAR2
)
AS
sRETURNS VARCHAR2(50):=' ';
SER_NO VARCHAR2(50); --文號
SER_NO_DESC VARCHAR2(50); --顯示的文號
V1 VARCHAR2(50); --SERNO_ID字段
V2 VARCHAR2(50); --NO_LEN　字段
V3 VARCHAR2(50); --IS_FILL_SYM　字段
V4 VARCHAR2(50); --IS_SHOW字段
V5 VARCHAR2(50); --RESET_KIND字段
V6 VARCHAR2(50); --YEAR_TYPE　字段
V7 VARCHAR2(50); --FILL_SYM　字段
V8 VARCHAR2(50); --SERNO_PART　字段
STR1 VARCHAR2(50):=' '; --SERNO_ID='1'時獲取的值
STR2 VARCHAR2(50):=' '; --SERNO_ID='2'時獲取的值
STR3 VARCHAR2(50):=' '; --SERNO_ID='3'時獲取的值
STR4 VARCHAR2(50):=' '; --SERNO_ID='4'時獲取的值
STR5 VARCHAR2(50):=' '; --SERNO_ID='5'時獲取的值
STR6 VARCHAR2(50):=' '; --SERNO_ID='6'時獲取的值
MONTH VARCHAR2(50); --系統的月份
STRSQL VARCHAR2(500); --SQL語句字符串
STRSQL_INSERT VARCHAR2(500); --SQL語句字符串
SERNO_ID VARCHAR2(50);
RESET_KIND VARCHAR2(50);
YEAR_CODE VARCHAR2(50):=' ';
MONTH_CODE VARCHAR2(50):=' ';
DAY_CODE VARCHAR2(50):=' ';
STR6_IS_SHOW VARCHAR2(50);
STR6_IS_FILL_SYM VARCHAR2(50);
STR6_NO_LEN VARCHAR2(50);
STR6_COPY VARCHAR2(50);
LEAP_YEAR INTEGER; --在此判斷是不是閏年
i INTEGER; --插入次數
DATANOW DATE;
STRDEPT_IDENT VARCHAR2(50);
cid INTEGER;
int_COMP_ID INTEGER;

BEGIN
SER_NO :=' ';
SER_NO_DESC :=' ';
STRSQL := ' ';
SERNO_ID := ' ';
RESET_KIND := ' ';
i :=1;
STRDEPT_IDENT:=' ';
int_COMP_ID :=0;

DECLARE Cursor Employee_Cusor IS
SELECT SERNO_ID,NO_LEN,IS_FILL_SYM,IS_SHOW,RESET_KIND,YEAR_TYPE,FILL_SYM,SERNO_PART
FROM ODMSERNOASMINF
WHERE COMP_ID= vstrCompID AND AP_ID = vstrAPID AND SERCLASS_ID=vstrSERCLASS_ID
ORDER BY SERNO_PART;
BEGIN
OPEN Employee_Cusor;
LOOP
FETCH Employee_Cusor INTO V1,V2,V3,V4,V5,V6,V7,V8;
EXIT WHEN Employee_Cusor%NOTFOUND;
BEGIN
SERNO_ID :=SERNO_ID || V1; --SERNO_ID累加
RESET_KIND :=RESET_KIND || V5; --RESET_KIND累加
IF V1='1' THEN
SELECT SUBSTR(TO_DATE(SYSDATE),1,4) INTO STR1 FROM DUAL;
IF V6='1' THEN
SELECT SUBSTR(TO_DATE(SYSDATE),1,4)-1911 INTO STR1 FROM DUAL;
END IF;
IF vstrISOldDay='Y' THEN
STR1 :=TO_NUMBER(TRIM(STR1))-1;
END IF;
IF V3='Y' THEN
SELECT LPAD(TO_CHAR(TRIM(STR1)),V2,'0') INTO STR1 FROM DUAL;
END IF;
IF V4='N' THEN
SER_NO :=SER_NO || TRIM(STR1);
END IF;
SER_NO_DESC :=SER_NO_DESC || TRIM(STR1);
END IF;

--****取得系統月份****
IF V1='2' THEN
SELECT TO_NUMBER(SUBSTR(TO_DATE(SYSDATE),6,2)) INTO STR2 FROM DUAL;
IF vstrISOldDay='Y' THEN
IF STR2='1' THEN
STR2 :='12';
ELSE
STR2 :=STR2-1;
END IF;
END IF;
IF V3='Y' THEN
SELECT LPAD(TO_CHAR(STR2),V2,'0') INTO STR2 FROM DUAL;
END IF;
IF V4='N' THEN
SER_NO :=SER_NO || STR2;
END IF;
SER_NO_DESC :=SER_NO_DESC || STR2;
END IF;

--****取得系統日期****
IF V1='3' THEN
SELECT TO_NUMBER(SUBSTR(TO_DATE(SYSDATE),9,2)) INTO STR3 FROM DUAL;

IF vstrISOldDay='Y' THEN

IF STR3='1' THEN
SELECT TO_NUMBER(SUBSTR(TO_DATE(SYSDATE),1,2)) INTO MONTH FROM DUAL;

IF MONTH='1' OR MONTH='3' OR MONTH='5' OR MONTH='7' OR MONTH='8' OR MONTH='10' OR MONTH='12' THEN
STR3 :='31';
ELSIF MONTH='2' THEN
STR3 :='28';
ELSE
STR3 :='30';
END IF;

SELECT SUBSTR(TO_DATE(SYSDATE),7,4) INTO LEAP_YEAR FROM DUAL;
LEAP_YEAR:=TO_NUMBER(LEAP_YEAR) MOD 4;

IF LEAP_YEAR=0 THEN
STR3 :='29';
ELSE
STR3 :=STR3-1;
END IF;
END IF;
IF V3='Y' THEN
SELECT LPAD(TO_CHAR(STR3),V2,'0') INTO STR3 FROM DUAL;
END IF;
IF V4='N' THEN
SER_NO :=SER_NO || STR3;
END IF;
SER_NO_DESC :=SER_NO_DESC || STR3;
END IF;
END IF;

IF V1='4' THEN
SELECT OU_NO_IDENT INTO STR4 FROM ODMADDOUINF WHERE OU_ID=vstrDEPT_ID AND OU_COMP_ID=vstrCompID AND OU_DOMAIN_ID='GSS' ;
IF V4='N' THEN
SER_NO :=SER_NO || STR4;
END IF;
SER_NO_DESC :=SER_NO_DESC || STR4;
END IF;

IF V1='5' THEN
STR5 :=V7;
IF V4='N' THEN
SER_NO :=SER_NO || STR5;
END IF;
SER_NO_DESC :=SER_NO_DESC || STR5;
END IF;

IF V1='6' THEN
STRSQL:='SELECT COUNT(COMP_ID) FROM ODMSERNOCTL WHERE COMP_ID =''' || vstrCompID || ''' AND AP_ID = ''' || vstrAPID || ''' AND SERCLASS_ID=''' || vstrSERCLASS_ID || '''';
IF SERNO_ID LIKE '%0%' THEN
IF RESET_KIND LIKE '%1%' THEN
STRSQL :=STRSQL || ' AND DEPT_IDENT=''' || vstrDEPT_ID || '''';
END IF;
IF RESET_KIND LIKE '%2%' THEN
STRSQL :=STRSQL || ' AND TRIM(YEAR_CLASS)||''A''=''' || trim(STR1) || 'A''';
YEAR_CODE :=STR1;
ELSE
YEAR_CODE :=' ';
END IF;
IF RESET_KIND LIKE '%3%' THEN
STRSQL :=STRSQL || ' AND TRIM(MONTH_CLASS)||''A''=''' || trim(STR2) || 'A''';
MONTH_CODE :=STR2;
ELSE
MONTH_CODE :=' ';
END IF;
IF RESET_KIND LIKE '%4%' THEN
STRSQL :=STRSQL || ' AND TRIM(DAY_CLASS)||''A''=''' || trim(STR3) || 'A''';
DAY_CODE :=STR3;
ELSE
DAY_CODE :=' ';
END IF;
END IF;
Execute immediate STRSQL into int_COMP_ID;
IF int_COMP_ID >0 THEN --****************如果查到記錄 ，待確認
SELECT TO_NUMBER(NVL(MAX_NO,'0'))+1 AS A INTO STR6
FROM ODMSERNOCTL
WHERE COMP_ID =vstrCompID
AND AP_ID = vstrAPID
AND SERCLASS_ID=vstrSERCLASS_ID
AND DEPT_IDENT=vstrDEPT_ID
AND TRIM(YEAR_CLASS)||'A'=TRIM(YEAR_CODE)||'A'
AND TRIM(MONTH_CLASS)||'A'=TRIM(MONTH_CODE)||'A'
AND TRIM(DAY_CLASS)||'A'=TRIM(DAY_CODE)||'A';
IF V3='Y' THEN
SELECT LPAD(TO_CHAR(STR6),V2,'0') INTO STR6 FROM DUAL;
END IF;
IF V4='N' THEN
SER_NO :=SER_NO || STR6;
END IF;
SER_NO_DESC :=SER_NO_DESC || STR6;
STR6_IS_SHOW :=V4;
STR6_IS_FILL_SYM :=V3;
STR6_NO_LEN :=V2;
UPDATE ODMSERNOCTL SET MAX_NO=(TO_NUMBER(NVL(MAX_NO,'0'))+TO_NUMBER(vstrCount))
WHERE COMP_ID =vstrCompID
AND AP_ID = vstrAPID
AND SERCLASS_ID=vstrSERCLASS_ID
AND DEPT_IDENT=vstrDEPT_ID
AND TRIM(YEAR_CLASS)||'A'=TRIM(YEAR_CODE)||'A'
AND TRIM(MONTH_CLASS)||'A'=TRIM(MONTH_CODE)||'A'
AND TRIM(DAY_CLASS)||'A'=TRIM(DAY_CODE)||'A';

ELSE
STR6 :='1';
INSERT INTO ODMSERNOCTL(COMP_ID,AP_ID,SERCLASS_ID,DEPT_IDENT,YEAR_CLASS,MONTH_CLASS,DAY_CLASS,MAX_NO)
VALUES (vstrCompID,vstrAPID,vstrSERCLASS_ID,vstrDEPT_ID,YEAR_CODE,MONTH_CODE,DAY_CODE,vstrCount);
IF V3='Y' THEN
SELECT LPAD(TO_CHAR(STR6),V2,'0') INTO STR6 FROM DUAL;
END IF;
IF V4='N' THEN
SER_NO :=SER_NO || STR6;
END IF;
SER_NO_DESC :=SER_NO_DESC || STR6;
STR6_IS_SHOW :=V4;
STR6_IS_FILL_SYM :=V3;
STR6_NO_LEN :=V2;
END IF;
END IF;
END;
END LOOP;

WHILE i<=vstrCount LOOP
INSERT INTO ODRBARCODE (COMP_ID,AP_ID,DEPT_IDENT,USR_ID,SERCLASS_ID,SER_NO,SER_NO_DESC,SER_NO_MARK)
VALUES (vstrCompID,vstrAPID,vstrDEPT_ID,vstrUSR_ID,vstrSERCLASS_ID,SER_NO,SER_NO_DESC,'*' || SER_NO || '*');
SELECT TO_DATE(SYSDATE) INTO DATANOW FROM DUAL;
INSERT INTO ODMCODEDET (COMP_ID,AP_ID,SER_NO,SER_NO_DESC,SERNO_TYPE,SERNO_KIND,USE_DEPT_IDENT,SERNO_YEAR,SERNO_DEPT_IDENT,SERNO_USR_IDENT,SERNO_DT,SERNO_SYS)
VALUES (vstrCompID,vstrApID,SER_NO,SER_NO_DESC,vstrSERCLASS_ID,LTRIM(RTRIM(vstrSERCLASS_ID)) || '0',vstrUSE_DEPT_ID,STR1,vstrDEPT_ID,vstrUSR_ID,DATANOW,vstrSERNO_SYS);

IF STR6 <> ' ' THEN
SELECT TO_NUMBER(STR6)+1 INTO STR6_COPY FROM DUAL;
IF STR6_IS_FILL_SYM = 'Y' THEN
SELECT LPAD(TO_CHAR(STR6_COPY), STR6_NO_LEN,'0') INTO STR6_COPY FROM DUAL;
END IF;
IF STR6_IS_SHOW='N' THEN
SER_NO :=REPLACE(SER_NO,STR6,STR6_COPY);
END IF;
SER_NO_DESC :=REPLACE(SER_NO_DESC,STR6,STR6_COPY);
STR6 := STR6_COPY;
END IF;

i :=i + 1;
END LOOP;

IF vstrCount =1 THEN
IF SER_NO <> '' THEN
sRETURNS :='1';
sRETURNS :=sRETURNS || '#';
sRETURNS :=sRETURNS || SER_NO;
sRETURNS :=sRETURNS || '#';
sRETURNS :=sRETURNS || SER_NO_DESC;
ELSE
sRETURNS :='0';
sRETURNS :=sRETURNS || '# ';
sRETURNS :=sRETURNS || '# ';
END IF;
END IF;

IF vstrCount >1 THEN
IF SER_NO <> '' THEN
sRETURNS :='1';
sRETURNS :=sRETURNS || '# ';
sRETURNS :=sRETURNS || '# ';
ELSE
sRETURNS :='0';
sRETURNS :=sRETURNS || '# ';
sRETURNS :=sRETURNS || '# ';
END IF;
END IF;
RETURNS:=sRETURNS;
END;
COMMIT;
END;
Execute immediate STRSQL into int_COMP_ID;
這句是蠻重要的