Winform开发框架之存储过程的支持--存储过程的实现和演化提炼(1)

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介:

在我前面很多篇关于框架设计和介绍的文章里面,大多数都是利用框架提供的基础性API进行各种的操作,包括增删改查、分页等各种实现和其衍生的实现,而这些实现绝大多数是基于SQL的标准操作实现的,由于框架的底层是利用了微软企业库Enterprise Library,因此框架也是很好的支持存储过程的各种调用,不过由于整体性和数据库迁移方面的考虑,建议一般使用标准的SQL操作而已,这样能够很大程度上保证数据库可以很平滑过渡到其他数据库,如Access、SQLite等单机版数据库。但是,有时候我们提供对存储过程的支持也是十分必要的,有些业务可能就只是固定在某种特定的数据库上跑,如SQLServer、Oracle等这些支持存储过程的关系型数据库,有些业务可能还真的需要存储过程的整体性的封装;基于这个原因,我撰写了这篇文章,力求从较为全面的角度上阐述存储过程的编写、实现和演化提炼方面做一个介绍。

 1、SQLServer存储过程的编写

虽然存储过程一般用于处理一些复杂的逻辑关系或者报表内容,不过为了介绍方便,我们从几个较为基础的操作进行介绍。

我们以一个客户表来进行对应的存储过程来介绍,先介绍客户表T_Customer的表定义。

它的SQLServer脚本如下所示

create table dbo.T_Customer (
   ID                   nvarchar(50)         not null,
   Name                 nvarchar(50)         null,
   Age                  int                  null,
   Creator              nvarchar(50)         null,
   CreateTime           datetime             null,
   constraint PK_T_CUSTOMER primary key (ID)
)

为了介绍存储过程的编写,我们以这个表的相关操作的存储过程来进行介绍,存储过程一般可以分为下面几种情况。

1)提供执行处理,可对执行结果进行反馈

 这种情况常常可以见到,如可以对插入、更新、删除等操作进行处理,并获得执行的结果,下面是这两种存储过程的代码。

------------------------------------
--作者:伍华聪 http://wuhuacong.cnblogs.com
--创建时间:2014年11月27日 
--功能描述:插入数据到表中 
------------------------------------
 CREATE PROCEDURE dbo.T_Customer_Insert 
 ( 
      @ID varchar(50),
      @Name varchar(50) ,
      @Age int 
 ) 
 AS 
 begin tran 
 Insert into dbo.T_Customer( ID,Name,Age ) Values( @ID,@Name,@Age ) 
 if @@error!=0 
     begin 
         rollback 
     end 
 else 
     begin 
         commit 
     end 
 go 
 
------------------------------------
--作者:伍华聪 http://wuhuacong.cnblogs.com
--创建时间:2014年11月27日 
--功能描述:以字段ID为关键字,修改表中的数据 
------------------------------------
 CREATE PROCEDURE dbo.T_Customer_UpdateByID 
 ( 
      @ID varchar(50),
      @Name varchar(50) ,
      @Age int 
 ) 
 AS 
 begin tran 
 Update dbo.T_Customer Set Name=@Name,Age=@Age Where ID= @ID 
 if @@error!=0 
     begin 
         rollback 
     end 
 else 
     begin 
         commit 
     end 
 go 
------------------------------------
--作者:伍华聪 http://wuhuacong.cnblogs.com
--创建时间:2014年11月27日 
--功能描述:以字段ID为关键字,删除表的记录 
------------------------------------
 CREATE PROCEDURE dbo.T_Customer_DeleteByID 
 ( 
      @ID varchar(50)
 ) 
 AS 
 begin tran 
 Delete From dbo.T_Customer where ID=@ID 
 if @@error!=0 
     begin 
         rollback 
     end 
 else 
     begin 
         commit 
     end 
 go 

2)提供执行处理,获得一个或者多个返回性参数,并可对执行结果进行反馈。

 基于上面的处理方式,我们可能还有一种情况,就是需要执行存储过程个,并返回对应的返回参数,我们可以在程序里面利用代码获取这些返回参数的数值,从而用作其他用途。

