insert table_b
values(1)
go
insert table_b
values(2)
go
begin tran
print '开始事务!'
update table_a
set x=x+1
where y=100
if @@rowcount=0 or @@error!=0
begin
rollback tran
print '发生错误,没有修改任何数据行.'
return
end
update table_b
set z=z+1
if @@rowcount=0 or @@error!=0
begin
rollback tran
print '发生错误,没有修改任何数据行.'
return
end
commit tran
go
print '结束事务,必须进行提交.'
go
select * from table_a
go
/*7数据库对象的操作权限进行授权管理。不同的用户具有不同的权限*/
sp_addlogin "maimai","654321",maidb,"us_english"
go
sp_adduser "maimai"
go
grant all to maimai
go
sp_helprotect maimai
go
sp_addlogin "mm","654321",maidb,"us_english"
go
sp_addgroup group1
go
sp_adduser "mm","mm",group1
go
grant select
on banji
to group1
go
grant update
on banji
to group1
go
/*8建逐行显示学生表信息的游标*/
declare cur_xueshen cursor
for
select * from xueshen
go
open cur_xueshen
go
fetch cur_xueshen while @@sqlstatus=0
fetch cur_xueshen
go
close cur_xueshen
go
deallocate cursor cur_xueshen
go
/*9将数据库备份为操作系统下面的文件。*/
/*创建转储设备*/
sp_addumpdevice 'disk', mailogdumpdev,"d:\mailogdumpdev.bkp"
go
/*备份数据库*/
dump database maidb to mailogdumpdev
go
/*10将教师表导出到操作系统下面的名字为teacher.out的文件中*/
/*c:\sybase\bin\bcp maidb..banji out mm.out -Usa -p -C*/
/*bcp maidb..banji out c:\mm.out -Usa -P -S401-05 -c -r\\r*/
/*客户段查询*/
/*1按班级查询某门课程的学生成绩。要求显示如下信息：学号、姓名、性别、课程、成绩*/
create procedure pro1
@m_bj varchar(20)
as
select a.xuehao,a.name,a.xb,d.kc_name,b.chengji
from xueshen a,xskc b,banji c,kecheng d
where a.bj_id=c.bj_id and a.xuehao=b.xuehao and b.kc_id=d.kc_id and c.bj_name=@m_bj
return 0
go
pro1 '应用1'
go
/*2按某系、某专业给出教师承担的课程信息要求显示如下信息：教师ID、姓名、性别、职称、课程、计划学时、学分*/
create procedure pro2
@m_xi char(8),@m_zy char(8)
as
select a.js_id,a.js_name,a.xb,a.zhiye,c.kc_name,b.xs,b.cxf
from jiaoshi a,jskc b,kecheng c
where a.js_id=b.js_id and b.kc_id=c.kc_id and @m_xi=a.xi_id and @m_zy=a.zy_id
return 0
go
pro2 '0401','0501'
go
/*3查询某名教师所承担课程的情况*/
create procedure pro3
@m_js varchar(20)
as
select a.js_id,a.js_name,a.xb,a.zhiye,c.kc_name,b.xs,b.cxf,d.bj_name
from jiaoshi a,jskc b,kecheng c,banji d
where a.js_id=b.js_id and b.kc_id=c.kc_id and d.bj_id=b.bj_id and @m_js=js_name
return 0
go
pro3 m1
go
/*4查询某专业课程使用教材情况*/
create procedure pro4
@m_zy varchar(20)
as
select a.zy_id,d.jiaocai
from zhuanye a,jiaoshi b,jskc c,jiaocai d
where a.zy_id=b.zy_id and b.js_id=c.js_id and c.jc_id=d.jc_id and @m_zy=zy_name
return 0
go
pro4 z1
go
/*5查询某个班的班长名字和联系电话*/
create procedure pro5
@m_bj varchar(20)
as
select bj_name,banzhang,phone from banji
where @m_bj=bj_name
return 0
go
pro5 '应用1'
go
/*6查询某系教学秘书的名字和联系电话*/
create procedure pro6
@m_xi varchar(20)
as
select xi_name ,jxms ,phone from xi
where @m_xi=xi_name
return 0
go
pro6 q1
go
/*7查询某专业教师基本情况要求显示如下信息：专业ID、专业名称、教师名字、教师性别、职称*/
create procedure pro7
@m_zy varchar(20)
as
select b.zy_id,b.zy_name,a.js_name,a.zhiye,a.xb from jiaoshi a,zhuanye b
where a.zy_id=b.zy_id and
