SQL中用户定义的存储过程

本文概述

• 建立表格
• 创建用户定义的存储过程。
• 执行存储过程
• 带参数的存储过程
• 修改存储过程
• 删除存储过程
• 通过加密增加安全性
• 总结

本教程涵盖以下主题。

1. 建立表格
   
2. 用户定义的存储过程
   
3. 执行存储过程
   
4. 带参数的存储过程
   
5. 修改存储过程
   
6. 删除存储过程
   
7. 通过加密增加安全性
   

你将使用Microsoft SQL Server作为数据库。本教程中介绍的概念对于大多数关系数据库管理系统都是相同的, 但是语法不同, 尽管此处使用的想法适用于所有数据库。

存储过程是关系数据库管理系统(RDBMS)中SQL语句的块, 通常由程序员, 数据库管理员, 数据分析师编写, 并保存并重新用于多个程序。根据各种RDBMS, 它也可以具有不同的类型。但是, 在任何RDBMS中找到的两个最关键的存储过程如下。

• 用户定义的存储过程
• 系统存储过程

存储过程是准备好的已编译代码, 将其存储或缓存, 然后重新使用。你可以缓存代码以供重用, 这使可维护性变得更加容易, 即不需要多次更改即可维护安全性。

建立表格

现在, 你将如下创建一个名为table_Employees的表。

CREATE TABLE table_Employees
(
    EmployeeId INT PRIMARY KEY NOT NULL, EmployeeFirstName VARCHAR(25) NOT NULL, EmployeeLastName VARCHAR(25) NOT NULL, EmployeeGender VARCHAR(25) NOT NULL, EmployeeDepartmentID INT

)

该表包含有关特定公司员工的信息, 并包含以下列：

1. EmployeeId：这是唯一的主键, 它是整数数据类型, 该值自动递增。
2. EmployeeFirstName：这是员工的名字, 它是字符数据类型, 不应保留为空。
3. EmployeeLast Name：这是员工的姓氏, 它是字符数据类型, 不应保留为空。
4. EmployeeGender：它显示员工的性别, 也是一种字符类型。
5. EmployeeDepartmentId：它是整数数据类型的雇员的ID。

你可以编写查询以将值插入表中。向表添加值的查询是：

    INSERT INTO <TABLE_NAME>(<COLUMNS_OF_TABLE>) VALUES(<CORRESPONDING_VALUES_TO_MATCH_COLUMN>)

将值插入表后, 将创建table_Employees。

创建用户定义的存储过程。

使用存储过程, 你只需编写一次查询, 查询的名称便可以保存并在需要时执行多次。通过执行查询, 你可以创建一个名为Programmabilty-> Stored Procedure的文件夹, 并且有一个名为dbo.uprocGetEmployees的文件。

创建存储过程的语法为：

CREATE PROCEDURE <<<procedure_name>>>
AS
BEGIN

   '''Required SQL Queries'''

END

你可以使用SQL语句创建存储过程, 如下所示。

创建过程procedure_name

创建PROC过程名称

另外, 你需要使用” sp_。”以外的其他命名约定来创建存储过程。示例中的存储过程的名称为：uprocGetEmployees。

执行存储过程

要执行存储过程, 可以使用以下三种方法之一, 然后按以下方式运行它。

• 执行< >
• 执行< >
• < >

在上面的程序中, 你可以使用过程名称uprocGetEmployees并选择它以执行查询。

带参数的存储过程

存储过程可以接受单个和多个参数。如果你先了解多个参数, 则可以轻松理解单个参数。

用于创建具有多个参数的存储过程的语法为：

CREATE PROCEDURE <<procedure_name>> <<procedure_parameter>>
AS
    BEGIN
            <<sql_query>>
    END

该示例显示下面的文件夹中有一个更改。

雇员->可编程性->存储过程-> dbo.uprocGetEmployessGenderAndDepartment

这里的概念与Python, Java等通用编程语言中的函数非常相似。上面的程序具有名为” uprocGetEmployeesGenderAndDepartment”的函数或过程名称, 其参数名称为” @EmployeeGender”和” @EmployeeDepartmentId”。通过将所需的值传递给我们的过程, 可以通过以下两种方法之一完成此过程, 下面对此进行了说明。

该程序中的参数是@EmployeeGender和@EmployeeDepartmentId, 它们通过将值传递给参数而被”调用”。

可以通过以下两种方式之一指定该值。你可以使用它们之一来使用多个参数执行存储过程。

通过在查询中指定参数名称并传递所需的值：

通过与查询的存储过程参数相匹配的位置, 并将所需的值传递给该参数：

修改存储过程

你还可以使用ALTER PROCEDURE命令修改存储过程。

    ALTER PROCEDURE uprocGetEmployeesGenderAndDepartment
@EmployeeGender nvarchar(25), @EmployeeDepartmentId int
BEGIN
    SELECT EmployeeFirstName, EmployeeGender, EmployeeDepartmentId FROM table_Employees WHERE
    EmployeeGender = @EmployeeGender AND EmployeeDepartmentId = @EmployeeDepartmentId
END

删除存储过程

可以使用以下命令快速删除存储过程：

删除程序<>删除程序<>

通过加密增加安全性

左下图所示的锁定符号表示存储过程已加密, 可确保只有授权人员才能访问它。

总结

你刚刚学习完用户存储过程的基础知识, 并且所涉及的主题在概念上与任何RDBMS相似。你可以通过参加srcmini的中级SQL Server课程来了解更多信息。另外, 以下”存储过程”教程也将很适合你的学习。