因此,这种操作,如要是获取返回性参数的情况,如下所示是判断记录是否存在,以及获取客户最大年龄的两个存储过程。

------------------------------------
--作者:伍华聪 http://wuhuacong.cnblogs.com
--创建时间:2014年11月27日 
--功能描述:以字段ID为关键字,检查表中是否存在符合条件的记录 
------------------------------------
 CREATE PROCEDURE dbo.T_Customer_ExistByID 
 ( 
     @Exist int output , 
      @ID varchar(50)
 ) 
 AS 
 Select @Exist = Case When Exists (Select 1 From dbo.T_Customer Where ID=@ID) Then 1 Else 0 End 
 go 

------------------------------------
--作者:伍华聪 http://wuhuacong.cnblogs.com
--创建时间:2014年11月27日 
--功能描述:获取客户最大年龄
------------------------------------
 CREATE PROCEDURE dbo.T_Customer_MaxAge
( @MaxAge int output )
 AS 
 Select @MaxAge=Case When Max(Age) is NULL Then 0 Else Max(Age) End  From dbo.T_Customer 
 go 

3)提供查询处理,并返回实体对象

这小节后面介绍的内容,都是存储过程的返回值,这些或者是一条记录,或者是多条记录的查询结果,这个在SQLServer里面很容易实现,而在Oracle里面需要通过游标进行处理。

下面存储过程脚本,是基于返回单条记录的存储过程。

------------------------------------
--作者:伍华聪 http://wuhuacong.cnblogs.com
--创建时间:2014年11月27日 
--功能描述:以字段ID为关键字,检索表中的数据 
------------------------------------
 CREATE PROCEDURE dbo.T_Customer_SelectByID 
 ( 
      @ID varchar(50)
 ) 
 AS 
 Select * from dbo.T_Customer Where ID= @ID 
 go 

4)提供查询处理,并返回多条记录集合;包括实体列表集合或DataTable集合对象

 对于返回多条集合的对象,在存储过程里面体现都一样的,我们可能在C#处理的时候,把它转换为不同的对象即可,返回多个集合,在SQLServer里面,它们的存储过程代码如下所示。

------------------------------------
--作者:伍华聪 http://wuhuacong.cnblogs.com
--创建时间:2014年11月27日 
--功能描述:检索表中所有的数据 
------------------------------------
 CREATE PROCEDURE dbo.T_Customer_SelectAll 
 AS 
 Select * from dbo.T_Customer 
 go 

2、Oracle存储过程的编写

对应客户表T_Customer,Oracle的创建脚本如下所示。

CREATE TABLE T_CUSTOMER ( 
    ID        VARCHAR2(100),
    NAME    VARCHAR2(50)     NOT NULL ,
    AGE        INTEGER         NOT NULL,
    CREATOR    VARCHAR2(50)    NULL,
    CREATETIME    DATE         DEFAULT SYSDATE,
);

ALTER TABLE T_CUSTOMER ADD CONSTRAINT PK_T_CUSTOMER PRIMARY KEY (ID);

对应SQLServer的存储过程,Oracle的存储过程也提供了对应的版本,下面是几种情况下的Oracle存储过程的编写。

1)提供执行处理,可对执行结果进行反馈

------------------------------------
--作者:伍华聪 http://wuhuacong.cnblogs.com
--创建时间:2014年11月27日 
--功能描述:插入数据到表中 
------------------------------------
 Create Or Replace Procedure T_Customer_Insert 
 ( 
     p_ID IN T_CUSTOMER.ID%TYPE,
     p_Name IN T_CUSTOMER.NAME%TYPE,
     p_Age IN T_CUSTOMER.AGE%TYPE
 ) 
 AS 
 Begin 
 Insert into T_CUSTOMER( ID,NAME,AGE ) Values( p_ID,p_Name,p_Age ) ;
 Commit; 
 Exception 
     When Others Then 
 Rollback; 

 End; 
 / 
