编程

当前位置:永利皇宫463登录 > 编程 > 至于存款和储蓄过程的某个文化永利皇宫463登录

至于存款和储蓄过程的某个文化永利皇宫463登录

来源:http://www.makebuLuo.com 作者:永利皇宫463登录 时间:2019-09-21 08:48

  今天需要进行百万测试数据的添加 ,虽然网上资料很多, 但是其实我并没有看懂 ,因为从来没有接触过自动化测试。但无论多么复杂的事情,总会有简单的方法完成。存储过程(

/*====================================================
*描述:            存储过程知识点总结,以Northwind数据库的Employees表为例
======================================================*/

/*==========================================================
*描述: 存储过程知识点总结,以Northwind数据库的Employees表为例

  declare

--========================1.没有参数的存储过程================
create procedure usp_NoParameterSelect
as
begin
select * from dbo.Employees
end

===========================================================*/

*    i number;
    j number;
    k number;
  begin
    for i in 1 .. 9 loop
      for j in 1 .. 9 loop
        for k in 1 .. 99 loop
          insert into EDIT_MST(SHIFT_TYPE,SHIFT,ROUND_NO,S_TIME,E_TIME)*

GO

--=========================1.out输出/输出的存储过程==================

*          values( i , j , k ,'3:00' ,'3:50');
        end loop;
      end loop;
    end loop;
   end;
commit; /*自动提交事务*/*

--========================执行测试========================
EXECUTE usp_NoParameterSelect

create procedure usp_OutParameterSelect
  @employeeID int,
  @name nvarchar(10) out, --**即作为输入,又作为输出**
  @lastName nvarchar(20) out --**out与output在这里通用**
as
begin
  select
    @name=FirstName --**重新赋值,作为输出**
    ,@lastName=LastName
  from dbo.Employees
  where EmployeeID = @employeeID
  and City = @name --**输入参数查询**
end

GO
--=======================2.带参数的存储过程===================

GO

这种写法比较简单 但是它在多个字段时候 处主键外 其他的没有要求的字段 都直接给了常量 对性能测试方面没有任何的作用。

create procedure usp_ParameterSelect
(
@employeeID INT
)
as

--===========================执行测试=======================

select count fromEDIT_MST;-------查询EDIT_MST 表中有多少条数据

begin
select * from dbo.Employees
where EmployeeID = @employeeID
end

declare @employeeID int
,@name nvarchar(10)
,@lastName nvarchar(20)

selectSHIFT_TYPE,SHIFT,ROUND_NO,S_TIME,E_TIME fromEDIT_MST; ------------查询所有字段**

--=======================执行测试===========================
EXECUTE usp_ParameterSelect 1

set @employeeID = 6
set @name = 'London'

delect fromEDIT_MST whereSHIFT_TYPE=‘2’;-----删除所有**SHIFT_TYPE字段为2的数据**

GO
--=======================3.带多个参数的存储过程==================

execute usp_OutParameterSelect @employeeID,@name output,@lastName output

*delect fromEDIT_MST whereROUND_NO like ‘%5%’;------删除所有***ROUND_NO字段中含有5的数据;**

create procedure usp_MultiParameterSelect
@employeeID INT,
@employeeFirstName nvarchar(10)--要加上参数类型的长度
as
begin
select * from dbo.Employees
where EmployeeID = @employeeID
and FirstName = @employeeFirstName
end

select @name as FirstName,@lastName as LastName

**以后会将工作中的问题 以及解决方案记录下来 期望今年可以更好的提高自己。路漫漫其修远兮,吾将上下而求索。**

--========================执行测试===========================
EXECUTE usp_MultiParameterSelect 1,'Nancy'

GO

GO

--=========================2.异常处理的存储过程=================

--=======================4.output输出存储过程====================
create procedure usp_OutputSelect
@employeeID int ,
@employeeFirstName nvarchar(10) output
as
begin
select @employeeFirstName = FirstName from Employees
where EmployeeID = @employeeID
end

create procedure usp_ExceptionHandling

--=======================执行测试=============================
declare @name nvarchar(10)
execute usp_OutputSelect 1,@name output
select @name as name
GO

as

--======================5.return输出存储过程======================

begin
  begin try
    select 1/0 --**除数为零**
  end try

create procedure usp_ReturnSelect
@employeeFirstName nvarchar(10)
AS
begin
declare @employeeID INT
select @employeeID = EmployeeID from Employees
where FirstName = @employeeFirstName

  begin catch
    if @@ERROR <> 0
      declare @errorMessage nvarchar(4000)
          ,@errorSeverity int
          ,@errorState int

return @employeeID --**返回值必须是INT类型**
end

      select @errorMessage = ERROR_MESSAGE() --**错误的信息**
          ,@errorSeverity = ERROR_SEVERITY() --***错误的严重级别*
          ,@errorState = ERROR_STATE() --**错误的状态**

GO

      /*抛出一个异常*/
      raiserror (@errorMessage,@errorSeverity,@errorState)
  end catch
end

--======================执行测试===============================
declare @employeeID INT
execute @employeeID = usp_ReturnSelect 'Nancy'
print @employeeID

GO

GO

--===========================执行测试==========================

--=====================6.同时有output和return输出的存储过程=============

execute usp_ExceptionHandling

create procedure usp_OutputAndReturnSelect
@firstName nvarchar(10),
@lastName nvarchar(20) output
as
begin
declare @employeeID INT;

--执行结果如下:
/*
Msg 50000, Level 16, State 1, Procedure usp_ExceptionHandling, Line 17
Divide by zero error encountered.
*/
GO
--=========================3.事物处理的存储过程===================

select @employeeID = EmployeeID,@lastName=LastName from Employees
where FirstName = @firstName

create procedure usp_Transaction

return @employeeID
end

as
begin
  begin try

GO

  SET XACT_ABORT ON
  /*
  *当SET XACT_ABORT为ON 时,如果Transact-SQL语句产生运行时错误,事务终止并回滚.
  *为OFF 时,只回滚产生错误的语句.而事务继续处理.
  */

--======================执行测试================================
declare @employeeID INT
declare @lastName nvarchar(20)
execute @employeeID = usp_OutputAndReturnSelect 'Nancy',@lastName output
select @employeeID,@lastName

  begin transaction

/*===========================================================
*****************************未完待续***************************************
*============================================================*/

  --**这条跟新语句执行时会出现异常,FirstName被定义为Not Null**
  update dbo.Employees set FirstName = NULL
  where EmployeeID = 1

  update dbo.Employees set FirstName = FirstName + 'XXX'
  where City = 'London'

  commit transaction

  end try

  begin catch
    if @@TRANCOUNT > 0

      rollback transaction --**事物回滚**

    declare @errorMessage nvarchar(4000)
        ,@errorSeverity int
        ,@errorState int

    select @errorMessage = ERROR_MESSAGE() --**错误的信息**
        ,@errorSeverity = ERROR_SEVERITY() --***错误的严重级别*
        ,@errorState = ERROR_STATE() --**错误的状态**

    /*抛出一个异常*/
    raiserror (@errorMessage,@errorSeverity,@errorState)
end catch

end

--===========================执行测试==============================

execute usp_Transaction

/*==============================================================
*********************************End*****************************************
*==============================================================*/

本文由永利皇宫463登录发布于编程,转载请注明出处:至于存款和储蓄过程的某个文化永利皇宫463登录

关键词: