SQL Stored Procedures: Creation and Use. Stored procedures in SQL
Include a line in your procedures - SET NOCOUNT ON:
With each DML expression, SQL server carefully returns us a message containing the number of processed records. This information may be useful to us while debugging the code, but after that it will be completely useless. By writing SET NOCOUNT ON, we disable this function. For stored procedures containing multiple expressions or/and loops, this action can give a significant performance increase, because the amount of traffic will be significantly reduced.
Transact-SQL
Use the schema name with the object name:
Well, I think it’s clear. This operation tells the server where to look for objects and instead of randomly rummaging through its bins, it will immediately know where it needs to go and what to take. With a large number of databases, tables and stored procedures, it can significantly save our time and nerves.
Transact-SQL
SELECT * FROM dbo.MyTable --Doing it this way is good -- Instead of SELECT * FROM MyTable --And doing it this way is bad --Calling the EXEC dbo.MyProc procedure --Good again --Instead of EXEC MyProc --Bad!
Don't use the "sp_" prefix in the name of your stored procedures:
If our procedure name starts with "sp_", SQL Server will look in its main database first. The fact is that this prefix is used for personal internal stored procedures of the server. Therefore, its use may lead to additional costs and even incorrect results if a procedure with the same name as yours is found in its database.
Use IF EXISTS (SELECT 1) instead of IF EXISTS (SELECT *):
To check for the existence of a record in another table, we use the IF EXISTS statement. This expression returns true if at least one value is returned from the internal expression, it doesn’t matter “1”, all columns or a table. The returned data is basically not used in any way. Thus, to compress traffic during data transmission, it is more logical to use “1”, as shown below.
Stored procedure - a database object, which is a set of SQL instructions that is compiled once and stored on the server. Stored procedures are very similar to ordinary high-level language procedures, 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, stored procedures allow loops and branches, 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 statement, while stored procedures must be called using the CALL function:
CALL procedure(…)
EXECUTE procedure(…)
Stored procedures can return multiple results, that is, the results of a SELECT query. Such result sets can be processed using cursors, other stored procedures that return a result set pointer, or applications. Stored procedures can also contain declared variables for processing data and cursors, which allow you to loop over multiple rows in a table. The SQL standard provides IF, LOOP, REPEAT, CASE, and many others 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 not written in SQL may or may not execute SQL queries on their own.
Behind
Sharing logic with other applications. Stored procedures encapsulate functionality;
this provides connectivity for data access and management across different applications.
Provides a protection mechanism. As per 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 execution as a result of reduced network traffic. Using stored procedures, multiple queries can be combined.
Against
Increased load on the database server due to the fact that most of the work is performed on the server side, and less on the client side.
You'll have to learn a lot. You will need to learn 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 data manipulation.
Migration 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 capabilities, and support data security features.
Instead of storing a frequently used query, clients can reference the corresponding stored procedure. When a stored procedure is called, its contents are immediately processed by the server.
In addition to actually executing the query, stored procedures also allow you to perform calculations and manipulate data - changing, deleting, executing DDL statements (not in all DBMSs!) and calling other stored procedures, and performing complex transactional logic. A single statement allows you to call a complex script contained in a stored procedure, avoiding sending hundreds of commands across 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 carried out faster. The Oracle DBMS interprets stored procedural code stored in the data dictionary. Starting with Oracle 10g, the so-called native compilation of stored procedural code in C and then into the machine code of the target machine is supported, after which, when a stored procedure is called, its compiled object code is directly executed.
Programming capabilities
The created stored procedure can be called at any time, which provides modularity and encourages code reuse. The latter makes the database easier to maintain, since it becomes isolated from changing business rules. You can modify a stored procedure in accordance with the new rules at any time. After this, all applications using it will automatically come into compliance with the new business rules without direct modification.
Safety
The use of stored procedures allows you to limit or completely eliminate direct user access to database tables, leaving users with only permissions to execute stored procedures that provide indirect and strictly regulated access to data. In addition, some DBMSs support text encryption (wrapping) of stored procedures.
These security features allow the database structure to be isolated from the user, ensuring database integrity and reliability.
The likelihood of actions such as SQL injection is reduced because well-written stored procedures additionally check input parameters before passing the query to the DBMS.
Implementing Stored Procedures
Stored procedures are usually created using the SQL language or its specific implementation in the selected DBMS. For example, for these purposes, in the Microsoft SQL Server DBMS there is the Transact-SQL language, in Oracle - PL/SQL, in InterBase and Firebird - PSQL, in PostgreSQL - PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, in IBM DB2 - SQL/PL (English), in Informix - SPL. MySQL follows the SQL:2003 standard quite closely, its language is similar to SQL/PL.
Some DBMSs allow the use of stored procedures written in any programming language that can create independent executable files, for example, C++ or Delphi. In Microsoft SQL Server terminology, such procedures are called extended stored procedures and are simply functions contained in a Win32 DLL. And, for example, in Interbase and Firebird, functions called from DLL/SO have a different name - UDF (User Defined Function). MS SQL 2005 introduced the ability to write stored procedures in any .NET language, and extended stored procedures are planned to be abandoned in the future. The Oracle DBMS, in turn, allows writing stored procedures in Java. In IBM DB2, writing stored procedures and functions in conventional programming languages is a traditional way, supported from the very beginning, and the SQL procedural extension was added to this DBMS only in fairly late versions, after its inclusion in the ANSI standard. Informix also supports procedures in Java and C.
In the Oracle DBMS, stored procedures can be combined into so-called packages. A package consists of two parts - a package specification, which specifies the definition of a stored procedure, and a package body, which contains its implementation. Thus, Oracle allows you to separate the interface of the program code from its implementation.
In the IBM DB2 DBMS, 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. The next one 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 always returns the same result and takes the same input parameters. This is for the replication and registration process. The default value is NOT DETERMINISTIC.
SQL Security: user rights are checked during the call. 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);
Modifying a Stored Procedure
MySQL has an ALTER PROCEDURE statement for changing procedures, but it is only suitable for changing certain characteristics. If you need to change the parameters or body of a procedure, you should delete and recreate it.
Removalstoredprocedures
DROP PROCEDURE IF EXISTS p2;
This is a simple command. The IF EXISTS statement catches an error if such a procedure does not exist.
Options
CREATE PROCEDURE proc1(): empty parameter list
CREATE PROCEDURE proc1 (IN varname DATA-TYPE): one input parameter. The word IN is optional because the default parameters are IN (in).
CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): one parameter returned.
CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): one parameter, both input and return.
The variable declaration syntax looks like this:
DECLARE varname DATA-TYPE DEFAULT defaultvalue;
Goal of the work– learn to create and use stored procedures on the database server.
1. Work through all the examples, analyze the results of their execution in the SQL Server Management Studio utility. Checking the presence of created procedures in the current database.
2. Completing all examples and tasks during laboratory work.
3. Completing individual tasks according to options.
Explanations for performing the work
To master stored procedure programming, we use an example database called DB_Books, which was created in laboratory work No. 1. When completing examples and tasks, pay attention to the correspondence of the names of the database, tables and other project objects.
Stored procedures are a set of commands consisting of one or more SQL statements or functions and stored in a database in compiled form.
Types of Stored Procedures
System stored procedures are designed to perform various administrative actions. Almost all server administration activities are performed with their help. We can say that system stored procedures are an interface that provides work with system tables. 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 full-fledged 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 in which they are created. When creating such a procedure, you must give it a name that begins with a single # character. Like all temporary objects, stored procedures of this type are automatically deleted when the user disconnects or the server is restarted or stopped. Global temporary stored procedures are available to any connection from a server that has the same procedure. To define it, just give it a name starting with the characters ##. These procedures are deleted when the server is restarted or stopped, or when the connection in the context in which they were created is closed.
Creating, modifying stored procedures
Creating a stored procedure involves solving the following problems: planning access rights. When you create a stored procedure, be aware that it will have the same access rights to database objects as the user who created it; defining the parameters of a stored procedure; stored procedures can have input and output parameters; stored procedure code development. The procedure code can contain a sequence of any SQL commands, including calls to other stored procedures.
The syntax of the operator for creating a new or changing an existing stored procedure in MS SQL Server notation:
( CREATE | ALTER ) PROC[ EDURE] procedure_name [ ;number] [ ( @parameter_name data_type ) [ VARYING ] [ = DEFAULT ] [ OUTPUT ] ] [ ,... n] [ WITH ( RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION ) ] [ FOR REPLICATION] AS sql_statement [ ... n]Let's look at the parameters of this command.
Using the prefixes sp_, #, ##, the created procedure can be defined as a system or temporary one. As you can see from the command syntax, it is not allowed to specify the name of the owner who will own the created procedure, as well as the name of the database where it should be located. Therefore, to place the stored procedure you are creating in a specific database, you must issue the CREATE PROCEDURE command in the context of that database. When accessing objects of the same database from the body of a stored procedure, you can use shortened names, i.e., without specifying the database name. When you need to access objects located in other databases, specifying the database name is mandatory.
To pass input and output data, the parameter names in the stored procedure you create must begin with the @ character. You can specify multiple parameters in a single stored procedure, separated by commas. The body of a procedure should not use local variables whose names coincide with the names of the parameters of this procedure. Any SQL data type, including user-defined ones, is suitable for determining the data type of stored procedure parameters. However, the CURSOR data type can only be used as the output parameter of a stored procedure, i.e. specifying the OUTPUT keyword.
The presence of the OUTPUT keyword means that the corresponding parameter is intended to return data from a stored procedure. However, this does not mean that the parameter is not suitable for passing values to a stored procedure. Specifying the OUTPUT keyword instructs the server, when exiting a stored procedure, to assign the current parameter value to the local variable that was specified as the parameter value when the procedure was called. 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 that are allowed for normal parameters are not permitted. The VARYING keyword is used in conjunction with the OUTPUT parameter, which is of type CURSOR. It specifies that the output will be the result set.
The DEFAULT keyword represents the value that the corresponding parameter will take by default. Thus, when calling a procedure, you do not have to explicitly specify the value of the corresponding parameter.
Since the server caches the query execution plan and the compiled code, the next time the procedure is called, the ready-made 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 for the stored procedure each 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 stored procedure code, which can provide protection against the use of proprietary algorithms that implement the stored procedure. The AS keyword is placed at the beginning of the stored procedure body itself. The procedure body can use almost all SQL commands, declare transactions, set locks, and call other stored procedures. You can exit a stored procedure using the RETURN command.
Removing a Stored Procedure
DROP PROCEDURE (procedure_name) [,...n]
Executing a Stored Procedure
To execute a stored procedure, use the command: [ [ EXEC [ UTE] procedure_name [ ;number] [ [ @parameter_name= ] ( value | @variable_name) [ OUTPUT ] | [ DEFAULT ] ] [ ,... n]
If the stored procedure call is not the only command in the batch, then the EXECUTE command is required. 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 allowed only for parameters that were declared when the procedure was created with the OUTPUT keyword.
When the DEFAULT keyword is specified for a parameter when calling a procedure, the default value will be used. Naturally, the specified word DEFAULT is allowed only for those parameters for which a default value is defined.
The syntax of the EXECUTE command shows 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 the procedure. You cannot assign a default value to a parameter by simply omitting it during enumeration. If you want to omit parameters that have a default value, it is enough to explicitly specify the parameter names when calling the stored procedure. Moreover, in this way you can list parameters and their values in any order.
Note that when calling a procedure, either parameter names with values, or only values without a parameter name are specified. Combining them is not allowed.
Using RETURN in a stored procedure
Allows you to exit the procedure at any point according to a specified condition, and also allows you to convey the result of the procedure as a number, by which you can judge the quality and correctness of the procedure. An example of creating a procedure without parameters:
CREATE PROCEDURE Count_Books AS SELECT COUNT (Code_book) FROM Books GOExercise 1.
EXEC Count_BooksCheck the result.
An example of creating a procedure with an input parameter:
CREATE PROCEDURE Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages GOTask 2. Create this procedure in the Stored Procedures section of the DB_Books database using the SQL server Management Studio utility. Run it using the command
EXEC Count_Books_Pages 100Check the result.
An example of creating a procedure with input parameters:
CREATE PROCEDURE Count_Books_Title @Count_pages AS INT , @Title AS CHAR (10 ) AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GOTask 3. Create this procedure in the Stored Procedures section of the DB_Books database using the SQL server Management Studio utility. Run it using the command
EXEC Count_Books_Title 100 , "P%"Check the result.
An example of creating a procedure with input parameters and an output parameter:
CREATE PROCEDURE Count_Books_Itogo @Count_pages INT , @Title CHAR (10 ) , @Itogo INT OUTPUT AS SELECT @Itogo = COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GOTask 4. Create this procedure in the Stored Procedures section of the DB_Books database using the SQL server Management Studio utility. Run using the command set:
Sql> Declare @q As int EXEC Count_Books_Itogo 100, "P%", @q output select @q
Check the result.
An example of creating a procedure with input parameters and RETURN:
CREATE PROCEDURE checkname @param INT AS IF (SELECT Name_author FROM authors WHERE Code_author = @param) = "Pushkin A.S."RETURN 1 ELSE RETURN 2 Task 5.
Create this procedure in the Stored Procedures section of the DB_Books database using the SQL server Management Studio utility. Run it using the commands:DECLARE @return_status INT EXEC @return_status = checkname 1 SELECT "Return Status" = @return_status
An example of creating a procedure without parameters to increase the value of a key field in the Purchases table by 2 times:CREATE PROC update_proc AS UPDATE Purchases SET Code_purchase = Code_purchase* 2 Create this procedure in the Stored Procedures section of the DB_Books database using the SQL server Management Studio utility. Run it using the command
EXEC update_procAn example of a procedure with an input parameter to obtain all information about a specific author:
CREATE PROC select_author @k CHAR (30 ) AS SELECT * FROM Authors WHERE name_author= @kTask 7.
EXEC select_author "Pushkin A.S."or select_author @k= "Pushkin A.S."
or EXEC select_author @k= "Pushkin A.S."An example of creating a procedure with an input parameter and a default value to increase the value of a key field in the Purchases table by a specified number of times (2 times by default):
CREATE PROC update_proc @p INT = 2 AS UPDATE Purchases SET Code_purchase = Code_purchase * @p The procedure does not return any data.
Task 8. Create this procedure in the Stored Procedures section of the DB_Books database using the SQL server Management Studio utility. Run it using the commands:EXEC update_proc 4 or EXEC update_proc @p = 4 or EXEC update_proc
--the default value will be used.An example of creating a procedure with input and output parameters. Create a procedure to determine the number of orders completed during a specified period: The procedure does not return any data.
CREATE PROC count_purchases @d1 SMALLDATETIME, @d2 SMALLDATETIME, @c INT OUTPUT AS SELECT @c= COUNT (Code_purchase) FROM Purchases WHERE Date_order BETWEEN @d1 AND @d2 SET @c = ISNULL(@c, 0 )Task 9.
DECLARE @c2 INT EXEC count_purchases '01-jun-2006', '01-jul-2006', @c2 OUTPUT SELECT @c2 Options for tasks for laboratory work No. 4
General provisions.
In the SQL Server Management Studio utility, create a new page for the code (the “Create Query” button). Programmatically make the created DB_Books database active using the Use statement. Create stored procedures using Create procedure statements, and define the names of the procedures yourself. Each procedure will execute one SQL query that was executed in the second lab. Moreover, the SQL code of the queries needs to be changed so that they can transmit the values of the fields used to search.
For example, the initial task and request in laboratory work No. 2:*/ –In this work the following procedure will be created:
CREATE PROC select_name_company @comp CHAR (30 ) AS SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = @comp–To start the procedure, use the command:
EXEC select_name_company "JSC MIR"Task list
Create a new program in SQL Server Management Studio. Programmatically make active the individual database created in laboratory work No. 1 using the Use statement. Create stored procedures using Create procedure statements, and define the names of the procedures yourself. Each procedure will execute one SQL query, which is presented in the form of separate tasks according to options.
Option 1
1. Display a list of employees who have at least one child.
2. Display a list of children who received gifts during the specified period.
3. Display a list of parents who have minor children.
4. Display information about gifts with a value greater than the specified number, sorted by date.
Option 2
1. Display a list of devices with the specified type.
2. Display the number of devices repaired and the total cost of repairs from the specified technician.
3. Display a list of device owners and the number of their requests, sorted by the number of requests in descending order.
4. Display information about craftsmen with a rank greater than the specified number or with a hiring date less than the specified date.
Option 3
2. Display a list of sales codes that sold flowers for an amount greater than the specified number.
3. Display the date of sale, amount, seller and flower according to the specified sale code.
4. Display a list of flowers and the variety for flowers with a height greater than the specified number or blooming.
Option 4
1. Display a list of medications with the specified indication for use.
2. Display a list of delivery dates for which more than the specified number of the drug of the same name was sold.
3. Display the delivery date, amount, full name of the manager from the supplier and the name of the medicine by receipt code greater than the specified number.
Option 5
2. Display a list of decommissioned equipment for the specified reason.
3. Display the date of receipt, name of the equipment, full name of the person responsible and the date of write-off for equipment written off during the specified period.
4. Display a list of equipment with a specified type or with a receipt date greater than a certain value
Option 6
1. Display a list of dishes with a weight greater than the specified number.
2. Display a list of products whose names contain the specified word fragment.
3. Display the volume of the product, the name of the dish, the name of the product with the dish code from the specified initial value to a certain final value.
4. Display the order of preparation of a dish and the name of a dish with the amount of carbohydrates greater than a certain value or the amount of calories greater than a specified value.
Option 7
1. Display a list of employees with the specified position.
3. Display the registration date, document type, full name of the registrar and name of the organization for documents registered during the specified period.
4. Display a list of registered documents with a specific document type or with a registration date greater than the specified value.
Option 8
1. Display a list of employees with the specified reason for dismissal.
3. Display the date of registration, reason for dismissal, full name of the employee for documents registered during the specified period.
Option 9
1. Display a list of employees who took leave of the specified type.
2. Display a list of documents with a registration date in the specified period.
3. Display the date of registration, type of leave, full name of the employee for documents registered during the specified period.
4. Display a list of registered documents with a document code in the specified range.
Option 10
1. Display a list of employees with the specified position.
2. Display a list of documents whose content contains the specified word fragment.
3. Display the registration date, document type, full name of the sender and name of the organization for documents registered during the specified period.
4. Display a list of registered documents with the specified document type or with a document code less than a certain value.
Option 11
1. Display a list of employees assigned to the specified position.
2. Display a list of documents with a registration date in the specified period.
3. Display the registration date, position, full name of the employee for documents registered during the specified period.
4. Display a list of registered documents with a document code in the specified range.
Option 12
3. Display a list of people who rented equipment and the number of their requests, sorted by the number of requests in descending order.
Option 13
1. Display a list of equipment with the specified type. 2. Display a list of equipment that has been written off by a specific employee.
3. Display the amount of decommissioned equipment, grouped by equipment type.
4. Display information about employees with a hire date greater than a certain date.
Option 14
1. Print a list of flowers with the specified leaf type.
2. Display a list of receipt codes for which flowers were sold for amounts greater than a certain value.
3. Display the receipt date, amount, name of the supplier and colors by a specific supplier code.
4. Display a list of flowers and the variety for flowers with a height greater than a certain number or blooming.
Option 15
1. Display a list of clients who checked into the rooms during the specified period.
2. Display the total amount of payments for rooms for each client.
3. Display the arrival date, room type, full name of clients registered during the specified period.
4. Display a list of registered clients in rooms of a certain type.
Option 16
1. Display a list of equipment with the specified type.
2. Display a list of equipment that was rented by a specific client.
3. Display a list of people who rented equipment and the number of their requests, sorted by the number of requests in descending order.
4. Display information about clients sorted by address.
Option 17
1. Display a list of valuables with a purchase price greater than a certain value or a warranty period greater than a specified number.
2. Display a list of locations of material assets whose names contain the specified word.
3. Display the sum of the value of values with a code in the specified range.
4. Display a list of financially responsible persons with the date of employment in the specified range.
Option 18
1. Display a list of repairs performed by a specific technician.
2. Display a list of work stages included in the work whose title contains the specified word.
3. Display the sum of the cost of repair work stages for work with a code in the specified range.
4. Display a list of foremen with the hiring date in the specified range.
Option 19
1. Display a list of medications with a specific indication.
2. Display a list of receipt numbers for which more than a certain number of drugs were sold.
3. Display the date of sale, amount, cashier’s name and medicine on the receipt with the specified number.
4. Display a list of medications and units of measurement for medications with a quantity in the package greater than the specified number or a drug code less than a certain value.
Option 20
1. Display a list of employees with the specified position.
2. Display a list of documents whose content contains the specified word fragment.
3. Display the registration date, document type, full name of the executor and the fact of execution for documents registered during the specified period.
4. Display a list of registered documents with the specified document type or document code in a certain range.
stored procedure is possible only if it is carried out in the context of the database where the procedure is located.Types of Stored Procedures
SQL Server has several types stored procedures.
- System stored procedures designed to perform various administrative actions. Almost all server administration activities are performed with their help. We can say that systemic 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 have the sp_ prefix, 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 full-fledged 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 while, 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 were created. When creating such a procedure, you must give it a name that begins with a single # character. Like all temporary objects, stored procedures of this type are automatically deleted when the user disconnects or the server is restarted or stopped. Global temporary stored procedures are available for any connections from a server that has the same procedure. To define it, just give it a name starting with the characters ## . These procedures are deleted when the server is restarted or stopped, or when the connection in the context in which they were created is closed.
Creating, modifying, and deleting stored procedures
Creation stored procedure involves solving the following problems:
- determining the type of created 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 the system database. This procedure will be available in the context of any local server database;
- planning access rights. While creating stored procedure it should be taken into account that it will have the same access rights to database objects as the user who created it;
- definition stored procedure parameters. Similar to 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 calls to other stored procedures.
Creating a new one and changing an existing one stored procedure done using the following command:
<определение_процедуры>::= (CREATE | ALTER ) procedure_name [;number] [(@parameter_name data_type ) [=default] ][,...n] AS sql_operator [...n]
Let's look at the parameters of this command.
Using the prefixes sp_ , # , ## , the created procedure can be defined as a system or temporary one. As you can see from the command syntax, it is not allowed to specify the name of the owner who will own the created procedure, as well as the name of the database where it should be located. Thus, in order to place the created stored procedure in a specific database, you must issue the CREATE PROCEDURE command in the context of that database. When turning from the body stored procedure shortened names can be used for objects of the same database, i.e. without specifying the database name. When you need to access objects located in other databases, specifying the database name is mandatory.
The number in the name is an identification number stored procedure, which uniquely identifies it in a group of procedures. For ease of management, procedures are logically of the same type stored procedures can be grouped by giving them the same name but different identification numbers.
To transfer input and output data in the created stored procedure parameters can be used, the names of which, like the names of local variables, must begin with the @ symbol. One stored procedure You can specify multiple parameters separated by commas. The body of a procedure should not use local variables whose names coincide with the names of the parameters of this procedure.
To determine the data type that the corresponding stored procedure parameter, any SQL data types are suitable, including user-defined ones. However, the CURSOR data type can only be used as output parameter stored procedure, i.e. specifying the OUTPUT keyword.
The presence of the OUTPUT keyword means that the corresponding parameter is intended to return data from stored procedure. However, this does not mean that the parameter is not suitable for passing values to stored procedure. Specifying the OUTPUT keyword instructs the server to exit stored procedure assign the current value of the parameter to the local variable that was specified when calling the procedure 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. The use of any expressions or constants that are allowed for regular parameters is not permitted.
The VARYING keyword is used in conjunction with
Procedure declaration
CREATE PROCEDURE [({IN|OUT|INOUT} [,…])]
[DYNAMIC RESULT SET ]
BEGIN [ATOMIC]
END
Keywords
. IN (Input) – input parameter
. OUT (Output) – output parameter
. INOUT – input and output, as well as a field (without parameters)
. DYNAMIC RESULT SET indicates that the procedure can open a specified number of cursors that will remain open after the procedure returns
Notes
It is not recommended to use many parameters in stored procedures (primarily large numbers and character strings) due to network and stack overload. In practice, existing dialects of Transact-SQL, PL/SQL and Informix have significant differences from the standard, both in the declaration and use of parameters, in the declaration of variables, and in the call of subroutines. Microsoft recommends using the following approximation to estimate the stored procedure cache size:
=(maximum number of concurrent users)*(size of largest execution plan)*1.25. Determining the size of the execution plan in pages can be done using the command: DBCC MEMUSAGE.
Calling a procedure
In many existing DBMSs, stored procedures are called using the operator:
EXECUTE PROCEDURE [(][)]
Note: Calls to stored procedures can be made from within an application, another stored procedure, or interactively.
Example of a procedure declaration
CREATE PROCEDURE Proc1 AS //declare the procedure
DECLARE Cur1 CURSOR FOR SELECT SName, City FROM SalesPeople WHERE Rating>200 //declare the cursor
OPEN Cur1 //open the cursor
FETCH NEXT FROM Cur1 //read data from the cursor
WHILE @@Fetch_Status=0
BEGIN
FETCH NEXT FROM Cur1
END
CLOSE Cur1 //close the cursor
DEALLOCATE Cur1
EXECUTE Proc1 //run the procedure
Polymorphism
Two subroutines with the same name can be created in the same schema if the parameters of the two subroutines are sufficiently different from each other that they can be distinguished. To distinguish between two routines with the same name in the same schema, each is given an alternative and unique name (specific name). Such a name may be specified explicitly when the subroutine is defined. When calling subroutines with several identical names, determining the required subroutine is carried out in several steps:
. Initially, all procedures with the specified name are defined, and if there are none, then all functions with the given name.
. Only those subroutines for which the user has the execution privilege (EXECUTE) are retained for further analysis.
. For them, those whose number of parameters corresponds to the number of call arguments are selected. The specified data types of the parameters and their positions are checked.
. If there is more than one subroutine left, then the one whose qualifying name is shorter is selected.
In practice, in Oracle polymorphism is supported for functions declared only in a package, DB@ - in different schemas, and in Sybase and MS SQL Server overloading is prohibited.
Deleting and changing procedures
To remove a procedure, use the operator:
To change a procedure, use the operator:
ALTER PROCEDURE [([{IN|OUT|INOUT}])]
BEGIN [ATOMIC]
END
Privileges to perform procedures
GRANT EXECUTE ON TO |PUBLIC [WITH GRANT OPTION]
System procedures
Many DBMSs (including SQL Server) have a certain set of built-in system stored procedures that you can use for your own purposes.
