-
目录
1、概念
2、常用系统存储过程
3、创建、修改、删除存储过程
4、不带参数的存储过程
5、带参数的存储过程
6、使用输出
7、使用返回值
8、补充链接 -
存储过程
一组实现特定功能的SQL语句。类似程序设计语言,包括数据类型、流程控制、输入输出、函数。
1、优点:
❶提高数据库执行效率,存储过程只在创建时编译,SQL语句每次查询时都编译一次;
❷自动化处理,当对数据库进行复杂操作时,可将复杂操作用存储过程封装起来,与数据库提供的事务处理结合使用;
❸降低网络的通信量,提升网络的通信速率。可使用一个存储过程将大量T-SQL语句封装。
❹存储过程可复用,提升开发效率。
❺安全,可设置存储过程使用权限。
2、一般存储过程应用:配置安全帐户 建立链接服务器 创建一个数据库维护计划 创建全文检索目录 添加远程登录 配置复制 设置调度作业 ...
-
常用系统存储过程
EXEC sp_databases --查询数据库
EXEC sp_tables --查询表
EXEC sp_columns basicInfo --查询列
EXEC sp_helpIndex basicInfo -- 查询索引
EXEC sp_helpConstraint 姓名统一表 -- 查看约束
EXEC sp_stored_procedures -- 查询存储过程
EXEC sp_helptext 'sp_tables'; -- 查看存储过程创建、定义语句
EXEC sp_rename basicInfo3, basicInfo2; -- 修改表名、列名、索引名
EXEC sp_renamedb test_restore, test_restore1; -- 修改数据库名
exec sp_defaultdb 'master', 'myDB'; -- 修改登录名的默认数据库
EXEC sp_helpdb; -- 数据库帮助,查询数据库信息
EXEC sp_helpdb master; -- 查询指定数据库信息
- 创建/修改/删除存储过程
-- 语法
-- 创建/修改存储过程
CREATE|ALTER PROC | PROCEDURE pro_name
[{@参数数据类型} [=默认值] [output],
{@参数数据类型} [=默认值] [output],
....
]
AS
SQL_statements
-- OR
CREATE|ALTER PROCEDURE|PROC pro_name AS
...
-- OR
CREATE|ALTER PROCEDURE|PROC pro_name @{Param Name} {data type} AS
DROP PROC proc_name
ALTER PROC proc_name ... AS ...
...
- 不带参数存储过程创建
-- eg.
CREATE PROC pro_basicInfo
AS
SELECT * FROM basicInfo ORDER BY Id DESC
GO
EXEC pro_basicInfo
-- eg.
ALTER PROC pro_basicInfo
AS
SELECT * FROM basicInfo ORDER BY Id
GO
EXEC pro_basicInfo
-- eg.
IF (EXISTS (SELECT * FROM sys.objects WHERE name='latest'))
DROP PROC latest
GO
CREATE PROC pro_latest100_basicInfo1
AS
SET ROWCOUNT 100
SELECT * FROM basicInfo ORDER BY Id DESC
GO
EXEC pro_latest100_basicINfo1
- 带输入参数存储过程
-- eg.
CREATE PROC latest @count int
AS
SET ROWCOUNT @count
SELECT * FROM basicInfo
ORDER BY Id DESC
GO
EXEC latest @count=10
IF EXISTS(SELECT * FROM sys.objects WHERE name='stuProc')
DROP PROC stuProc
GO
CREATE PROC stuProc @ch varchar(20), @am varchar(20)
AS
SELECT * FROM channel
WHERE port=@ch AND AM=@am
GO
EXEC stuProc @ch='Y', @am='鲁'
-- eg.
- 使用输出参数
IF EXISTS (SELECT * FROM sys.objects WHERE name='stuProc')
DROP PROC stuProc
GO
CREATE PROC stuProc
@am varchar(20), @port varchar(20) OUTPUT
AS
SELECT @port=端口 FROM channel WHERE AM=@am
GO
DECLARE @am varchar(20), @port varchar(20)
SET @am='鲁'
EXEC stuProc @am, @port output
PRINT @port
-- SELECT @port
- 使用返回值
-- RETURN
IF EXISTS (SELECT * FROM sys.objects WHERE name='stuProc')
DROP PROC stuProc
GO
CREATE PROC stuProc
@name varchar(20)
AS
IF EXISTS (SELECT AM FROM channel WHERE AM=@name)
RETURN 1
ELSE
RETURN 0
GO
DECLARE @result varchar(20)
EXEC @result=stuProc @name='a'
IF @result=1
print('存在')
ELSE
print('不存在')
GO
- 补充