------------------------------------
--作者:伍华聪 http://wuhuacong.cnblogs.com
--创建时间:2014年11月27日 
--功能描述:以字段ID为关键字,修改表中的数据 
------------------------------------
 Create Or Replace Procedure T_Customer_UpdateByID 
 ( 
     p_ID IN T_CUSTOMER.ID%TYPE,
     p_Name IN T_CUSTOMER.NAME%TYPE,
     p_Age IN T_CUSTOMER.AGE%TYPE
 ) 
 AS 
 Begin 
 Update T_CUSTOMER Set NAME=p_Name,AGE=p_Age Where ID= p_ID ;
 Commit; 
 Exception 
     When Others Then 
 Rollback; 

 End; 
 / 
------------------------------------
--作者:伍华聪 http://wuhuacong.cnblogs.com
--创建时间:2014年11月27日 
--功能描述:以字段ID为关键字,删除表的记录 
------------------------------------
 Create Or Replace Procedure T_Customer_DeleteByID 
 ( 
      p_ID IN T_CUSTOMER.ID%TYPE 
 ) 
 AS 
 Begin 
 Delete From T_CUSTOMER where ID=p_ID ;
 Commit; 
 Exception 
     When Others Then 
 Rollback; 

 End; 
 / 

其中上面的代码涉及几个地方,T_CUSTOMER.ID%TYPE是表示根据字段动态决定参数的类型,避免应硬编码或者反复修改参数类型。

Oracle的参数一般使用p_的前缀开始,方便区分。

2)提供执行处理,获得一个或者多个返回性参数,并可对执行结果进行反馈。

------------------------------------
--作者:伍华聪 http://wuhuacong.cnblogs.com
--创建时间:2014年11月27日 
--功能描述:以字段ID为关键字,检查表中是否存在符合条件的记录 
------------------------------------
 Create Or Replace Procedure T_Customer_ExistByID 
 ( 
     p_Exist OUT Number  ,
     p_ID IN T_CUSTOMER.ID%TYPE 
 ) 
 AS 
 Begin 
 --V9.i以下使用的语句 
 Select Case When (Count(1)>0) Then 1 Else 0 End Into p_Exist From T_CUSTOMER Where ID=p_ID ;
 --也可以使用的语句 
 -- Select Decode(Count(1),0,0,1) Into p_Exist From T_CUSTOMER Where ID=p_ID ;
 End; 
 / 

------------------------------------
--作者:伍华聪 http://wuhuacong.cnblogs.com
--创建时间:2014年11月27日 
--功能描述:获取表用来标识字段的最大ID值,在标识ID非自增字段时可用于数据插入时调用 
------------------------------------
 Create Or Replace Procedure T_Customer_MaxAge 
 ( 
     p_MaxAge OUT Number 
 ) 
 AS 
 Begin 
 Select Decode(Max(Age) ,NULL,0,Max(Age)) Into p_MaxAge From T_CUSTOMER; 
 End; 
 / 

上面的代码,都有一个输出的参数,虽然他们执行没有影响记录函数,但是这个主要是通过输出参数的值进行处理了。

3)提供查询处理,并返回实体对象

 提供查询处理,不管返回一条记录,还是多条记录,在Oracle里面,一般都是通过游标进行处理的,因此我们需要先定义一个游标类型,供我们返回记录使用的。

下面定义一个游标的包代码如下。

------------------------------------
--作者:伍华聪 http://wuhuacong.cnblogs.com
--创建时间:2014年11月27日 
--功能描述:创建一个包,含有一个游标类型:(一个数据库中只需声明一次)  
------------------------------------
 CREATE OR REPLACE PACKAGE MyCURSOR 
 AS 
     TYPE cur_OUT IS REF CURSOR; 
 End; 
 / 

然后我们就可以在各个返回记录的存储过程里面使用这个游标类型了。

例如在下面的存储过程里面,返回一条指定的数据记录,那么输出参数里面需要有一个游标的定义参数,但是我们在C#里面使用数据访问框架来处理数据的时候,可以忽略他它的存在,就只需要输入p_ID参数就可以了。

