《SQL必知必会》第十九课使用存储过程创建和使用存储过程的基本语法

最新推荐文章于 2024-08-02 10:43:54 发布

堵心好似八局

最新推荐文章于 2024-08-02 10:43:54 发布

阅读量607

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/weixin_43806252/article/details/111976861

版权

MySQL 专栏收录该内容

22 篇文章 0 订阅

订阅专栏

第十九课使用存储过程

#什么是存储过程
#为什么使用存储过程
#如何使用存储过程
#创建和使用存储过程的基本语法
#存储过程的执行和创建的语法以及使用存储过程的一些方法

一、存储过程

#迄今为止，使用的大多数SQL语句都是针对一个或多个表的单条语句
#并非所有操作都这么简单，经常会有一些复杂的操作需要多条语句才能完成
#存储过程就是为以后使用而保存的一条或多条SQL语句
#可将存储过程视为批文件，虽然它们的作用不仅限于批处理。
#MySQL 5支持存储过程

二、为什么要使用存储过程

#使用存储过程的理由：

通过把处理封装在容易使用的单元中，简化复杂的操作
由于不要求反复建立一系列处理步骤，因而保证了数据的一致性和完整性。如果所有开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
简化对变动的管理。如果表名、列名或业务逻辑有变化，那么只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据的讹误的机会。
因为存储过程通常以编译过的形式存储，所以DBMS处理命令所需的工作量少，提高了性能。
存在一些只能用在单个请求中的SQL元素和特征，存储过程可以使用它们来编写功能更强更灵活的代码

#使用存储过程有3个主要的好处： 简单、安全、高性能

#将SQL代码转换为存储过程前，也必须知道它的缺陷：

不同的DBMS中的存储过程语法有所不同。事实上，编写真正的可移植存储过程几乎不可能。不过存储过程的自我调用（名字及数据如何传递）可以相对保持可移植。因此，若需要移植到别的DBMS，至少需要客户端应用代码不需要变动
一般来说，存储过程的编写比基本SQL语句复杂，需要更高的技能，更丰富的经验。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。
尽管有这些缺陷，存储过程还是非常有用的，并且应该使用。多数DBMS都带有用于管理数据库和表的各种存储过程。

注意1：
不能编写存储过程？依然可以使用：

大多数DBMS将编写存储过程所需要的安全和访问权限与执行存储过程所需的安全和访问权限区分开来
即使你不能（或不想）编写自己的存储过程，也仍然可在适当的时候执行别的存储过程

三、执行存储过程

#存储过程的执行比编写要频繁的多
#执行存储过程的SQL语句很简单，即EXECUTE
#EXECUTE接受存储过程名和需要传递给它的任何参数

【1】EXCUTE Addnewproduct ('JTS01', 'Stuffed Eiffel Tower', 6.49, 'plush stuffed toy with the next la tour eiffel in red white and blue' );
#执行一个名为addnewproduct的存储过程，将一个新产品添加到product表中
#andnewproduct有四个参数，分别是：供应商id（vendors表的主键），产品名，价格和描述。
#这四个参数匹配存储过程中4个预期变量（定义为存储过程自身的组成部分）
#此存储过程将新行添加到Products表，并将传入的属性赋给相应的列
#在Products表中还有另一个需要值的列prod_id列，它是这个表的主键。这个值不作为属性传递给存储过程，要保证恰当生成此ID，最好是使得生成此ID的过程自动化（而不是依赖于最终用户的输入）

存储过程所完成的工作：

验证传递的数据，保证所有 4 个参数都有值
生成用作主键的唯一 ID
将新产品插入Products表，在合适的列中存储生成的主键和传递的数据

对于具体的DBMS，可能包括以下的执行选择：

参数可选，具有不提供参数时的默认值
不按次序给出参数，以“参数=值”的方式给出参数值
输出参数，允许存储过程在正执行的应用程序中更新所用的参数
用SELECT语句检索数据
返回代码，允许存储过程返回一个值到正在执行的应用程序

四、创建存储过程

#存储过程的编写很重要

【2】CREATE PROCEDURE MailingListCount ( ListCount OUT INTEGER ) IS v_rows INTEGER; BEGIN SELECT COUNT(*) INTO v_rows FROM Customers WHERE NOT cust_email IS NULL; ListCount := v_rows; END;
#Oracle版本
#对邮件发送清单中具有邮件地址的顾客进行计数
#这个存储过程有一个名为ListCount的参数，此参数从存储过程返回一个值而不是传递一个值给存储过程。关键字OUT用来指示这种行为
#Oracle 支持IN（传递值给存储过程）、OUT（从存储过程返回值，如这里）、INOUT（既传递值给存储过程也从存储过程传回值）类型的参数
#存储过程的代码括在BEGIN和END语句中，这里执行一条简单的SELECT语句
#检索具有邮件地址的顾客。然后用检索出的行数设置ListCount（要传递的输出参数）

【3】var ReturnValue NUMBER EXEC MailingListCount(:ReturnValue); SELECT ReturnValue;
#调用 Oracle 例子
#这段代码声明了一个变量来保存存储过程返回的任何值，然后执行存储过程，再使用SELECT语句显示返回的值

