SQL Server-存储过程

• 目录
  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

• 补充

1. [W3Cschool]https://www.w3cschool.cn/sql/sql-storage.html