好记性不如烂笔头,以备以后查看。
一,首先如前人所说,存储过程的优点有如下几点:
1,存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译故而提高数据库执行速度。
2,当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3,存储过程可以重复使用,可减少数据库开发人员的工作量
4,安全性高,可设定只有某此用户才具有对指定存储过程的使用权
5,降低网络负荷
二,语法
CREATE PROC[ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
部分参数:
[;Number]: 用于对同名过程进行分组的可选整数。使用一个 DROP PROCEDURE 语句可将这些分组过程一起删除。
三,简单的存储过程
这里假设有张Students表,则一个可用的存储过程为:
Create Procedure Q_Students
AS
Select * From Students;
即创建了名为Q_Students的存储过程。
在程式中可通过Exec Q_Students來执行。
四,部分代码
--修改存储过程
Alter Procedure Q_Students
As
Select S_Id as '編號',S_Name as '姓名',S_Sex as '性別',S_Age as '年齡',S_CId as '班別' From Students;
go
-- 输入型参数
Alter procedure Q_Students
@Q_Name varchar(20)
AS
Select * From Students where S_Name=@Q_Name
go
Declare @Q_Name varchar(20);
Set @Q_Name='劉偉';
-- 输出型参数
alter procedure Q_Students
@Q_Id varchar(10), @Pout varchar(20) output
AS
Select @Pout=S_Name From Students where S_Id=@Q_Id
go
Declare @Q_Id varchar(10)
Set @Q_Id='1';
--procedure with Schema
Create Schema Schema1
go
Alter procedure Schema1.Q_Students
AS
Select * from Students
五,部分常用的系统存储过程
--列出SQL Server实例中的数据库
sp_databases
--返回SQL Server、数据库网关或基础数据源的特性名和匹配值的列表
sp_server_info
--返回当前环境中的存储过程列表
sp_stored_procedures
--返回当前环境下可查询的对象的列表(任何可出现在 FROM 子句中的对象)
sp_tables
select * from sysobjects
---添加或更改SQL Server登录的密码。
sp_password @new=null,@loginame='sa'
- -将登录 Victoria 的密码更改为 ok。
EXEC sp_password NULL, 'ok', 'Victoria'
--将登录 Victoria 的密码由 ok 改为 coffee。
EXEC sp_password 'ok', 'coffee'
--更改配置选项
use master
go
exec sp_configure 'recovery interval','3'
reconfigure with override
go
--查看数据库文件
sp_helpdb tmp
use tmp
go
sp_helpfile
go
--分离数据库
use master
go
sp_detach_db tmp
go
- -sp_helpdb tmp --error
--go
--附加数据库
sp_attach_db tmp,@filename1='E:/DB/tmp_dat.mdf',@filename2='E:/DB/tmp_log.ldf'
go
sp_helpdb tmp
go
例如:
Create Proc S1 ;1
AS
Select * From Students
Go
Create Proc S1 ;2
As
Select * From Clazz
Go
创建完毕了两个存储过程。它们在同一个组S1里,如果执行Exec S1 则存储过程默认执行 Exec S1 ;1 。如果我们想得到所有据点信息则需要执行Exec S1 ;2。当我们要删除存储过程的时候,只能执行Drop Exec S1 则该组内所有的存储过程被删除。
五 存储过程中使用游标
--一个标准的游标实例
Alter procedure Name_Cursor
AS
Declare @Temp varchar(20)
--声明一个游标
Declare Names Cursor FOR Select S_Name From Students
--打开游标
open Names
--从游标中取数据并存储到变量中
fetch NEXT from Names Into @Temp
--如果查询成功则循环执行
while @@Fetch_Status =0
Begin
print('姓名:'+@Temp);
--取下一条数据
fetch NEXT From Names Into @Temp
end
--关闭游标
close Names
--释放游标
Deallocate Names
六,值得记忆的SQL语句
- 说明:复制表(只复制结构,源表名:a 新表名:b)
select * into b from a where 1<>1 - 说明:拷贝表(拷贝数据,源表名:a 目标表名:b)
insert into b(a, b, c) select d,e,f from b;