Ms sql server procedures. Stored procedures. An example of creating a procedure with input parameters
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 the specified number of cursors, which will remain open after returning from the procedure
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, in the existing dialects of Transact-SQL, PL / SQL and Informix, there is a significant difference from the standard, both in the declaration and use of parameters, declaration of variables, and in the call of subroutines. Microsoft recommends using the following approximation to estimate the cache size of stored procedures:
\u003d (maximum number of concurrent users) * (size of the largest execution plan) * 1.25. Determining the size of the execution plan in pages can be done with the command: DBCC MEMUSAGE.
Procedure call
In many existing DBMSs, stored procedures are called using the operator:
EXECUTE PROCEDURE [(][)]
Note: Stored procedure calls can be made from an application, another stored procedure, or interactively.
Example procedure declaration
CREATE PROCEDURE Proc1 AS // declare a procedure
DECLARE Cur1 CURSOR FOR SELECT SName, City FROM SalesPeople WHERE Rating\u003e 200 // declare the cursor
OPEN Cur1 // open cursor
FETCH NEXT FROM Cur1 // read data from the cursor
WHILE @@ Fetch_Status \u003d 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 so different from each other that they can be distinguished. In order to distinguish between two subroutines with the same name in the same schema, each of them is given an alternative and unique name (specific name). Such a name can be explicitly specified when a subroutine is defined. When calling subroutines with several identical names, the definition of the required subroutine is carried out in several steps:
... Initially, all procedures with specified name, and if there are none, then all functions with the given name.
... For further analysis, only those subroutines are left in relation to which given user has the execute privilege (EXECUTE).
... For them, those are selected for which the number of parameters corresponds to the number of call arguments. The specified data types are checked for parameters and their positions.
... If there is more than one subroutine left, the one with the shorter qualification name is selected.
In practice, in Oracle, polymorphism is supported for functions declared only in a package, [email protected] - in different schemes, and overloading is prohibited in Sybase and MS SQL Server.
Removing and changing procedures
To delete a procedure, use the operator:
To change the procedure, use the operator:
ALTER PROCEDURE [([{IN | OUT | INOUT}])]
BEGIN [ATOMIC]
END
Procedure Privileges
GRANT EXECUTE ON TO |PUBLIC [WITH GRANT OPTION]
System procedures
Many DBMSs (including SQL Server) have a specific set of built-in system stored procedures that you can use for your own purposes.
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 a very handy tool, and now you will see it. 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 specify its type, and in the body of the procedure we already use the names of the parameters. One nuance. 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 delimiter is redefined only for one session, 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 we 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 a full request every time. 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 the request to it already written in Lesson 11, combine it into a stored procedure and make the vendor ID (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 \u003d prices.id_product \u003d prices SELECT id_incoming FROM incoming WHERE id_vendor \u003d i); SELECT SUM (summa) FROM report_vendor; end //
But that won't work. 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 \u003d incoming.id_product AND magazine_comingid 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 \u003d 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 \u003d prices .id_product AND magazine_incoming.id_incoming \u003d incoming.id_incoming; SELECT SUM (summa) FROM report_vendor WHERE id_vendor \u003d 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 options 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:
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 \u003d prices.id_product AND magazine_incoming.id_incoming \u003d incoming.id_incoming // CREATE PROCEDURE sum_vendor (i INT) begin SELECT SUM (summa) FROM report_vendor WHERE id_vendor \u003d 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 \u003d incoming.id_product AND magazine_incoming \u003d prices .id_incoming; SELECT SUM (summa) FROM report_vendor WHERE id_vendor \u003d 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 code and makes working with queries more logical.
Stored procedure (eng. stored procedure) is a named database program object. There are several types of stored procedures in SQL Server.
System stored procedures (eng. system stored procedure) are supplied by 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 to EXEC. For example, the sp_helplogins stored procedure, run without parameters, generates two reports on account names (eng. logins) and the corresponding users in each database (eng. users).
EXEC sp_helplogins;
To give an idea of \u200b\u200bthe 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 is able to 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 transformed so 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 as a reliable protection 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 \u200b\u200bf 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 DB objects affected by it. EXECUTE AS SELF means using the user context 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 \u200b\u200bto the procedure. If a parameter is declared with the OUTPUT keyword (or abbreviated OUT), it is an output parameter: the value specified in the procedure after its end 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 \u200b\u200bbut 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 \u003d 0,
© result int OUTPUT) AS
SET @ result \u003d 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 database 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; 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 for the second parameter using keyword DEFAULT indicates that the default value should be used:
Figure: 10.2.
DECLARE @ with int;
EXEC summa 10.5, @ c OUTPUT;
PRINT 0c; - 15 will be displayed
DECLARE Gi int \u003d 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, 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\u003e 0LastYear RETURN 1
SET @ result \u003d (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 \u003d rownum 2004, 2002, Gres OUT;
IF 0ret \u003d 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 \u200b\u200bof two columns are entered into the table # Tab2, and the contents of the table are displayed 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 of 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 parameter name and its value are explicitly indicated:
CREATE TABLE dbo. # Tab2 (id int, name varchar (30));
EXEC My_Procl 0name \u003d "lvan", 0id \u003d 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 # Thab2. Then the temporary table of the same name will be created from the stored procedure:
DROP TABLE dbo. # Tab2;
EXEC My_Procl 0name \u003d "Ivan", 0id \u003d 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 when 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 remove the previously created summa procedure:
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 almost identical 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 - installable option
IF 0FirsYear\u003e 0LastYear RETURN 1 ELSE BEGIN
SET 0result \u003d (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 based on the condition that the Year attribute is equal to the value specified using a variable:
DECLARE 0y int \u003d 2000;
EXEC ("SELECT * FROM dbo.Bookl WHERE \u003d" [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 substituted parameters are taken from the results of user input.
Let's change the previous example a little:
DECLARE 0y varchar (100);
SET 0y \u003d "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 \u003d "2000; DELETE FROM dbo.Book2"; - injection
EXEC ("SELECT * FROM dbo.Book2 WHERE \u003d" + 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 \u003d 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 stored procedures are an executable program module that can be stored as various objects. In other words, it is an object that contains SQL statements. These stored procedures can be executed in the application client to get good performance. In addition, such objects are often called from other scripts or even from some other section.
Introduction
Many people think that they are similar to different procedures (respectively, except for MS SQL). Perhaps this is true. They have similar parameters, they can give similar values. Moreover, in a number of cases they touch. For example, they can be combined with DDL and DML databases, and user functions (codenamed UDF).
In reality, SQL stored procedures have a wide range of advantages that set them apart from similar processes. Security, programming variability, productivity - all this attracts users working with databases more and more. The peak of the popularity of procedures came in 2005-2010, when a program from Microsoft called "SQL Server Management Studio" was released. With its help, working with databases has become much easier, more practical and more convenient. From year to year this was gaining popularity among programmers. Today, it is an absolutely familiar program, which for users "communicating" with databases, is on a par with Excel.
When a procedure is called, it is instantly processed by the server itself without unnecessary processes and user intervention. After that, you can carry out any deletion, execution, change. The DDL-operator is responsible for all this, which alone performs the most complex operations for processing objects. And all this happens very quickly, and the server is not actually loaded. Such speed and performance allow very fast transfer of large amounts of information from the user to the server and vice versa.
To implement this technology for working with information, there are several programming languages. These include, for example, PL / SQL from Oracle, PSQL in InterBase and Firebird systems, as well as the classic "Microsoft" Transact-SQL. All of them are designed for creating and executing stored procedures, which allows large database processors to use their own algorithms. It is also necessary so that those who manage such information can protect all objects from unauthorized access by third parties and, accordingly, the creation, modification or deletion of certain data.
Productivity
These database objects can be programmed in a variety of ways. This allows users to choose the type of method used that is most appropriate, which saves time and effort. In addition, the procedure is handled by itself, which avoids the huge time spent exchanging between the server and the user. Also, the module can be reprogrammed and changed in the desired direction at absolutely any time. It is especially worth noting the speed with which the SQL stored procedure is launched: this process is faster than others, similar to it, which makes it convenient and versatile.
Safety
This type of information processing differs from similar processes in that it guarantees increased security. This is ensured due to the fact that access of other users to procedures can be completely and completely excluded. This will allow the administrator to conduct operations with them on his own, without fear of interception of information or unauthorized access to the database.
Data transfer
The relationship between the SQL stored procedure and the client application is through the use of parameters and return values. The latter does not need to pass data to the stored procedure, but this information (mainly at the user's request) is processed for SQL. After the stored procedure has completed its work, it sends data packets back (but, again, optionally) to the calling application using various methods that can be used to both call the SQL stored procedure and return, for example:
Passing data using a parameter of type Output;
Transferring data using a return statement;
Passing data using a select operator.
And now let's figure out what this process looks like from the inside.
1. Creating an EXEC Stored Procedure in SQL
You can create a procedure in MS SQL (Managment Studio). After the procedure is created, it will be listed in a programmable database node, in which the creation procedure is performed by the operator. SQL stored procedures use an EXEC process to execute, which contains the name of the object itself.
When a procedure is created, its name appears first, after which one or more parameters assigned to it are produced. Parameters can be optional. After the parameter (s), that is, the body of the procedure, are written, you need to perform some necessary operations.
The fact is that the body can have local variables located in it, and these variables are also local to procedures. In other words, they can only be viewed within the body of a Microsoft SQL Server procedure. Stored procedures are considered local in this case.
Thus, to create a procedure, we need the name of the procedure and at least one parameter as the body of the procedure. Note that a great option in this case is to create and execute a procedure named schema in the classifier.
The body of the procedure can be of any kind, for example, such as creating a table, inserting one or more rows of a table, setting the type and nature of the database, and so on. However, the body of the procedure restricts the execution of certain operations in it. Some of the important limitations are listed below:
The body should not create any other stored procedure;
The body must not create a false impression of the object;
The body shouldn't create any triggers.
2. Setting a variable in the body of the procedure
You can make variables local to the procedure body, and then they will be located exclusively inside the procedure body. It is good practice to create variables at the beginning of the stored procedure body. But you can also set variables anywhere in the body of this object.
Sometimes you will notice that several variables are set on one line, and each variable parameter is separated by a comma. Also note that the variable is prefixed with @. In the body of the procedure, you can set the variable wherever you want. For example, @ NAME1 can be declared towards the end of the procedure body. In order to assign a value to a declared variable, a set of personal data is used. Unlike a situation where more than one variable is declared on one line, in such a situation only one set of personal data is used.
Often users ask the question: "How to assign multiple values \u200b\u200bin one statement in the body of a procedure?" Well. An interesting question, but it's much easier to do than you think. Answer: using pairs such as "Select Var \u003d value". You can use these pairs by separating them with a comma.
Through a wide variety of examples, people show how to create a simple stored procedure and execute it. However, a procedure can take parameters such that the calling process will have values \u200b\u200bclose to it (but not always). If they coincide, then the corresponding processes begin inside the body. For example, if you create a procedure that will accept the city and region from the caller and return data about how many authors belong to the corresponding city and region. The routine will query the tables of the authors of the database, for example Pubs, to do this author count. To get these databases, for example, Google downloads the SQL script from the SQL2005 page.
In the previous example, the procedure takes two parameters, which on english language will be conventionally named @State and @City. The data type corresponds to the type defined in the application. The body of the procedure has internal variables @TotalAuthors (total authors), and this variable is used to display their number. Next, the query selection section appears, which calculates everything. Finally, the calculated value is displayed in the output window using a print statement.
How to execute a stored procedure in SQL
There are two ways to complete the procedure. The first path shows, by passing parameters, how a comma-separated list is executed after the procedure name. Let's say we have two values \u200b\u200b(as in the previous example). These values \u200b\u200bare collected using the procedure parameters @State and @City. In this way of passing parameters, order is important. This method is called ordinal passing of arguments. In the second method, the parameters are already directly assigned, in which case the order is not important. This second method is known as passing named arguments.
The procedure may deviate somewhat from the typical one. Everything is the same as in the previous example, but here the parameters are shifted. That is, @City is stored first and @State is stored next to the default. The default parameter is usually highlighted separately. SQL stored procedures pass as just parameters. In this case, provided, the "UT" parameter replaces the default "CA". In the second execution, only one argument value is passed for the @City parameter, and the @State parameter takes the default value "CA". Experienced programmers advise that all default variables be near the end of the parameter list. Otherwise, execution is not possible, and then you have to work with passing named arguments, which is longer and more complicated.
4. SQL Server Stored Procedures: Return Methods
There are three important ways sending data in the called stored procedure. They are listed below:
Returning the value of a stored procedure;
Stored procedure parameter output;
Selecting one of the stored procedures.
4.1 Returning values \u200b\u200bfor SQL stored procedures
In this technique, the procedure assigns a value to a local variable and returns it. The procedure can also return a constant value directly. In the following example, we have created a procedure that returns the total number of authors. If you compare this procedure with previous ones, you can see that the print value is reversed.
Now let's see how to execute the procedure and print the return value. The execution of the procedure requires setting a variable and printing, which is carried out after the whole process. Note that instead of a print statement, you can use a Select statement, such as Select @RetValue and OutputValue.
4.2 Parameter output of SQL stored procedures
The return value can be used to return a single variable, as we saw in the previous example. Using the Output parameter allows a procedure to send one or more variable values \u200b\u200bto the caller. The output parameter is denoted just the same by this keyword "Output" when creating a procedure. If a parameter is specified as an output parameter, then the procedure object must assign a value to it. SQL stored procedures, examples of which can be seen below, are then returned with summary information.
Our example will have two output names: @TotalAuthors and @TotalNoContract. They are listed in the parameter list. These variables are assigned values \u200b\u200bwithin the body of the procedure. When we use out parameters, the caller can see the value set inside the body of the procedure.
Additionally, in the previous script, two variables are declared to see the values \u200b\u200bthat set the MS SQL Server stored procedures in the out parameter. Then the procedure is performed by supplying the normal value of the "CA" parameter. The following parameters are output and therefore the declared variables are passed in the specified order. Note that when passing through variables, the output keyword is also set here. After the procedure is successful, the values \u200b\u200breturned by the output parameters are displayed in a message box.
4.3 Selecting one of the SQL stored procedures
This technique is used to return a set of values \u200b\u200bas a data table (RecordSet) to the calling stored procedure. In this SQL example, a stored procedure with @AuthID parameters queries the Authors table by filtering the returned records with this @AuthId parameter. The Select statement decides what should be returned to the caller of the stored procedure. When the stored procedure runs, AuthId is passed back. This procedure always returns only one record here, or none at all. But the stored procedure does not have any restrictions on returning more than one record. It is not uncommon to see examples in which returning data using selected parameters involving calculated variables occurs by providing multiple totals.
Finally
A stored procedure is a pretty serious piece of software that returns or passes, and sets the required variables through the client application. Since the stored procedure executes itself on the server, huge amounts of data exchange between the server and the client application (for some computations) can be avoided. This allows to reduce the load on SQL servers, which, of course, goes into the hands of their holders. T SQL stored procedures are one of the subspecies, but their study is necessary for those who are engaged in creating impressive databases. There is also a large, even a huge number of nuances that can be useful when studying stored procedures, but this is needed more for those who are planning to take up programming closely, including professionally.
Stored procedures
The subject of this chapter is one of the most powerful tools offered to developers of InterBase database applications for the implementation of business logic Stoied proceduies allow you to implement a significant part of the application logic at the database level and thus improve the performance of the entire application, centralize data processing and reduce the amount of code required to complete the tasks. Almost any sufficiently complex database application requires the use of stored procedures.
In addition to these well-known advantages of using stored procedures, which are common to most relational DBMSs, InterBase stored procedures can act as near-fledged datasets, which allows them to use the results they return in regular SQL queries.
Often novice developers think of stored procedures as simply a set of specific SQL queries that do something inside the database, and there is an opinion that working with stored procedures is much more difficult than implementing the same functionality in a client application in a high-level language
So what are stored procedures in InterBase?
A stored procedure (SP) is a part of the database metadata, which is a subroutine compiled into the InterBase internal representation, written in a special language, the compiler of which is built into the InteiBase server core
The stored procedure can be called from client applications, from triggers and other stored procedures. A stored procedure is executed inside the server process and can manipulate data in the database, as well as return the results of its execution to the client that called it (i.e., trigger, CP, application)
The basis of the powerful capabilities inherent in the SP is a procedural programming language that contains both modified statements of ordinary SQL, such as INSERT, UPDATE and SELECT, as well as branch and loop organizers (IF, WHILE), as well as error handling tools and exceptions The stored procedure language allows you to implement complex algorithms for working with data, and due to the focus on working with relational data, HP are much more compact than similar procedures in traditional languages.
It should be noted that the same programming language is used for triggers, with the exception of a number of features and restrictions. The differences between the subset of the language used in triggers and the XPS language are discussed in detail in the chapter "Triggers" (Part 1).
An example of a simple stored procedure
Now is the time to create the first stored procedure and use it to study the process of creating stored procedures. But first, a few words should be said about how to work with stored procedures. The fact is that the extremely poor standard tools for developing and debugging stored procedures owe their glory to the obscure and inconvenient CP tool. In the InterBase documentation, it is recommended to create procedures using SQL script files containing the text of CP, which are fed to the input to the isql interpreter, and thus create and modify the CP. If in this SQL script at the stage of compiling the text of the procedure in BLR (about BLR see If an error occurs, isql displays a message on which line of the SQL script file this error occurred. Correct the mistake and repeat all over again. Debugging in the modern sense of the word, that is, execution tracing, with the ability to see the intermediate values \u200b\u200bof variables, is not at all talking. Obviously, this approach does not increase the attractiveness of stored procedures in the eyes of the developer.
However, in addition to the standard minimalist approach to HP development<_\ществ\ют также
инструменты сторонних разработчиков, которые делают работу с хранимыми
процедурами весьма удобной Большинство универсальных продуктов для работы с
InterBase, перечисленных в приложении "Инструменты администратора и разработчика
InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем
обязательно воспользоваться одним из этих инструментов для работы с хранимыми
процедурами и изложение материала будем вести в предположении, что у вас имеется
удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
The syntax for stored procedures is described as follows:
CREATE PROCEDURE name
[(param datatype [, param datatype ...])]
)]
AS
< procedure_body> =
[
<
block>
< vanable_declaration_list>
=
DECLARE VARIABLE var datatype;
BEGIN
< compound_statement>
[< compound_statement> ...]
END
< compound_statement> =
(
It looks quite voluminous and can be even cumbersome, but in reality everything is very simple. In order to gradually master the syntax, let's look at gradually more complicated examples.
So, here's an example of a very simple stored procedure that takes two numbers as input, adds them up, and returns the result:
CREATE PROCEDURE SP_Add (first_arg DOUBLE PRECISION,
second_arg DOUBLE PRECISION)
RETURNS (Result DOUBLE PRECISION)
AS
BEGIN
Result \u003d first_arg + second_arg;
SUSPEND;
END
As you can see, everything is simple: after the CREATE PROCEDURE command, the name of the newly created procedure is indicated (which must be unique within the database) - in this case, SP_Add, then the input parameters of the SP - first_arg and second_arg - are listed in brackets, separated by commas, with an indication of their types.
The list of input parameters is an optional part of the CREATE PROCEDURE statement - there are cases when the procedure receives all the data for its operation by querying the tables inside the procedure body.
Stored procedures use any scalar InteiBase data types No arrays and user-defined types - domains
Next comes the RETURNS keyword, after which the returned parameters are listed in parentheses, indicating their types - in this case, only one - Result.
If the procedure should not return parameters, then the word RETURNS and the list of return parameters are missing.
The RETURNSQ is followed by the AS keyword. Before the AS keyword goes title,and after it - techoprocedures.
The body of a stored procedure is a list of descriptions of its internal (local) variables (if any, we will discuss them in more detail below), separated by a semicolon (;), and a block of statements, enclosed in statement brackets BEGIN END. In this case, the body of the SP is very simple - we ask to add two input arguments and assign their result to the output, and then call the SUSPEND command. A little later, we will explain the essence of the operation of this command, but for now we will just note that it is needed to pass the returned parameters to where the stored procedure was called from.
Separators in Stored Procedures
Note that a statement inside a procedure ends with a semicolon (;). As you know, the semicolon is the standard command separator in SQL - it is a signal to the SQL interpreter that the command text has been entered in full and it is necessary to start processing it. Could it be that when it detects a semicolon in the middle of the SP, the SQL interpreter will think that the command has been entered in full and will try to execute part of the stored procedure? This assumption makes sense. Indeed, if you create a file in which to write the above example, add a command to connect from the database and try to execute this SQL script using the isql interpreter, an error will be returned related to the unexpected, in the interpreter's opinion, termination of the command to create a stored procedure. If you create stored procedures using SQL script files, without using specialized InterBase developer tools, then before each CP creation command (the same applies to triggers), change the script command separator to another character other than a semicolon, and after HP text to restore it back. The isql command that changes the SQL statement separator looks like this:
SET TERM
For a typical case of creating a stored procedure, it looks like this:
SET TERM ^;
CREATE PROCEDURE some_procedure
... . .
END
^
SET TERM; ^
Stored Procedure Call
But back to our stored procedure. Now that it is created, you need to call it somehow, pass parameters to it and get the returned results. It is very easy to do this - you just need to write a SQL query of the following form:
SELECT *
FROM Sp_add (181.35, 23.09)
This query will return us one row containing only one Result field, which will contain the sum of the numbers 181.35 and 23.09, that is, 204.44.
Thus, our procedure can be used in ordinary SQL queries executed as in client programs, and in other HP or triggers. This use of our procedure was made possible by the use of the SUSPEND command at the end of the stored procedure.
The fact is that in InterBase (and in all its clones) there are two types of stored procedures: selectable procedures and executable procedures. The difference in the operation of these two types of SPs is that fetch procedures usually return many sets of output parameters, grouped line by line, which are in the form of a dataset, and the executed procedures may either not return parameters at all, or return only one set of output parameters. listed in Returns, where a single line of parameters. Select procedures are called in SELECT queries, and executable procedures are called using the EXECUTE PROCEDURE command.
Both types of stored procedures have the same creation syntax and are not formally different, so any executable procedure can be called in a SELECT query and any selection procedure can be called using EXECUTE PROCEDURE. The question is how HP will behave when different types call. In other words, the difference lies in the design of the procedure for a specific type of call. That is, a select procedure is specifically created to be called from a SELECT query, and an executable procedure is specifically created to be called using EXECUTE PROCEDURE. Let's take a look at what are the differences when designing these two types of HP.
In order to understand how the sampling procedure works, you have to go a little deeper into the theory. Let's imagine a simple SQL query like SELECT ID, NAME FROM Table_example. As a result of its execution, we get at the output a table consisting of two columns (ID and NAME) and a certain number of rows (equal to the number of rows in the Table_example table). The table returned as a result of this query is also called an SQL dataset Let's think about how the dataset is formed during the execution of this query.The server, having received a query, determines which tables it belongs to, then finds out which subset of records from these tables must be included in the query result ... Next, the server reads each record that satisfies the query results, selects the required fields from it (in our case, these are ID and NAME) and sends them to the client. Then the process is repeated again - and so on for each selected entry.
All this digression is necessary in order for the dear reader to understand that all SQL data sets are formed line by line, including in stored procedures! And the main difference between fetch procedures and executable procedures is that the former are designed to return many rows, while the latter are designed for only one. Therefore, they are used in different ways: a select procedure is called using the SELECT command, which "requires" the procedure to give all the records that it can return. The executable procedure is called using EXECUTE PROCEDURE, which "takes out" only one line from the CP, and ignores the rest (even if they exist!).
Let's look at an example of a fetch procedure to make it clearer. For the\u003e forgiveness, let's create a stored procedure that works exactly like the SELECT ID, NAME FROM Table_Example query, that is, it just fetches the ID and NAME fields from the entire table. Here's this example:
CREATE PROCEDURE Simple_Select_SP
RETURNS (
procID INTEGER,
procNAME VARCHAR (80))
AS
BEGIN
FOR
SELECT ID, NAME FROM table_example
INTO: procID,: procNAME
DO
BEGIN
SUSPEND;
END
END
Let's take a look at the actions of this procedure called Simple_Select_SP. As you can see, it has no input parameters and has two output parameters - ID and NAME. The most interesting thing, of course, lies in the body of the procedure. Here the FOR SELECT construction is used:
FOR
SELECT ID, NAME FROM table_example
INTO: procID,: procNAME
DO
BEGIN
/ * do something with the procID and procName variables * /
END
This piece of code means the following: for each row selected from the Table_example, put the selected values \u200b\u200bin the procID and procName variables, and then perform some action with these variables.
You can make a surprised face and ask, "Variables? What other variables? 9" It's kind of a surprise in this chapter that we can use variables in stored procedures. In the XPS language, you can declare your own local variables within a procedure, or use input and output parameters as variables.
In order to declare a local variable in a stored procedure, you must place its description after the AS keyword and before the first BEGIN word.The description of a local variable looks like this:
DECLARE VARIABLE
For example, to declare an integer local variable Mylnt, insert the following description between AS and BEGIN
DECLARE VARIABLE Mylnt INTEGER;
The variables in our example start with a colon. This is done because they are accessed inside the SQL FOR SELECT command, therefore, to distinguish between fields in tables that are used in SELECT, and variables, it is necessary to precede the last colon. After all, variables can have exactly the same name as fields in tables!
But the colon in front of the variable name should be used only inside SQL queries. Outside the texts, the variable is accessed without a colon, for example:
procName \u003d "Some name";
But back to the body of our procedure. The FOR SELECT clause returns data not in the form of a table - a set of data, but one line at a time. Each returned field must be placed in its own variable: ID \u003d\u003e procID, NAME \u003d\u003e procName. In the DO part, these variables are sent to the client who called) procedure\u003e p\u003e, using the SUSPEND command
Thus, the FOR SELECT ... DO command loops through the records selected in the SELECT part of the command. In the body of the loop formed by the DO part, the next generated record is transferred to the client using the SUSPEND command.
So, the fetch procedure is designed to return one or more rows, for which a loop is organized inside the SP body that fills the resulting variable parameters. And at the end of the body of this cycle there is always a SUSPEND command, which will return the next line of data to the client.
Loops and branching operators
In addition to the FOR SELECT ... DO command, which organizes a loop over the records of a selection, there is another type of loop - WHILE ... DO, which allows you to organize a loop based on the check of any conditions. Here is an example of a CP using a WHILE .. DO loop. This routine returns the squares of integers between 0 and 99:
CREATE PROCEDJRE QUAD
RETURNS (QUADRAT INTEGER)
AS
DECLARE VARIABLE I INTEGER;
BEGIN
I \u003d 1;
WHILE (i<100) DO
BEGIN
QUADRAT \u003d I * I;
I \u003d I + 1;
SUSPEND;
END
END
As a result of executing the SELECT FROM QUAD query, we will get a table containing one QUADRAT column, in which there will be squares of integers from 1 to 99
In addition to iterating over the results of a SQL selection and a classic loop, the stored procedure language uses the IF ... THEN..ELSE operator, which allows you to organize branching depending on the execution of some \\ words. Its syntax is similar to most branching operators in high-level programming languages, like Pascal and C.
Let's take a look at a more complex example of a stored procedure that does the following.
- Calculates the average price in the Table_example table (see chapter "Tables Primary Keys and Generators")
- Further, for each record in the table, it makes the following check, if the existing price (PRICE) is greater than the average price, then it sets the price equal to the value of the average price, plus a specified fixed percentage
- If the existing price is less than or equal to the average price, then sets the price equal to the previous price, plus half the difference between the previous and average prices.
- Returns all changed rows in the table.
First, let's define the name of the SP, as well as the input and output parameters. All this is written in the header of the stored procedure.
CREATE PROCEDURE IncreasePrices (
Percent2lncrease DOUBLE PRECISION)
RETURNS (ID INTEGER, NAME VARCHAR (SO), new_price DOUBLE
PRECISION) AS
The procedure will be named IncreasePrices, it has one input parameter Peiceni21nciease of type DOUBLE PRECISION, and 3 output parameters - ID, NAME and new_pnce. Note that the first two output parameters have the same names as the fields in the Table_example that we are going to work with. This is allowed by the rules of the stored procedure language.
Now we have to declare a local variable that will be used to store the average value. The declaration will look like this:
DECLARE VARIABLE avg_price DOUBLE PRECISION;
Now let's move on to the body of the stored procedure. Open the body of the HP
the BEGIN keyword.
First, we need to perform the first step of our algorithm - calculate the average price. To do this, we will use the following query:
SELECT AVG (Price_l)
FROM Table_Example
INTO: avg_price, -
This query uses the AVG aggregate function, which returns the average of the PRICE_1 field among the selected query rows — in our case, the average of PRICE_1 over the entire Table_example. The value returned by the query is placed in the avg_price variable. Note that the avg_pnce variable is prefixed with a colon - to distinguish it from the fields used in the query.
The peculiarity of this query is that it always returns exactly one and only record. Such queries are called singleton queries. And only such selections can be used in stored procedures. If the query returns more than one row, then it must be formatted in the form of a FOR SELECT ... DO structure, which organizes a loop to process each returned row
So, we got the average price. Now you need to go through the entire table, compare the price value in each record with the average price and take the appropriate action
From the beginning, we organize the iteration over each record from the Table_example table
FOR
SELECT ID, NAME, PRICE_1
FROM Table_Example
INTO: ID,: NAME,: new_price
DO
BEGIN
/ * _ ossify each record here * /
END
When this construction is executed, data will be taken out line by line from the Table_example, and the field values \u200b\u200bin each line will be assigned to the ID, NAME and new_pnce variables. You will, of course, remember that these variables are declared as out parameters, but you should not worry that the selected data will be returned as results: the fact that something is assigned to the out parameters does not mean that the client calling the HP will immediately receive these values. ! Parameters are passed only when the SUSPEND command is executed, and before that we can use the output parameters as ordinary variables - in our example, we do just that with the new_price parameter.
So, inside the body of the BEGIN .. .END loop, we can process the values \u200b\u200bof each line. As you remember, we need to find out how the current price relates to the average and take appropriate action. We will implement this comparison procedure using the IF statement:
IF (new_price\u003e avg_price) THEN / * if the existing price is higher than the average price * /
BEGIN
/ * then set a new price equal to the average price plus a fixed percentage * /
new_price \u003d (avg_price + avg_price * (Percent2Increase / 100));
UPDATE Table_example
SET PRICE_1 \u003d: new_price
WHERE ID \u003d: ID;
END
ELSE
BEGIN
/ * If the existing price is less than or equal to the average price, then set the price equal to the previous price, plus half the difference between the previous and average prices * /
new_price \u003d (new_pnce + ((avg_pnce new_price) / 2));
UPDATE Table_example
SET PRICE_1 \u003d: new_price
WHERE ID \u003d .ID;
END
As you can see, the result is a rather large IF construct, which would be difficult to understand if it were not for the comments enclosed in / ** / symbols.
In order to change the price according to the calculated difference, we will use the UPDATE statement, which allows you to modify existing records - one or more. In order to unambiguously indicate in which record the price needs to be changed, we use the primary key field in the WHERE clause, comparing it with the value of the variable that stores the ID value for current record: ID \u003d: ID. Note that the variable ID is preceded by a colon.
After the execution of the IF ... THEN ... ELSE construction, the variables ID, NAME and new_price contain the data that we must return to the client \\ that called the procedure. To do this, after the IF, it is necessary to insert the SUSPEND command, which will send the data to the place from which the SP was called.At the time of transfer, the procedure will be suspended, and when a new record is required from the SP, it will be resumed, and this will continue until FOR SELECT ... DO will not iterate over all the records of its query.
It should be noted that in addition to the SUSPEND command, which only suspends the stored procedure, there is an EXIT command, which terminates the stored procedure after the string has been passed. However, the EXIT command is rarely used, since it is needed mainly in order to interrupt the cycle when a condition is reached.
In this case, in the case when the procedure was called by the SELECT statement and completed by EXIT, the last retrieved row will not be returned. That is, if you need to interrupt the procedure and still\u003e get this string, you must use the sequence
SUSPEND;
EXIT;
The main purpose of EXIT is to get singleton datasets of return parameters by calling them through EXECUTE PROCEDURE. In this case, the values \u200b\u200bof the output parameters are set, but the SQL dataset is not formed from them, and the procedure ends.
Let's write out the entire text of our stored procedure so that we can capture its logic at a glance:
CREATE PROCEDURE IncreasePrices (
Percent2Increase DOUBLE PRECISION)
RETURNS (ID INTEGER, NAME VARCHAR (80),
new_price DOUBLE PRECISION) AS
DECLARE VARIABLE avg_price DOUBLE PRECISION;
BEGIN
SELECT AVG (Price_l)
FROM Table_Example
INTO: avg_price;
FOR
SELECT ID, NAME, PRICE_1
FROM Table_Example
INTO: ID,: NAME,: new_price
DO
BEGIN
/ * process each record here * /
IF (new_pnce\u003e avg_price) THEN / * if the existing price is higher than the average price * /
BEGIN
/ * set a new price equal to the average price plus a fixed percentage * /
new_price \u003d (avg_price + avg_price * (Percent2lncrease / 100));
UPDATE Table_example
SET PRICE_1 \u003d: new_price
WHERE ID \u003d: ID;
END
ELSE
BEGIN
/ * If the existing price is less than or equal to the average price, then sets the price equal to the previous price, plus half the difference between the previous and average prices * /
new_price \u003d (new_price + ((avg_price - new_price) / 2));
UPDATE Table_example
SET PRICE_1 \u003d: new_price
WHERE ID \u003d: ID;
END
SUSPEND;
END
END
This sample stored procedure illustrates the use of basic Stored Procedure and Trigger language constructs. Next, we'll look at how to use stored procedures to solve some common problems.
Recursive stored procedures
InterBase stored procedures can be recursive. This means that you can call itself from within a stored procedure. Up to 1000 levels of nesting of stored procedures are allowed, but one must remember that free resources on the server may run out before the maximum nesting of the HP is reached.
One of the common uses for stored procedures is to manipulate tree structures stored in a database. Trees are often used in inventory, warehousing, HR, and other common applications.
Let's look at an example of a stored procedure that selects all products of a specific type, starting at a specific nesting level.
Suppose we have the following problem statement: we have a catalog of goods with hierarchical structure of this kind:
Products
- Appliances
- Refrigerators
- Three-chamber
- Two-chamber
- Single chamber
-
Washing machines
- Vertical
- Frontal
- Classic
- Narrow
- Computer technology
....
This structure of the directory of product categories can have branches of various depths. and also build up over time. Our task is to ensure that all leaf elements are selected from the directory with "expanding full name", starting from any node. For example, if we select the "Washing machines" node, then we need to get the following categories:
Washing Machines - Vertical
Washing Machines - Front Classic
Washing machines - Frontal Narrow
Let's define the structure of tables for storing information in the catalog of goods. We use a simplified scheme to organize the tree in one table:
CREATE TABLE GoodsTree
(ID_GOOD INTEGER NOT NULL,
ID_PARENT_GOOD INTEGER,
GOOD_NAME VARCHAR (80),
constraint pkGooci primary key (ID_GOOD));
We create one table GoodsTree, in which there are only 3 fields: ID_GOOD - smart category identifier, ID_PARENT_GOOD - identifier of the parent city for this category and GOOD_NAME - name of the category. To ensure the integrity of the data in this table, we impose a foreign key constraint on this table:
ALTER TABLE GoodsTree
ADD CONSTRAINT FK_goodstree
FOREIGN KEY (ID_PARENT_GOOD)
REFERENCES GOODSTPEE (ID__GOOD)
The table refers to itself and the given foreign key keeps track of that. so that there are no references to non-existent parents in the table, and also prevents attempts to delete product categories that have descendants.
Let's put the following data into our table:
| ID_GOOD 1
|
ID_PARENT_GOOD 0
|
GOOD_NAME GOODS |
Now that we have some storage space, we can start creating a stored procedure that displays all the "final" product categories in an "expanded" view - for example, for the "Three-compartment" category, the full category name would look like "Household appliances Refrigerators Three-chamber ".
Stored procedures that process tree-like structures have their own terminology. Each element of the tree is called a node; and the relationship between nodes referencing each other is called a parent-child relationship. The nodes that are at the very end of the tree and do not have children are called "leaves".
Our stored procedure will have a category identifier as an input parameter, from which we will have to start expanding. The stored procedure will look like this:
CREATE PROCEDURE GETFULLNAME (ID_GOOD2SHOW INTEGER)
RETURNS (FULL_GOODS_NAME VARCHAR (1000),
ID_CHILD_GOOD INTEGER)
AS
DECLARE VARIABLE CURR_CHILD_NAME VARCHAR (80);
BEGIN
/ * 0 organize external fOR loop SELECT on immediate descendants of the product with ID_GOOD \u003d ID_GOOD2SHOW * /
FOR SELECT gtl.id_good, gtl.good_name
FROM GoodsTree gtl
WHERE gtl.id_parent_good \u003d: ID_good2show
INTO: ID_CHILD_GOOD,: full_goods_name
DO
BEGIN
/ "Checking with the EXISTS function, which returns TRUE if the query in parentheses returns at least one row. If the found node with ID_PARENT_GOOD \u003d ID_CHILD_GOOD has no descendants, then it is a" leaf "of the tree and is included in the results * /
IF (NOT EXISTS (
SELECT * FROM GoodsTree
WHERE GoodsTree.id_parent_good \u003d: id_child_good))
THEN
BEGIN
/ * Pass the "leaf" of the tree to the results * /
SUSPEND;
END
ELSE
/ * For nodes that have children * /
BEGIN
/ * store the name of the parent node in a temporary variable * /
CURR_CHILD_NAME \u003d full_goods_name;
/ * run this procedure recursively * /
FOR
SELECT ID_CHILD_GOOD, full_goods_name
FROM GETFULLNAME (: ID_CHILD_GOOD)
INTO: ID_CHILD_GOOD,: full_goods_name
DO BEGIN
/ * add the name of the parent node to the found one., the name of the child using the string concatenation operation || * /
full_goods_name \u003d CURR_CHILD_NAME | "" | f ull_goods_name, -
SUSPEND; / * return the full name of the product * /
END
END
END
END
If we execute this procedure with the input parameter ID_GOOD2SHOW \u003d 1, we get the following:
As you can see, using a recursive stored procedure, we walked through the entire category tree and displayed the full name of the "leaf" categories that are at the very tips of the branches.
Conclusion
This concludes the review of the main features of the stored procedure language. Obviously, it is impossible to fully master the development of stored procedures in one chapter, but here we have tried to present and explain the basic concepts associated with stored procedures. The described CP constructs and design techniques can be applied in most database applications.
Some of the important issues related to the development of stored procedures will be disclosed in the next chapter - "Advanced features of the InterBase stored procedure language", which is devoted to handling exceptions, resolving errors in stored procedures and working with arrays.
