程序博客网 > 如何设置淘宝直通车

MySQL存储过程

来源：互联网 发布：如何设置淘宝直通车 编辑：程序博客网 时间：2024/05/16 01:04

DELIMITER $$  

  1.   
  2. DROP PROCEDURE IF EXISTS HelloWorld$$  
  3. CREATE PROCEDURE HelloWorld()  
  4. BEGIN  
  5.      SELECT "Hello World!";  
  6. END$$  
  7.   
  8. DELIMITER ;  

DELIMITER $$ DROP PROCEDURE IF EXISTS HelloWorld$$ CREATE PROCEDURE HelloWorld() BEGIN SELECT "Hello World!"; END$$ DELIMITER ;

3，变量

使用DECLARE来声明，DEFAULT赋默认值，SET赋值

 

  1. DECLARE counter INT DEFAULT 0;  
  2. SET counter = counter+1;  

DECLARE counter INT DEFAULT 0; SET counter = counter+1;

4，参数

IN为默认类型，值必须在调用时指定，值不能返回（值传递）

OUT值可以返回（指针传递）

INOUT值必须在调用时指定，值可以返回

 

  1. CREATE PROCEDURE test(a INT, OUT b FLOAT, INOUT c INT)  

CREATE PROCEDURE test(a INT, OUT b FLOAT, INOUT c INT)

5，条件判断

IF THEN、ELSEIF、ELSE、END IF

 

  1. DELIMITER $$  
  2.   
  3. DROP PROCEDURE IF EXISTS discounted_price$$  
  4. CREATE PROCEDURE discunted_price(normal_price NUMERIC(8, 2), OUT discount_price NUMERIC(8, 2))  
  5. BEGIN  
  6.      IF (normal_price > 500) THEN  
  7.          SET discount_price = normal_price * .8;  
  8.      ELSEIF (normal_price > 100) THEN  
  9.          SET discount_price = normal_price * .9;  
  10.      ELSE  
  11.          SET discount_price = normal_price;  
  12.      END IF;  
  13. END$$  
  14.   
  15. DELIMITER ;  

DELIMITER $$ DROP PROCEDURE IF EXISTS discounted_price$$ CREATE PROCEDURE discunted_price(normal_price NUMERIC(8, 2), OUT discount_price NUMERIC(8, 2)) BEGIN IF (normal_price > 500) THEN SET discount_price = normal_price * .8; ELSEIF (normal_price > 100) THEN SET discount_price = normal_price * .9; ELSE SET discount_price = normal_price; END IF; END$$ DELIMITER ;

6，循环

LOOP、END LOOP

 

  1. DELIMITER $$  
  2.   
  3. DROP PROCEDURE IF EXISTS simple_loop$$  
  4.   
  5. CREATE PROCEDURE simple_loop(OUT counter INT)  
  6. BEGIN  
  7.      SET counter = 0;  
  8.      my_simple_loop: LOOP  
  9.          SET counter = counter+1;  
  10.          IF counter = 10 THEN  
  11.              LEAVE my_simple_loop;  
  12.          END IF;  
  13.      END LOOP my_simple_loop;  
  14. END$$  
  15.   
  16. DELIMITER ;  

DELIMITER $$ DROP PROCEDURE IF EXISTS simple_loop$$ CREATE PROCEDURE simple_loop(OUT counter INT) BEGIN SET counter = 0; my_simple_loop: LOOP SET counter = counter+1; IF counter = 10 THEN LEAVE my_simple_loop; END IF; END LOOP my_simple_loop; END$$ DELIMITER ;

WHILE DO、END WHILE

 

  1. DELIMITER $$  
  2.   
  3. DROP PROCEDURE IF EXISTS simple_while$$  
  4.   
  5. CREATE PROCEDURE simple_while(OUT counter INT)  
  6. BEGIN  
  7.      SET counter = 0;  
  8.      WHILE counter != 10 DO  
  9.          SET counter = counter+1;  
  10.      END WHILE;  
  11. END$$  
  12.   
  13. DELIMITER ;  

DELIMITER $$ DROP PROCEDURE IF EXISTS simple_while$$ CREATE PROCEDURE simple_while(OUT counter INT) BEGIN SET counter = 0; WHILE counter != 10 DO SET counter = counter+1; END WHILE; END$$ DELIMITER ;

REPEAT、UNTILL

 

  1. DELIMITER $$  
  2.   
  3. DROP PROCEDURE IF EXISTS simple_repeat$$  
  4.   
  5. CREATE PROCEDURE simple_repeat(OUT counter INT)  
  6. BEGIN  
  7.      SET counter = 0;  
  8.      REPEAT  
  9.          SET counter = counter+1;  
  10.      UNTIL counter = 10 END REPEAT;  
  11. END$$  
  12.   
  13. DELIMITER ;  

