存储过程应用及命名规范

1.存储过程

能够快速简便的实现某种功能，

系统存储过程：由数据库管理器创建，实现对数据库管理器、数据库对象的操作，以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;