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

2012-03-21 02:54:55 435 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

PLSQL: 存储过程,用户自定义函数[oracle] 使用匿名块调用存储过程时需要输出语句的原因在于，存储过程的输出参数（OUT参数）不会自动显示其值，它们是“静默”的，即不会像函数返回值那样直接返回。因此，为了查看存储过程的执行结果，需要在调用存储过程的匿名块中使用输出语句来显示这些值。CREATE OR REPLACE：创建或者替换，当存储过程修改的时候，编译后会覆盖掉原来的同名的存储过程。在存储过程中，对 IN 类型参数的值进行修改，新修改的值只在存储过程内部有效。-- 存储过程在创建的时候，只是编译，不会执行，调用存储过程的时候才会执行。阅读详情

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

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

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

...全文

SQL Server的存储过程和自定义函数而更改存储过程时，只是更改存储过程的内部定义，并不影响与该存储过程的内部定义，并不影响与该存储过程相关联的存储权限，并且不会影响相关的存储过程。该函数的返回值是一个数据表，但它和标量值自定义函数一样，有一个用BEGIN END包含起来的函数体，返回值的表中的数据是由函数体中的语句插入的。带输出参数的存储过程在执行时，一定要实现定义输出变量，输出变量的名称可以设定为符合标识命名规范的任意字符，也可以和存储过程中定义的输出变量名称保持一致，变量的类型要和存储过程中变量的类型完全一致。阅读详情

IDWB

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

昵称被占用了 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上的查询语句

加载更多回复（14）

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

相关推荐