java存储过程 返回表 存储过程返回数组

在存储过程中，数据的返回有三种方法，当我们编写一个存储过程时，我们应该考虑前台绑定数据的方式而采取不同的返回方法。
    第一：select方法
      select是我们最常用的方法，它将数据作为标准的纪录集合返回。
    第二：return方法
      return 提供了返回状态值得方法，这个值一定要是个整数值。不要试图返回一个非整形的数据类型。
    第三：output方法：
      提供了返回参数值的方法。它可以将指定的参数返回。
请看下面的依照上面的顺序的举例：

CREATE PROCEDURE Sp_check_toq_task

     @present_time varchar(12)，

AS

SET NOCOUNT ON

DECLARE @send_task_mask INT   ///要返回的值

DECLARE @active_time varchar(12)

DECLARE @fail_time    varchar(12)

DECLARE @block_time   int

DECLARE @apply_days   varchar(9)

DECLARE @task_type    int

DECLARE @now_state    int

DECLARE @weekofday    varchar(3) 


DECLARE @present_number_time int

DECLARE @active_number_time   int

DECLARE @fail_number_time     int

DECLARE @old_time           Datetime


--- what is day of week to today

SET @weekofday = CASE DATENAME(WEEKDAY， GETDATE())

        WHEN ’Monday’       THEN   ’1’

        WHEN ’Tuesday’      THEN   ’2’

        WHEN ’Wednesday’    THEN   ’3’

        WHEN ’Thursday’     THEN   ’4’

        WHEN ’Friday’       THEN   ’5’

        WHEN ’Saturday’     THEN   ’6’

        WHEN ’Sunday’       THEN   ’7’

        ELSE                      ’0’

        END   

--- init task mask to 0 

SET @send_task_mask = 0

--- convert string time to int time

SET @present_number_time = CONVERT(INT，RTRIM(@present_time))

--- declare cursor          

DECLARE check_task_cursor CURSOR LOCAL DYNAMIC FOR SELECT
start_time，end_time，seprate_time，affect_days，data_type，recently_time，
present_status 

        FROM Toq_task_list 

        WHERE affect_days LIKE

        ’%’ + rtrim(@weekofday) + ’%’ and present_status = 0 AND
ABS(DATEDIFF(MI，recently_time， GETDATE())) >= seprate_time   

         FOR UPDATE OF recently_time 

      

--- open recdord set  

OPEN check_task_cursor

--- fill variable

FETCH NEXT FROM check_task_cursor INTO @active_time，@fail_time，@block_time，@apply_days，@task_type，@old_time，@now_state

WHILE @@FETCH_STATUS = 0

BEGIN      

     --- above of all， convert string to number

     SET @active_number_time = CONVERT(INT，rtrim(@active_time))

     SET @fail_number_time    = CONVERT(INT，rtrim(@fail_time))

    ----- IF((@present_number_time > @active_number_time) and (@present_number_time <= @fail_number_time)) BEGIN       

     IF((@present_number_time - @active_number_time>= @block_time) and (@present_number_time <= @fail_time)) BEGIN   

           SET @send_task_mask = @send_task_mask | @task_type

           BEGIN TRANSACTION

               UPDATE Toq_task_list SET recently_time = getdate() WHERE CURRENT OF check_task_cursor

           COMMIT

     END

     FETCH NEXT FROM check_task_cursor INTO @active_time，@fail_time，@block_time，@apply_days，@task_type，@old_time，@now_state

END

--- close sursor

CLOSE check_task_cursor

--- free malloc

DEALLOCATE check_task_cursor

SELECT (@send_task_mask) AS task_mask ///将@send_task_mask 

//作为标准记录集合返回


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


CREATE PROCEDURE Sp_check_toq_task

     @present_time varchar(12)，

AS

SET NOCOUNT ON

DECLARE @send_task_mask INT   ///要返回的值

DECLARE @active_time varchar(12)

DECLARE @fail_time    varchar(12)

DECLARE @block_time   int

DECLARE @apply_days   varchar(9)

DECLARE @task_type    int

DECLARE @now_state    int

DECLARE @weekofday    varchar(3) 


DECLARE @present_number_time int

DECLARE @active_number_time   int

DECLARE @fail_number_time     int

DECLARE @old_time           Datetime


--- what is day of week to today

SET @weekofday = CASE DATENAME(WEEKDAY， GETDATE())

        WHEN ’Monday’       THEN   ’1’

        WHEN ’Tuesday’      THEN   ’2’

        WHEN ’Wednesday’    THEN   ’3’

        WHEN ’Thursday’     THEN   ’4’

        WHEN ’Friday’       THEN   ’5’

        WHEN ’Saturday’     THEN   ’6’

        WHEN ’Sunday’       THEN   ’7’

        ELSE                      ’0’

        END   

--- init task mask to 0 

SET @send_task_mask = 0

--- convert string time to int time

SET @present_number_time = CONVERT(INT，RTRIM(@present_time))

--- declare cursor          

DECLARE check_task_cursor CURSOR LOCAL DYNAMIC FOR SELECT
start_time，end_time，seprate_time，affect_days，data_type，recently_time，
present_status 

        FROM Toq_task_list 

        WHERE affect_days LIKE

        ’%’ + rtrim(@weekofday) + ’%’ and present_status = 0 AND
