home Internet What are stored procedures used for? stored procedures. An example of creating a procedure with an input parameter

What are stored procedures used for? stored procedures. An example of creating a procedure with an input parameter

stored procedure - a database object, which is a set of SQL statements that is compiled once and stored on the server. Stored procedures are very similar to ordinary procedures in high-level languages, they can have input and output parameters and local variables, they can perform numeric calculations and operations on character data, the results of which can be assigned to variables and parameters. Stored procedures can perform standard database operations (both DDL and DML). In addition, loops and branches are possible in stored procedures, that is, they can use instructions to control the execution process.

Stored procedures are similar to user-defined functions (UDFs). The main difference is that user-defined functions can be used like any other expression in a SQL query, while stored procedures must be called using the CALL function:

CALL procedure(…)

EXECUTE procedure(...)

Stored procedures can return result sets, that is, the results of a SELECT query. Such result sets can be processed using cursors, by other stored procedures returning a result set pointer, or by applications. Stored procedures can also contain declared variables for handling data and cursors, which allow you to loop over multiple rows in a table. The SQL standard provides IF, LOOP, REPEAT, CASE and many more expressions to work with. Stored procedures can accept variables, return results, or modify variables and return them, depending on where the variable is declared.

The implementation of stored procedures varies from one DBMS to another. Most major database vendors support them in one form or another. Depending on the DBMS, stored procedures can be implemented in various programming languages such as SQL, Java, C or C++. Stored procedures written in non-SQL may or may not execute SQL queries on their own.

Per

Sharing logic with other applications. Stored procedures encapsulate functionality; this ensures data access and management connectivity between different applications.

Isolate users from database tables. This allows you to give access to stored procedures, but not to the table data itself.

Provides a protection mechanism. According to the previous point, if you can only access data through stored procedures, no one else can erase your data through the SQL DELETE command.

Improved performance as a result of reduced network traffic. With stored procedures, multiple queries can be combined.

Against

Increased load on the database server due to the fact that most of the work is done on the server side, and less on the client side.

You have to learn a lot. You will need to learn the MySQL expression syntax to write your stored procedures.

You are duplicating your application logic in two places: server code and code for stored procedures, thereby complicating the process of manipulating data.

Migrating from one DBMS to another (DB2, SQL Server, etc.) can lead to problems.

Purpose and Benefits of Stored Procedures

Stored procedures improve performance, enhance programming options, and support data security features.

Instead of storing a frequently used query, clients can refer to the appropriate stored procedure. When a stored procedure is called, its contents are immediately processed by the server.

In addition to the actual execution of the query, stored procedures also allow you to perform calculations and manipulate data - change, delete, execute DDL statements (not in all DBMS!) and call other stored procedures, perform complex transactional logic. A single statement allows you to call a complex script that is contained in a stored procedure, which avoids sending hundreds of commands over the network and, in particular, the need to transfer large amounts of data from the client to the server.

In most DBMSs, the first time a stored procedure is run, it is compiled (parsed and a data access plan is generated). In the future, its processing is faster. Oracle DBMS interprets the stored procedural code stored in the data dictionary. Starting with Oracle 10g, the so-called native compilation (native compilation) of stored procedural code in C and then into the machine code of the target machine is supported, after which, when the stored procedure is called, its compiled object code is directly executed.

Programming options

Once you create a stored procedure, you can call it at any time, which provides modularity and encourages code reuse. The latter makes it easier to maintain the database, as it becomes isolated from changing business rules. You can modify a stored procedure to comply with the new rules at any time. After that, all applications using it will automatically comply with the new business rules without direct modification.

Safety

The use of stored procedures allows you to limit or completely exclude direct user access to database tables, leaving users only permissions to execute stored procedures that provide indirect and strictly regulated access to data. In addition, some DBMS support text encryption (wrapping) of a stored procedure.

These security features allow you to isolate the database structure from the user, which ensures the integrity and reliability of the database.

Actions such as "SQL injection" are less likely because well-written stored procedures additionally validate input parameters before passing the query to the DBMS.

Implementing Stored Procedures

Stored procedures are usually created with SQL language or its specific implementation in the selected DBMS. For example, for these purposes, the Microsoft SQL Server DBMS has the Transact-SQL language, Oracle has PL/SQL, InterBase and Firebird have PSQL, PostgreSQL has PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, IBM DB2 - SQL / PL (English), in Informix - SPL. MySQL follows the SQL:2003 standard quite closely, and its language is similar to SQL/PL.

