t–sql pl–sql_SQL存储过程–终极指南

最新推荐文章于 2022-05-03 13:18:03 发布

cunchi4221

最新推荐文章于 2022-05-03 13:18:03 发布

阅读量194

点赞数

文章标签：数据库 java python mysql sql

原文链接：https://www.journaldev.com/40682/sql-stored-procedures

版权

t–sql pl–sql

Hey, folks! In this article, we will be focusing on SQL Stored Procedures.

嘿伙计！在本文中，我们将重点介绍SQL存储过程 。

什么是存储过程？ (What is a Stored Procedure?)

Stored Procedures can be considered as Macros of System Programming. An SQL Stored Procedure is a customized and pre-defined SQL code that can be used at any point in time during the execution in the database by calling the function accordingly.

存储过程可以视为系统编程的宏。 SQL存储过程是一个自定义的预定义SQL代码，可以通过相应调用该函数在数据库中执行过程中的任何时间点使用它。

SQL Stored Procedures enable the reusability of code and help save a lot of execution time.

SQL存储过程可实现代码的可重用性，并有助于节省大量执行时间。

For example, let us consider a website’s login database wherein for any utility function it is necessary to preview the login details of a user. For the same, we can create a Stored Procedure containing an SQL SELECT statement to view the login details.

例如，让我们考虑一个网站的登录数据库，其中对于任何实用程序功能，必须预览用户的登录详细信息。同样，我们可以创建一个包含SQL SELECT语句的存储过程来查看登录详细信息。

Thus, every time the database administrator wants to preview the login details of a user, that can be easily done by calling the Stored Procedure.

因此，每次数据库管理员想要预览用户的登录详细信息时，都可以通过调用存储过程来轻松完成。

This saves the overhead of executing the same SQL query again and again.

这样可以节省一次又一次执行同一SQL查询的开销。

创建一个SQL存储过程 (Creating an SQL Stored Procedure)

SQL Stored Procedures are actually defined as SQL code blocks to be called at the time of execution.

SQL存储过程实际上定义为在执行时要调用SQL代码块。

We have created a Table ‘Info’ using SQL CREATE and INSERT query with data columns as ‘id’ and ‘Cost’. SQL SELECT statement is used to display the contents of the table ‘Info’.

我们使用SQL CREATE和INSERT查询创建了一个表'Info'，数据列为'id'和'Cost'。 SQL SELECT语句用于显示表“ Info”的内容。

Creating a DataBase:

创建一个数据库：


create table Info(id integer, Cost integer);
insert into Info(id, Cost) values(1, 100);
insert into Info(id, Cost) values(2, 50);
insert into Info(id, Cost) values(3, 65);
insert into Info(id, Cost) values(4, 97);
insert into Info(id, Cost) values(5, 12);

select * from Info;

Output:

输出：

Having created the database Table, let us create a SQL Stored Procedure using the below command:

创建数据库表之后，让我们使用以下命令创建一个SQL存储过程：

Syntax:

句法：


CREATE PROCEDURE procedure-name
AS
<SQL Commands>
GO;

The GO statement is not a mandatory query. It helps the entire Stored Procedure block to execute separately and at once by the compiler.

GO语句不是强制性查询。它有助于整个存储过程块分别由编译器立即执行。

Example:

例：


CREATE PROCEDURE show
AS
SELECT * FROM Info
GO;

执行存储过程 (Execution of a Stored Procedure)

SQL Stored Procedures can be called anywhere within the entire program execution through the below command:

可以通过以下命令在整个程序执行过程中的任何位置调用SQL存储过程：

Syntax:

句法：


EXEC procedure-name;

So, everytime we call the SQL Stored Procedure by SQL EXEC query, it executes the entire SQL block at once.

因此，每次我们通过SQL EXEC查询调用SQL存储过程时，它都会立即执行整个SQL块。

Example:

例：


EXEC show;

Output:

输出：

具有单个参数的存储过程 (Stored Procedure with a Single Parameter)

SQL Stored Procedures work with parameters as well. We can create Stored Procedures with one or more parameters and then call the procedures by providing the parameters to it.

SQL存储过程也可以使用参数。我们可以使用一个或多个参数创建存储过程，然后通过向其提供参数来调用该过程。

Syntax:

句法：


CREATE PROCEDURE procedure-name @variable data-type
AS
SELECT * FROM Table
WHERE column = @variable;

Here, we have used SQL WHERE clause to put restrictions to the execution of stored procedure where only those data values will be displayed which satisfies the mentioned condition.

在这里，我们已使用SQL WHERE子句对存储过程的执行施加了限制，在该过程中将仅显示满足上述条件的那些数据值。

Example:

例：


CREATE PROCEDURE disp @Cost nvarchar(30)
AS
SELECT * FROM Info WHERE Cost = @Cost;

In the above example, we have created a stored procedure with a single parameter declared as ‘Cost’ with data type nvarchar. We applied a condition through WHERE clause according to which those values whose ‘Cost’ parameter value = “100” are displayed.

在上面的示例中，我们创建了一个存储过程，该存储过程的单个参数声明为“ Cost”，数据类型为nvarchar。我们通过WHERE子句应用了一个条件，根据该条件，将显示“成本”参数值=“ 100”的那些值。


EXEC disp @Cost = "100";

Output:

输出：


Cost
100

具有多个参数SQL存储过程 (SQL Stored Procedures with Multiple Parameters)

SQL Stored Procedures occupy multiple parameters of the database by using a SQL AND operator.

SQL存储过程通过使用SQL AND运算符来占用数据库的多个参数。

Syntax:

句法：


CREATE PROCEDURE procedure-name @variable1 data-type, @variable2 data-type
AS
SELECT * FROM Table
WHERE column1 = @variable1 AND column2 = @variable2;

Example:

例：


CREATE PROCEDURE Multi_disp @Cost nvarchar(30), @id INTEGER
AS
SELECT * FROM Info WHERE Cost = @Cost AND id = @id;

So, in this example we have created a SQL Stored Procedure with two parameters ‘id’ and ‘Cost’.

因此，在此示例中，我们创建了一个带有两个参数“ id”和“ Cost”SQL存储过程。


EXEC Multi_disp @Cost = "100" , @id = "1";

As seen, when the compiler encounters the above execution statement, it displays only those data values whose id = 1 and cost = 100.

可以看到，当编译器遇到上述执行语句时，它仅显示id = 1和cost = 100的那些数据值。


id         Cost
1          100

结论 (Conclusion)

Thus, we have come to the end of this topic. Please feel free to comment in case you come across any doubt and for more such articles related to SQL, please do visit SQL JournalDev.

因此，我们到了本主题的结尾。如果您有任何疑问，请随时发表评论，有关SQL的更多此类文章，请访问SQL JournalDev 。