Mysql Oracle Sql server 三种类型的存储过程
来源：互联网 发布：win7注册表优化工具 编辑：程序博客网 时间：2024/05/21 10:59
Oracle存储过程
1.通用查询
CREATE OR REPLACE PACKAGE CommonSelPack AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE CommonSelect
(
G_typeId number,
G_sum number,
G_column varchar2,
G_tableName varchar2,
G_terms varchar2,
G_orderBy varchar2,
G_cursor OUT T_CURSOR
);
END CommonSelPack;
create or replace package body CommonSelPack AS
procedure CommonSelect
(
G_typeId number,
G_sum number,
G_column varchar2,
G_tableName varchar2,
G_terms varchar2,
G_orderBy varchar2,
G_cursor OUT T_CURSOR
)
is
G_sqlStr varchar2(4000);
begin
if G_typeId=1 then --查询指定字段的所有记录
G_sqlStr:='select '||G_column||' from '||G_tableName||' where '||G_terms||' order by '||G_orderBy;
elsif G_typeId=2 then --查询指定字段的的sum条记录
G_sqlStr:='select * from (select '||G_column||' from '||G_tableName||' where '||G_terms||' order by '||G_orderBy||') a where rownum <' ||(G_sum+1);
elsif G_typeId=3 then --查询指定字段的所有记录（不带筛选条件）
G_sqlStr:='select '||G_column||' from '||G_tableName||' order by '||G_orderBy;
else --查询指定字段的sum条记录（不带筛选条件）
G_sqlStr:='select * from (select '||G_column||' from '||G_tableName||' order by '||G_orderBy||') a where rownum<'||(G_sum+1);
end if;
open G_cursor for G_sqlStr;
end CommonSelect;
end CommonSelPack;
2.通用分页
CREATE OR REPLACE PACKAGE PageSelPack AS
TYPE P_CURSOR IS REF CURSOR;
PROCEDURE Proce_CommonPaging
(
G_TableName varchar2, --表名
G_ReFieldsStr varchar2, --字段名(全部字段为*)
G_OrderString varchar2, --排序字段(必须!支持多字段不用加order by)
G_WhereString varchar2, --条件语句(不用加where)
G_PageSize number, --每页多少条记录
G_PageIndex number, --指定当前为第几页
G_TotalRecord out number, --返回总记录数
G_pcursor OUT P_CURSOR
);
END PageSelPack;
create or replace package body PageSelPack AS
procedure Proce_CommonPaging
(
G_TableName varchar2, --表名
G_ReFieldsStr varchar2, --字段名(全部字段为*)
G_OrderString varchar2, --排序字段(必须!支持多字段不用加order by)
G_WhereString varchar2, --条件语句(不用加where)
G_PageSize number, --每页多少条记录
G_PageIndex number, --指定当前为第几页
G_TotalRecord out number, --返回总记录数
G_pcursor OUT P_CURSOR
)
is
--处理开始点和结束点
G_StartRecord number;
G_EndRecord number;
G_TotalCountSql varchar2(500);
G_SqlString varchar2(2000);
G_TempTotal number;
BEGIN
G_StartRecord := (G_PageIndex-1)*G_PageSize + 1;
G_EndRecord := G_StartRecord + G_PageSize - 1;
G_TotalCountSql:= 'select count(*) from ' ||G_TableName;--总记录数语句
G_SqlString := 'select t.*,rownum rnum from (select '||G_ReFieldsStr||' from '||G_TableName;--查询语句
IF G_WhereString is not null then
BEGIN
G_TotalCountSql:=G_TotalCountSql || ' where ' || G_WhereString;
G_SqlString :=G_SqlString || ' where '|| G_WhereString;
END;
end if;
G_SqlString:=G_SqlString||' order by '||G_OrderString ||') t where rownum<='||G_EndRecord;
--第一次执行得到
--IF(@G_TotalRecord is null)
-- BEGIN
execute immediate G_TotalCountSql into G_TempTotal;--返回总记录数
G_TotalRecord:=G_TempTotal;
-- END
----执行主语句
G_SqlString :='select * from (' || G_SqlString || ') tt where rnum>=' || G_StartRecord;
open G_pcursor for G_SqlString;
end Proce_CommonPaging;
end PageSelPack;
3.测试表增、删、改
CREATE OR REPLACE PROCEDURE Test_Insert_Update_Delete
(
DataAction number,
Id int default 0,
A varchar(50) default '',
B varchar(50) default ''
)
AS
begin
if DataAction=0 then
begin
insert into test
(
Id,
A,
B
)
values
(
Id,
A,
B
);
end;
end if;
if DataAction=1 then
begin
Update test SET
Id= Id,
A=A,
B=B
Where
Id= Id;
end;
end if;
if DataAction=2 then
begin
delete from test where Id = Id;
end;
end if;
end;
-----------------------------------------------------------------------------------------------
Mysql存储过程
1.通用查询
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `CommonSelect`(
in typeId int,
in sum int,/*1:限制全选，2：限制部分选，3：未限制全选，4：未限制部分选*/
in col varchar(1000),
in tableName varchar(50),
in terms varchar(500),
in orderBy varchar(100)
)
BEGIN
declare sqlStr varchar(8000);
if typeId=1 then /*查询指定字段的所有记录*/
set @sqlStr=CONCAT('select ',col,' from ',tableName,' where ',terms,' order by ',orderBy);
elseif typeId=2 then /*查询指定字段的的sum条记录*/
set @sqlStr=CONCAT('select ',col,' from ',tableName,' where '+terms,' order by ',orderBy,' limit 0,',@sum);
elseif typeId=3 then /*查询指定字段的所有记录（不带筛选条件）*/
set @sqlStr=CONCAT('select ',col,' from ',tableName,' order by ',orderBy);
else /*查询指定字段的sum条记录（不带筛选条件）*/
set @sqlStr=CONCAT('select ',col,' from ',tableName,' order by ',orderBy,' limit 0,',sum);
end if;
PREPARE stmt FROM @sqlStr;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
2.通用分页
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Proce_CommonPaging`(
in TableName varchar(50), #表名
in ReFieldsStr varchar(200), #字段名(全部字段为*)
in OrderString varchar(200), #排序字段(必须!支持多字段不用加order by)
in WhereString varchar(500), #条件语句(不用加where)
in PageSize int, #每页多少条记录
in PageIndex int, #指定当前为第几页
out TotalRecord int #返回总记录数
)
BEGIN
#处理开始点和结束点
Declare StartRecord int;
Declare TotalCountSql varchar(500);
Declare SqlString varchar(4000);
set @StartRecord = (PageIndex-1)*PageSize;
SET @TotalCountSql= CONCAT('select count(1) into TotalRecord from ' ,TableName);#总记录数语句
SET @SqlString = concat('select ',ReFieldsStr,' from ', TableName);#查询语句
IF (WhereString<> '' or WhereString<>null) then
SET @TotalCountSql=concat(@TotalCountSql ,' where ', WhereString);
SET @SqlString =concat(@SqlString, ' where ',WhereString);
end if;
set @SqlString =concat(@SqlString,' order by ',OrderString,'limit ',@StartRecord,',',PageSize);
PREPARE stmtC FROM @TotalCountSql;
EXECUTE stmtC;
DEALLOCATE PREPARE stmtC;
PREPARE stmt FROM @SqlString;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt;
END
3.测试表增、删、改
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Test_Insert_Update_Delete`(
DataAction int,
Id int,
A nvarchar(50),
B nvarchar(50)
)
BEGIN
if DataAction=0 then
insert into test
(
Id,
A,
B
)
values
(
Id,
A,
B
);
end if;
if DataAction=1 then
Update test SET
Id=Id,
A=A,
B=B
Where
Id = Id;
end if;
END
-----------------------------------------------------------
Sql Server存储过程
1.通用查询
CREATE procedure [dbo].[CommonSelect]
(
@typeId int,
@sum int,/*1:限制全选，2：限制部分选，3：未限制全选，4：未限制部分选*/
@col varchar(1000),
@tableName varchar(50),
@terms varchar(500),
@orderBy varchar(100)
)
as
declare @sqlStr nvarchar(4000)
if @typeId=1 --查询指定字段的所有记录
set @sqlStr=N'select '+@col+' from '+@tableName+' where '+@terms+' order by '+@orderBy
else if @typeId=2 --查询指定字段的的sum条记录
set @sqlStr=N'select top '+str(@sum)+' '+@col+' from '+@tableName+' where '+@terms+' order by '+@orderBy
else if @typeId=3 --查询指定字段的所有记录（不带筛选条件）
set @sqlStr=N'select '+@col+' from '+@tableName+' order by '+@orderBy
else --查询指定字段的sum条记录（不带筛选条件）
set @sqlStr=N'select top '+str(@sum)+' '+@col+' from '+@tableName+' order by '+@orderBy
exec(@sqlStr)
2.通用分页
CREATE PROCEDURE [dbo].[Proce_CommonPaging]
(
@TableName varchar(50), --表名
@ReFieldsStr varchar(200) = '*', --字段名(全部字段为*)
@OrderString varchar(200), --排序字段(必须!支持多字段不用加order by)
@WhereString varchar(500) =N'', --条件语句(不用加where)
@PageSize int, --每页多少条记录
@PageIndex int = 1 , --指定当前为第几页
@TotalRecord int output --返回总记录数
)
AS
BEGIN
--处理开始点和结束点
Declare @StartRecord int;
Declare @EndRecord int;
Declare @TotalCountSql nvarchar(500);
Declare @SqlString nvarchar(2000);
set @StartRecord = (@PageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @PageSize - 1
SET @TotalCountSql= N'select @TotalRecord=count(*) from ' + @TableName;--总记录数语句
SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;--查询语句
--
IF (@WhereString! = '' or @WhereString!=null)
BEGIN
SET @TotalCountSql=@TotalCountSql + ' where '+ @WhereString;
SET @SqlString =@SqlString+ ' where '+ @WhereString;
END
--第一次执行得到
--IF(@TotalRecord is null)
-- BEGIN
EXEC sp_executesql @TotalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数
-- END
----执行主语句
set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' + ltrim(str(@EndRecord));
Exec(@SqlString)
END
GO
3.测试表增、删、改
CREATE PROCEDURE [dbo].[Test_Insert_Update_Delete]
@DataAction int,
@Id int,
@A nvarchar(50),
@B nvarchar(50)=''
AS
begin tran
SET NOCOUNT ON
if @DataAction=0
begin
insert into test
(
[Id],
[A],
[B]
)
values
(
@Id,
@A,
@B
)
end
if @DataAction=1
begin
Update [test] SET
[Id]=@Id,
[A]=@A,
[B]=@B
Where
[Id] = @Id
end
if @@error<>0 goto sqlerr
commit tran
return
sqlerr:
rollback
SET NOCOUNT OFF
GO
- Mysql Oracle Sql server 三种类型的存储过程
- mysql + sql Server 存储过程
- SQL Server 2000 数据库有三种类型的文件
- SQL Server 2000 数据库三种类型的文件
- SQL Server 存储过程的分页的三种方案
- 存储过程的种类：
- 存储过程的种类
- Oracle和SQL Server存储过程的不同
- Oracle和SQL Server存储过程的不同
- SQL Server的存储过程
- sql server的存储过程
- sql server的存储过程
- 调试oracle, sql server存储过程
- sql server 存储过程内访问Oracle
- Sql Server与Ｏracle存储过程转换
- C#连接4种类型数据库（Access、SQL Server、Oracle、MySQL）
- C#连接4种类型数据库（Access、SQL Server、Oracle、MySQL）
- C#连接4种类型数据库（Access、SQL Server、Oracle、MySQL）
- Python入门自学笔记20160705
- HandlerThread
- Visual Leak Detector无法显示Call Stack文字的解决方法
- nil和Nil的区别
- shell中利用自定义命令简化操作
- Mysql Oracle Sql server 三种类型的存储过程
- 常用的正则表达式一览
- 磁条卡刷卡原理
- postgresql 部分函数整理
- RxJava 事件流之过滤数据
- Linux man命令的使用方法
- linux服务器分区因挂载问题突然变只读处理
- 有关ajax表单序列化serialize()的问题
- 关于RPC
