MYSQL存储过程怎么写
DELIMITER $$
DROP PROCEDURE IF EXISTS HelloWorld$$
CREATE PROCEDURE HelloWorld()
BEGIN
SELECT "Hello World!";
END$$
DELIMITER ;
  3，变量
  DECLARE声明，SET赋值
可以在DECLARE变量时⽤DEFAULT设置默认值，不设则为NULL
DECLARE counter INT DEFAULT 0;
SET counter = counter+1;
  4，参数
IN为默认类型，值必须在调⽤时指定，值不能返回（值传递）
OUT值可以返回（指针传递）
INOUT值必须在调⽤时指定，值可以返回
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
代码
DELIMITER $$
mysql数据库创建表格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
mysql查看所有存储过程代码
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
代码
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
代码
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⽤于存储单⾏记录的查询结果
DECLARE total_sales NUMERIC(8, 2);
SELECT SUM(sale_value) INTO total_sales FROM sales WHERE customer_id=in_customer_id;
  CURSOR⽤于处理多⾏记录的查询结果
代码
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语句⽤于存储过程返回结果集
代码
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等⾮查询语句也可以嵌⼊存储过程⾥
代码
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调⽤存储程序
枇杷膏的制作方法和配方
代码
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，⼀个复杂的例⼦
代码
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 */listview控件最新版
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，存储⽅法不能返回结果集
语法：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。