在线等待，关于存储过程、自定义函数方面的问题！

IDWB 2012-03-21 02:54:55

一个表中有3个字段分别是
T1(datetime) | T2(datetime) | A1(numeric)

其中A1字段的值是要用到T1和T2字段的值为参数，通过一个很复杂的存储过程计算得到。
现在T1和T2字段值已经确定，记录大概有100万笔，请问如何批量生成A1字段的值

补充说明：我用过自定义函数，但是提示“只有函数和扩展存储过程才能从函数内部执行”

...全文

310 34 打赏收藏转发到动态举报

写回复

用AI写文章

34 条回复

切换为时间正序

请发表友善的回复…

发表回复

昵称被占用了 2012-03-23

打赏
举报

[Quote=引用 32 楼 idwb 的回复:]
Sp_calcstrdatediff和Sp_datetimetostr 这两个存储过程写成函数很简单，但解决不了问题
[/Quote]
如果Sp_calcstrdatediff和Sp_datetimetostr 这两个存储过程写成函数很简单，可以考虑用函数，临时表改成表变量。

昵称被占用了 2012-03-23

打赏
举报

存储过程调用存储过程
全部单条处理

需要改用集合操作思想，成批操作，你的存储过程多于两个参数，其他参数固定？你的这两个参数是固定的还是来自表数据？
估计改成集合操作的存储过程工作量最小，或者改成单行操作的函数，但是由于原来调用存储过程，估计很难改。这是个技术活也是苦力活。

IDWB 2012-03-22

打赏
举报

Sp_calcstrdatediff和Sp_datetimetostr 这两个存储过程写成函数很简单，但解决不了问题

IDWB 2012-03-22

打赏
举报

关键是不在于这两个过程，关键是上面写的代码中用到了临时表，自定义函数是不允许创建临时表的，编译就通不过，我现在考虑的不是我写的存储过程的效率，而是使用这个存储过程的算法来解决我那100万条数据的修改问题。上面说到用公用表表达式来解决，但是2000不支持

Vidor 2012-03-22

打赏
举报

改函数肯定可以改：Sp_calcstrdatediff和Sp_datetimetostr过程写成语句（这两个过程和复杂？猜测就一些列case/convert或日期函数之类），#改为@

问题是不从根本上优化算法，对效率提升没有太大的帮助。

IDWB 2012-03-22

打赏
举报

所以楼上的几位建议我吧这个存储过程贴出来直接变为函数，但是看样子更加不可行

IDWB 2012-03-22

打赏
举报

显然可以写一个自定义函数，里面只要调用这个存储过程就可以实现update table set myfield=myfunction(@rcsj，@ccsj) 但是mssql不允许我这么做

IDWB 2012-03-22

打赏
举报

以上代码只是将参数@rcsj，@ccsj通过那么大段代码得到@sfje参数的值的算法，而我真正的要求是，现在有一个存着@rcsj，@ccsj值的100万条记录的表高效的得到每条记录对应的@sfje值

Vidor 2012-03-22

打赏
举报

你的代码我就不看了，容易先入为主。
说说你要实现什么功能吧，涉及的表，计算逻辑。

IDWB 2012-03-22

打赏
举报

语法和逻辑上没什么问题，有问题也不是我所要问的关键

IDWB 2012-03-22

打赏
举报

是楼上几位让我贴出来的，我都不想贴的
其实不用看在写什么，关键是看看能不能转成函数

Vidor 2012-03-22

打赏
举报

眼花，就不能缩一些行吗，生怕不够长似的。

IDWB 2012-03-22

打赏
举报

还有没有人能帮我解决吗，难道我只能用游标了吗？

IDWB 2012-03-22

打赏
举报

没人能解决吗？看样子只能用笨办法了

IDWB 2012-03-21

打赏
举报



CREATE PROCEDURE Sp_fygz_a @rcsj   DATETIME,

                           @ccsj   DATETIME,

                           @sfbz   INTEGER,

                           @sfje   NUMERIC(18, 2) OUTPUT,

                           @result INTEGER OUTPUT,

                           @tcsj   INTEGER OUTPUT

