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
没有合适的资源?快使用搜索试试~ 我知道了~
.net 三层+Wcf框架(自适应Oracle,Mysql,SqlServer三种数据库,完整实例)
共161个文件
cs:49个
dll:34个
pdb:28个
需积分: 9 64 下载量 141 浏览量
2018-03-07
13:59:20
上传
评论
收藏 809KB RAR 举报
温馨提示
.net 三层+Wcf框架(自适应Oracle,Mysql,SqlServer三种数据库,完整实例)
资源推荐
资源详情
资源评论
收起资源包目录
.net 三层+Wcf框架(自适应Oracle,Mysql,SqlServer三种数据库,完整实例) (161个子文件)
test.aspx 1000B
WebApplication.csprojResolveAssemblyReference.cache 24KB
ProjectWCF.csprojResolveAssemblyReference.cache 24KB
DesignTimeResolveAssemblyReferences.cache 22KB
ProjectBusiness.csprojResolveAssemblyReference.cache 19KB
ProjectAccess.csprojResolveAssemblyReference.cache 16KB
DBUtility.csprojResolveAssemblyReference.cache 11KB
DesignTimeResolveAssemblyReferencesInput.cache 10KB
DesignTimeResolveAssemblyReferencesInput.cache 8KB
DesignTimeResolveAssemblyReferencesInput.cache 7KB
DesignTimeResolveAssemblyReferencesInput.cache 7KB
DesignTimeResolveAssemblyReferencesInput.cache 7KB
DesignTimeResolveAssemblyReferencesInput.cache 7KB
DesignTimeResolveAssemblyReferencesInput.cache 6KB
ProjectCommonFunctions.csprojResolveAssemblyReference.cache 5KB
Web.config 5KB
Web.config 3KB
Web.Release.config 1KB
Web.Release.config 1KB
Web.Debug.config 1KB
Web.Debug.config 1KB
ExcelHelper.cs 18KB
FileHelper.cs 12KB
JsonOperation.cs 12KB
Graph.cs 10KB
DBHelper.cs 6KB
Reference.cs 4KB
EnumeItem.cs 4KB
DbFactory.cs 3KB
Encript.cs 3KB
Test.cs 3KB
MessageBox.cs 3KB
test.aspx.designer.cs 2KB
SiteSession.cs 2KB
PageBase.cs 2KB
AssemblyInfo.cs 1KB
AssemblyInfo.cs 1KB
AssemblyInfo.cs 1KB
AssemblyInfo.cs 1KB
AssemblyInfo.cs 1KB
AssemblyInfo.cs 1KB
AssemblyInfo.cs 1KB
test.aspx.cs 1010B
TestManage.svc.cs 998B
CmdText.cs 720B
IpHelper.cs 704B
TestBll.cs 692B
ITestManage.cs 639B
Test.cs 611B
TemporaryGeneratedFile_5937a670-0e60-4077-877b-f7221da3dda1.cs 0B
TemporaryGeneratedFile_E7A71F73-0F8D-4B9B-B56E-8E70B10BC5D3.cs 0B
TemporaryGeneratedFile_036C0B5B-1481-4323-8D20-8F5ADCB23D92.cs 0B
TemporaryGeneratedFile_5937a670-0e60-4077-877b-f7221da3dda1.cs 0B
TemporaryGeneratedFile_E7A71F73-0F8D-4B9B-B56E-8E70B10BC5D3.cs 0B
TemporaryGeneratedFile_036C0B5B-1481-4323-8D20-8F5ADCB23D92.cs 0B
TemporaryGeneratedFile_5937a670-0e60-4077-877b-f7221da3dda1.cs 0B
TemporaryGeneratedFile_E7A71F73-0F8D-4B9B-B56E-8E70B10BC5D3.cs 0B
TemporaryGeneratedFile_036C0B5B-1481-4323-8D20-8F5ADCB23D92.cs 0B
TemporaryGeneratedFile_5937a670-0e60-4077-877b-f7221da3dda1.cs 0B
TemporaryGeneratedFile_E7A71F73-0F8D-4B9B-B56E-8E70B10BC5D3.cs 0B
TemporaryGeneratedFile_036C0B5B-1481-4323-8D20-8F5ADCB23D92.cs 0B
TemporaryGeneratedFile_5937a670-0e60-4077-877b-f7221da3dda1.cs 0B
TemporaryGeneratedFile_E7A71F73-0F8D-4B9B-B56E-8E70B10BC5D3.cs 0B
TemporaryGeneratedFile_036C0B5B-1481-4323-8D20-8F5ADCB23D92.cs 0B
TemporaryGeneratedFile_5937a670-0e60-4077-877b-f7221da3dda1.cs 0B
TemporaryGeneratedFile_E7A71F73-0F8D-4B9B-B56E-8E70B10BC5D3.cs 0B
TemporaryGeneratedFile_036C0B5B-1481-4323-8D20-8F5ADCB23D92.cs 0B
TemporaryGeneratedFile_5937a670-0e60-4077-877b-f7221da3dda1.cs 0B
TemporaryGeneratedFile_E7A71F73-0F8D-4B9B-B56E-8E70B10BC5D3.cs 0B
TemporaryGeneratedFile_036C0B5B-1481-4323-8D20-8F5ADCB23D92.cs 0B
WebApplication.csproj 7KB
ProjectWCF.csproj 5KB
ProjectCommonFunctions.csproj 3KB
DBUtility.csproj 3KB
ProjectBusiness.csproj 3KB
ProjectAccess.csproj 3KB
ProjectModel.csproj 2KB
ProjectModel.Test.datasource 583B
TestManage.disco 367B
MySql.Data.dll 288KB
MySql.Data.dll 288KB
MySql.Data.dll 288KB
MySql.Data.dll 288KB
MySql.Data.dll 288KB
ProjectCommonFunctions.dll 36KB
ProjectCommonFunctions.dll 36KB
DBUtility.dll 9KB
DBUtility.dll 9KB
DBUtility.dll 9KB
DBUtility.dll 9KB
DBUtility.dll 9KB
DBUtility.dll 9KB
WebApplication.dll 7KB
WebApplication.dll 7KB
ProjectAccess.dll 6KB
ProjectAccess.dll 6KB
ProjectAccess.dll 6KB
ProjectAccess.dll 6KB
ProjectAccess.dll 6KB
ProjectModel.dll 5KB
共 161 条
- 1
- 2
资源评论
qq_16313575
- 粉丝: 392
- 资源: 207
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功