mysql存储过程和函数使用实例详解数据库

DELIMITER $$ 
 
USE `dbTest`$$ 
 
DROP PROCEDURE IF EXISTS `sp_calc_year`$$ 
 
CREATE PROCEDURE `sp_calc_year`(IN Y INT,IN M INT,OUT Diff INT) 
BEGIN 
    #declare current date default now(); 
    DECLARE c_y INT DEFAULT 0; 
    DECLARE c_m INT DEFAULT 0; 
    SET c_y = YEAR(NOW()); 
    SET c_m = MONTH(NOW()); 
    IF c_y > Y THEN 
        IF c_m < M THEN 
          SET c_y = c_y - 1; 
        END IF;      
        SET Diff = c_y - Y; 
        IF Diff = 0 THEN 
          SET Diff = 1; 
        END IF; 
    ELSE 
        SET Diff = 1; 
    END IF;   
    END$$ 
 
DELIMITER ;

SET @p_inY=2011; 
SET @p_inM=10; 
SET @p_out=0;  
CALL sp_calc_year(@p_inY,@p_inM,@p_out); 
SELECT @p_out;

DELIMITER $$ 
 
CREATE 
    /*[DEFINER = { user | CURRENT_USER }]*/ 
    FUNCTION `dbTest`.`sp_calc_ym`(Y INT, M INT) 
    RETURNS INT 
    /*LANGUAGE SQL 
    | [NOT] DETERMINISTIC 
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 
    | SQL SECURITY { DEFINER | INVOKER } 
    | COMMENT 'string'*/ 
    BEGIN 
    DECLARE c_y INT DEFAULT 0; 
    DECLARE c_m INT DEFAULT 0; 
    DECLARE Diff INT DEFAULT 1; 
    SET c_y = YEAR(NOW()); 
    SET c_m = MONTH(NOW()); 
    IF c_y > Y THEN 
        IF c_m < M THEN 
          SET c_y = c_y - 1; 
        END IF;      
        SET Diff = c_y - Y; 
        IF Diff = 0 THEN 
          SET Diff = 1; 
        END IF; 
    ELSE 
        SET Diff = 1; 
    END IF;   
    RETURN Diff; 
    END$$ 
 
DELIMITER ;

SELECT `sp_calc_ym`(2011,10);

SELECT @var:=80000*POWER(0.85,2) AS "车辆残值";

SELECT reg_no,  
CASE reg_no WHEN 718170554 THEN '黄色宾利，沪A55662' 
  WHEN 124553063 THEN '红色宾利，沪A55661'  
  WHEN 114480011 THEN '白色奔驰，沪N55663'  
  WHEN 816053337 THEN '褐色宾利，沪N55665' 
  WHEN 653973365 THEN '银色阿斯顿马丁，沪N55666' 
  WHEN 990125871 THEN '绿色阿斯顿马丁，京B55667' 
  WHEN 425710387 THEN '红色宝马，京B55668' 
  WHEN 704411338 THEN '黄色宝马，沪D55669' 
  WHEN 116323731 THEN '白色宝马，杭C55670' 
  WHEN 589618212 THEN '黑色宝马，沪D55671' 
   ELSE 'more' END 
AS testCol , 
plate_num, 
c.brand_txt AS "品牌", 
c.year AS "年份", 
c.month AS "月份", 
c.guide_price AS "指导价", 
c.suggest_price AS "建议小时价格", 
c.hour_price AS "每小时价格",  
c.day_price AS "天价格", 
c.week_price AS "周价格" , 
c.month_price AS "月价格", 
c.min_rent_time AS "最少租用时间（小时）", 
c.max_rent_time AS "最长租用时间（小时）", 
80000*POWER(0.85,sp_calc_ym(c.year,c.month)) AS "车辆残值" 
FROM car AS c WHERE reg_no IN ('718170554','124553063','114480011','816053337','653973365','990125871','116323731','704411338','425710387','589618212') ORDER BY reg_no ASC;