The article discusses a database object such as a stored procedure and the advantages of using such an approach when developing software in a clientserver architecture. The definition of stored procedures is given, the features of their creation and compilation are listed. The syntax of creating stored procedures in the client-server database management system MySQL is considered. The examples of stored procedures developed for the database for accounting student performance, which demonstrate the feasibility of their use and advantages compared with the usual queries are given. The article provides an example of using the stored procedure on the client side of an application developed in the Embarcadero RAD Studio integrated environment using BDE (Borland Database Engine) technology as a tool for accessing the database.
In applications that implement the client-server architecture, and now, this is the most common architecture for both web-based and desktop applications, data processing is distributed in such a way that the data presentation program is located on the user's machine (on the client), and the data management program and the data itself are placed on the server. The server software accepts requests from client software and returns to it the results of processing these requests.
The popularity of client-server technology is inextricably linked with IBM's
invention of the query language to SQL relational databases – Structured Query Language,
which is currently the universal standard for working with databases. Despite the fact
that due to the growing amount of data and their complication, a new approach to data
storage and processing is being developed – the NoSQL technology [
In client-server applications, it is the server that is responsible for managing the
database, and client machines may have different applications using this data. Specialized
software connects the client and the server, allowing the client to perform requests and
access the database. A comparative analysis of popular client-server database
management systems (DBMS) is given in [
The stored procedure is a compiled routine and is part of the database metadata. The
stored procedure compiler is built into the server core. It is the use of stored procedures
that allows implementing most of the application's business logic at the server level,
which significantly improves application performance, centralizes data processing and
minimizes errors, ensuring data integrity [
Stored procedures are a SQL tool that allows you to create queries with parameters
first, because when creating a procedure, you can set a list of input parameters, which
ensures that the corresponding arguments are accepted when it is called. Stored
procedures can return information that the user expects, as well as in the case of incorrect
data - an error message. Considering the fact that the stored procedure is compiled
before it is stored as an object in the database, the form of the procedure is saved each
time it is called. This fact reduces the amount of data involved in the exchange between
the client application calling the procedure and the database server. The use of stored
procedures significantly increases the level of security provided by the use of the
GRANT and REVOKE instructions, by which users are granted different access
privileges, since procedures can be used to control access authorization [
Stored procedures are used in other tasks besides the ones stated above. For example,
in [
The syntax for creating and calling stored procedures is somewhat different in different client-server database management systems.
Consider the examples of stored procedures for the MySQL server, which is widely used both in web-based applications and in desktop ones. Not only hundreds of thousands of commercial websites work on this base, but also a large number of corporate applications use MySQL as a server-based DBMS. The MySQL distribution contains API (Application Programming Interface) modules for interacting with such programming languages as C, C ++, Java, PHP, Python, etc. MySQL provides support for various character sets, including Cyrillic. To integrate into the operating system and to access the database from the application software, the MySQL distribution kit contains a set of Open Database Connectivity (ODBC) drivers.
Stored procedures, like other database objects, are created using the DDL (Data Definition Language) language.
The general syntax for creating a stored procedure in MySQL is: CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE имя_процедуры ([параметры_процедуры[,...]]) [характеристики ...] тело_подпрограммы параметры_процедуры: [ IN | OUT | INOUT ] имя_параметра type type: Любой валидный тип данных MySQL характеристики: COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } тело_подпрограммы: Валидный оператор программы SQL
3
As an example, consider the task of accounting student performance. The development
of an information system begins with the construction of a conceptual database model.
As a modeling tool, the model proposed by P. Chen in 1976 [
The basic concepts of the ER-model – is the "entity" and "relationship." An entity is any object in the real world or a process that is significant in the context of the task, in fact it is a class of similar objects, information about which should be taken into account. Each entity is identified by a name and a set of attributes that characterize it. An entity key is a set of attributes that uniquely identify each entity instance. An entity is a projection of the notion “relation” of the relational model. Connection is a relationship of entities that defines their functional dependence. In this case, one of the entities acts as a parent in relation to the other, and the second – as a child. Between them there are two main types of links – identifying and non-identifying. When the type of link is identifying, the primary key of the parent entity becomes the primary key or part of the composite primary key of the child. With a non-identifying relationship, the primary key of the parent entity becomes the child's foreign key as one of the non-key attributes. As it is known from the conceptual apparatus of the relational model, the primary key of the entity is responsible for ensuring the categorical integrity of the database, which by definition cannot have a NULL value, i.e. have an undefined value. Relationships provide referential integrity, which means that no foreign key value in a child entity can have a value that is missing among the primary key values of the parent entity.
Several methodologies for constructing "entity-relationship" models are known. The most widespread methodology is IDEF1X.
The CASE-tool (Computer Aided Software / System Engineering) AllFusion ERwin Data Modeler is used as a modeling tool.
The logical model of database "student" is presented below (Fig. 1). The model has five entities: student (student), department (faculty), discipline (discipline), teacher (teacher), academic performance (exam). Relations between entities are determined by the following rules: each student studies at a department, each teacher works at a department, a teacher teaches every discipline, each student pass several disciplines, and several students pass each discipline. This many-to-many relationship is implemented as an entity "exam", which is identified through its foreign keys from the student and discipline entities and is subordinate.
The AllFusion ERwin Data Modeler package allows you not only to build a database model, but also to generate a database: tables with key fields and implement referential integrity constraints on foreign keys. To do this, you can use the “Forward Engineer / Schema Generation ...” command from the “Tools” menu.
The following are SQL instructions for creating tables of database "student".
id_discipline smallint NOT NULL, title varchar(20) NULL, id_teacher smallint NOT NULL );
id_stud smallint NOT NULL, id_discipline smallint NOT NULL, _score smallint NULL );
id_fac smallint NOT NULL, title varchar(15) NULL );
CREATE TABLE student( id_stud smallint NOT NULL, fio VARCHAR(40) NOT NULL, birthday date NULL, course smallint NULL, id_fac smallint NOT NULL, resume text(65535) NULL );
id_teacher smallint NOT NULL, fio VARCHAR(40) NULL, id_fac smallint NOT NULL );
The stored procedure is associated with a specific database, so before creating it, you must go to this database using the standard instruction: "use database_name".
Consider an example of a procedure that allows you to get the results of intermediate certification of a particular student by his last name, including the title of the disciplines that he passed, and the resulting assessments. delimiter ^ create procedure stud_fio_scores(in in_fio varchar(20)) begin declare stud_id integer; select id_stud into stud_id from student where fio=in_fio; select id_stud, title, _score from exam inner join discipline using (id_discipline) where id_stud=stud_id;
Since inside the procedure operators are separated by a semicolon, and in SQL this character is a separator for closing an operator, the first instruction of the procedure sets another separator character. In this case, this "^". Quite often, the symbol "//" or "$" is used as a separator.
As an input parameter, the procedure takes the student's last name, according to which the corresponding primary key of the student table id_stud is determined using the query. This value is stored in a local variable, which is declared inside the procedure by the declare statement. As students are identified in the table with the results of the exams using the key field (id_stud), it is this calculated parameter that is transmitted to the second query of the procedure body, allowing you to get the desired result.
The following stored procedure allows you to get a list of students who have passed the session on the "good" and "excellent", which can apply for scholarships. The procedure displays information in the format of last name of student, course and name of faculty. delimiter ^ create procedure student_score_not3( ) begin select fio, course, title from exam inner join (student inner join faculty using(id_fac)) using(id_stud) group by id_stud having min(_score)>3; Below is an example of a stored procedure that determines the student with the highest average score (if there are several such students, the entire list will be displayed) and the student with the lowest score based on the results of the intermediate attestation. delimiter ^ create procedure max_min_avg() begin declare min_ball double(6,4); declare max_ball double(6,4); select max(sr_b) into max_ball from sr_ball; select min(sr_b) into min_ball from sr_ball;
select fio, avg(_score) as max_sr from student, exam where exam.id_stud=student.id_stud group by exam.id_stud having max_sr >=max_ball;
select fio, avg(_score) as min_sr from student, exam where exam.id_stud=student.id_stud group by exam.id_stud having min_sr <=min_ball; Creating a complex of procedures that implement the basic business processes ensures reliable operation of the information system. 4
The client-server MySQL DBMS, like other SQL servers, does not have a graphical
user interface. Consider the technology of using stored procedures in the client part of
the application for MySQL in the C++ programming language in the Embarcadero
RAD Studio integrated software development environment - the Rapid Application
Development (RAD) environment. Combining powerful tools of the C ++ programming
language with the library of visual components provides the programmer with the tools
for the rapid development of a graphical interface [
Embarcadero RAD Studio provides a programmer with several technologies for developing database applications. Consider one of them - BDE (Borland Database Engine). The main components of the BDE technology that provide the data set are such components as TTable (table), TQuery (query) and TStoredProc (stored procedure). There is a large set of visualization and data management components, such as DBGrid (grid), DBNavigator (navigator), DBText (label), DBEdit (input field), etc. The DataSource component is used to exchange information between data sets and visual components. - data source. The connection of components providing access to the database is shown in Fig. 2.
Table, Query, StoredProc…
DataSource
DBGrid, DBText … of
Consider a specific example of using a stored procedure in the client part of an application.
As an example, take a procedure that displays information about student results of the examination session. As shown above, the procedure is stored on the server under the name stud_fio_scores. The input parameter of the procedure is the last name of the student.
The data access technology through the StoredProc component requires setting the following properties: DataBaseName — selected from the list of available databases; in the StoredProcName property, the required stored procedure is selected from the set of stored procedures of this database. Since stored procedures often have input parameters, it is necessary to select the parameter binding mode, for which the ParamBindMode property is responsible, in which the choice by name or index is available. The data type and other characteristics of the parameters of the stored procedure are available by the Params property, in which you can specify a specific value of the parameter already at the application development stage. In most cases, the value of the input parameter is set or calculated during program execution (runtime).
In this example, in addition to the StoredProc component, you will need to use the TTable (TStudent) component to get a list of student names. This information will be displayed in such a visual component as the ComboBox drop-down list (CBFio_stud). This is implemented by the following code snippet.: void __fastcall TForm1::FormActivate(TObject *Sender) {
TStudent->Active=true;
CBFio_stud->ItemIndex=0;
The selection of the input parameter, the call of the stored procedure and the display of the results of its work is implemented by the following fragment: void __fastcall TForm1::Button1Click(TObject *Sender) {
StoredProc1->Active=false;
StoredProc1->ParamByName("in_fio")->AsString =
In Fig. 3 shows an example of a call to a stored procedure that displays information about a particular student's grades with an indication of the name of the discipline.
The presented information, considered examples clearly demonstrate the advantage of using stored procedures as compared to the usual queries, since it allows you to receive data with any specific input parameter.
If using dynamic SQL, reusing a query with a different input parameter would be difficult. In addition to improving performance, since stored procedures are usually faster than regular SQL statements, they make it easier to perform repetitive tasks, allowing you to output complex operations into a single object. The stored procedure code is compiled once and then stored in compiled form.
Based on the considered examples of creating stored procedures on a server and their use in client applications, it can be concluded that the use of a database object such as a stored procedure in information systems simplifies program maintenance and changes to it. Being independent of tables or any other database objects, stored procedures accumulate in themselves all integrity constraints in the form of rules and data processing algorithms.
They are implemented on the server and the end user is provided only with the data processing interface in the form of a call to certain stored procedures. The mechanism for passing parameters to stored procedures provides the multifunctionality of the query to obtain the necessary data.