SQL SERVER视图调用存储过程 sqlyog调用存储过程

转载

mob64ca141677f9 2023-10-29 21:56:25

文章标签 SQL SERVER视图调用存储过程存储过程数据 SQL 文章分类 SQL Server 数据库

1、存储过程

方法（函数），不过它是SQL世界中的方法。

考虑包含业务规则在内的智能处理。封装操作的好处就不过多说明，无非是简化，复用，降低耦合等，同时，它还具有更高的性能。

考虑这种业务情况，现在你需要获得订单总额，但同时需要增加营业税，且只针对某些顾客，那么你需要：

获得基本的订单总额
将营业税有条件地添加到合计中
返回合计

1.1 基本语句

先看基本的语句，然后再看示例，就豁然开朗了：

--创建存储过程CREATE PROCEDURE<存储过程的名称>(<变量的类型定义>)BEGIN <执行操作>END;--执行存储过程CALL <存储过程的名称>(<@变量名>);--删除存储过程DROP PROCEDURE <存储过程的名称>;

1.2 创建

然后，根据刚才我们说到的返回包含营业税的订单总额，创建如下存储过程：

-- Name: ordertotal-- Parameters: onumber = order number-- taxable = 0 if not taxable, 1 if taxable-- ototal = order total variableCREATE PROCEDURE ordertotal( IN onumber INT, IN taxable BOOLEAN, OUT ototal DECIMAL(8, 2)) COMMENT 'Obtain order total, optionally adding tax'BEGIN --Declare variable for total DECLARE total DECIMAL(8, 2); --Declare tax percentage DECLARE taxrate INT DEFAULT 6; --GET the order total SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO total; --Is this taxable IF taxable THEN SELECT total+(total/100*taxrate) INTO total; END IF; SELECT total INTO ototal;

END;

看起来这么长好像挺唬人，其实很清楚：

CREATE PROCEDURE ordertotal( IN onumber INT, IN taxable BOOLEAN, OUT ototal DECIMAL(8, 2)) COMMENT 'Obtain order total, optionally adding tax'

使用CREATE PROCEDURE关键词创建了名为ordertotal的存储过程
该存储过程定义了三个变量，IN表示要求输入的参数，OUT表示输出的结果。INT、BOOLEAN等表示变量的数据类型
COMMENT非必需，如果有，那么在SHOW PROCEDURE STATUS的结果时会显示（简单说，类似于方法的说明）

BEGIN ...END;

BEGIN和END用来界定存储过程操作执行的语句

--Declare variable for total DECLARE total DECIMAL(8, 2); --Declare tax percentage DECLARE taxrate INT DEFAULT 6; --GET the order total SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO total;

DECLARE用来定义存储过程中的局部变量
INTO表示赋值到变量

--Is this taxable IF taxable THEN SELECT total+(total/100*taxrate) INTO total; END IF;

IF <boolean> THEN <do something> END IF 为条件执行语句，记得END IF结尾

假如用Java来写的话，大概是这么个意思：

public void ordertotal(int onumber, boolean taxable, double ototal) { double total; int taxrate = 6; total = getOrderTotal(onumber); if (taxable) { total += total / (100 * taxrate); } ototal = total;}

1.3 执行

在1.2我们定义了存储过程ordertotal()，则执行方式为：

--不含营业税CALL ordertotal(20005, 0, @total);SELECT @total+----------+| @total |+----------+| 149.87 |+----------+--包含营业税CALL ordertotal(20005, 1, @total);SELECT @total+-----------------+| @total |+-----------------+| 158.862200000 |+-----------------+

定义时我们说过，IN表示定义输入，OUT表示定义输出，所以这里的三个变量中，前两者由调用者传入，而第三个变量，则作为返回结果的变量。

调用存储过程时，用于临时存储返回数据的变量必须以@开头）

1.4 检查

用来显示“创建某个存储过程的CREATE语句”，使用SHOW CREATE PROCEDURE语句：

SHOW CREATE PROCEDURE ordertotal;

1.5 删除

DROP PROCEDURE ordertotal;

注意，检查和删除存储过程，都不用加后面的()，只需要给出存储过程的名称即可。

2、游标

语句检索出来的结果集，另外， MySQL游标只能用于存储过程（和函数）。

2.1 创建游标

DECLARE和 CURSOR关键字：

CREATE PROCEDURE processorders()BEGIN DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;END;

2.2 打开和关闭游标

OPEN进行打开。另，游标相关的SELECT查询语句，在定义时是不执行的，在OPEN时才执行查询，存储检索出的数据以供浏览和滚动。在游标使用完成后，使用 CLOSE进行关闭：

CREATE PROCEDURE processorders()BEGIN --Declare DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; --Open OPEN ordernumbers; --Close CLOSE ordernumbers;END;

2.3 使用游标数据

FETCH访问数据了，FETCH是从第一行开始，获取当前行的数据，每次执行后会移动内部行指针，再次调用FETCH则会检索到下一行（不会重复读取同一行）：

CREATE PROCEDURE processorders()BEGIN --Declare DECLARE o INT; DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; --Open OPEN ordernumbers; --Get FETCH ordernumbers INTO o; --Close CLOSE ordernumbers;END;

看一个复杂些的例子：

CREATE PROCEDURE processorders()BEGIN --Declare local variables DELCARE done BOOLEAN DEFAULT 0; DECLARE o INT; DECLARE t DECIMAL(8,2); --Declare cursor DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; --Declare continue handler DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; --Create a table to store the results CREATE TABLE IF NOT EXISTS ordertotals(order_num INT, total DECIMAL(8,2)); --Open the cursor OPEN ordernumbers; --Loop through all rows REPEAT FETCH ordernumbers INTO o; CALL ordertotal(o, 1, t); INSERT INTO ordertotals(order_num, total) VALUES(o, t); UNTIL done END REPEAT; --Close the cursor CLOSE ordernumbers;END;