Save procedures in SQL. Vidalennya procedures that are taken. RETURN link to procedures that are saved

If there are following procedures to be saved, and if I am guilty of victorizing a statement in SQL Server?

Let me allow you to create dynamic drinks, how can we improve the parameters?

Which one of them is the most visible, and which one is the best, and which one is the least?

Look over or the procedures that are being saved, are they constantly saving memory?

What does it mean, let's say, that statements create a virtual table and procedures create a table of materials?

Be kind, let me know about the dots, yakscho stink.

Solutions Collecting From Web of "Why is there a difference between procedures, savings, and taxes?"

View є virtual table. You can come up to the number of tables in the submission and win the application for the submission of data, so the data were found in one table.

The procedure for selecting the parameters for the selected function has been saved ... so that the insertion of data or rotation of the other values ​​or the set of data is updated.

Created notifications and procedures that are being saved - to avenge the information from Microsoft about those, if and why win them.

Let's say I have two tables:

tbl_user Stovptsi: .user_id, .user_name, .user_pw

tbl_profile Stowpci: .profile_id, .user_id .profile_description

So, as I am trying to fetch these tables ALOT ... instead of that, to work in the CLEAR peice sql, I would use a look, for example:

CREATE View vw_user_profile AS Select A.user_id, B.profile_description FROM tbl_user A left join tbl_profile B on A.user_id = b.user_id GO

Therefore, in the future, I want to request a profile_description for the id of the koristuvach ... everything that I need to rob,

SELECT profile_description FROM vw_user_profile WHERE user_id = @ID

This code can be tweaked in the procedure that is saved, for example:

Create procedure dbo.getDesc @ID int AS begin SELECT profile_description FROM vw_user_profile WHERE user_id = @ID END GO

For that I can phone

Dbo.getDesc 25

and I remove the description for the ID of the koristuvach 25 de 25 - your parameter.

It is obvious that BAGATO is more, but the main idea is less.

On the back, you need to understand that insults are different speeches. The savings of procedures are more victorious for INSERT-UPDATE-DELETE statements. Those submissions are vindicated for SELECT statements. and you are guilty of victorious resentment.

You cannot change data at the vistas.

Take a look: This is a virtual table that is made up of one or more rows and columns from different real data base tables. Tse template rowk_v and stovpts_v k_lkoh table. You cannot pass any parameters here.

Save procedures: use a set of SQL-statements ahead of time, for which you can edit parameters like input data and remove output data.

Appearances can be featured in a procedure that is saved, but a procedure that is saved cannot be featured in Views…!

The shovischa procedure is victorious if plain SQL is simply not enough. Procedures for saving should be changed, cycles and cycles of other procedures that are saved. Tse mova programming, not mova zapitіv.

Appearances are static. Think of them as if they were new tables with a simple layout, and the data in them are being created on a whim, with a vicarious note, with which you have created everything. As in any SQL table, you can sort and filter by WHERE , GROUP BY and ORDER BY .

Tse to lay aside the fact that you work.

Tse to lay in the base of data. Sorry, just run the query and filter the result. And yet, data bases, like Oracle, allow you to create "materialization" statements, like in the main table, as they are automatically updated when you change the basic data type.

The materialization of the statement allows you to create indexes in the columns of the statement (especially on the calculated columns, which are not found anywhere in the data base).

I don’t understand what you say.

The main difference lies in the fact that if you request a statement, the designation is inserted into your request. The procedure can give results, but it's compiled and so fast. The second option is to index the notice.

SQL View is a virtual table based on the SQL SELECT query. Submissions are submitted for one or more data base tables or for other applications. This mittєvy sign of the database, just like the procedure that is taken, is a group of Transact-SQL statements, folded into a single plan of execution.

Pereglyad is a simple demonstration of data that is saved in data base tables, just like a procedure that is saved by a group of operators, which can be viconated.

Filed more, oscillki vodobrazhae data from the table, on how to ask, just like the procedure of the collection of sql-instructions.

Reverse this article: Reviewing the procedures that are being saved. The very ones that you are whispering

@Patrick is correct when you say that, well, you need to look at your other food, create a View for yourself in memory, and in the fallow type Joins, Data and even if the aggregation will be broken down, you can still end up looking hungry.

Save the procedure to save all your work either with the Temp Hash Table substitutions, for example #tmpTable1, or in memory of @tmpTable1 for help. Zalezhno v_d of what you want to say.

The procedure that is taken is similar to a function, but is called by direct names. zamіst funktsіy, yakі in fact vikoristovuyutsya in the middle of the request.

Obviously, most of the time the tables are in memory, because you don’t take a lot of money.

Mahesh is not right, if he allows wine, you cannot change the tribute from the tribute. Father, look at Patrick

CREATE View vw_user_profile AS Select A.user_id, B.profile_description FROM tbl_user A left join tbl_profile B on A.user_id = b.user_id

I can update data ... like a butt, I can work it out whether it’s a cich ...

Update vw_user_profile Set profile_description="Manager" where user_id=4

Update tbl_profile Set profile_description="Manager" where user_id=4

You can't insert it into the declaration, since not all fields in the entire table are present, and I'm assuming that PROFILE_ID is the primary key and can't be NULL. However, you can also insert an INSERT at the vistavu...

I created an application for the main table, vicorist ...

Create View Junk like SELECT * view

Insert in junk (Code,name) values ​​("glyn","Glyn Roberts"), ("Mary","Maryann Roberts")

DELETE from Junk Where ID>4

І INSERT, і DELETE worked at the same point

Obviously, you can't change whether fields are, whether they are aggregations, or whether they are secured, or whether they are statements, if they are just direct statements, they can be changed.

If you have seen more than one table, you cannot insert or see, but if you have seen more than one table, you can sound.

In addition to pointing more comments, I would like to add some respect about Views.

1. Gifts can be victorious for prikhovuvannya folding. Show your own scenario, in which 5 people work on the project, and only one of them is already a team with a data base, for example, collapsible associations. In such a scenario, wines can be created, which can be easily requested by other members of the team, and stink shards query one table.
2. Security can be easily implemented Views. Let's say we spіvrobіtnik tables The salary , SSN number. Citizens are not guilty of looking for coristuvachs, who are not allowed to look at them. In this way, we can create a notification, such as choosing the columns in the table, as not authorizing, such as im'ya , vіk ta etc., without giving conflicting remarks (for example, about salary, etc., about how we were guessed earlier). Now we can see allowed for direct request to tables employee and just save it for reading from the subject. In this way, we can realize security for help Views.

Voice procedure

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 specify the number of cursors, so that the procedure can be closed after the procedure is returned.

Notes
It is not recommended to change a lot of parameters in procedures that are saved (for example, in front of great numbers and character strings) through revantaging the stack. In practice, in the basic dialects of Transact-SQL, PL/SQL and Informix, it is possible to maintain consistency with the standard, as in the voiced and different parameters, the voiced changes, and in the common program. Microsoft recommends stopping the next approximation to evaluate the Cache size of the procedures that are saved:
\u003d (maximum number of one-hour working coristuvachiv) * (experiment of the largest plan of attendance) * 1.25. Depending on the plan, the sides can be expanded with the help of the command: DBCC MEMUSAGE.

