• 欢迎访问搞代码网站,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站!
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏搞代码吧

我的存储过程学习2_sqlserver

sqlserver 搞代码 3年前 (2018-06-17) 112次浏览 已收录 0个评论

业务系统需要一个关于合同状态的报表,主要显示合同的状态,地区,合同客户类型,合同金额,利息金额等信息.
数据库中存在4个表
crec01c,sysc01,sysc03d,crmc02分别是’合同主表’,’区域及业务伙伴’,’系统状态代码’,’法人信息表’
下面第一个存储过程是我第一次写的,执行时间是5秒
 ——————效率较差的存储过程—————————

http://www.gaodaima.com/?p=36272我的存储过程学习2_sqlserver

 CREATE TABLE #tmptba –创建一个临时表,用于储存我们的结果
 (
  colID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
  khlx varchar(20),
  dq varchar(20),
  ywhb varchar(40),
  htzt varchar(20),
  htbs int ,
  htje numeric(13,2),
  lxje numeric(13,2)
 )
DECLARE  @bkhlx char(1), @ikhid int, @cywdbd char(10), @chtzt char(1), @njkje numeric(13,2), @iqx int, @nhtyll numeric(8,6) –FOR Progress
DECLARE @t_khlx varchar(20), @t_dq varchar(20), @t_ywhb varchar(40), @t_htzt varchar(20), @t_htbs int, @t_htje numeric(13,2), @t_lxje numeric(13,2)–FOR INSERT INTO #tmptba
–DECLARE @index int
–SET @index = 1
DECLARE cur1 CURSOR FOR SELECT bkhlx, ikhid, cywdbd, chtzt, njkje, iqx, nhtyll FROM crec01c
OPEN cur1
FETCH NEXT FROM cur1 INTO @bkhlx, @ikhid, @cywdbd, @chtzt, @njkje, @iqx, @nhtyll
WHILE @@FETCH_STATUS = 0
BEGIN
  IF @bkhlx = ‘1’
      BEGIN
          SET @t_khlx = ‘自然人’
          SET @t_dq  = (SELECT vjgmc FROM sysc01 WHERE cjgdm = left(@cywdbd,6))
      END
  ELSE
      BEGIN
          SET @t_khlx = ‘法人’
          SET @t_dq = (SELECT vjgmc FROM sysc01 WHERE cjgdm = (SELECT cbmdm FROM crmc02 WHERE frid = @ikhid ) )
      END
    SET @t_htbs = 1– @index–合同笔数

    SET @t_ywhb = (SELECT vjgmc FROM sysc01 WHERE cjgdm = @cywdbd)–业务伙伴
    SET @t_htzt = (SELECT vsjxc FROM sysc03d WHERE czddm = ‘htzt’ AND csjxm = @chtzt)–合同状态
    SET @t_htje = @njkje
    SET @t_lxje = @njkje * @iqx * @nhtyll * 0.001

   INSERT INTO #tmptba (khlx, dq, ywhb, htzt, htbs, htje, lxje) VALUES (@t_khlx, @t_dq, @t_ywhb, @t_htzt, @t_htbs, @t_htje, @t_lxje)
      –SET @index  1
  FETCH NEXT FROM cur1 INTO @bkhlx, @ikhid, @cywdbd, @chtzt, @njkje, @iqx, @nhtyll
END
CLOSE cur1
DEALLOCATE cur1
SELECT * FROM #tmptba
GO
 ——————效率较高的存储过程执行时间是1秒—————————
 CREATE TABLE #tmptbl –创建一个临时表,用于储存我们的结果
 (
  colID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
  khlx varchar(20),
  dq varchar(20),
  ywhb varchar(40),
  htzt varchar(20),
  htbs int ,
  htje numeric(13,2),
  lxje numeric(13,2)
 )
INSERT INTO #tmptbl SELECT CASE WHEN a.bkhlx = ‘1’ THEN ‘自然人’ ELSE ‘法人’ END AS khlx, dq = (SELECT vjgmc FROM sysc01 WHERE cjgdm = left(a.cywdbd,6)), ywhb = (SELECT vjgmc FROM sysc01 WHERE cjgdm = a.cywdbd), htzt = (SELECT vsjxc FROM sysc03d WHERE czddm = ‘htzt’ AND csjxm = a.chtzt), htbs = 1, htje = a.njkje, lxje = a.njkje * a.iqx * a.nhtyll * 0.001 FROM crec01c a WHERE a.bkhlx = ‘1’

INSERT INTO #tmptbl SELECT CASE WHEN a.bkhlx = ‘1’ THEN ‘自然人’ ELSE ‘法人’ END AS khlx, dq = (SELECT vjgmc FROM sysc01 WHERE cjgdm = (SELECT cbmdm FROM crmc02 WHERE frid = a.ikhid)), ywhb = ‘无’, htzt = (SELECT vsjxc FROM sysc03d WHERE czddm = ‘htzt’ AND csjxm = a.chtzt), htbs = 1, htje = a.njkje, lxje = a.njkje * a.iqx * a.nhtyll * 0.001 FROM crec01c a WHERE a.bkhlx = ‘2’

SELECT * FROM #tmptbl
GO

可以看到,第二个存储过程只用了两条sql语句就完成了第一个存储过程的工作。

欢迎大家阅读《我的存储过程学习2_sqlserver,跪求各位点评,若觉得好的话请收藏本文,by 搞代码


喜欢 (0)
[搞代码]
分享 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址