零基础学SQL（十二、存储过程）

最新推荐文章于 2024-08-02 10:43:54 发布

心寒丶

最新推荐文章于 2024-08-02 10:43:54 发布

阅读量2.4k

点赞数

分类专栏： # mysql 文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/jungeCSND/article/details/128420790

版权

mysql 专栏收录该内容

18 篇文章 3 订阅

订阅专栏

文章介绍了存储过程的基本概念，阐述了使用存储过程的原因，如简化操作、保证数据完整性和提高性能，并详细说明了如何在MySQL中创建、调用和检查存储过程。同时，文章还讨论了游标的用途和使用步骤，解释了游标在处理多行数据时的重要性。

摘要由CSDN通过智能技术生成

前言

原本是不打算写这一篇存储过程的文章的，因为目前大数据时代崛起，很多技术都可以替代存储过程，可能使用起来更简洁，效率更高，但是还是有不少朋友会问到存储过程的问题，因此作为一个简单介绍，希望有助于大家理解吧。

一、什么是存储过程

迄今为止，使用的大多数SQL 语句都是针对一个或多个表的单条语句。并非所有操作都这么简单，经常会有一个完整的操作需要多条语句才能完成。因此这里就需要用到存储过程。

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

二、为什么使用存储过程

既然我们知道了什么是存储过程，那么为什么要使用它们呢？有许多理由，下面列出一些主要的理由。

1、通过把处理封装在容易使用的单元中，简化复杂的操作。

2、由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。

3、简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。

4、提高性能。因为使用存储过程比使用单独的 SQL 语句要快。

5、存在一些只能用在单个请求中的 MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

换句话说，使用存储过程有3个主要的好处，即简单、安全、高性能。

显然，它们都很重要。不过，在将 SQL 代码转换为存储过程前，也必须知道它的一些缺陷。

1、一般来说，存储过程的编写比基本 SQL 语句复杂，编写存储过程需要更高的技能，更丰富的经验。

2、你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。

三、创建存储过程

1、创建存储过程语法

CREATE   PROCEDURE name(parm1,pram2)
BEGIN
   xxxxxxxxxxxxxx
END

传参格式：[in|out|inout] 参数 datatype

MySQL 支持 IN （传递给存储过程）、 OUT （从存储过程传出，如这里所用）和INOUT （对存储过程传入和传出）类型的参数，如果不显式指定“in”、“out”、“inout”，则默认为“in”。习惯上，对于是“in” 的参数，我们都不会显式指定。

2、创建无参存储过程

如使用无参存储过程计算3+4结果

CREATE DEFINER=`root`@`localhost` PROCEDURE `add1`()
BEGIN
	SELECT 3+4  from dual;
 
END

调用无参存过

CALL add1();

结果为7

3、创建有参存过

如使用存过计算两数之和，传入数组a,b返回结果sum

CREATE DEFINER=`root`@`localhost` PROCEDURE `add3`(in a int,in b int,out sum int)
begin
 set sum=a+b;
end

call add3(3,2,@sum);
select @sum from dual;

4、检查存过

为显示用来创建一个存储过程的 CREATE 语句，使用SHOW CREATE PROCEDURE语句。

SHOW CREATE PROCEDURE add3

为了获得包括何时、由谁创建等详细信息的存储过程列表，使用 SHOW PROCEDURE STATUS。

可使用 LIKE 指定一个过滤模式，如 SHOW PROCEDURE STATUS like 'add%'

SHOW PROCEDURE STATUS

四、游标介绍

1、什么是游标

有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标（cursor ）是一个存储在 MySQL 服务器上的数据库查询，它不是一条SELECT 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

2、使用游标

使用游标涉及几个明确的步骤。

1、在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT 语句。

2、一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。

3、对于填有数据的游标，根据需要取出（检索）各行。

4、在结束游标使用时，必须关闭游标。

在声明游标后，可根据需要频繁地打开和关闭游标。在游标打开后，可根据需要频繁地执行取操作。

游标用 OPEN CURSOR语句来打开,如 open C_RULESQLS ;

游标处理完成后，应当使用 CLOSE CURSOR关闭游标，如 CLOSE C_RULESQLS;

使用游标数据 ,在一个游标被打开后，可以使用FETCH语句分别访问它的每一行，如

fetch C_RULESQLS into v_sql,v_type,rulenos;

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
 
