>데이터 베이스 >MySQL 튜토리얼 >mysql 저장 프로시저에서 동적 SQL 문 사용

mysql 저장 프로시저에서 동적 SQL 문 사용

步履不停
步履不停원래의
2019-06-24 18:06:023335검색

程 간단한 저장 절차 각 키워드 사용법:

mysql 저장 프로시저에서 동적 SQL 문 사용

CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc`(in _xnb varchar(50))
BEGIN
	## 定义变量
	DECLARE _num FLOAT(14,6) DEFAULT 0;
	## @表示全局变量 相当于php $
	## 拼接赋值 INTO 必须要用全局变量不然语句会报错
    ## //CONCAT会把'SELECT SUM('和_xnb和') INTO @tnum FROM btc_user_coin'拼接起来,CONCAT的各个参数中间以","号分割
	SET @strsql = CONCAT('SELECT SUM(',_xnb,') INTO @tnum FROM btc_user_coin');
	## 预处理需要执行的动态SQL,其中stmt是一个变量
	PREPARE stmt FROM @strsql;  
	## 执行SQL语句
	EXECUTE stmt;  
	## 释放掉预处理段
	deallocate prepare stmt;
	## 赋值给定义的变量
	SET _num = @tnum;
	SELECT _num
END;;
e

Mysql 저장 절차는 동적 SQL 문을 사용합니다.

Mysql 5.0은 동적 SQL 문을 지원합니다. 우리가 원하는 값

저장 프로시저에는 두 가지 유형의 동적 SQL이 있습니다

1.

set sql = (concat으로 이어붙일 수 있는 전처리된 sql 문)  set @sql = sql

준비

stmt

_name FROM @sql; EXECUTE

stmt

_name;

 {DEALLOCATE | DROP} PREPARE stmt_name;

过程过程示例:

CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`(IN `USER_ID` varchar(36),IN `USER_NAME` varchar(36))BEGIN
    
      declare SQL_FOR_SELECT varchar(500); -- 定义预处理sql语句

      set SQL_FOR_SELECT = CONCAT("select * from  user  where user_id = '",USER_ID,"' and user_name = '",USER_NAME,"'");   -- 拼接查询sql语句

      set @sql = SQL_FOR_SELECT;      PREPARE stmt FROM @sql;       -- 预处理动态sql语句
      EXECUTE stmt ;                -- 执行sql语句
      deallocate prepare stmt;      -- 释放prepareEND;

上述是一个简单的查询用户表的存储过程,当我们调用此存储过程,可以根据传入不同的参数获得不同的值。

但是:上述存储过程中,我们必须在拼接sql语句之前把USER_ID,USER_NAME定义好,而且在拼接sql语句之后,我们无法改变USER_ID,USER_NAME的值,如下:

CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`(IN `USER_ID` varchar(36),IN `USER_NAME` varchar(36))BEGIN
    
       declare SQL_FOR_SELECT varchar(500);  -- 定义预处理sql语句

       set SQL_FOR_SELECT = CONCAT("select * from user where user_id = '",USER_ID,"' and user_name = '",USER_NAME,"'");   -- 拼接查询sql语句

       set @sql = SQL_FOR_SELECT;       PREPARE stmt FROM @sql;        -- 预处理动态sql语句
       EXECUTE stmt ;                 -- 执行sql语句
       deallocate prepare stmt;       -- 释放prepare


       set USER_ID = '2'; -- 主动指定参数USER_ID的值
       set USER_NAME = 'lisi';       set @sql = SQL_FOR_SELECT;       PREPARE stmt FROM @sql;       -- 预处理动态sql语句
       EXECUTE stmt ;                -- 执行sql语句
       deallocate prepare stmt;      -- 释放prepareEND;

 我们用call aa('1','zhangsan');来调用该存储过程,第一次动态执行,我们得到了‘张三’的信息,然后我们在第14,15行将USER_ID,USER_NAME改为lisi,我们希望得到李四的相关信息,可查出来的结果依旧是张三的信息,说明我们在拼接sql语句后,不能再改变参数了。

为了解决这种问题,下面介绍第二中方式:

2.set sql = (预处理的sql语句,可以是用concat拼接的语句,参数用 ?代替)

 set @sql = sql

 PREPARE stmt_name FROM @sql;

 set @var_name = xxx;var_name = xxx;

 EXECUTE stmt_name USING [USING @var_name [, @var_name

 

EXECUTE stmt_name 사용 [@var_name [, @var_name] ...]

;

 {DEALLOCATE | 삭제} 준비하세요 🎜stmt🎜🎜_name🎜;🎜🎜上述的代码我们就可以改成 :🎜- - 사전 처리 SQL 문 정의 t set SQL_FOR_SELECT = "select*from user where user_id =? And user_name =?" From @sql;-전처리 동적 SQL 문 t set@Parm1 = User_id;-SQL 동적 매개변수 전송

set@Parm2 = User_name; - 릴리스 준비 - set @sql = SQL_FOR_SELECT; PREPARE stmt FROM @sql; -- 동적 SQL 문 전처리

set @parm1 = '2' -- SQL 동적 매개변수 전달
          set @ parm2 =  'lisi';

                                      ' s ' s ' s ' t ‐ ‐ ‐                                  @parm1 , @parm2;                                                                                                       ~         @parm 매개변수) 동적 SQL을 사용합니다.

몇 가지 참고 사항:

  •  存储动态SQL的值的变量不能是自定义变量,必须是用户变量或者全局变量   如:set sql = 'xxx';  prepare stmt from sql;是错的,正确为: set @sql = 'xxx';  prepare stmt from @sql;

  •    即使 preparable_stmt 语句中的 ? 所代表的是一个字符串,你也不需要将 ? 用引号包含起来。

  •   如果动态语句中用到了 in ,正常写法应该这样:select * from table_name t where t.field1 in (1,2,3,4,...);

  •   则sql语句应该这样写:set @sql = "select * from user where user_id in (?,?,?) "   

因为有可能我不确定in语句里有几个参数,所以我试过这么写 

set @sql = "select * from user where user_id in (?) "  

然后参数我传的是  "'1','2','3'"  我以为程序会将我的动态sql解析出来(select * from user where user_id in ('1','2','3')) 但是并没有解析出来,在写存储过程in里面的列表用个传入参数代入的时候,就需要用到如下方式:

1.使用find_in_set函数

select * from table_name t where find_in_set(t.field1,'1,2,3,4');

2.还可以比较笨实的方法,就是组装字符串,然后执行

DROP PROCEDURE IF EXISTS photography.Proc_Test;
CREATE PROCEDURE photography.`Proc_Test`(param1 varchar(1000))
BEGIN
set @id = param1;
set @sel = 'select * from access_record t where t.ID in (';
set @sel_2 = ')';
set @sentence = concat(@sel,@id,@sel_2); -- 连接字符串生成要执行的SQL语句
prepare stmt from @sentence; -- 预编释一下。 “stmt”预编释变量的名称,
execute stmt; -- 执行SQL语句
deallocate prepare stmt; -- 释放资源
END;

위 내용은 mysql 저장 프로시저에서 동적 SQL 문 사용의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

성명:
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.