实验6:存储过程

前言：存储过程实验包含3个实验项目，其中2个必修实验项目，1个选修实验项目，均为设计型实验项目。

6.1 存储过程实验

1.实验目的

掌握数据库PL/SQL编程语言，以及数据库存储过程的设计和使用方法

2.实验内容和要求

存储过程定义、存储过程运行，存储过程更名，存储过程删除，存储过程的参数传递。掌握PL/SQL编程语言和编程规范，规范设计存储过程。
(1) 存储过程的优点:

加快系统运行速度，存储只在创建时编译，以后每次执行时不需要重新编译。

可以封装复杂的数据库操作，简化操作流程，例如对多个表的更新，删除等。

可实现模块化的程序设计，存储过程可多次调用，提供统一的数据库访问口。

可以增加代码的安全性，对于用户不能直接操作存储过程中引用的对象，SQL Server可以设定用户对指定存储过程的执行权限。

存储过程可以降低网络流量。

(2) 存储过程的缺点：

移植不方便，存储过程依赖与数据库管理系统，SQL Server存储过程中封装的操作代码不能直接移植到其他的数据库管理系统中。

不支持面向对象的设计，无法采用面向对象的方式将逻辑业务进行封装。

代码可读性差，不易维护。不支持集群。
(3) 存储过程分类：
系统存储过程：系统存储过程是 SQL Server系统自身提供的存储过程，可以作为命令执行各种操作。系统存储过程主要用来从系统表中获取信息，使用系统存储过程完成数据库服务器的管理工作，为系统管理员提供帮助，为用户查看数据库对象提供方便，系统存储过程位于数据库服务器中，并且以sp_开头，系统存储过程定义在系统定义和用户定义的数据库中，在调用时不必在存储过程前加数据库限定名。

用户存储过程(自定义存储过程)：自定义存储过程即用户使用T_SQL语句编写的、为了实现某一特定业务需求，在用户数据库中编写的T_SQL语句集合，自定义存储过程可以接受输入参数、向客户端返回结果和信息，返回输出参数等。用户定义的存储过程分为两类：T_SQL 和CLR：T_SQL:存储过程是值保存的T_SQL语句集合，可以接受和返回用户提供的参数，存储过程也可能从数据库向客户端应用程序返回数据。CLR存储过程是指引用Microsoft.NET Framework公共语言的方法存储过程，可以接受和返回用户提供的参数，它们在.NET Framework程序集是作为类的公共静态方法实现的。

扩展存储过程：扩展存储过程是以在SQL SERVER环*执行的动态连接(DLL文件)来实现的，可以加载到SQL SERVER实例运行的地址空间中执行，扩展存储过程可以用SQL SERVER扩展存储过程API编程，扩展存储过程以前缀”xp_”来标识，对于用户来说，扩展存储过程和普通话存储过程一样，可以用相同的方法来执行。

3.实验重点和难点

实验重点：存储过程定义和运行。
实验难点：存储过程的参数传递方法。

4.实验步骤

创建数据库

create table books( 
book_id int primary key, 
book_name varchar(20), 
book_price float, 
book_auth varchar(10));

插入测试数据

insert into books (book_id,book_name,book_price,book_auth) values 
(1,'盗墓笔记',25.6,'南派三叔'),
(2,'回到明朝当王爷',35.6,'月当'),
(3,'雪山飞狐',32.7,'金庸'),
(4,'鹿鼎记',32.7,'金庸'),
(5,'甄缳传',32.7,'流潋紫'),
(6,'鬼吹灯',45.8,'天下霸唱'),
(7,'藏海华',34.8,'南派三叔');

(1) 创建无参数的存储过程

delimiter $$  //将标准分隔符;改成$$(因为定义函数过程中出现多个sql语句)
create procedure show_books()       //创建存储过程，目的是展示书名
begin
select book_name from books;
end$$
delimiter ;
call show_books();      //调用，执行存储过程

(2) 查看存储过程：show procedure status where db='my_test';

(3) 删除存储过程：drop procedure show_books;

(4) 重命名存储过程：sp_rename show_books,s_books;
(5) 带一个参数的存储过程(存储过程参数三种类型in,out,inout)

delimiter $$
create procedure getname(in name varchar(20))
begin
select * from books where book_name=name;
end$$
delimiter ;
call getname('盗墓笔记');

实现了输入书名，即可返回该书的所有信息

(5) 带两个参数,并且带有返回值的存储过程

delimiter $$
create procedure getbook_auth(in name1 varchar(20),out author varchar(20))
begin
select book_auth into author from books where book_name=name1; //select into 可用于变量赋值。此处是将书名等于输入的书名的作者赋值给输出变量author
end$$
delimiter ;
call getbook_auth('盗墓笔记',@a);  //@a相当于是返回结果
select @a;  //查看返回结果

实现了输入书名，即可返回该书的作者

实验6.2 自定义函数

1.实验目的

掌握数据库PL/SQL编程语言以及数据库自定义函数的设计和使用方法。

2.实验内容和要求

自定义函数定义、自定义函数运行，自定义函数更名，自定义函数删除，自定义函数的参数传递。掌握PL/SQL和编程规范，规范设计自定义函数。

3.实验重点和难点

实验重点：自定义函数的定义和运行。
实验难点：自定义函数的参数传递方法。

4.实验步骤

(1) 创建一个函数:

set global log_bin_trust_function_creators=1;
delimiter $$
CREATE FUNCTION get_author(name2 varchar(20))
RETURNS VARCHAR(20)
BEGIN
DECLARE auth VARCHAR(20);
SELECT book_auth INTO auth FROM books
WHERE book_name = name2;
RETURN auth;
END $$
DELIMITER ;
select get_author('盗墓笔记');

(2) 查看函数状态或定义语句
查看函数状态语法：SHOW FUNCTION STATUS where db='my_test;

(3) 函数删除:DROP FUNCTION get_author;

实验6.3 游标实验

1.实验目的

掌握PL/SQL游标的设计、定义和使用方法，理解PL/SQL游标按行操作和SQL按结果集操作的区别和联系。

2.实验内容和要求

游标定义、游标使用。掌握各种类型游标的特点、区别与联系。

3.实验重点和难点

实验重点：游标定义和使用。
实验难点：游标类型。

4.实验步骤

(1)首先在MySql中创建一张数据表:

CREATE TABLE IF NOT EXISTS store (
id int(11) NOT NULL AUTO_INCREMENT,
name1 varchar(20) NOT NULL,
count1 int(11) NOT NULL DEFAULT 1,
PRIMARY KEY (id));

INSERT INTO store (id, name1, count1) VALUES
(1, 'android', 15),
(2, 'iphone', 14),
(3, 'iphone', 20),
(4, 'android', 5),
(5, 'android', 13),
(6, 'iphone', 13);

(2) 我们现在要用存储过程做一个功能，统计iphone的总库存是多少，并把总数输出到控制台。

CREATE PROCEDURE StatisticStore()
BEGIN
declare c int;
declare n varchar(20);
declare total int default 0;
declare done int default false;
declare cur cursor for select name1,count1 from store where name1 = 'iphone';
declare continue HANDLER for not found set done = true;
set total = 0;
open cur;
read_loop:loop
fetch cur into n,c;
if done then
leave read_loop;   
end if;
set total = total + c;
end loop;
close cur;
select total;
END; $$
call StatisticStore();