------------------------------------
--作者:伍华聪 http://wuhuacong.cnblogs.com
--创建时间:2014年11月27日 
--功能描述:以字段ID为关键字,检索表中的数据 
------------------------------------
 Create Or Replace Procedure T_Customer_SelectByID 
 ( 
     cur_OUT OUT MyCURSOR.cur_OUT  ,
     p_ID IN T_CUSTOMER.ID%TYPE 
 ) 
 AS 
 Begin 
 OPEN cur_OUT FOR Select * from T_CUSTOMER Where ID = p_ID ; 
 End; 
 / 

4)提供查询处理,并返回多条记录集合;包括实体列表集合或DataTable集合对象

和上面返回单条记录一样,需要返回多条记录的存储过程,也需要使用一个游标的输出参数来获取返回的记录,并可以对游标进行处理。

------------------------------------
--作者:伍华聪 http://wuhuacong.cnblogs.com
--创建时间:2014年11月27日 
--功能描述:检索表中所有的数据 
------------------------------------
 Create Or Replace Procedure T_Customer_SelectAll 
 ( cur_OUT OUT MyCURSOR.cur_OUT ) 
 AS 
 Begin 
 OPEN cur_OUT FOR Select * from T_CUSTOMER; 
 End; 
 / 

最后,我们看看SQLServer和Oracle数据库的脚本完整情况。

SQLServer存储过程代码:

  View Code

Oracle存储过程代码:

  View Code

以上就是存储过程编写过程中的处理和对比,下一篇将继续介绍这个主体,并针对性的介绍如何在C#底层数据访问里面,对这些存储过程的使用。

本系列两篇文章,列表如下:

Winform开发框架之存储过程的支持--存储过程的实现和演化提炼(1)

Winform开发框架之存储过程的支持--存储过程的实现和演化提炼(2)

本文转自博客园伍华聪的博客,原文链接:Winform开发框架之存储过程的支持--存储过程的实现和演化提炼(1),如需转载请自行联系原博主。



相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
2月前
|
存储 NoSQL JavaScript
MongoDB存储过程实战:聚合框架、脚本、最佳实践,一文全掌握!
【8月更文挑战第24天】MongoDB是一款备受欢迎的文档型NoSQL数据库,以灵活的数据模型和强大功能著称。尽管其存储过程支持不如传统关系型数据库,本文深入探讨了MongoDB在此方面的最佳实践。包括利用聚合框架处理复杂业务逻辑、封装业务逻辑提高复用性、运用JavaScript脚本实现类似存储过程的功能以及考虑集成其他工具提升数据处理能力。通过示例代码展示如何创建订单处理集合并定义验证规则,虽未直接实现存储过程,但有效地演示了如何借助JavaScript脚本处理业务逻辑,为开发者提供更多实用指导。
36 2
|
5月前
|
存储 Java 数据库
JAVAEE框架数据库技术之13_oracle 之PLSQL技术及存储过程和函数(二)
JAVAEE框架数据库技术之13_oracle 之PLSQL技术及存储过程和函数
73 0
|
5月前
|
存储 SQL Java
JAVAEE框架数据库技术之13_oracle 之PLSQL技术及存储过程和函数(一)
JAVAEE框架数据库技术之13_oracle 之PLSQL技术及存储过程和函数
57 0
|
存储 关系型数据库 Shell
使用 Rust 开发 PostgreSQL 存储过程
pgxr 使用 Rust 来编写 PostgreSQL 的扩展函数(相当于存储过程)。 项目地址:https://github.com/clia/pgxr 使用这个星球上最快的、高效、安全、有趣的编程语言,来为世界上功能最强大的开源关系数据库编写库内的程序! 试想,当你从数据库中查询出 1000 条记录用于程序处理时,当你的程序是数据库内的程序时,你根本无需将这 1000 条结果通过 PostgreSQL 的通讯协议走网络传输到应用程序里,在应用程序里分配这么大一块内存来装这些数据,再来进行处理。
7305 0
|
存储 关系型数据库 PostgreSQL
下一篇
无影云桌面