存储过程

1 ** 判断是否有相应记录 return 0

set @n = (select 1 from store_pos where pos_serial_no =@posSerialNo )

        if   @n   is   null     

        print   '没有记录！'  

  else  

        print   '有记录！'

 

2 **判断是否有相应记录 return 结果集

select * from store_pos where pos_serial_no =@posSerialNo

        if   @@rowcount=0   --系统常量@@rowcount，会返回

        print   '没有记录！'  

  else  

        print   '有记录！'

 

3 ** 存储过程创建

create   proc   过程名  
  as  
  begin  
  create   table   #临时表名{field1   type,field2   type}  
  end  
  或  
  select   *   into   #临时表名   from   table

 

4 ** 创建临时表

Create   proc   [dbo].[test_create] 

@posSerialNo bigint,  --变量之间用逗号隔开

@count int

as  

declare @n int

declare @firstNo int

set @firstNo=0

--创建临时表

begin  

     create table  #tab1 (store_pdt_name varchar(30),pos_serial_no varchar(20), base_serial_no varchar(20),pin_serial_no varchar(20),input_date datetime)

 

--循环查找，符合条件的插入临时表

       while(@firstNo<@count)

begin

       SELECT @n=1 from store_pos where pos_serial_no =@posSerialNo

        if   @n   is  not  null     

        insert into #tab1 select spi.store_pdt_name ,sp.pos_serial_no ,sp.base_serial_no,sp.pin_serial_no,sp.input_date

                    from store_pos sp , store_in si , store_pdt_info spi

                           where sp.store_in_id = si.store_in_id and si.store_pdt_id=spi.store_pdt_id

                                  and  pos_serial_no =@posSerialNo

      set @posSerialNo=@posSerialNo+1

       set @firstNo=@firstNo+1

end

 select * from #tab1

end

 

5 事务处理

ALTER PROCEDURE [dbo].[test_TRANSACTION]

AS

declare @date datetime

set @date = getdate()

begin

BEGIN tran

       SET NOCOUNT ON;

       insert into store_pos(store_in_id,pos_serial_no,base_serial_no,pin_serial_no,input_date) values(1,1,1,1,@date);

       insert into store_pos(store_in_id,pos_serial_no,base_serial_no,pin_serial_no,input_date) values(1,1,1,1,@date);

       IF @@ERROR <> 0

       BEGIN

        ROLLBACK TRAN

        print 'wrong'

    END

       else

       BEGIN

        commit TRAN

        print 'ok'

    END

END

 

** 6 存储过程的三种返回值

           OUPUT参数返回值  2 RETURN过程返回值  3.SELECT 数据集返回值

cstmt = conn.prepareCall("{?=call test_cursor(?)}");

           cstmt.registerOutParameter(1,java.sql.Types.BIGINT);//返回 return的值

           cstmt.registerOutParameter(2,java.sql.Types.VARCHAR);//返回output的值

           cstmt.execute();

           //long intRet = cstmt.getLong(1);//

           cstmt.getMoreResults();

           int strOut = cstmt.getInt(1);   //100

           String strOut2 = cstmt.getString(2);   //123

 

alter PROCEDURE test_cursor

@value varchar(20) output  //返回output的值

AS

BEGIN

    SET NOCOUNT ON;

     set @value=123

return 100

END

GO

（一）、参数返回值@parm2；
（二）、过程返回值return 100；
（三）、数据集返回值select * from table1 where age = @parm1

 

3、返回多重结果
　　JDBC 2 规范的一个局限是，在任意时刻，返回多重结果的语句只能打开一个ResultSet。作为 JDBC 3.0 规范中改变的一个部分，规范将允许 Statement 接口支持多重打开的 ResultSets。然而，重要的是 execute() 方法仍然会关闭任何以前 execute() 调用中打开的 ResultSet。所以，要支持多重打开的结果，Statement 接口就要加上一个重载的 getMoreResults() 方法。新式的方法会做一个整数标记，在 getResultSet() 方法被调用时指定前一次打开的 ResultSet 的行为。接口将按如下所示定义标记：

CLOSE_ALL_RESULTS：当调用 getMoreResults() 时，所有以前打开的 ResultSet 对象都将被关闭。CLOSE_CURRENT_RESULT：当调用 getMoreResults() 时，当前的 ResultSet 对象将被关闭。KEEP_CURRENT_RESULT：当调用 getMoreResults() 时，当前的 ResultSet 对象将不会被关闭。

conn = (Connection) pool.requestObject();

                 cstmt = conn.prepareCall("{call prcd_newAccountQRY(?,?)}");

                 cstmt.setString(1, _MerchantNo);

                 cstmt.registerOutParameter(2,java.sql.Types.BIGINT);//返回记录数量

                 rs = cstmt.executeQuery();  //第一个结果

                 cstmt.getMoreResults(Statement.KEEP_CURRENT_RESULT);//第二个结果

                

                 long recordCount = cstmt.getLong(2);

while (rs.next())

 

执行带嵌入参数的sql——sp_executesql

declare @sql_str nvarchar(1000) --数据类型要修改为unicode类型

set @sql_str = 'set @temp = (select  count(*)'+@sql_str+')'

exec sp_executesql @sql_str,N'@temp bigint out',@countValue out

 

execute 对拼成的字符串SQL是有长度限制的

sp_executesql对参数类型有限制 Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'。这句话很明显说明只支持以上类型。

 

 

** 7 游标 cursor

定义一个游标

1．DECLARE authors_cursor CURSOR  FOR select pos_serial_no from store_pos

2．DECLARE @MyCursor CURSOR

SET @MyCursor = CURSOR LOCAL SCROLL FOR

SELECT * FROM titles

字符转换函数

STR()
把数值型数据转换为字符型数据。
STR (<float_expression>[，length[， <decimal>]])
length 指定返回的字符串的长度，decimal 指定返回的小数位数。如果没有指定长度，缺省的length 值为10， decimal 缺省值为0。
当length 或者decimal 为负值时，返回NULL；
当length 小于小数点左边（包括符号位）的位数时，返回length 个*；
先服从length ，再取decimal ；
当返回的字符串位数小于length ，左边补足空格。

8, case

update #temp_result

set add_money =

case

 when add_money is null then 0

 else add_money

end,

consume_money =

case

 when consume_money is null then 0

 else consume_money

end