Stored procedures
The subject of this chapter is one of the most powerful tools offered to developers of InterBase database applications for implementing business logic. Stored procedures (English, stoied proceduies) allow you to implement a significant part of the application logic at the database level and thus increase the performance of the entire application, centralize data processing and reduce the amount of code required to complete the tasks. Almost anything is enough complex application Databases are not complete without the use of stored procedures.
In addition to these well-known advantages of using stored procedures, common to most relational DBMSs, InterBase stored procedures can act as almost complete data sets, allowing the results they return to be used in ordinary SQL queries.
Often, novice developers imagine stored procedures simply as 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 the language high level
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 internal representation of InterBase, written in a special language, the compiler of which is built into the core of the InteiBase server
A stored procedure can be called from client applications, from triggers, and from other stored procedures. The stored procedure runs 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, HP, application)
The basis of the powerful capabilities inherent in HP is a procedural programming language, which includes both modified statements of regular SQL, such as INSERT, UPDATE and SELECT, as well as tools for organizing branches and loops (IF, WHILE), as well as error handling tools and exceptional situations The language of stored procedures allows you to implement complex algorithms for working with data, and due to the focus on working with relational data, HP is 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 limitations. The differences between the subset of the language used in triggers and the HP language are discussed in detail in the chapter “Triggers” (part 1).
Example of a simple stored procedure
It's time to create your first stored procedure and use it as an example to learn the process of creating stored procedures. But first, we should say a few words about how to work with stored procedures. The fact is that HP owes its reputation as an obscure and inconvenient tool to extremely poor standard tools for developing and debugging stored procedures. The InterBase documentation recommends creating procedures using SQL script files containing HP text, which are supplied as input to the isql interpreter, and thus creating and modifying HP If in this SQL script at the stage of compiling the text of the procedure in BLR (about BLR, see Chapter "InterBase Database Structure" (Part 4)) if an error occurs, isql will display a message about which line of the SQL script file this error occurred on. Correct the mistake and do it all over again. There is no talk at all about debugging in the modern sense of the word, i.e. about execution tracing, with the ability to view intermediate values of variables. Obviously, this approach does not contribute to the growth of 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 of 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 may even be cumbersome, but in fact everything is very simple. In order to gradually master the syntax, let's look at gradually more complex examples.
So, here is an example of a very simple stored procedure that takes two numbers as input, adds them and returns the result:
CREATE PROCEDURE SP_Add(first_arg DOUBLE PRECISION,
second_arg DOUBLE PRECISION)
RETURNS (Result DOUBLE PRECISION)
AS
BEGIN
Result=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 HP input parameters - first_arg and second_arg - are listed in parentheses, separated by commas, indicating their types.
The list of input parameters is an optional part of the CREATE PROCEDURE statement - there are cases when a procedure receives all the data for its work through queries to tables inside the body of the procedure.
Stored procedures use any scalar data types InteiBase It does not allow for the use of 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 returned parameters are missing.
After RETURNSQ the keyword AS is specified. Before the keyword AS goes title, and after that - techo procedures.
The body of a stored procedure is a list of descriptions of its internal (local) variables (if any, we will look at them in more detail below), separated by a semicolon (;), and a block of statements enclosed in operator brackets BEGIN END. In this case, the body of the HP is very simple - we ask to add two input arguments and assign their result to the output one, and then call the SUSPEND command. A little later we will explain the essence of the action of this command, but for now we will only note that it is needed to transfer the return parameters to where the stored procedure was called from.
Delimiters in stored procedures
Note that a statement within a procedure ends with a semicolon (;). As you know, the semicolon is a standard command separator in SQL - it is a signal to the SQL interpreter that the command text has been entered in full and should begin processing it. Wouldn't it turn out that if the SQL interpreter finds a semicolon in the middle of the HP, it will assume that the command has been entered in full and will try to execute part of the stored procedure? This assumption is not without merit. Indeed, if you create a file in which to write the above example, add a connection command from the database and try to execute this SQL script using the isql interpreter, an error will be returned due to the unexpected, in the interpreter’s opinion, ending of the stored procedure creation command. If you create stored procedures using SQL script files, without using specialized InterBase developer tools, then before each HP creation command (the same applies to triggers) you need to change the script command separator to another character other than a semicolon, and after text HP to restore it back. The isql command that changes the SQL clause 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 ;^
Calling a Stored Procedure
But let's return to our stored procedure. Now that it has been created, you need to call it somehow, pass parameters to it and get the results returned. This is very easy to do - just write an SQL query like this:
SELECT *
FROM Sp_add(181.35, 23.09)
This query will return us one line containing just one Result field, which will contain the sum of the numbers 181.35 and 23.09, i.e. 204.44.
Thus, our procedure can be used in ordinary SQL queries executed both in client programs and in other HP or triggers. This use of our procedure is made possible by using 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 HP is that sampling procedures usually return many sets of output parameters, grouped line by line, which look like a set of data, and executable procedures could either not return parameters at all, or return only one set of output parameters , listed in Returns, where one 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 formally no 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 under different types of calls. In other words, the difference lies in designing the procedure for a particular type of call. That is, the select procedure is specifically created to be called from a SELECT query, and the executable procedure is specifically created to be called using EXECUTE PROCEDURE. Let's look at what the differences are in the design of these two types of HP.
In order to understand how the sampling procedure works, you will have to delve a little deeper into the theory. Let's imagine a regular SQL query like SELECT ID, NAME FROM Table_example. As a result of its execution, we get 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 from this query is also called a set SQL data Let's think about how the data set is formed during the execution of this query. The server, having received the query, determines which tables it refers to, then finds out which subset of records from these tables needs to 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, ID and NAME) and sends them to the client. Then the process is repeated again - and so on for each selected record.
All this digression is necessary so that the dear reader understands that all SQL data sets are generated row by row, 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 to return only one. That's why they are used differently: the select procedure is called using the SELECT command, which "requires" the procedure to give up all the records that it can return. The executable procedure is called using EXECUTE PROCEDURE, which “takes out” only one line from the HP, and ignores the rest (even if they exist!).
Let's look at an example of a sampling procedure to make it clearer. For > forgiveness, let's create a stored procedure that works exactly like a SELECT ID, NAME FROM Table_Example query, that is, it simply selects the ID and NAME fields from the entire table. Here is 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 look at the steps 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. The FOR SELECT construct is used here:
FOR
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
DO
BEGIN
/*do something with the variables procID and procName*/
END
This piece of code means the following: for each row selected from the Table_example table, put the selected values in the procID and procName variables, and then do something with these variables.
You might make a surprised face and ask, "Variables? What other variables? 9" It's kind of the surprise of this chapter that we can use variables in stored procedures. In the HP language, you can declare both your own local variables inside a procedure and use input and output parameters as variables.
In order to declare a local variable in a stored procedure, you need to place its description after the AS keyword and before the first word BEGIN. The description of a local variable looks like this:
DECLARE VARIABLE
For example, to declare an integer local variable Mylnt, you would insert the following declaration between AS and BEGIN
DECLARE VARIABLE Mylnt INTEGER;
The variables in our example begin with a colon. This is done because they are accessed within the FOR SELECT SQL command, so to distinguish between fields in tables that are used in SELECT and variables, the latter must be preceded by a colon. After all, variables can have exactly the same name as fields in tables!
But the colon before a variable name should only be used inside SQL queries. Outside of texts, a variable is referred to without a colon, for example:
procName="Some name";
But let's return 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 row at a time. Each returned field must be placed in its own variable: ID => procID, NAME => procName. In the DO part, these variables are sent to the client that called the procedure 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 selection procedure is designed to return one or more rows, for which a loop is organized inside the HP body that fills the resulting variable parameters. And at the end of the body of this loop there is always a SUSPEND command, which will return the next row of data to the client.
Loops and branch statements
In addition to the FOR SELECT...DO command, which organizes a loop through the records of a selection, there is another type of loop - WHILE...DO, which allows you to organize a loop based on checking any conditions. Here is an example of HP using the WHILE..DO loop. This procedure returns the squares of integers from 0 to 99:
CREATE PROCEDJRE QUAD
RETURNS (QUADRAT INTEGER)
AS
DECLARE VARIABLE I INTEGER;
BEGIN
I = 1;
WHILE(i<100) DO
BEGIN
QUADRAT= I*I;
I=I+1;
SUSPEND;
END
END
As a result of executing the SELECT FROM QUAD query, we will receive a table containing one QUADRAT column, which will contain the squares of integers from 1 to 99
In addition to iterating over the results of a SQL sample 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 any conditions. Its syntax is similar to most branching operators in high-level programming languages, like Pascal and C.
Let's look at a more complex example of a stored procedure that does the following.
- Calculates the average price in the table Table_example (see chapter "Tables Primary Keys and Generators")
- Next, for each entry in the table, it makes the following check: if the existing price (PRICE) is greater than the average price, then it sets a price equal to the average price, plus a specified fixed percentage
- If the existing price is less than or equal to the average price, then sets a price equal to the previous price, plus half the difference between the previous and average price.
- Returns all modified rows in the table.
First, let's define the name of the HP, as well as 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 called 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 Table_example that we are going to work with. This is allowed by the rules of the stored procedure language.
Now we need 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
keyword BEGIN.
First we need to perform the first step of our algorithm - calculate the average price. To do this, we will use the following type of 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 the PRICE_1 across the entire Table_example table. The value returned by the request is placed in the avg_price variable. Note that the avg_pnce variable is preceded by a colon to distinguish it from the fields used in the request.
The peculiarity of this query is that it always returns exactly one single record. Such queries are called singleton queries. And only such selections can be used in stored procedures. If a query returns more than one row, then it must be formatted as a FOR SELECT...DO construct, 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 entry with the average price and take appropriate actions
From the beginning, we organize the search for each record from the Table_example table
FOR
SELECT ID, NAME, PRICE_1
FROM Table_Example
INTO:ID, :NAME, :new_price
DO
BEGIN
/*_here we describe each entry*/
END
When this construction is executed, data will be extracted from the Table_example table row by row and the field values in each row will be assigned to the variables ID, NAME and new_pnce. You, of course, remember that these variables are declared as output parameters, but there is no need to worry that the selected data will be returned as results: the fact that the output parameters are assigned something 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 of each row. As you remember, we need to figure out how the existing price compares to the average and take appropriate action. We implement this comparison procedure using the IF statement:
IF (new_price > avg_price) THEN /*if the existing price is greater than the average price*/
BEGIN
/*then install new price, equal to the average price, plus a fixed percentage */
new_price = (avg_price + avg_price*(Percent2Increase/100));
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID = :ID;
END
ELSE
BEGIN
/* If the existing price is less than or equal to the average price, then set a price equal to the previous price, plus half the difference between the previous and average price */
new_price = (new_pnce + ((avg_pnce new_price)/2)) ;
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID = .ID;
END
As you can see, the result is a fairly large IF construct, which would be difficult to understand if not for the comments enclosed in the /**/ symbols.
In order to change the price according to the calculated difference, we will use the UPDATE statement, which allows us 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 condition, comparing it with the value of the variable that stores the ID value for the current record: ID=:ID. Note that the ID variable is preceded by a colon.
After executing the IF...THEN...ELSE construct, the ID, NAME and new_price variables contain data that we must return to the client that called the procedure. To do this, after IF, you need to insert the SUSPEND command, which will send the data to where the HP was called from. During the transfer, the procedure will be suspended, and when the HP is required new entry, then it will be continued again - and this will continue until FOR SELECT...DO iterates through 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 that terminates the stored procedure after passing the string. However, the EXIT command is used quite rarely, since it is needed mainly to interrupt the loop when a condition is reached
Moreover, in the case when the procedure was called SELECT statement and terminated by EXIT, the last row retrieved will not be returned. That is, if you need to interrupt the procedure and still >get this string, you need to use the sequence
SUSPEND;
EXIT;
The main purpose of EXIT is to receive singleton data sets, returned parameters by calling EXECUTE PROCEDURE. In this case, the values of the output parameters are set, but the SQL data set is not generated from them, and the execution of the procedure ends.
Let's write down the text of our stored procedure in its entirety 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 > avg_price) THEN /*if the existing price is greater than the average price*/
BEGIN
/*set a new price equal to the average price plus a fixed percentage */
new_price = (avg_price + avg_price*(Percent2lncrease/100));
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID = :ID;
END
ELSE
BEGIN
/* If the existing price is less than or equal to the average price, then sets a price equal to the previous price plus half the difference between the previous and average price */
new_price = (new_price + ((avg_price - new_price)/2));
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID = :ID;
END
SUSPEND;
END
END
This stored procedure example illustrates the use of basic stored procedure language constructs and triggers. Next, we'll look at ways to use stored procedures to solve some common problems.
Recursive Stored Procedures
InterBase stored procedures can be recursive. This means that a stored procedure can call itself. Up to 1000 levels of nesting of stored procedures is allowed, but we must remember that free resources on the server may run out before the maximum nesting of HP is reached.
One common use of stored procedures is to process tree structures stored in a database. Trees are often used in product composition, warehouse, personnel, and other common applications.
Let's look at an example of a stored procedure that selects all products of a certain type, starting from a certain nesting level.
Let us have the following formulation of the problem: we have a directory of goods with a hierarchical structure of the following type:
Goods
- Appliances
- Refrigerators
- Three-chamber
- Double chamber
- Single chamber
-
Washing machines
- Vertical
- Frontal
- Classic
- Narrow
- Computer technology
....
This structure of the product category directory can have branches of varying depths. and also increase over time. Our task is to ensure the selection of all finite elements from the directory with "expanding the 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 - Front Narrow
Let's define the table structure for storing product directory information. 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 - the smart identifier of the category, ID_PARENT_GOOD - the identifier of the parent company for this category and GOOD_NAME - the name of the category. To ensure the integrity of the data in this table, we will 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 this foreign key keeps track of that. so that the table does not contain references to non-existent parents, and also prevents attempts to delete product categories that have children.
Let's enter the following data into our table:
| ID_GOOD 1
|
ID_PARENT_GOOD 0
|
GOOD_NAME GOODS |
Now that we have a place to store the data, we can start creating a stored procedure that will output all the "final" product categories in an "expanded" form - for example, for the category "Three-Chamber", the full category name would be "Household Appliances Refrigerators" Three-chamber".
Stored procedures that process tree 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. Nodes that are at the very end of the tree and have no children are called "leaves".
For this stored procedure, the input parameter will be the category identifier, from which we will have to start the drilldown. 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
/*0organize the outer FOR SELECT loop according to the immediate descendants of the product with ID_GOOD=ID_GOOD2SHOW */
FOR SELECT gtl.id_good, gtl.good_name
FROM GoodsTree gtl
WHERE gtl.id_parent_good=:ID_good2show
INTO:ID_CHILD_GOOD, :full_goods_name
DO
BEGIN
/"Check using the EXISTS function, which returns TRUE if the query in brackets returns at least one row. If the found node with ID_PARENT_GOOD = ID_CHILD_GOOD has no children, 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=:id_child_good))
THEN
BEGIN
/* Pass the “leaf” of the tree to the results */
SUSPEND;
END
ELSE
/* For nodes that have children*/
BEGIN
/*save the name of the parent node in a temporary variable */
CURR_CHILD_NAME=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 child name using the string concatenation operation || */
full_goods_name=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= 1, we will get the following:
As you can see, using a recursive stored procedure, we went through the entire category tree and displayed the full name of the “leaf” categories that are located at the very tips of the branches.
Conclusion
This concludes our consideration of the main features of the stored procedure language. Obviously, it is impossible to fully master stored procedure development in just one chapter, but here we have tried to introduce and explain the basic concepts associated with stored procedures. The described designs and techniques for designing HP can be applied in most database applications
Some of the important issues related to the development of stored procedures will be covered in the next chapter - “Advanced capabilities of the InterBase stored procedure language”, which is devoted to exception handling, resolving error situations in stored procedures, and working with arrays.
MySQL 5 has many new features, 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 declare variables, manipulate data flow, and use other programming techniques.
The reason for their creation is clear and is confirmed by frequent use. On the other hand, if you talk to those who work with them irregularly, the opinions will be divided into two completely opposite flanks. Don't forget this.
Behind
- Sharing logic with other applications. Stored procedures encapsulate functionality; this provides connectivity for data access and management across different applications.
- Isolating users from database tables. This allows you to give access to stored procedures, but not to the table data itself.
- Provides a protection mechanism. As per the previous point, if you can only access data through stored procedures, no one else can erase your data through the SQL DELETE command.
- Improved execution as a result of reduced network traffic. Using stored procedures, multiple queries can be combined.
Against
- Increased load on the database server due to the fact that most of the work is performed on the server side, and less on the client side.
- You'll have to learn a lot. You will need to learn MySQL expression syntax to write your stored procedures.
- You are duplicating your application logic in two places: server code and code for stored procedures, thereby complicating the process of data manipulation.
- Migration from one DBMS to another (DB2, SQL Server, etc.) can lead to problems.
The tool I work with is called MySQL Query Browser, which is pretty standard for interacting with databases. Tool command line MySQL is another excellent choice. The reason I'm telling you this is because everyone's favorite phpMyAdmin doesn't support running stored procedures.
By the way, I'm using a basic table structure to make it easier for you to understand this topic. I’m talking about stored procedures, and they are complex enough to require delving into the cumbersome table structure.
Step 1: Place a limiter
A delimiter is a character or string of characters that is used to indicate to the MySQL client that you have finished writing the SQL expression. For ages, the semicolon has been the delimiter. However, problems may arise because there may 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 part of the code creates a stored procedure. The next one contains optional parameters. Then comes the name and, finally, the body of the procedure itself.
Stored procedure names are case sensitive. You also cannot create multiple procedures with the same name. There cannot be expressions inside a stored procedure that modify the database itself.
4 characteristics of a stored procedure:
- Language: For portability purposes, the default is SQL.
- Deterministic: if the procedure always returns the same result and takes the same input parameters. This is for the replication and registration process. The default value is NOT DETERMINISTIC.
- SQL Security: user rights are checked during the call. INVOKER is the user calling the stored procedure. DEFINER is the “creator” of the procedure. The default value is DEFINER.
- Comment: For documentation purposes, the default value is ""
Calling a Stored Procedure
To call a stored procedure, you must type the keyword CALL, followed by the name of the procedure, followed by the parameters (variables or values) in parentheses. Parentheses are required.
CALL stored_procedure_name (param1, param2, ....) CALL procedure1(10 , "string parameter" , @parameter_var);
Modifying a Stored Procedure
MySQL has an ALTER PROCEDURE statement for changing procedures, but it is only suitable for changing certain characteristics. If you need to change the parameters or body of a procedure, you should delete and recreate it.
Removing a Stored Procedure
DROP PROCEDURE IF EXISTS p2;
This is a simple command. The IF EXISTS statement catches an error if such a procedure does not exist.
Step 3: Options
Let's see how we 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 (in).
- CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): one parameter returned.
- CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): one parameter, both input and return.
Naturally, you can specify several parameters of different types.
IN parameter example
DELIMITER // CREATE PROCEDURE `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS result; END//
Example OUT parameter
DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "This is a test"; END //
INOUT parameter example
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 way as session variables, literals, or column names.
The variable declaration syntax looks like this:
DECLARE varname DATA-TYPE DEFAULT defaultvalue;
Let's declare some 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 its value 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: Thread Control Structures
MySQL supports IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs to control threads within a stored procedure. We'll look at how to use IF, CASE, and WHILE since they are the most commonly used.
IF design
Using 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; ENDIF; IF param1 = 0 THEN SELECT "Parameter value = 0"; ELSE SELECT "Parameter value<>0"; END IF; END //
CASE design
CASE is another method of testing conditions and selecting a suitable solution. This is a great way to replace many IF constructs. The construct can be described in two ways, providing flexibility in managing multiple conditional expressions.
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 design
Technically, there are three types of loops: the WHILE loop, the LOOP loop, and the REPEAT loop. You can also loop using the Darth Vader programming technique: GOTO statements. Here's an example 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 process each row.
MySQL supports cursors in stored procedures. Here short syntax creating and using a cursor.
DECLARE cursor-name CURSOR FOR SELECT ...; /*Declaring a cursor and filling it */ 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 a value to a variable equal to the current value of the column*/ CLOSE cursor-name; /*Close cursor*/
In this example we will perform some simple operations using a cursor:
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; ENDIF; END WHILE; CLOSE cur1; SET param1 = c; END //
Cursors have three properties that you need to understand to avoid getting unexpected results:
- Not sensitive: a cursor that opens once will not reflect changes in the table that occur later. In reality, MySQL does not guarantee that the cursor will be updated, so don't rely on it.
- Read-only: Cursors cannot be modified.
- No rewind: the cursor can only move in one direction - forward, you will not be able to 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 should calculate the benefits of using stored procedures in your specific application, and then create only the necessary procedures. In general, I use procedures; In my opinion, they are worth implementing into projects due to their security, code maintenance and overall design. In addition, do not forget that over MySQL procedures still a work in progress. Expect improvements regarding functionality and improvements. Please feel free to share your opinions.
For programming extended stored procedures, Microsoft provides ODS (Open Data Service) API set macros and functions used to build server applications that allow you to expand the functionality of MS SQL Server 2000.
Extended stored procedures are ordinary functions written in C/C++ using the ODS API and WIN32 API, designed as a library dynamic linking(dll) and designed, as I already said, to expand the functionality of the SQL server. The ODS API provides the developer with a rich set of functions that allows you to transfer data to the client received from any external data source in the form of regular record sets. Also, an extended stored procedure can return values through the parameter passed to it (OUTPUT parameter).
How extended stored procedures work.
- When a client application calls an extended stored procedure, the request is transmitted in TDS format through the Net-Libraries and Open Data Service to the MS SQL SERVER core.
- SQL Sever finds the dll library associated with the name of the extended stored procedure and loads it into its context, if it has not been loaded there before, and calls the extended stored procedure implemented as a function inside the dll.
- The extended stored procedure performs the necessary actions on the server and transfers a set of results to the client application using the service provided by the ODS API.
Features of extended stored procedures.
- Extended stored procedures are functions that execute in the MS SQL Server address space and in the security context account under which the MS SQL Server service is running;
- Once an extended stored procedure dll has been loaded into memory, it remains there until SQL Server is stopped, or until an administrator forces it to unload it using the command:
DBCC DLL_name (FREE). - An extended stored procedure is executed in the same way as a regular stored procedure:
EXECUTE xp_extendedProcName @param1, @param2 OUTPUT
@param1 input parameter
@param2 input/output parameter
Since extended stored procedures are executed in the address space of the MS SQL Server service process, any critical errors that occur in their operation can disable the server core, so it is recommended to thoroughly test your DLL before installing it on a production server.
Creating extended stored procedures.
An extended stored procedure is a function that has the following prototype:
SRVRETCODE xp_extendedProcName(SRVPROC * pSrvProc);
Parameter pSrvProc A pointer to an SRVPROC structure, which is a handle for each specific client connection. The fields in this structure are undocumented and contain information that the ODS library uses to manage communication and data between server application(Open Data Services server application) and the client. In any case, you do not need to access this structure, much less modify it. This parameter is required to be specified when calling any ODS API function, so I will not dwell on its description further.
The use of the xp_ prefix is optional, but there is a convention to start the name of an extended stored procedure this way to distinguish it from a regular stored procedure, which, as you know, begins its names with the sp_ prefix.
You should also remember that extended stored procedure names are case sensitive. Don't forget about this when you call an extended stored procedure, otherwise you will receive an error message instead of the expected result.
If you need to write dll initialization/deinitialization code, use standard function DllMain(). If you do not have such a need, and you do not want to write DLLMain(), then the compiler will build its own version of the DLLMain() function, which does nothing but simply returns TRUE. All functions called from a dll (ie extended stored procedures) must be declared as exportable. If you write in MS Visual C++, use the directive __declspec(dllexport). If your compiler does not support this directive, describe the exported function in the EXPORTS section of the DEF file.
So, to create a project, we will need the following files:
- Srv.h header file, contains a description of the ODS API functions and macros;
- Opends60.lib is an import file for the Opends60.dll library, which implements the entire service provided by the ODS API.
Declspec(dllexport) ULONG __GetXpVersion()
{
return ODS_VERSION;
}
When MS SQL Server loads a DLL with an extended stored procedure, it first calls this function to obtain information about the version of the library being used.
To write your first extended stored procedure, you will need to install on your computer:
MS SQL Server 2000 of any edition (I have Personal Edition). During the installation process, be sure to select the source sample option
- MS Visual C++ (I used version 7.0), but I know for sure 6.0 will do
Installing SQL Server -a is needed to test and debug your DLL. Debugging over the network is also possible, but I have never done this, so I installed everything on my local disk. Microsoft Visual C++ 7.0 Interprise Edition includes the Extended Stored Procedure DLL Wizard. In principle, it does not do anything extra natural, but only generates a template template for an extended stored procedure. If you like masters, you can use it. I prefer to do everything by hand, and therefore I will not consider this case.
Now to the point:
- Launch Visual C++ and create a new project - Win32 Dynamic Link Library.
- Include a header file in the project - #include
- Go to the Tools => Options menu and add search paths for include and library files. If you did not change anything when installing MS SQL Server, then specify:
C:Program FilesMicrosoft SQL Server80ToolsDevToolsInclude for header files;
- C:Program FilesMicrosoft SQL Server80ToolsDevToolsLib for library files.
- Specify the name of the library file opends60.lib in the linker options.
At this point the preparatory stage is completed, you can start writing your first extended stored procedure.
Formulation of the problem.
Before you start programming, you need to have a clear idea of where to start, what the end result should be, and how to achieve it. So, here's the technical specification:
Develop an extended stored procedure for MS SQL Server 2000 that receives full list users registered in the domain, and returns it to the client in the form of a standard record set. As the first input parameter, the function receives the name of the server containing the catalog database ( Active Directory), i.e. the name of the domain controller. If this parameter is NULL, then a list of local groups must be passed to the client. The second parameter will be used by the extended stored procedure to return the value of the result of successful/unsuccessful operation (OUTPUT parameter). If the extended stored procedure is completed successfully, then it is necessary to pass the number of records returned to the client record set; if during operation it was not possible to obtain the required information, the value of the second parameter must be set to -1, as a sign of unsuccessful completion.
The conditional prototype of an extended stored procedure is as follows:
xp_GetUserList(@NameServer varchar, @CountRec int OUTPUT);
And here is the extended stored procedure template that we need to fill with content:
#include
#include
#define XP_NOERROR 0
#define XP_ERROR -1
__declspec(dllexport) SERVRETCODE xp_GetGroupList(SRVPROC* pSrvProc)
{
//Checking the number of passed parameters
//Checking the type of passed parameters
//Checking if parameter 2 is an OUTPUT parameter
//Checking if parameter 2 is long enough to store the value
//Getting input parameters
//Getting a list of users
// Sending the received data to the client in the form of a standard record set
//Setting the value of the OUTPUT parameter
return (XP_NOERROR);
}
Working with input parameters
In this chapter, I do not want to scatter your attention on extraneous things, but I want to focus it on working with the parameters passed to the extended stored procedure. Therefore, we will somewhat simplify our technical specifications and develop only that part of it that works with input parameters. But first, not much theory
The first action our exteneded stored procedure must perform is to receive the parameters that were passed to it when it was called. Following the above algorithm, we need to perform the following steps:
Determine the number of passed parameters;
- Make sure that the passed parameters have the correct data type;
- Make sure that the specified OUTPUT parameter is of sufficient length to store in it the value returned by our extended stored procedure.
- Receive the passed parameters;
- Set the output parameter values as a result of successful/unsuccessful completion of the extended stored procedure.
Now let's look at each point in detail:
Determining the number of parameters passed to an extended stored procedure
To get the number of parameters passed, you must use the function:
int srv_rpcparams(SRV_PROC * srvproc);
If successful, the function returns the number of parameters passed to the extended stored procedure. If the extended stored procedure was called without parameters, srv_rpcparams will return -1. Parameters can be passed by name or by position (unnamed). In any case, these two methods should not be mixed. An attempt to pass input parameters to a function by name and by position at the same time will result in an error, and srv_rpcparams will return 0.
Determining the data type and length of passed parameters
To obtain information about the type and length of parameters passed, Microsoft recommends using the srv_paramifo function. This generic function replaces the srv_paramtype, srv_paramlen, srv_parammaxlen calls, which are now deprecated. Here is its prototype:
int srv_paraminfo(
SRV_PROC * srvproc,
int n,
BYTE * pbType,
ULONG* pcbMaxLen,
ULONG * pcbActualLen,
BYTE * pbData,
BOOL * pfNull);
pbType specifies the serial number of the parameter. The number of the first parameter starts from 1.
pcbMaxLen pointer to a variable into which the function stores the maximum value of the parameter length. This value is determined by the specific data type of the parameter passed, and we will use it to ensure that the OUTPUT parameter is of sufficient length to store the data being passed.
pcbActualLen pointer to the real length of the parameter passed to the extended stored procedure when called. If passed parameter has zero length and the pfNull flag is set to FALSE then (* pcbActualLen) ==0.
pbData- a pointer to a buffer for which memory must be allocated before calling srv_paraminfo. In this buffer, the function places the input parameters received from the extended stored procedure. The buffer size in bytes is equal to the pcbMaxLen value. If this parameter is set to NULL, no data is written to the buffer, but the function correctly returns the values *pbType, *pcbMaxLen, *pcbActualLen, *pfNull. Therefore, you need to call srv_paraminfo twice: first with pbData=NULL, then, having allocated the required memory size for a buffer equal to pcbActualLen, call srv_paraminfo a second time, passing a pointer to the allocated memory block to pbData.
pfNull pointer to a NULL flag. srv_paraminfo sets it to TRUE if the value of the input parameter is NULL.
Checking if the second OUTPUT parameter is a parameter.
The srv_paramstatus() function is designed to determine the status of the passed parameter:
int srv_paramstatus (
SRV_PROC * srvproc,
int n
);
n is the number of the parameter passed to the extended stored procedure when called. Let me remind you: parameters are always numbered starting from 1.
To return a value, srv_paramstatus uses the zero bit. If it is set to 1 the passed parameter is an OUTPUT parameter, if it is set to 0 normal parameter passed by value. If the exteneded stored procedure was called without parameters, the function will return -1.
Setting the value of the output parameter.
The output parameter passed to the extended stored one can be given a value using the srv_paramsetoutput function. This new feature replaces the call to the srv_paramset function, which is now deprecated because does not support new data types introduced in the ODS API and zero-length data.
int srv_paramsetoutput(
SRV_PROC *srvproc,
int n,
BYTE *pbData,
ULONG cbLen,
BOOL fNull
);
pbData pointer to a buffer with data that will be sent to the client to set the value of the output parameter.
cbLen length of the sent data buffer. If the data type of the parameter passed to OUTPUT specifies constant-length data and does not allow storage of a NULL value (for example, SRVBIT or SRVINT1), then the function ignores the cbLen parameter. cbLen=0 indicates zero-length data, and fNull must be set to FALSE.
fNull set this to TRUE if the return parameter needs to be set to NULL, and cbLen must be 0 or the function will fail. In all other cases fNull=FALSE.
If successful, the function returns SUCCEED. If the return value is FAIL, then the call failed. Everything is simple and clear
Now we know enough to write our first extended stored procedure, which will return a value through the parameter passed to it. Let, according to established tradition, this be the string Hello world! A debug version of the example can be downloaded here.
#include
#define XP_NOERROR 0
#define XP_ERROR 1
#define MAX_SERVER_ERROR 20000
#define XP_HELLO_ERROR MAX_SERVER_ERROR+1
void printError(SRV_PROC*, CHAR*);
#ifdef __cplusplus
extern "C" (
#endif
SRVRETCODE __declspec(dllexport) xp_helloworld(SRV_PROC* pSrvProc);
#ifdef __cplusplus
}
#endif
SRVRETCODE xp_helloworld(SRV_PROC* pSrvProc)
{
char szText = "Hello World!";
BYTE bType;
ULONG cbMaxLen;
ULONG cbActualLen;
BOOL fNull;
/* Determining the number of transferred to the extended storage
parameter procedure */
if (srv_rpcparams(pSrvProc) != 1)
{
printError(pSrvProc, "Invalid number of parameters!");
return (XP_ERROR);
}
/* Obtaining information about the data type and length of the passed parameters */
if (srv_paraminfo(pSrvProc, 1, &bType, &cbMaxLen,
&cbActualLen, NULL, &fNull) == FAIL)
{
printError(pSrvProc,
"Unable to obtain information about input parameters...");
return (XP_ERROR);
}
/* Check if the passed OUTPUT parameter is a parameter */
if ((srv_paramstatus(pSrvProc, 1) & SRV_PARAMRETURN) == FAIL)
{
printError(pSrvProc,
"The passed parameter is not an OUTPUT parameter!");
return (XP_ERROR);
}
/* Check the data type of the passed parameter */
if (bType != SRVBIGVARCHAR && bType != SRVBIGCHAR)
{
printError (pSrvProc, "The type of the passed parameter is not correct!");
return (XP_ERROR);
}
/* Make sure the passed parameter is long enough to store the returned string */
if (cbMaxLen< strlen(szText))
{
printError(pSrvProc,
"The parameter passed is not of sufficient length to store the n returned string!");
return (XP_ERROR);
}
/* Set the value of the OUTPUT parameter */
if (FAIL == srv_paramsetoutput(pSrvProc, 1, (BYTE*)szText, 13, FALSE))
{
printError(pSrvProc,
"I can't set the value of the OUTPUT parameter...");
return (XP_ERROR);
}
srv_senddone(pSrvProc, (SRV_DONE_COUNT | SRV_DONE_MORE), 0, 1);
return (XP_NOERROR);
}
void printError (SRV_PROC *pSrvProc, CHAR* szErrorMsg)
{
srv_sendmsg(pSrvProc, SRV_MSG_ERROR, XP_HELLO_ERROR, SRV_INFO, 1,
NULL, 0, 0, szErrorMsg,SRV_NULLTERM);
Srv_senddone(pSrvProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
}
The functions srv_sendmsg and srv_senddone remained unconsidered. The srv_sendmsg function is used to send messages to the client. Here is its prototype:
int srv_sendmsg (
SRV_PROC * srvproc,
int msgtype,
DBINT msgnum,
DBTINYINT class
DBTINYINT state,
DBCHAR * rpcname,
int rpcnamelen,
DBUSMALLINT linenum,
DBCHAR *message,
int msglen
);
msgnum message number;
class- the severity of the error that occurred. Information messages have a severity value less than or equal to 10;
state The error status number for the current message. This parameter provides information about the context of the error that occurred. Valid values range from 0 to 127;
rpcname is currently unused;
rpcnamelen - currently not used;
linenum here you can specify the line number source code. Based on this value, it will subsequently be easy to determine where the error occurred. If you do not want to use this feature, then set linenum to 0;
message pointer to a string sent to the client;
msglen specifies the length in bytes of the message string. If this string ends with a null character, then the value of this parameter can be set to SRV_NULLTERM.
Return values:
- if successful SUCCEED
- if FAIL fails.
During operation, the extended stored procedure must regularly report its status to the client application, i.e. send messages about completed actions. This is what the srv_senddone function is designed for:
int srv_senddone(
SRV_PROC * srvproc,
DBUSMALLINT status,
DBUSMALLINT info,
DBINT count
);
status status flag. The value of this parameter can be set using logical operators AND and OR for combining the constants given in the table:
Status flag Description
SRV_DONE_FINAL The current result set is final;
SRV_DONE_MORE The current result set is not final; the next batch of data should be expected;
SRV_DONE_COUNT The count parameter contains a valid value
SRV_DONE_ERROR Used to notify when errors occur and terminate immediately.
into reserved, must be set to 0.
count is the number of result sets sent to the client. If the status flag is set to SRV_DONE_COUNT, then count must contain the correct number of recordset sent to the client.
Return values:
- if successful SUCCEED
- if FAIL fails.
Installing extended stored procedures on MS SQL Server 2000
1.Copy the dll library with the extended stored procedure to the binn directory on the machine with MS SQL Server installed. My path is as follows: C:Program FilesMicrosoft SQL ServerMSSQLBinn;
2. Register the extended stored procedure on the server by running the following script:
USE Master
EXECUTE SP_ADDEXTENDEDPROC xp_helloworld, xp_helloworld.dll
Test xp_helloworld by running the following script:
DECLARE @Param varchar(33)
EXECUTE xp_helloworld @Param OUTPUT
SELECT @Param AS OUTPUT_Param
Conclusion
This concludes the first part of my article. Now I am sure you are ready to cope with our technical specifications 100%. In the next article you will learn:
- Data types defined in the ODS API;
- Features of debugging extended stored procedures;
- How to create recordsets and transfer them to the client application;
- We will partially consider the Active Directory Network Manegment API functions necessary to obtain a list of domain users;
- We will create a finished project (we will implement our technical specifications)
I hope - see you soon!
PS: download example files for the article for Studio 7.0
Goal of the work– learn to create and use stored procedures on the database server.
1. Work through all the examples, analyze the results of their execution in the SQL Server Management Studio utility. Checking the presence of created procedures in the current database.
2. Completing all examples and tasks during laboratory work.
3. Completing individual tasks according to options.
Explanations for performing the work
To learn stored procedure programming, we use an example database called DB_Books, which was created in laboratory work No. 1. When completing examples and tasks, pay attention to the correspondence of the names of the database, tables and other project objects.
Stored procedures are a set of commands consisting of one or more SQL statements or functions and stored in a database in compiled form.
Types of Stored Procedures
System stored procedures are designed to perform various administrative actions. Almost all server administration activities are performed with their help. We can say that system stored procedures are an interface that provides work with system tables. System stored procedures are prefixed with sp_, are stored in the system database, and can be called in the context of any other database.
Custom stored procedures implement certain actions. Stored procedures are a full-fledged database object. As a result, each stored procedure is located in a specific database, where it is executed.
Temporary stored procedures exist only for a short time, after which they are automatically destroyed by the server. They are divided into local and global. Local temporary stored procedures can only be called from the connection in which they are created. When creating such a procedure, you must give it a name that begins with a single # character. Like all temporary objects, stored procedures of this type are automatically deleted when the user disconnects or the server is restarted or stopped. Global temporary stored procedures are available to any connection from a server that has the same procedure. To define it, just give it a name starting with the characters ##. These procedures are deleted when the server is restarted or stopped, or when the connection in the context in which they were created is closed.
Creating, modifying stored procedures
Creating a stored procedure involves solving the following problems: planning access rights. When you create a stored procedure, be aware that it will have the same access rights to database objects as the user who created it; defining the parameters of a stored procedure; stored procedures can have input and output parameters; stored procedure code development. The procedure code can contain a sequence of any SQL commands, including calls to other stored procedures.
The syntax of the operator for creating a new or changing an existing stored procedure in MS SQL Server notation:
( CREATE | ALTER ) PROC[ EDURE] procedure_name [ ;number] [ ( @parameter_name data_type ) [ VARYING ] [ = DEFAULT ] [ OUTPUT ] ] [ ,... n] [ WITH ( RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION ) ] [ FOR REPLICATION] AS sql_statement [ ... n]Let's look at the parameters of this command.
Using the prefixes sp_, #, ##, the created procedure can be defined as a system or temporary one. As you can see from the command syntax, it is not allowed to specify the name of the owner who will own the created procedure, as well as the name of the database where it should be located. Therefore, to place the stored procedure you are creating in a specific database, you must issue the CREATE PROCEDURE command in the context of that database. When accessing objects of the same database from the body of a stored procedure, you can use shortened names, i.e., without specifying the database name. When you need to access objects located in other databases, specifying the database name is mandatory.
To pass input and output data, the parameter names in the stored procedure you create must begin with the @ character. You can specify multiple parameters in a single stored procedure, separated by commas. The body of a procedure should not use local variables whose names coincide with the names of the parameters of this procedure. Any SQL data type, including user-defined ones, is suitable for determining the data type of stored procedure parameters. However, the CURSOR data type can only be used as the output parameter of a stored procedure, i.e. specifying the OUTPUT keyword.
The presence of the OUTPUT keyword means that the corresponding parameter is intended to return data from a stored procedure. However, this does not mean that the parameter is not suitable for passing values to a stored procedure. Specifying the OUTPUT keyword instructs the server, when exiting a stored procedure, to assign the current parameter value to the local variable that was specified as the parameter value when the procedure was called. Note that when specifying the OUTPUT keyword, the value of the corresponding parameter when calling the procedure can only be set using a local variable. Any expressions or constants that are allowed for normal parameters are not permitted. The VARYING keyword is used in conjunction with the OUTPUT parameter, which is of type CURSOR. It specifies that the output will be the result set.
The DEFAULT keyword represents the value that the corresponding parameter will take by default. Thus, when calling a procedure, you do not have to explicitly specify the value of the corresponding parameter.
Since the server caches the query execution plan and compiled code, the next time the procedure is called, the ready-made values will be used. However, in some cases it is still necessary to recompile the procedure code. Specifying the RECOMPILE keyword instructs the system to create an execution plan for the stored procedure each time it is called.
The FOR REPLICATION parameter is required when replicating data and including the created stored procedure as an article in a publication. The ENCRYPTION keyword instructs the server to encrypt the stored procedure code, which can provide protection against the use of proprietary algorithms that implement the stored procedure. The AS keyword is placed at the beginning of the stored procedure body itself. Almost everything can be used in the body of the procedure SQL commands, declare transactions, set locks, and call other stored procedures. You can exit a stored procedure using the RETURN command.
Removing a Stored Procedure
DROP PROCEDURE (procedure_name) [,...n]
Executing a Stored Procedure
To execute a stored procedure, use the command: [ [ EXEC [ UTE] procedure_name [ ;number] [ [ @parameter_name= ] ( value | @variable_name) [ OUTPUT ] | [ DEFAULT ] ] [ ,... n]
If the stored procedure call is not the only command in the batch, then the EXECUTE command is required. Moreover, this command is required to call a procedure from the body of another procedure or trigger.
The use of the OUTPUT keyword when calling a procedure is allowed only for parameters that were declared when the procedure was created with the OUTPUT keyword.
When the DEFAULT keyword is specified for a parameter when calling a procedure, the default value will be used. Naturally, the specified word DEFAULT is allowed only for those parameters for which a default value is defined.
The syntax of the EXECUTE command shows that parameter names can be omitted when calling a procedure. However, in this case, the user must specify the values for the parameters in the same order in which they were listed when creating the procedure. You cannot assign a default value to a parameter by simply omitting it during enumeration. If you want to omit parameters that have a default value, it is enough to explicitly specify the parameter names when calling the stored procedure. Moreover, in this way you can list parameters and their values in any order.
Note that when calling a procedure, either parameter names with values, or only values without a parameter name are specified. Combining them is not allowed.
Using RETURN in a stored procedure
Allows you to exit the procedure at any point according to a specified condition, and also allows you to convey the result of the procedure as a number, by which you can judge the quality and correctness of the procedure. An example of creating a procedure without parameters:
CREATE PROCEDURE Count_Books AS SELECT COUNT (Code_book) FROM Books GOExercise 1.
EXEC Count_BooksCheck the result.
An example of creating a procedure with an input parameter:
CREATE PROCEDURE Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages GOTask 2. Create this procedure in the Stored Procedures section of the DB_Books database using the SQL server Management Studio utility. Run it using the command
EXEC Count_Books_Pages 100Check the result.
An example of creating a procedure with input parameters:
CREATE PROCEDURE Count_Books_Title @Count_pages AS INT , @Title AS CHAR (10 ) AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GOTask 3. Create this procedure in the Stored Procedures section of the DB_Books database using the SQL server Management Studio utility. Run it using the command
EXEC Count_Books_Title 100 , "P%"Check the result.
An example of creating a procedure with input parameters and an output parameter:
CREATE PROCEDURE Count_Books_Itogo @Count_pages INT , @Title CHAR (10 ) , @Itogo INT OUTPUT AS SELECT @Itogo = COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GOTask 4. Create this procedure in the Stored Procedures section of the DB_Books database using the SQL server Management Studio utility. Run using the command set:
Sql> Declare @q As int EXEC Count_Books_Itogo 100, "P%", @q output select @q
Check the result.
An example of creating a procedure with input parameters and RETURN:
CREATE PROCEDURE checkname @param INT AS IF (SELECT Name_author FROM authors WHERE Code_author = @param) = "Pushkin A.S." RETURN 1 ELSE RETURN 2Task 5. Create this procedure in the Stored Procedures section of the DB_Books database using the SQL server Management Studio utility. Run it using the commands:
DECLARE @return_status INT EXEC @return_status = checkname 1 SELECT "Return Status" = @return_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 using the command
EXEC update_procAn example of a procedure with an input parameter to obtain all information about a specific author:
CREATE PROC select_author @k CHAR (30 ) AS SELECT * FROM Authors WHERE name_author= @kTask 7.
EXEC select_author "Pushkin A.S." or select_author @k= "Pushkin A.S." or EXEC select_author @k= "Pushkin A.S."An example of creating a procedure with an input parameter and a default value to increase the value of a key field in the Purchases table by a specified number of times (2 times by default):
CREATE PROC update_proc @p INT = 2 AS UPDATE Purchases SET Code_purchase = Code_purchase * @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. Run it using the commands:
EXEC update_proc 4 or EXEC update_proc @p = 4 or EXEC update_proc --the default value will be used.An example of creating a procedure with input and output parameters. Create a procedure to determine the number of orders completed during a specified period:
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. Run it using the commands:
DECLARE @c2 INT EXEC count_purchases '01-jun-2006', '01-jul-2006', @c2 OUTPUT SELECT @c2Options for tasks laboratory work №4
General provisions. In SQL Server Management Studio, create new page for the code (the “Create request” button). Programmatically make the created DB_Books database active using the Use statement. Create stored procedures using Create procedure statements, and define the names of the procedures yourself. Each procedure will execute one SQL query that was executed in the second lab. Moreover, the SQL code of the queries needs to be changed so that they can transmit the values of the fields used to search.
For example, the initial task and request in laboratory work No. 2:
/*Select from the supplier directory (Delivery table) the names of companies, telephone numbers and INN (fields Name_company, Phone and INN), whose company name (field Name_company) is “OJSC MIR”.
SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = "OJSC MIR"*/ –In this work the following procedure will be created:
CREATE PROC select_name_company @comp CHAR (30 ) AS SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = @comp–To start the procedure, use the command:
EXEC select_name_company "JSC MIR"Task list
Create a new program in SQL Server Management Studio. Programmatically make active the individual database created in laboratory work No. 1 using the Use statement. Create stored procedures using Create procedure statements, and define the names of the procedures yourself. Each procedure will perform one SQL query, which are presented in the form of separate tasks according to options.
Option 1
1. Display a list of employees who have at least one child.
2. Display a list of children who received gifts during the specified period.
3. Display a list of parents who have minor children.
4. Display information about gifts with a value greater than the specified number, sorted by date.
Option 2
1. Display a list of devices with the specified type.
2. Display the number of devices repaired and the total cost of repairs from the specified technician.
3. Display a list of device owners and the number of their requests, sorted by the number of requests in descending order.
4. Display information about craftsmen with a rank greater than the specified number or with a hiring date less than the specified date.
Option 3
2. Display a list of sales codes that sold flowers for an amount greater than the specified number.
3. Display the date of sale, amount, seller and flower according to the specified sale code.
4. Display a list of flowers and the variety for flowers with a height greater than the specified number or blooming.
Option 4
1. Display a list of medications with the specified indication for use.
2. Display a list of delivery dates for which more than the specified number of the drug of the same name was sold.
3. Display the delivery date, amount, full name of the manager from the supplier and the name of the medicine by receipt code greater than the specified number.
Option 5
2. Display a list of decommissioned equipment for the specified reason.
3. Display the date of receipt, name of the equipment, full name of the person responsible and the date of write-off for equipment written off during the specified period.
4. Display a list of equipment with the specified type or with a receipt date greater than certain value
Option 6
1. Display a list of dishes with a weight greater than the specified number.
2. Display a list of products whose names contain the specified word fragment.
3. Display the volume of the product, the name of the dish, the name of the product with the dish code from the specified initial value to a certain final value.
4. Display the order of preparation of a dish and the name of a dish with the amount of carbohydrates greater than a certain value or the amount of calories greater than a specified value.
Option 7
1. Display a list of employees with the specified position.
3. Display the registration date, document type, full name of the registrar and name of the organization for documents registered during the specified period.
4. Display a list of registered documents with a specific document type or with a registration date greater than the specified value.
Option 8
1. Display a list of employees with the specified reason for dismissal.
3. Display the date of registration, reason for dismissal, full name of the employee for documents registered during the specified period.
Option 9
1. Display a list of employees who took leave of the specified type.
2. Display a list of documents with a registration date in the specified period.
3. Display the date of registration, type of leave, full name of the employee for documents registered during the specified period.
4. Display a list of registered documents with a document code in the specified range.
Option 10
1. Display a list of employees with the specified position.
2. Display a list of documents whose content contains the specified word fragment.
3. Display the registration date, document type, full name of the sender and name of the organization for documents registered during the specified period.
4. Display a list of registered documents with the specified document type or with a document code less than a certain value.
Option 11
1. Display a list of employees assigned to the specified position.
2. Display a list of documents with a registration date in the specified period.
3. Display the registration date, position, full name of the employee for documents registered during the specified period.
4. Display a list of registered documents with a document code in the specified range.
Option 12
3. Display a list of people who rented equipment and the number of their requests, sorted by the number of requests in descending order.
Option 13
1. Display a list of equipment with the specified type. 2. Display a list of equipment that has been written off by a specific employee.
3. Display the amount of decommissioned equipment, grouped by equipment type.
4. Display information about employees with a hire date greater than a certain date.
Option 14
1. Print a list of flowers with the specified leaf type.
2. Display a list of receipt codes for which flowers were sold for amounts greater than a certain value.
3. Display the receipt date, amount, name of the supplier and colors by a specific supplier code.
4. Display a list of flowers and the variety for flowers with a height greater than a certain number or blooming.
Option 15
1. Display a list of clients who checked into the rooms during the specified period.
2. Display the total amount of payments for rooms for each client.
3. Display the arrival date, room type, full name of clients registered during the specified period.
4. Display a list of registered clients in rooms of a certain type.
Option 16
1. Display a list of equipment with the specified type.
2. Display a list of equipment that was rented by a specific client.
3. Display a list of people who rented equipment and the number of their requests, sorted by the number of requests in descending order.
4. Display information about clients sorted by address.
Option 17
1. Display a list of valuables with a purchase price greater than a certain value or a warranty period greater than a specified number.
2. Display a list of locations of material assets whose names contain the specified word.
3. Display the sum of the value of values with a code in the specified range.
4. Display a list of financially responsible persons with the date of employment in the specified range.
Option 18
1. Display a list of repairs performed by a specific technician.
2. Display a list of work stages included in the work whose title contains the specified word.
3. Display the sum of the cost of repair work stages for work with a code in the specified range.
4. Display a list of foremen with the hiring date in the specified range.
Option 19
1. Display a list of medications with a specific indication.
2. Display a list of receipt numbers for which more than a certain number of drugs were sold.
3. Display the date of sale, amount, cashier’s name and medicine on the receipt with the specified number.
4. Display a list of medications and units of measurement for medications with a quantity in the package greater than the specified number or a drug code less than a certain value.
Option 20
1. Display a list of employees with the specified position.
2. Display a list of documents whose content contains the specified word fragment.
3. Display the registration date, document type, full name of the executor and the fact of execution for documents registered during the specified period.
4. Display a list of registered documents with the specified document type or document code in a certain range.
Include a line in your procedures - SET NOCOUNT ON:
With each DML expression, SQL server carefully returns us a message containing the number of processed records. This information It may be useful to us while debugging the code, but after that it will be completely useless. By writing SET NOCOUNT ON, we disable this function. For stored procedures containing multiple expressions or/and loops, this action can give a significant performance increase, because the amount of traffic will be significantly reduced.
Transact-SQL
Use the schema name with the object name:
Well, I think it’s clear. This operation tells the server where to look for objects and instead of randomly rummaging through his bins, he will immediately know where he needs to go and what to take. With a large number of databases, tables and stored procedures, it can significantly save our time and nerves.
Transact-SQL
SELECT * FROM dbo.MyTable --Doing it this way is good -- Instead of SELECT * FROM MyTable --And doing it this way is bad --Calling the EXEC dbo.MyProc procedure --Good again --Instead of EXEC MyProc --Bad!
Don't use the "sp_" prefix in the name of your stored procedures:
If our procedure name starts with "sp_", SQL Server will look in its main database first. The fact is that this prefix is used for personal internal stored procedures of the server. Therefore, its use may lead to additional costs and even incorrect results if a procedure with the same name as yours is found in its database.
Use IF EXISTS (SELECT 1) instead of IF EXISTS (SELECT *):
To check for the existence of a record in another table, we use the IF EXISTS statement. This expression returns true if at least one value is returned from the internal expression, it doesn’t matter “1”, all columns or the table. The returned data is basically not used in any way. Thus, to compress traffic during data transmission, it is more logical to use “1”, as shown below.
Loading...
