string ls_sql
ls_sql="DECLARE @l_unicode int,@p_len int,@position int;~013~010" &
+"DECLARE @nstring nchar("+string(p_len)+");~013~010" &
+"DECLARE @n_encode nvarchar("+string(p_len)+");~013~010" &
+"SET @nstring = '"+pass+"';~013~010" &
+"delete from zj_unicode;~013~010" &
+"SET @position = 1;~013~010" &
+"SET @p_len=len(@nstring);~013~010" &
+"SET @n_encode='';~013~010" &
+"WHILE @position <= @p_len~013~010" &
+"BEGIN~013~010" &
+"set @l_unicode=UNICODE(SUBSTRING(@nstring, @position, 1));~013~010" &
+"set @n_encode=@n_encode+nchar(@l_unicode+20);~013~010" &
+"SELECT @position = @position + 1;~013~010" &
+"END~013~010" &
+"insert into zj_unicode values(@n_encode);"
execute immediate :ls_sql;看了上面的例子,你现大应该明白怎样使用了吧,讨论请发邮件到[email protected]
ls_sql="DECLARE @l_unicode int,@p_len int,@position int;~013~010" &
+"DECLARE @nstring nchar("+string(p_len)+");~013~010" &
+"DECLARE @n_encode nvarchar("+string(p_len)+");~013~010" &
+"SET @nstring = '"+pass+"';~013~010" &
+"delete from zj_unicode;~013~010" &
+"SET @position = 1;~013~010" &
+"SET @p_len=len(@nstring);~013~010" &
+"SET @n_encode='';~013~010" &
+"WHILE @position <= @p_len~013~010" &
+"BEGIN~013~010" &
+"set @l_unicode=UNICODE(SUBSTRING(@nstring, @position, 1));~013~010" &
+"set @n_encode=@n_encode+nchar(@l_unicode+20);~013~010" &
+"SELECT @position = @position + 1;~013~010" &
+"END~013~010" &
+"insert into zj_unicode values(@n_encode);"
execute immediate :ls_sql;看了上面的例子,你现大应该明白怎样使用了吧,讨论请发邮件到[email protected]
select @aa = 1
while @aa <10
begin
select @aa
select @aa = @aa + 1
end
/*进度表*/
CREATE PROCEDURE PRO_CXTJ_JDB_1 (@BEGIN_DATETIME Char(20),@END_DATETIME Char(20))
AS
DECLARE @BM CHAR(20)
DECLARE @GZZ CHAR(20)
DECLARE @ZU CHAR(20)
DECLARE @BEGIN_DATE DATETIME
DECLARE @BEGIN_YEAR DATETIME
DECLARE @END_DATE DATETIME
DECLARE @DEC_NCCJDY DEC(12,2)
DECLARE @DEC_TEST DEC(12,2)
DECLARE @DEC_QBCJLJ DEC(12,2)
DECLARE @DEC_BYLLJG DEC(12,2)
DECLARE @DEC_LJLLJG DEC(12,2)
DECLARE @DEC_CKLJ_CIF DEC(12,2)
DECLARE @DEC_BYJK DEC(12,2)
DECLARE @DEC_CJBY DEC(12,2)
DECLARE @DEC_CJLJ DEC(12,2)
DECLARE @DEC_CKBY DEC(12,2)
DECLARE @DEC_CKBY_CIF DEC(12,2)
DECLARE @DEC_CKLJ DEC(12,2)
DECLARE @DEC_TOTAL DEC(14,2)
DECLARE @DEC_CJDY DEC(12,2)
DECLARE @DEC_YTWY DEC(12,2)
DECLARE @DEC_YTYY DEC(12,2)
SELECT @BEGIN_DATE = CONVERT(DATETIME,@BEGIN_DATETIME)
SELECT @END_DATE = CONVERT(DATETIME,@END_DATETIME)
SELECT @BEGIN_YEAR = CONVERT(DATETIME,SUBSTRING(@BEGIN_DATETIME,1,4)+'-01-01')
BEGIN TRAN
DECLARE CUR_BM CURSOR FOR
SELECT LTRIM(RTRIM(F_DM ))
FROM BS_BM
WHERE F_DM NOT IN ('O','F','H') DELETE FROM APP_CXTJ_JDB OPEN CUR_BM
FETCH CUR_BM INTO @BM
WHILE (@@fetch_status = 0 )
/*部门循环*/
BEGIN
DECLARE CUR_GZZ CURSOR FOR
SELECT LTRIM(RTRIM(F_ZBH)),LTRIM(RTRIM(F_ZMC))
FROM BS_GZZ
WHERE LTRIM(RTRIM(F_SSBM)) = @BM
OPEN CUR_GZZ
FETCH CUR_GZZ INTO @GZZ,@ZU
WHILE (@@fetch_status = 0)
/*组循环*/
BEGIN
SELECT @DEC_BYJK = 0, @DEC_BYLLJG =0,@DEC_LJLLJG = 0, @DEC_CJBY = 0,@DEC_CJLJ = 0,@DEC_CKBY = 0,@DEC_CKLJ = 0,@DEC_CKBY_CIF = 0,@DEC_CJDY = 0 ,/*@DEC_YTWY*/@DEC_TEST = 0,@DEC_YTYY =0, @DEC_CKLJ_CIF=0,@DEC_NCCJDY=0,@DEC_QBCJLJ=0
/*CJBY *****本月合同cif*/
DELETE FROM APP_CXTJ_TEMP
INSERT INTO APP_CXTJ_TEMP(F_BM,F_AMOUNT)
SELECT @BM, A.F_ZJ*(SELECT B.F_HL
FROM BS_HB B,APP_HT_WXHEAD C
WHERE B.F_DM = C.F_HBZT AND
C.F_WXHTBH = A.F_HTBH )
FROM APP_HT_WXMX A,APP_HT_WXHEAD D
WHERE A.F_HTBH = D.F_WXHTBH AND
D.F_BMDM = @BM AND
D.F_GZZ = @GZZ AND
D.F_HTZT = '3' AND
D.F_SHRQ <= @END_DATE AND
D.F_SHRQ >= @BEGIN_DATE
SELECT @DEC_CJBY = SUM(F_AMOUNT)
FROM APP_CXTJ_TEMP
GROUP BY F_BM
IF @DEC_CJBY IS NULL SELECT @DEC_CJBY = 0
/*BYLLJG *****本月来料加工合同cif*/
DELETE FROM APP_CXTJ_TEMP
INSERT INTO APP_CXTJ_TEMP(F_BM,F_AMOUNT)
SELECT @BM, A.F_MONEY*(SELECT B.F_HL
FROM BS_HB B,APP_HT_LLJGHEAD C
WHERE B.F_DM = C.F_HBZL AND
C.F_HTBH = A.F_HTBH )
FROM APP_HT_LLJGHEAD A
WHERE A.F_BMDM = @BM AND
A.F_GZZ = @GZZ AND
A.F_HTZT = '3' AND
A.F_SHRQ <= @END_DATE AND
A.F_SHRQ >= @BEGIN_DATE
SELECT @DEC_BYLLJG = SUM(F_AMOUNT)
FROM APP_CXTJ_TEMP
GROUP BY F_BM
IF @DEC_BYLLJG IS NULL SELECT @DEC_BYLLJG = 0
SELECT @DEC_CJBY = @DEC_CJBY + @DEC_BYLLJG
/*SUM(CJLJ) ***累计合同cif**/
DELETE FROM APP_CXTJ_TEMP
INSERT INTO APP_CXTJ_TEMP(F_BM,F_AMOUNT)
SELECT @BM, A.F_ZJ*(SELECT B.F_HL
FROM BS_HB B,APP_HT_WXHEAD C
WHERE B.F_DM = C.F_HBZT AND
C.F_WXHTBH = A.F_HTBH )
FROM APP_HT_WXMX A,APP_HT_WXHEAD D
WHERE A.F_HTBH = D.F_WXHTBH AND
D.F_BMDM = @BM AND
D.F_HTZT = '3' AND
D.F_GZZ = @GZZ AND
D.F_SHRQ <= @END_DATE AND
D.F_SHRQ >= @BEGIN_YEAR
SELECT @DEC_CJLJ = SUM(F_AMOUNT)
FROM APP_CXTJ_TEMP
GROUP BY F_BM
IF @DEC_CJLJ IS NULL SELECT @DEC_CJLJ = 0
/*LJLLJG *****本月来料加工合同累计cif*/
DELETE FROM APP_CXTJ_TEMP
INSERT INTO APP_CXTJ_TEMP(F_BM,F_AMOUNT)
SELECT @BM, A.F_MONEY*(SELECT B.F_HL
FROM BS_HB B,APP_HT_LLJGHEAD C
WHERE B.F_DM = C.F_HBZL AND
C.F_HTBH = A.F_HTBH )
FROM APP_HT_LLJGHEAD A
WHERE A.F_BMDM = @BM AND
A.F_GZZ = @GZZ AND
A.F_HTZT = '3' AND
A.F_SHRQ <= @END_DATE AND
A.F_SHRQ >= @BEGIN_YEAR
SELECT @DEC_LJLLJG = SUM(F_AMOUNT)
FROM APP_CXTJ_TEMP
GROUP BY F_BM
IF @DEC_LJLLJG IS NULL SELECT @DEC_LJLLJG = 0
SELECT @DEC_CJLJ = @DEC_CJLJ + @DEC_LJLLJG
/*出口实际fob , */
DELETE FROM APP_CXTJ_TEMP
INSERT INTO APP_CXTJ_TEMP(F_BM,F_AMOUNT)
SELECT @BM, A.F_FOBJE * (SELECT B.F_HL
FROM BS_HB B,APP_FXD_JBTK C
WHERE B.F_DM = C.F_HKBZ AND
C.F_FPBH = A.F_FPBH )
FROM APP_FXD_HWTK A,APP_FXD_JBTK D,APP_DZ_HX_XZ,APP_DZ_HXBS_CM
WHERE SUBSTRING(A.F_FPBH,3,1) = @BM AND
SUBSTRING(A.F_FPBH,4,1) = @GZZ AND
A.F_FPBH = D.F_FPBH AND
/* (NOT APP_DZ_HX_XZ.F_CMMC IS NULL) AND*/
APP_DZ_HX_XZ.F_FPBH=D.F_FPBH AND
APP_DZ_HX_XZ.F_CMBH = APP_DZ_HXBS_CM.F_DH AND
APP_DZ_HXBS_CM.F_YJKHRQ >= @BEGIN_DATE AND
APP_DZ_HXBS_CM.F_YJKHRQ <= @END_DATE
and SUBSTRING(A.F_FPBH,1,2)='01' /*AND
(D.F_ZT = '1' OR D.F_ZT = '2')*/
SELECT @DEC_CKBY = round(SUM(F_AMOUNT),2)
FROM APP_CXTJ_TEMP
GROUP BY F_BM
IF @DEC_CKBY IS NULL SELECT @DEC_CKBY = 0
/*出口实际fob(sum) , */
DELETE FROM APP_CXTJ_TEMP
INSERT INTO APP_CXTJ_TEMP(F_BM,F_AMOUNT)
SELECT @BM, A.F_FOBJE * (SELECT B.F_HL
FROM BS_HB B,APP_FXD_JBTK C
WHERE B.F_DM = C.F_HKBZ AND
C.F_FPBH = A.F_FPBH )
FROM APP_FXD_HWTK A,APP_FXD_JBTK D,APP_DZ_HX_XZ,APP_DZ_HXBS_CM
WHERE SUBSTRING(A.F_FPBH,3,1) = @BM AND
SUBSTRING(A.F_FPBH,4,1) = @GZZ AND
A.F_FPBH = D.F_FPBH AND
(NOT APP_DZ_HX_XZ.F_CMBH IS NULL) AND
(NOT APP_DZ_HX_XZ.F_CMBH ='无') and
APP_DZ_HX_XZ.F_FPBH=D.F_FPBH AND
APP_DZ_HX_XZ.F_CMBH = APP_DZ_HXBS_CM.F_DH AND
APP_DZ_HXBS_CM.F_YJKHRQ >= @BEGIN_YEAR AND
APP_DZ_HXBS_CM.F_YJKHRQ <= @END_DATE
/* AND
(D.F_ZT = '1' OR D.F_ZT = '2')*/
SELECT @DEC_CKLJ = SUM(F_AMOUNT)
FROM APP_CXTJ_TEMP
GROUP BY F_BM
IF @DEC_CKLJ IS NULL SELECT @DEC_CKLJ = 0
/*出口实际cif*/
DELETE FROM APP_CXTJ_TEMP
INSERT INTO APP_CXTJ_TEMP(F_BM,F_AMOUNT)
SELECT @BM, A.F_FPJE * (SELECT B.F_HL
FROM BS_HB B,APP_FXD_JBTK C
WHERE B.F_DM = C.F_HKBZ AND
C.F_FPBH = A.F_FPBH )
FROM APP_FXD_HWTK A,APP_FXD_JBTK D,APP_DZ_HX_XZ,APP_DZ_HXBS_CM
WHERE SUBSTRING(A.F_FPBH,3,1) = @BM AND
SUBSTRING(A.F_FPBH,4,1) = @GZZ AND
A.F_FPBH = D.F_FPBH AND
/* D.F_SDRQ >= @BEGIN_DATE AND
D.F_SDRQ <= @END_DATE AND*/
(NOT APP_DZ_HX_XZ.F_CMMC IS NULL) AND
APP_DZ_HX_XZ.F_FPBH=D.F_FPBH AND
APP_DZ_HX_XZ.F_CMBH = APP_DZ_HXBS_CM.F_DH AND
APP_DZ_HXBS_CM.F_YJKHRQ >= @BEGIN_YEAR AND
APP_DZ_HXBS_CM.F_YJKHRQ <= @END_DATE
/* AND
(D.F_ZT = '1' OR D.F_ZT = '2')*/
SELECT @DEC_CKBY_CIF = SUM(F_AMOUNT)
FROM APP_CXTJ_TEMP
GROUP BY F_BM
IF @DEC_CKBY_CIF IS NULL SELECT @DEC_CKBY_CIF = 0 /*出口实际累计cif*/
DELETE FROM APP_CXTJ_TEMP
INSERT INTO APP_CXTJ_TEMP(F_BM,F_AMOUNT)
SELECT @BM, A.F_FPJE * (SELECT B.F_HL
FROM BS_HB B,APP_FXD_JBTK C
WHERE B.F_DM = C.F_HKBZ AND
C.F_FPBH = A.F_FPBH )
FROM APP_FXD_HWTK A,APP_FXD_JBTK D,APP_DZ_HX_XZ,APP_DZ_HXBS_CM
WHERE SUBSTRING(A.F_FPBH,3,1) = @BM AND
SUBSTRING(A.F_FPBH,4,1) = @GZZ AND
A.F_FPBH = D.F_FPBH AND
/* D.F_SDRQ >= @BEGIN_YEAR AND
D.F_SDRQ <= @END_DATE AND*/
(NOT APP_DZ_HX_XZ.F_CMMC IS NULL) AND
APP_DZ_HX_XZ.F_FPBH=D.F_FPBH AND
APP_DZ_HX_XZ.F_CMBH = APP_DZ_HXBS_CM.F_DH AND
APP_DZ_HXBS_CM.F_YJKHRQ >= @BEGIN_YEAR AND
APP_DZ_HXBS_CM.F_YJKHRQ <= @END_DATE /* AND
(D.F_ZT = '1' OR D.F_ZT = '2')*/
SELECT @DEC_CKLJ_CIF = SUM(F_AMOUNT)
FROM APP_CXTJ_TEMP
GROUP BY F_BM
IF @DEC_CKLJ_CIF IS NULL SELECT @DEC_CKLJ_CIF = 0
/*成交待运 = 出口合同累计cif - 出口实际累计cif*/
SELECT @DEC_CJDY = @DEC_CJLJ - @DEC_CKLJ_CIF
/*NCCJLJ *****年初成交累计*/
DELETE FROM APP_CXTJ_TEMP
INSERT INTO APP_CXTJ_TEMP(F_BM,F_AMOUNT)
SELECT @BM, A.F_NCJDY
FROM APP_HT_NCJDY A
WHERE substring(A.F_BMBH,1,1) = @BM AND
substring(A.F_GZZ,1,2) = @BM+@GZZ
/*AND
A.F_YEAR=@BEGIN_YEAR*/
SELECT @DEC_NCCJDY =F_AMOUNT
FROM APP_CXTJ_TEMP
IF @DEC_NCCJDY IS NULL SELECT @DEC_NCCJDY = 0
/*全部成交累计(QBCJLJ)=成交累计(CJLJ)+年初成交待运(NCCJDY)*/
SELECT @DEC_QBCJLJ=@DEC_CJLJ+@DEC_NCCJDY
/*已托已运累计cif*/
DELETE FROM APP_CXTJ_TEMP
INSERT INTO APP_CXTJ_TEMP(F_BM,F_AMOUNT)
/*SELECT @BM, A.F_FPJE * (SELECT B.F_HL
FROM BS_HB B,APP_FXD_JBTK C
WHERE B.F_DM = C.F_HKBZ AND
C.F_FPBH = A.F_FPBH )
FROM APP_FXD_HWTK A,APP_FXD_JBTK ,APP_DZ_HX_XZ D
WHERE SUBSTRING(A.F_FPBH,3,1) = @BM AND
SUBSTRING(A.F_FPBH,4,1) = @GZZ AND
APP_FXD_JBTK.F_SDRQ >= @BEGIN_DATE AND
APP_FXD_JBTK.F_SDRQ <= @END_DATE and
APP_FXD_JBTK.F_FPBH =+ D.F_FPBH AND
NOT D.F_TDSHRQ IS NULL AND
(A.F_ZT = '1' OR A.F_ZT = '2')*/ SELECT @BM,A.F_FPJE* (SELECT B.F_HL
FROM BS_HB B,APP_FXD_JBTK C
WHERE B.F_DM = C.F_HKBZ AND
C.F_FPBH = A.F_FPBH ) FROM APP_FXD_HWTK A,APP_FXD_JBTK,APP_DZ_HX_XZ
WHERE
SUBSTRING(APP_FXD_JBTK.F_FPBH,3,1) = @BM AND
SUBSTRING(APP_FXD_JBTK.F_FPBH,4,1) = @GZZ AND
APP_FXD_JBTK.F_SDRQ >= @BEGIN_YEAR AND
APP_FXD_JBTK.F_SDRQ <= @END_DATE and
A.F_FPBH = APP_FXD_JBTK.F_FPBH AND
APP_DZ_HX_XZ.F_FPBH=A.F_FPBH AND
NOT APP_DZ_HX_XZ.F_CMMC IS NULL
/*AND
(APP_FXD_JBTK.F_ZT = '1' OR APP_FXD_JBTK.F_ZT = '2')*/ SELECT @DEC_YTYY = SUM(F_AMOUNT)
FROM APP_CXTJ_TEMP
GROUP BY F_BM
IF @DEC_YTYY IS NULL SELECT @DEC_YTYY = 0 /*已托未运累计cif = 出口实际累计cif - 已托已运累计cif*/
/*-------------------------------------------------------------*/ DELETE FROM APP_CXTJ_TEMP
INSERT INTO APP_CXTJ_TEMP(F_BM,F_AMOUNT)
SELECT @BM, A.F_FPJE * (SELECT B.F_HL
FROM BS_HB B,APP_FXD_JBTK C
WHERE B.F_DM = C.F_HKBZ AND
C.F_FPBH = A.F_FPBH )
FROM APP_FXD_HWTK A,APP_FXD_JBTK D,APP_DZ_HX_XZ
WHERE SUBSTRING(A.F_FPBH,3,1) = @BM AND
SUBSTRING(A.F_FPBH,4,1) = @GZZ AND
A.F_FPBH = D.F_FPBH AND
/* D.F_SDRQ >= @BEGIN_YEAR AND
D.F_SDRQ <= @END_DATE AND*/
(APP_DZ_HX_XZ.F_CMMC IS NULL) AND
APP_DZ_HX_XZ.F_FPBH=D.F_FPBH
AND
(D.F_ZT = '1' OR D.F_ZT = '2') SELECT @DEC_TEST= SUM(F_AMOUNT)
FROM APP_CXTJ_TEMP
GROUP BY F_BM
/*-----------------------------------------------------*/ SELECT @DEC_YTWY = @DEC_CKLJ_CIF - @DEC_YTYY
/*BYJK *****本月进口合同cif*/
/*DELETE FROM APP_CXTJ_TEMP
INSERT INTO APP_CXTJ_TEMP(F_BM,F_AMOUNT)
SELECT @BM, A.F_ZJ*(SELECT B.F_HL
FROM BS_HB B,APP_HT_JKHEAD C
WHERE B.F_DM = C.F_HBZT AND
C.F_HTBH = A.F_HTBH )
FROM APP_HT_JKMX A,APP_HT_JKHEAD D
WHERE A.F_CBHTBH = D.F_CBHTBH AND
D.F_BMDM = @BM AND
D.F_GZZ = @GZZ AND
D.F_HTZT = '3' AND
D.F_SHRQ <= @END_DATE AND
D.F_SHRQ >= @BEGIN_DATE
SELECT @DEC_BYJK = SUM(F_AMOUNT)
FROM APP_CXTJ_TEMP
GROUP BY F_BM
IF @DEC_BYJK IS NULL SELECT @DEC_BYJK = 0*/ /*BYJK *****本年进口合同cif*/
DELETE FROM APP_CXTJ_TEMP
INSERT INTO APP_CXTJ_TEMP(F_BM,F_AMOUNT)
SELECT @BM, A.F_ZJ*(SELECT B.F_HL
FROM BS_HB B,APP_HT_JKHEAD C
WHERE B.F_DM = C.F_HBZT AND
C.F_HTBH = A.F_HTBH )
FROM APP_HT_JKMX A,APP_HT_JKHEAD D
WHERE A.F_CBHTBH = D.F_CBHTBH AND
D.F_BMDM = @BM AND
D.F_GZZ = @GZZ AND
D.F_HTZT = '3'/* AND
D.F_SHRQ <= @END_DATE AND
D.F_SHRQ >= @BEGIN_YEAR*/
SELECT @DEC_BYJK = SUM(F_AMOUNT)
FROM APP_CXTJ_TEMP
GROUP BY F_BM
IF @DEC_BYJK IS NULL SELECT @DEC_BYJK = 0
SELECT @DEC_TOTAL = @DEC_CJBY+@DEC_CJLJ+@DEC_CKBY+@DEC_CKLJ+@DEC_CKBY_CIF+@DEC_CJDY+/*@DEC_YTWY*/@DEC_TEST+@DEC_YTYY
/* IF @DEC_TOTAL <> 0
BEGIN*/
INSERT INTO APP_CXTJ_JDB(F_BM,F_GZZ,F_BYCJ,F_LJCJ,F_BYCK,F_LJCK,F_CJDY,F_YTWY,F_JKDH,F_JKDAOH)
SELECT @BM,@ZU,round(/*@DEC_CJBY*/9999/10000,2),round(@DEC_QBCJLJ/10000,2),round(@DEC_CKBY/*10000*/,2),round(@DEC_CKLJ/*10000*/,2),round(@DEC_CJDY/10000,2),round(/*@DEC_YTWY*/@DEC_TEST/10000,2),round(@DEC_BYJK/10000,2),0
/* END */
FETCH CUR_GZZ INTO @GZZ,@ZU
END
CLOSE CUR_GZZ
DEALLOCATE CUR_GZZ
FETCH CUR_BM INTO @BM
END
CLOSE CUR_BM
DEALLOCATE CUR_BM if @@error <> 0
begin
rollback tran
select -1
return
end
else
begin
COMMIT TRAN
select 0
return
end