BEGIN

	DECLARE do1 INTEGER DEFAULT 1; #记录下一个游标位置是否存在
 	DECLARE v_sql varchar(4000);  -- 申明sql变量
	DECLARE v_sql_stmt varchar(4000); 
	DECLARE v_templateNo varchar(100);
	DECLARE v_sqlcounts varchar(4000);
	DECLARE msg TEXT;
	DECLARE code CHAR(5) DEFAULT '00000';
	DECLARE v_sysdate date;
	DECLARE o_taskCount int;
	DECLARE rulenos varchar(255); 
 
 --申明游标
	DECLARE C_RULESQLS CURSOR FOR SELECT a.rulesql,a.sentMessageType,a.ruleno  from rule a  WHERE
	a.validStatus='1' and sysdate() BETWEEN a.validstartdate and a.validenddate   and a.ruletype='26' ;
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET do1=0;
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 
		BEGIN 
			-- 获取异常code,异常信息 写入对应表
					GET DIAGNOSTICS CONDITION 1
						code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
			insert into  error (uuid,runType,runTypeName,ruleNo,runDate,startTime,endTime,errlog)
							 values (UUID(),'1','test',rulenos,v_sysdate,v_sysdate,v_sysdate,msg);
				insert into sucess (uuid,runType,runTypeName,ruleNo,runDate,startTime,endTime,resultFlag,resultName)
							 values (UUID(),'1','test',rulenos,v_sysdate,v_sysdate,v_sysdate,'0','失败');
		END;
		

	open C_RULESQLS ; -- 打开游标
	 out_loop:LOOP --循环游标
		fetch  C_RULESQLS into v_sql,v_type,rulenos; -- 将游标查询的值放入变量
		     IF do1=0 THEN 
                 LEAVE out_loop;
         END IF;
				  select NOW() into v_sysdate from dual;
					set v_sqlcounts = concat('select  count(1) into @count from test where sysdate() BETWEEN startDate and endDate ',v_sql);
					set @sqlcounts = v_sqlcounts;
					prepare stmt from @sqlcounts;
					execute stmt;
					deallocate prepare stmt;
					#获取动态SQL语句返回值
					set o_taskCount=@count ;

				 IF v_type=1 THEN
					SET v_templateNo='test1'; 
				 ELSEIF v_type=2 THEN
					SET v_templateNo='test2'; 
				 END IF;
		
					set v_sql_stmt=concat('insert into mstest (id ,usercode,username,no, Name,toUser,externalUserId,licenseNo,vinceno,carBrand,mobile,
	usermobile,birthday,enddate,firstTime,makeusercode,misissionflag,receiveRole,receiveName,makeComCode,twoComcode,ThreeComcode,
	useNature,policyType,riskCode,channeltype,Channeltype1,sentflag,createdate,templateNo,sentType,messageType,automaticType)
	SELECT nextval(''messageid''),b.usercode,(SELECT u.username from  account u where u.usercode=b.usercode),b.policyno,b.appliname,
	IFNULL(IFNULL(IFNULL(IFNULL(b.mobile,b.rmobile4),b.rmobile),b.rmobile2),b.rmobile3),
			(SELECT w.externalUserId from  client w where w.certName=b.appliname and w.remarkMobiles=b.rmobile),
	b.licenseno,b.vinceno,b.carbrand,b.mobile,(SELECT u.phonenumber from  account u where u.usercode=b.usercode), b.birthday,
	b.enddate,b.firsttime,b.makecomcode,b.misissionflag,''1'',b.appliname,b.makeComCode,
	getComcodeByLevel(b.makecomcode,''2''),
	getComcodeByLevel(b.makecomcode,''3''),b.usenature,b.policytype,b.riskcode,
	b.transname2,b.transname1,''0'',sysdate(),''',v_templateNo,''',',v_type,', ''0'',''1'' 
	from testmain b where  sysdate() BETWEEN b.startDate and b.endDate ',v_sql); 
				
				SET @sql=v_sql_stmt;
				PREPARE stmt FROM @sql;  -- 预处理需要执行的动态SQL，其中stmt是一个变量
				EXECUTE stmt; -- 执行SQL语句
				DEALLOCATE PREPARE stmt;  --  释放掉预处理段
				
				insert into sucess (uuid,runType,runTypeName,ruleNo,runDate,startTime,endTime,resultFlag,resultName,runcount)
           values (UUID(),'1','test',rulenos,NOW(),v_sysdate,NOW(),'1','成功',o_taskCount);
    end loop out_loop;
			 
CLOSE C_RULESQLS;



END