[MySQL 08] 저장프로시저

우선 SQL 저장 프로시저의 경우 Java의 정의 방식 및 호출 방식과 유사합니다.

1. 저장 프로시저 생성

MySQL에서 저장 프로시저의 기본 형식은

CREATE PROCEDURE sp_name ([proc_parameter[,...]])  
        [characteristic ...] routine_body

• sp_name 매개변수의 이름입니다.

• proc_parameter는 저장 프로시저의 매개변수 목록을 나타냅니다.

• 특성 매개변수는 저장 프로시저의 특성을 지정합니다.

• routine_body 매개변수는 SQL 코드의 내용입니다. BEGIN...END를 사용하여 SQL 코드의 시작과 끝을 표시할 수 있습니다.

• proc_parameter의 각 매개변수는 3개의 부분으로 구성됩니다. 이 세 부분은 입력 및 출력 유형, 매개변수 이름 및 매개변수 유형입니다. 형식은 다음과 같습니다.

[ IN | OUT | INOUT ] param_name type

• IN은 입력 매개변수를 나타냅니다.

• OUT은 출력 매개변수를 나타냅니다.

INOUT은 입력 또는 출력이 가능함을 의미합니다.
param_name 매개변수는 저장 프로시저의 매개변수 이름입니다. >
• 유형 매개변수는 MySQL 데이터베이스의 모든 데이터 유형이 될 수 있는 저장 프로시저의 매개변수 유형을 지정합니다.

• 간단한 정의:

  create procedure 过程名(参数...)
  begin
      SQL语句...end

  호출:

call 过程名(实参)

참고:

정의하기 전에 기본 문 끝 ';'을 '&&'와 같은 다른 것으로 변경해야 저장 프로시저에 정의된 세미콜론이 다음과 같이 간주되지 않습니다. 명세서 끝(그렇지 않으면 직접 제출됩니다). 정의한 후에는 ';'을 기본 종결자로 복원해야 합니다.

2. 매개변수가 없는 저장 프로시저

//建立存储过程mysql> delimiter %%
mysql> create procedure p1()    
-> begin    
-> select Sname,Ssex from student;    
-> end %%mysql> delimiter ;  
//调用存储过程mysql> call p1(); 
+--------+------+| Sname  | Ssex |
+--------+------+| 赵英    | 女   |
| 郑美丽  | 女   |
| 李哲    | 男   |
| 王洋    | 女   |
| 王平    | 男   |
| 赵东新  | 男   |
| 王新    | 男   |
| 陶丽平  | 男   |
| 陈文    | 女   |
| 蔡天    | 女   || 杨洋    | 男   |
+--------+------+

3. 매개변수가 있는 저장 프로시저

//查询mysql> select * from employee;
+------------+------------+------------+--------------+| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 || 6651       | Ajay Patel | Programmer |          128 |
+------------+------------+------------+--------------+//建立存储过程mysql> delimiter &&
mysql> create procedure p4(in employeeID char(4),in name varchar(20),in job varchar(20),in departmentID int(11))    -> begin    
-> insert into employee value(employeeID,name,job,departmentID);    -> select * from employee;    -> end &&mysql> delimiter ;  
//调用存储过程mysql> call p4('7322','cid','udewhuuwdwq',127);
+------------+------------+-------------+--------------+| employeeID | name       | job         | departmentID |
+------------+------------+-------------+--------------+| 6651       | Ajay Patel | Programmer  |          128 |
| 7513       | Nora Edwar | Programmer  |          128 |
| 9006       | Candy Burn | Systems Ad  |          128 |
| 9842       | Ben Smith  | DBA         |           42 |
| 9843       | Pert Park  | DBA         |           42 |
| 9845       | Ben Patel  | DBA         |          128 |
| 9846       | Red Right  | NULL        |          128 |
| 9847       | Run Wild   | NULL        |          128 |
| 9848       | Rip This J | NULL        |          128 |
| 9849       | Rip This J | NULL        |          128 |
| 9850       | Reader U   | NULL        |          128 |
| 6651       | Ajay Patel | Programmer  |          128 || 7322       | cid        | udewhuuwdwq |          127 |
+------------+------------+-------------+--------------+