DELIMITER $$ DROP PROCEDURE IF EXISTS simple_repeat$$ CREATE PROCEDURE simple_repeat(OUT counter INT) BEGIN SET counter = 0; REPEAT SET counter = counter+1; UNTIL counter = 10 END REPEAT; END$$ DELIMITER ;

7，异常处理

如果用cursor获取SELECT语句返回的所有结果集时应该定义NOT FOUND error handler来防止存储程序提前终结

如果SQL语句可能返回constraint violation等错误时应该创建一个handler来防止程序终结

8，数据库交互

INTO用于存储单行记录的查询结果

  1. DECLARE total_sales NUMERIC(8, 2);  
  2. SELECT SUM(sale_value) INTO total_sales FROM sales WHERE customer_id=in_customer_id;  

DECLARE total_sales NUMERIC(8, 2); SELECT SUM(sale_value) INTO total_sales FROM sales WHERE customer_id=in_customer_id;

CURSOR用于处理多行记录的查询结果

 

  1. DELIMITER $$  
  2.   
  3. DROP PROCEDURE IF EXITS cursor_example$$  
  4. CREATE PROCEDURE cursor_example()  
  5.      READS SQL DATA  
  6. BEGIN  
  7.      DECLARE l_employee_id INT;  
  8.      DECLARE l_salary NUMERIC(8,2);  
  9.      DECLARE l_department_id INT;  
  10.      DECLARE done INT DEFAULT 0;  
  11.      DECLARE cur1 CURSOR FOR SELECT employee_id, salary, department_id FROM employees;  
  12.      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;  
  13.   
  14.      OPEN cur1;  
  15.      emp_loop: LOOP  
  16.          FETCH cur1 INTO l_employee_id, l_salary, l_department_id;  
  17.          IF done=1 THEN  
  18.              LEAVE emp_loop;  
  19.          END IF;  
  20.      END LOOP emp_loop;  
  21.      CLOSE cur1;  
  22. END$$  
  23. DELIMITER ;  

DELIMITER $$ DROP PROCEDURE IF EXITS cursor_example$$ CREATE PROCEDURE cursor_example() READS SQL DATA BEGIN DECLARE l_employee_id INT; DECLARE l_salary NUMERIC(8,2); DECLARE l_department_id INT; DECLARE done INT DEFAULT 0; DECLARE cur1 CURSOR FOR SELECT employee_id, salary, department_id FROM employees; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; OPEN cur1; emp_loop: LOOP FETCH cur1 INTO l_employee_id, l_salary, l_department_id; IF done=1 THEN LEAVE emp_loop; END IF; END LOOP emp_loop; CLOSE cur1; END$$ DELIMITER ;

unbounded SELECT语句用于存储过程返回结果集

 

  1. DELIMITER $$  
  2. DROP PROCEDURE IF EXISTS sp_emps_in_dept$$  
  3. CREATE PROCEDURE sp_emps_in_dept(in_employee_id INT)  
  4. BEGIN  
  5.      SELECT employee_id, surname, firstname, address1, address2, zipcode, date_of_birth FROM employees WHERE department_id=in_employee_id;  
  6. END$$  
  7.   
  8. DELIMITER ;  

DELIMITER $$ DROP PROCEDURE IF EXISTS sp_emps_in_dept$$ CREATE PROCEDURE sp_emps_in_dept(in_employee_id INT) BEGIN SELECT employee_id, surname, firstname, address1, address2, zipcode, date_of_birth FROM employees WHERE department_id=in_employee_id; END$$ DELIMITER ;

UPDATE、INSERT、DELETE、CREATE TABLE等非查询语句也可以嵌入存储过程里

 

  1. DELIMITER $$  
  2.   
  3. DROP PROCEDURE IF EXITS sp_update_salary$$  
  4. CREATE PROCEDURE sp_update_salary(in_employee_id INT, in_new_salary NUMERIC(8,2))  
  5. BEGIN  
  6.      IF in_new_salary < 5000 OR in_new_salary > 500000 THEN  
  7.          SELECT "Illegal salary: salary must be between $5000 and $500, 000";  
  8.      ELSE  
  9.          UPDATE employees SET salary=in_new_salary WHERE employee_id=in_employee_id;  
  10.      END IF:  
  11. END$$  
  12.   
  13. DELIMITER ;  

