How to write the stored procedure in the database

This article is about how to write stored procedures in the database. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The stored procedure is written as follows: 1, create the stored procedure with the code [create proc sp_name]; 2, call the stored procedure with the code [exec sp_name [parameter name]].

Stored procedures are written as follows:

1. Create a stored procedure

Create procedure sp_name@ [parameter name] [type], @ [parameter name] [type] asbegin.end can also be abbreviated to: create proc sp_name@ [parameter name] [type], @ [parameter name] [type] asbegin.end/* Note: "sp_name" is the name of the stored procedure to be created, which cannot start with an Arabic numeral * /

2. Call the stored procedure

Stored procedures can be called in three environments:

Under the command command, the basic syntax is: exec sp_name [parameter name]

In SQL environment, the basic syntax is: call sp_name [parameter name]

In PL/SQL environment, the basic syntax is: begin sp_name [parameter name] end

3. Delete stored procedures

1. Basic syntax:

Drop procedure sp_name

two。 Matters needing attention

(1) you cannot delete another stored procedure in one stored procedure, only another stored procedure can be called.

4. Other common commands

1.show procedure status

Displays the basic information of all stored procedures in the database, including the database, the name of the stored procedure, the creation time, etc.

2.show create procedure sp_name

Displays the details of a mysql stored procedure

3.exec sp_helptext sp_name

Show you this sp_name object to create text

Thank you for reading! This is the end of the article on "how to write stored procedures in the database". I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it for more people to see!