In some DBMS, it is possible to use stored procedures written in any programming language capable of creating independent executable files, such as C++ or Delphi. In Microsoft SQL Server terminology, these procedures are called extended stored procedures and are simply functions contained in a Win32-DLL. And, for example, in Interbase and Firebird for functions called from DLL / SO, another name is defined - UDF (User Defined Function). In MS SQL 2005, it became possible to write stored procedures in any .NET language, and extended stored procedures are planned to be abandoned in the future. Oracle DBMS, in turn, allows writing stored procedures in Java. In IBM DB2, writing stored procedures and functions in conventional programming languages is the traditional way, supported from the outset, and procedural SQL extension was added to this DBMS only in rather late versions, after its inclusion in the ANSI standard. Informix also supports Java and C procedures.

In Oracle DBMS, stored procedures can be combined into so-called packages. The package consists of two parts - the specification (English package specification), which specifies the definition of the stored procedure, and the body (English package body), where its implementation is located. This is how Oracle allows you to separate the interface program code from its implementation.

In IBM DB2, stored procedures can be combined into modules.

Syntax

CREATE PROCEDURE `p2`()

SQL SECURITY DEFINER

COMMENT "A procedure"

SELECT "Hello World!";

The first part of the code creates a stored procedure. Next - contains optional parameters. Then comes the name and finally the body of the procedure itself.

4 characteristics of a stored procedure:

Language: For portability purposes, the default is SQL.

Deterministic: If the procedure returns the same result all the time and takes the same input parameters. This is for the replication and registration process. The default value is NOT DETERMINISTIC.

SQL Security: during the call, the user's rights are being checked. INVOKER is the user calling the stored procedure. DEFINER is the "creator" of the procedure. The default value is DEFINER.

Comment: for documentation purposes, the default value is ""

Calling a stored procedure

CALL stored_procedure_name (param1, param2, ....)

CALL procedure1(10 , "string parameter" , @parameter_var);

Changing a stored procedure

MySQL has an ALTER PROCEDURE statement for modifying procedures, but it is only suitable for modifying certain characteristics. If you need to change the parameters or the body of the procedure, you must delete it and re-create it.

Removalstoredprocedures

DROP PROCEDURE IF EXISTS p2;

This is a simple command. The IF EXISTS statement catches an error if no such procedure exists.

Options

CREATE PROCEDURE proc1(): empty parameter list

CREATE PROCEDURE proc1 (IN varname DATA-TYPE): one input parameter. The IN word is optional because the default parameters are IN (incoming).

CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): one return parameter.

CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): one parameter, both input and output.

The syntax for declaring a variable looks like this:

DECLARE varname DATA-TYPE DEFAULT defaultvalue;

When working with SQL Server, users can create their own procedures that implement certain actions. Stored procedures are full-fledged database objects, and therefore each of them is stored in a specific database. Calling a stored procedure directly is possible only if it is performed in the context of the database where the procedure resides.

Types of Stored Procedures

SQL Server has several types of stored procedures.

System stored procedures are designed to perform various administrative actions. Almost all server administration actions are performed with their help. It can be said that system stored procedures are an interface that provides work with system tables, which, ultimately, comes down to changing, adding, deleting, and retrieving data from system tables of both user and system databases. System stored procedures are prefixed with sp_, are stored in the system database, and can be called in the context of any other database.

Custom stored procedures implement certain actions. Stored procedures are a complete database object. As a result, each stored procedure is located in a specific database, where it is executed.

Temporary stored procedures exist only for a short time, after which they are automatically destroyed by the server. They are divided into local and global. Local temporary stored procedures can only be called from the connection they were created on. When creating such a procedure, it must be given a name that starts with a single # character. Like all temporary objects, stored procedures of this type are automatically deleted when the user disconnects, restarts, or stops the server. Global temporary stored procedures are available for any connection on a server that has the same procedure. To define it, it is enough to give it a name that starts with the characters ##. These procedures are deleted when the server is restarted or stopped, or when the connection in whose context they were created is closed.

triggers

triggers are one of the varieties of stored procedures. Their execution occurs when a data manipulation language (DML) statement is executed on the table. Triggers are used to check data integrity and also to roll back transactions.

