SQL存储过程

建立

1
2
3
4
5
6
7
create procedure lianxi66
@arel char(20)
@x1 int output,@x2 float output
as
select @x1=count(*),@x2=avg(年龄)
from S
where 所在系[email protected]

引用

1
2
3
4
declare @ok int, @ck float
exec lianxi66 "会计系",@ok output,@ck output
print @ok
print @ck

建立

1
2
3
4
5
6
7
CREATE PROCEDURE lianxi3511

@varl char(10), @var3 int output
as
select @var3=S.年龄
frоm ѕ
where s.学号[email protected]

引用

1
2
3
Declare @res int
Execute liarixi3511 '101101', @res output
Print @res

建立

1
2
3
4
CREATE PROCEDURE lianxi2511
@var1 int, @var2 int, var3 int output
As
Set @var3 = @vari*@var2

引用

1
2
3
4
5
Declare @res int
Execute liarxi2511 5, 7, @res output
Print @res

exec liarxi2511 3,4

建立

1
2
3
4
5
6
7
8
9
CREATE PROCEDURE lianxi1511
@student_no char (10),
@course_no char (20)
AS
SELECT S.姓名, S.学号,SC.课程号,C.课程名, SC.成绩
FROM S JOIN SC  ON S.学号=SC.学号
INNER JOIN C  ON C.课程号=SC.课程号
WHERE S.学号[email protected]_no
ANO SC.课程号[email protected]_no

引用

1
exec lianxi1511 '101101','101'

习题

若输入的学号存在则删除

  • 创建
    1
    2
    3
    4
    5
    6
    7
    8
    9
    create procedure del_cno1
    @cno char(20)
    as
    begin tran t1 --事件t1开始
    delete from kcgx  where 课程号[email protected]
    if exists (select  * from xkgx where 课程号[email protected])
    rollback tran t1
    else
    commit tran t1
  • 引用与验证
    1
    2
    3
    exec del_cno1 '305'
    select * from xkgx
    select * from kcgx

    存储过程使用事务

    实例
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    Create Procedure  MyProcedure
    AS
    Begin
    Set    NOCOUNT    ON;
    Set XACT_ABORT ON;
    begin   tran  ok  --开始一个事务OK
    delete  from  rxqz  where qz=   'rx015 ' --删除数据
    save   tran  bcd   --保存一个事务点命名为bcd
    update  sz  set   name='李丽s' where name= '李丽'--修改数据
    if  @@error<>0  --判断修改数据有没有出错
    begin --如果出错
    rollback   tran  bcd  -- 回滚事务到BCD 的还原点
    commit   tran  ok  --提交事务
    end
    else  --没有出错
    commit  tran ok --提交事务
    End
1
2
3
4
5
6
7
8
create proc 1
@si chark20),@pi char(20),@Ji chark20),@shu int
insert into SPJ values(@si,@pi,@Ji,@shu)
bdgin tran t1
if exists (select * from p where [email protected]Pi)
commit tran t1
else
rollback tran t1