SQL编程技术可以有效客服SQL语言实现复杂应用方面的不足,提高应用系统和数据库管理系统间的互操作性。
一、例题
1、写一个带参数的函数fun1,计算1+2+3+...+n
/*1、写一个带参数的函数fun1,计算1+2+3+...+n*/
DELIMITER $$
CREATE FUNCTION fun1(n INT) RETURNS INT
BEGIN
DECLARE s INT DEFAULT 0 ;
DECLARE i INT DEFAULT 1 ;
WHILE i <= n DO
SET s = s + i ;
SET i = i + 1 ;
END WHILE ;
RETURN s ;
END
$$
DELIMITER ;
SELECT fun1(3);
/*2、编写一个存储过程sum_add,其功能是完成两个整数相加。*/
DELIMITER $$
CREATE PROCEDURE sum_add(IN a INT ,IN b INT)
BEGIN
SET @sum1:=0;
SET @sum1=a+b;
SELECT @sum1;
END
$$
DELIMITER ;
CALL sum_add(1,2);
/*3*/使用SQL语句在Mystudent“学生选课”数据库中创建一个名为xuesheng_cxbyid的带一个in类型参数的存储过程。该存储过程根据参数的值(学号),返回“学生”表中的对应学号的学生记录。
DELIMITER $$
CREATE PROCEDURE xuesheng_cxbyid(IN int_id INT)
BEGIN
SELECT * FROM student
WHERE student.`Sno`=int_id;
END
$$
DELIMITER ;
CALL xuesheng_cxbyid('201215122');
4编写一个存储过程p_deleteStudent(in sno char(9),out msg char(8)),其功能是删除指定学号sno的学生记录,如果sc表中有该学生的选课记录,要级联删除该学生的选课记录,并根据删除情况返回msg信息(未找到学生,已经删除学生信息,已经删除学生信息及其选课信息)等。
DELIMITER $$
CREATE PROCEDURE p_deleteStudent1(IN sno CHAR(9),OUT msg CHAR(20))
BEGIN
IF NOT EXISTS(SELECT Sno FROM Student WHERE Student.Sno=sno) THEN
SET msg='未找到学生';
ELSE IF EXISTS(SELECT Sno FROM Student WHERE Student.Sno=sno) AND EXISTS(SELECT *FROM sc WHERE sc.sno=sno) THEN
DElETE FROM sc WHERE sc.Sno=sno;
DELETE FROM Student WHERE Student.Sno=sno;
SET msg='已经删除学生信息及其选课信息';
ELSE
DELETE FROM Student WHERE Student.Sno=sno;
SET msg='已经删除学生信息';
END IF ;
END $$
SET @msg='';
CALL p_deleteStudent1('201215121',@msg);
SELECT @msg;
5.编写一个存储存过程 set_cj(IN xh CHAR(9),IN kh CHAR(4)),其功能是设定Mystudent数据库的sc表中指定学号xh,指定课程号kh的学生成绩为空
DELIMITER $$
CREATE PROCEDURE set_cj(IN xh CHAR(9),IN kh CHAR(4))
BEGIN
IF EXISTS
(SELECT * FROM sc WHERE sc.`Sno`=xh AND sc.`Cno`=kh)
THEN UPDATE sc
SET Grade=NULL
WHERE sc.`Sno`=xh AND sc.`Cno`=kh;
END IF ;
END
$$
DELIMITER ;
CALL set_cj('201215122','2');
Select * from sc;
6.编写一个存储过程set_xy_cj(in xy char(9),in kh char(4)),该存储过程能够把给定学院xy和给定课程号kh的所有学生成绩设置为null。要求使用游标和调用第5题的set_cj存储过程实现。
DELIMITER $$
CREATE PROCEDURE set_xy_cj(IN xy CHAR(9),IN kh CHAR(4))
BEGIN
DECLARE no_more_record INT DEFAULT 0;
DECLARE ssno CHAR(9) DEFAULT '';
DECLARE cur CURSOR FOR
SELECT sno FROM student,dept WHERE student.`Sdept`=dept.`Deptno`
AND dname=xy;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record=1;
OPEN cur;
FETCH cur INTO ssno;
WHILE no_more_record!=1 DO
CALL set_cj(ssno,kh);
FETCH cur INTO ssno;
END WHILE;
CLOSE cur;
END$$
DELIMITER ;
CALL set_xy_cj('软件学院','1');
对于游标(也叫光标)的相关介绍
1.游标,提供了一种灵活的操作方式,让我们能够对结果集中的每一条记录进行定位,并对指向的记录中的数据进行操作的数据结构。游标让 SQL 这种面向集合的语言有了面向过程开发的能力。SQL 中,游标是一种临时的数据库对象,可以指向存储在数据库表中的数据行指针。这里游标充当了指针的作用
,我们可以通过操作游标来对数据行进行操作MySQL中游标在存储过程和函数中使用。
2、如何使用游标
- 声明游标
如例题6中的声明:declare 游标名 CURSOR FOR 查询语句
delcare cur CURSOR FOR SELECT。。。。
要使用 SELECT 语句来获取数据结果集,而此时还没有开始遍历数据,这里 查询语句代表的是 SELECT 语句,返回一个用于创建游标的结果集
- 打开游标
OPEN 游标名
当我们定义好游标之后,如果想要使用游标,必须先打开游标。打开游标的时候 SELECT 语句的查询结果集就会送到游标工作区,为后面游标的逐条读取
结果集中的记录做准备。
- 使用游标
FETCH 游标名 INTO (事先定义的)变量名
游标所指向的数据保存到变量中
如果游标读取的数据行有多个列名,则在 INTO 关键字后面赋值给多个变量名即可
- 关闭游标
CLOSE 游标名;
因为游标会占用系统资源
,如果不及时关闭,游标会一直保持到存储过程结束,影响系统运行的效率。而关闭游标的操作,会释放游标占用的系统资源。
关闭游标之后,我们就不能再检索查询结果中的数据行,如果需要检索只能再次打开游标。