试图:
试图:虚拟表,由一个或多个表通过查询而定义出来的。将查询定义保存起来,实际不包括数据。
与表的区别:表是用于存储数据的地方;试图存储的查询语句(索引试图除外);
试图作用:简化查询,增加数据的保密性,安全性上得到保障;
试图缺点:只是简化查询,并不提高查询速度;增加维护成本;
视图分类:
- 标准试图:存储查询定义,没有存储数据
- 索引试图:被具体化的试图,创建了索引,显著提高查询性能,聚合了很多行的查询,不太适合经常更新基本数据集(不能删除数据,删除数据导致基本表中数据删除)
- 分区试图:一台或多台服务器之间水平连接一组成员表的分区数据
标准视图如下所示:
--新增视图 IF EXISTS(SELECT * FROM sysobjects WHERE id = object_id(N'[VIEW_USERINFO]') AND OBJECTPROPERTY(id, N'IsView') = 1) DROP View [VIEW_USERINFO] GO CREATE VIEW [dbo].[VIEW_USERINFO] AS SELECT U.ID, LoginName, TrueName, PassWord, OrganizationCode, Organization, DepartMent, SystemClass, UserType, StuffID, UserParam, U.RoleCode RoleName, RoleRemark, PermissionCodes FROM [dbo].[UserMEAS] U,[dbo].[UserRole] WHERE U.RoleCode=UserRole.RoleCode GO
索引试图:慎用
- 不能出现select * ;
- with schemabinding
- 必须写出需要查询的所有列;
- 表名前面需要所有者,即dbo.tablename(创建索引视图时要指定表所属的架构;)
- 只能为索引视图创建唯一聚集索引;
- 索引视图中的select包含一个或多个 UNION、INTERSECT 或 EXCEPT 运算符时,不能创建索引(创建视图时不报错,创建索引的时候会报错);
--1.创建索引视图 create view v_customer_sch_index with schemabinding as select Col1,Col2 from dbo.customer go
。。。。。。
注意:在对标准试图,存在子查询的多表查询的视图——创建索引时貌似不支持哦(未做深入研究)
PROC存储过程:
https://www.cnblogs.com/pxyblog/p/16327517.html
存储过程,一种为了完成特定功能的一组SQL语句集合。经编译后存储在服务器的数据库中,可以利用存储过程来加速SQL语句的执行。
调用名称,传入参数,执行其完成特定的功能。
存储过程分类:
- 系统存储过程(master数据库中),其他数据库中可直接调用且不必在前面加数据库名,创建数据库时,这些系统存储过程在新的数据库中将会自动创建。
- 用户自定义存储过程:
- 用户自助创建的,用于完成特定功能;
- 可传入参数,也可有返回值;
- 可以存在一个或多个执行操作;
- 执行:exec 或 execute 存储过程名称 参数列表(多个参数,使用逗号隔开)
PROC优点:
- 提高应用程序的通用性和可移植性;多次调用,而不用重新去编写,可随时修改;
- 可以更有效的管理数据库权限;
- 可提高执行SQL语句的速度(一次编译,多次调用)
- 减轻服务器的负担(一次可执行多个sql操作)
PROC缺点:
- 需要专职维护——存储过程的代码不易阅读,维护难度大
- 占用数据库空间
存储过程的参数分为两种:输入参数和输出参数。
- 输入参数:用于向存储过程传入值,类似开发语言中的入参。
- 输出参数:用于调用存储过程后,输出参数,类似开发语言的返回值,可理解为开发语言中的 out 参数;
无参存储过程示例:
--创建学生表 create table student( student_id int identity(1,1) primary key, student_name varchar(10), student_age int, student_sex varchar(2) ); --插入学生数据 insert into student(student_name,student_age,student_sex) values ('张三',23,'男'), ('李四',18,'女'), ('王五',32,'男') --创建无参存储过程 if object_id('getAllstu','p') is not null drop proc getAllstu go create proc getAllstu --prco全写:procedure as select * from student go --调用,执行存储过程 exec getAllstu
--修改存储过程 alter proc getAllstu as select name,age from student go --删除存储过程 drop proc getSomestu -- 重命名存储过程 sp_rename getAllstu,getSomestu
有参存储过程示例:
--示例一 单个参数 if object_id('searchStu','p') is not null drop proc searchStu go create proc searchStu(@stuID int) as --要求student_id列与输入参数相等 select * from student where student_id=@stuID go --执行searchStu exec searchStu 1 --示例二 多个参数 if object_id('searchStus','p') is not null drop proc searchStus go create proc searchStus( @stuID int, @stu_name varchar(10)) as --要求stuID和stu_name列与输入参数相等 select * from student where student_id=@stuID and student_name=@stu_name go --执行searchStus exec searchStus 2,'王五' --示例三 存在返回值 if object_id('getStuId','p') is not null drop proc getStuId go create proc getStuId( @stu_Name varchar(10),--输入参数,无默认值 @stuId int output --输入/输出参数 无默认值 ) as select @stuId=student_id from student where student_name=@stu_Name go --执行getStuId这个带返回值的存储过程 declare @id int --声明一个变量用来接收执行存储过程后的返回值 exec getStuId '张三',@id output select @id as stuId;--as是给返回的列值起一个名字 --示例四:带默认值 if object_id('getStuId','p') is not null drop proc getStuId go create proc getStuId( @stu_Name varchar(10)='李四',--输入参数,有默认值 @stuId int output --输入/输出参数 无默认值 ) as select @stuId=student_id from student where student_name=@stu_Name go --执行getStuId这个带返回值的存储过程 declare @id int --声明一个变量用来接收执行存储过程后的返回值 exec getStuId default,@id output select @id as stuId;--as是给返回的列值起一个名字 --示例5:带通配符的存储过程 if object_id('charStu','p') is not null drop proc charStu go create proc charStu( @stu_Name varchar(10)='张%' ) as select * from student where student_name like @stu_Name go --执行存储过程charStu exec charStu '王%'
存储过程实际应用示例:
USE DBNAME GO /****** Object: StoredProcedure [dbo].[INIT_DICT_QUEUECODE] Script Date: 2023-06-08 09:49:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[INIT_DICT_QUEUECODE] ( @p_QUEUEID int, @p_QUEUENAME varchar(100), @p_DEVICECOUNT int, @p_CHECKDURATION int, @p_BEGINTIMEPART varchar(50), @p_ENDTIMEPART varchar(50), @p_CODECOUNT int, @p_QUEUESIGN varchar(50), @p_ADDRESS varchar(300), @p_REMARK varchar(500), @p_ofdepart varchar(30), @p_PMBEGINTIME varchar(50), @p_PMENDTIME varchar(50), @p_CodeCoefficient varchar(50), @p_TIMEPART varchar(50), @p_PARAMTYPE varchar(50), @p_CALLTYPE varchar(50), @p_result int output ) as insert into QS_PARAM (QUEUEID, QUEUENAME, DEVICECOUNT, CHECKDURATION, BEGINTIMEPART, ENDTIMEPART, CODECOUNT,QUEUESIGN,ADDRESS,ofdepart,PMBEGINTIME, PMENDTIME, CodeCoefficient, TIMEPART,PARAMTYPE,CALLTYPE,REMARK) values(@p_QUEUEID, @p_QUEUENAME, @p_DEVICECOUNT, @p_CHECKDURATION, @p_BEGINTIMEPART, @p_ENDTIMEPART, @p_CODECOUNT,@p_QUEUESIGN,@p_ADDRESS,@p_ofdepart,@p_PMBEGINTIME, @p_PMENDTIME, @p_CodeCoefficient, @p_TIMEPART,@p_PARAMTYPE,@p_CALLTYPE,@p_REMARK) declare @loopNum int,--循环次数 @codeNum int,--号源编号 @codeFirstAm int, @codeFirstPm int DECLARE paramDetails cursor for select queuename,paramtype,begintimepart,endtimepart,ofdepart,calltype,codecount from qs_param a where a.queueid=@p_QUEUEID and a.paramtype=@p_PARAMTYPE order by convert(float,endtimepart) begin try set @loopNum=1 set @codeNum=1 set @codeFirstAm=0--未进行初始化 set @codeFirstPm=0--未进行初始化 --删除号源字典 delete from dict_queuecode where queuename=@p_QUEUENAME and codeparamtype=@p_PARAMTYPE and ofdepart=@p_ofdepart begin tran --当前事务点,rollback、commit都从这里开始 DECLARE @v_QUEUEID int, @v_QUEUENAME varchar(100), @v_DEVICECOUNT int, @v_CHECKDURATION int, @v_BEGINTIMEPART varchar(50), @v_ENDTIMEPART varchar(50), @v_CODECOUNT int, @v_QUEUESIGN varchar(50), @v_ADDRESS varchar(50), @v_ofdepart varchar(30), @v_PMBEGINTIME varchar(50), @v_PMENDTIME varchar(50), @v_CodeCoefficient varchar(50), @v_TIMEPART varchar(50), @v_PARAMTYPE varchar(50), @v_CALLTYPE varchar(50) open paramDetails fetch next from paramDetails into @v_queuename,@v_paramtype,@v_begintimepart,@v_endtimepart,@v_ofdepart,@v_calltype,@v_codecount WHILE @@FETCH_STATUS =0 begin --循环获取qs_param表中维护的某个时间段 set @loopNum=1 while @loopNum <= @v_codecount begin --上午 if @codeFirstAm=0 and @v_calltype='上午' begin set @codeNum=1 set @codeFirstAm=1 end --下午 if @codeFirstPm=0 and @v_calltype='下午' begin set @codeNum=1 set @codeFirstPm=1 end --添加号源信息 insert into DICT_QUEUECODE (queuename,codeparamtype,codevalue,timepart,hintinfo,OFDEPART,calltype,queueid) values (@v_queuename,@v_paramtype,@codeNum,@v_begintimepart+'~'+@v_endtimepart,'',@v_ofdepart,@v_calltype,@p_QUEUEID) --重新赋值 set @loopNum=@loopNum+1 set @codeNum=@codeNum+1 end fetch next from paramDetails into @v_queuename,@v_paramtype,@v_begintimepart,@v_endtimepart,@v_ofdepart,@v_calltype,@v_codecount end close paramDetails DEALLOCATE paramDetails set @p_result=1 commit end try begin catch set @p_result=-1 rollback end catch
执行调用:
--变量声明
DECLARE @RC int DECLARE @p_QUEUEID int DECLARE @p_QUEUENAME varchar(100) DECLARE @p_DEVICECOUNT int DECLARE @p_CHECKDURATION int DECLARE @p_BEGINTIMEPART varchar(50) DECLARE @p_ENDTIMEPART varchar(50) DECLARE @p_CODECOUNT int DECLARE @p_QUEUESIGN varchar(50) DECLARE @p_ADDRESS varchar(300) DECLARE @p_REMARK varchar(500) DECLARE @p_ofdepart varchar(30) DECLARE @p_PMBEGINTIME varchar(50) DECLARE @p_PMENDTIME varchar(50) DECLARE @p_CodeCoefficient varchar(50) DECLARE @p_TIMEPART varchar(50) DECLARE @p_PARAMTYPE varchar(50) DECLARE @p_CALLTYPE varchar(50) DECLARE @p_result int -- TODO: 在此处设置参数值。 EXECUTE @RC = [dbo].[INIT_DICT_QUEUECODE] @p_QUEUEID ,@p_QUEUENAME ,@p_DEVICECOUNT ,@p_CHECKDURATION ,@p_BEGINTIMEPART ,@p_ENDTIMEPART ,@p_CODECOUNT ,@p_QUEUESIGN ,@p_ADDRESS ,@p_REMARK ,@p_ofdepart ,@p_PMBEGINTIME ,@p_PMENDTIME ,@p_CodeCoefficient ,@p_TIMEPART ,@p_PARAMTYPE ,@p_CALLTYPE ,@p_result OUTPUT GO
示例:
ALTER PROCEDURE [dbo].[c_ScoreReport] @index int=0 AS declare @tmpcat varchar(16) if @index = 0 begin --drop table ##table_ScoreReport SELECT f.REPORTDOCTOR,b.MODALITY, COUNT(DISTINCT b.STUDYID) AS "诊断人数", sum( case e.ReportScore when 5 then 1 else 0 end) as "甲", sum( case e.ReportScore when 4 then 1 else 0 end) as "乙", sum( case e.ReportScore when 3 then 1 else 0 end) as "丙", sum( case e.ReportScore when 2 then 1 else 0 end) as "丁", sum( case e.ReportScore when 1 then 1 else 0 end) as "不合格", CONVERT(FLOAT, sum( case ReportScore when 5 then 1 else 0 end))/(COUNT( b.STUDYID)) AS "诊断符合率" into ##table_ScoreReport FROM dbo.TableSeries a,dbo.TableStudies b ,TablePatientInfo c ,dbo.TableExamInfo d,TableImageQuality e,TableReport F where a.REQ_PROCNO = b.REQ_PROCNO and d.SERV_REGNO = b.SERV_REGNO AND f.REQ_PROCNO = b.REQ_PROCNO and c.PATIENTNO =d.PATIENTNO and b.STUDYID = e.StudyID -- and f.CONTENT_DATE > @startdate and f.CONTENT_DATE < @enddate group by b.MODALITY,f.REPORTDOCTOR select * from ##table_ScoreReport union all select '合计','' as MODALITY,sum(诊断人数),sum(甲),sum(乙),sum(丙),sum(丁),sum(不合格),CONVERT(FLOAT, sum(不合格))/sum(诊断人数) from ##table_ScoreReport group by REPORTDOCTOR --with ROLLUP drop table ##table_ScoreReport end else if @index = 1 begin --drop table ##table_ImageScore SELECT f.REPORTDOCTOR,b.MODALITY, COUNT(DISTINCT b.STUDYID) AS "诊断人数", sum( case e.ImageScore when 5 then 1 else 0 end) as "甲", sum( case e.ImageScore when 4 then 1 else 0 end) as "乙", sum( case e.ImageScore when 3 then 1 else 0 end) as "丙", sum( case e.ImageScore when 2 then 1 else 0 end) as "丁", sum( case e.ImageScore when 1 then 1 else 0 end) as "不合格", CONVERT(FLOAT, sum( case ReportScore when 5 then 1 else 0 end))/(COUNT( b.STUDYID)) AS "诊断符合率" into ##table_ImageScore FROM dbo.TableSeries a,dbo.TableStudies b ,TablePatientInfo c ,dbo.TableExamInfo d,TableImageQuality e,TableReport F where a.REQ_PROCNO = b.REQ_PROCNO and d.SERV_REGNO = b.SERV_REGNO AND f.REQ_PROCNO = b.REQ_PROCNO and c.PATIENTNO =d.PATIENTNO and b.STUDYID = e.StudyID --and f.CONTENT_DATE > @startdate and f.CONTENT_DATE < @enddate group by b.MODALITY,f.REPORTDOCTOR select * from ##table_ImageScore union all select '合计','' as MODALITY,sum(诊断人数),sum(甲),sum(乙),sum(丙),sum(丁),sum(不合格),CONVERT(FLOAT, sum(不合格))/sum(诊断人数) from ##table_ImageScore group by REPORTDOCTOR --with ROLLUP drop table ##table_ImageScore end --调用 DECLARE @return_value int EXEC @return_value = [dbo].[c_ScoreReport] @index = 1 --@startdate = N'2020-01-01', --@enddate = N'2024-01-01' SELECT 'Return Value' = @return_value GO --EXEC [c_ScoreReport] @index = 1 --EXEC [c_ScoreReport] 1
示例:单引号、存储过程、动态执行sql
if object_id('GetData','p') is not null drop proc GetData go create proc GetData as declare @className VARCHAR(50) declare @sql varchar(4000) set @sql= 'select ' +'''检查医生'',' --1.声明游标 DECLARE cursor_devices CURSOR FOR select classname from test group by ClassName --2.打开游标 OPEN cursor_devices; --3.读取游标数据 FETCH NEXT FROM cursor_devices INTO @className; --判断是否执行成功,0 表示成功;-1表示失败 WHILE @@FETCH_STATUS = 0 BEGIN set @sql = @sql +''''+ @className + ''',' --再次读取,否则只读取一次 FETCH NEXT FROM cursor_devices INTO @className; END; set @sql= LEFT(@sql,len(@sql)-1) print @sql; exec(@sql); --4.关闭游标 CLOSE cursor_devices; --5.释放游标 DEALLOCATE cursor_devices; go exec GetData
博客内容主要用于日常学习记录,内容比较随意,如有问题,还需谅解!!!