数据库高级02

--一：存储过程
-----查看当前用户、会话、进程的信息
--exec sp_who 用户名|会员ID|active;
----查看指定数据库的所有信息
--exec sp_helpdb 数据库名称
----显示和SqlServer相关的信息
--exec sp_monitor
--1:自定义存储过程
--create procedure 存储过程名字
--参数1 类型1 [=默认值][output]
--as
--begin
--    SQL语句
--end
--求两数之和
create proc p_sum
@a int,
@b int,
@sum int output
as 
begin
   set @sum= @a+@b
end
--执行存储过程  exec 过程名 参数1 ，参数2 [output]
declare @l int;
exec dbo.p_sum 10,20,@l output;
select @l;
go
--案例1：输入一个名字，输出名字+你真棒！
create proc p_name
@name varchar(20),
@name2 varchar(50) output
as
begin
    set @name2=@name+'你真棒！'
end
declare @name3 varchar(50);
exec dbo.p_name '田小宾', @name3 output;
select @name3'结果';
go
--新增存储过程
create proc p_add
@name varchar(50),
@id int output
as 
begin
    set nocount on;-- 关闭受影响行数
    insert into dbo.Positions (Name) values (@name);--插入数据
    set @id=SCOPE_IDENTITY();--插入成功后返回的id
end
declare @a int;
exec dbo.p_add '田小宾的位置',@a output;
select @a;
go
--往评论表新增数据
create proc p_add2
@CommentContent varchar(4000),
@ProductMarks int,
@id2 int output
as
begin
    set nocount on;
    insert into dbo.Comments (CommentContent,ProductMarks) values (@CommentContent,@ProductMarks);
    set @id2=SCOPE_IDENTITY();
end
declare @b int;
exec dbo.p_add2 '挺不错的',10,@b output;
select @b;
go
--修改位置的存储过程
create proc p_update
@CommentContent varchar(50),
@id int
as
begin
    --set nocount on;
    update dbo.Comments set CommentContent=@CommentContent where Id=@id;
    --set @id=SCOPE_IDENTITY();
end
exec dbo.p_update '挺不错的啊',2;
go
--既能修改又能修改
create proc p_addorupdate
@name varchar(50),
@id int
as
begin
    if exists (
       select * from dbo.Comments where Id=@id
    )
    begin
        update dbo.Comments set CommentContent=@name where Id=@id;
    end
    else
    begin
        insert into dbo.Comments (CommentContent) values (@name);
    end
end
exec dbo.p_addorupdate '其实没那么好',5;
go
--删除数据
create proc p_delect 
@id int,
@count int output
as
begin
     delete dbo.Comments where Id=@id;
     set @count=@@ROWCOUNT;
end
declare @a int;
exec dbo.p_delect 2,@a output;
select @a;
go
--传用户名 找出所有的收货地址
create proc p_getaddress
@realname varchar(50)
with encryption  --加密
as
begin
     select a.* from dbo.Address a
	 inner join dbo.Customers c on a.BelongToUserId=c.UserId
     where c.RealName=@RealName
end
exec dbo.p_getaddress '郭聪';
go
--传用户名 获取所有订单
create proc p_getorder
@realname varchar(50)
with encryption
as
begin
    select o.*
    from dbo.Orders o
    inner join dbo.Customers c
    on o.CustomerId=c.UserId
    where c.RealName=@realname;
end

exec dbo.p_getorder '郭聪'
go
--嵌套使用
create proc p_hand
as
begin 
     declare @he int;
     exec dbo.p_sum 10,10,@he output;
     select @he;
end
exec dbo.p_hand
go
--修改存储过程
--alter proc 名字
--参数 类型
--as
--begin
--    SQL语句
--end
alter proc dbo.p_hand
as
begin
     declare @he int;
     exec dbo.p_sum 5,5,@he output;
     select @he;
end
exec dbo.p_hand
go
--删除存储过程
--drop proc 存储过程
drop proc dbo.p_hand
go
--为查询结果产生一列序号
--row_number() over (order by 列名)
select ROW_NUMBER() over(order by Id) '序号',*
from dbo.Product
--exec(sql) 执行字符串拼接的sql
declare @q varchar(8000);
declare @id int;
set @id=100;
set @q='select * from dbo.Product where Id='+CONVERT(varchar,@id)
exec (@q);
--任务一：编写一组对客户表（Customers）进行增，删，改的存储过程
--新增
create proc P_addcustomers
@name varchar(50),
@birthday datetime
as
begin
  insert into dbo.Customers(UserId,RealName,Birthday) 
  values(NEWID(),@name,@birthday);
end
go
--删除
create proc P_sccustomers
@name varchar(50)
as
begin
  delete dbo.Customers where RealName=@name
end
go
--修改
create proc P_xgcustomers
@name varchar(50),
@birthday datetime,
@ssname varchar(50)
as
begin
  update dbo.Customers set 
  RealName=@name,
  Birthday=@birthday
  where RealName=@ssname
end
go
exec dbo.P_addcustomers '商品名2','2021-03-09';         --新增
exec dbo.P_sccustomers '商品名2';                       --删除
exec dbo.P_xgcustomers '修改后','2021-03-09','小鱼';    --修改