DELIMITER $$ DROP PROCEDURE IF EXITS sp_update_salary$$ CREATE PROCEDURE sp_update_salary(in_employee_id INT, in_new_salary NUMERIC(8,2)) BEGIN IF in_new_salary < 5000 OR in_new_salary > 500000 THEN SELECT "Illegal salary: salary must be between $5000 and $500, 000"; ELSE UPDATE employees SET salary=in_new_salary WHERE employee_id=in_employee_id; END IF: END$$ DELIMITER ;

9，使用CALL调用存储程序

 

  1. DELIMITER $$  
  2.   
  3. DROP PROCEDURE IF EXISTS call_example$$  
  4. CREATE PROCEDURE call_example(employee_id INT, employee_type VARCHAR(20))  
  5.      NO SQL  
  6. BEGIN  
  7.      DECLARE l_bonus_amount NUMERIC(8,2);  
  8.   
  9.      IF employee_type='MANAGER' THEN  
  10.          CALL calc_manager_bonus(employee_id, l_bonus_amount);  
  11.      ELSE  
  12.          CALL calc_minion_bonus(employee_id, l_bonus_amount);  
  13.      END IF;  
  14.      CALL grant_bonus(employee_id, l_bonus_amount);  
  15. END$$  
  16. DELIMITER ;  

DELIMITER $$ DROP PROCEDURE IF EXISTS call_example$$ CREATE PROCEDURE call_example(employee_id INT, employee_type VARCHAR(20)) NO SQL BEGIN DECLARE l_bonus_amount NUMERIC(8,2); IF employee_type='MANAGER' THEN CALL calc_manager_bonus(employee_id, l_bonus_amount); ELSE CALL calc_minion_bonus(employee_id, l_bonus_amount); END IF; CALL grant_bonus(employee_id, l_bonus_amount); END$$ DELIMITER ;

10，一个复杂的例子

 

  1. CREATE PROCEDURE putting_it_all_together(in_department_id INT)  
  2.      MODIFIES SQL DATA  
  3. BEGIN  
  4.      DECLARE l_employee_id INT;  
  5.      DECLARE l_salary NUMERIC(8,2);  
  6.      DECLARE l_department_id INT;  
  7.      DECLARE l_new_salary NUMERIC(8,2);  
  8.      DECLARE done INT DEFAULT 0;  
  9.   
  10.      DECLARE cur1 CURSOR FOR  
  11.          SELECT employee_id, salary, department_id  
  12.          FROM employees  
  13.          WHERE department_id=in_department_id;  
  14.   
  15.      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;  
  16.   
  17.      CREATE TEMPORARY TABLE IF NOT EXISTS emp_raises  
  18.          (employee_id INT, department_id INT, new_salary NUMERIC(8,2));  
  19.   
  20.      OPEN cur1;  
  21.      emp_loop: LOOP  
  22.          FETCH cur1 INTO l_employee_id, l_salary, l_department_id;  
  23.          IF done=1 THEN    /* No more rows */  
  24.              LEAVE emp_loop;  
  25.          END IF;  
  26.          CALL new_salary(1_employee_id, l_new_salary); /* Get new salary */  
  27.          IF (l_new_salary <> l_salary) THEN  /* Salary changed */  
  28.              UPDATE employees  
  29.                  SET salary=l_new_salary  
  30.              WHERE employee_id=l_employee_id;  
  31.             /* Keep track of changed salaries */  
  32.              INSERT INTO emp_raises(employee_id, department_id, new_salary)  
  33.                  VALUES (l_employee_id, l_department_id, l_new_salary);  
  34.          END IF:  
  35.      END LOOP emp_loop;  
  36.      CLOSE cur1;  
  37.     /* Print out the changed salaries */  
  38.      SELECT employee_id, department_id, new_salary from emp_raises  
  39.          ORDER BY employee_id;  
  40. END;  

CREATE PROCEDURE putting_it_all_together(in_department_id INT) MODIFIES SQL DATA BEGIN DECLARE l_employee_id INT; DECLARE l_salary NUMERIC(8,2); DECLARE l_department_id INT; DECLARE l_new_salary NUMERIC(8,2); DECLARE done INT DEFAULT 0; DECLARE cur1 CURSOR FOR SELECT employee_id, salary, department_id FROM employees WHERE department_id=in_department_id; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; CREATE TEMPORARY TABLE IF NOT EXISTS emp_raises (employee_id INT, department_id INT, new_salary NUMERIC(8,2)); OPEN cur1; emp_loop: LOOP FETCH cur1 INTO l_employee_id, l_salary, l_department_id; IF done=1 THEN /* No more rows */ LEAVE emp_loop; END IF; CALL new_salary(1_employee_id, l_new_salary); /* Get new salary */ IF (l_new_salary <> l_salary) THEN /* Salary changed */ UPDATE employees SET salary=l_new_salary WHERE employee_id=l_employee_id; /* Keep track of changed salaries */ INSERT INTO emp_raises(employee_id, department_id, new_salary) VALUES (l_employee_id, l_department_id, l_new_salary); END IF: END LOOP emp_loop; CLOSE cur1; /* Print out the changed salaries */ SELECT employee_id, department_id, new_salary from emp_raises ORDER BY employee_id; END;

