思享工具箱

MySQL存储过程（转）

摘要：
MySQL存储过程（转）一、MySQL创建存储过程“pr_add”是个简单的MySQL存储过程，这个存储过程有两个int类型的输入参数“a”、“b”，返回这两个参数的和。/end;二、调用Mysql存储过程eg：callpr_add;执行MySQL存储过程，存储过程参数为MySQL用户变量。createprocedurepr_add3.MySQL存储过程的参数不能指定默认值。而SQLServer存储过程必须加“as”关键字。setc=a+b;selectcassum;/*returnc;-不能在MySQL存储过程中使用。
MySQL存储过程（转）

一、MySQL 创建存储过程
“pr_add” 是个简单的 MySQL 存储过程，这个存储过程有两个 int 类型的输入参数 “a”、“b”，返回这两个参数的和。

drop procedure if exists pr_add;
-- 计算两个数之和
create procedure pr_add
(
a int,
b int
)
begin
declare c int;
if a is null then
set a = 0;
end if;
if b is null then
set b = 0;
end if;
set c = a + b;
select c as sum;
/*
return c;- 不能在 MySQL 存储过程中使用。return 只能出现在函数中。
/
end;
二、调用Mysql存储过程
eg： call pr_add(10,20);
执行 MySQL 存储过程，存储过程参数为 MySQL 用户变量。
set @a = 10;
set @b = 20;
call pr_add(@a, @b);
三、MySQL 存储过程特点
创建 MySQL 存储过程的简单语法为：
create procedure 存储过程名字()
(
[in|out|inout] 参数 datatype
)
begin
MySQL 语句;
end;
MySQL 存储过程参数如果不显式指定“in”、“out”、“inout”，则默认为“in”。习惯上，对于是“in” 的参数，我们都不会显式指定。
如下，给出解释
1. MySQL 存储过程名字后面的“()”是必须的，即使没有一个参数，也需要“()”
2. MySQL 存储过程参数，不能在参数名称前加“@”，如：“@aint”。下面的创建存储过程语法在 MySQL 中是错误的（在 SQL Server 中是正确的）。 MySQL 存储过程中的变量，不需要在变量名字前加“@”，虽然 MySQL 客户端用户变量要加个“@”。
create procedure pr_add
(
@a int,- 错误
b int - 正确
)
3. MySQL 存储过程的参数不能指定默认值。
4. MySQL 存储过程不需要在 procedure body 前面加 “as”。而 SQL Server 存储过程必须加 “as” 关键字。
create procedure pr_add
(
a int,
b int
)
as - 错误，MySQL不需要 “as”
begin
mysql statement ...;
end;
5. 如果 MySQL 存储过程中包含单条或者多条 MySQL 语句，都需要 begin end 关键字。
create procedure pr_add
(
a int,
b int
)
begin
mysql statement 1 ...;
mysql statement 2 ...;
end;
6.MySQL 存储过程中的每条语句的末尾，都要加上分号 “;”
...
declare c int;
if a is null then
set a = 0;
end if;
...
7. MySQL 存储过程中的注释。
/*
这是个
多行 MySQL 注释。
/
declare c int; - 这是单行 MySQL 注释 （注意- 后至少要有一个空格）
if a is null then 这也是个单行 MySQL 注释
set a = 0;
end if;
...
end;
8. 不能在 MySQL 存储过程中使用 “return” 关键字。
set c = a + b;
select c as sum;
/*
return c;- 不能在 MySQL 存储过程中使用。return 只能出现在函数中。
/
end;

9. 调用 MySQL 存储过程时候，需要在过程名字后面加“()”，即使没有一个参数，也需要“()”
call pr_no_param();

10. 因为 MySQL 存储过程参数没有默认值，所以在调用 MySQL 存储过程时候，不能省略参数。可以用 null 来替代。
call pr_add(10, null);
1、当然以上这些，前提是 mysql 5
2、下面写一个mysql存储过程之 HelloWorld
  1. DELIMITER$$
  3. DROPPROCEDUREIFEXISTSHelloWorld$$
  4. CREATEPROCEDUREHelloWorld()
  5. BEGIN
  6. SELECT"HelloWorld!";
  7. END$$
  9. DELIMITER;

3、变量

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

  1. DECLAREcounterINTDEFAULT0; - 默认为0
  2. SETcounter=counter+1; - 自增+1

4、参数

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

eg:

  1. CREATEPROCEDUREtest(aINT,OUTbFLOAT,INOUTcINT)

5、条件判断

eg:

  1. DELIMITER$$
  3. DROPPROCEDUREIFEXISTSdiscounted_price$$
  4. CREATEPROCEDUREdiscunted_price(normal_priceNUMERIC(8,2),OUTdiscount_priceNUMERIC(8,2))
  5. BEGIN
  6. IF(normal_price>500)THEN
  7. SETdiscount_price=normal_price*.8;
  8. ELSEIF(normal_price>100)THEN
  9. SETdiscount_price=normal_price*.9;
  10. ELSE
  11. SETdiscount_price=normal_price;
  12. ENDIF;
  13. END$$
  15. DELIMITER;

6、循环

LOOP、END LOOP

eg:

  1. DELIMITER$$
  3. DROPPROCEDUREIFEXISTSsimple_loop$$
  5. CREATEPROCEDUREsimple_loop(OUTcounterINT)
  6. BEGIN
  7. SETcounter=0;
  8. my_simple_loop:LOOP
  9. SETcounter=counter+1;
  10. IFcounter=10THEN
  11. LEAVEmy_simple_loop;
  12. ENDIF;
  13. ENDLOOPmy_simple_loop;
  14. END$$
  16. DELIMITER;

WHILE DO、END WHILE

  1. DELIMITER$$
  3. DROPPROCEDUREIFEXISTSsimple_while$$
  5. CREATEPROCEDUREsimple_while(OUTcounterINT)
  6. BEGIN
  7. SETcounter=0;
  8. WHILEcounter!=10DO
  9. SETcounter=counter+1;
  10. ENDWHILE;
  11. END$$
  13. DELIMITER;

REPEAT、UNTILL

  1. DELIMITER$$
  3. DROPPROCEDUREIFEXISTSsimple_repeat$$
  5. CREATEPROCEDUREsimple_repeat(OUTcounterINT)
  6. BEGIN
  7. SETcounter=0;
  8. REPEAT
  9. SETcounter=counter+1;
  10. UNTILcounter=10ENDREPEAT;
  11. END$$
  13. DELIMITER;

7，异常处理
如果用cursor获取SELECT语句返回的所有结果集时应该定义NOT FOUND error handler来防止存储程序提前终结
如果SQL语句可能返回constraint violation等错误时应该创建一个handler来防止程序终结
8，数据库交互
INTO用于存储单行记录的查询结果

  1. DECLAREtotal_salesNUMERIC(8,2);
  2. SELECTSUM(sale_value)INTOtotal_salesFROMsalesWHEREcustomer_id=in_customer_id;

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

  1. DELIMITER$$
  3. DROPPROCEDUREIFEXITScursor_example$$
  4. CREATEPROCEDUREcursor_example()
  5. READSSQLDATA
  6. BEGIN
  7. DECLAREl_employee_idINT;
  8. DECLAREl_salaryNUMERIC(8,2);
  9. DECLAREl_department_idINT;
  10. DECLAREdoneINTDEFAULT0;
  11. DECLAREcur1CURSORFORSELECTemployee_id,salary,department_idFROMemployees;
  12. DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;
  14. OPENcur1;
  15. emp_loop:LOOP
  16. FETCHcur1INTOl_employee_id,l_salary,l_department_id;
  17. IFdone=1THEN
  18. LEAVEemp_loop;
  19. ENDIF;
  20. ENDLOOPemp_loop;
  21. CLOSEcur1;
  22. END$$
  23. DELIMITER;

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

  1. DELIMITER$$
  2. DROPPROCEDUREIFEXISTSsp_emps_in_dept$$
  3. CREATEPROCEDUREsp_emps_in_dept(in_employee_idINT)
  4. BEGIN
  5. SELECTemployee_id,surname,firstname,address1,address2,zipcode,date_of_birthFROMemployeesWHEREdepartment_id=in_employee_id;
  6. END$$
  8. DELIMITER;

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

  1. DELIMITER$$
  3. DROPPROCEDUREIFEXITSsp_update_salary$$
  4. CREATEPROCEDUREsp_update_salary(in_employee_idINT,in_new_salaryNUMERIC(8,2))
  5. BEGIN
  6. IFin_new_salary<5000ORin_new_salary>500000THEN
  7. SELECT"Illegalsalary:salarymustbebetween$5000and$500,000";
  8. ELSE
  9. UPDATEemployeesSETsalary=in_new_salaryWHEREemployee_id=in_employee_id;
  10. ENDIF:
  11. END$$
  13. DELIMITER;

9，使用CALL调用存储程序

  1. DELIMITER$$
  3. DROPPROCEDUREIFEXISTScall_example$$
  4. CREATEPROCEDUREcall_example(employee_idINT,employee_typeVARCHAR(20))
  5. NOSQL
  6. BEGIN
  7. DECLAREl_bonus_amountNUMERIC(8,2);
  9. IFemployee_type='MANAGER'THEN
  10. CALLcalc_manager_bonus(employee_id,l_bonus_amount);
  11. ELSE
  12. CALLcalc_minion_bonus(employee_id,l_bonus_amount);
  13. ENDIF;
  14. CALLgrant_bonus(employee_id,l_bonus_amount);
  15. END$$
  16. DELIMITER;

10，一个复杂的例子

  1. CREATEPROCEDUREputting_it_all_together(in_department_idINT)
  2. MODIFIESSQLDATA
  3. BEGIN
  4. DECLAREl_employee_idINT;
  5. DECLAREl_salaryNUMERIC(8,2);
  6. DECLAREl_department_idINT;
  7. DECLAREl_new_salaryNUMERIC(8,2);
  8. DECLAREdoneINTDEFAULT0;
  10. DECLAREcur1CURSORFOR
  11. SELECTemployee_id,salary,department_id
  12. FROMemployees
  13. WHEREdepartment_id=in_department_id;
  15. DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;
  17. CREATETEMPORARYTABLEIFNOTEXISTSemp_raises
  18. (employee_idINT,department_idINT,new_salaryNUMERIC(8,2));
  20. OPENcur1;
  21. emp_loop:LOOP
  22. FETCHcur1INTOl_employee_id,l_salary,l_department_id;
  23. IFdone=1THEN/*Nomorerows*/
  24. LEAVEemp_loop;
  25. ENDIF;
  26. CALLnew_salary(1_employee_id,l_new_salary);/*Getnewsalary*/
  27. IF(l_new_salary<>l_salary)THEN/*Salarychanged*/
  28. UPDATEemployees
  29. SETsalary=l_new_salary
  30. WHEREemployee_id=l_employee_id;
  31. /*Keeptrackofchangedsalaries*/
  32. INSERTINTOemp_raises(employee_id,department_id,new_salary)
  33. VALUES(l_employee_id,l_department_id,l_new_salary);
  34. ENDIF:
  35. ENDLOOPemp_loop;
  36. CLOSEcur1;
  37. /*Printoutthechangedsalaries*/
  38. SELECTemployee_id,department_id,new_salaryfromemp_raises
  39. ORDERBYemployee_id;
  40. END;

