Implementation of relationships in stored procedures. Creating stored procedures in microsoft sql server. Selecting one of the SQL stored procedures
Stored procedure (eng. stored procedure) is a named software object DB. There are several types of stored procedures in SQL Server.
System stored procedures (eng. system stored procedure) are supplied by the DBMS developers and are used to perform actions with the system catalog or obtain system information. Their names usually start with the "sp_" prefix. Stored procedures of all types are invoked with the EXECUTE command, which can be abbreviated as EXEC. For example, the sp_helplogins stored procedure, run without parameters, generates two reports on account names (eng. logins) and their corresponding users in each database (eng. users).
EXEC sp_helplogins;
To give an idea of the actions performed by using system stored procedures, in table. 10.6 provides some examples. There are over a thousand system stored procedures in SQL Server.
Table 10.6
SQL Server System Stored Procedure Examples
The user can create stored procedures in user databases and in databases for temporary objects. In the latter case, the stored procedure will 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 with "##" if it is a global one. The local temporary procedure can only be used within the connection in which it was created, the global one - and within other connections.
SQL Server programmable objects can be created using either Transact-SQL tools or assemblies (eng. assembly) in the Common Language Runtime (CRL) of the Microsoft .Net Framework. In this tutorial, only the first method will be considered.
Stored procedures are created using the CREATE PROCEDURE statement (can be abbreviated to PROC), the format of which is shown below:
CREATE (PROC I PROCEDURE) proc_name [; number]
[(gparameter data_type)
[“Default] |
[WITH
[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 so that the text becomes unreadable. At the same time, as noted in, the algorithm used is carried over from early versions SQL Server cannot be considered as a reliable security algorithm - there are utilities that allow you to quickly perform the reverse conversion.
The RECOMPILE option instructs the system to recompile the text each time the procedure is called. Normally, the procedure compiled at the first start is saved in the cache, which can improve performance.
EXECUTE AS defines the security context in which the procedure should be executed. Then one of the values f CALLER | SELF | OWNER | "user_name"). CALLER is the default 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 programmed object itself, but also for other database objects affected by it. EXECUTE AS SELF means using the context of the user creating or modifying the programmable object. OWNER indicates that the code will be executed in the context of the current owner of the procedure. If no owner is defined for it, then the owner of the schema to which it belongs is meant. EXECUTE AS "user_name" allows you to explicitly specify the username (in single quotes).
Parameters can be specified for the procedure. These are local variables used to pass values to the procedure. If a parameter is declared with the OUTPUT keyword (or abbreviated as OUT), it is an output parameter: the value specified in the procedure after its termination can be used by the program that called the procedure. The READONLY keyword means that the parameter value cannot be changed inside the stored procedure.
Parameters can be assigned values but default, which will be used if the parameter value is not specified explicitly when calling the procedure. Let's 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, and 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, the created stored procedure can be found in the Programmable DB Objects section (eng. Programmability) in the subsection for stored procedures (Figure 10.2).
When calling a procedure, you can use both variables and constants as input parameters. Let's look at two examples. In the first, the input parameters of the procedure are explicitly set by constants, for the output parameter in the call it is indicated keyword OUTPUT. In the second option, the value of the variable is used as the first input parameter, and for the second parameter, using the DEFAULT keyword, it is indicated that the default value should be used:
Rice. 10.2.
DECLARE @ with int;
EXEC summa 10.5, @ c OUTPUT;
PRINT 0c; - 15 will be displayed
DECLARE Gi int = 5;
- when calling, use the default value
EXEC summa Gi, DEFAULT, 0с OUTPUT;
PRINT 0c; - 5 will be displayed
Let us now consider an example analyzing the return code with which the procedure ends. Let it be necessary to calculate how many books in the Bookl table have been published in a given range of years. Moreover, if the initial year turned out to be more than the final 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). The CAST function used in the PRINT operator is used to convert the value of the integer variable Gres to a string type:
DECLARE 0ret int, Gres int
EXEC Gret = rownum 2004, 2002, Gres OUT;
IF 0ret = l PRINT "Starting year is greater than ending 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, you cannot create schemas, functions, triggers, procedures, and views from a stored procedure.
The following example illustrates both these capabilities and the scope for temporary objects. The stored procedure below checks for the existence of the # thab2 temporary table; if this table does not exist, then creates it. After that, the values of two columns are entered into the # Tab2 table, 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, we will create the temporary table # Thab2 used in it. Pay attention to the EXEC operator. In the previous examples, parameters were passed to the procedure "by position", but in this case, another format for passing parameters is used - "by name", the name of the parameter 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 be executed twice: the first time - inside the procedure, the second time - from the calling code fragment (marked with the comment "# 2").
Before the second call of the procedure, we will 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 comment "Xa 1") will display the data. SELECT "# 2" will result in an error because the temporary table created in the stored procedure has already been deleted from tempdb at the time the procedure returns.
You can drop a stored procedure using the DROP PROCEDURE statement. Its format is shown below. One operator can delete several stored procedures by listing them separated by commas:
DROP (PROC I PROCEDURE) (procedure) [
For example, let's delete the previously created this procedure summa:
DROP PROC summa;
You can make changes to an existing procedure (and in fact, override it) using the ALTER PROCEDURE statement (admissible
the abbreviation PROC). With the exception of the ALTER keyword, the format of the statement is similar to that of CREATE PROCEDURE. For example, let's change the dbo procedure. rownum by setting it an execute option in the security context of the owner:
ALTER PROC dbo.rownum (SFirsYear int,
SLastYear int, Sresult int OUTPUT)
WITH EXECUTE AS Owner is an installable 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 form a command and execute it on the database server. This task can also be solved using the EXEC operator. The example below fetches records from the Bookl table by the condition that the Year attribute is equal to the value set using a variable:
DECLARE 0y int = 2000;
EXEC ("SELECT * FROM dbo.Bookl WHERE =" [email protected]) ;
Execution of dynamically generated instructions creates prerequisites for the implementation of computer attacks such as "SQL injection" (eng. SQL injection). The essence of the attack is that the attacker injects its own SQL code into a dynamically generated query. This usually happens when the inline parameters are taken from the results of user input.
Let's change the previous example a little:
DECLARE 0y varchar (100);
SET 0y = "2OOO"; - we got this 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 such cases, it is recommended to use the sp_executcsql system stored procedure whenever possible, which allows you to control the type of parameters, which is one of the barriers to SQL injection. Without considering its format in detail, let's look at an example similar to the one presented earlier:
EXECUTE sp_executesql
N "SELECT * FROM dbo.Bookl WHERE = 0y",
The type of parameter used in the query is explicitly specified here, and SQL Server will monitor it during execution. The letter "N" in front of the quotes 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.
SQL - Lesson 15. Stored procedures. Part 1.
As a rule, when working with a database, we use the same queries, or a set of sequential queries. Stored procedures allow you to combine a sequence of requests and store them on the server. This is very handy tool, and now you will be convinced of this. Let's start with the syntax:CREATE PROCEDURE sp_name (parameters) begin end statements
Parameters are the data that we will pass to the procedure when it is called, and operators are the actual requests. Let's write our first procedure and see how convenient it is. In lesson 10, when we added new records to the shop database, we used a standard add query of the form:
INSERT INTO customers (name, email) VALUE ("Ivanov Sergey", " [email protected]");
Because We will use such a request every time we need to add a new customer, then it is quite appropriate to issue it in the form of a procedure:
CREATE PROCEDURE ins_cust (n CHAR (50), e CHAR (50)) begin insert into customers (name, email) value (n, e); end
Pay attention to how the parameters are set: you need to give a name to the parameter and indicate its type, and in the body of the procedure we already use the names of the parameters. One caveat. As you remember, the semicolon means the end of the request and sends it for execution, which is not acceptable in this case. Therefore, before writing the procedure, you must override the separator with; to "//" so that the request is not sent ahead of time. This is done using the DELIMITER // statement: 
Thus, we have indicated to the DBMS that the commands should now be executed after //. It should be remembered that the redefinition of the separator is carried out only for one session of work, i.e. the next time you work with MySql, the separator will again become a semicolon and will have to be redefined again if necessary. Now you can place the procedure:
CREATE PROCEDURE ins_cust (n CHAR (50), e CHAR (50)) begin insert into customers (name, email) value (n, e); end //
So, the procedure has been created. Now, when we need to enter a new customer, we just need to call it by specifying the necessary parameters. A CALL statement is used to call a stored procedure, followed by the name of the procedure and its parameters. Let's add a new customer to our customers table:
call ins_cust ("Valery Sychov", " [email protected]")//
Agree that this is much easier than writing every time full request... Let's check if the procedure works by looking to see if a new customer has appeared in the customers table:
Appeared, the procedure works, and will always work until we delete it using the operator DROP PROCEDURE procedure_name.
As mentioned at the beginning of the lesson, procedures allow you to combine a sequence of requests. Let's see how this is done. Remember in lesson 11 we wanted to know how much the supplier "Printing House" brought us the goods? To do this, we had to use nested queries, joins, calculated columns and views. And if we want to know how much the other supplier brought us the goods? You will have to compose new queries, associations, etc. It's easier to write a stored procedure for this action once.
It would seem that the easiest way is to take the view and request to it already written in Lesson 11, combine it into a stored procedure and make the vendor identifier (id_vendor) an input parameter, like this:
CREATE PROCEDURE sum_vendor (i INT) begin CREATE VIEW report_vendor AS SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity * prices.price AS summa FROM magazine_incoming, prices WHEREcoming_incoming.id_product = prices.id_product = prices SELECT id_incoming FROM incoming WHERE id_vendor = i); SELECT SUM (summa) FROM report_vendor; end //
But the procedure won't work that way. The thing is that no parameters can be used in views... Therefore, we will have to slightly change the sequence of requests. First, we will create a view that will output the vendor id (id_vendor), product id (id_product), quantity, price, and summa from three tables Deliveries (incoming), Magazine_incoming, Prices ( prices):
CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity * prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product = incoming.id_product AND magazine_incoming = prices .id_incoming;
And then we will create a request that will sum up the supply amounts of the supplier we are interested in, for example, with id_vendor = 2:
Now we can combine these two requests into a stored procedure, where the input parameter will be the vendor identifier (id_vendor), which will be substituted into the second request, but not into the view:
CREATE PROCEDURE sum_vendor (i INT) begin CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity * prices.price AS summa FROM incoming, magazine_incoming, WHERE magazine_incoming. prices = prices .id_product AND magazine_incoming.id_incoming = incoming.id_incoming; SELECT SUM (summa) FROM report_vendor WHERE id_vendor = i; end //
Let's check the operation of the procedure, with different input parameters:
As you can see, the procedure fires once and then throws an error, telling us that the report_vendor view is already in the database. This is because the first time a procedure is called, it creates a view. When accessing a second time, it tries to create a view again, but it already exists, which is why an error appears. There are two possible ways to avoid this.
The first is to take the idea out of the procedure. That is, we will create a view once, and the procedure will only refer to it, but not create it. He will not forget to delete the already created procedure and view in advance:
DROP PROCEDURE sum_vendor // DROP VIEW report_vendor // CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity * prices.price. AS summa FROM incoming WHER, magazine_incoming, magazine prices = prices.id_product AND magazine_incoming.id_incoming = incoming.id_incoming // CREATE PROCEDURE sum_vendor (i INT) begin SELECT SUM (summa) FROM report_vendor WHERE id_vendor = i; end //
Checking the work:
call sum_vendor (1) // call sum_vendor (2) // call sum_vendor (3) //
The second option is to add a command right in the procedure that will delete the view, if it exists:
CREATE PROCEDURE sum_vendor (i INT) begin DROP VIEW IF EXISTS report_vendor; CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity * prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product = incoming.id_product AND magazine_comingid prices .id_incoming; SELECT SUM (summa) FROM report_vendor WHERE id_vendor = i; end //
Remember to remove the sum_vendor routine before using this option, and then test it: 
As you can see, complex queries or their sequence is really easier to form once into a stored procedure, and then just refer to it, specifying the necessary parameters. This greatly reduces the code and makes working with queries more logical.
purpose of work- learn how to create and use stored procedures on the database server.
1. Working out all the examples, analyzing the results of their execution in the SQL Server Management Studio utility. Checking the presence of created procedures in the current database.
2. Completion of all examples and tasks in the course of laboratory work.
3. Implementation of individual assignments by options.
Explanations for the work
To master the programming of stored procedures, we use an example of a database called DB_Books, which was created in laboratory work No. 1. When performing examples and tasks, pay attention to the correspondence of the names of the database, tables and other objects of the project.
Stored procedures are a set of commands consisting of one or more SQL statements or functions and saved in the database in a compiled form.
Stored procedure types
System stored procedures are designed to perform various administrative actions. Almost all server administration actions are performed with their help. We can say that system stored procedures are the interface for working 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 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 in which they are created. When creating such a procedure, it must be given a name starting with one # 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 server connection that has the same procedure. To define it, you just need to give it a name starting with ## symbols. These procedures are deleted when the server is restarted or stopped, or when the connection in the context of which they were created is closed.
Creating, modifying stored procedures
Creating a stored procedure involves solving the following problems: planning access rights. When creating a stored procedure, keep in mind that it will have the same access rights to database objects as the user who created it; defining parameters of a stored procedure, stored procedures can have input and output parameters; stored procedure code development. Procedure code can contain any sequence of SQL commands, including calls to other stored procedures.
The syntax for the operator to create a new or modify an existing stored procedure in MS SQL Server notation:
(CREATE | ALTER) PROC [EDURE] procedure_name [; number] [(@ parameter_name datatype) [VARYING] [= DEFAULT] [OUTPUT]] [, ... n] [WITH (RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION)] [FOR REPLICATION] AS sql_operator [... n]Let's consider the parameters of this command.
Using the prefixes sp_, #, ##, the created procedure can be defined as system or temporary. 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 that you are creating in a specific database, you must run the CREATE PROCEDURE command in the context of that database. When referring to objects of the same database from the body of a stored procedure, abbreviated names can be used, that is, without specifying the database name. When you need to refer to objects located in other databases, specifying the name of the database is required.
Parameter names must begin with the @ character to pass input and output data to the generated stored procedure. Multiple parameters can be specified in a single stored procedure, separated by commas. The body of a procedure should not use local variables whose names are the same as the names of the parameters of this procedure. Any type is suitable for defining the data type of the stored procedure parameters. SQL data including user-defined. However, the CURSOR data type can only be used as an output parameter of a stored procedure, i.e. specifying the OUTPUT keyword.
The presence of the OUTPUT keyword indicates that the corresponding parameter is for returning 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 the 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 the OUTPUT keyword is specified, the value of the corresponding parameter when calling a procedure can only be set using a local variable. You are not allowed to use any expressions or constants that are valid for normal parameters. The VARYING keyword is used in conjunction with the OUTPUT parameter of type CURSOR. It specifies that the output parameter will be the result set.
The DEFAULT keyword is a value that will accept the corresponding default parameter. Thus, when calling a procedure, you do not need to explicitly specify the value of the corresponding parameter.
Since the server caches the query execution plan and the compiled code, the next call to the procedure will use the ready-made values. However, in some cases, you still need to recompile the procedure code. Specifying the RECOMPILE keyword instructs the system to generate an execution plan for the stored procedure each time it is called.
The FOR REPLICATION parameter is required when replicating data and including the generated 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 authoring algorithms that implement the stored procedure. The AS keyword is placed at the beginning of the actual body of the stored procedure. The body of a procedure can use almost all SQL commands, declare transactions, acquire locks, and call other stored procedures. You can exit the stored procedure with the RETURN command.
Deleting a stored procedure
DROP PROCEDURE (procedure_name) [, ... n]
Stored Procedure Execution
To execute the stored procedure, use the command: [[EXEC [UTE] procedure_name [; number] [[@ parameter_name =] (value | @ variable_name) [OUTPUT] | [DEFAULT]] [, ... n]
If the call to the stored procedure is not the only command in the package, then the EXECUTE command must be present. Moreover, this command is required to call a procedure from the body of another procedure or trigger.
The use of the OUTPUT keyword in a procedure call is permitted 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. Of course, the specified DEFAULT word is allowed only for those parameters for which a default value is defined.
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 supply values for the parameters in the same order as they were listed when the procedure was created. You cannot assign a default value to a parameter by simply skipping it during enumeration. If you need to omit parameters for which a default value is defined, it is sufficient to explicitly specify the parameter names when calling the stored procedure. Moreover, in this way you can list the parameters and their values in any order.
Note that when calling a procedure, either parameter names with values are specified, or only values without a parameter name. Combining them is not allowed.
Using RETURN in a Stored Procedure
Allows you to exit the procedure at any point according to the specified condition, and also allows you to transmit 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 GOAssignment 2... Create this procedure in the Stored Procedures section of the DB_Books database using the SQL server Management Studio utility. Run it with 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 with 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 with the set of commands:
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 2Task 5. Create this procedure in the Stored Procedures section of the DB_Books database using the SQL server Management Studio utility. Start it with the commands:
DECLARE @return_status INT EXEC @return_status = checkname 1 SELECT "Return Status" = @return_statusAn 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 * 2Task 6. Create this procedure in the Stored Procedures section of the DB_Books database using the SQL server Management Studio utility. Run it with the command
EXEC update_procAn example of a procedure with an input parameter to get all the 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 (by default, 2 times):
CREATE PROC update_proc @p INT = 2 AS UPDATE Purchases SET Code_purchase = Code_purchase * @pThe 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. Start it with 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 made during the specified period:
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. Create this procedure in the Stored Procedures section of the DB_Books database using the SQL server Management Studio utility. Start it with the commands:
DECLARE @ c2 INT EXEC count_purchases '01 - jun-2006 ’, ’01 - jul-2006’, @ c2 OUTPUT SELECT @ c2Options for assignments for laboratory work No. 4
General Provisions. In SQL Server Management Studio utility create new page for the code (button "Create request"). Programmatically make the created DB_Books active using the Use statement. Create stored procedures using Create procedure statements, and define procedure names yourself. Each procedure will execute one SQL statement that was performed in the second lab. Moreover, the code of the SQL queries must be changed so that it would be possible to transfer the values of the fields by which the search is carried out in them.
For example, the original assignment and query in lab # 2:
/ * Select from the directory of suppliers (table Deliveries) the names of companies, telephones and TIN (fields Name_company, Phone and INN), which have the company name (field Name_company) “OJSC MIR”.
SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = "OJSC MIR"* / –In this work, a 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 "OJSC MIR"Task list
Create a new program in the SQL Server Management Studio utility. Programmatically make an individual database active, created in laboratory work No. 1, using the Use operator. Create stored procedures using Create procedure statements, and define procedure names yourself. Each procedure will execute one SQL query, which are presented as separate tasks by options.
Option 1
1. Display a list of employees who have at least one child.
2. Display a list of children who were given 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 repaired devices and the total cost of repairs from the specified master.
3. Display the list of instrument owners and the number of their calls, sorted by the number of calls in descending order.
4. Display information about masters with a rank greater than the specified number or with the date of hiring less than the specified date.
Option 3
2. Display a list of sales codes for which flowers were sold for an amount exceeding the specified number.
3. Display the date of sale, amount, seller and flower by the specified code sales.
4. List flowers and a variety for flowers with a height greater than the specified number or flowering.
Option 4
1. Display a list of drugs with the indicated indication for use.
2. Display a list of delivery dates for which more than the specified number of the drug of the same name has been sold.
3. Display the delivery date, amount, full name of the manager from the supplier and the name of the drug 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, the name of the equipment, the name of the person in charge and the date of write-off for equipment written off during the specified period.
4. Display a list of equipment with the specified type or with the date of receipt 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, the name of which contains the specified word fragment.
3. Output 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 the specified final value.
4. Display the order of preparation of the dish and the name of the dish with the amount of carbohydrates greater than a certain value or the number of calories greater than the specified value.
Option 7
1. Display a list of employees with the specified position.
3. Display the date of registration, type of document, full name of the registrar and the name of the organization for documents registered during the specified period.
4. Display a list of registered documents with a certain type of document or with a registration date greater than the specified value.
Option 8
1. Display a list of employees with the specified reason layoffs.
3. Display the date of registration, the reason for dismissal, the 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 the registration date in the specified period.
3. Display the date of registration, type of vacation, 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 containing the specified word fragment.
3. Display the date of registration, type of document, full name of the sender and the name of the organization for documents registered in 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 the registration date in the specified period.
3. Display the date of registration, 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 have rented equipment and the number of their calls, sorted by the number of calls in descending order.
Option 13
1. Display a list of equipment with the specified type. 2. Display a list of equipment that was written off by a certain employee.
3. Display the amount of decommissioned equipment, grouped by equipment type.
4. Display information about employees with the date of hiring more than a certain date.
Option 14
1. List flowers with the specified leaf type.
2. Display a list of receipt codes for which flowers are sold for amounts greater than a certain value.
3. Display the date of receipt, the amount, the name of the supplier and colors for a specific supplier code.
4. List flowers and a variety for flowers with a height greater than a certain number or flowering.
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 check-in date, room type, full name of clients registered during the specified period.
4. Display a list of registered customers in rooms of a certain type.
Option 16
1. Display a list of equipment with the specified type.
2. Display a list of equipment rented by a specific client.
3. Display a list of people who have rented equipment and the number of their calls, sorted by the number of calls in descending order.
4. Display information about clients sorted by addresses.
Option 17
1. Display a list of values with a purchase value greater than a certain value or a warranty period greater than a specified number.
2. Display a list of locations of material assets, in the name of which the specified word is found.
3. Display the sum of the value of the valuables 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 master.
2. List the stages of work included in the work, in the title of which the specified word occurs.
3. Output the sum of the cost of the stages of repair work for work with a code in the specified range.
4. Display a list of masters with the date of hiring within the specified range.
Option 19
1. Display a list of drugs with a specific indication.
2. Display a list of check numbers for which more than a certain number of drugs have been sold.
3. Display the date of sale, amount, full name of the cashier and medicine on the receipt with the specified number.
4. Display a list of drugs and units of measurement for drugs with a package quantity greater than a 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 containing the specified word fragment.
3. Display the date of registration, type of document, full name of the contractor and the fact of execution for documents registered during the specified period.
4. Display a list of registered documents with the specified type of document or with a document code in a specific range.
There are many new features in MySQL 5, one of the most significant of which is the creation of stored procedures. In this tutorial, I'll talk about what they are and how they can make your life easier.
Introduction
A stored procedure is a way to encapsulate repetitive actions. Stored procedures can be used to declare variables, manipulate data streams, and use other programming techniques.
The reason for their creation is clear and confirmed by their frequent use. On the other hand, if you talk to those who work with them irregularly, then opinions will split into two completely opposite flanks. Don't forget this.
Per
- Sharing logic with other applications. Stored procedures encapsulate functionality; this ensures consistency in data access and management between different applications.
- Isolation of users from database tables. This allows you to give access to stored procedures, but not to the actual table data.
- Provides a defense mechanism. As per the previous point, if you can only access the 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.
- There is a lot to learn. You will need to learn the syntax of MySQL expressions to write your stored procedures.
- You duplicate your application logic in two places: server code and code for stored procedures, thereby complicating the process of manipulating data.
- Migration from one DBMS to another (DB2, SQL Server, etc.) can lead to problems.
The tool I'm working in is called the MySQL Query Browser, and it's pretty standard for interacting with databases. Tool command line MySQL is another excellent choice. I am telling you about this for the reason that everyone's favorite phpMyAdmin does not support the execution of stored procedures.
By the way, I am using a rudimentary table structure to make it easier for you to understand this topic. After all, I'm talking about stored procedures, and they are complex enough to delve into the cumbersome structure of tables.
Step 1: set the limiter
A delimiter is a character or string of characters that is used to indicate to the MySQL client that you have finished writing an SQL statement. For ages, the semicolon has been the delimiter. However, problems can arise because there can be multiple expressions in a stored procedure, each of which must end with a semicolon. In this tutorial, I use the string “//” as a delimiter.
Step 2: How to work with stored procedures
Creating a stored procedure
DELIMITER // CREATE PROCEDURE `p2` () LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT" A procedure "BEGIN SELECT" Hello World! "; END //
The first piece of code creates a stored procedure. The next one contains optional parameters. Then comes the name and, finally, the body of the procedure itself.
Stored procedure names are case sensitive. You also cannot create multiple procedures with the same name. There can be no expressions inside a stored procedure that modify the database itself.
4 characteristics of a stored procedure:
- Language: For portability purposes, SQL is specified by default.
- 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 is NOT DETERMINISTIC.
- SQL Security: During the call, the user's rights are checked. INVOKER is the user calling the stored procedure. DEFINER is the “creator” of the procedure. The default is DEFINER.
- Comment: for documentation purposes, the default is ""
Stored Procedure Call
To call a stored procedure, type the CALL keyword followed by the name of the procedure, followed by the parameters (variables or values) in parentheses. The parentheses are required.
CALL stored_procedure_name (param1, param2, ....) CALL procedure1 (10, "string parameter", @parameter_var);
Modifying a stored procedure
MySQL provides an ALTER PROCEDURE statement for modifying procedures, but it is suitable for changing only a few characteristics. If you need to change the parameters or body of the procedure, you must delete and re-create it.
Deleting a stored procedure
DROP PROCEDURE IF EXISTS p2;
This is a simple command. The IF EXISTS expression catches an error if such a procedure does not exist.
Step 3: Parameters
Let's see how you can pass parameters to a stored procedure.
- 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 (incoming).
- CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): one return parameter.
- CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): one parameter, both entering and returning.
Naturally, you can specify several parameters of different types.
Example IN parameter
DELIMITER // CREATE PROCEDURE `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS result; END //
OUT parameter example
DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR (100)) BEGIN SET var1 =" This is a test "; END //
Example of INOUT parameter
DELIMITER // CREATE PROCEDURE `proc_INOUT` (OUT var1 INT) BEGIN SET var1 = var1 * 2; END //
Step 4: variables
Now I will teach you how to create variables and store them inside procedures. You must declare them explicitly at the beginning of the BEGIN / END block, along with their data types. Once you have declared a variable, you can use it in the same place as session variables, literals, or column names.
The syntax for declaring a variable looks like this:
DECLARE varname DATA-TYPE DEFAULT defaultvalue;
Let's declare a few variables:
DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR (50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT;
Working with variables
Once you have declared a variable, you can set a value to it using the SET or SELECT commands:
DELIMITER // CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR (20)) BEGIN DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR (50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT; INSERT INTO table1 VALUES (a); SET str = "I am a string"; SELECT CONCAT (str, paramstr), today FROM table2 WHERE b> = 5; END //
Step 5: Flow Control Structures
MySQL supports IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs for flow control within a stored procedure. We'll take a look at how to use IF, CASE and WHILE as they are the most commonly used ones.
IF construction
With the help of the IF construct, we can perform tasks containing conditions:
DELIMITER // CREATE PROCEDURE `proc_IF` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; IF variable1 = 0 THEN SELECT variable1; END IF; IF param1 = 0 THEN SELECT "Parameter value = 0"; ELSE SELECT "Parameter value<>0 "; END IF; END //
CASE construction
CASE is another method of checking conditions and choosing the right solution. This is a great way to replace many IF constructs. The construct can be described in two ways, providing flexibility in managing multiple conditionals.
DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE variable1 WHEN 0 THEN INSERT INTO table1 VALUES (param1); WHEN 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); END CASE; END //
DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE WHEN variable1 = 0 THEN INSERT INTO table1 VALUES (param1); WHEN variable1 = 1 THEN INSERT INTO table1 VALUES (variable1); ELSE INSERT INTO table1 VALUES (99); END CASE; END //
WHILE construction
Technically, there are three kinds of loops: WHILE loop, LOOP loop, and REPEAT loop. You can also organize a loop using Darth Vader's programming technique: GOTO expressions. Here's an example of a loop:
DELIMITER // CREATE PROCEDURE `proc_WHILE` (IN param1 INT) BEGIN DECLARE variable1, variable2 INT; SET variable1 = 0; WHILE variable1< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //
Step 6: cursors
Cursors are used to traverse the set of rows returned by a query and also process each row.
MySQL supports cursors in stored procedures. Here's a quick syntax for creating and using a cursor.
DECLARE cursor-name CURSOR FOR SELECT ...; / * Declaring a cursor and filling it in * / DECLARE CONTINUE HANDLER FOR NOT FOUND / * What to do when there are no more records * / OPEN cursor-name; / * Open cursor * / FETCH cursor-name INTO variable [, variable]; / * Assign the value to the variable equal to the current value of the column * / CLOSE cursor-name; / * Close cursor * /
In this example, we'll do some simple cursor operations:
DELIMITER // CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 CURSOR FOR SELECT col1 FROM table1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur1; SET b = 0; SET c = 0; WHILE b = 0 DO FETCH cur1 INTO a; IF b = 0 THEN SET c = c + a; END IF; END WHILE; CLOSE cur1; SET param1 = c; END //
Cursors have three properties that you need to understand to avoid getting unexpected results:
- Insensitive: once opened, the cursor will not reflect later changes to the table. In reality, MySQL does not guarantee that the cursor will be updated, so do not hope for that.
- Read-only: Cursors cannot be modified.
- No rewind: the cursor can only move in one direction - forward, you cannot skip lines without selecting them.
Conclusion
In this tutorial, I introduced you to the basics of working with stored procedures and some of the specific properties associated with it. Of course, you will need to deepen your knowledge in areas such as security, SQL expressions, and optimization before becoming a true MySQL procedure guru.
You must calculate the benefits of using stored procedures in your specific application, and only then create only the necessary procedures. In general, I use procedures; in my opinion, they should be implemented in projects due to their security, code maintenance and overall design... Also, keep in mind that there is still work in progress on MySQL procedures. Expect functional improvements and enhancements. Please do not hesitate to share your opinions.
Include in your procedures the line - SET NOCOUNT ON:
With each DML statement, SQL server carefully returns us a message containing the number of processed records. This information may be useful to us during code debugging, but afterwards it will be completely useless. By prescribing SET NOCOUNT ON, we disable this function. For stored procedures containing multiple expressions or \ and loops this action can give a significant increase in performance, because the amount of traffic will be significantly reduced.
Transact-SQL
Use the schema name with the object name:
Well, here 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 --It's good to do this - Instead of SELECT * FROM MyTable --It's bad to do that --Calling EXEC dbo.MyProc - Good again --Instead of EXEC MyProc --Bad!
Do not use the "sp_" prefix in the name of your stored procedures:
If our procedure name begins with "sp_", SQL Server will look in its master database first. The point is that this prefix is used for the server's personal internal stored procedures. Therefore, its use can lead to additional costs and even an incorrect result 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 a record in another table, we use the IF EXISTS statement. This expression returns true if at least one value is returned from the inner expression, it does not matter "1", all columns or a table. The returned data, in principle, is not used in any way. Thus, to compress traffic during data transmission, it is more logical to use "1", as shown below.
