A brief discussion of database stored procedures

What is a stored procedure

If you have been exposed to other programming languages, then it will be easier to understand. Stored procedures are just like methods. .

It turns out that it is a method, so it has a similar method name, variables to be passed by the method and return results, so the stored procedure has a stored procedure name, stored procedure parameters and return value.

Advantages of stored procedures:

• The ability of stored procedures greatly enhances the functionality and flexibility of the SQL language.
• Can ensure the security and integrity of data.
• Through stored procedures, users without permissions can indirectly access the database under control, thereby ensuring data security.
• Through stored procedures, related actions can occur together, thereby maintaining the integrity of the database.
• Before running the stored procedure, the database has analyzed its grammar and syntax and provided an optimized execution plan. This compiled process can greatly improve the performance of SQL statements.
• Can reduce network traffic.
• Put the computing program that embodies the enterprise rules into the database server for centralized control.

Stored procedures can be divided into system stored procedures, extended stored procedures and user-defined stored procedures

System stored procedures

We Let's first take a look at the system stored procedures. The system stored procedures are defined by the system and are mainly stored in the MASTER database. The names start with "SP" or "XP". Although these system stored procedures are in the MASTER database, we can still call system stored procedures in other databases. There are some system stored procedures that are automatically created in the current database when a new database is created.

Commonly used system stored procedures are:

exec sp_databases; --View database

• exec sp_tables; --View table
• exec sp_columns student ;--View columns
• exec sp_helpIndex student;--View index
• exec sp_helpConstraint student;--Constraints
• exec sp_helptext 'sp_stored_procedures';--View stored procedure creation Defined statement
• exec sp_stored_procedures;
• exec sp_rename student, stuInfo;--change the table name
• exec sp_renamedb myTempDB, myDB;--change the database name
• exec sp_defaultdb 'master', 'myDB';--Change the default database for login name
• exec sp_helpdb;--Database help, query database information
• exec sp_helpdb master;
• exec sp_attach_db --Attach database
• exec sp_detach_db --Detach database
• Stored procedure syntax:

Before creating a stored procedure, let’s talk about it first Let’s talk about the naming of stored procedures. I saw that several articles about stored procedures like to add a prefix when creating a stored procedure. It is important to develop the habit of adding a prefix before the name of a stored procedure. Although this is only a small thing, But often small details determine big success or failure. I saw that some people like to add prefixes like this, such as proc_name. Also see this prefix usp_name. The former proc is the abbreviation of procedure, and the latter sup means user procedure. I prefer the first method, so all the stored procedure names below will be written in the first method. As for the writing of the name, the camel nomenclature is used.

The syntax for creating a stored procedure is as follows:

CREATE PROC[EDURE] 存储过程名 

@参数1 [数据类型]=[默认值] [OUTPUT] 

@参数2 [数据类型]=[默认值] [OUTPUT]

AS 

SQL语句

EXEC 过程名[参数]

Use a stored procedure instance:

1. Without parameters

create procedure proc_select_officeinfo--(存储过程名)as select Id,Name from Office_Info--(sql语句)

exec proc_select_officeinfo--(调用存储过程)

2. With input parameters

create procedure procedure_proc_GetoffinfoById ----  Name  dbo.Office_Info  Id=@Id----(存储过程名称之后,空格加上参数,多个参数中间以逗号分隔)

注:参数赋值是,第一个参数可以不写参数名称,后面传入参数,需要明确传入的是哪个参数名称

3. With input and output parameters

create procedure proc_office_info--( Stored procedure name)

@Id int,@Name varchar(20) output--(parameter name parameter type) Outgoing parameters should be added with output

as
begin
select @Name=Name from dbo.Office_Info where Id=@Id --(sql statement)
end

declare @houseName varchar(20) --Declare a variable and get the value passed by the stored procedure

exec proc_office_info- -(stored procedure name)

4,@houseName output--(It is said that the parameter needs to add output. If you use @variable = OUTPUT here, an error will be reported, so change the writing method)

select @houseName-- (Display value)

4. With return value

create procedure proc_office_info--（存储过程名）
@Id int--(参数名 参数类型)as beginif(select Name from dbo.Office_Info where Id=@Id)=null --(sql语句)
beginreturn -1endelsebeginreturn 1end
end

declare @house varchar(20) --声明一个变量,获取存储过程传出来的值
exec @house=proc_office_info 2 --(调用存储过程,用变量接收返回值)--注：带返回值的存储过程只能为int类型的返回值
print @house

For more technical articles related to SQL, please visit

SQL tutorial column to learn!

The above is the detailed content of A brief discussion of database stored procedures. For more information, please follow other related articles on the PHP Chinese website!