Viklik procedures

For rich DBMSs, the list of procedures that are saved, are used for the assistance of the operator:

EXECUTE PROCEDURE [(][)]

Note: Quickly save procedures can be saved with programs, other procedures that can be saved, or in interactive mode.

An example of a voicing procedure

CREATE PROCEDURE Proc1 AS // stun the procedure
DECLARE Cur1 200 // stun the cursor
OPEN Cur1 // open the cursor
FETCH NEXT FROM Cur1 //read data from cursor
WHILE @@Fetch_Status=0
BEGIN
FETCH NEXT FROM Cur1
END
CLOSE Cur1 // close cursor
DEALLOCATE Cur1
EXECUTE Proc1 // run the procedure

Polymorphism
Two subprograms with the same name can be created in one and the same scheme, as the parameters of these two subprograms are in such a world one type of one, so that they can be separated. In order to differentiate between two subprograms with the same names in one scheme, the skin of them is given an alternative and unique name (specific name). Such im'ya can be assigned, if a subprogram is being developed. When calling a subprogram for the presence of a number of the same names, the designation of the required subprograms is charged for a small amount of time:
. More often, all procedures are assigned from assigned names, and if there are no such, then all functions from given names.
. For a deeper analysis, these subprograms are abandoned, hundreds of such dani- tals can be privileged for vikonannya (EXECUTE).
. Їх choose ті, yakі mayut the number of parametrіv in vіdpodіdaє number of arguments vikliku. The assigned data types and parameters of those positions are reviewed.
. If more than one subprogram has been lost, the one with the shortest qualification is chosen.
In Oracle practice, polymorphism is allowed for functions that are only exposed in packages, [email protected]- In other schemes, and in Sybase and MS SQL Server, the focus is blocked.

Vidalennya and change of procedures
For the remote procedure, the operator is used:

To change the procedure, the operator is selected:

ALTER PROCEDURE [([{IN|OUT|INOUT}])]
BEGIN [ATOMIC]

END

Privileges of vikonannya procedures

GRANT EXECUTE ON TO |PUBLIC [WITH GRANT OPTION]

System procedures
A rich DBMS (including SQL Server) may have the same set of system procedures, so you can tweak it for your own purposes.

Saving procedures

The subject of this distribution is one of the most important tools, introduced to retailers by addenda of InterBase databases for the implementation of business logic. and change the amount of code required for setting up tasks It is practically impossible to complete a database program without changing the procedures that are saved.
While there is a wide choice of procedures to be saved that are important for most relational DBMSs, InterBase procedures can play the role of practically complete datasets, allowing them to rotate results in specific SQL queries.
Rosobniks, pushing often, learning the soberezhezhe procedures just yak nabir of specific sql-zapitiv, yaki shit diligently diligent, and the prazuvati is a mile of the time, and the real-handedly high level.
So what are the procedures that are stored in InterBase?
The procedure that is saved (CP) is a part of the data base metadata, which is compiled from the internal InterBase application subprogram, written in a special language, the compiler that is inserted into the core of the InteiBase server
Saving a procedure can be called from client programs, from triggers and other procedures that are saved. The procedure is saved in the middle of the server process and you can manipulate the data in the database, as well as turn the client on how to click (those trigger, HP, addendum) the results of your win
The basis of the hard possibilities laid down by HP is procedural language programming, which can be done in its own warehouse, such as modifying propositions of great SQL, such as INSERT, UPDATE and SELECT, as well as the organization of the analysis and cycles (IF, WHILE), as well as the and faulty situations Mova procedures that are saved allow the implementation of collapsible algorithms for working with data, and the focus on work with relational data of HP is significantly more compact for similar procedures with traditional language.
It should be noted that the trigger is victorious with the very same programming, the crime is low in features and the border. Vіdmіnnostі pіdmіnnostі pіdnіzhini movi, scho vikoristovuєєtsya in trigery, vіd mivi KHP reportedly reviewed in the "Trigeri" section (part 1).

An example of a simple procedure that is saved

The hour has come to create the first procedure, which is saved, and on the її butt, the process of creating saving procedures. Ale for the beginning of the next to say a few words about those, how to practice with the procedures that are saved. InterBase documentation recommends creating procedures for additional files in SQL scripts, which will clean up the text of the CP, as they are submitted to the input of the isql interpreter, and in this way, create that modification of the CP, as in this SQL script at the stage of compiling BLR into the text of the BLR procedure (about the BLR procedure text see Rozdіl "Structure of databases InterBase" (part 4)) winkle pardon, then isql will show you about those, on which row of the SQL script file winkla tsya pardon. Correct the pardon and repeat everything back to back. About nagodzhennya in the daily comprehension of the word, that is about trasuvannya vykonannya, with the possibility of marveling at the changing meanings of the change, you can’t go out of your way. It is obvious that such a pidhid does not match the increase in the privability of procedures that are saved in the eyes of the retailer.
Prote, cream of the standard minimalist approach to the development of HP<_\ществ\ют также инструменты сторонних разработчиков, которые делают работу с хранимыми процедурами весьма удобной Большинство универсальных продуктов для работы с InterBase, перечисленных в приложении "Инструменты администратора и разработчика InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем обязательно воспользоваться одним из этих инструментов для работы с хранимыми процедурами и изложение материала будем вести в предположении, что у вас имеется удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
The syntax of the procedures that are saved is described by the next step:

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> = (statement;)

Seemingly dosit large and can be cumbersome, but really everything is even simpler.
Again, the axis of the butt is a simple procedure that is taken, as it takes two numbers as input, adding them up and turning 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

Well, everything is simple: after the CREATE PROCEDURE command, the name of the newly created procedure (which may be unique at the data base boundaries) is specified - for this SP_Add drop, then for the arms, through which the input parameters of the HP type are reset - first_arg and second_arg - from the assigned values .
The list of input parameters in the non-language part of the CREATE PROCEDURE statement - skips are traversed, if all data for its work procedure is taken for additional input to the table in the middle of the procedure body.

In the procedures that are saved, there are scalar data types InteiBase

Give the key word RETURNS, after which the parameters are re-arranged in the arms, which are rotated according to the assigned types - in this category only one - Result.
How the procedure is not guilty of rotating the parameters, the word RETURNS and the list of parameters to be rotated, daily.
After the RETURNSQ keyword AS was specified. Before the AS keyword title, and after a new one - techo procedures.
The body of the procedure, which is taken, is a copy of the descriptions of internal (local) changes (like stinks, we will look at it below), which are divided by a dot after a coma (;), and a block of operators, places in the operator arc BEGIN END. In this case, the body of HP is even simpler - we ask you to add two input arguments and attach the result to the output one, and then call the SUSPEND command. Trohi pіznіshe roz' it is clear the essence of the di ї tsієї command, but for the time being it is significant that it is necessary for the transmission of parameters that turn there, the stars of the bula viklikan saved the procedure.

Variations in procedures that are saved

