mysql 存储过程和函数开发实践 |

1月 7, 2019 | Nix.Huang

很少写，哎一个分隔符都搞了半天，记录之

DELIMITER $$
CREATE FUNCTION concat_supplierName(v_productId INT) RETURNS varchar(128)
begin
declare v_name varchar(128);
select 1 into v_name;
RETURN v_name;
end;$$
DELIMITER ;

DELIMITER $$

CREATE FUNCTION concat_supplierName(v_productId INT) RETURNS varchar(128)

begin

declare v_name varchar(128);

select 1 into v_name;

RETURN v_name;

end;$$

DELIMITER ;

创建存储过程的样例代码

delimiter $$
CREATE PROCEDURE repeat_cursor_test()
begin
    DECLARE done BOOLEAN DEFAULT FALSE;
    declare v_product_id decimal(11);
     
   declare v_counter decimal(11) default 0;
   DECLARE repeat_cursor  cursor for
	   select ...
	   
    declare continue handler for not found set done = TRUE;
	
	-- 根据latlng+ 1个条件匹配
    open repeat_cursor;
       read_loop: LOOP
	   fetch repeat_cursor into v_product_id;
	   IF done THEN
			LEAVE read_loop;
	   END IF;
	   if v_counter >= 1000 then
		   commit;
		   set v_counter := 0;
	   end if;
	   set v_counter := v_counter + 1;
    END LOOP;
    CLOSE repeat_cursor;
	
end$$
delimiter ;

delimiter $$

CREATE PROCEDURE repeat_cursor_test()

begin

DECLARE done BOOLEAN DEFAULT FALSE;

declare v_product_id decimal(11);

declare v_counter decimal(11) default 0;

DECLARE repeat_cursor cursor for

select ...

declare continue handler for not found set done = TRUE;

-- 根据latlng+ 1个条件匹配

open repeat_cursor;

read_loop: LOOP

fetch repeat_cursor into v_product_id;

IF done THEN

LEAVE read_loop;

END IF;

if v_counter >= 1000 then

commit;

set v_counter := 0;

end if;

set v_counter := v_counter + 1;

END LOOP;

CLOSE repeat_cursor;

end$$

delimiter ;

Posted in: MySQL practise

知行合一，止于至善

mysql 存储过程和函数开发实践

站内搜索