Vіdminnіst vіd vіd procedure, scho zberіgaєtsya. DROP VIEW proposition. Intolerance of functions to be avoided

The procedure is being saved stored procedure) - naming the database program object. SQL Server has decal types of procedures that are saved.

System procedures that are saved system stored procedure) are supplied by DBMS retailers and are compiled for viewing from the system catalog or from system information. Their names start with the prefix "sp_". Procedures of all types that are saved are launched, for the help of the EXECUTE command, so you can shorten to EXEC. For example, the sp_helplogins procedure, launched without parameters, forms two calls about the names of cloud entries (Engl. logins) (Engl. users).

EXEC sp_helplogins;

Shchob to date the notification about diy, scho vykonuyutsya for additional system procedures that are saved, in Table. 10.6 put deyaki butt. There are over a thousand system procedures that are stored in SQL Server.

Table 10.6

Apply SQL Server System Procedures

Koristuvachevi available creation of saving procedures for coristuvac DBs and DBs for timchasovye objects. In the rest of the day, the procedure that is taken will be timchasova. So, just like in vipadku with timchasov tables, naming the timchasov procedure, which is saved, it is guilty to start with the prefix "#", as the whole local timchasov procedure is saved, but with "##" - it is so global. The local timing procedure can only be performed within the framework of the day, in which it was created, it is global - and in other fields.

Programming SQL Server objects can work both for the help of Transact-SQL, and for the help of the selections (Engl. assembly) in the CRL (Common Language Runtime) environment of the Microsoft.Net Framework. It is only the first way to look at this assistant.

For the creation of procedures that are saved, the CREATE PROCEDURE statement is used (you can shorten it to PROC), the format of which guidance is below:

CREATE(PROC I PROCEDURE) proc_name[; number]

[(gparameter data_type )

[“default] |

[WITH [ ,...n ] ]

[ FOR REPLICATION ]

AS ([BEGIN]sql_statement[;][...n][END])

Likewise, the procedure that is being saved (either a trigger, a function, a view) is created with the ENCRYPTION option, so the code is rewritten in such a way that the text becomes unreadable. At the same time, as it is assigned to, the algorithm for porting from earlier versions of SQL Server and cannot be seen as a superior algorithm is based on utilities that allow the porting algorithm to be reversed.

The RECOMPILE option indicates that the system will recompile the text during skin wicking procedures. In a remarkable way, the procedure is compiled at the first start and stored in the cache, which allows you to save the code.

EXECUTE AS Sets the security context, which may have a procedure. Give one of the values ​​f CALLER | SELF | OWNER | "user_name"). CALLER is the value behind the promotion and means that the code will be selected in the context of the security code that calls the module. Evidently, the mother is guilty of coristuvach, not only on the programming object itself, but also on other database objects, which they get caught up in. EXECUTE AS SELF means depending on the context of the speaker, which creates or changes the programming object. OWNER specifies that the code will be wrapped in the context of the procedure's thread manager. If a vlasnik is not appointed for her, then she swears at the vlasnik of the scheme, to lie down. EXECUTE AS "user_name" allows you to explicitly specify the name of the koristuvach (in single paws).

Parameters can be specified for the procedure. All local changes, which are changed to transfer values ​​to the procedure. In addition to the voicing parameter with the OUTPUT keyword (or shortened to OUT), you can also set it to a value after the procedure ends, you can also use the program that called the procedure. The keyword READONLY means that the value of the parameter cannot be changed in the middle of the procedure, which is saved.

Parameters can be assigned a value for a promotion, which will be changed, so that the value of the parameter will not be specified explicitly in the next hour of the procedure. Let's look at the example:

CREATE PROC surma (@a int, @b int=0,

©result int OUTPUT) AS

SET @result=0a+0b

We created a procedure with three parameters, moreover, the parameter @b has the value for locking = 0, and the parameter @result is out: the value is turned through the new one into the program that called. Diya, what to win, do it easy - the output parameter takes the value of the sum of two inputs.

When working in SQL Server Management Studio, I created a procedure that is saved, you can find it in the database programming section (Engl. Programmability) in parentheses for procedures that are saved (Fig. 10.2).

When you click the procedure like the input parameters, you can tweak both the change and the constants. Let's take a look at two examples. The first input parameters of the procedure are explicitly set by constants, the output parameter of the wiki has the keyword OUTPUT. In another option, as the first input parameter, the value of the change is set, and for the other parameter, after the help of the DEFAULT keyword, it is indicated what value can be changed for the lock:

Rice. 10.2.

DECLARE @3 int;