11，存储方法
存储方法与存储过程的区别
1，存储方法的参数列表只允许IN类型的参数，而且没必要也不允许指定IN关键字
2，存储方法返回一个单一的值，值的类型在存储方法的头部定义
3，存储方法可以在SQL语句内部调用
4，存储方法不能返回结果集
语法：

  1. CREATE
  2. [DEFINER={user|CURRENT_USER}]
  3. PROCEDUREsp_name([proc_parameter[,...]])
  4. [characteristic...]routine_body
  6. CREATE
  7. [DEFINER={user|CURRENT_USER}]
  8. FUNCTIONsp_name([func_parameter[,...]])
  9. RETURNStype
  10. [characteristic...]routine_body
  12. proc_parameter:
  13. [IN|OUT|INOUT]param_nametype
  15. func_parameter:
  16. param_nametype
  18. type:
  19. AnyvalidMySQLdatatype
  21. characteristic:
  22. LANGUAGESQL
  23. |[NOT]DETERMINISTIC
  24. |{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}
  25. |SQLSECURITY{DEFINER|INVOKER}
  26. |COMMENT'string'
  28. routine_body:
  29. ValidSQLprocedurestatement

各参数说明见CREATE PROCEDURE and CREATE FUNCTION Syntax
例子：

  1. DELIMITER$$
  3. DROPFUNCTIONIFEXISTSf_discount_price$$
  4. CREATEFUNCTIONf_discount_price
  5. (normal_priceNUMERIC(8,2))
  6. RETURNSNUMERIC(8,2)
  7. DETERMINISTIC
  8. BEGIN
  9. DECLAREdiscount_priceNUMERIC(8,2);
  11. IF(normal_price>500)THEN
  12. SETdiscount_price=normal_price*.8;
  13. ELSEIF(normal_price>100)THEN
  14. SETdiscount_price=normal_price*.9;
  15. ELSE
  16. SETdiscount_price=normal_price;
  17. ENDIF;
  19. RETURN(discount_price);
  20. END$$
  22. DELIMITER;

