mysql存储过程

用户5927264

发布于 2019-07-31 18:38:43

8.2K0

发布于 2019-07-31 18:38:43

文章被收录于专栏：OSChinaOSChina

CONCAT(person_no,"号犯人住", i , "号床位"); ---字符串拼接

存储过程的创建标准

DELIMITER $$  -- 定义语句结束符 为$$
DROP PROCEDURE IF EXISTS helloworld$$  --这里就相当与" ; "
CREATE PROCEDURE helloworld()
BEGIN 	
	SELECT "hello world";
END $$
DELIMITER ;  --还原语句结束符为;

创建存储过程的方法 IN OUT INOUT

---IN  参数的使用
DELIMITER//
CREATE PROCEDURE sp_demo_in_parameter(IN p_in INT)
BEGIN
SELECT 4;
SELECT p_in;
SET p_in=2;
SELECT p_in;
END;
//


SET @p_in=1;

CALL sp_demo_in_parameter(3)

DROP PROCEDURE sp_demo_in_parameter;

--OUT 参数的使用
DELIMITER //
CREATE PROCEDURE sp_demo_out_parameter(OUT p_out INT)
BEGIN
SELECT p_out;
SET p_out=2;
SELECT p_out;
END;
//

SET @p_out=1;
CALL sp_demo_out_parameter(@p_out);

--inout 参数的使用
DELIMITER //
CREATE PROCEDURE sp_demo_inout_parameter(INOUT p_inout INT)
BEGIN
SELECT p_inout;
SET p_inout=2;
SELECT p_inout;
END;
//

SET @p_inout=1;
CALL sp_demo_inout_parameter(@p_inout);

存储过程的定义和调用

DELIMITER $$
DROP PROCEDURE IF EXISTS helloworld $$
CREATE PROCEDURE helloworld(normal_price NUMERIC(8,2),OUT discount_price NUMERIC(8,2))
BEGIN
	IF (normal_price > 500) THEN
		SET discount_price=normal_price*0.8;
	ELSEIF (normal_price > 100) THEN
		SET discount_price = normal_price * 0.9;
	ELSE 
		SET discount_price = normal_price;
	END IF;		
 END $$

 CALL helloworld(600,@newprice);
 SELECT @newprice

while do end while 循环

DELIMITER $$ 
DROP PROCEDURE IF EXISTS helloworld$$
CREATE PROCEDURE helloworld(OUT counter INT)
BEGIN 
	SET counter=0;
	WHILE counter !=10 DO
		SET counter =counter +1;
	END WHILE;
END $$

CALL helloworld(@counter);
SELECT @counter;

repeat until 替换

DELIMITER $$
DROP PROCEDURE IF EXISTS simple_repeat$$
CREATE PROCEDURE simple_repeat(OUT counter INT)
BEGIN 
	SET counter = 0;
	REPEAT 
		SET counter = counter +1;
	UNTIL counter =10 
	END REPEAT;
END $$

CALL simple_repeat(@repeat_counter);
SELECT @repeat_counter;

游标的使用

查询表中最后的一行数据

CURSOR用于处理多行记录的查询结果

DECLARE LOOP_T CURSOR FOR　--游标的宣言

OPEN LOOP_T;   --打开游标

