|
|
Adaptive Server Anywhere 7.0网上培训教材
存储过程
目标 ：
1、定义存储过程
2、建立一个简单存储过程
3、讨论存储过程的优点和缺点
4、通过实例介绍Adaptive Server Anywhere对SQL的过程化扩展
5、说明存储过程的结构
6、定义一个复合语句
7、解释输入参数与存储过程返回的结果集之间的差别
内容
1、关于存储过程
2、建立存储过程
3、过程化语言
4、调用存储过程
存储过程：
1、SQL 语句和流程执行语句
2、仅允许间接存取数据
3、封装着公用的： 算法和事务
4、它被存储在系统表 SYSPROCEDURE中
5、仅在该过程第一次被执行时进行编译
6、可接收输入参数
7、可调用其它存储过程
8、可返回状态值
9、可返回输出参数或结果集
存储过程的优点 ：
1、改善性能
2、提高安全性
3、减少网络交通量
4、提高开发人员的生产率
5、能充分发挥专家的作用
6、简化了应用的维护
7、应用的灵活性
8、确保算法的一致性
9、数据透明性
存储过程的缺点：
1、可能加重了服务器方的负载
2、要注意对该共享资源的管理以免造成并发冲突
3、可移植性差
存储过程的结构 ：
在CREATE PROCEDURE 语句中可包含：
1、过程参数清单的说明
2、结果集
3、过程体，它由复合SQL语句构成
存储过程的结果：
1、OUT 或 INOUT 参数
2、结果集
3、允许存储过程返回多个列和行
4、RETURN 状态
5、允许你无条件地退出一个存储过程，同时返回一个特定的状态值。
CREATE PROCEDURE：
1、CREATE PROCEDURE
进行语法分析 存入系统表 SYSPROCEDURE
2、直到被调用时才被编译
然后建立存取计划并存储在内存中
在ASE中，你必须以明显方式指明要进行编译。
3、基本语法 CREATE PROCEDURE procedure-name ([IN | OUT | INOUT] parameter-name datatype [DEFAULT expression], …) [RESULT (result-col1, result-col2, ...)] compound-statement
实例 ：
CREATE PROCEDURE big_num
(IN a INT, IN b INT, OUT c INT)
BEGIN
IF a >; b
THEN
SET c = a;
ELSE
SET c = b;
END IF;
END;
参数列表清单 ：
1、用来向存储过程传送值或接受存储过程返回值
2、参数的定义
种类: IN, OUT 或 INOUT
名字: 要遵守数据库标识符约定
数据类型: 任何有效的Adaptive Server Anywhere 数据类型
可给出 DEFAULT 值
3、在掉用该存储过程时，必须为每个参数提供一个值。
为参数赋值 ：
1、SET 语句
2、SELECT INTO 语句
实例：
CREATE PROCEDURE order_count
(IN @customer_id INT, OUT @orders INT)
BEGIN
SELECT COUNT("dba".sales_order.id) INTO @orders FROM "dba".customer KEY LEFT OUTER JOIN "dba".sales_order WHERE "dba".customer.id = @customer_id;
END
结果集 ：
1、允许一个存储过程返回多行
2、实例 1 — 返回客户的 ID和订单
CREATE PROCEDURE customer_orders( )
RESULT (customer_id INT, order_count INT)
BEGIN
SELECT "dba".customer.id, COUNT( "dba".sales_order.id ) FROM "dba".customer KEY LEFT OUTER JOIN "dba".sales_order GROUP BY "dba".customer.id;
END;
3、还可由多个SELECT 语句返回数据
4、结果集中必须： 列的数目相同 有相同的数据类型 (或是可进行隐式转换)
5、T-SQL 可以包含动态的结果集
6、实例 2 — 返回客户和联系人的清单
CREATE PROCEDURE custs_and_contacts ( )
RESULT (lname CHAR (36), fname CHAR (36))
BEGIN
SELECT lname, fname FROM "dba".customer;
SELECT last_name, first_name FROM "dba".contact;
END;
组合语句 ：
1、用在过程本体中
被BEGIN 和 END所包围
2、可包含局部说明，它仅在组合语句的上下文中有效。
局部说明可被嵌套的子句所访问
3、被内部语句所使用的资源要在它们的语句的 END处才释放
4、实例：
CREATE PROCEDURE someproc ( )
BEGIN
DECLARE x INT;
DECLARE y INT;
SELECT count(*) INTO x FROM TableA;
SELECT count(*) INTO y FROM TableB;
BEGIN
DECLARE z INT;
CALL greater (x, y, z);
PRINT ‘z = %1!’, z;
END;
END;
编写存储过程的注意事项 ：
1、校验命令分隔符
2、语句的分隔
使用分号
3、应采用完全受限命名
4、应采用无二意性的日期和时间格式
过程化语言 ：
1、Adaptive Server Anywhere 过程化扩展
说明语句
条件语句
反复循环语句
分支语句
错误处理指令
2、编写存储过程就类似于编程序
说明语句 ：
1、BEGIN, END
2、DECLARE, SET
3、说明： 变量 表达式 语句块
4、赋值
BEGIN 和 END：
1、定义一个组合语句
2、可包含局部变量，游标，临时表及表达式。
3、语法
BEGIN [ATOMIC]
…
…
END
4、实例
BEGIN
UPDATE product SET unit_price = 1.25 * unit_price;
UPDATE employee SET salary = .95 * salary;
END;
原子化多个SQL语句 ：
1、这些语句或是全部完成或是一个也不做
2、确保数据的一致性
3、使用 BEGIN ATOMIC 语法
4、实例：
CREATE PROCEDURE update_dept(IN @old_dept_num INT, IN @new_dept_num INT, IN @new_dept_name CHAR (40), IN @new_dept_head_id INT)
BEGIN ATOMIC
INSERT INTO department (dept_id, dept_name, dept_head_id) VALUES (@new_dept_num, @new_dept_name, @new_dept_head_id);
UPDATE employee SET dept_id = @new_dept_num
WHERE dept_id = @old_dept_num;
DELETE FROM department
WHERE dept_id = @old_dept_num;
END ;
DECLARE:
1、说明变量，游标，临时表及表达式。
2、利用SET设置初始值
3、必须紧跟在一个 BEGIN 语句之后
ASE的T-SQL 允许在存储过程中的任何点使用 DECLARE
DECLARE variable-name datatype;
SET variable-name expression;
DECLARE LOCAL TEMPORARY TABLE table-name (table-definition) •[ON COMMIT {DELETE | PRESERVE} ROWS]
4、实例：
BEGIN
DECLARE discount numeric (6,2);
SET discount = .15;
SELECT discount * unit_price FROM product;
END
BEGIN
DECLARE LOCAL emp_temp (emp_fname char(20), emp_lname char (20) ON COMMIT DELETE ROWS);
END
条件语句 ：
1、根据前面的语句来改变执行流程
2、IF
3、CASE
4、RETURN (经常是伴有条件来使用)
IF 语句 ：
1、当一个条件为 TRUE（真）时准许执行
2、ELSE 子句是当一个条件为 FALSE（失败）时准许执行
3、ELSE IF 准许附加的条件
4、语法：
IF search-condition1 THEN statement-list1;
[ELSEIF search-condition2
THEN statement-list2];
[ELSE statement-list3];
END IF;
5、实例
IF @color = red
THEN SELECT * FROM product WHERE color = 'red';
ELSEIF @color = blue
THEN
SELECT * FROM product WHERE color = 'blue';
ELSE SELECT * FROM product;
END IF;
CASE 语句 ：
1、利用WHEN子句指明多个条件
2、ELSE子句是用于如果没有一个WHEN子句能成为 TRUE时
3、语法：
CASE value-expression
WHEN constant1 THEN statement-list1
WHEN constant2 THEN statement-list2
WHEN constant3 THEN statement-list3
ELSE statement-list4;
END CASE;
4、实例
CASE @id_type
WHEN 'cust' THEN
SELECT id FROM customer
WHEN 'dept' THEN
SELECT dept_id FROM department
WHEN 'contact' THEN
SELECT id FROM contact
ELSE
SELECT emp_id FROM employee;
END CASE;
RETURN 语句：
1、导致立即退出一个存储过程
2、可带有表达式以向调用者返回单个值
3、语法：
RETURN [表达式]
4、实例：
IF @quantity =>;1
THEN
UPDATE sales_order_items SET quantity = @quantity;
ELSE RETURN 'You must enter a quantity';
END IF;
反复循环语句：
1、请看Adaptive Server Anywhere参考手册第9章：SQL语句
2、 LOOP 语句 [WHILE…] LOOP
3、其中 WHILE 条件是选项
4、FOR (用在游标中)
WHILE…LOOP：
1、只要WHILE条件为TRUE则不断地重复执行
2、语法
[statement-label:]
[WHILE condition] LOOP
statement-list
END LOOP [statement-label]
3、实例：
SET @total = 0;
SET @num_items = 0;
INSERT INTO total_temp VALUES (0,0);
loop1:
WHILE @total < 100.00 LOOP
UPDATE total_temp SET num_items = @num_items;
SET @total = @total + SELECT unit_price FROM product WHERE id = @product_id;
SET @num_items = @num_items + 1;
END LOOP loop1;
SELECT num_items from total_temp;
END;
分支语句：
1、改变执行流程
2、LEAVE
3、CALL
LEAVE：
1、结束一个过程循环
2、要求一个语句标号
3、语法：
LEAVE 语句标号
4、实例：
SET @total = 0;
SET @num_items = 0;
INSERT INTO total_temp VALUES (0,0);
loop1:
WHILE @total < 100.00 LOOP
UPDATE total_temp SET num_items = @num_items;
IF @total >; 100.0
THEN LEAVE loop1;
END if;
SET @total = @total + SELECT unit_price
FROM product WHERE id = @product_id;
SET @num_items = @num_items + 1;
END LOOP loop1;
SELECT num_items from total_temp;
END;
CALL：
1、调用一个存储过程
2、语法：
CALL存储过程名 (参数名值 ,…);
3、实例：
CALL sp_sales_order (23345, 1923;
错误处理 ：
1、缺省的错误处理
向调用者返回出错消息
2、例外处理
允许存储过程在内部处理错误
并不向调用者返回出错消息
关键词EXCEPTION
3、ON EXCEPTION RESUME
用在 CREATE PROCEDURE
既使出现例外也允许存储过程继续运行
适用于大型的批作业中
4、SIGNAL
人为地制造一个意外（它不是属于Adaptive Server Anywhere的一种意外状态）
适用于对存储过程的测试
语法： SIGNAL exception-name
5、CREATE MESSAGE
建立一个用户定义的出错消息。
语法： CREATE MESSAGE message-num AS 'message text'
6、sp_addmessage
增加一个包含用户定义的出错号码的用户定义消息
CALL sp_addmessage (error-number, ‘format-string parameters’)
7、RAISERROR
类似于 SIGNAL
此外还明确地给出并非来自数据库缺省说明中的实际出错消息
RAISERROR error-number, 'format-string parameters'
删除存储过程 ：
1、必须是DBA或存储过程的主人
2、语法：
DROP PROCEDURE procedure-name;
3、实例：
DROP PROCEDURE myproc;
调用存储过程 ：
1、在CALL语句中给出存储过程名
参数可按名字或按位置次序来赋值
2、语法：
CALL procedure-name ([parameter-name =] expression, …)
3、实例：
CALL update_dept (123, 12300, 'Accounting', 987)
存储过程查询计划 ：
1、存储过程在它第一次被执行时进行编译
2、在数据库运行时被编译好的代码和查询计划被保留在内存中
3、在第一次执行时应确保：
各索引已安排好
各参数取值的数据类型要符合于用户实际的需要
小结 ：
1、存储过程中包含SQL语句及对SQL的过程化扩展.
2、存储过程可以接受参数，调用其它存储过程，返回一个状态值或参数及结果集。
3、Adaptive Server Anywhere支持的过程化扩展包括说明语句，条件语句，循环语句，分支语句和错误处理命令。
4、具有RESOURCE 权限的用户才能建立存储过程
5、执行 CREATE PROCEDURE 语句，导致让 Adaptive Server Anywhere 进行语法分析并把该存储过程存入系统表SYSPROCEDURE。
6、调用一个存储过程则导致 Adaptive Server Anywhere 从系统表中检索该存储过程，如果自数据库启动以来它是第一次被调用则对它进行编译。
复习题 ：
1 用实例说明Adaptive Server Anywhere所支持的两类用于存储过程的对SQL的过程扩展语句。
2. 说明存储过程的结构
3. 说明存储过程输入参数与返回结果集之间的差别
4. 建立存储过程需要有什么权力？
5. 删去存储过程需要有什么权力？
6. 调用存储过程需要有什么权力？
注明：存储过程方面的内容和ASE有很大的区别，这点需要特别注意！ |
|
楼主: ninetailsfox
IP卡
狗仔卡
楼主
显身卡