EXEC summa 10.5, @OUTPUT;

PRINT0c; – will be shown 15

DECLARE Gi int = 5;

- under the hour of viklik vikoristovuєmo value for zamovchuvannyam

EXEC summa Gi, DEFAULT, 0s OUTPUT;

PRINT0c; – will be displayed 5

Now let's look at the example by analyzing the turn code, with which the procedure ends. Let me cheer you up, skilki at the tables Bookl of books, seen at a given range of years. If so, the cob rіk appeared larger than the kontsevy, the procedure is turned "1" and the pidrakunok is not carried out, otherwise - the number of books is also changed and it is turned 0:

CREATE PROC dbo.rownum(0FirsYear int, GLastYear int, 0result int OUTPUT) AS

IF 0FirsYear>0LastYear RETURN 1

SET @result= (SELECT COUNT(*) FROM dbo.Bookl

WHERE BETWEEN 0FirsYear AND 0LastYear) ;

Let's look at a variant of this procedure, in which the rotation code is taken into the whole change 0ret, after which its value is analyzed (in this case it will be 1). The CAST function, which is victorious in the PRINT operator, serves to convert the value of the integer variable Gres to the row type:

DECLARE 0ret int, Gres int

EXEC Gret = rownum 2004, 2002, Gres OUT;

IF 0ret=l PRINT

PRINT "Number of books" + CAST(Gres as varchar(20))

Saving procedures can read data from tables, and change data and create tables and other database objects.

However, it is not possible to create schemas, functions, triggers, procedures, and the saved procedure.

The offensive butt illustrates the ability, and nutrition, tied to the field of visibility of sentry objects. The procedure is given below, which is taken, reverifying the presence of the time table #Tab2; if there are no tables, I create them. After the table #Tab2, the values ​​of two columns are entered, and instead of the table, it is displayed by the SELECT statement:

CREATE PROC My_Procl (@id int, @name varchar(30))

