mysql procedure call_MySQL存储过程

DELIMITER $$DROPPROCEDUREIFEXISTSHelloWorld$$CREATEPROCEDUREHelloWorld()BEGINSELECT"Hello World!";END$$

DELIMITER ;

3，变量

DECLARE声明，SET赋值

可以在DECLARE变量时用DEFAULT设置默认值，不设则为NULL

DECLAREcounterINTDEFAULT0;SETcounter=counter+1;

4，参数

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

OUT值可以返回(指针传递)

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

CREATEPROCEDUREtest(aINT, OUT bFLOAT, INOUT cINT)CREATEPROCEDUREtest(aINT, OUT bFLOAT, INOUT cINT)

5，条件判断

IF THEN、ELSEIF、ELSE、END IF

代码

DELIMITER $$DROPPROCEDUREIFEXISTSdiscounted_price$$CREATEPROCEDUREdiscunted_price(normal_price NUMERIC(8,2), OUT discount_price NUMERIC(8,2))BEGINIF(normal_price>500)THENSETdiscount_price=normal_price*.8;

ELSEIF (normal_price>100)THENSETdiscount_price=normal_price*.9;ELSESETdiscount_price=normal_price;ENDIF;END$$

DELIMITER ;

6，循环

LOOP、END LOOP

代码

DELIMITER $$DROPPROCEDUREIFEXISTSsimple_loop$$CREATEPROCEDUREsimple_loop(OUT counterINT)BEGINSETcounter=0;

my_simple_loop:LOOPSETcounter=counter+1;IFcounter=10THENLEAVEmy_simple_loop;ENDIF;ENDLOOPmy_simple_loop;END$$

DELIMITER ;

WHILE DO、END WHILE

代码

DELIMITER $$DROPPROCEDUREIFEXISTSsimple_while$$CREATEPROCEDUREsimple_while(OUT counterINT)BEGINSETcounter=0;WHILEcounter!=10DOSETcounter=counter+1;ENDWHILE;END$$

DELIMITER ;

REPEAT、UNTILL

代码

DELIMITER $$DROPPROCEDUREIFEXISTSsimple_repeat$$CREATEPROCEDUREsimple_repeat(OUT counterINT)BEGINSETcounter=0;

REPEATSETcounter=counter+1;

UNTIL counter=10ENDREPEAT;END$$

DELIMITER ;

7，异常处理

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

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

8，数据库交互

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

DECLAREtotal_sales NUMERIC(8,2);SELECTSUM(sale_value)INTOtotal_salesFROMsalesWHEREcustomer_id=in_customer_id;

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

代码

DELIMITER $$DROPPROCEDUREIFEXITS cursor_example$$CREATEPROCEDUREcursor_example()

READS SQL DATABEGINDECLAREl_employee_idINT;DECLAREl_salary NUMERIC(8,2);DECLAREl_department_idINT;DECLAREdoneINTDEFAULT0;DECLAREcur1CURSORFORSELECTemployee_id, salary, department_idFROMemployees;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;OPENcur1;

emp_loop: LOOPFETCHcur1INTOl_employee_id, l_salary, l_department_id;IFdone=1THENLEAVE emp_loop;ENDIF;ENDLOOP emp_loop;CLOSEcur1;END$$

DELIMITER ;

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

代码

DELIMITER $$DROPPROCEDUREIFEXISTSsp_emps_in_dept$$CREATEPROCEDUREsp_emps_in_dept(in_employee_idINT)BEGINSELECTemployee_id, surname, firstname, address1, address2, zipcode, date_of_birthFROMemployeesWHEREdepartment_id=in_employee_id;END$$

DELIMITER ;

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

代码

DELIMITER $$DROPPROCEDUREIFEXITS sp_update_salary$$CREATEPROCEDUREsp_update_salary(in_employee_idINT, in_new_salary NUMERIC(8,2))BEGINIFin_new_salary<5000ORin_new_salary>500000THENSELECT"Illegal salary: salary must bebetween$5000and$500,000";ELSEUPDATEemployeesSETsalary=in_new_salaryWHEREemployee_id=in_employee_id;ENDIF;END$$

