存储过程
能够快速简便的实现某种功能,
系统存储过程:由数据库管理器创建,实现对数据库管理器、数据库对象的操作,以sp_开头
扩展存储过程
允许使用其他编程语言创建外部存储过程,以xp_
用户存储过程
用户自行编写,对某张表或数据库进行快速操作的存储过程
调用存储过程
exec 存储过程名 参数值
系统存储过程
sp_databases 列出服务器上的所有数据库信息,包括数据库名和数据大小
sp_helpdb 报告有关指定数据库或所有数据库信息
sp_renamedb 更改数据库名
sp_tables 返回当前环境下可查询的表信息
sp_columns 返回某个视图或表的信息,包括数据类型和长度
sp_help 查看某个数据库对象的信息,如列名、主键、约束、外键、索引等
sp_helpconstraint 查看某个表的约束
sp_helpindex 查看某个表的索引
sp_stored_procedures 显示存储过程列表
sp_password 添加或修改登录账户的密码
sp_helptext 显示默认值、未加密的存储过程、用户自定义过程、触发器或视图实际文本
扩展存储过程
应用举例:
EXEC xp_cmdshell ‘mkdir D:\shopping’, NO_OUTPUT;
--创建文件夹D:\shopping
EXEC xp_cmdshell ‘dir D:\shopping\’
使用以下语句来启用:
EXEC sp_configure ‘show advanced options’,1
\\显示高级配置信息
GO
RECONFIGURE --重新配置
GO
EXEC sp_configure ‘xp_cmdshell’,1 --打开xp_cmdshell
GO
RECONFIGURE --重新配置
GO
用户存储过程
完整的存储过程包括
输入参数和输出参数
执行的T-sql语句
存储过程返回值
①无参存储过程
语法格式:
CREATE PROC [EDURE] <过程名>
AS
<T-SQL语句> ----包括增删改查
实例
create proc name
as
select * from User_Info
where
User_pwd='123.com'
执行name存储过程
exec name
②带入参存储过程
在调用存储过程时需要手动输入条件称为入参存储过程。
语法格式:
CREATE PROC <过程名1>(<入参1>[,<入参2>]…)
AS
<过程化SQL块>
实例
create proc loginname1(@login varchar(50))
as
select * from User_Info
where
User_login_name=@login
exec loginname1 'zhaosi'
③带出参存储过程
在调用出参存储过程时直接显示出参结果称为出参存储过程。
语法格式:
CREATE PROC <过程名>(<入参1>,<出参1 OUTPUT> )
AS
<过程化SQL块>
实例:
create proc loginname1(@type varchar(50),@num int output)
as
select @num=count(*) from User_Info
where
User_login_name=@type;
declare @num int
declare @type varchar(50)='zhaosi'
exec loginname2 @type,@num output
select @type,@num
修改存储过程
将创建命令改成alter即可
---查看存储过程属性信息、参数与数据类型---
sp_help <存储过程名>
sp_help loginname1
删除存储过程
语法格式:
DROP PROC <存储过程名>
drop proc loginname1
触发器
用户对表的增删改查会激活触发器执行相应动作
常用触发器
DDL触发器:响应事件包括create、alter、drop,
DML触发器:insert、update、delete
DML触发器又分为alter触发器、instead of触发器
alter属于后置触发器,只适用于基本表
instead of是前置触发器,适用于表和视图
DML触发器的类型
delete:删除表中数据触发
insert:插入表中数据触发
update:更新表中数据触发
创建触发器
语法格式:
CREATE TRIGGER <触发器名>
ON <数据表 | 视图 | DATABASE>
<AFTER | FOR | INSTEAD OF> <delete | insert | update>
AS
<触发动作体>
实例
create trigger ppp
on User_Info
for insert
as
select * from User_Info;
修改触发器
重命名触发器
exec sp_rename <原触发器名>,<新触发器名>
exec sp_rename ppp,ooo
修改触发器内容
ALTER TRIGGER <触发器名>
ON <数据表 | 视图 | DATABASE>
<AFTER | FOR | INSTEAD OF><触发事件>
AS
<触发动作体>
禁用触发器
语法格式:
---禁用DML触发器---
DISABLE TRIGGER <触发器名> ON <表 | 视图>
---禁用DDL触发器Goods_tring---
DISABLE TRIGGER Goods_tring ON DATABASE
disable trigger ooo on User_Info
删除触发器
语法格式:
DROP TRIGGER <触发器名>
drop trigger ooo
事务
是访问并可能操作各种数据的一个数据库操作序列,要么全部执行,要么全部不执行,不可分割。
事务的特性
原子性:事务中所有操作是不可分割的,要么全部执行,要么全部不执行
一致性:几个并行执行的事务,并行执行的结果必须与按照某一顺序串行执行结果一致
夺隔离性:事务的执行不受其他事务的干扰,系统执行的中间结果对其他事务必须是透明的
夺持久性:对于任意已提交的事务,系统必须保证该事务对数据库的改变不丢失
数据库事务模型:
显式事务:称为用户自定义事务,是指显式的方式定义开始和结束的事务,当使用start transaction和commit语句时表示显式事务
隐式事务:指每一条数据操作都自动成为一个事务,事务的开始是隐式的。
自动事务:指能够自动开启事务并且能够自动结束事务,如果没有出现异常则自动提交,如果执行错误则自动回滚
Windows SQL Server数据库默认使用的就是隐式事务。
begin transaction:开启事务
commit transaction:提交事务
rollback transaction:回滚事务
实例:
开启显示事务,开启一个事务,插入一条数据,然后回滚。
set implicit transactions off; \\可忽略
go
begin transaction;
go
insert into User_Info
(User_login_name,User_pwd)
values
('chou','123456')
rollback transaction;