Respect that the operator in the middle of the procedure will end with a spot with a coma (;). Apparently, the point with which the standard SQL command distributor is out is a signal to the SQL interpreter that the text of the introduction command is correct and needs to be processed. Chi not see so, scho, showing a speck with a coma in the middle of HP, the SQL interpreter recognizes that the command was entered exactly and tries to viconate part of the procedure, what is taken? Tse pripuschennya not maє sensu. For example, if you create a file, in which case you can write guidance, add a command to start from the data base and try the SQL script for the help of the isql interpreter, then the pardon will be turned, it will turn out to be unsuitable, at the thought of the interpreter, the end of the command the creation of procedures that are taken. In order to save procedures for additional SQL script files, without using specialized tools of the InterBase roamer, it is necessary before the skin command to create HP (the same goes to triggers) inspire yoga. The isql command, which changes the SQL proposition splitter, looks like this:

SET TERM

For a typical way of creating a procedure, what is taken care of, looks like this:

SET TERM ^;
CREATE PROCEDURE some_procedure
... . .
END
^
SET TERM ;^

Weekly saving procedures

Let's turn to our procedure, what to take care of. Now, if it has been created, it is required to call, pass the parameters and take the results that are rotated. It’s even easier to do this - it’s enough to write an SQL query for an offensive look:

SELECT *
FROM Sp_add (181.35, 23.09)

Tsei asks us to turn one row to us, which will take away only one Result field, in which the sum of numbers 181.35 and 23.09, then 204.44 will be changed.
In this way, our procedure can be hacked in special SQL queries, which are hacked like in client programs, as well as in other HP or triggers. This variation of our procedure was made possible by adding the SUSPEND command to the end of the procedure to be taken.
On the right, in InterBase (and in all its clones) there are two types of procedures that are saved: selectable procedures and executable procedures. The fact that the robots have two types of HP is related to the fact that selection procedures sound to rotate an impersonal set of input parameters grouped in a row, as if they could look at a set of data, and viconation procedures can either not rotate the parameters, or they can rotate them alone in Returns, de one row of parameters. Selected procedures are called next to SELECT queries, and the procedures that are called follow the help of the EXECUTE PROCEDURE command.
Having offended seeing the procedures that are saved, the same syntax of creation and formally do not challenge anything, then whether the procedure can be used in the SELECT query and whether it is a selection procedure, for the help of EXECUTE PROCEDURE. The nutrition is in how CP behaves with different types of wickedness. In other words, the difference is different in the design of the procedure for the singing type of weekly. So the selection procedure is created specifically for the query query SELECT, and the procedure that is selected is created specifically for the query query EXECUTE PROCEDURE. Let's take a look at what powers are considered when designing these two types of HP.
To understand, how to practice the procedure-vibration, to get a little bit into the theory. Let's get a simple SQL query like SELECT ID, NAME FROM Table_example. As a result of this, we take into account the output table, which consists of two columns (ID and NAME) and the same number of rows (equal number of rows for the Table_example table). Turned into the results of this query, the table is also called a SQL dataset. . Then the server reads the skin record, which submits the results of the query, selects the required fields from it (for example, ID and NAME) and replies to the client. Let's repeat the process again - and so for the skin vibrated record.
All the steps are necessary for a smart reader to understand that all SQL datasets are formed in a row, including in the procedures that are saved! І the main feature of procedures-selections of types of procedures, which are based on the fact that the first ones were designed for turning faceless rows, and others - only for one. That's why the stench zastosovyatsya in a different way: the procedure-selection calls for the help of the SELECT command, like "wimage" in the procedure to check all the records, so you can turn it around. The procedure, which is called, calls for help EXECUTE PROCEDURE, like "wiymaє" from HP only one row, and reshta (navit that stinks!) Ignore.
Let's take a look at the example of the selection procedure, so that it becomes clearer. For > Forgiveness, let's save the procedure, yak pratsyuє so itself, yak ask SELECT ID, NAME FROM Table_Example, so you won't be able to select fields ID і NAME z usієї tables. Axis tsey butt:

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 procedure called Simple_Select_SP. As a matter of fact, there are no input parameters and two output parameters – ID and NAME. Naytsіkavіshe, zvichayno, polagaє in tіlі procedure. Here is the FOR SELECT construct:

FOR
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
DO
BEGIN

/* we need to change procID and procName */

END

This little piece of code means a step: for the skin row, selected from the Table_example table, select the value of the change procID and procName, and then add the value of the change.
You can work out the appearance and request: "Change? How else change 9" If it's similar to the surprise of the difference - those who are in the procedures that are saved, we can win the change. In the language of HP, you can decipher as a local change in the middle of the procedure, and beat the input and output parameters as changed.
In order to voice the local change in the procedure that is being saved, it is necessary to include a description after the AS keyword and before the first word BEGIN. The description of the local change looks like this:

DECLARE VARIABLE ;

For example, in order to announce the length of the local change Mylnt, it is necessary to insert between AS and BEGIN offensive description

DECLARE VARIABLE MyInt INTEGER;

Changes in our butt are made from two-pieces. The reason is that it is necessary to change them in the middle of the SQL-command FOR SELECT, to separate the fields in the tables that are selected in SELECT, and to change them, it is necessary to transfer the rest of the doubles. Even if you change, you can name the same name, like the fields in the tables!
Ale dvokrapka in front of the name of the change is necessary to win only in the middle of SQL queries. Pose with the texts of the beast to fight to the death without a dvokrapka, for example:

procName="Some name";

Let's turn to the body of our procedure. The proposition FOR SELECT rotates the data over the visual tables - a set of data, and, one row at a time. The skin field that is rotated can be placed in its own change: ID => procID, NAME => procName. In part of the DO and changes are asked to the client, who called the procedure> p>, for the help of the SUSPEND command
In this manner, the FOR SELECT... DO command organizes a loop of records that are selected in the SELECT part of the command. At the same cycle, which is part of the DO, the transfer of the drafted record to the client for the help of the SUSPEND command is consummated.
Also, the procedure-selection is recognized for the rotation of one or more rows, for which a cycle is organized in the middle of the HP body, which will restore the resulting parameter changes. For example, the SUSPEND command should be used for the last cycle of the cycle, as if turning the row of data to the client.

Cycles and Operators

Crim FOR SELECT... DO command, which organizes a cycle from the records of any choice, another type of cycle - WHILE...DO, which allows you to organize a cycle on the basis of a re-verification of any minds. Axis stock HP, scho vikoristovu cycle WHILE. DO. This procedure rotates the squares of integer numbers 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 the SELECT FROM QUAD query, we need a table to replace one row of QUADRAT, which will have squares of integers from 1 to 99
To enumerate the results of the SQL-vibration and the classic cycle, in the move procedures that are saved, the IF...THEN..ELSE operator is selected, which allows you to organize the delimitation in the fallacy in the fallback in the future, whether or not the syntax is similar to the majority of the decompression operators in moves programming of high level, on Pascal's square and Cі.
Let's take a look at the foldable example of the procedure that is being saved, so as to rob one's feet.

