mysql 저장 프로시저에 대한 자세한 설명

mysql/>MySQL 저장 프로시저

14.1.1 저장 프로시저 생성

MySQL에서 저장 프로시저를 생성하는 기본 형태는 다음과 같습니다.

프로시저 생성 sp_name([proc_parameter[,...]])
[특징...] routine_body

이 중 sp_name 매개변수는 저장 프로시저의 이름이고, proc_parameter는 저장 프로시저의 특성을 지정하며, routine_body 매개변수는 BEGIN의 내용입니다. ...END는 SQL 코드의 시작과 끝을 표시하는 데 사용할 수 있습니다.

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

[ IN | OUT | INOUT ] param_name 유형

그 중 IN은 입력 매개변수를 나타내고, OUT은 입력 또는 출력을 나타냅니다. param_name 매개변수는 저장 프로시저의 매개변수 유형을 지정합니다. MySQL이 될 수 있습니다. 데이터베이스의 모든 데이터 유형입니다.

특성 매개변수에는 여러 값이 있습니다. 값 설명은 다음과 같습니다.

LANGUAGE SQL: routine_body 부분은 데이터베이스 시스템의 기본 언어이기도 한 SQL 언어문으로 구성되어 있음을 설명합니다.

[NOT] DETERMINISTIC: 저장 프로시저의 실행 결과가 결정적인지 여부를 나타냅니다. DETERMINISTIC은 결과가 결정적임을 의미합니다. 저장 프로시저가 실행될 때마다 동일한 입력으로 동일한 출력이 발생합니다. NOT DETERMINISTIC은 결과가 비결정적이며 동일한 입력이 다른 출력을 생성할 수 있음을 의미합니다. 기본적으로 결과는 비결정적입니다.

{ CONTAINS SQL | NO SQL READS SQL DATA | MODIFIES SQL DATA }: 서브 프로그램의 SQL 문 사용에 대한 제한 사항을 나타냅니다. CONTAINS SQL은 하위 프로그램에 SQL 문이 포함되어 있지만 데이터를 읽거나 쓰는 명령문은 포함되어 있지 않음을 의미하고, NO SQL은 하위 프로그램에 SQL 문이 포함되어 있지 않음을 의미하며, READS SQL DATA는 하위 프로그램에 데이터 읽기용 명령문이 포함되어 있음을 의미합니다. 서브프로그램에는 데이터를 읽는 명령문이 포함되어 있습니다. 기본적으로 시스템은 CONTAINS SQL을 지정합니다.

SQL SECURITY { DEFINER | }: 실행 권한이 있는 사용자를 지정합니다. DEFINER는 정의자만이 이를 실행할 수 있다는 것을 의미하고, INVOKER는 호출자가 이를 실행할 수 있다는 것을 의미합니다. 기본적으로 시스템이 지정한 권한은 DEFINER입니다.

COMMENT '문자열': 댓글 정보입니다.

팁: 저장 프로시저를 생성할 때 시스템은 기본적으로 CONTAINS SQL을 지정하여 SQL 문이 저장 프로시저에서 사용됨을 나타냅니다. 그러나 저장 프로시저에 SQL 문이 사용되지 않으면 NO SQL로 설정하는 것이 가장 좋습니다. 또한, 나중에 저장 프로시저의 코드를 읽기 쉽도록 COMMENT 섹션에 저장 프로시저에 대한 간단한 설명을 작성하는 것이 가장 좋습니다.

[예제 14-1] 아래와 같이 num_from_employee라는 저장 프로시저를 생성합니다. 코드는 다음과 같습니다.

CREATE PROCEDURE num_from_employee (IN emp_id INT, OUT count_num INT ) 
     READS SQL DATA 
     BEGIN 
       SELECT COUNT(*) INTO count_num 
       FROM employee 
       WHERE d_id=emp_id ; 
     END

로그인 후 복사

위 코드에서 저장 프로시저 이름은 num_from_employee이고, 입력 변수는 emp_id입니다. SELECT 문은 Employee 테이블에서 d_id 값이 emp_id와 같은 레코드를 쿼리하고, COUNT(*)를 이용해 d_id 값이 동일한 레코드의 개수를 계산하고, 최종적으로 계산 결과를 count_num에 저장한다. 코드의 실행 결과는 다음과 같습니다.

mysql> DELIMITER && 
mysql> CREATE PROCEDURE num_from_employee
(IN emp_id INT, OUT count_num INT ) 
  -> READS SQL DATA 
  -> BEGIN 
  -> SELECT COUNT(*) INTO count_num 
  -> FROM employee 
  -> WHERE d_id=emp_id ; 
  -> END && 
Query OK, 0 rows affected (0.09 sec) 
mysql> DELIMITER ;

로그인 후 복사

코드 실행 후 오류 메시지가 보고되지 않으면 저장 기능이 성공적으로 생성되었음을 의미합니다. 이 저장 프로시저는 나중에 호출할 수 있으며 저장 프로시저의 SQL 문은 데이터베이스에서 실행됩니다.

참고: MySQL의 기본 문 종결자는 세미콜론(;)입니다. 저장 프로시저의 SQL 문을 끝내려면 세미콜론이 필요합니다. 충돌을 피하려면 먼저 "DELIMITER &&"를 사용하여 MySQL 종결자를 &&로 설정하십시오. 마지막으로 "DELIMITER;"를 사용하여 종결자를 세미콜론으로 복원합니다. 이는 트리거를 생성할 때와 동일합니다.