4. 반환 값이 있는 저장 프로시저

//建立存储过程mysql> delimiter &&
mysql> create procedure p9(in employeeID char(4),in name varchar(20),in job varchar(20),in departmentID int(11),out num int)
    -> begin
    -> insert into employee value(employeeID,name,job,departmentID);
    -> select count(*) into num from employee;
    -> end &&

mysql> delimiter ; 

//调用存储过程mysql> call p9('3632','dueh','xianggang',28,@num);//注意输出变量的输出mysql> select @num;
+------+
| @num |
+------+
|   15 |
+------+//////////////////////////系统变量名称：@@变量名/// //用户变量名称：@变量名 ///

5. 변수의 사용

저장 프로시저와 함수에서는 변수를 정의하고 사용할 수 있습니다. 사용자는 DECLARE 키워드를 사용하여 변수를 정의할 수 있습니다. 그런 다음 변수에 값을 할당할 수 있습니다. 이러한 변수의 범위는 BEGIN...END 프로그램 섹션에 있습니다. 이 섹션에서는 변수를 정의하고 변수에 값을 할당하는 방법을 설명합니다.

1． 변수 정의

DECLARE 키워드를 사용하여 MySQL에서 변수를 정의할 수 있습니다. 변수를 정의하는 기본 구문은 다음과 같습니다.

DECLARE var_name[,...] type [DEFAULT value]

그 중 DECLARE 키워드는 변수를 선언하는 데 사용됩니다. var_name 매개변수는 변수 이름으로, 여러 변수를 동시에 정의할 수 있습니다. type 매개변수는 변수의 유형을 지정하는 데 사용됩니다. DEFAULT 값 절은 변수의 기본값을 value로 설정합니다. DEFAULT 절이 사용되지 않으면 기본값은 NULL입니다.

아아아아

2． 변수에 값 할당

MySQL에서 SET 키워드를 사용하여 변수에 값을 할당할 수 있습니다. SET 문의 기본 구문은 다음과 같습니다.

DECLARE  my_sql  INT  DEFAULT 10 ; //定义变量my_sql，数据类型为INT型，默认值为10

그 중 SET 키워드는 변수에 값을 할당하는 데 사용됩니다. var_name 매개변수는 변수의 이름입니다. 표현. SET 문은 동시에 여러 변수에 값을 할당할 수 있습니다. 각 변수에 대한 할당 문은 쉼표로 구분됩니다.

SET var_name = expr[,var_name = expr] ...

SELECT...INTO 문을 사용하여 MySQL의 변수에 값을 할당할 수도 있습니다. 기본 구문은 다음과 같습니다.

SELECT col_name[,…] INTO var_name[,…]

FROM table_name WEHRE 조건

이 중 col_name 매개변수는 쿼리 필드 이름을 나타냅니다. 변수의 이름입니다. table_name 매개변수는 테이블의 이름을 나타냅니다. 조건 매개변수는 쿼리 조건을 나타냅니다.

SET my_sql = 30 ; //为变量my_sql赋值为30

6. 조건 및 핸들러 정의

조건 및 핸들러를 정의한다는 것은 프로그램 실행 중에 발생할 수 있는 문제를 미리 정의하는 것입니다. 그리고 이러한 문제에 대한 해결책은 핸들러에서 정의할 수 있습니다. 이러한 접근 방식을 통해 발생할 수 있는 문제를 사전에 예측하고 해결책을 제안할 수 있습니다. 이렇게 하면 프로그램의 문제 처리 능력이 향상되고 프로그램이 비정상적으로 중지되는 것을 방지할 수 있습니다. MySQL에서는 조건과 핸들러가 DECLARE 키워드를 통해 정의됩니다. 이 섹션에서는 조건과 핸들러를 정의하는 방법을 자세히 설명합니다.

