MySQL 저장 프로시저를 호출하는 방법

개요

MySQL 버전 5.0부터 저장 프로시저가 지원됩니다.

특정 사용자 요구 사항을 충족하기 위해 복잡한 SQL 문 집합을 작성해야 하는 경우 데이터베이스에 이 복잡한 SQL 문 집합을 미리 작성하고 JDBC를 호출하여 이 SQL 문 집합을 실행할 수 있습니다. 데이터베이스에 기록된 SQL 문 집합을 저장 프로시저라고 합니다.

저장 프로시저: (PROCEDURE)는 미리 컴파일되어 데이터베이스에 저장된 SQL 문 모음입니다. 저장 프로시저를 호출하면 애플리케이션 개발자의 많은 작업이 단순화되고, 데이터베이스와 애플리케이션 서버 간의 데이터 전송이 줄어들며, 데이터 처리 효율성을 향상시키는 데 매우 유용합니다.

데이터베이스의 SQL 언어 수준에서 코드 캡슐화 및 재사용입니다.

Java의 메서드와 유사하게 저장 프로시저를 먼저 정의하고 사용할 때 호출해야 합니다. 매개변수는 저장 프로시저에서 정의할 수 있습니다. 이러한 매개변수는 IN, OUT 및 INOUT의 세 가지 유형으로 구분됩니다.

• IN 유형 매개변수는 호출자가 전달한 데이터를 받아들입니다.

• OUT 유형 매개변수는 호출자에게 반환되는 데이터를 나타냅니다.

• INOUT 유형 매개변수는 호출자가 전달한 매개변수를 받아들일 수도 있습니다. 발신자에게 돌아왔습니다.

장점

1. 저장 프로시저는 처리를 통해 사용하기 쉬운 단위로 패키지되어 복잡한 작업을 단순화합니다.

2. 변경 사항 관리를 단순화하세요. 테이블 이름, 열 이름, 비즈니스 로직이 변경되는 경우 저장 프로시저의 코드만 변경하면 됩니다. 이를 사용하는 사람들은 코드를 변경할 필요가 없습니다.

3. 일반적으로 저장 프로시저는 애플리케이션 성능을 향상시키는 데 도움이 됩니다. 생성된 저장 프로시저가 컴파일되면 데이터베이스에 저장됩니다.
   그러나 MySQL은 저장 프로시저를 약간 다르게 구현합니다.
   MySQL 저장 프로시저는 요청 시 컴파일됩니다. 저장 프로시저를 컴파일한 후 MySQL은 이를 캐시에 저장합니다.
   MySQL은 각 연결에 대해 자체 저장 프로시저 캐시를 유지합니다. 애플리케이션이 단일 연결에서 저장 프로시저를 여러 번 사용하는 경우 컴파일된 버전을 사용하고, 그렇지 않으면 저장 프로시저가 쿼리처럼 작동합니다.

4. 저장 프로시저는 애플리케이션과 데이터베이스 서버 간의 트래픽을 줄이는 데 도움이 됩니다.
   애플리케이션은 긴 SQL 문을 여러 개 보낼 필요가 없으므로 저장 프로시저의 이름과 매개 변수만 보내면 됩니다.

5. 저장 프로시저는 재사용이 가능하며 모든 애플리케이션에 투명합니다. 개발자가 이미 지원되는 기능을 복제하지 않도록 저장 프로시저는 데이터베이스 인터페이스를 모든 애플리케이션에 공개합니다.

6. 저장된 프로그램은 안전합니다. 데이터베이스 관리자는 기본 데이터베이스 테이블에 대한 권한을 부여하지 않고도 데이터베이스의 저장 프로시저에 액세스하는 응용 프로그램에 적절한 권한을 부여할 수 있습니다.

단점

1. 많은 수의 저장 프로시저를 사용하는 경우 이러한 저장 프로시저를 사용하는 각 연결의 메모리 사용량이 크게 늘어납니다.
   또한, 저장 프로시저에서 다수의 논리 연산이 과도하게 사용되면 CPU 사용량도 증가하게 됩니다. 왜냐하면 MySQL 데이터베이스의 원래 설계는 논리 연산보다는 효율적인 쿼리에 중점을 두었기 때문입니다.

2. 저장 프로시저의 구조로 인해 복잡한 비즈니스 로직이 포함된 저장 프로시저를 개발하기가 어렵습니다.

3. 저장 프로시저를 디버그하는 것은 어렵습니다. 소수의 데이터베이스 관리 시스템만이 저장 프로시저 디버깅을 허용합니다. 불행하게도 MySQL은 저장 프로시저를 디버깅하는 기능을 제공하지 않습니다.

