mysql存储过程学习笔记2

/*1.根据生日计算年龄的函数*/
DELIMITER //
DROP FUNCTION IF EXISTS getAage;//
CREATE FUNCTION getAage(birth DATETIME) RETURNS INT
BEGIN
DECLARE age INT;
DECLARE temp INT;
SET temp=DATE_FORMAT(NOW(),'%m-%d')-DATE_FORMAT(birth,'%m-%d');
IF temp>=0 THEN
SET age=DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(birth,'%Y');
ELSE
SET age=DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(birth,'%Y')-1;
END IF;
RETURN age;
END;
//
SELECT getAage('87-04-05');  //23
/*2.计算1到n的函数*/
/*(1).使用loop...end loop循环来实现*/
DELIMITER //
DROP FUNCTION IF EXISTS total;//
CREATE FUNCTION total(n INT) RETURNS INT
BEGIN
DECLARE result INT DEFAULT 0;
loop_a:LOOP
SET result=result+n;
SET n=n-1;
IF n=0 THEN
LEAVE loop_a;
END IF;
END LOOP loop_a;
RETURN result;
END;
//
/*(2).使用while...do...end while循环来实现*/
DELIMITER //
DROP FUNCTION IF EXISTS total;//
CREATE FUNCTION total(n INT) RETURNS INT
BEGIN
DECLARE result INT DEFAULT 0;
WHILE n>0 DO
SET result=result+n;
SET n=n-1;
END WHILE;
RETURN result;
END;
//
SELECT total(100);
/*(3).使用repeat...util...end repeat循环来实现*/
DELIMITER //
DROP FUNCTION IF EXISTS total;//
CREATE FUNCTION total(n INT) RETURNS INT
BEGIN
DECLARE result INT DEFAULT 0;
REPEAT
SET result=result+n;
SET n=n-1;
UNTIL n=0
END REPEAT;
RETURN result;
END;
//
SELECT total(100);
/*3.使用游标和创建异常处理语句存储过程实例*/
DELIMITER //
DROP PROCEDURE IF EXISTS cur_example;//
CREATE PROCEDURE cur_example()
BEGIN
DECLARE temp INT;
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN cur;
loop_a:LOOP
FETCH cur INTO temp;
SELECT temp;
IF done=1 THEN
LEAVE loop_a;
END IF;
END LOOP loop_a;
CLOSE cur;
END;
//
CALL cur_example();
/*4.如果表不存在就去创建表*/
CREATE TABLE IF NOT EXISTS b(b INT);
/*5.MySQL相较于存储过程，对于存储函数有更严格的规则。函数必须声明不修改SQL（使用NO SQL或者READS SQL DATA子句）
或者声明为DETERMINISTIC（如果服务器被允许开启二进制日志）。这个限制是为了防止当函数返回不确定值时，数据库同步复制的不一致性，
我们的样例例程使用了“deterministic”，这样我们就能确保在提供了相同的参数的情况下返回相同的值*/
/*6.触发器*/
触发器是一种用来相应数据库事件是自动回调的存储程序，在MySQL5的实现中，触发器将在特定表的DML
（数据库操纵语言）激活时被回调，触发器可以用来自动计算引用值或者格式化值。下面的例子展示了用于维护引用值的触发器；
当员工salary的值被改变是，contrib_401K列将被自动修改为特定值。
CREATE TRIGGER employees_trg_bu 
BEFORE UPDATE ON employees 
FOR EACH ROW 
BEGIN 
IF NEW.salary <50000 THEN 
SET NEW.contrib_401K=500;
ELSE 
SET NEW.contrib_401K=500+(NEW.salary-50000)*.01;
END IF; 
END
练习:
DELIMITER //
DROP TRIGGER IF EXISTS a_trigger;//
CREATE TRIGGER a_trigger
BEFORE INSERT ON t
FOR EACH ROW
BEGIN
UPDATE t SET s1=123456 ;
END;
//
INSERT INTO t VALUES(33);
/*7.mysql数据类型声明演示*/
DECLARE l_int1 INT DEFAULT -2000000; 
DECLARE l_int2 INT UNSIGNED DEFAULT 4000000; 
DECLARE l_bigint1 BIGINT DEFAULT 4000000000000000; 
DECLARE l_float FLOAT DEFAULT 1.8e8; 
DECLARE l_double DOUBLE DEFAULT 2e45; 
DECLARE l_numeric NUMERIC(8,2) DEFAULT 9.95;
DECLARE l_date DATE DEFAULT '1999-12-31'; 
DECLARE l_datetime DATETIME DEFAULT '1999-12-31 23:59:59'; 
DECLARE l_char CHAR(255) DEFAULT 'This will be padded to 255 chars';
DECLARE l_varchar VARCHAR(255) DEFAULT 'This will not be padded'; 
DECLARE l_text TEXT DEFAULT 'This is a really long string. In stored programs we can use text columns fairly freely, but in tables there are some limitations regarding indexing and use in various expressions.';
/*8.逻辑语句实践的存储过程例子*/
DELIMITER //
DROP FUNCTION IF EXISTS f_title;//
CREATE FUNCTION f_title(in_gender CHAR(1), in_age INT, in_marital_status VARCHAR(7)) RETURNS VARCHAR(6) 
BEGIN 
DECLARE title VARCHAR(6); 
IF in_gender='F' AND in_age<16 THEN SET title='Miss'; 
ELSEIF in_gender='F' AND in_age>=16 AND in_marital_status='Married' 
THEN SET title='Mrs'; 
ELSEIF in_gender='F' AND in_age>=16 AND in_marital_status='Single' 
THEN SET title='Ms'; 
ELSEIF in_gender='M' AND in_age<16 
THEN SET title='Master'; 
ELSEIF in_gender='M' AND in_age>=16 
THEN SET title='Mr'; 
END IF; 
RETURN(title); 
END;
//
SELECT f_title('M',24,'Single');
/*9.经常被使用的MySQL函数*/
   函数                                                 描述
