一、 SQL脚本标准
- 各文件夹存放的脚本说明
存储过程:除“基础_”开头的所有存储过程,包含新增、修改、删除、列表、提交、审核。
基础数据:“基础_”开头的存储过程,用于下拉列表的数据加载公共方法。
函数:视图与存储过程中使用到的函数,如获取简拼等。
其他脚本:存放新增表、新增列等脚本,命名规则:
1) 新增表,例:XB_学报_新增表
2) 新增列,例:XB_学报_新增列
3) 修改表,对表格某一列的数据类型修改,例:XB_学报_修改表
4) 数据修改,对表中数据的UPDATE、DELETE、INSERT操作,例:XB_学报_数据修改
5) 添加菜单,例:QX_模块_添加菜单_学报
6) 修改菜单,所有对菜单的UPDATE操作放在此脚本内,修改模块名称、模块地址、上级模块等,例:QX_模块_修改菜单
7) 删除菜单,所有删除菜单脚本放在此脚本内,例:QX_模块_删除菜单
8) 删除废弃脚本,所有的需要删除的脚本放在此脚本内,脚本用于数据库中执行,需要同时删除脚本文件。
- 新增表脚本
1)命名规则,表格名称前面需要加上中文缩写,以便于数据库中操作时快速找到表,创建之前需要判断是否存在,如果存在则先删除再创建。
2)创建方法:以此为模板复制粘贴另存到项目的其他脚本文件夹中,修改表名,以及除[编号]之外的列名字段类型即可。
IF OBJECT_ID('GZJH_工作计划','U') IS NOT NULL BEGIN DROP TABLE [dbo].[GZJH_工作计划] END
CREATE TABLE [dbo].[GZJH_工作计划]( [编号] [int] IDENTITY(1,1) NOT NULL, [工作计划编号] [varchar](30) NULL, [工作计划分类] [int] NULL, [开始学年] [int] NULL, [结束学年] [int] NULL, [学期] [int] NULL, [组名] [varchar](40) NULL, [申报人员] [varchar](50) NULL, [申报日期] [datetime] NULL, [审核状态] [int] NULL, [审核意见] [varchar](400) NULL, [附件名称] [varchar](50), [附件路径] [varchar](100), PRIMARY KEY ([编号]) ) |
- 视图脚本
1) 命名规则,“V_”加上表格名称
2) 在适当的位置换行,每行需要差不多长短,换行需要缩进且逗号在前
3) 在视图中编辑好关联性字段编号和名称,命名规则示例:“工作计划分类编号”,“工作计划分类”
4) 需要在界面上显示的日期需要转换为“2016-10-09”格式,命名规则示例:“申报日期_显示”,不需要在界面上显示的不要添加。
5) 有审核状态的使用函数DBO.XT_获取审核状态(1,A.审核状态),从XT_审核状态表中查询,1代表通用,用途最广,如有特殊情况需要在审核状态表中新增类别。
6) 创建方法:以此为模板复制粘贴另存到项目的视图文件夹中,修改视图名称,修改查询脚本,注意换行与缩进格式。
IF OBJECT_ID('V_工作计划','V') IS NOT NULL BEGIN DROP VIEW [dbo].[V_工作计划] END GO
CREATE VIEW [dbo].[V_工作计划] WITH ENCRYPTION AS SELECT A.编号,A.工作计划编号,A.工作计划分类 AS 工作计划分类编号,C.名称 AS 工作计划分类,A.开始学年,A.结束学年,A.学期 AS 学期编号 ,D.名称 AS 学期,A.组名,A.申报人员 AS 申报人员编号,B.姓名 AS 申报人员,B.所属单位,A.申报日期,CONVERT(VARCHAR(10),A.申报日期,120) AS 申报日期_显示 ,A.审核状态 AS 审核状态编号, DBO.XT_获取审核状态(1,A.审核状态) AS 审核状态,A.审核意见,A.附件名称,A.附件路径 FROM dbo.GZJH_工作计划 AS A LEFT JOIN dbo.V_职工信息 AS B ON A.申报人员 = B.人员编号 LEFT JOIN dbo.GZJH_工作计划分类 AS C ON A.工作计划分类 = C.编号 LEFT JOIN dbo.ZG_学期 AS D ON A.学期 = D.编号 |
- 新增存储过程
1) 命名规则,表格名称加上“新增”
2) 如果列名较多在适当的位置换行,每行需要差不多长短,换行需要缩进且逗号在前
3) 参数后数据类型需要对齐,使用TAB键,以最长的参数名隔一个TAB键为准,如此例中@工作计划编号TAB键VARCHAR(30)。
4) 如果是有审核状态的表单,新增的时候默认为5待完善状态。
5) 创建方法:以此为模板复制粘贴另存到项目的存储过程文件夹中,修改存储过程名称,修改表名列名参数名,注意换行与缩进格式。
IF OBJECT_ID('GZJH_工作计划新增','P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[GZJH_工作计划新增] END GO
CREATE PROCEDURE [dbo].[GZJH_工作计划新增] @工作计划编号 VARCHAR(30), @工作计划分类 INT, @开始学年 INT, @结束学年 INT, @学期 INT, @组名 VARCHAR(40), @申报人员 VARCHAR(50), @申报日期 DATETIME, @审核意见 VARCHAR(400), @附件名称 VARCHAR(50), @附件路径 VARCHAR(100) WITH ENCRYPTION AS INSERT INTO GZJH_工作计划(工作计划编号,工作计划分类,开始学年,结束学年,学期,组名,申报人员,申报日期,审核状态,附件名称,附件路径) VALUES(@工作计划编号,@工作计划分类,@开始学年,@结束学年,@学期,@组名,@申报人员,@申报日期,5,@附件名称,@附件路径)
IF @@ERROR <> 0 BEGIN RETURN(-1) END ELSE BEGIN RETURN(@@IDENTITY) END |
- 修改存储过程
1) 命名规则,表格名称加上“修改”
2) 如果列名较多在适当的位置换行,每行需要差不多长短,换行需要缩进且逗号在前,字段与字段之间逗号之后空一格
3) 参数后数据类型需要对齐,使用TAB键,以最长的参数名隔一个TAB键为准,如此例中@工作计划编号TAB键VARCHAR(30)。
4) 如果是有审核状态的表单,如果是5待完善状态修改之后还是为5待完善状态,否则修改为0待审核状态,用于审核不通过后重新修改保存后校级审核角色重新审核。
5) 创建方法:以此为模板复制粘贴另存到项目的存储过程文件夹中,修改存储过程名称,修改表名列名参数脚本,注意换行与缩进格式。
IF OBJECT_ID('GZJH_工作计划修改','P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[GZJH_工作计划修改] END GO
CREATE PROCEDURE [dbo].[GZJH_工作计划修改] @编号 INT, @工作计划编号 VARCHAR(30), @工作计划分类 INT, @开始学年 INT, @结束学年 INT, @学期 INT, @组名 VARCHAR(40), @申报人员 VARCHAR(50), @申报日期 DATETIME, @审核意见 VARCHAR(400), @附件名称 VARCHAR(50), @附件路径 VARCHAR(100) WITH ENCRYPTION AS UPDATE GZJH_工作计划 SET 开始学年=@开始学年, 结束学年=@结束学年, 学期=@学期, 组名=@组名,审核状态=(CASE WHEN 审核状态=5 THEN '5' ELSE '0' END) ,审核意见=@审核意见, 附件名称=@附件名称, 附件路径=@附件路径 WHERE 编号=@编号
IF @@ERROR <> 0 BEGIN RETURN(-1) END ELSE BEGIN RETURN(1) END |
- 删除存储过程
1) 命名规则,示例为通用删除存储过程,适用于系统中大部分表格的删除功能,如遇特殊情况需要单独创建,命名为表格名称加上“删除”
2) 是否删除判断:如果有数据关联性删除前需要进行判断是否被使用;涉及到权限需要根据当前的审核状态判断角色级别是否有权限删除
3) 参数后数据类型需要对齐,使用TAB键,以最长的参数名隔一个TAB键为准。
4) 成功删除返回值为1,否则为0,执行出错返回-1。
5) 创建方法:以此为模板复制粘贴另存到项目的存储过程文件夹中,修改存储过程名称,修改删除脚本,注意空行格式。
IF OBJECT_ID('XT_通用删除','P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[XT_通用删除] END GO
CREATE PROCEDURE [dbo].[XT_通用删除] @表名 VARCHAR(50), @角色级别 VARCHAR(50), @编号 INT WITH ENCRYPTION AS SET NOCOUNT ON DECLARE @sql NVARCHAR(1000) DECLARE @申报审核状态 INT
SET @sql = N'SELECT @申报审核状态=审核状态 FROM ' + @表名 + N' WHERE 编号=' + CONVERT(VARCHAR,@编号); EXEC sp_executesql @sql, N'@申报审核状态 INT OUTPUT', @申报审核状态 OUTPUT
DECLARE @rValue INT SET @rValue=0
SET @sql = 'DELETE FROM ' + @表名 + ' WHERE 编号=' + CONVERT(VARCHAR,@编号)
IF @角色级别='校级' BEGIN SET @rValue=1 EXEC(@sql) END ELSE IF @角色级别='院系级' BEGIN IF @申报审核状态 NOT IN (2,4) BEGIN SET @rValue=1 EXEC(@sql) END END ELSE IF @角色级别='个人级' BEGIN IF @申报审核状态 IN (0,5) BEGIN SET @rValue=1 EXEC(@sql) END END
IF @@ERROR <> 0 BEGIN RETURN(-1) END ELSE BEGIN RETURN(@rValue) END GO |
- 浏览存储过程
1) 命名规则,表格名称加上“浏览”
2) 参数固定为编号
3) 创建方法:以此为模板复制粘贴另存到项目的存储过程文件夹中,修改存储过程名称,修改视图名称。
IF OBJECT_ID('GZJH_工作计划浏览','P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[GZJH_工作计划浏览] END GO
CREATE PROCEDURE [dbo].[GZJH_工作计划浏览] @编号 INT WITH ENCRYPTION AS SELECT * FROM V_工作计划 WHERE 编号 = @编号 GO |
- 列表存储过程
1) 命名规则,表格名称加上“列表”
2) 固定参数,如果需要分页第一个为@RecordCount最后四个为@排序字段、@排序模式、@PageSize、@PageNum,如果涉及到权限第二到第四为@角色级别、@人员编号、@所属单位,顺序保持不变,其他参数按照页面上的顺序写。
3) 创建方法:以此为模板复制粘贴另存到项目的存储过程文件夹中,修改存储过程名称,修改视图名称,修改参数,修改查询条件,姓名标题之类用LIKE,数值日期范围用BETWEEN,其他大部分用等于,少量需要用到CHARINDEX判断是否包含某字符串,例CHARINDEX('''+@作者姓名+''',作者) > 0。
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GZJH_工作计划列表]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[GZJH_工作计划列表] GO
CREATE PROCEDURE [dbo].[GZJH_工作计划列表] @RecordCount INT OUTPUT, @角色级别 VARCHAR(50), @人员编号 VARCHAR(50), @所属单位 VARCHAR(50), @工作计划分类 INT, @开始学年 INT, @结束学年 INT, @学期 INT, @组名 VARCHAR(40), @申报人员 VARCHAR(50), @申报日期1 VARCHAR(50), @申报日期2 VARCHAR(50), @审核状态 INT, @排序字段 VARCHAR(50), @排序模式 VARCHAR(50), @PageSize INT, @PageNum INT WITH ENCRYPTION AS DECLARE @PRESQL VARCHAR(1000) DECLARE @SUFSQL VARCHAR(8000)
SELECT @PRESQL='SELECT * FROM (' SELECT @PRESQL=@PRESQL+'SELECT Row_Number() OVER (' IF @排序字段<>'' AND @排序模式<>'' BEGIN SELECT @PRESQL=@PRESQL+'ORDER BY '+@排序字段+' '+@排序模式+') as row_num,' END ELSE BEGIN SELECT @PRESQL=@PRESQL+'ORDER BY 编号 desc) as row_num,' END
SELECT @PRESQL=@PRESQL+'*' SELECT @SUFSQL=' FROM V_工作计划 WHERE 1=1'
IF @角色级别='个人级' BEGIN SELECT @SUFSQL=@SUFSQL+' AND 申报人员编号='''+@人员编号+'''' END
IF @角色级别='院系级' BEGIN SELECT @SUFSQL=@SUFSQL+' AND 所属单位='''+@所属单位+'''' END
IF @所属单位<>'' BEGIN SELECT @SUFSQL=@SUFSQL+' AND 所属单位='''+@所属单位+'''' END
IF @工作计划分类 > 0 BEGIN SELECT @SUFSQL=@SUFSQL+' AND 工作计划分类编号=' + CONVERT(VARCHAR,@工作计划分类) END
IF @开始学年 > 0 BEGIN SELECT @SUFSQL=@SUFSQL+' AND 开始学年=' + CONVERT(VARCHAR,@开始学年) END
IF @结束学年 > 0 BEGIN SELECT @SUFSQL=@SUFSQL+' AND 结束学年=' + CONVERT(VARCHAR,@结束学年) END
IF @学期 > 0 BEGIN SELECT @SUFSQL=@SUFSQL+' AND 学期=' + CONVERT(VARCHAR,@学期) END
IF @组名<>'' BEGIN SELECT @SUFSQL=@SUFSQL+' AND 组名 LIKE ''%'+@组名+'%''' END
IF @申报人员<>'' BEGIN SELECT @SUFSQL=@SUFSQL+' AND 申报人员 LIKE ''%'+@申报人员+'%''' END
IF @申报日期1<>'' AND @申报日期2<>'' BEGIN SELECT @SUFSQL=@SUFSQL+' AND 申报日期 BETWEEN '''+@申报日期1+''' AND '''+@申报日期2+'''' END
IF @审核状态>0 BEGIN SELECT @SUFSQL=@SUFSQL+' AND 审核状态 =' + CONVERT(VARCHAR,@审核状态) END
DECLARE @SQL VARCHAR(8000) EXEC GetPagingStr @PRESQL,@SUFSQL,@SQL OUT,@PageSize,@PageNum,@RecordCount OUT EXEC (@SQL) |
- 提交与审核存储过程
1) 命名规则,示例为通用审核存储过程,适用于系统中大部分表格的审核功能,即XT_审核状态表中1通用类别的6个审核状态(0未审核1院系通过2学校通过3院系不通过4学校不通过5待完善),如遇特殊情况需要单独创建,命名为表格名称加上“审核”或“提交”,特殊审核状态的同样需要在XT_审核状态表中添加此类别的审核状态。
2) 参数,@是否通过为固定3种,0不通过、1通过、2提交
3) 通过与不通过,需要根据当前的审核状态判断角色级别是否有权限删除,通过时院级只能将0未审核与3院系不通过两个状态修改为1院系通过,校级不限制;不通过时院级只能将0未审核与1院系通过两个状态修改为3院系不通过,校级不限制。
4) 提交,只有状态为5待完善时才改变状态为0未审核。
5) 参数后数据类型需要对齐,使用TAB键,以最长的参数名隔一个TAB键为准。
6) 成功修改状态后返回值为1,否则为0,执行出错返回-1。
7) 创建方法:以此为模板复制粘贴另存到项目的存储过程文件夹中,修改存储过程名称,修改参数及执行脚本,注意空行格式。
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[XT_通用审核]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[XT_通用审核] GO
CREATE PROCEDURE [dbo].[XT_通用审核] @表名 VARCHAR(50), @角色级别 VARCHAR(50), @编号 INT, @是否通过 INT --0不通过,1通过,2提交 WITH ENCRYPTION AS SET NOCOUNT ON DECLARE @sql NVARCHAR(1000) DECLARE @申报审核状态 INT DECLARE @审核状态 INT SET @sql = N'SELECT @申报审核状态=审核状态 FROM ' + @表名 + N' WHERE 编号=' + CONVERT(VARCHAR,@编号); EXEC sp_executesql @sql, N'@申报审核状态 INT OUTPUT', @申报审核状态 OUTPUT
IF @是否通过=0 AND @申报审核状态 <> 5 BEGIN IF @角色级别='校级' BEGIN SET @审核状态 = 4 END IF @角色级别='院系级' BEGIN IF @申报审核状态 IN (0,1) BEGIN SET @审核状态 = 3 END END END
IF @是否通过=1 AND @申报审核状态 <> 5 BEGIN IF @角色级别='校级' BEGIN SET @审核状态 = 2 END IF @角色级别='院系级' BEGIN IF @申报审核状态 IN (0,3) BEGIN SET @审核状态 = 1 END END END
IF @是否通过=2 BEGIN IF @申报审核状态 = 5 BEGIN SET @审核状态 = 0 END END
DECLARE @rValue INT SET @rValue=0
IF @审核状态 IS NOT NULL BEGIN SET @rValue=1 SET @sql = 'UPDATE ' + @表名 + ' SET 审核状态=' + CONVERT(VARCHAR,@审核状态) + ' WHERE 编号=' + CONVERT(VARCHAR,@编号) EXEC(@sql) END
IF @@ERROR <> 0 BEGIN RETURN(-1) END ELSE BEGIN RETURN(@rValue) END GO |
- 基础存储过程
1) 命名规则,以基础开头加上表格中文名称,如XM_成果形式为“基础_成果形式”
2) 创建方法:以此为模板复制粘贴另存到项目的基础数据文件夹中,修改存储过程名称,修改视表格名称。
IF OBJECT_ID('基础_成果形式','P') IS NOT NULL BEGIN DROP PROCEDURE [dbo].[基础_成果形式] END GO
CREATE PROCEDURE [dbo].[基础_成果形式] WITH ENCRYPTION AS BEGIN SET NOCOUNT ON
SELECT * FROM dbo.XM_成果形式
END |