【4】CREATE PROCEDURE MailingListCount AS DECLARE @cnt INTEGER SELECT @cnt=COUNT(*) FROM Customers WHERE NOT cust_email IS NULL; RETURN @cnt;
#此存储过程没有参数
#调用程序检索SQL Server的返回代码支持的值
#用DECLARE语句声明了一个名为@cnt的局部变量（SQL Server中所有局部变量名都以@起头）。然后在SELECT语句中中使用这个变量，让它包含COUNT()函数返回的值
#用RETURN @CNT语句来将计数返回给调用程序

【5】DECLARE @ReturnValue INT EXECUTE @ReturnValue = MailingListCount; SELECT @ReturnValue;
#调用SQL Server的例子
#声明一个变量来保存存储过程返回的任何值
#然后执行存储过程，再使用SELECT语句显示返回的值

【6】CREATE PROCEDURE NewOrder @cust_id CHAR(10) AS --Declare variable for order number DECLARE @order_num INTEGER --Get current highest order number SELECT @order_num=MAX(order_num) FROM Orders --Determine next order number SELECT @order_num=@order_num+1 --Insert new order INSERT INTO Orders(order_num,order_date,cust_id) VALUES(@order_num<GETDATR(),@CUST_id) --Return order number RETURN @order_num;
#在Orders表中插入一个新订单
#仅适用于SQL Server，但它说明了存储过程的某些用途和技术
#它只有一个参数，即下订单的客户的ID。其他两个表列，订单号和订单日期，在存储过程中自动生成
#代码首先声明一个局部变量来存储订单号。
#检索当前最大订单号（使用MAX()函数）并增加1(使用SELECT语句）
#用INSERT语句插入由新生成的订单号、当前系统日期（用GETDATE()函数检索）和传递的客户ID组成的订单
#用RETURN @order_num返回订单号（处理订单物品需要它）
#此代码加了注释，在编写存储过程时应该多加注释

注意2：
注释代码：
[1]应该注释所有代码，存储过程也不例外
[2]增加注释不影响性能，因此不存在缺陷（除了增加编写时间外）
[3]注释代码的好处很多，包括使用别人（以及你自己）更容易理解和更安全修改代码
[4]对代码进行注释的标准方式是在之前放置 --（两个连字符）
[5]有的DBMS还支持其他的注释语法，不过所有DBMS都支持 --，因此在注释代码时最好都使用两个连字符这种语法

【7】CREATE PROCEDURE NewOrder @cust_id CHAR(10) AS -- Insert new order INSERT INTO Orders(cust_id) VALUES(@cust_id) --Return order number SELECT order_num=@@IDENTITY;
#此存储过程也在Orders表中创建一个新订单
#由DBMS生成订单号。大多数DBMS都支持这种功能
#SQL Servre中称这些自动增量的列为标识字段（identity field),而其他DBMS称之为自动编号（auto number)或序列（sequence)
#传递给此过程的参数也是一个，即下订单的客户ID
#订单号和订单日期没有给出，DBMS对日期使用默认值（GETDATE（）函数），订单号自动生成
#为得到这个自动生成的ID，在SQL Server上可在全局变量@@IDENTITY中得到，它返回到调用程序（这里使用SELECT语句）

#借助存储过程，有许多不同的方法完成相同的工作
#不过，所选择的方法受到使用的DBMS特性的制约

EXCUTE Addnewproduct ('JTS01',
				'Stuffed Eiffel Tower',
				6.49,
				'plush stuffed toy with the next la tour eiffel in red white and blue'	
);

CREATE PROCEDURE MailingListCount (
ListCount OUT INTEGER
)
IS
v_rows INTEGER;
BEGIN
SELECT COUNT(*) INTO v_rows
FROM Customers
WHERE NOT cust_email IS NULL;
ListCount := v_rows;
END;
--Oracle版本
var ReturnValue NUMBER
EXEC MailingListCount(:ReturnValue);
SELECT ReturnValue;

CREATE PROCEDURE MailingListCount
AS 
DECLARE @cnt INTEGER
SELECT @cnt=COUNT(*)
FROM Customers
WHERE NOT cust_email IS NULL;
RETURN @cnt;
--SQL Server版本

DECLARE @ReturnValue INT
EXECUTE @ReturnValue = MailingListCount;
SELECT @ReturnValue;
--调用SQL Server的例子

CREATE PROCEDURE NewOrder @cust_id CHAR(10)
AS 
--Declare variable for order number
DECLARE @order_num INTEGER
--Get current highest order number
SELECT @order_num=MAX(order_num)
FROM Orders
--Determine next order number
SELECT @order_num=@order_num+1
--Insert new order
INSERT INTO Orders(order_num,order_date,cust_id)
VALUES(@order_num<GETDATR(),@CUST_id)
--Return order number
RETURN @order_num;

CREATE PROCEDURE NewOrder @cust_id CHAR(10)
AS 
-- Insert new order
INSERT INTO Orders(cust_id)
VALUES(@cust_id)
--Return order number
SELECT order_num=@@IDENTITY;