1.概要
存储过程 :
是一组为了完成特定功能而编写的 SQL 语句集合,它存储在数据库中,可以通过指定的名称调用执行。存储过程可以接受参数,执行复杂的操作,包括数据验证、流程控制语句(如条件判断和循环)等,并且可以返回结果。
存储过程的优点包括:
性能提升
:存储过程在首次创建时就被编译并存储在数据库中,因此执行速度比动态执行的 SQL 语句快。减少网络流量
:对于需要执行多条 SQL 语句的操作,只需通过一次调用存储过程即可完成,减少了客户端和数据库服务器之间的网络通信。代码重用和封装
:存储过程可以被多个应用程序或多处在应用程序中调用,实现了逻辑的重用和封装。安全性
:可以通过控制用户对存储过程的访问权限来增强数据库的安全性,而不是直接访问数据。
示例:
步骤 1:创建存储过程
假设我们有一个名为 Employees 的表,包含员工的 ID、Name 和 Salary 字段。我们将创建一个存储过程来增加员工的薪水。
这个存储过程名为 IncreaseSalary,接受两个参数:EmployeeID(员工的 ID)和 IncrementAmount(薪水增加的金额)。存储过程的主体是一个 UPDATE 语句,用于增加指定员工的薪水。
CREATE PROCEDURE IncreaseSalary
@EmployeeID INT,
@IncrementAmount DECIMAL(10, 2)
AS
BEGIN
UPDATE Employees
SET Salary = Salary + @IncrementAmount
WHERE ID = @EmployeeID;
END;
步骤 2:调用存储过程
这条命令调用了 IncreaseSalary 存储过程,为 ID 为 1 的员工薪水增加了 500.00。
EXEC IncreaseSalary @EmployeeID = 1, @IncrementAmount = 500.00;
2. 不同数据库使用存储过程的区别
存储过程在不同的数据库管理系统(DBMS)中有一些区别,主要体现在语法、功能特性以及如何处理某些操作上。下面是一些流行数据库系统中存储过程的区别:
例如:
2.1. SQL Server
• 使用 Transact-SQL (T-SQL) 语言编写存储过程。
• 支持复杂的错误处理和事务管理。
• 允许使用表变量和临时表。
• 支持创建带有输入、输出参数和返回值的存储过程。
CREATE PROCEDURE GetEmployeeDetails
@EmployeeId INT
AS
BEGIN
SELECT Id, Name, Position
FROM Employees
WHERE Id = @EmployeeId;
END;
2.2 Oracle
• 使用 PL/SQL 语言编写存储过程。
• 支持包(Packages),允许将相关的存储过程和函数组织在一起。
• 强大的异常处理机制。
• 支持游标和记录类型。
CREATE OR REPLACE PROCEDURE MyProcedure (MyParam IN NUMBER) IS
BEGIN
SELECT * FROM MyTable WHERE ID = MyParam;
END MyProcedure;
2.3 MySQL
• 使用类似于 SQL 的过程语言编写存储过程。
• 支持基本的控制流语句,如 IF、CASE、LOOP、REPEAT 和 WHILE。
• 支持游标和异常处理,但相比于 SQL Server 和 Oracle,可能在某些高级功能上有所限制。
DELIMITER //
CREATE PROCEDURE MyProcedure(IN MyParam INT)
BEGIN
SELECT * FROM MyTable WHERE ID = MyParam;
END//
DELIMITER ;
2.4. PostgreSQL
• 使用 PL/pgSQL 语言编写存储过程,该语言类似于 Oracle 的 PL/SQL。
• 支持事务控制,即存储过程内部可以提交或回滚事务。
• 强大的数据类型和函数支持。
• 支持触发器和复杂的查询操作。
CREATE OR REPLACE PROCEDURE MyProcedure(MyParam INT)
LANGUAGE plpgsql
AS $$
BEGIN
SELECT * FROM MyTable WHERE ID = MyParam;
END; $$
小结
• 语言和语法:不同的数据库系统使用不同的过程语言
,有着不同的语法规则
。
• 功能和特性:某些数据库(如 Oracle 和 PostgreSQL)提供了更复杂的过程编程特性
,包括异常处理
、包
、高级数据类型
等。
• 性能和优化:不同的数据库系统在执行存储过程时的性能和优化策略也有所不同。
在使用存储过程时,了解所使用的数据库系统的特定语法
和功能是非常重要的,以确保存储过程的正确编写和最优性能。
附带WPF调用SQL Server存储过程示例
通过以下步骤在WPF MVVM应用程序中调用存储过程
并显示结果:
定义模型
(Model):创建一个简单的模型来表示数据。创建视图模型
(ViewModel):在视图模型中调用存储过程并填充模型数据。更新视图
(View):在视图中展示数据。
ps: 初学者记得在xaml.cs中绑定对应DataContext
步骤1:定义模型(Model)
public class Employee
{
public int Id { get; set; }
public string Name { get; set; }
}
步骤2:创建视图模型(ViewModel)
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
public class EmployeeViewModel
{
public ObservableCollection<Employee> Employees { get; set; }
public EmployeeViewModel()
{
Employees = new ObservableCollection<Employee>();
LoadEmployees(); // 加载所有员工信息、有参则根据参数执行
}
public void LoadEmployees(int? employeeId = null)
{
string connectionString = "你的数据库连接字符串";
using (var connection = new SqlConnection(connectionString))
{
var command = new SqlCommand("GetEmployeeDetails", connection)
{
CommandType = CommandType.StoredProcedure
};
// 如果employeeId有值,则添加为参数;否则,传递DBNull.Value
command.Parameters.Add(new SqlParameter("@EmployeeId", employeeId.HasValue ? (object)employeeId.Value : DBNull.Value));
connection.Open();
using (var reader = command.ExecuteReader())
{
Employees.Clear(); // 清空当前列表
while (reader.Read())
{
Employees.Add(new Employee
{
Id = Convert.ToInt32(reader["Id"]),
Name = reader["Name"].ToString()
});
}
}
}
}
}
步骤3:更新视图(View)
<Window x:Class="YourNamespace.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns:local="clr-namespace:YourNamespace"
mc:Ignorable="d"
Title="MainWindow" Height="450" Width="800">
<Window.DataContext>
<local:EmployeeViewModel/>
</Window.DataContext>
<Grid>
<ListView ItemsSource="{Binding Employees}">
<ListView.View>
<GridView>
<GridViewColumn Header="ID" DisplayMemberBinding="{Binding Id}" Width="100"/>
<GridViewColumn Header="Name" DisplayMemberBinding="{Binding Name}" Width="300"/>
</GridView>
</ListView.View>
</ListView>
</Grid>
</Window>