11，存储方法

存储方法与存储过程的区别

1，存储方法的参数列表只允许IN类型的参数，而且没必要也不允许指定IN关键字

2，存储方法返回一个单一的值，值的类型在存储方法的头部定义

3，存储方法可以在SQL语句内部调用

4，存储方法不能返回结果集

语法：

  1. CREATE  
  2.      [DEFINER = { user | CURRENT_USER }]  
  3.      PROCEDURE sp_name ([proc_parameter[,...]])  
  4.      [characteristic ...] routine_body  
  5.   
  6. CREATE  
  7.      [DEFINER = { user | CURRENT_USER }]  
  8.      FUNCTION sp_name ([func_parameter[,...]])  
  9.      RETURNS type  
  10.      [characteristic ...] routine_body  
  11.       
  12. proc_parameter:  
  13.      [ IN | OUT | INOUT ] param_name type  
  14.       
  15. func_parameter:  
  16.      param_name type  
  17.   
  18. type:  
  19.      Any valid MySQL data type  
  20.   
  21. characteristic:  
  22.      LANGUAGE SQL  
  23.    | [NOT] DETERMINISTIC  
  24.    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }  
  25.    | SQL SECURITY { DEFINER | INVOKER }  
  26.    | COMMENT 'string'  
  27.   
  28. routine_body:  
  29.      Valid SQL procedure statement  

 

CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body CREATE [DEFINER = { user | CURRENT_USER }] FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type func_parameter: param_name type type: Any valid MySQL data type characteristic: LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string' routine_body: Valid SQL procedure statement

各参数说明见CREATE PROCEDURE and CREATE FUNCTION Syntax

例子：

 

  1. DELIMITER $$  
  2.   
  3. DROP FUNCTION IF EXISTS f_discount_price$$  
  4. CREATE FUNCTION f_discount_price  
  5.      (normal_price NUMERIC(8,2))  
  6.      RETURNS NUMERIC(8,2)  
  7.      DETERMINISTIC  
  8. BEGIN  
  9.      DECLARE discount_price NUMERIC(8,2);  
  10.   
  11.      IF (normal_price > 500) THEN  
  12.          SET discount_price = normal_price * .8;  
  13.      ELSEIF (normal_price >100) THEN  
  14.          SET discount_price = normal_price * .9;  
  15.      ELSE  
  16.          SET discount_price = normal_price;  
  17.      END IF;  
  18.   
  19.      RETURN(discount_price);  
  20. END$$  
  21.   
  22. DELIMITER ;  

DELIMITER $$ DROP FUNCTION IF EXISTS f_discount_price$$ CREATE FUNCTION f_discount_price (normal_price NUMERIC(8,2)) RETURNS NUMERIC(8,2) DETERMINISTIC BEGIN DECLARE discount_price NUMERIC(8,2); IF (normal_price > 500) THEN SET discount_price = normal_price * .8; ELSEIF (normal_price >100) THEN SET discount_price = normal_price * .9; ELSE SET discount_price = normal_price; END IF; RETURN(discount_price); END$$ DELIMITER ;

12，触发器

触发器在INSERT、UPDATE或DELETE等DML语句修改数据库表时触发

触发器的典型应用场景是重要的业务逻辑、提高性能、监控表的修改等

触发器可以在DML语句执行前或后触发

 

  1. DELIMITER $$  
  2.   
  3. DROP TRIGGER sales_trigger$$  
  4. CREATE TRIGGER sales_trigger  
  5.      BEFORE INSERT ON sales  
  6.      FOR EACH ROW  
  7. BEGIN  
  8.      IF NEW.sale_value > 500 THEN  
  9.          SET NEW.free_shipping = 'Y';  
  10.      ELSE  
  11.          SET NEW.free_shipping = 'N';  
  12.      END IF;  
  13.   
  14.      IF NEW.sale_value > 1000 THEN  
  15.          SET NEW.discount = NEW.sale_value * .15;  
  16.      ELSE  
  17.          SET NEW.discount = 0;  
  18.      END IF;  
  19. END$$  
  20.   
  21. DELIMITER ;