ABS(number)                             返回提供数字的绝对值。比方说， ABS(-2.3)=2.3.
CEILING(number)                         返回下一个最大整数，比方说， CEILING(2.3)=3.
CONCAT(string1[,string2,string3,...])   返回所有提供字符串的连接形式的值
CURDATE()                               返回当前时间（不带秒）  SELECT CURDATE();
DATE_ADD(DATE,INTERVAL amount_type)     给提供的时间值加上一个时间间隔并返回一个新时间。正确的形式有SECOND, MINUTE, HOUR, DAY, MONTH和YEAR
DATE_SUB(DATE,INTERVAL interval_type)   从提供的时间值上减去一个时间间隔并返回一个新的时间。正确的形式有SECOND, MINUTE, HOUR, DAY, MONTH和YEAR
FORMAT(number,decimals)                 返回一个指定精确度的数值，并给与以1000为单位的分割（通常使用“，”）
GREATEST(num1,num2[,num3, ... ])        返回所有提供参数中的最大数
IF(test, value1,value2)                 测试一个逻辑条件，如果为真则返回value1，如果为假返回value2，例如 SELECT IF(2>1,'123','322')返回123
IFNULL(VALUE,value2)                    返回第一个值，除非第一个值为空；这样的话返回第二个值
INSERT(STRING,POSITION,LENGTH,NEW)      把一个字符串插入到另一个字符串中
INSTR(STRING,SUBSTRING)                 返回一个字符串中子串的位置
ISNULL(expression)                      如果参数为空则返回1，否则返回0
LEAST(num1,num2[,num3, ... ])           返回参数列表中的最小值
LEFT(STRING,LENGTH)                     返回字符串最左边的部分
LENGTH(STRING)                          返回字符串中的字节数。CHAR_LENGTH可以被用来返回字符数（这会在你使用多字节字符集是产生差异）
LOCATE(SUBSTRING,STRING[,number])       返回字符串中子串的位置，可选的第三个参数为在父字符串中开始搜索的位置
LOWER(STRING)                           返回给定字符串的小写形式
LPAD(STRING,LENGTH,padding)             返回字符串 str, 其左边由字符串padding 填补到LENGTH 字符长度，第三个参数为填充字符
LTRIM(STRING)                           删除所有字符串中的前缀空格
MOD(num1,num2)                          返回第一个数除于第二个数后的模（余数部分）
NOW()                                   返回当前日期和时间
POWER(num1,num2)                        返回num1的num2次方
RAND([seed])                            返回一个随机数。seed可被用于随机数生成器的种子数
REPEAT(STRING,number)                   返回一个重复number次STRING的字符串
REPLACE(STRING,OLD,NEW)                 用NEW替换所有出现OLD的地方
ROUND(number[,DECIMAL])                 舍去给定数值的指定精度的位数
RPAD(STRING,LENGTH,padding)             返回字符串 str, 其右边由字符串padding 填补到LENGTH 字符长度，第三个参数为填充字符
RTRIM(STRING)                           删除字符串尾部的空格
SIGN(number)                            如果number小于0则返回-1，如果大于0则返回1，如果为0则返回0
SQRT(number)                            返回number的平方根
STRCMP(string1,string2)                 如果两个值相同则返回0，若根据当前分类次序，第一个参数小于第二个，则返回 -1，其它情况返回 1 。
SUBSTRING(STRING,POSITION,LENGTH)       从字符串指定位置开始返回LENGTH个字符  
UPPER(STRING)                           将指定字符串转换为大写
VERSION                                 返回MySQL服务器当前版本号的字符串
/*在SET和IF子句中使用函数的例子*/
DELIMITER //
DROP FUNCTION IF EXISTS function_example;//
CREATE PROCEDURE function_example( ) 
BEGIN DECLARE TwentyYearsAgoToday DATE; 
DECLARE mystring VARCHAR(250); 
SET TwentyYearsAgoToday=DATE_SUB(CURDATE( ), INTERVAL 20 YEAR); 
SET mystring=CONCAT('It was ',TwentyYearsAgoToday, ' Sgt Pepper taught the band to play...'); 
SELECT mystring;
 IF (CAST(SUBSTR(VERSION( ),1,3) AS DECIMAL(2,1)) <5.0) 
 THEN SELECT 'MySQL versions earlier than 5.0 cannot run stored programs - you must be hallucinating'; 
 ELSE SELECT 'Thank goodness you are running 5.0 or higher!';
 END IF; 
 END;
 //
 CALL function_example(); 
 /*10.使用DECLARE语句来定义游标*/
 它的语法如下： DECLARE cursor_name CURSOR FOR SELECT_statement; 
 游标的声明必须在我们所有的变量声明之后。在我们的变量之前定义游标会产生一个1337错误       
 
 /*11展示了在命令行客户端下使用预处理语句的例子*/   
 PREPARE prod_insert_stmt FROM "INSERT INTO product_codes VALUES(?,?)"; 
 SET @CODE='QB';
 SET @NAME='MySQL Query Browser'; 
 EXECUTE prod_insert_stmt USING @CODE,@NAME;
 SET @CODE='AD';
 SET @NAME='MySQL Administrator'; 
 EXECUTE prod_insert_stmt USING @CODE,@NAME  
 
 /*12.可以更新任何表中列值的存储过程*/
 CREATE PROCEDURE set_col_value (in_table VARCHAR(128), in_column VARCHAR(128), in_new_value VARCHAR(1000), in_where VARCHAR(4000)) 
 BEGIN 
 DECLARE l_sql VARCHAR(4000); 
 SET l_sql=CONCAT_WS(' ', 'UPDATE',in_table,'SET',in_column,'=',in_new_value, ' WHERE',in_where);
 SET @SQL=l_sql; 
 PREPARE s1 FROM @SQL; 
 EXECUTE s1;
 DEALLOCATE PREPARE s1; 
 END;
 
 CALL set_col_value('employees','salary','0','employee_id=1')
 
 /*13.使用动态SQL的搜索过程*/
 CREATE PROCEDURE sp_customer_search (in_customer_name VARCHAR(30), in_contact_surname VARCHAR(30), in_contact_firstname VARCHAR(30), in_phoneno VARCHAR(10)) 
 BEGIN 
 SELECT * FROM customers WHERE (customer_name LIKE in_customer_name OR in_customer_name IS NULL) 
 AND (contact_surname LIKE in_contact_surname OR in_contact_surname IS NULL) 
 AND (contact_firstname LIKE in_contact_firstname OR in_contact_firstname IS NULL) 
 AND (phoneno LIKE in_phoneno OR in_phoneno IS NULL) ; 
 END;