12，触发器
触发器在INSERT、UPDATE或DELETE等DML语句修改数据库表时触发
触发器的典型应用场景是重要的业务逻辑、提高性能、监控表的修改等
触发器可以在DML语句执行前或后触发

  1. DELIMITER$$
  3. DROPTRIGGERsales_trigger$$
  4. CREATETRIGGERsales_trigger
  5. BEFOREINSERTONsales
  6. FOREACHROW
  7. BEGIN
  8. IFNEW.sale_value>500THEN
  9. SETNEW.free_shipping='Y';
  10. ELSE
  11. SETNEW.free_shipping='N';
  12. ENDIF;
  14. IFNEW.sale_value>1000THEN
  15. SETNEW.discount=NEW.sale_value*.15;
  16. ELSE
  17. SETNEW.discount=0;
  18. ENDIF;
  19. END$$
  21. DELIMITER;

标签：#存储过程#mysql#mysql创建数据库#mysql创建存储过程#mysql函数 | 浏览：320 | 发布日期：

免责声明：文章转载自《MySQL存储过程（转）》仅用于学习参考。如对内容有疑问，请及时联系本站处理。

上篇Mac下破解intellij IDEA 2018Yapi部署说明下篇

宿迁高防，2C2G15M，22元/月；香港BGP，2C5G5M，25元/月 雨云优惠码：MjYwNzM=

