SQLSERVER和ORACLE存储过程的简单实现

wuchangjian2021-11-16 08:41:42编程学习

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

在我前面很多篇关于框架设计和介绍的文章里面,大多数都是利用框架提供的基础性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存储过程代码:

复制代码
------------------------------------
--作者:伍华聪 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日
–功能描述:检索表中所有的数据
–----------------------------------
CREATE PROCEDURE dbo.T_Customer_SelectAll
AS
Select * from dbo.T_Customer
go

–----------------------------------
–作者:伍华聪 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

–----------------------------------
–作者:伍华聪 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日
–功能描述:以字段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

–----------------------------------
–作者:伍华聪 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

复制代码
View Code

 

Oracle存储过程代码:

复制代码
------------------------------------
--作者:伍华聪 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日
–功能描述:创建一个包,含有一个游标类型:(一个数据库中只需声明一次)
–----------------------------------
CREATE OR REPLACE PACKAGE MyCURSOR
AS
TYPE cur_OUT IS REF CURSOR;
End;
/
–----------------------------------
–作者:伍华聪 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;
/
–----------------------------------
–作者:伍华聪 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;
/
–----------------------------------
–作者:伍华聪 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 ;
–V8.i及以下使用的语句
– 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为关键字,删除表的记录
–----------------------------------
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;
/
–----------------------------------
–作者:伍华聪 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;
/

复制代码
View Code

 

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

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

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

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

主要研究技术:代码生成工具、会员管理系统、客户关系管理软件、病人资料管理软件、Visio二次开发、酒店管理系统、仓库管理系统等共享软件开发
专注于Winform开发框架/混合式开发框架、Web开发框架、Bootstrap开发框架、微信门户开发框架的研究及应用。
  转载请注明出处:
撰写人:伍华聪  http://www.iqidi.com 
    
标签: Winform开发框架
<div id="blog_post_info">
好文要顶 关注我 收藏该文
伍华聪
关注 - 83
粉丝 - 9018
+加关注
2
1
<div class="clear"></div>
<div id="post_next_prev">

<a href="https://www.cnblogs.com/wuhuacong/p/4109833.html" class="p_n_p_prefix">« </a> 上一篇:    <a href="https://www.cnblogs.com/wuhuacong/p/4109833.html" title="发布于 2014-11-20 09:39">基于MVC4+EasyUI的Web开发框架经验总结(15)--在MVC项目中使用RDLC报表</a>
<br>
<a href="https://www.cnblogs.com/wuhuacong/p/4130525.html" class="p_n_p_prefix">» </a> 下一篇:    <a href="https://www.cnblogs.com/wuhuacong/p/4130525.html" title="发布于 2014-11-29 17:27">Winform开发框架之存储过程的支持--存储过程的实现和演化提炼(2)</a>

posted on 2014-11-29 11:54  伍华聪  阅读(5759)  评论(3)  编辑  收藏  举报

相关文章

面试复盘|杭州-蚂蚁金服-CTO-Java研发

序言 参加完一场惨烈的秋招战斗,最终签约蚂蚁,现来总结秋招...

C语言:斐波那契数列解决兔子问题

斐波那契数列:每一项之和等于前两项之和 接下来我们用C语言解决 #in...

台风“马鞍”25日登陆,中国气象局启动台风四级应急响应

台风“马鞍”25日登陆,中国气象局启动台风四级应急响应

2022-08-23 13:55:09 据中国气象局消息,中央气象台于...

Java实习生是做什么的?大厂对实习生的要求是什么?本文带你一探究竟

Java实习生是做什么的?大厂对实习生的要求是什么?本文带你一探究竟

很多计算机专业毕业的学生,看到各个公司招聘Java实习生,有...

“国际张”的野心藏不住了!不只是吸睛,天门山将把更酷的一面给你看

“国际张”的野心藏不住了!不只是吸睛,天门山将把更酷的一面给你看

2022-08-19 14:37:49 这个夏天,张家界很热,天门山景...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。