DELIMITER ;

9，使用CALL调用存储程序

代码

DELIMITER $$DROPPROCEDUREIFEXISTScall_example$$CREATEPROCEDUREcall_example(employee_idINT, employee_typeVARCHAR(20))

NO SQLBEGINDECLAREl_bonus_amount NUMERIC(8,2);IFemployee_type='MANAGER'THENCALL calc_manager_bonus(employee_id, l_bonus_amount);ELSECALL calc_minion_bonus(employee_id, l_bonus_amount);ENDIF;

CALL grant_bonus(employee_id, l_bonus_amount);END$$

DELIMITER ;

10，一个复杂的例子

代码

CREATEPROCEDUREputting_it_all_together(in_department_idINT)

MODIFIES SQL DATABEGINDECLAREl_employee_idINT;DECLAREl_salary NUMERIC(8,2);DECLAREl_department_idINT;DECLAREl_new_salary NUMERIC(8,2);DECLAREdoneINTDEFAULT0;DECLAREcur1CURSORFORSELECTemployee_id, salary, department_idFROMemployeesWHEREdepartment_id=in_department_id;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;CREATETEMPORARYTABLEIFNOTEXISTSemp_raises

(employee_idINT, department_idINT, new_salary NUMERIC(8,2));OPENcur1;

emp_loop: LOOPFETCHcur1INTOl_employee_id, l_salary, l_department_id;IFdone=1THEN/*No more rows*/LEAVE emp_loop;ENDIF;

CALL new_salary(1_employee_id, l_new_salary);/*Get new salary*/IF(l_new_salary<>l_salary)THEN/*Salary changed*/UPDATEemployeesSETsalary=l_new_salaryWHEREemployee_id=l_employee_id;/*Keep track of changed salaries*/INSERTINTOemp_raises(employee_id, department_id, new_salary)VALUES(l_employee_id, l_department_id, l_new_salary);ENDIF:ENDLOOP emp_loop;CLOSEcur1;/*Print out the changed salaries*/SELECTemployee_id, department_id, new_salaryfromemp_raisesORDERBYemployee_id;END;

11，存储方法

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

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

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

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

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

语法：

代码

CREATE[DEFINER = { user | CURRENT_USER }]PROCEDUREsp_name ([proc_parameter[,...]])[characteristic ...]routine_bodyCREATE[DEFINER = { user | CURRENT_USER }]FUNCTIONsp_name ([func_parameter[,...]])RETURNStype[characteristic ...]routine_body

proc_parameter:[IN | OUT | INOUT]param_name type

func_parameter:

param_name type

type:Anyvalid MySQL data type

characteristic:

LANGUAGE SQL|[NOT]DETERMINISTIC|{CONTAINSSQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA }|SQL SECURITY { DEFINER|INVOKER }|COMMENT'string'routine_body:

Valid SQLprocedurestatement

例子：

代码

DELIMITER $$DROPFUNCTIONIFEXISTSf_discount_price$$CREATEFUNCTIONf_discount_price

(normal_price NUMERIC(8,2))RETURNSNUMERIC(8,2)

DETERMINISTICBEGINDECLAREdiscount_price NUMERIC(8,2);IF(normal_price>500)THENSETdiscount_price=normal_price*.8;

ELSEIF (normal_price>100)THENSETdiscount_price=normal_price*.9;ELSESETdiscount_price=normal_price;ENDIF;RETURN(discount_price);END$$

DELIMITER ;

12，触发器

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

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

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

代码

DELIMITER $$DROPTRIGGERsales_trigger$$CREATETRIGGERsales_trigger

BEFOREINSERTONsalesFOREACH ROWBEGINIFNEW.sale_value>500THENSETNEW.free_shipping='Y';ELSESETNEW.free_shipping='N';ENDIF;IFNEW.sale_value>1000THENSETNEW.discount=NEW.sale_value*.15;ELSESETNEW.discount=0;ENDIF;END$$

DELIMITER ;