IF OBJECT_ID("tempdb.dbo.#Tab21) IS NULL

INSERT INTO dbo.#Tab2 (id, name)VALUES (0id,0name)

SELECT * FROM dbo. #Tab2-#1

Before the first week of the procedure, which is taken, we will put it into the next time table #Tab2. Give respect to the operator ЄХES. For the front butts, the parameters were transferred to the procedure "by position", and in this case, a different format for transferring parameters - "by name", is explicitly indicated by the name of the parameter of that value:

CREATE TABLE dbo. # Tab2(id int, name varchar(30));

EXEC My_Procl 0name="lvan", 0id=2;

SELECT * FROM dbo. # tab2; -#2

In the hovered butt, the SELECT statement changes binaries: the first time - the middle of the procedure, the second - from the fragment of the code that calls out (assigned by the comment "№ 2").

Before another wiki of the procedure, we saw the time table #Tab2. Then a single time table will be created from the procedure that is taken:

DROP TABLE dbo. # tab2;

EXEC My_Procl 0name="Ivan", 0id=2;

SELECT * FROM dbo. # tab2; -#2

In this case, I have given only the SELECT statement, which is located in the middle of the procedure (with the "Xa 1" comment). The SELECT "No. 2" entry has been brought to a pardon, as it was created in the procedure, which is saved, the time table at the time of turning back from the procedure will already be deleted from the tempdb database.

The procedure can be seen with the help of the DROP PROCEDURE statement. Yogo format is hovered below. With one operator, you can see a few procedures that are saved by resurrecting them through someone:

DROP (PROC I PROCEDURE) ( procedure ) [

For example, apparently earlier I created the summa procedure:

DROP PROC summa;

You can make changes to the main procedure (and in fact - re-assign її) using the help of the ALTER PROCEDURE operator (addendum

steam speed PROC). Apart from the ALTER keyword, the statement format is practically different from the CREATE PROCEDURE format. For example, we change the procedure dbo. rownum, setting the viscon option in the security context of the sergeant:

ALTER PROC dbo.rownum(SFirsYear int,

SLastYear int, Sresult int OUTPUT)

WITH EXECUTE AS Owner - option to install

IF 0FirsYear>0LastYear RETURN 1 ELSE BEGIN

SET 0result= (SELECT COUNT(*) FROM dbo.Bookl

WHERE BETWEEN SFirsYear AND SLastYear);

In some cases, you may need to vindicate the need for a dynamic molding command and a viconann її on the database server. Tse zavdannya can also be checked for the assistance of the operator ЄХES. In the hovered lower butt, a selection of records from the Bookl tables is selected for the intellectual equality of the Year attribute, the value that is set to be changed:

DECLARE 0y int = 2000;

EXEC("SELECT * FROM dbo.Bookl WHERE = " [email protected]) ;

Vikonannya dynamically formed instructions create rethinking implementation of computer attacks such as "SQL-injection" (Engl. SQL injection). The essence of the attack lies in the fact that the destroyer is used in the data, which is dynamically formed, the master code in SQL. Sounds like it, if the parameters that are presented, take from the results of the introduction of data by a shorthand.

Descho change the front butt:

DECLARE 0y varchar(100);

SET 0y = "2TOV"; - tse mi took away from the coristuvach

Assuming that the row value that is included in the SET statements, we took away as a code (no matter what rank, for example, through a web appendix), then the example illustrates the "regular" behavior of our code.

DECLARE 0y varchar(100);

SET 0y = "2000; DELETE FROM dbo.Book2"; – injection

EXEC("SELECT * FROM dbo.Book2 WHERE="+0y);

It is recommended, if possible, to use the sp_executcsql system procedure in similar ways, which is saved, as it allows you to control the type of parameters, which is one of the bars on the SQL path. Without looking into the details of the її format, let's take a look similar to the one presented earlier:

EXECUTE sp_executesql

N"SELECT * FROM dbo.Bookl WHERE = 0y",

Here, the type of the parameter is explicitly specified, which will be won in the request, and SQL Server will be controlled in the event of a change. The letter "N" in front of the paws indicates that the character is a constant in the Unicode format, which means the procedure. The parameter can be assigned as a permanent value, and the second value is a different one.

Meta roboti– learn how to create and tweak procedures that are stored on the database server.

1. Operation of all applications, analysis of the results of their implementation in the SQL Server Management Studio utility. Revalidation of the presence of procedures in a streaming database.

2. Vikonannya all applications and tasks in the process of laboratory work.

3. Seeking individual orders for options.

Explanation to vikonannya roboti

For mastering the programming of procedures that are saved, we use the butt of the data base with the name DB_Books, like a bula was created by laboratory robot No. 1. When vykonannі prikladіv that zavdan give respect to the validity of the names of the database, the table and other objects of the project.

Saving procedures are a set of commands that are composed of one or a set of SQL operators or functions that are collected in the database at a glance.

Types of procedures that are saved

The system procedures that are taken care of are recognized for the supervision of various administrative activities. Practically all tasks for the administration of the server are counted for help. It can be said that the system procedures that are saved by the interface that ensure the work with system tables. The system procedures that are saved, may be prefixed with sp_, are saved in the system database and can be called in the context of any other database.

Koristuvatsky procedures, which are saved, are implemented by those chi and others. Saving procedures - a complete object of the data base. Depending on which skin procedure, which is saved, it is listed in a specific data base, de and vikonuetsya.

Timing procedures, which are saved, only last a single hour, after which they are automatically reduced by the server. The stench is shared locally and globally. Local timchasovy procedures, which are saved, can only be recalled from that day, in any case. Under the hour of such a procedure, it is necessary to give a name that starts from one symbol #. Like all clock objects, procedures of the same type, which are saved, are automatically deleted when the server is turned on, restarted or the server is running. The global timing procedures that are saved are available for any server day, on which the same procedure is used. For її appointment, it is enough to give їy іm'ya, which starts from ## symbols. Qi procedures are seen when restarting either server, as well as when the session is closed, in the context of what a stench was created.

Creation, change of procedures that are saved

The creation of a procedure that is taken care of, transferring the next task: planning access rights. When you create a saved procedure, you should protect that you won’t have the right to access data base objects that you have created; the choice of parameters for choosing a procedure, the procedures that are saved, can be the mother of input and output parameters; rozrobka procedure code, scho zberіgaetsya. The procedure code can replace the sequence of any SQL commands, including the recall of other procedures that are saved.

Syntax of the operator for creating a new or changing explicit procedure in MS SQL Server values:

( CREATE | ALTER ) PROC[ EDURE] procedure_name [ ;number] [ ( @parameter_name datatype ) [ VARYING ] [ = DEFAULT ] [ OUTPUT] ] [ ,... n] [ WITH ( RECOMPILE | ENCRYPTION | RECOMPILE , ENC [ FOR REPLICATION] AS sql_statement [ ... n]

Let's look at the parameters of the command.

Vikoristovuyuchi prefixes sp_, #, ##, the procedure that is created, can be assigned as a system or timchasova. As can be seen from the syntax of the command, it is not allowed to specify the name of the sergeant, to which the procedure is necessary, and also the name of the data base, where it can be placed. Thus, in order to create a procedure that is stored in a specific database, it is necessary to type the CREATE PROCEDURE command in the context of the data base. With the help of the body of the procedure, which is taken care of, it is possible to select shortened names before the data bases are declared, so without specifying the names of the data bases. If you need to go to the objects listed in other data bases, enter the name of the data base obov'yazkovo.

To transfer input and output data to the created procedures, which are saved, the names of the parameters to be used begin with the @ symbol. In one procedure, which is selected, you can set anonymous parameters, separated by coma. The procedure type is not guilty of local changes, the names of which are chosen with the names of the parameters of the procedure. For assigning the type of data parameters of the procedure, which is taken, it is suitable whether the data types are SQL, including the strings. However, the CURSOR data type can only be used as an optional procedure parameter, which should be taken into account. from the assignments of the OUTPUT keyword.

The presence of the OUTPUT keyword means that it is a valid assignment parameter for turning data from the procedure that is taken. However, it does not mean that the parameter is not suitable for passing a value to the procedure to be saved. Specifying the OUTPUT keyword instructs the server, upon exiting the procedure that is being saved, to assign a local change parameter value to the thread that was specified when calling the procedure as the parameter value. Significantly, when specifying the OUTPUT keyword, the value of the variable parameter for the second hour of the procedure can be set only by a local change. It is not allowed to twist whether it is a virazi or a constant that is valid for sizable parameters. The VARYING keyword is invoked at once with the OUTPUT parameter, which can be of type CURSOR. It is determined that the output parameter will be the resulting multiplier.

The DEFAULT keyword is a value, so we take the optional parameter as a lock. In this way, when calling a procedure, you can explicitly specify the value of a given parameter.

Oscilki the server caches the plan for downloading and compiling the code, with a slight delay in the procedure, the values ​​are already ready. However, in some situations, it is still necessary to recompile the procedure code. Entering the RECOMPILE keyword instructs the system to create a treatment plan to be saved in case of skin rash.

The FOR REPLICATION parameter of the request when replicating data and including the created procedures, which is taken as articles in the publication. The ENCRYPTION keyword instructs the Viconity server to encrypt the code of the procedure that is saved, that it can protect the protection of the copyright algorithms that the robot implements the procedure that is saved. The key word AS is placed on the cob of the body, which saves the procedure. At the same procedure, practically all SQL commands can be frozen, transactions can be deafened, blocking can be set, and other procedures that are saved can be called. Exit from the procedure, which is saved, can be done with the help of the RETURN command.

Vidalennya procedures that are taken

DROP PROCEDURE ( procedure_name) [ ,... n]

Vikonannya procedures that are taken

To win a procedure, the command is: [ [ EXEC [UTE] procedure_name [ ;number] [ [ @parameter_name= ] ( value | @name_change) [ OUTPUT ] | [ DEFAULT ] ] [ ,...n]

If the procedure to be taken is not a single command in the package, then the presence of the EXECUTE command is obligatory. Moreover, this command is needed for a quick call procedure from another procedure or a trigger.

The choice of the keyword OUTPUT for the hour of the procedure cycle is no longer valid for the parameters, as if the hour of the creation of the procedure with the OUTPUT keyword was omitted.

If the keyword DEFAULT is specified for the parameter during the cycle of the procedure, the value for the promotion will be repeated. Obviously, the specified word DEFAULT is allowed only for those parameters, for which a standard value is assigned.

From the syntax of the EXECUTE command, it can be seen that the parameter names can be omitted at the end of the procedure cycle. However, in this case, the coristuvach is guilty of specifying the values ​​for the parameters in the order in which the stench was re-protected during the combined procedure. It is not possible to assign a value for the promotion to the parameter, simply by skipping the first hour of the resurrection. If it is necessary to omit the parameters for which the value is assigned, it is sufficient to explicitly specify the names of the parameters when calling the procedure to be taken. More than that, in such a way it is possible to reset the parameters and their values ​​to a sufficient order.

It is significant that at the end of the procedure cycle, either parameter names are specified with values, or more than a value without a parameter name. These combinations are not allowed.

RETURN link to procedures that are saved

It allows you to exit the procedure at any point beyond the specified mental, and also allows you to transfer the result of the procedure by a number, by which you can judge the correctness of the procedure. An example of a procedure creation without parameters:

CREATE PROCEDURE Count_Books AS SELECT COUNT (Code_book) FROM Books GO

Task 1.

EXEC Count_Books

Pervert the result.

An example of a procedure creation with an input parameter:

CREATE PROCEDURE Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages GO

Manager 2. Create this procedure in the Stored Procedures branch of the DB_Books database using the SQL server Management Studio utility. Run її for help command

EXEC Count_Books_Pages 100

Pervert the result.

An example of a procedure creation 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 GO

Task 3. Create this procedure in the Stored Procedures branch of the DB_Books database using the SQL server Management Studio utility. Run її for help command

EXEC Count_Books_Title 100 "P%"

Pervert the result.

An example of a procedure creation with input parameters and output parameters:

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 @Tit

Task 4. Create this procedure in the Stored Procedures branch of the DB_Books database using the SQL server Management Studio utility. Run for an additional set of commands:

Sql> Declare @q As int EXEC Count_Books_Itogo 100, "P%", @q output select @q

Pervert the result.

An example of creating a procedure with input parameters and RETURN:

CREATE PROCEDURE checkname @param INT AS IF (SELECT Name_author FROM authors WHERE Code_author = @param) = "Pushkin A.S." RETURN 1 ELSE RETURN 2

Task 5. Create this procedure in the Stored Procedures branch of the DB_Books database using the SQL server Management Studio utility. Run її for additional commands:

DECLARE @return_status INT EXEC @return_status = checkname 1 SELECT "Return Status" = @return_status

An example of creating a procedure without parameters to increase the value of the key field in the Purchases table 2 times:

CREATE PROC update_proc AS UPDATE Purchases SET Code_purchase = Code_purchase* 2

Task 6. Create this procedure in the Stored Procedures branch of the DB_Books database using the SQL server Management Studio utility. Run її for help command

EXEC update_proc

An example of a procedure with an input parameter for retrieving all information about a particular author:

CREATE PROC select_author @k CHAR (30) AS SELECT * FROM Authors WHERE name_author= @k

Task 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 a procedure creation with an input parameter and values ​​for locking to increase the value of the key field in the Purchases table for a given number of times (for locking for 2 times):

CREATE PROC update_proc @p INT = 2 AS UPDATE Purchases SET Code_purchase = Code_purchase * @p

The procedure does not reverse the expected data.

Manager 8. Create this procedure in the Stored Procedures branch of the DB_Books database using the SQL server Management Studio utility. Run її for additional commands:

EXEC update_proc 4 or EXEC update_proc @p = 4 or EXEC update_proc --zastosovuєtsya value for promotion.

An example of the creation of a procedure from input and output parameters. Create a procedure for determining the number of orders, calculated for the indication 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(@,

Manager 9. Create this procedure in the Stored Procedures branch of the DB_Books database using the SQL server Management Studio utility. Run її for additional commands:

DECLARE @c2 INT EXEC count_purchases '01- jun- 2006 ', '01- jul- 2006 ', @c2 OUTPUT SELECT @c2

Options for getting to laboratory work No. 4

Zagalni position. In the SQL Server Management Studio utility, create a new side for the code ("Create Request" button). Programmatically create an active database DB_Books behind the help of the Use statement. Create procedures that take care of additional Create procedure statements, and assign procedure names on their own. Skin procedure vikonuvatime on one SQL request, which is the vikonovation of another laboratory robot. Moreover, the SQL code needs to be changed in such a way, so that they can transfer the values ​​​​of the fields, which are how the search is made.

For example, outside the head of the laboratory robot No. 2:

/*Select the number of postal employees (table Deliveries) name the companies, telephony and IPN (fields Name_company, Phone and INN), for which the name of the company (field Name_company) "BAT SVIT".

SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = "BAT SVIT"

*/ –The procedure will be created for this robot:

CREATE PROC select_name_company @comp CHAR (30 ) AS SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = @comp

- To start the procedure, the command is given:

EXEC select_name_company "BAT SVIT"

List of directors

Create a new program in SQL Server Management Studio. Programmatically create an active individual database, created in laboratory robot No. 1, for the help of the Use operator. Create procedures that take care of additional Create procedure statements, and assign procedure names on their own. Skin procedure vikonuvatime on one SQL request, as presented in the view of the next task for the options.

Option 1

1. Enter a list of spivrobitnikiv, yakі may want one child.

2. Enter a list of children who saw gifts at the end of the period.

3. Enter a list of batkіv, yakі mayutnі inopnolіtnі children.

4. Enter information about gifts with a quantity greater than the designated quantity, sorted by date.

Option 2

1. Enter a list of accessories with the assigned type.

2. Inform the number of repairs of fittings and the total number of repairs at the appointed master.

3. Enter a list of vlasniks of fittings and number of animals, sorting for the number of animals and how to fall.

4. Enter information about jobs with a higher assigned number or with a date of acceptance for a job less than the assigned date.

Option 3

2. Enter a list of sales codes, for which colors were sold for an amount greater than the specified amount.

3. Enter the date of the sale, the amount, the seller and the ticket for the specified sale code.

4. Enter a list of tickets and the grade for tickets with a height greater than the specified number or more elegant.

Option 4

1. Enter a list of labels from assigned indications before stopping.

2. Enter a list of delivery dates, for which more than the assigned number of the same liqs were sold.

3. Enter the date of delivery, the amount, the PIB of the quantity according to the supplier and I will name the liqiv after the code of the need for more than the specified quantity.

Option 5

2. Enter a list of debited property for a specified reason.

3. Enter the date of the due date, I will name the possession, PIB of the date of debiting for the debiting of the debited period.

4. Enter a list of possessions with a designated type or with a date of ownership greater than the first value

Option 6

1. Enter a list of insurances for more than the specified number.

2. Enter a list of products, in the name of which meanings are used, a fragment of a word.

3. Enter the name of the product, name the line, name the product with the line code from the designated initial value after the first final value.

4. Enter the order of preparation of the infusion and name the infusion with the number of carbohydrates more for the first value, or the quantity of calories more for the indicated value.

Option 7

1. Enter a list of practitioners from the assigned planting.

3. Enter the date of registration, type of document, PIB of the registrar and the name of the organization for documents registered for appointments of the period.

4. Enter a list of registered documents with the first document type or with a registration date greater than the specified value.

Option 8

1. Enter a list of practitioners with a designated reason for the call.

3. Enter the date of registration, the reason for the registration, the PIB of the reference for documents registered for appointments of the period.

Option 9

1. Enter a list of practitioners who were allowed to enter the specified type.

2. Enter a list of documents with registration date for period assignments.

3. Enter the date of registration, type of permit, PIB of the reference for documents registered for appointments of the period.

4. Enter a list of registered documents from the document code in the assigned range.

Option 10

1. Enter a list of practitioners from the assigned planting.

2. Enter a list of documents, which may have meanings of a fragment of a word.

3. Enter the date of registration, type of document, PIB of the owner and the name of the organization for documents registered for appointments of the period.

4. Enter a list of registered documents from the specified document type or with a document code less than the last value.

Option 11

1. Enter a list of practitioners who are recognized for the given planting.

2. Enter a list of documents with registration date for period assignments.

3. Enter the date of registration, settlement, PIB of the reference for documents registered for appointments of the period.

4. Enter a list of registered documents from the document code in the assigned range.

Option 12

3. Enter a list of specialties, how they took possession from the rental of that number of animals, sorting for the amount of animals and how much fall.

Option 13

1. Enter a list of possessions from the assigned type. 2. Enter a list of possessions, as if writing off a singing practitioner.

3. Enter the amount of decommissioned property, which is grouped according to the types of property.

4. Enter information about recruitment with the date of employment more than the first date.

Option 14

1. Enter a list of tickets from the sheet type assigned.

2. Enter a list of codes of need, for which colors were sold for a sum of more than one value.

3. Enter the date of the due date, the amount, name the postal worker and the color following the postal worker's sing code.

4. Enter a list of colors and grade for tickets with a height greater than the song number or more elegant.

Option 15

1. Enter a list of clients who have reached the numbers of the appointments period.

2. Enter the total amount of payments for skin client numbers.

3. Enter the date of arrival, type of room, PIB of clients, registrations for appointments period.

4. Enter a list of registered clients for single rooms.

Option 16

1. Enter a list of possessions from the assigned type.

2. Enter a list of possessions, such as renting a song client.

3. Enter a list of osіb, yakі took possession from the rental of that number of animals, sorted for the number of animals and how to fall.

4. Enter information about clients sorted by addresses.

Option 17

1. Enter a list of values ​​from the purchase price greater than the initial value, or by the term of the guarantee greater than the specified quantity.

2. To introduce the place of significance of material values, the word is indicated in the names of those.

3. Enter the sum of vartosti values ​​from the code for the designated range.

4. Enter a list of materially valid conditions from the date of admission to work in the designated range.

Option 18

1. Vivest a change of repair robits, vikonanih sing master.

2. Enter a list of stages of work, which enter before the work, the names of which have a designated word.

3. Enter the sum of the cost of the repair work steps for the work from the code for the designated range.

4. Enter a list of files from the date of admission to the robot in the specified range.

Option 19

1. Enter a list of liqiv from the first readings.

2. Enter a list of check numbers for which more than one number of liqs were sold.

3. Enter the date of sale, amount, PIB of the cashier and cashier for the check from the assigned number.

4. Enter a list of liqs and one of them for liqs with a quantity in a package greater than the indicated quantity or with a liq code of less than the first value.

Option 20

1. Enter a list of practitioners from the assigned planting.

2. Enter a list of documents, which may have meanings of a fragment of a word.

3. Enter the date of registration, type of document, PIB date and fact of date for documents registered for a period.

4. Enter a list of registered documents from the designated document type or the document code for the first range.

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 the tasks. It is practically possible 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 vikonat 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 call to rotate an impersonal set of input parameters that are grouped in a row, as they may 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 the procedure-selection is 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 in the table Table_example). 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 request, selects the required fields from it (in the case of ID and NAME) and sends the message 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! І main vіdminnіst of procedures-selection 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, is invoked for the help of EXECUTE PROCEDURE, like "wiymaє" from HP only one row, and reshta (navit yakshcho stink!) 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 the same, yak zap 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 shmachok code means offensive: 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 voice as a local change in the middle of the procedure, and beat the input and output parameters as changed.
In order to declare a 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 after the additional SUSPEND command is consummated.
Also, the selection procedure 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

Krim commands FOR SELECT... DO, 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 request, we need a table that will 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 set the price equal to the total price, plus half of 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. You 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 will rotate the average value of the PRICE_1 field of the average of the selected rows - our average of the PRICE_1 value 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 revive the waters that 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 complete it in the 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 changed 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 an essential 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 query.
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 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 for this table, we put it on the table for 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 collecting data, we can proceed to the creation of a saving procedure, which will see the removal of all "residual" categories of goods from the "fired" one - for example, for the category "Three-chamber" 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.

22 episodes

My knowledge of writing in the main WinForms Client/Server program is simple, to which I have come:

Vykoristovuvat procedures that are taken care of:

• For being a foldable robotic worker. If you choose to work, you will effectively need a table cursor or temp, so call the best way to run it in SQL Server.
• You may need to block access to data. If you don't give access to the tables of coristuvachas (or roles or anything else), you can be convinced that the only way to interact with them is through the joint venture, which is created.

Victory for special requests:

• For CRUD, if you don't need to interleave access to data (because you need to do it the other way).
• For the simplest jokes. The creation of SP for impersonal criteria for a joke - that's all that folding in service. If you can create a swedish poke request, wink it.

For most of my additions, I victorious like SP, so ad-hoc sql, I want to know that I have less and less vicorist SP, shards of stink in the end bag with the code like C #, only more control, test and improve. I recommend bi-wiring ad-hoc sql because you don't know the specific reason.

Saving procedures - a contract on a security program, which encapsulates them, live against data bases. The code of the procedures and the very schema of the data base can be changed without compiling, developing the code, so the inputs of the procedure are left unchanged.

When you ask your program, you will clearly tell yourself about your data model.

Well, it's also not a good practice to simply create procedures that are saved, like CRUD requests for skin tables in your database, so everything is still clearly explained. The nature of the procedure is due to bulky, coarse-grained.

I think that this is the main conflict between people, as it is their fault to improve the data base of people, as it is to expand the interface of the correspondent.

As a person with data, I didn’t take a look at the work behind the data base, before they go through adhoc requests, so it’s important for them to effectively learn or care. How can I find out what I can contribute to changing the schemes? In addition, I don't think that coristovats should be given direct access to database tables with mirroring security (and I can't only attack SQL injections, but also the fact that the basic internal control, which does not allow direct rights and vimagaє vіd usіh koristuvachіv vikoristovyte only procs, recognized for the program, schob zapobіgti mozhlivogo shahraystvo.

Data bases are object-oriented, and code that looks good from an object-oriented point of view can be utterly ruined from the point of view of the base data.

Our retailers remind us, for the sake of it, that all our access to databases is created through procs, so that we will significantly speed up the correction of pardons, so as to lie in data, and then just run the proc in the working environment, and not create a new code and recompile and re-advantage in virobnitstvo. We believe that all our processes were in the hands of the driver, so the control of the dzherel is not a problem at all. Even though the wine is not in Subversion, the wine dbas is periodically seen, and there is no support for Source Control.

Saving procedures, insanely, coming... stink compiled, tossing the plan before the cob of work, and you can cherish the rights to them.

I do not understand the problem with the exit code on the procedure that is being saved. You can easily control them, even if you have a little bit of discipline.

Always start from the .sql file, which is the core of the procedure to save. Post yogo in keruvannya with versions after you wrote the code. The next time, if you want to modify your procedure, what is saved, you will take away your own external control element, below your data base. If you follow this, you will have the same good management as your code.

I'd like to quote Tom Kyte from Oracle here... This is the rule about writing code... even if it's a bit of a mismatch, but I know better, I think.

Our addendum has a ball of code, which we can use to ask (and sometimes we need a procedure that is saved). Tse allows us:

• easily otrimat all ask pіd hour keruvannya versions
• robiti all changes for skin care for various database servers
• includes the repetition of the same code through our code

Access control is implemented in the middle ball, and not in the database, so we do not need procedures that are saved. The whole world is the middle road between special requests and procedures that are taken care of.

It is necessary to change the arguments for both procedures, which are saved as being in the central repository, but (potentially) important to be transferred, and specialties are easier to find, stink shards with your code, but stinks can also be better known in the code.

Argument that the procedures that are saved are more effective, do not take revenge anymore. text sent

Google's advice for the procedure that is taken vs Dynamic Query will show you some arguments in any case and, it's better for you to accept your own decision.

Deyakі rechі, about yaki it is necessary to think: Who needs procedures, what are they saving, Anyways?

It is clear that the food of your power needs is important, but it’s even more important to think about those that, with the most special drinks in the middle, oriented to the bulk, are safe. Beware of your parametrization and follow typical inconsistencies such as SQL injections.

Procs for other reasons, and it is also easier to improve proc for additional profiles or parts of proc. In this rank, you do not need to tell anyone about the launch of your programs, to find out what is being sent to the SQL server

If you want to ask ad-hoc, change your mind that they are parametrized

SQL or SPROC parameterization... doesn't matter much in terms of performance... you can request optimization of one of them.

For me, the rest of SPROC's advantage is that I can turn off a lot of rights to SQL rights management, just grant my rights to log in to the sprocs... so you can win Parametized SQL, login, bind to your connection row, maybe more (record of any type of operator to select on one of the tables, to which they have access, for example).

As before, I've been favoring parameterized SQL, wanting to...

Argument of productivity sproc є spirnim - 3 upper RDBMs win back the plan for drinking and daily hours. Yogo was documented... What is 1995 yet?

However, the implementation of SQL in your program is also a lousy design - code maintenance, perhaps not a good enough concept for the rich.

Likewise, the program can start from scratch for additional ORM (additions from the green field far away from the kіlkoh!), tse vіdminny vibrіr, oskіlki model of your class keruє your DB model that just an hour.

Since the ORM structure is not available, we have hacked a hybrid pidhide to create an XML file of SQL resources for asking a row of SQL for the necessary (the stench is then cached by the resource infrastructure). Just as SQL will require some minor manipulations that are different from the code, it will also require a large manipulation of a row of SQL, which we rethink.

This hybrid pidkhid makes it easier to manage retailers (maybe, if I’m smaller, my team’s shards can be cleared up, so that I can read the plan for the request), and the larynx is just a re-verification of SVN. In addition, I'll ask you to switch RDBM - just replace the SQL resource file (not so simple as an ORM tool, obviously, but it works with old systems or a data base, which is not supported)

My opinion is that 90% of the requests and / or procedures that are saved are not responsible for making an appointment (accepted, manually).

Access to data can be generated automatically. You can choose whether you want to generate procedures statically at compilation time or dynamically at build time, or if you want to add a column to the table (object authority), you only have to change one file.

I will save all data access the code of the program, in which case access to data can be obtained directly from SQL-requests. From the other side, logic management, as I have placed in the database at the sight of triggers, saving procedures, core functions and more. An example of the fact that I respect the date of the data base, and the generation of data - it is acceptable that our client can have FirstName and LastName. Now for the koristuvach interface, DisplayName is needed, as it appears from some non-trivial logic. For this generation, I create a procedure that is saved, then it will be launched by a trigger whenever the row is updated (otherwise, other data).

It seems that there is something more incomprehensible, that access to data is the same as the data base, and everything that is worth accessing data that data is itself there. It's just wrong, but I'm running a lot of projects, like screaming out of ideas. Possibly a whole local phenomonon.

I can just turn on SP's idea by making so many bad designs. For example, in one project, in which I took a part, I tried to type CRUD for skin tables and skins for a smart request, and the stench hit me. With whom, the stench simply added another stupid ball. It hurts more to think about such speeches.

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 the easy-to-use 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).
. Їх are selected ті, in which the number of parameters is determined by the number of arguments in the query. The assigned data types and parameters of those positions are reviewed.
. If more than one subprogram is left out, then the one with the shortest qualification is selected.
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.