14.1.2 저장 함수 생성

MySQL에서 저장 함수를 생성하는 기본 형태는 다음과 같습니다.

CREATE FUNCTION sp_name ([func_parameter[,...]]) 
    RETURNS type 
    [characteristic ...] routine_body

로그인 후 복사

그 중 sp_name 매개변수는 저장 함수의 이름이고, func_parameter는 저장 함수의 매개변수 목록을 나타내며, RETURNS 유형은 저장 함수의 특성을 지정합니다. 이 매개변수는 저장 프로시저의 값과 동일합니다. 마찬가지로 14.1.1절의 내용을 참조하세요. routine_body 매개변수는 SQL 코드의 내용이며, BEGIN...END를 사용하여 시작을 표시할 수 있습니다. SQL 코드의 끝입니다.

func_parameter는 여러 개의 매개변수로 구성될 수 있으며, 각 매개변수는 매개변수 이름과 매개변수 유형으로 구성되며, 그 형식은 다음과 같습니다.

param_name 유형
그중 param_name 매개변수는 저장 함수의 매개변수 이름이고, type 매개변수는 MySQL 데이터베이스의 모든 데이터 유형이 될 수 있는 저장 함수의 매개변수 유형을 지정합니다.

[예제 14-2] 다음으로 name_from_employee라는 저장 함수를 생성합니다. 코드는 다음과 같습니다.

CREATE FUNCTION name_from_employee (emp_id INT ) 
     RETURNS VARCHAR(20) 
     BEGIN 
       RETURN (SELECT name 
       FROM employee 
       WHERE num=emp_id ); 
     END

로그인 후 복사

위 코드에서 저장 함수의 이름은 name_from_employee입니다. 이 함수의 매개변수는 emp_id입니다. 반환 값은 VARCHAR 유형입니다. SELECT 문은 Employee 테이블에서 num 값이 emp_id와 동일한 레코드를 쿼리하고 해당 레코드의 name 필드 값을 반환합니다. 코드의 실행 결과는 다음과 같습니다.

mysql> DELIMITER && 
mysql> CREATE FUNCTION name_from_employee (emp_id INT ) 
  -> RETURNS VARCHAR(20) 
  -> BEGIN 
  -> RETURN (SELECT name 
  -> FROM employee 
  -> WHERE num=emp_id ); 
  -> END&& 
Query OK, 0 rows affected (0.00 sec) 
mysql> DELIMITER ;

로그인 후 복사

결과는 저장 기능이 성공적으로 생성되었음을 보여줍니다. 이 기능의 사용은 MySQL 내부 기능의 사용과 동일합니다.

14.1.3 변수 사용

在存储过程和函数中，可以定义和使用变量。用户可以使用DECLARE关键字来定义变量。然后可以为变量赋值。这些变量的作用范围是BEGIN…END程序段中。本小节将讲解如何定义变量和为变量赋值。

1．定义变量

MySQL中可以使用DECLARE关键字来定义变量。定义变量的基本语法如下：

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

其中， DECLARE关键字是用来声明变量的；var_name参数是变量的名称，这里可以同时定义多个变量；type参数用来指定变量的类型；DEFAULT value子句将变量默认值设置为value，没有使用DEFAULT子句时，默认值为NULL。

【示例14-3】下面定义变量my_sql，数据类型为INT型，默认值为10。代码如下：

DECLARE my_sql INT DEFAULT 10 ;

2．为变量赋值

MySQL中可以使用SET关键字来为变量赋值。SET语句的基本语法如下：

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

其中，SET关键字是用来为变量赋值的；var_name参数是变量的名称；expr参数是赋值表达式。一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。

【示例14-4】下面为变量my_sql赋值为30。代码如下：

SET my_sql = 30 ;

MySQL中还可以使用SELECT…INTO语句为变量赋值。其基本语法如下：

SELECT col_name[,…] INTO var_name[,…]
FROM table_name WEHRE condition

其中，col_name参数表示查询的字段名称；var_name参数是变量的名称；table_name参数指表的名称；condition参数指查询条件。

【示例14-5】下面从employee表中查询id为2的记录，将该记录的d_id值赋给变量my_sql。代码如下：

SELECT d_id INTO my_sql
FROM employee WEHRE id=2 ;

14.1.4 定义条件和处理程序

定义条件和处理程序是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法。这样可以增强程序处理问题的能力，避免程序异常停止。MySQL中都是通过DECLARE关键字来定义条件和处理程序。本小节中将详细讲解如何定义条件和处理程序。

1．定义条件

MySQL中可以使用DECLARE关键字来定义条件。其基本语法如下：

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。

【示例14-6】下面定义"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表示一些存储过程或函数的执行语句。

【示例14-7】下面是定义处理程序的几种方式。代码如下：

//方法一：捕获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';

로그인 후 복사

위 코드는 핸들러를 정의하는 6가지 방법입니다. 첫 번째 방법은 sqlstate_value 값을 캡처하는 것입니다. sqlstate_value 값이 42S02이면 CONTINUE 연산을 수행하고 "CAN NOT 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" 정보를 출력한다.

이 기사를 읽어주셔서 감사합니다. 모든 사람에게 도움이 되기를 바랍니다. 이 사이트를 지원해 주셔서 감사합니다!