Trigger is a compiled SQL procedure, the execution of which is determined by the occurrence of certain events within a relational database. The use of triggers is, for the most part, very convenient for database users. Yet their use is often associated with additional resource costs for I / O operations. When the same results (with much less resource overhead) can be achieved using stored procedures or applications, triggers are inappropriate.

triggers is a special SQL server tool used to maintain the integrity of data in a database. Integrity constraints, rules, and defaults may not always provide the desired level of functionality. It is often necessary to implement complex data validation algorithms to ensure that they are valid and real. In addition, it is sometimes necessary to track changes to table values in order to change related data as needed. Triggers can be thought of as a kind of filters that take effect after all operations have been performed according to rules, default values, and so on.

Trigger is a special type of stored procedure that is automatically run by the server when it tries to change data in the tables with which triggers are associated. Each Trigger is tied to a specific table. All data modifications it makes are treated as one transaction. If an error or data integrity violation is detected, the transaction is rolled back. Therefore, changes are prohibited. Any changes already made by the trigger are also undone.

Creates trigger only the owner of the database. This restriction allows you to avoid accidental changes in the structure of tables, ways of connecting other objects with them, etc.

Trigger is a very useful and at the same time dangerous tool. So, with the wrong logic of its work, you can easily destroy the entire database, so triggers must be debugged very carefully.

Unlike a regular subroutine, trigger is executed implicitly every time the trigger event occurs, and it has no arguments. Activating it is sometimes referred to as firing a trigger. Triggers achieve the following goals:

validating entered data and implementing complex data integrity constraints that are difficult, if not impossible, to maintain with the integrity constraints set on the table;

issuing warnings that remind you of the need to perform certain actions when updating a table implemented in a certain way;

accumulation of audit information by fixing information about the changes made and those persons who performed them;

replication support.

The basic format of the CREATE TRIGGER command is shown below:

<Определение_триггера>::=

CREATE TRIGGER trigger_name

BEFORE | AFTER<триггерное_событие>

ON<имя_таблицы>

<список_старых_или_новых_псевдонимов>]

<тело_триггера>

Trigger events consist of inserting, deleting, and updating rows in a table. In the latter case, specific table column names can be specified for the trigger event. The start time of a trigger is defined using the BEFORE keywords ( Trigger fires before the execution of its associated events) or AFTER (after their execution).

The actions performed by the trigger are set for each row (FOR EACH ROW) covered by this event, or only once for each event (FOR EACH STATEMENT).

Incorrectly written triggers can lead to serious problems, such as the appearance of "dead" locks. Triggers can block many resources for a long time, so you should pay special attention to minimizing access conflicts.

Trigger can be created only in the current database, but other databases can be accessed inside the trigger, including those located on a remote server.

Stored procedure stored procedure) is a named database program object. SQL Server has several types of stored procedures.

System stored procedures system stored procedure) are supplied by DBMS developers and are used to perform actions on the system catalog or get system information. Their names usually begin with the prefix "sp_". Stored procedures of all types are run with the EXECUTE command, which can be shortened to EXEC. For example, the sp_helplogins stored procedure, run with no parameters, generates two reports of account names (English) logins) and their corresponding users in each database (English) users).

EXEC sp_helplogins;

To give an idea of the actions performed using system stored procedures, in table. 10.6 shows some examples. In total, there are more than a thousand system stored procedures in SQL Server.

Table 10.6

Examples of SQL Server System Stored Procedures

The user can create stored procedures in user databases and in the database for temporary objects. In the latter case, the stored procedure would be temporal. As with temporary tables, the name of a temporary stored procedure must begin with a "#" prefix if it is a local temporary stored procedure, or "##" if it is a global one. A local temporary procedure can only be used within the connection in which it was created, a global one can also be used within other connections.

Programmable SQL Server objects can be created either using Transact-SQL tools or using assemblies (English) assembly) in the CRL (Common Language Runtime) environment of the Microsoft .Net Framework. In this tutorial, only the first method will be considered.

To create stored procedures, use the CREATE PROCEDURE statement (can be abbreviated to PROC), the format of which is given below:

CREATE (PROC I PROCEDURE) proc_name [ ; number]

