Shulou(Shulou.com)05/31 Report--

This article will explain in detail the examples of the use of stored procedures in the database. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

What is a stored procedure?

If you have been exposed to other programming languages, it is easy to understand that stored procedures are like methods.

It turns out that he is a method, so he has a similar method name, the variable to be passed by the method and the return result, so the stored procedure has a stored procedure name, a stored procedure parameter and a return value.

Advantages of stored procedures:

The ability of stored procedures greatly enhances the function and flexibility of the SQL language.

The security and integrity of the data can be guaranteed.

Through the stored procedure, the unauthorized users can access the database indirectly under control, so as to ensure the security of the data.

Through stored procedures, related actions can occur together, thus the integrity of the database can be maintained.

Before running the stored procedure, the database has analyzed the syntax and syntax of the stored procedure, and given the optimized execution scheme. This compiled process can greatly improve the performance of SQL statements.

Can reduce the traffic of the network.

The operation program that embodies the enterprise rules is put 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 procedure

Let's first take a look at the system stored procedures, which are defined by the system and are mainly stored in the MASTER database, with the name beginning with "SP" or "XP". Although these system stored procedures are in the MASTER database

However, we can still call system stored procedures in other databases. Some system stored procedures are automatically created in the current database when a new database is created.

Common system stored procedures are:

Exec sp_databases;-View the database

Exec sp_tables;-View tabl

Exec sp_columns student;-- View column

Exec sp_helpIndex student;-- View Index

Exec sp_helpConstraint student;-- constraint

Exec sp_helptext 'sp_stored_procedures';-- view the statements defined by the stored procedure creation

Exec sp_stored_procedures

Exec sp_rename student, stuInfo;-- change table name

Exec sp_renamedb myTempDB, myDB;-- change database name

Exec sp_defaultdb 'master',' myDB';-- changes the default database of the login

Exec sp_helpdb;-- database help, querying database information

Exec sp_helpdb master

Exec sp_attach_db-attach database

Exec sp_detach_db-detach the database

Stored procedure syntax:

Before creating a stored procedure, let's talk about the naming of the stored procedure. I have seen several articles about stored procedures that like to add a prefix when creating a stored procedure. It is important to get into the habit of prefixing the name of a stored procedure. Although this is only a small thing, small details often determine success or failure. See that some people like to add prefixes like this, such as proc_ name. Also see this plus sample prefix usp_ name. The former proc is an abbreviation for procedure, while the latter sup means user procedure. I prefer the first, so all the stored procedure names below are written in the first. As for the writing of the name, the camel naming method is used.

The syntax for creating a stored procedure is as follows:

CREATE PROC [EDURE] stored procedure name @ parameter 1 [data type] = [default] [OUTPUT] @ parameter 2 [data type] = [default] [OUTPUT] AS SQL statement EXEC procedure name [parameter] uses the stored procedure example: 1. No parameter create procedure proc_select_officeinfo-- (stored procedure name) as select Id,Name from Office_Info-- (sql statement) exec proc_select_officeinfo-- (calling stored procedure) 2. With input parameter create procedure procedure_proc_GetoffinfoById-Name dbo.Office_Info Id=@Id---- (after the stored procedure name, a space is added with a parameter, and multiple parameters are separated by a comma) Note: the parameter assignment is that the first parameter can not write the parameter name, followed by the parameter, which parameter name needs to be passed in clearly. With input and output parameters

Create procedure proc_office_info-- (stored procedure name)

@ Id int,@Name varchar (20) output-- (parameter name parameter type) add output to the outgoing parameter

As

Begin

Select @ Name=Name from dbo.Office_Info where Id=@Id-- (sql statement)

End

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

Exec proc_office_info-- (stored procedure name)

4 output houseName output-- (legend says that the parameter needs to be added. If you use @ variable = OUTPUT here, you will get an error, so write it another way)

Select @ houseName-- (display value)

4. With a return value

Create procedure proc_office_info-- (stored procedure name) @ Id int-- (parameter name parameter type) as beginif (select Name from dbo.Office_Info where Id=@Id) = null-- (sql statement) beginreturn-1endelsebeginreturn 1endenddeclare @ house varchar (20)-- declare a variable and get the value exec @ house=proc_office_info 2 from the stored procedure-- (call the stored procedure) Receive return values with variables)-- Note: stored procedures with return values can only be returned values of type int print @ house. That's all for the article on "instances of using stored procedures in a database". Hope that the above content can be helpful to you, so that you can learn more knowledge, if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.