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

##1. Use Navicat Premium to open the Create Function Wizard and operate : Connection name - database - function - new function

2. Select process - enter stored procedure parameters - complete (you can not fill in the parameters in this step, write Set parameters when storing procedure code)

3. Complete the writing of stored procedure code as required

 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

4. Save - enter the stored procedure name - OK

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

Extended information　

Stored Procedure (Stored Procedure) is a A set of SQL statements to complete a specific function. It is stored in the database and is permanently valid after compilation. The user executes it by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters).

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

1 System stored procedure

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

2 Local Stored Procedure

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.

3 Temporary 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.

4 Remote Stored Procedures

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.

5 Extended Stored Procedures

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!