1． 조건 정의

DECLARE 키워드를 사용하여 MySQL에서 조건을 정의할 수 있습니다. 기본 구문은 다음과 같습니다.

DECLARE  condition_name  CONDITION  FOR  condition_value  
condition_value:  
      SQLSTATE [VALUE] sqlstate_value | mysql_error_code

其中，condition_name参数表示条件的名称；condition_value参数表示条件的类型；sqlstate_value参数和mysql_error_code参数都可以表示MySQL的错误。例如ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146。

//定义"ERROR 1146 (42S02)"这个错误，名称为can_not_find
//方法一：使用sqlstate_value  DECLARE  can_not_find  CONDITION  FOR  SQLSTATE  '42S02' ;  
//方法二：使用mysql_error_code  DECLARE  can_not_find  CONDITION  FOR  1146 ;

2．定义处理程序

MySQL中可以使用DECLARE关键字来定义处理程序。其基本语法如下：

DECLARE handler_type HANDLER FOR 
condition_value[,...] sp_statement  
handler_type:  
    CONTINUE | EXIT | UNDO  
condition_value:  
    SQLSTATE [VALUE] sqlstate_value |
condition_name  | SQLWARNING  
       | NOT FOUND  | SQLEXCEPTION  | mysql_error_code

• handler_type参数指明错误的处理方式，该参数有3个取值。这3个取值分别是CONTINUE、EXIT和UNDO。

• CONTINUE表示遇到错误不进行处理，继续向下执行； EXIT表示遇到错误后马上退出；

• UNDO表示遇到错误后撤回之前的操作，MySQL中暂时还不支持这种处理方式

注意：通常情况下，执行过程中遇到错误应该立刻停止执行下面的语句，并且撤回前面的操作。但是，MySQL中现在还不能支持UNDO操作。因此，遇到错误时最好执行EXIT操作。如果事先能够预测错误类型，并且进行相应的处理，那么可以执行CONTINUE操作。

condition_value参数指明错误类型，该参数有6个取值。sqlstate_value和mysql_error_code与条件定义中的是同一个意思。condition_name是DECLARE定义的条件名称。SQLWARNING表示所有以01开头的sqlstate_value值。NOT FOUND表示所有以02开头的sqlstate_value值。SQLEXCEPTION表示所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值。sp_statement表示一些存储过程或函数的执行语句。

//定义处理程序的几种方式
//方法一：捕获sqlstate_value  
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'SET @info='CAN NOT FIND';  
//方法二：捕获mysql_error_code  
DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND';  
//方法三：先定义条件，然后调用  
DECLARE  can_not_find  CONDITION  FOR  1146 ;  
DECLARE CONTINUE HANDLER FOR can_not_find SET 
@info='CAN NOT FIND';  
//方法四：使用SQLWARNING  
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';  
//方法五：使用NOT FOUND  
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND';  
//方法六：使用SQLEXCEPTION  
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

• 第一种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为42S02，执行CONTINUE操作，并且输出”CANNOT FIND”信息。

• 第二种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146，执行CONTINUE操作，并且输出”CAN NOT FIND”信息。

• 第三种方法是先定义条件，然后再调用条件。这里先定义can_not_find条件，遇到1146错误就执行CONTINUE操作。

• 第四种方法是使用SQLWARNING。SQLWARNING捕获所有以01开头的sqlstate_value值，然后执行EXIT操作，并且输出”ERROR”信息。

• 第五种方法是使用NOT FOUND。NOT FOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出”CAN NOT FIND”信息。

• 第六种方法是使用SQLEXCEPTION。SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作，并且输出”ERROR”信息。

以上就是 【MySQL 08】存储过程的内容，更多相关内容请关注PHP中文网（www.php.cn）！