SYBASE ASE12.5中存储过程的建立和调用

最新推荐文章于 2021-02-24 11:27:41 发布

原创最新推荐文章于 2021-02-24 11:27:41 发布 · 372 阅读

0 ·

CC 4.0 BY-SA版权

数据库_Sybase 专栏收录该内容

30 篇文章

订阅专栏

本文详细介绍了如何在数据库中创建三个SQL存储过程，分别用于获取短信报告展示、发送短信以及分组发送短信。通过这些存储过程，可以有效地管理和操作短信数据，包括获取特定会话的短信接收者列表、发送短信以及批量发送短信到多个接收者。

一、建立存储过程（三个存储过程：up_sendgroup2->up_sendsms_ex2->up_getsmsrepdisplay）

1、建立存储过程up_getsmsrepdisplay

CREATE PROCEDURE up_getsmsrepdisplay(    
@i_sessionid         varchar(100),    
@o_displayall         varchar(100) output    
)    
AS      
BEGIN    
declare @displayone varchar(100)     
declare @counter        int    
    
set @counter=1    
declare cur_smsdisplay cursor for    
select displayname from ncomsmsreptemp    
where sessionid=@i_sessionid    
    
open cur_smsdisplay     
fetch cur_smsdisplay into @displayone     
while(@@sqlstatus=0 and @counter<=3)     
begin     
   set @displayone=ltrim(rtrim(@displayone))    
   if(@counter=1)    
    set @o_displayall=@displayone    
   else    
    set @o_displayall=@o_displayall+','+@displayone    
    
   set @counter=@counter+1    

   fetch cur_smsdisplay into @displayone     
end     
close cur_smsdisplay 
    
if @counter>4    
   set @o_displayall=@o_displayall+'...'    
END

2、建立存储过程up_sendsms_ex2

CREATE PROCEDURE [dbo].[up_sendsms_ex2]       
-- Add the parameters for the stored procedure here      
@sessionid       Varchar(100),     -- ncomsmsreptemp.sessionid      
@MsgText     Varchar(1024),    -- Example:This is Test Message!      
        @Priority    Int               -- Values: 1=LOW 2=Normal 3=HIGH      
AS      
BEGIN      
declare @SMSID      varchar(100)      
declare @TextSize   int      
       Declare @AccountID Varchar(100)      
DECLARE @displayallname varchar(100)     
         
    --Check SP PASS IN Arguments & Set it's to Default:      
    If (@Priority<>1 and @Priority<>2 and @Priority<>3)      
       SET @Priority=2         --Default Priority is Normal(2)       
      
    --Prepare Some Fields' Values:      
    SET @SMSID=NewID() -- GET NEW GUID      
    SET @TextSize=len(@MsgText)      
    SET @AccountID='sysmobile' --SET in Table 'nComSMSAccount',Must be Exists!!      
      
   --GET DISPLAYALLNAME     
   set @displayallname=''     
   execute up_getsmsrepdisplay @i_sessionid=@sessionid,@o_displayall=@displayallname   output 
     
    --Add SMS Master Information:      
    INSERT INTO ncomsms      
              (smsid,      
               smstype,      
               direction,      
               accountid,      
               fromdisplay,      
               owner,      
               priority,      
               usetemplate,      
               templateid,      
               msgtype,      
               msgtext,      
               pushurl,      
               msgsize,      
               todisplay,      
               boxtype,      
               pirorboxtype,      
               writetime,      
               sendstate,      
               retrycount,      
               sendresult       
              )      
      SELECT @SMSID,      
             1,           --SMSType: 1=Normal SMS 2=Flash SMS 3=WAPPUSH (Must set pushurl)      
             2,           --Direction: 1=Receive 2=SEND OUT      
             @AccountID, --Sender's Account ID      
             ncomsmsaccount.displayname,      
             ncomsmsaccount.childgatecode,      
             @Priority,      
             0,           --Use template? 0=Not USE 1=USE a Template      
             -1,          --template id      
             0,           --MsgType 0=AutoSelect 1=English 2:Chinese      
             @MsgText,      
             '',          --Not Use WAPPUSH      
             @TextSize,      
             @displayallname,     
             2,           -- BoXType: 1=WRITING 2=SENDING 3=SEND 4=RECEIVED 5=DELETED      
             NULL,        --Piror Box Type      
             getdate(),   --WriteTime       
             0,           --Send State 0=Is Ready 1=In Sending Queue      
             0,           --Retry Count      
             0            --Send Result -1:Failed 0:NO_SET 1:SUCCESS 2:Partly SUCCESS      
      FROM ncomsmsaccount      
      where accountid=@AccountID      
     if @@error <>0 return -1      
      
     Return 1 --Return SUCCESS      
END

3、建立存储过up_sendgroup2

CREATE PROCEDURE up_sendgroup2       
-- Add the parameters for the stored procedure here      
AS      
BEGIN      
declare @msgtext varchar(1024)     
declare @sessionid    varchar(100)      
     
SET @sessionid    =NewID() -- GET NEW GUID      
SET @msgtext =‘存储过程测试'+CAST(GETDATE() as varchar(30))    
     
   exec dbo.up_sendsms_ex2 @sessionid,@msgtext,2     
     
END

调用存储过程up_getsmsrepdisplay如下，这是有输入参数和返回参数的情况：

declare @sessionid varchar(100) 
declare @displayall varchar(100) 
select @sessionid ="1001" 
execute up_getsmsrepdisplay @i_sessionid=@sessionid ,@o_displayall=@displayall output
select @displayall


如果没有参数，直接使用execute执行即可，比如要执行up_sendgroup2就可以直接使用

execute up_sendgroup2