[(gparameter data_type )

[“default] |

[WITH [ ,...n ] ]

[ FOR REPLICATION ]

AS ([ BEGIN ] sql_statement [;] [ ...n ] [ END ] )

If a stored procedure (or trigger, function, view) is created with the ENCRYPTION option, its code is converted in such a way that the text becomes unreadable. At the same time, as noted in , the algorithm used is carried over from earlier versions of SQL Server and cannot be considered a reliable protection algorithm - there are utilities that allow you to quickly perform the reverse conversion.

The RECOMPILE option specifies that each time the procedure is called, the system will recompile the text. In the usual case, the procedure compiled at the first run is stored in the cache, which allows you to increase performance.

EXECUTE AS defines the security context in which the procedure is to be executed. Next, one of the values f CALLER | SELF | OWNER | "user_name"). CALLER is the default value and means that the code will be executed in the security context of the user calling this module. Accordingly, the user must have permissions not only for the programmable object itself, but also for other database objects affected by it. EXECUTE AS SELF means using the context of a user creating or modifying a programmable object. OWNER specifies that the code will be executed in the context of the procedure's current owner. If no owner is defined for it, then the owner of the schema to which it belongs is assumed. EXECUTE AS "user_name" allows you to explicitly specify the username (in single quotes).

Parameters can be specified for a procedure. These are local variables used to pass values to a procedure. If the parameter is declared with keyword OUTPUT (or OUT for short), it is an output: the value given to it in the procedure after its completion can be used by the program that called the procedure. The keyword READONLY means that the value of the parameter cannot be changed inside the stored procedure.

Parameters can be assigned default values, which will be used if the parameter value is not explicitly specified when calling the procedure. Consider an example:

CREATE PROC surma (@a int, @b int=0,

©result int OUTPUT) AS

SET @result=0a+0b

We have created a procedure with three parameters, where the @b parameter has a default value of 0, and the @result parameter is an output parameter: through it, the value is returned to the calling program. The actions performed are quite simple - the output parameter receives the value of the sum of the two inputs.

When working in SQL server management Studio created stored procedure can be found in programmable DB objects section (English) Programmability) in the section for stored procedures (Figure 10.2).

When calling a procedure, both variables and constants can be used as input parameters. Let's consider two examples. In the first, the input parameters of the procedure are explicitly set by constants, the keyword OUTPUT is specified for the output parameter in the call. In the second option, the value of the variable is used as the first input parameter, and the second parameter is specified using the DEFAULT keyword that the default value should be used:

Rice. 10.2.

DECLARE @with int;

EXEC summa 10,5,@c OUTPUT;

PRINT0c; - 15 will be displayed

DECLARE Gi int = 5;

- when calling, use the default value

EXEC summa Gi,DEFAULT , 0s OUTPUT;

PRINT0c; - 5 will be displayed

Consider now an example with the analysis of the return code with which the procedure ends. Let it be necessary to calculate how many books in the Bookl table were published in a given range of years. In this case, if the start year is greater than the end year, the procedure returns "1" and does not count, otherwise, we count the number of books and return 0:

CREATE PROC dbo.rownum(0FirsYear int, GLastYear int, 0result int OUTPUT) AS

IF 0FirsYear>0LastYear RETURN 1

SET @result= (SELECT COUNT(*) FROM dbo.Bookl

WHERE BETWEEN 0FirsYear AND 0LastYear) ;

Consider a variant of calling this procedure, in which the return code is stored in the integer variable 0ret, after which its value is analyzed (in this case it will be 1). used in the operator PRINT function CAST is used to convert the value of an integer Gres variable to a string type:

DECLARE 0ret int, Gres int

EXEC Gret = rownum 2004, 2002, Gres OUT;

IF 0ret=l PRINT "Start year is greater than end year"

PRINT "Number of books "+ CAST(Gres as varchar(20))

Stored procedures can not only read data from a table, but also modify data and even create tables and a number of other database objects.

However, schemas, functions, triggers, procedures, and views cannot be created from a stored procedure.

The following example illustrates both these possibilities and issues related to the scope of temporary objects. The following stored procedure checks for the existence of a temporary table #Tab2; if this table does not exist, it creates it. After that, the values of two columns are entered into the table #Tab2, and the contents of the table are displayed by the SELECT statement:

CREATE PROC My_Procl (@id int, @name varchar(30))