ABS(DATEDIFF(MI，recently_time， GETDATE())) >= seprate_time   

         FOR UPDATE OF recently_time 

      

--- open recdord set  

OPEN check_task_cursor

--- fill variable

FETCH NEXT FROM check_task_cursor INTO @active_time，@fail_time，@block_time，@apply_days，@task_type，@old_time，@now_state

WHILE @@FETCH_STATUS = 0

BEGIN      

     --- above of all， convert string to number

     SET @active_number_time = CONVERT(INT，rtrim(@active_time))

     SET @fail_number_time    = CONVERT(INT，rtrim(@fail_time))

    ----- IF((@present_number_time > @active_number_time) and (@present_number_time <= @fail_number_time)) BEGIN       

     IF((@present_number_time - @active_number_time>= @block_time) and (@present_number_time <= @fail_time)) BEGIN   

           SET @send_task_mask = @send_task_mask | @task_type

           BEGIN TRANSACTION

               UPDATE Toq_task_list SET recently_time = getdate() WHERE CURRENT OF check_task_cursor

           COMMIT

     END

     FETCH NEXT FROM check_task_cursor INTO @active_time，@fail_time，@block_time，@apply_days，@task_type，@old_time，@now_state

END

--- close sursor

CLOSE check_task_cursor

--- free malloc

DEALLOCATE check_task_cursor

return (@send_task_mask) ///作为状态码返回


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


CREATE PROCEDURE Sp_check_toq_task

     @present_time varchar(12)，

     @send_task_mask    int output ///指定这个参数为返回值

AS

SET NOCOUNT ON

DECLARE @active_time varchar(12)

DECLARE @fail_time    varchar(12)

DECLARE @block_time   int

DECLARE @apply_days   varchar(9)

DECLARE @task_type    int

DECLARE @now_state    int

DECLARE @weekofday    varchar(3) 


DECLARE @present_number_time int

DECLARE @active_number_time   int

DECLARE @fail_number_time     int

DECLARE @old_time           Datetime


--- what is day of week to today

SET @weekofday = CASE DATENAME(WEEKDAY， GETDATE())

        WHEN ’Monday’       THEN   ’1’

        WHEN ’Tuesday’      THEN   ’2’

        WHEN ’Wednesday’    THEN   ’3’

        WHEN ’Thursday’     THEN   ’4’

        WHEN ’Friday’       THEN   ’5’

        WHEN ’Saturday’     THEN   ’6’

        WHEN ’Sunday’       THEN   ’7’

        ELSE                      ’0’

        END   

--- init task mask to 0 

SET @send_task_mask = 0

--- convert string time to int time

SET @present_number_time = CONVERT(INT，RTRIM(@present_time))

--- declare cursor          

DECLARE check_task_cursor CURSOR LOCAL DYNAMIC FOR SELECT
start_time，end_time，seprate_time，affect_days，data_type，recently_time，
present_status 

        FROM Toq_task_list 

        WHERE affect_days LIKE

        ’%’ + rtrim(@weekofday) + ’%’ and present_status = 0 AND
ABS(DATEDIFF(MI，recently_time， GETDATE())) >= seprate_time   

         FOR UPDATE OF recently_time 

      

--- open recdord set  

OPEN check_task_cursor

--- fill variable

FETCH NEXT FROM check_task_cursor INTO @active_time，@fail_time，@block_time，@apply_days，@task_type，@old_time，@now_state

WHILE @@FETCH_STATUS = 0

BEGIN      

     --- above of all， convert string to number

     SET @active_number_time = CONVERT(INT，rtrim(@active_time))

     SET @fail_number_time    = CONVERT(INT，rtrim(@fail_time))

    ----- IF((@present_number_time > @active_number_time) and (@present_number_time <= @fail_number_time)) BEGIN       

     IF((@present_number_time - @active_number_time>= @block_time) and (@present_number_time <= @fail_time)) BEGIN   

           SET @send_task_mask = @send_task_mask | @task_type

           BEGIN TRANSACTION

               UPDATE Toq_task_list SET recently_time = getdate() WHERE CURRENT OF check_task_cursor

           COMMIT

     END

     FETCH NEXT FROM check_task_cursor INTO @active_time，@fail_time，@block_time，@apply_days，@task_type，@old_time，@now_state

END

--- close sursor

CLOSE check_task_cursor

--- free malloc

DEALLOCATE check_task_cursor

////因为@send_task_mask有了OUTPUT的定义，它将自动返回给客户
////端，所以它不需要任何的返回指令

显然返回数据的三种方法，有各自的优点和缺点
SELECT 对资源的开销比较大，因为它将数据作为记录集合而返回，
它最大的优点是客户端可以常规的方便的取出数据。
RETURN 对资源的开销比较小，但它限定了数据类型为整形，适应面
不广，而且客户端要做一些特殊判断才能取出数据。
OUTPUT 对资源的开销比较小，适应各种数据类型，而且服务端不需要做数据类型转换，减少了服务端的负担。它唯一的缺点是要多做一些特殊判断才能取出数据。

下面给出了在DB-LIBRARY中分别对应的取数据用到的函数：

1。SELECT：DBBIND

    2。RETURN：DBHASRETSTAT

    3。OUTPUT：DBNUMREC、DBRETDATA