AS

  DECLARE @qssj      VARCHAR(4),

          @zzsj      VARCHAR(4),

          @strrcsj   VARCHAR(4),

          @strccsj   VARCHAR(20),

          @sjc       INTEGER,

          @temptcsj  INTEGER,

          @maxqssj   INTEGER,

          @minqssj   INTEGER,

          @signstart INTEGER,

          @signend   INTEGER,

          @signsjc   INTEGER,

          @signid    INTEGER,

          @signzgxe  NUMERIC(18, 2),

          @tempzzd   INTEGER,

          @tempqsd   INTEGER,

          @tempdw    INTEGER,

          @tempdj    NUMERIC(18, 2),

          @tempid    INTEGER,

          @templx    BIT,

          @tempsfje  NUMERIC(18, 2),

          @Fid       INTEGER



  SELECT @qssj = Min(qssj),

         @zzsj = Max(zzsj)

  FROM   sfsjfdb

  WHERE  sflx = @sfbz



  SET @tcsj=Datediff(MINUTE, @rcsj, @ccsj)



  SET @temptcsj=@tcsj



  EXEC Sp_calcstrdatediff

    @qssj,

    @zzsj,

    @sjc OUTPUT ----------计算周期时间



  EXEC Sp_datetimetostr

    @rcsj,

    @ccsj,

    @strrcsj OUTPUT,

    @strccsj OUTPUT -----------将时间格式转换为4位字符格式



  --if datediff(second,@rcsj,@ccsj)<=60

  --begin

  -- @tcsj=1

  --@sjc=1

  --end

  -------计算一共几个收费周期，并且计算这些周期内费用

  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  SET @sfje=0



  select @signzgxe=zgxe

  FROM   sflxb

  WHERE  id = @sfbz



  WHILE @temptcsj >= @sjc

    BEGIN

        SET @sfje=@sfje + (SELECT zgxe

                           FROM   sflxb

                           WHERE  id = @sfbz)



        SET @temptcsj=@temptcsj - @sjc



        IF @temptcsj < @sjc

          SET @strccsj=Substring('0000' + Cast(@strccsj - ( 100 * @sjc / 60 ) AS VARCHAR(4)), Len('0000' + Cast(@strccsj - ( 100 * @sjc / 60 ) AS VARCHAR(4))) - 3, 4)

        ELSE

          SET @strccsj=@strccsj - ( 100 * @sjc / 60 )

    END



  ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  



 





 CREATE TABLE #tempsfsjfdb

    (

       [id]     [INT] NOT NULL,

       [sflx]   [INT] NULL,

       [qssj]   [INT] NULL,

       [zzsj]   [INT] NULL,

       [zgxe]   [NUMERIC](18, 2) NULL,

       [rcmf]   [BIT] NULL,

       [rcmfsj] [INT] NULL,

       [ccmf]   [BIT] NULL,

       [ccmfsj] [INT] NULL,

       [Fid]    [INT] IDENTITY (1, 1) NOT NULL

    )



  CREATE TABLE #tempsfbzb

    (

       [id]    [INT] NOT NULL,

       [sfsjd] [INT] NULL,

       [lx]    [BIT] NULL,

       [zzd]   [INT] NULL,

       [dj]    [NUMERIC](18, 2) NULL,

       [dw]    [INT] NULL

    )



  INSERT INTO #tempsfsjfdb

  SELECT id,

         sflx,

         ( qssj - 2400 ),

         ( zzsj - 2400 ),

         zgxe,

         rcmf,

         rcmfsj,

         ccmf,

         ccmfsj

  FROM   sfsjfdb

  WHERE  sflx = @sfbz

  UNION

  SELECT id,

         sflx,

         ( qssj - 0 ),

         ( zzsj - 0 ),

         zgxe,

         rcmf,

         rcmfsj,

         ccmf,

         ccmfsj

  FROM   sfsjfdb

  WHERE  sflx = @sfbz

  UNION

  SELECT id,

         sflx,

         ( qssj + 2400 ),

         ( zzsj + 2400 ),

         zgxe,

         rcmf,

         rcmfsj,

         ccmf,

         ccmfsj

  FROM   sfsjfdb

  WHERE  sflx = @sfbz





   

  -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  ------------根据实际时间缩小计算范围

  SELECT @minqssj = Max(qssj)

  FROM   #tempsfsjfdb

  WHERE  qssj <= @strrcsj



  SELECT @maxqssj = Max(qssj)

  FROM   #tempsfsjfdb

  WHERE  qssj <= @strccsj



  DELETE FROM #tempsfsjfdb

  WHERE  qssj < @minqssj

          OR qssj > @maxqssj



  SET @tempqsd=0



  SET @tempsfje=0



