存储过程

declare @bian1 int ,@bian2 int
set  @bian1=1 
set  @bian2=2
select @bian1+@bian2

---------带输入参数的返回值
create proc JiaFa
@a int ,--输入参数: 逗号隔开，放在as和存储过程名之间
@b int 
as
  return @a+@b
go
declare @a int
exec @a=JiaFa 34,55      --但参数的存储过程 需要在存储过程名后输入参数，空格隔开 参数之间
print @a

create proc  pandingshuzi
@a int       --存储过程的输入参数   输入参数不用定义
as 
     if @a>=0 and @a<10
     begin                     --begin end 相当于 括号
            return 1
     end
     else if  @a>=10 and @a<100
     begin 
            return 2
     end
      else 
     begin 
            return -1
     end
go        --go可写可不写
declare @a int 
exec @a = pandingshuzi 101
print @a



alter proc he    
@num int       --这是你输入的数
as
begin
    declare @sum int,@i int    --定义
    set @sum=0        --赋值
    set @i=0
        while @i<= @num        
    begin
        set  @sum=@i+ @sum           --算出结果并赋值
        set @i=1+@i
    end
    return @sum        --返回值
end
go
declare @he11 int
exec @he11=he 10
print @he11


 


 

 

create database university
create table Student
(
  Sno varchar(50)primary key not null,        --学号（主码）
  Sname varchar(50) not null,        --学生姓名
  Ssex varchar(50)not null,        --学生性别
  Sbirthday datetime,     --学生出生年月
  Class varchar(50)       --学生所在班级
)
insert into Student values('109','曾华动','女','1977-09-01','95033')
insert into Student values('108','曾华','女','1977-09-01','95033')
insert into Student values('105','匡明','男','1975-10-02','95031')
insert into Student values('106','曾刚','男','1997-09-01','95033')
insert into Student values('107','匡家','女','1955-10-02','95031')
create table Score
(
 Sno varchar(50)references student(Sno)not null , --学号（外码）
 Cno varchar(50)not null , --课程号（外码）
 Degree Decimal(4,1)   --成绩
 primary key (Sno)
)
select*from student
select*from score

---------常用系统存储过程有：

exec sp_databases; --查看数据库
exec sp_tables;        --查看表
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--约束
exec sp_stored_procedures;    --查看master
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
exec sp_renamedb myTempDB, myDB;--更改数据库名称
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助，查询数据库信息
exec sp_helpdb master;

--表重命名
exec sp_rename 'Stud','Student'
select *from student
--列重命名
exec sp_rename 'stud.sname', 'Sname';
exec sp_help 'stud';
--重命名索引
exec sp_rename N'student.idx_cidd', N'Sno';
exec sp_help 'student';

--查询所有存储过程
select * from sys.objects where type = 'P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';


--创建不带参数存储过程
      drop proc proc_get_student
 go 
 create proc proc_get_student
 as
   select*from student
   
   --调用、执行存储过程
exec proc_get_student;

--修改存储过程
alter proc proc_get_student
as
select * from student;

--带参存储过程
    drop proc proc_find_stu
go
create proc proc_find_stu(@startId int, @endId int)
as
    select * from student where sno between @startId and @endId
go

exec proc_find_stu 106, 108;

--带通配符参数存储过程
    drop proc proc_findStudentByName
go
create proc proc_findStudentByName(@name varchar(20) = '%曾%', @nextName varchar(20) = '%')
as
    select * from student where sname like @name and sname like @nextName;
go

exec proc_findStudentByName;
exec proc_findStudentByName '%';

-- 带输出参数存储过程
 drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
    @sno int, --默认输入参数
    @name varchar(20) out, --输出参数
    @class varchar(20) output--输入输出参数
)
as
    select @name = sname, @class = class  from student where sno = @sno ;
go

-- 
declare @sno int,
        @name varchar(20),
        @class varchar(20);
set @sno =105; 
exec proc_getStudentRecord @sno, @name out, @class out;
select @name, @class;
--print @name + '#' + @class;


--WITH RECOMPILE 不缓存存储过程
    drop proc proc_temp
go
create proc proc_temp
with recompile
as
    select * from student;
go

exec proc_temp;



--WITH ENCRYPTION  加密存储过程
    drop proc proc_temp_encryption
go
create proc proc_temp_encryption
with encryption
as
    select * from student;
go

exec proc_temp_encryption;
exec sp_helptext 'proc_temp';
exec sp_helptext 'proc_temp_encryption';

　　

转载于:https://www.cnblogs.com/Mr-xue/p/4461001.html