FETCH NEXT FROM LOOP_T INTO [@PART](https://my.oschina.net/u/857680); -- 将提取结果带入游标

CLOSE LOOP_T; -- 关闭游标

在MySQL的存储过程中经常会看到这句话：DECLARE countinue handler for NOT FOUND。它的含义是：若没有数据返回,程序继续,并将变量IS_FOUND设为0 ，这种情况是出现在select XX into XXX from tablename的时候发生的。

DELIMITER $$
DROP PROCEDURE IF EXISTS cursor_example$$
CREATE PROCEDURE cursor_example(OUT row_message INT)
	READS SQL DATA
        --整个过程中所有的SQL语句仅需要读记录，不对记录进行写的操作。 
BEGIN
	DECLARE 1_id INT;
	DECLARE 1_mapguid VARCHAR(64);
	DECLARE 1_fac_code VARCHAR(64);
	DECLARE done INT DEFAULT 0;

	DECLARE cur1 CURSOR FOR SELECT id,mapguid,fac_code FROM ht_t_2w_map;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
	
	OPEN cur1;
		map_loop:LOOP
			FETCH cur1 INTO 1_id,1_mapguid,1_fac_code;
			IF done =1 THEN			
				LEAVE map_loop;
			END IF;
		END LOOP map_loop;	
	CLOSE cur1;	
	SET row_message=1_id;
END $$

CALL cursor_example(@row_message);
SELECT @row_message;

update insert delete

DELIMITER $$
DROP PROCEDURE IF EXISTS sp_update_map$$
CREATE PROCEDURE sp_update_map(in_id INT,in_mapName VARCHAR(64))
BEGIN
	UPDATE ht_t_2w_map SET mapName=in_mapName 
		WHERE id=in_id;
END $$

CALL sp_update_map(173,"施爷");

DELIMITER $$
DROP PROCEDURE IF EXISTS sp_insert_map$$
CREATE PROCEDURE sp_insert_map(in_mapguid VARCHAR(64), in_fac_code VARCHAR(64), in_mapName VARCHAR(64))
BEGIN 
	INSERT INTO ht_t_2w_map (mapGuid,fac_code,mapName) VALUES (in_mapguid,in_fac_code,in_mapName);
END $$

CALL sp_insert_map(UPPER(REPLACE(UUID(),'-','')),"0001","张三");

SELECT UPPER(REPLACE(UUID(),'-',''));


 在mysql中，可以使用uuid 来生成主键，但是用mysql的uuid()函数 ，生成的uuid是36位的，
 其中包含32个字符以及4个分隔符（-），往往这个分隔符对我们来说是没有用的，
 可以使用mysql自带的replace函数去掉分隔符
REPLACE(UUID(),'-','')   ---->将uuid()中的‘-’，去掉，即替换成空串；
此外upper(REPLACE(UUID(),'-',''))用于将字符转换为大写

NO SQL：表示子程序不包含SQL语句。

READS SQL DATA：表示子程序包含读数据的语句，但不包含写数据的语句。

MODIFIES SQL DATA：表示子程序包含写数据的语句。

SQL SECURITY DEFINER：表示执行存储过程中的程序是由创建该存储过程的用户的权限来执行。

SQL SECURITY INVOKER：表示执行存储过程中的程序是由调用该存储过程的用户的权限来执行。

存储方法

存储方法与存储过程的区别

1，存储方法的参数列表只允许IN类型的参数，而且没必要也不允许指定IN关键字

2，存储方法返回一个单一的值，值的类型在存储方法的头部定义

3，存储方法可以在SQL语句内部调用

4，存储方法不能返回结果集

DELIMITER $$
DROP FUNCTION IF EXISTS f_discount_price$$
CREATE FUNCTION  f_discount_price
	(normal_price NUMERIC(8,2))
	RETURNS NUMERIC(8,2)
	DETERMINISTIC
BEGIN
	DECLARE discount_price NUMERIC(8,2);  
	
	IF (normal_price>500) THEN
		SET discount_price=normal_price*0.8;
	ELSEIF (normal_price>100) THEN
		SET discount_price=normal_price*0.9;
	ELSE 
		SET discount_price=normal_price;
	END IF;
	
	RETURN  (discount_price);
END$$

一个复杂的例子-----------------------------------------------

/*给定一个id 让该让其查ag_prisoner表 中看是否存在该犯人*/
DELIMITER $$
DROP FUNCTION IF EXISTS  personNo$$
CREATE FUNCTION personNo(person_no BIGINT)              
RETURNS BIGINT                                                          
BEGIN  
	DECLARE person_exit BIGINT;
	SET person_exit=person_no;	
	WHILE (SELECT pr_no  FROM ag_prisoner WHERE pr_no=person_exit) IS NULL   DO
		SET person_exit = person_exit + 1;
	END WHILE;
	RETURN (person_exit);
END$$

/*现在ht_t_2w_map表中生成一条数据  
  再在ag_prisoner表中查存在的编号
  再在ag_prisoner_bed表中插入一些数据
   personNo(person_no); 他会返回一个存在的person的编号
 */
DELIMITER $$
DROP PROCEDURE IF EXISTS insert_map_bed$$
CREATE PROCEDURE insert_map_bed(IN person_no BIGINT)
BEGIN 
	DECLARE i INT; /*床位的循环变量*/
	DECLARE n INT;/*监舍的循环变量*/
	DECLARE uuid_val VARCHAR(32); 			/*床位的uuid值*/
	DECLARE uuid_jianshe VARCHAR(32);		/*监舍的uuid的值*/
	DECLARE person_code VARCHAR(32);		/*床位的code值*/
	DECLARE person_code_jianshe VARCHAR(32);	/*监舍的code值*/
	DECLARE person_code_default VARCHAR(32); 	/*默认的code的值*/
	DECLARE person_bed VARCHAR(32);			/*床位的信息*/
	DECLARE person_name VARCHAR(32);            	/*床位的名字*/
	DECLARE person_name_jianshe VARCHAR(32);    /*监舍的名字*/
	
	SET person_code_default="00000001";
	
	SET n=3;
	WHILE n!=5 DO
		
		/*插入监舍*/
		SET uuid_jianshe=UPPER(REPLACE(UUID(),'-',''));
		SET person_code_jianshe=CONCAT(person_code_default, "000",  n);
		SET person_name_jianshe=CONCAT("10", n,"监舍");
		INSERT INTO ht_t_2w_map(mapguid, fac_code, mapname, maplevel, mapType)  VALUES  (uuid_jianshe, person_code_jianshe,person_name_jianshe,"3","4");
	
		/*插入床位*/
		SET i=1;
		WHILE i !=3 DO
			/*他会返回一个存在的person的编号*/
			SET person_no=personNo(person_no);
			
			SET uuid_val=UPPER(REPLACE(UUID(),'-','')); /*设置床铺的uuid值*/	
			SET person_code=CONCAT(person_code_jianshe,"000",i); /*床铺的编号*/
			SET person_bed=CONCAT(person_no,"号犯人住", i , "号床位"); /*床铺上住的犯人的名称*/
			SET person_name=CONCAT(i,"号床位");   /*床铺的名称*/
			/*先插入ht_t_2w_map表中*/
			INSERT INTO ht_t_2w_map(mapguid,fac_code,mapname,maplevel,mapType,parentGuid)  VALUES  (uuid_val, person_code,person_name,"4","5",uuid_jianshe);
			/*在插入ag_prisoner_bed表中*/
			INSERT INTO ag_prisoner_bed(pb_pr_no,pb_bed_code,pb_bed_name) VALUES (person_no,person_code,person_bed);
			
			SET person_no=person_no+1;
			SET i=i+1;
		END WHILE;
		
		SET n=n+1;
	END WHILE;
END $$

CALL insert_map_bed(6501003731);

5 根据首字母查询信息

DELIMITER $$
DROP FUNCTION IF EXISTS GET_FIRST_PINYIN_CHAR$$
CREATE FUNCTION GET_FIRST_PINYIN_CHAR(PARAM VARCHAR(255)) RETURNS VARCHAR(255) DETERMINISTIC
BEGIN 
	DECLARE V_RETURN VARCHAR(255) ;
	DECLARE V_FIRST_CHAR VARCHAR(2) ;  
	SET V_FIRST_CHAR = UPPER(LEFT(PARAM,1));
	SET V_RETURN = V_FIRST_CHAR;
	IF LENGTH( V_FIRST_CHAR) <> CHARACTER_LENGTH( V_FIRST_CHAR ) 
		THEN  
			SET V_RETURN = ELT(INTERVAL(CONV(HEX(LEFT(CONVERT(PARAM USING gbk),1)),16,10),  
			0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,  
			0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,  
			0xC8F6,0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),  
			'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z');  
	END IF;  
	RETURN V_RETURN;  
END$$

SELECT de_name,GET_FIRST_PINYIN_CHAR(de_name) FROM jfp_dept WHERE get_first_pinyin_char(de_name) = 's'

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

sql

存储

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

sql

存储

登录后参与评论

0 条评论

热度