declare 

  @tempsfsjfdcount int,

  @temptcsj1 integer,

  @tempzzsj1 varchar(4),

  @temptcsj2 integer,

  @Je1 numeric(18,2),

  @je2 numeric(18,2)



   ----------------------------判断相离、相交、包含-----------------------------

       --------------------1、判断相离，条件为#tempsfsjfdb 数量=1为相离------------

       --------------------2、判断相交，条件为#tempsfsjfdb 数量=2为相交------------

       --------------------3、判断包含，条件为#tempsfsjfdb 数量>2为相交------------

       select @tempsfsjfdcount=count(*) from #tempsfsjfdb



 if @tempsfsjfdcount=1

  begin

   set @temptcsj1=@temptcsj

   set @temptcsj2=0

   set @je2=0

  end



 if @tempsfsjfdcount=2

  begin

    select top 1 @temptcsj2=(@strccsj/100-zzsj/100)*60+(@strccsj%100-zzsj%100) from #tempsfsjfdb order by qssj asc

    set @temptcsj1=@temptcsj-@temptcsj2

  end

 if @tempsfsjfdcount=3

  begin

    select top 1 @temptcsj2=(zzsj/100-qssj/100)*60+(zzsj%100-qssj%100)  from (select top 2 * from #tempsfsjfdb order by qssj asc) a order by a.qssj desc

    set @temptcsj1=@temptcsj-@temptcsj2

  end



  SELECT TOP 1 @signid = id

  FROM   #tempsfsjfdb

  ORDER  BY qssj ASC



  INSERT INTO #tempsfbzb

  SELECT *

  FROM   sfbzb

  WHERE  sfsjd = @signid

         AND zzd > 0

  ORDER  BY zzd ASC





  WHILE ( EXISTS(SELECT 1

                 FROM   #tempsfbzb)

          AND ( @temptcsj1 != 0 ) )

    BEGIN

        SELECT TOP 1 @tempzzd = zzd,

                     @tempdj = dj,

                     @tempdw = dw,

                     @tempid = id

        FROM   #tempsfbzb



        IF @tempzzd <= @temptcsj1

          BEGIN

              SET @tempsfje=@tempsfje + @tempdj * ( @tempzzd - @tempqsd ) / @tempdw



              SET @tempqsd=@tempzzd



              DELETE FROM #tempsfbzb

              WHERE  id = @tempid

          END

        ELSE

          BEGIN

              SET @tempsfje=@tempsfje + @tempdj * Ceiling(( @temptcsj1 - @tempqsd ) / ( @tempdw + 0.0 ))



              SET @temptcsj1=0

          END

    END



 set @Je1=@tempsfje





 if @tempsfsjfdcount=2

         begin  

      -----------------------------计算在相交的时间段所产生的费用----------------------------------------------

           SET @tempqsd=0



           SET @tempsfje=0





           SELECT TOP 1 @signid = id,

                        @strrcsj=qssj

           FROM   #tempsfsjfdb

           ORDER  BY qssj desc



           





           delete from #tempsfbzb



           INSERT INTO #tempsfbzb

           SELECT *

           FROM   sfbzb

           WHERE  sfsjd = @signid

                  AND zzd > 0

           ORDER  BY zzd ASC





           WHILE ( EXISTS(SELECT 1

                          FROM   #tempsfbzb)

                   AND ( @temptcsj2 != 0 ) )

             BEGIN

                 SELECT TOP 1 @tempzzd = zzd,

                              @tempdj = dj,

                              @tempdw = dw,

                              @tempid = id

                 FROM   #tempsfbzb



                 IF @tempzzd <= @temptcsj2

                   BEGIN

                       SET @tempsfje=@tempsfje + @tempdj * ( @tempzzd - @tempqsd ) / @tempdw



                       SET @tempqsd=@tempzzd



                       DELETE FROM #tempsfbzb

                       WHERE  id = @tempid

                   END

                 ELSE

                   BEGIN

                       SET @tempsfje=@tempsfje + @tempdj * Ceiling(( @temptcsj2 - @tempqsd ) / ( @tempdw + 0.0 ))



                       SET @temptcsj2=0

                   END

             END

          

          set @je2=@tempsfje

      -------------------------------------------------------------------------------------------------------



         end



       if @tempsfsjfdcount=3

         begin     

          select top 1 @je2=zgxe from (select top 2 * from #tempsfsjfdb order by qssj asc) a order by a.qssj desc

         end



--------------------------------------------

-----------------------------------------------  

  



-----------------------以下为计算按照出场时间来话费计算范围----------------------------------------------

   ----------------------------判断相离、相交、包含-----------------------------

       --------------------1、判断相离，条件为#tempsfsjfdb 数量=1为相离------------

       --------------------2、判断相交，条件为#tempsfsjfdb 数量=2为相交------------

       --------------------3、判断包含，条件为#tempsfsjfdb 数量>2为相交------------



   ----------------------------------------------------------------------------

 ---------------------------------------------------------------------------------------------------------------

 -----------------------------------------------------------------------------------------------------------





            set @tempsfje=@je1+@je2 

        if @tempsfje>=@signzgxe

          begin

           set @tempsfje=@signzgxe

          end



 --------------------------------------------------------------------------------------------------------

 

  



  SET @sfje=@sfje + @tempsfje



  DROP TABLE #tempsfbzb



  DROP TABLE #tempsfsjfdb



  IF @@ERROR != 0

    BEGIN

        SET @result=1



        RETURN

    END

  ELSE

    BEGIN

        SET @result=0



        RETURN

    END

GO

dawugui 2012-03-21

打赏
举报

建议你把存储过程转换为函数,然后去调用之更新数据.

例如:函数名dbo.f_setvalue(t1,t2)

update tb set a1 = dbo.f_setvalue(t1,t2)



建议你提供详细的资料：

例如表的结构，表之间的关系，测试数据，相关算法及需要的结果。

这样有助于我们理解你的意思，更主要的是能尽快让你获得答案或解决问题的方法。

黄_瓜 2012-03-21

打赏
举报

[Quote=引用 16 楼 idwb 的回复:]

忘记补充了，我用的是mssql2000
[/Quote]

你干脆贴出来数据，和计算步骤吧以及预期的结果，让人家帮你写的了

开着拖拉机泡妞 2012-03-21

打赏
举报

[Quote=引用 16 楼 idwb 的回复:]

忘记补充了，我用的是mssql2000
[/Quote]
那你就多见一个表吧

IDWB 2012-03-21

打赏
举报

忘记补充了，我用的是mssql2000

开着拖拉机泡妞 2012-03-21

打赏
举报

[Quote=引用 12 楼 idwb 的回复:]

我刚才试着将存储过程转为函数但是得到的提示是函数内不能使用临时表
[/Quote]

你可以用公用表达式替代临时表

;with t
as(
查询语句
)
基于t上的查询语句

SQL 自动批量数据脚本、Mysql 自定义函数、存储过程自动生产数据脚本

产品经理提需求我不慌了，Doris自定义函数三剑客一把梭！

深入学习Python自定义函数

目录一: php自定义函数之回调函数二: php自定义函数之变量函数三: php自定义函数之匿名函数四: php自定义函数之内部函数五: php自定义函数之变量作用域六: php自定义函数之参数的引用七: php自定义函数之递归函数八: php自定义函数之静态变量一: php自定义函数之回调函数 1.定义: 1).回调函数，可以配合匿名函数和变量函数实现更加...

MS-SQL Server

34,837

社区成员

254,632

社区内容

发帖

与我相关

我的任务

社区管理员

加入社区

近7日
近30日
至今

加载中

查看更多榜单

社区公告

暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章