/*14.使用动态SQL的搜索过程*/
CREATE PROCEDURE sp_customer_search_dyn (in_customer_name VARCHAR(30), in_contact_surname VARCHAR(30),in_contact_firstname VARCHAR(30), in_phoneno VARCHAR(10))
BEGIN 
DECLARE l_where_clause VARCHAR(1000) DEFAULT 'WHERE'; 
IF in_customer_name IS NOT NULL 
THEN SET l_where_clause=CONCAT(l_where_clause, ' customer_name="',in_customer_name,'"'); 
END IF; 
IF in_contact_surname IS NOT NULL THEN 
IF l_where_clause<>'WHERE' 
THEN SET l_where_clause=CONCAT(l_where_clause,' AND '); 
END IF; 
SET l_where_clause=CONCAT(l_where_clause, ' contact_surname="',in_contact_surname,'"'); 
END IF; 
IF in_contact_firstname IS NOT NULL 
THEN IF l_where_clause<>'WHERE' 
THEN SET l_where_clause=CONCAT(l_where_clause,' AND '); 
END IF;
SET l_where_clause=CONCAT(l_where_clause, ' contact_firstname="',in_contact_firstname,'"'); 
END IF;
IF in_phoneno IS NOT NULL 
THEN IF l_where_clause<>'WHERE' 
THEN SET l_where_clause=CONCAT(l_where_clause,' AND '); 
END IF; 
SET l_where_clause=CONCAT(l_where_clause, ' phoneno="',in_phoneno,'"');
END IF; 
SET @SQL=CONCAT('SELECT * FROM customers ', l_where_clause); 
PREPARE s1 FROM @SQL; 
EXECUTE s1; 
DEALLOCATE PREPARE s1; 
END;