4. 저장 프로시저를 개발하고 유지 관리하는 것은 쉽지 않습니다.
   저장 프로시저를 개발하고 유지 관리하려면 모든 애플리케이션 개발자가 보유하지 않은 전문 기술이 필요한 경우가 많습니다. 이로 인해 애플리케이션 개발 및 유지 관리 단계에서 문제가 발생할 수 있습니다.

5. 데이터베이스 의존도가 높고 전송성이 좋지 않습니다.

MySQL 저장 프로시저의 정의

저장 프로시저의 기본 문 형식

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE 数据库名.存储过程名([in变量名 类型,out 参数 2，...])
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
	BEGIN
		[DECLARE 变量名 类型 [DEFAULT 值];]
		存储过程的语句块;
	END$$

DELIMITER ;

● 저장 프로시저의 매개변수는 in, out, inout입니다.

1. in은 입력 매개변수를 나타냅니다. in 매개변수) 이는 이 매개변수의 값이 호출 프로그램에 의해 지정되어야 함을 나타냅니다.

2. ou는 출력 매개변수를 나타냅니다. 즉, 매개변수 값이 저장 프로시저에 의해 계산된 후 out 매개변수의 계산 결과가 호출 프로그램에 반환됩니다.

3. inout은 즉시 입력 매개변수와 출력 매개변수를 나타내며, 이는 매개변수의 값이 호출 프로그램에 의해 공식화될 수 있고, inout 매개변수의 계산 결과가 호출 프로그램에 반환될 수 있음을 의미합니다.

● 저장 프로시저의 문은 BEGIN과 END 사이에 포함되어야 합니다.

● DECLARE는 변수 선언에 사용됩니다. 기본 변수 할당은 DEFAULT를 사용합니다. 명령문 블록에서 변수 값을 변경하려면 SET 변수 = 값을 사용하세요.

저장 프로시저 정의

DELIMITER $$

CREATE
    PROCEDURE `demo`.`demo1`()
	-- 存储过程体
	BEGIN
		-- DECLARE声明 用来声明变量的
		DECLARE de_name VARCHAR(10) DEFAULT '';
		
		SET de_name = "jim";
		
		-- 测试输出语句（不同的数据库，测试语句都不太一样。
		SELECT de_name;
	END$$

DELIMITER ;

저장 프로시저 호출

CALL demo1();

매개변수로 저장 프로시저 정의

먼저 학생 데이터베이스 테이블을 정의합니다.

现在要查询这个student表中的sex为男的有多少个人。

DELIMITER $$

CREATE
    PROCEDURE `demo`.`demo2`(IN s_sex CHAR(1),OUT s_count INT)
	-- 存储过程体
	BEGIN
		-- 把SQL中查询的结果通过INTO赋给变量
		SELECT COUNT(*) INTO s_count FROM student WHERE sex= s_sex;
		SELECT s_count;
		
	END$$
DELIMITER ;

调用这个存储过程

-- @s_count表示测试出输出的参数
CALL demo2 ('男',@s_count);

定义一个流程控制语句 IF ELSE

IF 语句包含多个条件判断，根据结果为 TRUE、FALSE执行语句，与编程语言中的 if、else if、else 语法类似。

DELIMITER $$
CREATE
    PROCEDURE `demo`.`demo3`(IN `day` INT)
	-- 存储过程体
	BEGIN
		IF `day` = 0 THEN
		SELECT '星期天';
		ELSEIF `day` = 1 THEN
		SELECT '星期一';
		ELSEIF `day` = 2 THEN
		SELECT '星期二';
		ELSE
		SELECT '无效日期';
		END IF;
		
	END$$
DELIMITER ;

调用这个存储过程

CALL demo3(2);

定义一个条件控制语句 CASE

case是一种类似于编程语言中的choose和when语法的条件判断语句。MySQL 中的 case语句有两种语法格式。

第一种

DELIMITER $$
CREATE 
    PROCEDURE demo4(IN num INT)
	BEGIN
		CASE -- 条件开始
	
		WHEN num<0 THEN 
			SELECT &#39;负数&#39;;
		WHEN num>0 THEN 
			SELECT &#39;正数&#39;;
		ELSE 
		SELECT &#39;不是正数也不是负数&#39;;
	
		END CASE; -- 条件结束
	END$$
DELIMITER;

调用这个存储过程

CALL demo4(1);

2.第二种

DELIMITER $$
CREATE 
    PROCEDURE demo5(IN num INT)
	BEGIN
		CASE num  -- 条件开始
		WHEN 1 THEN 
			SELECT '输入为1';
		WHEN 0 THEN 
			SELECT '输入为0';
		ELSE 
		SELECT '不是1也不是0';
		END CASE; -- 条件结束
	END$$