IF OBJECT_ID("tempdb.dbo.#Tab21) IS NULL

INSERT INTO dbo.#Tab2 (id, name)VALUES (0id,0name)

SELECT * FROM dbo. #Tab2 -#1

Before the first call to the stored procedure, let's create the temporary table #Tab2 used in it. Pay attention to the EXEC operator. In the previous examples, the parameters were passed to the procedure "by position", but in this case, a different format for passing parameters is used - "by name", the parameter name and its value are explicitly indicated:

CREATE TABLE dbo.#Tab2 (id int, name varchar(30));

EXEC My_Procl 0name="lvan", 0id=2;

SELECT * FROM dbo.#Tab2; –#2

In the above example, the SELECT statement will execute twice: the first time - inside the procedure, the second time - from the calling code fragment (marked with the comment "No. 2").

Before the second procedure call, let's delete the temporary table #Tab2. Then the temporary table of the same name will be created from the stored procedure:

DROP TABLE dbo.#Tab2;

EXEC My_Procl 0name="Ivan", 0id=2;

SELECT * FROM dbo.#Tab2; –#2

In this case, only the SELECT statement inside the procedure (with the "Xa 1" comment) will display the data. Executing SELECT "#2" will result in an error, because the temporary table created in the stored procedure will already be deleted from the tempdb database when the procedure returns.

You can drop a stored procedure using the DROP PROCEDURE statement. Its format is shown below. With one statement, you can delete several stored procedures by listing them separated by commas:

DROP (PROC I PROCEDURE) ( procedure ) [

For example, let's remove the previously created summa procedure:

DROP PROC summa;

You can make changes to an existing procedure (in fact, override it) using the ALTER PROCEDURE statement (allow

abbreviation PROC). With the exception of the ALTER keyword, the format of the statement is almost the same as that of CREATE PROCEDURE. For example, let's change the procedure dbo. rownum by setting it to execute in the owner's security context:

ALTER PROC dbo.rownum(SFirsYear int,

SLastYear int, Sresult int OUTPUT)

WITH EXECUTE AS Owner - set option

IF 0FirsYear>0LastYear RETURN 1 ELSE BEGIN

SET 0result= (SELECT COUNT(*) FROM dbo.Bookl

WHERE BETWEEN SFirsYear AND SLastYear);

In some cases, it may be necessary to dynamically generate a command and execute it on the database server. This task can also be solved using the EXEC operator. The following example selects records from the Bookl table based on the condition that the Year attribute is equal to the value specified by the variable:

DECLARE 0y int = 2000;

EXEC("SELECT * FROM dbo.Bookl WHERE = " [email protected]) ;

The execution of dynamically generated instructions creates the prerequisites for the implementation of computer attacks such as "SQL injection" (English) SQL injection). The essence of the attack is that the offender injects his own SQL code into a dynamically generated query. This usually happens when inline parameters are taken from user input results.

Let's change the previous example a bit:

DECLARE 0y varchar(100);

SET 0y="2OOO"; - this is what we received from the user

If we assume that we received the string value assigned in the SET statement from the user (no matter how, for example, through a web application), then the example illustrates the "regular" behavior of our code.

DECLARE 0y varchar(100);

SET 0y="2000; DELETE FROM dbo.Book2"; – injection

EXEC("SELECT * FROM dbo.Book2 WHERE="+0y);

In it is recommended, if possible, to use the sp_executcsql system stored procedure in such cases, which allows you to control the type of parameters, which is one of the barriers to SQL injection. Without considering its format in detail, we will analyze an example similar to the one presented earlier:

EXECUTE sp_executesql

N"SELECT * FROM dbo.Bookl WHERE =0y",

This explicitly specifies the type of parameter used in the query, and SQL Server will control it during execution. The letter "N" before the quotation marks indicates that this is a Unicode literal constant, as required by the procedure. A parameter can be assigned not only a constant value, but also the value of another variable.

The concept of stored procedures is defined. Examples of creating, modifying and using stored procedures with parameters are given. The definition of input and output parameters is given. Examples of creating and calling stored procedures are given.

The concept of a stored procedure

Stored procedures are groups of interconnected SQL statements, the use of which makes the programmer's work easier and more flexible, since to execute stored procedure is often much simpler than a sequence of individual SQL statements. Stored procedures are a set of commands that consists of one or more SQL statements or functions and is stored in the database in a compiled form. Execution on the database stored procedures Instead of individual SQL statements, it gives the user the following benefits:

the necessary operators are already in the database;
they all passed the stage parsing and are in executable format; before executing a stored procedure SQL Server generates an execution plan for it, optimizes it, and compiles it;
stored procedures support modular programming, as they allow you to break large tasks into independent, smaller and easy-to-manage parts;
stored procedures may cause others stored procedures and functions;
stored procedures can be called from other types of application programs;
usually, stored procedures are executed faster than a sequence of individual statements;
stored procedures easier to use: they can consist of tens and hundreds of commands, but to run them, it is enough to specify just the name of the desired stored procedure. This allows you to reduce the size of the request sent from the client to the server, and hence the load on the network.

Storing procedures in the same place where they are executed reduces the amount of data transferred over the network and improves overall system performance. Application stored procedures simplifies maintenance software systems and making changes to them. Usually, all integrity constraints in the form of rules and data processing algorithms are implemented on the database server and are available to the end application as a set stored procedures, which represent the data processing interface. To ensure the integrity of the data, as well as for security purposes, the application usually does not get direct access to the data - all work with them is done by calling one or another stored procedures.

This approach makes it very easy to modify data processing algorithms, which immediately become available to all network users, and provides the ability to expand the system without making changes to the application itself: it is enough to change stored procedure on the database server. The developer does not need to recompile the application, create copies of it, and also instruct users about the need to work with the new version. Users may not even be aware that changes have been made to the system.

Stored procedures exist independently of tables or any other database objects. They are called by the client program, another stored procedure or trigger. The developer can manage access rights to stored procedure, allowing or forbidding its execution. Change the code stored procedure only allowed by its owner or a member of the fixed database role. If necessary, you can transfer ownership of it from one user to another.

Stored procedures in MS SQL Server environment

When working with SQL Server, users can create their own procedures that implement certain actions. Stored procedures are full-fledged database objects, and therefore each of them is stored in a specific database. Direct call stored procedure is possible only if it is executed in the context of the database where the procedure resides.

Types of Stored Procedures

There are several types in SQL Server stored procedures.

Systemic stored procedures designed to perform various administrative actions. Almost all server administration actions are performed with their help. We can say that the system stored procedures are an interface that provides work with system tables, which, ultimately, comes down to changing, adding, deleting, and retrieving data from system tables of both user and system databases. Systemic stored procedures are prefixed with sp_ , are stored in the system database, and can be called in the context of any other database.
Custom stored procedures implement certain actions. Stored procedures- a complete database object. As a result of this, each stored procedure is located in a specific database, where it is executed.
Temporary stored procedures exist only for a short time, after which they are automatically destroyed by the server. They are divided into local and global. Local temporary stored procedures can only be called from the connection in which they are created. When creating such a procedure, it must be given a name that starts with a single # character. Like all temporary objects, stored procedures of this type are automatically deleted when the user disconnects, restarts, or stops the server. Global temporary stored procedures available for any server connections that have the same procedure. To define it, it is enough to give it a name that starts with the characters ## . These procedures are deleted when the server is restarted or stopped, or when the connection in whose context they were created is closed.

Creating, Modifying, and Deleting Stored Procedures

Creation stored procedure involves solving the following tasks:

defining the type of stored procedure: temporary or custom. In addition, you can create your own system stored procedure, giving it a name prefixed with sp_ and placing it in system base data. Such a procedure will be available in the context of any database on the local server;
access planning. While creating stored procedure keep in mind that it will have the same access rights to database objects as the user who created it;
definition stored procedure parameters. Like the procedures included in most programming languages, stored procedures may have input and output parameters ;
code development stored procedure. The procedure code can contain a sequence of any SQL commands, including calling others. stored procedures.

Creating a new one and modifying an existing one stored procedure is done with the following command:

<определение_процедуры>::= (CREATE | ALTER ) PROC procedure_name [;number] [(@parameter_name datatype ) [=default] ][,...n] AS sql_statement [...n]

Consider the parameters of this command.

Using the prefixes sp_ , # , ## , the created procedure can be defined as a system or temporary procedure. As you can see from the syntax of the command, it is not allowed to specify the name of the owner to whom the created procedure will belong, as well as the name of the database where it should be placed. Thus, in order to accommodate the created stored procedure in a particular database, you must run the CREATE PROCEDURE command in the context of that database. When handled from the body stored procedure Shortened names can be used for objects in the same database, i.e. without specifying the database name. When you want to refer to objects located in other databases, specifying the database name is mandatory.

The number in the name is an identification number stored procedure, which uniquely defines it in a group of procedures. For the convenience of managing procedures, logically the same type stored procedures can be grouped by giving them the same name but different identification numbers.

To pass input and output data in the created stored procedure parameters can be used, whose names, like the names of local variables, must begin with the @ symbol. One stored procedure You can specify multiple options separated by commas. The body of a procedure must not use local variables whose names are the same as the names of the procedure's parameters.

To determine the data type that the corresponding stored procedure parameter, any type is suitable SQL data, including user-defined ones. However, the CURSOR data type can only be used as output parameter stored procedure, i.e. with the keyword OUTPUT .

The presence of the OUTPUT keyword means that the corresponding parameter is intended to return data from stored procedure. However, this does not mean at all that the parameter is not suitable for passing values to stored procedure. Specifying the OUTPUT keyword instructs the server to exit from stored procedure assign the current value of the parameter to the local variable that was specified when the procedure was called as the value of the parameter. Note that when specifying the OUTPUT keyword, the value of the corresponding parameter when calling the procedure can only be set using a local variable. Any expressions or constants allowed for normal parameters are not allowed.

The VARYING keyword is used in conjunction with the OUTPUT parameter, which is of type CURSOR . It defines that output parameter will be the resulting set.

The DEFAULT keyword is the value that the corresponding default setting. Thus, when calling a procedure, you can not explicitly specify the value of the corresponding parameter.

Since the server caches the query execution plan and compiled code, the next time the procedure is called, the already prepared values will be used. However, in some cases it is still necessary to recompile the procedure code. Specifying the RECOMPILE keyword instructs the system to create an execution plan stored procedure every time it is called.

The FOR REPLICATION parameter is required when replicating data and including the created stored procedure as an article in a publication.

The ENCRYPTION keyword instructs the server to encrypt the code stored procedure, which can provide protection against the use of copyright algorithms that implement the work stored procedure.

The AS keyword is placed at the beginning of the actual body stored procedure, i.e. a set of SQL commands, with the help of which this or that action will be implemented. Almost all SQL commands can be used in the body of the procedure, transactions can be declared, locks can be set, and others can be called. stored procedures. exit from stored procedure can be done with the RETURN command.

Deleting a stored procedure carried out by the command:

DROP PROCEDURE (procedure_name) [,...n]

Executing a Stored Procedure

For executing a stored procedure the command is used:

[[ EXEC [ UTE] procedure_name [;number] [[@parameter_name=](value | @variable_name) |][,...n]

If the call stored procedure is not the only command in the package, then the presence of the EXECUTE command is mandatory. Moreover, this command is required to call a procedure from the body of another procedure or trigger.

The use of the OUTPUT keyword when calling a procedure is only allowed for parameters that were declared when creating a procedure with the OUTPUT keyword.

When the DEFAULT keyword is specified for a procedure call, the DEFAULT keyword will be used. default value. Naturally, the specified word DEFAULT is allowed only for those parameters for which it is defined default value.

From the syntax of the EXECUTE command, you can see that parameter names can be omitted when calling a procedure. However, in this case, the user must specify the values for the parameters in the same order in which they were listed when creating a procedure. Assign to parameter default value, simply skipping it when enumerating is not possible. If it is required to omit the parameters for which the default value, it is enough to explicitly specify the parameter names when calling stored procedure. Moreover, in this way, you can list the parameters and their values in any order.

Note that when a procedure is called, either parameter names with values are specified, or only values without a parameter name. Their combination is not allowed.

Example 12.1. Procedure without parameters. Develop a procedure for obtaining the names and prices of goods purchased by Ivanov.

CREATE PROC my_proc1 AS SELECT Item.Name, Item.Price*Trade.Quantity AS Cost, Customer.Last Name FROM Customer INNER JOIN (Item INNER JOIN Transaction ON Item.ItemId=Trade.ItemId) ON Customer.CustomerCode=Trade.CustomerCode WHERE Customer .Lastname='Ivanov' Example 12.1. Procedure for obtaining the names and prices of goods purchased by Ivanov.