007 数据库学习笔记--试图 + PROC

试图：

试图：虚拟表，由一个或多个表通过查询而定义出来的。将查询定义保存起来，实际不包括数据。

与表的区别：表是用于存储数据的地方；试图存储的查询语句(索引试图除外)；

试图作用：简化查询，增加数据的保密性，安全性上得到保障；

试图缺点：只是简化查询，并不提高查询速度；增加维护成本；

视图分类：

标准试图：存储查询定义，没有存储数据
索引试图：被具体化的试图，创建了索引，显著提高查询性能，聚合了很多行的查询，不太适合经常更新基本数据集(不能删除数据，删除数据导致基本表中数据删除)
分区试图：一台或多台服务器之间水平连接一组成员表的分区数据

标准视图如下所示：

--新增视图
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 '王%'

存储过程实际应用示例：

100

101

102

103

104

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

执行调用：

--变量声明<br>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