DELIMITER;

调用此函数

CALL demo5(0);

定义一个循环语句 WHILE

DELIMITER $$
CREATE 
    PROCEDURE demo6(IN num INT,OUT SUM INT)
	BEGIN
	     SET SUM = 0;
	     WHILE num<10 DO -- 循环开始
	         SET num = num+1;
	         SET SUM = SUM+num;
	         END WHILE; -- 循环结束
	END$$
DELIMITER;

调用此函数

-- 调用函数
CALL demo6(0,@sum);

-- 查询函数
SELECT @sum;

定义一个循环语句 REPEAT UNTLL

REPEATE…UNTLL 语句的用法和 Java中的 do…while 语句类似，都是先执行循环操作，再判断条件，区别是REPEATE 表达式值为 false时才执行循环操作，直到表达式值为 true停止。

-- 创建过程
DELIMITER $$
CREATE 
    PROCEDURE demo7(IN num INT,OUT SUM INT)
	BEGIN
	     SET SUM = 0;
	     REPEAT-- 循环开始
		SET num = num+1;
		SET SUM = SUM+num ;
		UNTIL num>=10
		END REPEAT; -- 循环结束
	END$$
DELIMITER;

调用此函数

CALL demo7(9,@sum);

SELECT @sum;

定义一个循环语句 LOOP

循环语句，用来重复执行某些语句。

执行过程中可使用 LEAVE语句或者ITEREATE来跳出循环，也可以嵌套IF等判断语句。

LEAVE 语句效果对于Java中的break，用来终止循环；

ITERATE语句效果相当于Java中的continue，用来跳过此次循环。进入下一次循环。且ITERATE之下的语句将不在进行。

DELIMITER $$
CREATE 
    PROCEDURE demo8(IN num INT,OUT SUM INT)
	BEGIN
	     SET SUM = 0;
	     demo_sum:LOOP-- 循环开始
		SET num = num+1;
		IF num > 10 THEN
		    LEAVE demo_sum; -- 结束此次循环
		ELSEIF num < 9 THEN
		    ITERATE demo_sum; -- 跳过此次循环
		END IF;
		
		SET SUM = SUM+num;
		END LOOP demo_sum; -- 循环结束
	END$$
DELIMITER;

调用此函数

CALL demo8(0,@sum);

SELECT @sum;

使用存储过程插入信息

DELIMITER $$
CREATE 
    PROCEDURE demo9(IN s_student VARCHAR(10),IN s_sex CHAR(1),OUT s_result VARCHAR(20))
	BEGIN
	   -- 声明一个变量 用来决定这个名字是否已经存在
	   DECLARE s_count INT DEFAULT 0;
	   -- 验证这么名字是否已经存在
	   SELECT COUNT(*) INTO s_count FROM student WHERE `name` = s_student;	
	   IF s_count = 0 THEN
	        INSERT INTO student (`name`, sex) VALUES(s_student, s_sex);
		SET s_result = '数据添加成功';
	   ELSE
                SET s_result = '名字已存在，不能添加';
                SELECT s_result;
	   END IF;
	END$$
DELIMITER;

调用此函数

CALL demo9("Jim","女",@s_result);

再次调用次函数

CALL demo9("Jim","女",@s_result)

存储过程的管理

显示存储过程

SHOW PROCEDURE STATUS

显示特定数据库的存储过程

SHOW PROCEDURE STATUS WHERE db = 'db名字' AND NAME = 'name名字';

显示特定模式的存储过程

SHOW PROCEDURE STATUS WHERE NAME LIKE '%mo%';

显示存储过程的源码

SHOW CREATE PROCEDURE 存储过程名;

删除存储过程

DROP PROCEDURE 存储过程名;

后端调用存储过程的实现

在mybatis当中，调用存储过程

<parameterMap type="savemap" id=“usermap"> 
	<parameter property="name" jdbcType="VARCHAR" mode="IN"/>
	<parameter property="sex" jdbcType="CHAR" mode="IN"/>
	<parameter property="result" jdbcType="VARCHAR" mode="OUT"/>
</parameterMap>

<insert id="saveUserDemo" parameterMap="savemap" statementType="CALLABLE"> 
{call saveuser(?, ?, ?)} 
</insert >

调用数据库管理

HashMap<String, Object> map = new HashMap<String, Object>(); 
	map.put("name", "Jim"); 
	map.put("sex","男");
	userDao.saveUserDemo(map); 
	map.get(“result”);//获得输出参数

通过这样就可以调用数据库中的存储过程的结果。

위 내용은 MySQL 저장 프로시저를 호출하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!