1. Calculate the average price in the table Table_example (div. division "Tables Primary Keys and Generators")
2. Dali for the skin record in the table to start re-checking, as the main price (PRICE) is higher than the average price, then set the price equal to the value of the average price, plus set the fixing of the water
3. If the price is lower or more than the average price, then I will set the price equal to the total price, plus half the retail price between that average price.
4. Rotate all row changes in the table.

For the cob, the name of the HP is significant, as well as the input and output parameters. Everything is written in the header of the procedure, which is saved

CREATE PROCEDURE IncreasePrices (
Percent2Increase 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, which may be of type DOUBLE PRECISION, and 3 input parameters - ID, NAME and new_pnce. Please note that the first two parameters may have the same names, as the fields in the Table_example table, with which we choose to parse. Tse allowed by the rules of the mov procedures, which are saved.
Now it’s our fault to voice the local change, so that you will be victorious in order to save the average value. Your voice will look like this:

DECLARE VARIABLE avg_price DOUBLE PRECISION;

Now let's move on to the body of the procedure, which is taken care of Keyword BEGIN.
We need to count the first time for our algorithm - calculate the average price. For whom we are speeding up with a request of this kind:

SELECT AVG(price_l)
FROM Table_Example
INTO:avg_price,-

This will feed the AVG variable aggregate function, so that it rotates the average value of the PRICE_1 field of the average of the selected rows - in our case, the average value of PRICE_1 for all tables Table_example. The value that is rotated by the request is placed in the avg_price change. To restore respect, which is changed avg_pnce in front of a double-flap in order to renew the watering, which are victorious in the request.
The particularity of this request is those who always check strictly one single entry. Such requests are called singleton-requests and only such choices can be chosen in the procedures that are saved. If you want to rotate more than one row, then it is necessary to draw up a FOR SELECT ... DO structure in order to organize a cycle for processing the skin row that is rotated.
Otzhe, we got the average value of the price. Now it is necessary to go through all the tables, to compare the value of the price in the skin record with the average price and live in the
On the cob we organize the sorting of the skin record from the table Table_example

FOR
SELECT ID, NAME, PRICE_1
FROM Table_Example
INTO:ID, :NAME, :new_price
DO
BEGIN
/*_here we open the skin record*/
END

When the design is different from the Table_example table, data and watering values ​​in the skin row will be given a changed ID, NAME and new_pnce. You, obviously, remember that the selected data will be rotated as a result, not varto: the fact that the output parameters were assigned does not mean that the client rejects the HP client qi value! The transfer of parameters is only possible when the SUSPEND command is disabled, and before that we can change the default parameters as the primary change - in our application, we also work with the new_price parameter.
Also, in the middle of the body of the BEGIN.. END cycle, we can modify the value of the skin row. As you remember, it is necessary for us to know, as a basic price, to rise from the average, and to live in the old days. Qiu procedure for matching mi is implemented for the help of the IF operator:

IF (new_price > avg_price) THEN /*so the current price is higher for the average price*/
BEGIN
/*then set a new price, equal to the value of the average price, plus fixing hundreds */
new_price = (avg_price + avg_price*(Percent2Increase/100));
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID =: ID;
END
ELSE
BEGIN
/* If the actual price is less or more than the average price, then we will set the price equal to the total price, plus half of the retail price between that average price */
new_price = (new_pnce + ((avg_pnce new_price)/2)) ;
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID = .ID;
END

Like a bachite, it was great to complete the great IF construction, it’s important to discuss it, so don’t comment, like to write in the symbol /**/.
In order to change the price in the same way to the calculated retail, we speed up the UPDATE operator, which allows you to modify the actual records - one or more. In order to unambiguously indicate in which record you need to change the price, we will win for understanding the WHERE field of the primary key, equal to the value of the change, in which ID value is taken for the stream record: ID =: ID. Catch the respect that the ID is changed in front of the doublet.
After changing the IF...THEN...ELSE construct, the changed ID, NAME and new_price have data, as it is our responsibility to turn the client to call the procedure. For the next IF it is necessary to insert the SUSPEND command, so that the data was sent there, the stars called HP. SELECT...DO does not iterate over all records in your request.
It is necessary to specify that the SUSPEND command is used, so that only the procedure to be saved is saved, the EXIT command is used, so that the procedure is saved after the transfer of the row. However, the EXIT command rarely needs to be completed, the chips are needed mainly in order to interrupt the cycle at the reach of the mind.
In case of fallback, if the procedure was called by the SELECT statement and completed with EXIT, the remaining row won't be rotated. Tobto, if you need to interrupt the procedure and still take the row, you need to speed up the sequence

SUSPEND;
EXIT;

The main feature of EXIT is the selection of singleton-sets of data, which are rotated by parameters in the shortcut through the EXECUTE PROCEDURE. In this case, the values ​​of the external parameters are set, but the SQL data collection is not formed, and the procedure ends.
Let's write down the text of our procedure again, so that the mother can grasp this 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
/*here we process the skin record*/
IF (new_pnce > avg_price) THEN /*so the current price is higher for the average price*/
BEGIN
/*set a new price, equal to the average price, plus fixing hundreds */
new_price = (avg_price + avg_price*(Percent2Increase/100));
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID =: ID;
END
ELSE
BEGIN
/* If the actual price is less or more than the average price, then I set the price equal to the total price, plus half the retail price between the amount and the 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

Danish example of the procedure that is saved, illustrating the development of the main constructions of the movi saving procedures and triggers. Let us look at ways to fix the procedures that are saved for the accomplishment of some often blamed tasks.

Recursive procedures that are saved

The InterBase procedures that are saved can be recursive. Tse means that the procedures that are saved, you can call to yourself. It is allowed up to 1000 equal procedures, which are saved, however, you need to remember that the available resources on the server may run out earlier, lower the maximum HP investment will be reached.
One of the widest range of procedures that are saved is the processing of tree-like structures that are saved in the database. Trees are often vicorated in warehouse management, warehouse, personnel and other broad additions.
Let's take a look at the example of the procedure that is taken, how to choose all goods of the same type, starting from the first equal investment.
Let me come to the statement of the task: may I bring goods with a hierarchical structure of this kind:

Goods
- Pobutov technique
- Refrigerators
- Three chambers
- Two-chamber
- Single chamber
- Cleaning machines
- Vertical
- frontal
- Classical
- Vuzki
- Computer technology
....

Tsya structure dovіdnika categorіy varіv_v may mother g_lki raznoї glibini. and also build up over time. Our mission is to provide a selection of all the elements of the end of the day from the "recitation of the common name", starting from any vuzla. For example, if we choose the "Palny cars", then we need to take into account the following categories:

Prying machines - Vertical
Frontal cars - Frontal classic
Frontal cars - Higher frontal cars

The structure of the tables is significant for the collection of information about the goods' progress. I have simplified the scheme for organizing a 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 GoodsTree table, in which there are only 3 fields: ID_GOOD - a reasonable category identifier, ID_PARENT_GOOD - an identifier for the given category and GOOD_NAME - the name of the category. To ensure the integrity of the data in these tables, we impose on the table the exchange of the call key:

ALTER TABLE GoodsTree
ADD CONSTRAINT FK_goodstree
FOREIGN KEY (ID_PARENT_GOOD)
REFERENCES GOODSTPEE (ID_GOOD)

The table is self-supporting and the same old key is to be followed by it. schob at the table buv posilan on the unknown fathers, і navіt reshkodzhaє try to see the category of goods, yakі mayut nashchadki.
Let's bring it up to our table of upcoming data:

ID_GOOD 1 2 3 4 5 6 7 8 9 10 11 12

ID_PARENT_GOOD 0 1 1 2 2 4 4 4 5 5 10 10

GOOD_NAME GOODS Pobutov technique Computers and accessories Refrigerators Cleaning machines Trichamber Two-chamber Single chamber Vertical Frontal Vuzki classical

Now, if we have a place for saving data, we can proceed to the creation of a saving procedure, which will see the removal of all the "residual" categories of goods from the "burnt" one - for example, for the category "Three-chambers" outside of the category will look like " Pobutov tehnіka Refrigerators Trikamernі".
The procedures that take care of tree-like structures have developed their own terminology. The skin element of a tree is called a knot; and the blues between the knots, which are huddled one on one, are called the boots of the old man. Vuzli, which are located on the very end of the tree and do not make naschadkiv, are called "leaves".
For each procedure to be saved, the input parameter will be the category identifier, for some reason we will start sorting. The matime procedure is saved 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
/*0rgan_zuєmo zovnіshnіy cycle FOR SELECT for bezporednіmi product labels 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
/"Checking for an additional EXISTS function, so turn TRUE, as if it would take one row at the arms turn. If the found node ID_PARENT_GOOD = ID_CHILD_GOOD has no shortcuts, then it is a "leaf" of the tree and consumed as a result */
IF (NOT EXISTS(
SELECT * FROM GoodsTree
WHERE GoodsTree.id_parent_good=:id_child_good))
THEN
BEGIN
/* Pass the tree's arc to the result */
SUSPEND;
END
ELSE
/* For knots, like making laces */
BEGIN
//
CURR_CHILD_NAME=full_goods_name;
/* run my procedure recursively */
FOR
SELECT ID_CHILD_GOOD,full_goods_name
FROM GETFULLNAME(:ID_CHILD_GOOD)
INTO:ID_CHILD_GOOD, :full_goods_name
DO BEGIN
/*dodaєmo for the vuzla-daddy to the known one. */
full_goods_name=CURR_CHILD_NAME| " " | f ull_goods_name,-
SUSPEND; /* turn over the goods*/
END
END
END
END

If we use this procedure with the ID_GOOD2SHOW=1 input parameter, then we take the following:

Just like a bachite, for the help of a recursive procedure that is saved, we went through the whole tree of categories and brought out the names of the categories-"leaves", as if they were on the tips of the needles themselves.

Visnovok

At this point, we conclude the review of the main possibilities of my procedures, which are saved. Obviously, in order to master the breakdown of procedures that are saved when reading one division is impossible, we have tried to reveal and explain the main concepts related to saving procedures. Descriptions of the design and design of the HP can be found in most databases
Part of the important food, associated with the cost of procedures that are saved, will be revealed in the upcoming section - "Expanding the Possibility of Saving InterBase Procedures", as it is associated with the general blame, the vir_ishennu of pardon situations in the procedures that are saved, and by robots.

At the front of this cycle, the world looked at how it is possible to draw data from the table, modify its structure, create, modify and see the data bases and objects that are hidden in them. In this article, we will talk more about the objects that are typical for server DBMS: notifications, triggers and procedures that are saved.

In the first article of this cycle, published in No. 3'2000 of our journal, we indicated that most of the current server DBMS support notifications, triggers and procedures that are saved. Appearances are also supported by bugatma DBMS, for example Access, dBase, Clipper.

Specify that triggers and procedures that are saved should be written in programming language, which are procedural extensions of SQL language. Expanded statements allow you to describe algorithms, for example, do…while, if…then…else, used in SQL language itself . На відміну від мови SQL, що підпорядковується стандарту, його процедурні розширення ніяк не стандартизовані, і різні СУБД використовують різні синтаксичні конструкції для реалізації тих самих алгоритмічних конструкцій, але обговорення відмінностей у синтаксисі розширень SQL для різних СУБД виходить за рамки цієї статті.

To illustrate how it is possible to twist the appearance, triggers and procedures that are saved, we chose Microsoft SQL Server 7.0 and the NorthWind database, which is included before the delivery set of the DBMS.

First of all, apply, give respect to those who implement and save triggers and procedures that are victorious in the DBMS, can be considered as pointing at these statistics. In addition, for the creation of server objects, the following mothers are allowed, as if the administrator of the data base is given.

It is also significant that some ODBC drivers do not support the cycle of procedures, which are saved from client addenda, but that is how the DBMS itself supports it. Prote in the same way the procedures that are saved, as early as possible, can be eliminated from triggers.

Let's look at the facts, then discuss the procedures that are saved, and finish by looking at the triggers.

Appearance

Submission - tse virtual table, ring out the number of columns of one of the number of tables. There is no real data, only SQL-query of the SELECT type, showing the same data and from which tables it is necessary to take when returning to the first representation. Z tsієї point zoru yavlennya - tse ask, scho zberіgaєtsya.

Most of the vipadkiv are presented with victories for the safety of the data. For example, deacons of the category coristuvachiv can be accessed before they appear, but not in a table, they can form data; In addition, the SQL-request can set the USER parameter (name, under which registered koristuvach), and in which case the data available when you return to the date will fall under the name of the koristuvach.

Below are the main characteristics of the manifestation:

• yavlennya behave like a table;
• manifested not to avenge the data;
• Submissions can win more data from the same table.

To create appearances, we can tweak the SQL proposition CREATE VIEW, modify it with the ALTER VIEW proposition, and delete it with the DROP VIEW proposition.

Let's look at the CREATE VIEW statement, which allows you to create a view for a streaming database.

CREATE VIEW proposition

The syntax of the proposition for the creation of a statement guesses the SQL SELECT proposition with a number of additional keywords. Below is a simple syntax:

CREATE VIEW view_name AS select_statement

The view_name argument specifies the name of the view. The key word, which is matched in Microsoft SQL Server, allows you to attach the original text of the CREATE VIEW proposition to the syscomments table.

The AS keyword indicates which SELECT query is actually applicable when returning before filing. Caution that the request cannot replace the keywords ORDER BY, COMPUTE or COMPUTE BY, INTO and cannot refer to the time table.

To modify the previously created statement, follow the proposition ALTER VIEW, as described in the offensive section.

DROP VIEW proposition

Tsya proposition vikoristovuєtsya for vydalannya vyavlennya z da nih. When you see the tables from the data base, you can see all the statements that refer to it. Vikoristovuyuchi tsyu proposition, we are guilty of saying im'ya vyavlennya, scho seen. After being seen, all information about the new one is seen from the system tables.

One more point, if you need to see it, you can blame it for your mind that the structure of the tables, on which it is based, changed after the creation of the phenomenon. You can see the vistava in this view, and then create it again for the additional CREATE VIEW proposition.

Creation and vikoristannya manifestation

The CREATE VIEW proposition is used to create a statement that allows you to change the data, as if it were to be given to the singers. Submissions are created in the current data base and are collected as an object.

The best way to create an appearance is to create a SELECT query and, having misinterpreted it, add the missing part of the CREATE VIEW proposition. Let's take a look at the output text of the Products by Category declaration in the NorthWind database (Listing 1).

The first row, in bold type, is where the SQL proposition is used to create the SELECT statement, which wins the robot over the choice of data. The SELECT clause to apply to this data selects fields from two tables - the CategoryName field from the CATEGORIES table and the ProductName, QuantityPerUnit, UnitsInStock, Discontinued fields from the PRODUCTS table. If this data is given, the two tables are linked by the CategoryID field, and only those products that are in stock (div. criterion after the WHERE keyword) are included in the resulting set of data. The result of the return to the first submission is shown in fig. one .

Now let's make a statement, as if showing all the territories of a similar region. The purpose of the presentation is to come out of the attack (Listing 2).

Having messed with the fact that the SELECT proposition rotates the results as we need, we add the CREATE VIEW statement and add the EASTTERR name that is created (Listing 3).

Instead of creating a text submitted manually, you can use visual tools to enter the DBMS warehouse. On fig. Figure 2 shows how the same application can be created behind the additional View Designer tool, which is the warehouse part of Enterprise Manager, that comes before Microsoft SQL Server.

The top part of the View Designer allows you to specify how tables are bound and which fields are displayed in the data. Below you can enter pseudonymous tables and fields, substitution of their values, method of display. Dalі put the final text of the notification and the results of yogo vikonannya.

First, let me finish a short look around, we’ll talk a little about those, how to take additional information about them. In Microsoft SQL Server 7.0, we can tweak the following system procedures that are saved:

• To retrieve information about filings, you can use the sp_help system procedure. For example, sp_help EastTerr to turn around the situation about the incident;
• to edit the input text, you can tweak the sp_helptext procedure, which is saved;
• in order to know the list of tables in which to store a representation, you can use the system procedure sp_depends;
• to rename a statement, you can tweak the sp_rename system procedure, which is saved.

We have looked at whom we have divided, how to win the evidence for the removal of data, how to meet these and other criteria. Let's turn to the rest of the butt. At the NorthWind data base there are several regions, and for the selection of the list of territories of the selected regions, we need several different declarations. The task can be forgiven, yakbee could have passed the RegionID value as a parameter. It is possible to work for additional procedures, what to take care of, about which we will talk in the offensive division.

Saving procedures

The procedure that is saved is the compilation of a set of SQL propositions, savings in the database as object naming and concatenation as a single code fragment. Saving procedures can accept and rotate parameters. If you create a procedure that is saved, the server compiles it and places it in the cache, which is distributed, after which the compilation code can be blocked by decals. If the program beats the procedure to be saved, it passes the parameters as needed, and the server wins the procedure without recompilation.

Saving procedures allow you to increase the productivity of programs. In the first place, porіvnjano іz sizable SQL-requests, which are overpowered by client programs, the stench takes less than an hour to prepare before the end, the stinks of the stink are already compiled and saved. On the other hand, less traffic is also less in times, less in times of SQL-query transfers, less number of data is transmitted in links. Rice. 3 illustrative weekly procedures, which are taken by the client's addendum.

Saving procedures are automatically recompiled, as if with objects, pouring stench into them, zrobleno be-yaki change; іnakshe kazhuchi, stink zavzhdi actual. As already mentioned above, the procedures that are saved can accept parameters that allow different add-ons to win the same procedure, zastosovayuchi different sets of input data.

Saving procedures are called for to support the integrity of data and the implementation of business rules. At times, dodatkova flexibility, shards are available, as business rules change, you can change the text of the procedure, without changing client addenda.

To create, change and modify procedures, use special SQL propositions - CREATE PROCEDURE, ALTER PROCEDURE and DROP PROCEDURE. We look at them at the offensive division.

Proposition CREATE PROCEDURE

The CREATE PROCEDURE clause is chosen to create the procedure to be saved. There may be an offensive parsing syntax:

CREATE PROC proc_name [(@parameter data_type) [= default] ] [...] AS sql_statements

The proc_name argument sets the name of the procedure, which can be saved if it can be unique within the thread database. The @parameter argument specifies a procedure parameter. The CREATE PROCEDURE clause can have one or more parameters. Although the parameter does not have a value for promotions, there may be transfers by a correspondent (or a client's addendum) under the hour of a weekly procedure. In Microsoft SQL Server 7.0, the number of procedure parameters that are taken is not responsible for overriding 1024; for umovchannyam stench mozhut mother znachennya NULL.

Significantly, the existence of universal mechanisms for accessing data can impose additional restrictions on the number of procedure parameters that are saved. For example, the BDE driver for Oracle 8 is built-to-work only with procedures, the number of parameters of which does not change 10.

The data_type argument specifies the data type for the parameter. The default keyword can be used to set a value behind a promotion - it can be a constant or NULL. When a value is specified for a lock, the procedure can be left out without a value for the parameter. Likewise, the procedure uses a parameter with the LIKE keyword, the value of the promotion can be replaced by group characters (%, _, and [^]).

The OUTPUT keyword indicates which parameter to turn.

The AS keyword indicates that the procedure is to blame for viconating between seeing how many SQL propositions and propositions on the SQL procedural extension specific to this server.

The procedure, created for the additional CREATE PROCEDURE clause, will be saved from the streaming database. In Microsoft SQL Server, procedure names are placed in the sysobjects system table, and output text is placed in the syscomments table.

To change what was done earlier, the following procedure is chosen to win the proposition ALTER PROCEDURE, briefly described in the next section.

Proposition DROP PROCEDURE

Tsya proposition vikoristovuetsya for remote procedures that are taken from the data base. The DROP PROCEDURE proposition takes one argument, the name of the procedure to be seen.

When removing procedures that are saved, information about them is removed from the system tables sysobjects and syscomments.

The creation of the selected procedures that are saved

At the branch assigned to the events, we gave respect to those that would have been handy, so we could pass a parameter to the presentation to replace the RegionID value for selecting one of the four regions in the NorthWind data base. Let's look at it again, which turns the list of territories to the region:

SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = 1

To select another region, we need to change the mind in the WHERE clause in the rest of the row. Also, as we have changed (called RegID), we can choose one of four regions without changing other parts of the enrollment.

The NorthWind database has several regions with numbers 1 to 4. This means that the RegID can be changed as a whole type. The code of the procedure to be taken is shown below:

CREATE PROCEDURE ShowRegion @RegID int AS SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = @RegID

Give credit to those who may have deprived the entire text of the SELECT request is not occupied (in visions in italics) and only added the CREATE PROCEDURE proposition to the name of the newly created saved procedure (in the first row), the parameter deprecation (in the other row) and the keyword AS, scho vkazuє cob propositions, really vykonuyut diї.

The result of the created SQL Server Query Analyzer procedure for RegID = 2 readings in fig. 3 .

Obviously, we can save the procedures that are being saved, not only for the implementation of extended versions of the manifestation, but of "intellectual" SELECT requests. Saving procedures provide mechanisms that allow you to automate a lot of routine tasks.

With Microsoft SQL Server 7.0, we can also tweak system-saving procedures to work with the most important procedures that are saved:

• sp_stored_procedures - show a list of procedures to save;
• sp_helptext - show the text of the procedure to be saved;
• sp_depends - show information about the staleness of saving procedures;
• sp_procoption - set options for procedures that are saved or set;
• sp_recompile - recompile the procedure at the time of the next week;
• sp_rename – change the name of a procedure.

System procedures that are saved

We're talking about Microsoft SQL Server, but we mean the great number of system procedures that are saved and implemented by him. The names of the system procedures that are saved start from SP_ or XP_ and are saved in the master database. More often, we have already described the deeds of the often victorious systemic procedures that are saved.

Reveal respect, that the triggers are not guilty of turning the coristuvachev data.

The CREATE TRIGGER proposition can have two special tables. For example, deleted and inserted tables can have the same structure as a table, a trigger is assigned to it, and delete old and new values ​​of records that have been changed. For example, we can twist the following SQL proposition to search for remote records:

SELECT * FROM deleted

At the table Figure 3 shows both the deleted and inserted tables for all possible data changes.

To change an overt trigger, follow the proposition ALTER TRIGGER. Let's talk about something new in the offensive division.

For the cob, we need to add two new fields to the table, in which the numbers will be reduced. Namely їх UpdatedBy (the name of the manager who stopped the record) and UpdatedWhen (the hour when the record was changed). Let's create a trigger called KeepTrack. yoga axis code:

CREATE TRIGGER KeepTrack ON Customers INSERT, UPDATE AS UPDATE Customers SET Customers.UpdatedBy = USER_NAME(), Customers.UpdatedWhen = GETDATE() FROM inserted, Customers WHERE inserted.CustomerID = Customers.CustomerID

As you can see from the output text of the trigger, the vin is checked after the INSERT and UPDATE skin operations on the Customers table. This trigger will save the name of the manager (data base) from the Customers.UpdatedBy field, and change the date and hour from the Customers.UpdatedWhen field. Numbers of data are excluded from the time table, introduced.

Like Bachimo, this trigger allows you to follow changes and insert new records in the table.

Before that, how to finish a short look at triggers, we are guilty of help, so you can know the information about triggers. The sysobjects table stores information about triggers and types, and the syscomments table stores their output text.

Visnovok

In this part, we looked at a few types of database objects - procedures, detection and triggers that are saved. We recognized the advance:

• Appearance - tse virtual table, sound created as a subset of columns in one number of tables. To create a view, the CREATE VIEW proposition is used, to modify it, the ALTER VIEW proposition, and to delete, the DROP VIEW proposition.
• The procedure that is saved is the compilation of a set of SQL propositions, savings in the database as object naming and concatenation as a single code fragment. To create a procedure that is saved, the proposition CREATE PROCEDURE is set, to change - ALTER PROCEDURE, and to delete - DROP PROCEDURE.
• A trigger is a special type of procedure that is saved, as it is automatically invoked, if the data in the single table is added, visible or changed for additional SQL propositions INSERT, DELETE or UPDATE. Triggers are created for the help of the CREATE TRIGGER clause. To change a trigger, the ALTER TRIGGER proposition is selected, and the DROP TRIGGER proposition is removed.

ComputerPress 12"2000

Saving SQL procedures - hacking a program module, which can be saved from looking at different objects. In other words, the object that has the SQL-instructions. The procedures that are saved can be used in application clients to achieve good productivity. In addition, such objects are often called out from other scenarios, or to inspire some other kind of distribution.

Entry

It is important for anyone to know that they are similar to other procedures (for example, MS SQL). Maybe, you're right. They may have similar parameters, they can see similar values. Above those, the stench is sticking for an hour. For example, stinks are associated with the DDL and DML databases, as well as with the core functions (codename - UDF).

In fact, the SQL procedures that are taken care of are capable of a wide range of advantages, as they see the middle of such processes. Bezpeka, variability of programming, productivity - all add to the core, which works from data bases, more data. The peak of popularity of procedures fell on 2005-2010, if the program was released under the name "Microsoft" under the name SQL Server Management Studio. With the help of data bases, it has become much simpler, more practical and more convenient. From the rock in the world such gaining popularity among programmers. Today, it is an absolutely sound program, like for koristuvachivs, like “connecting” with data bases, it has become like “Exel”.

When you click on the procedure, it is instantly processed by the server itself without zayvih protsessіv that vtruchannya coristuvacha. If so, you can zdiyasnyuvati be-like a distant, vikonannya, change. For all that, you need a DDL-operator, which is self-taught to rob the most complicated parts of object processing. Moreover, it seems to be very fast, and the server is actually not navantazhuetsya. This kind of speed and productivity allow you to easily transfer large amounts of information from the server to the server.

For the implementation of this technology, robots with information are based on mov programming. Before them, you can see, for example, PL / SQL like Oracle, PSQL in InterBase and Firebird systems, as well as the classic “Microsoft” Transact-SQL. All stinks are recognized for creating and concluding procedures that are saved, that allow mastering algorithms in great databases. It is necessary for those who manage such information to be able to protect all objects from unauthorized access to third-party systems and, obviously, create, change or view other data.

Productivity

Qi database objects can be programmed with different paths. Tse allows coristuvachas to choose the type of vicorous method, which will be the most suitable, which will save the strength of that hour. In addition, the procedure itself is being processed, which allows you to hide the great hourly exchange rates between the server and the server. Also, the module can be reprogrammed and changed at any time. Particularly, it is important to note the flexibility, for which the launch of the saved SQL procedure is expected: this process is more similar to others, similar to it, to make it efficient and universal.

Bezpeka

This type of processing of information is used in similar processes, which guarantees improved security. Tse bezpechuetsya for the account of the fact that the access of others to the procedures can be turned off again. Tse to allow the administrator to carry out operations with them independently, not being afraid of perehoplennya information or unauthorized access to the data base.

Transfer of data

The connection between the SQL procedure, which is taken, and the client's addendum is based on different parameters and values, which are rotated. Let's not obov'yazkovo transfer data to the procedure, which is saved, the information is processed (mainly on demand) and processed for SQL. Since the procedure, which is saved, has completed its work, there are data packets back (ale, I know, for the bugs) to the addendum, so that you can use yoga, victorious methods, for the help of which you can find out how to save the SQL procedure, so and turn, for example:

Passing data for an additional parameter to the Output type;

Transfer of data for help of the operator;

The strength of the tribute for the assistance of the operator to the choice.

And now let's take a look, as if looking at the whole process in the middle.

1. Creating an EXEC Saved Procedure in SQL

You can create a procedure in MS SQL (Managment Studio). After the procedure has been created, it will be re-arranged from the programming vuzol base data, in which the procedure will be created by the operator. For vykonannya SQL procedure, scho zberіgayutsya, vykoristovuyut EXEC-process, kaki m_sti іm'ya of the object itself.

When the procedure is combined, the name is declared first, after which one or more of the parameters assigned to you are selected. Parameters can be non-binding. Since the parameter(s) for the body of the procedure will be written, it is necessary to carry out the necessary operations.

On the right, in that the body can be changed locally, roztashovani in nіy, and tsі change locally also according to the date before the procedures. In other words, they can only be seen in the middle of the body of a Microsoft SQL Server procedure. Saving procedures at times are vvazhayutsya local.

Thus, in order to create a procedure, we need the name of the procedure and at least one parameter in the body of the procedure. Catch the respect that the most important option for such a time is the creation of the procedure for the name of the scheme at the classifier.

The body of the procedure may be some kind of example, such as the creation of tables, the insertion of one or a row of rows in tables, the type and nature of the data base, and so on. The proteo body of the procedure is between the victories of the current operations in the new one. Acts of important delimitation have been built below:

The body is not guilty of doing any other procedure that is being saved;

The body can create a pardon about the object;

The body is not guilty of creating everyday triggers.

2. Installing a change in the body of the procedure

You can change them local to the body of the procedure, and the stinks will be changed only in the middle of the body of the procedure. Good practice is the creation of procedures that are changed on the cob of the body, which are saved. But it is also possible to install changes in any place in the body of a given object.

In some cases, you can tell that a few of the changes are installed in one row, and the skin of the change parameter is water-reinforced. Also, please respect that you can change the prefix @. You can insert a change for the type of procedure, wherever you want. For example, changing @NAME1 can be voiced closer to the end of the procedure. In order to give the meaning of the voiceless change, a set of special data is collected. On the basis of the situation, if more than one change is voiced in one row, in such a situation there is less than one set of special data.

Quite often, quizzes ask: "How do you recognize a small value in one statement in a procedure title?" Well. Feeding the cicava, but it’s easier to make it richer, you don’t think so. Hint: for the help of such pairs, like "Select Var = value". You can beat the odds, spreading them out with a coma.

In the most manipulative butts, people show the creation of a simple procedure, what is taken care of, and vikonannya її. For this procedure, you can take such parameters as the process, what it calls, and the value is close to the new one (but don't change it). As the stench escapes, then in the middle of the body, the processes begin. For example, how to create a procedure, how to accept the location of the region from the subscriber, what to call, and turn the data about those, how many authors are sent to the location of that region. The procedure will take into account the tables of authors of the data base, for example, Pubs, to the end of the list of authors. To take a look at the data base, for example, Google captures the SQL script from the side of SQL2005.

In the front case, the procedure takes two parameters, which will be called @State and @City in English. The type of data is consistent with the type assigned to the supplement. The title of the procedure may be internally changed by @TotalAuthors (of all authors), and it will be changed for the purpose of displaying their quantity. Dalі z'yavlyaєtsya razdіl vyboru zaputu, yakii all pіdrakhovuє. Nareshti, podrahovane znachennya vіdobrazhaєєtsya vіknі vyvodu for the assistance of the operator to a friend.

How do I save the procedure with SQL Viconati

There are two ways to follow the procedure. The first path is shown, passing the parameters, as the list of divisions is shown after the name of the procedure. It is permissible, we may have two meanings (like in the front butt). The values ​​are taken for additional changing parameters of the @State and @City procedures. Whose whole way of transferring parameters is in an important order. This method is called ordinal argument passing. In the other way, the parameters are unequivocally recognized, and in this way the order is not important. Another way to do this is to pass named arguments.

The procedure can be changed as standard. So it goes, like in the front butt, but only here the parameters are shown. So the @City parameter is taken first, and @State is taken from the lock values. The parameter behind the lock will sound like it’s all right. The SQL procedures that are saved are passed as simple parameters. In this case, mind you, the UT parameter overrides the value for the SA lock. The other vicoon has more than one argument value for the @ City parameter, and the @ State parameter takes the value of the CA key. Updates to the program will be glad, so that the changes for the abbreviations will be updated closer to the end of the list of parameters. In a different way, we can’t vykonannya, and even you are guilty of practicing with the transfer of naming arguments, which is better and more foldable.

4. Save SQL Server Procedures: Ways to Rotate

There are three important ways to manage data at the foreclosure procedures that are saved. The stench is rehabilitated below:

Turning the value of the procedure that is taken;

Exclude the parameter of the procedures that are being saved;

Choose one of the procedures that are saved.

4.1 Turning the value of saving SQL procedures

In this method, the procedure gives the value of the local change and rotates it. The procedure can be turned around without delay. At the attacking butt, they created a procedure, as if turning over a large number of authors. If you compare this procedure with the previous ones, you can think that the meaning for the other is replaced by the return one.

Now let's wonder how to follow the procedure and enter the meaning, how to turn around. Vikonannya procedure vmagaє vstanovlennya zminnoї that friend, yak be carried out after the process. Note that you can substitute a Select operator, for example, Select @RetValue, as well as OutputValue.

4.2 Remove parameters of SQL procedures that are saved

The meaning of the vіdpovіd can be vikoristane for turning one zminnoy, which we and bachiled in the front butt. Variant of the Output parameter allows the procedure to correct one or the other the value of the change for the other side. The output parameter is designated by the same key word "Output" when the procedure is created. If a job parameter is an output parameter, then the procedure object is responsible for setting its value. Saving SQL procedures, which can be used lower, are used in different cases with sub-bag information.

Our application will have two official names: @ TotalAuthors and @ TotalNoContract. The stench is indicated by the list of parameters. Qi zminnі give the meaning of the middle of the body of the procedure. If we win the parameters, the subscriber can select the value inserted in the middle of the body of the procedure.

In addition, in the previous scenario, two changes are made in order to increase the value, as if saving the MS SQL Server procedure in the output parameter. Then the procedure is beaten by the path of setting the normal value of the CA parameter. The next parameters are the same, and, later, the voiceless changes are transmitted in order. Catch the respect that the hour of the passage of the changed days is the key word is also given here. After the procedure has been successful, the significance, as they turn for additional help of the last parameters, is shown for further reconciliation.

4.3 Choose one of the SQL procedures to save

This technique is used to rotate the set of values ​​in the data tables (RecordSet) to the full procedure that is taken. For this application, the SQL procedure that is being saved, with the @AuthID parameters, feeds the table "Authors" with a filter path, records are rotated after the additional @AuthId parameter. The Select statement overrides what can be rotated and calls procedures to select. When the procedure fails, the AuthId is passed back. Such a procedure here always rotates only one record, or else it is necessary. Ale, the procedure that is being taken is not allowed to turn over more than one record every day. It is often possible to use an example, for some of the data rotations with different parameters, with the participation of the calculation of the change, there is a way of giving a number of bag values.

At the end

The procedure that is being saved is to complete with a serious software module, which it turns or transmits, and also installs the necessary changes to the client addendum. Oskіlki procedure, which is saved, vikonuєtsya on the server itself, the exchange of data with the great obligations between the server and the client's addendum (for those deductible) can be deleted. This allows you to downgrade to the SQL server, which, obviously, goes into the hands of their rulers. One of the foreseeable is the T SQL procedures that are taken care of by those who are involved in the creation of significant databases. Also, there is a large, majestic number of nuances, which can be different when performing procedures that are saved, it is necessary more for those who plan to be engaged in programming, including professionally.