How to create a stored procedure in navicat

#How to create a stored procedure in navicat? The following is a detailed introduction to the operations involved.

Recommended tutorial: MySQL introductory video

 BEGIN 
    DECLARE t_error INTEGER DEFAULT 0;  
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
# 保证数据一致性 开启事务 
    START TRANSACTION; 
# 获取需同步数据的时间节点（3个月前的第一天） 
# 即当前日期 2018-07-10  @upmonth 日期 2018-04-01 8
     SET @upmonth= DATE_ADD(CURDATE() - DAY (CURDATE()) + 1, INTERVAL - 3 MONTH);
# 迁移数据语句
    SET @sqlstr=CONCAT(‘INSERT INTO fd_aseet_record_back_3_6 
    SELECT * FROM fd_asset_record WHERE type in (3, 6) AND calendar_date < ?‘);
# 删除数据语句
    SET @delsqlstr=CONCAT(‘DELETE FROM fd_asset_record WHERE type in (3, 6) AND calendar_date < ?‘);
 #执行数据迁移
    PREPARE _fddatamt FROM @sqlstr;
    EXECUTE _fddatamt USING @upmonth;
    DEALLOCATE PREPARE _fddatamt;
#执行迁移后的数据删除
    PREPARE _fddatadel FROM @delsqlstr;
    EXECUTE _fddatadel USING @upmonth;
    DEALLOCATE PREPARE _fddatadel;
    IF t_error = 1 THEN  
       ROLLBACK;    #语句异常-回滚
    ELSE  
       COMMIT;    #提交事务
     END IF;  
   END

## 5. Select the stored procedure name - run the function - view the results

Extended information　

Stored procedure is an important object in the database. Mainly divided into the following categories.

Starting with sp_, it is used to set various settings of the system, obtain information, and related management work.

A user-created stored procedure is a stored procedure that is created by the user and completes a specific function. In fact, the generally referred to as a stored procedure is Refers to a local stored procedure.

It is divided into two types of stored procedures:

One is the local temporary stored procedure, with the pound sign (#) as its The first character of the name, the stored procedure will become a local temporary stored procedure stored in the tempdb database, and only the user who created it can execute it;

The second is a global temporary stored procedure, with two Starting with a pound sign (##), the stored procedure will become a global temporary stored procedure stored in the tempdb database. Once the global temporary stored procedure is created, any user connected to the server can execute it without the need for specific permissions.

In SQL Server2005, remote stored procedures (Remote Stored Procedures) are stored procedures located on the remote server. Distributed queries can usually be used and EXECUTE command to execute a remote stored procedure.

Extended Stored Procedures are stored procedures that users can write using external programming languages, and the names of extended stored procedures usually start with xp_begins.

The above is the detailed content of How to create a stored procedure in navicat. For more information, please follow other related articles on the PHP Chinese website!