============================================================

查看存储过程：
方法一：
       select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'
方法二：
         show procedure status;
1.修改mysql 存储过程的definer
修改mysql.proc表 的definer字段
Sql代码
update mysql.proc set definer='author@%'
update mysql.proc set security_type='INVOKER' where db='MC';
UPDATE `mysql`.`proc` SET `definer`='author%' WHERE `db`='test' AND `name`='proc_name' AND `type`='PROCEDURE';

UPDATE `mysql`.`proc` SET `definer`='xuzhijing@%' WHERE `db`='myDB'
AND `type`='PROCEDURE';

update mysql.proc set definer='root@%' where db='myDb';
UPDATE `mysql`.`proc` SET `definer`='root00@%' WHERE `db`='test' AND `name`='jjjj' AND `type`='PROCEDURE';

UPDATE `mysql`.`proc` SET `definer`='wtc_678869@%' WHERE `db`='myDB' AND `type`='PROCEDURE';

2.修改security_type

Sql代码
update mysql.proc set security_type='INVOKER' where db='myDB';

或者

delimiter //

CREATE PROCEDURE simproc_definer (OUT param1 INT)

SQL SECURITY DEFINER

BEGIN

SELECT COUNT(*) INTO param1 FROM aaa;

END//


（1）MySQL存储过程是通过指定SQL SECURITY子句指定执行存储过程的实际用户；

（2）如果SQL SECURITY子句指定为DEFINER，存储过程将使用存储过程的DEFINER执行存储过程，验证调用存储过程的用户是否具有存储过程的execute权限和DEFINER用户是否具有存储过程引用的相关对象的权限；

（3）如果SQL SECURITY子句指定为INVOKER，那么MySQL将使用当前调用存储过程的用户执行此过程，并验证用户是否具有存储过程的execute权限和存储过程引用的相关对象的权限；

（4）如果不显示的指定SQL SECURITY子句，MySQL默认将以DEFINER执行存储过程。

3.执行存储过程授权
Sql代码
GRANT EXECUTE ON test.* TO 'xuzhijing'@'%'；

GRANT CREATE ROUTINE,ALTER ROUTINE, SELECT,CREATE, INSERT, UPDATE, DELETE, EXECUTE ON test.* TO'xuzhijing'@'%' IDENTIFIED BY '111111'

GRANT EXECUTE ON test.* TO 'xuzhijing'@'%'；

GRANT CREATE ROUTINE,ALTER ROUTINE, SELECT,CREATE, INSERT, UPDATE, DELETE, EXECUTE ON test.* TO'xuzhijing'@'%' IDENTIFIED BY '111111'

CREATE ROUTINE ： 创建存储过程的权限
ALTER ROUTINE ： 修改存储过程的权限

4.删除用户

REVOKE all ON test.* FROM xuzhijing@'%'


DELETE FROM user WHERE User='user_name' and Host='host_name';

FLUSH PRIVILEGES;

如何设置淘宝直通车 如何设置淘宝直通车
原创粉丝点击
热门IT博客
犀牛建模软件犀牛建模软件
手机同屏软件手机同屏软件
沥青层上刷js有用吗
mac系统怎么安装flash
分类算法训练数据集
吉他初学者 知乎
赣州骏诚网络
加工贸易利用外资数据
社交网络的好处与坏处
pg数据库 decode
古剑奇谭 mac
淘宝天天特价入口
金建数据软件
集体智慧编程代码
阿里云校园扶持
淘宝客服管理方案
查看本地数据库ip
数据采集控制系统
g76锥度螺纹编程实例
怎么ping网络通不通
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 山煤国际分析 山煤集团下属煤矿名单 600546 山煤国际 山煤国际600546 山煤国际股票代码 山煤国际资金流向 山煤国际停牌 山煤国际能源集团 山煤国际股票分析 600546山煤国际股吧 山煤国际重组 山煤集团所有领导名字 山煤集团的21个煤矿2018 山煤国际股票怎么样 杨红梅秦腔木偶戏全本上煤山 山特 山特ups电池 山特mt1000 山特ups公司 山特ups价格 ups山特 山特ups代理 山特ups指示灯图解 山特ups电源故障灯图解 ups电源 山特 山特ups电源1kva 山特 ups 电源 山特ups电池报价 山特ups电源价格 山特在线式ups报价 山特ups电源多少钱 山特ups电源报价 山特 ups电源 山特ups不间断电源价格 山特 ups不间断电源 山特15kva ups 山特ups电源维修图纸 山特ups不间断电源 美国山特 ups电源蓄电池 ups电源

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里