Home >Common Problem >How to write stored procedure

How to write stored procedure

coldplay.xixi
coldplay.xixiOriginal
2021-01-08 14:35:5239504browse

The way to write a stored procedure is: 1. Create a stored procedure, the code is [create proc sp_name]; 2. Call the stored procedure, the code is [exec sp_name [parameter name]].

How to write stored procedure

The operating environment of this article: Windows 7 system, Microsoft Visual C 2015 version, Dell G3 computer.

The stored procedure writing method is:

1. Create a stored procedure

create procedure sp_name
@[参数名] [类型],@[参数名] [类型]
as
begin
.........
end
以上格式还可以简写成:
create proc sp_name
@[参数名] [类型],@[参数名] [类型]
as
begin
.........
end
/*注:“sp_name”为需要创建的存储过程的名字,该名字不可以以阿拉伯数字开头*/

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]

  • PL/SQL Environment, the basic syntax is: begin sp_name [parameter name] end;

##3. Delete the stored procedure

1. Basic syntax:

drop procedure sp_name

2. Notes

(1) You cannot delete another stored procedure in one stored procedure, you can only call another stored procedure

4. Other commonly used commands

1.

show procedure status

Displays the basic information of all stored procedures stored in the database, including the database to which it belongs, storage Process name, creation time, etc.

2.

show create procedure sp_name

Display detailed information of a certain mysql stored procedure

3.

exec sp_helptext sp_name

Display your

sp_nameobject creation text

For more programming-related knowledge, please visit:

Programming Teaching! !

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

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn