存储过程和过程函数区别:
1)一般来说,存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强。存储过程,功能强大,可以执行包括修改表等一系列数据库操作;用户定义函数不能用于执行一组修改全局数据库状态的操作。
2)对于存储过程来说可以返回参数,如记录集,而函数只能返回值或者表对象。函数只能返回一个变量;而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型,而函数只能有IN类~~存储过程声明时不需要返回类型,而函数声明时需要描述返回类型,且函数体中必须包含一个有效的RETURN语句。
3)存储过程,可以使用非确定函数,不允许在用户定义函数主体中内置非确定函数。
4)存储过程一般是作为一个独立的部分来执行( EXECUTE 语句执行),而函数可以作为查询语句的一个部分来调用(SELECT调用),由于函数可以返回一个表对象,因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程,而可以使用函数。
有个学生表结构如下:
CREATE TABLE `student` (
`sid` varchar(10) DEFAULT NULL,
`sname` varchar(10) DEFAULT NULL,
`score` int(3) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gb2312;
一、过程函数应用案例1
建立个函数实现能够按给定的学生编号查询学生的成绩。实现如下:
DELIMITER $
CREATE FUNCTION query_score(sudent_id VARCHAR(10)) RETURNS INT
DETERMINISTIC
BEGIN
DECLARE v_score INT;
SELECT score INTO v_score FROM student WHERE sid = sudent_id;
RETURN (v_score);
END $
DELIMITER ;
SELECT query_score('s001')
二、过程函数应用案例2
建立个过程函数,根据学号查找,如果这个学生是’zhangsan’,则将其名字更改为’lisi’,如果更改成功函数返回值为ok,否则返回none。
DELIMITER $
CREATE FUNCTION demo002(id VARCHAR(20)) RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
DECLARE X VARCHAR(20);
DECLARE flg VARCHAR(20);
SELECT sname INTO X FROM student WHERE sid = id;
IF X='zhangsan' THEN
UPDATE student SET sname = 'lisi' WHERE sid=id;
SET flg ='ok';
ELSE
SET flg ='none';
END IF;
RETURN (flg);
END $
DELIMITER ;
SELECT demo002('s003' ) FROM student
三、过程函数应用案例3
创建个过程函数,计算1 + 2 + 3 … + 100 = ? ,实现过程如下:
DELIMITER $
CREATE FUNCTION demo001( ) RETURNS INT
DETERMINISTIC
BEGIN
DECLARE i INT;
DECLARE _sum INT;
SET i=0;
SET _sum=0;
WHILE i<=100 DO
SET _sum=_sum+i;
SET i=i+1;
END WHILE;
RETURN (_sum);
END $
DELIMITER ;
SELECT demo001( )
四、过程函数应用案例4
设计个函数,输入参数是成绩,当成绩在90到100分之间的时候,显示“优秀”;当成绩在80到90分之间的时候,显示“良好”,其它情况显示“一般”。
实现过程如下:
DELIMITER $
CREATE FUNCTION score(stu_score INT) RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
DECLARE result VARCHAR(10);
CASE stu_score
WHEN stu_score>=90 AND stu_score<=100 THEN
SET result = '优秀';
WHEN stu_score>=80 AND stu_score<90 THEN
SET result = '良好';
ELSE
SET result = '一般';
END CASE;
RETURN (result);
END $
DELIMITER ;
SELECT score(99)
五、系统函数
常见的系统函数:
CHAR_LENGTH(s)
CONCAT(s1,s2...sn)
LCASE(s)
LEFT(s,n)
LOWER(s)
LTRIM(s)
REPLACE(s,s1,s2)
REVERSE(s)
SUBSTR(s, start, length)
给两个练习的示例,剩下的自行练习:
(1)CHAR_LENGTH(s)
SELECT sname, CHAR_LENGTH(sname) AS '字符串长度' FROM student
(2)CONCAT(s1,s2...sn)
SELECT sname, CONCAT('我是:',sname) AS '连接两个字符串' FROM student
六、聚合函数
以学生表的成绩字段score为对象,练习下列常见的聚合函数:max、min、count、sum、avg
给两个练习的示例,剩下的自行练习:
(1)求全班平均成绩
SELECT AVG(score) '平均成绩' FROM student
(2)求全班最高分
SELECT MAX(score) '最高分' FROM student