相关文章

Apache HTTP服务器扩展模块的工具-apxs

1.安装httpd # yum install httpd    # service httpd start  在地址栏里输入http://172.16.22.1（你输入你自己的IP地址，这个是我的），验证web服务是否可以正常工作。 2.安装mysql 方法和LAMP（linux下apache+mysql+php）平台编译安装的实现的安装mysql的...

zabbix 安装

1、在已有的LAMP或者LNMP的基础上安装zabbix，安装一些依赖包： yum -y install mysql-devel libcurl-devel net-snmp-devel 2、添加用户： groupadd zabbixuseradd zabbix -g zabbix 3、创建数据库，添加授权账号 mysql -uxxx  -pxxx my...

mysql-connector-java与Mysql、Java的对应版本

转载自 https://blog.csdn.net/lingeio/article/details/93487770 mysql-connector-java与Mysql对应版本： MySQL Connector/J 8.0 is highly recommended for use with MySQL Server 8.0, 5.7, and 5.6...

mysql日期加减

一、MySQL 为日期增加一个时间间隔：date_add()。 1、  示例： set @dt = now(); select date_add(@dt, interval 1 day);           - 加1天 select date_add(@dt, interval 1 hour);                -加1小时 select d...

PostgreSQL概述

PostgreSQL概述 概要介绍： PostgreSQL是一个功能强大的开源数据库系统。经过长达15年以上的积极开发和不断改进，PostgreSQL已在可靠性、稳定性、数据一致性等获得了业内极高的声誉。目前PostgreSQL可以运行在所有主流操作系统上，包括Linux、Unix（AIX、BSD、HP-UX、SGI IRIX、Mac OS X、Solar...

MySQL 集群

MySQL Galera介绍主要功能: 同步复制 真正的multi-master，即所有节点可以同时读写数据库 自动的节点成员控制，失效节点自动被清除 新节点加入数据自动复制 真正的并行复制，行级 用户可以直接连接集群，使用感受上与MySQL完全一致 优势: 因为是多主，所以不存在Slave lag(延迟) 不存在丢失交易的情况 同时具有读和写的扩展...

最新文章

随机推荐

思享工具箱导航