MySQL Client Add stored procedure

DROP PROCEDURE IF EXISTS add_costitem;
DELIMITER $$

CREATE
PROCEDURE ' cloud_org '. ' Add_costitem ' ()
BEGIN
#定义 variables
DECLARE Costtypeid BIGINT;
DECLARE Costtypecode VARCHAR (32);
DECLARE Accttypeid INT;
DECLARE Bizzhycode VARCHAR (40);
DECLARE Stationcode VARCHAR (40);

DECLARE str VARCHAR (300);
DECLARE Numindex INT;

DECLARE s INT DEFAULT 0;

DECLARE cursor_name cursor for SELECT cost_type_id,cost_type_code,acct_type_id,biz_zhy_code,server_station_code from Sys_cost_item where Server_station_code not in (SELECT Server_station_code from sys_cost_item WHERE cost_item_name = ' None ' A ND server_station_code!= ') GROUP by cost_type_id;

#设置一个终止标记

DECLARE CONTINUE HANDLER for SQLSTATE ' 02000 ' SET S=1;

SET str = "--";
#打开游标

OPEN cursor_name;

#获取游标当前指针的记录, read a row of data and pass it to the variable

FETCH cursor_name into Costtypeid,costtypecode,accttypeid,bizzhycode,stationcode;
#开始循环, determine if the cursor has reached the end as a loop condition

While S <> 1 do
SET str = CONCAT (str,numindex);

INSERT into ' sys_cost_item ' (' cost_item_name ', ' cost_item_desc ', ' cost_type_id ', ' cost_type_code ', ' status ', ' Is_ Preset ', ' acct_type_id ', ' Price ', ' unit ', ' Biz_zhy_code ', ' server_station_code ', ' create_time ', ' create_user_id ', ' last_update_user_id ', ' update_time ', ' last_update_no ', ' extra_param ', ' modify_rule ') VALUES
(' None ', Null,costtypeid,costtypecode,1,2,accttypeid, ' 0.00 ', 1,bizzhycode,stationcode,null,null,null, ' 2016-08-24 16:02:53 ', 0, ', 1);
#读取下一行的数据
FETCH cursor_name into Costtypeid,costtypecode,accttypeid,bizzhycode,stationcode;

END while;

#关闭游标

CLOSE cursor_name;

SELECT str;
#语句执行结束
end$$

DELIMITER;

After creation, call the method called Add_costitem ();

The results of the stored procedure are executed every time, and you can now click Create stored procedure in the editor and copy the content between Begin and end

Description: Because the specification of some companies may disable stored procedures, you can write a simple stored procedures in advance to try

Reference: http://shitou521.iteye.com/blog/1069027

MySQL Client Add stored procedure