The PL / SQL language

SQL is not a programme speech in the coarse sense. It do not, for case, grant you to define work or variable, to do looping or conditional statement. This is not a fault in speech design, but a careful orientation of SQL towards information search procedure in a large informationbase, with database given to simplicity and efficiency. These two term take a strong connotation in the context of a sceptical speech, and correspond to precisely specify standard (and restraint).

The simmpleness of a language is essentially link to its declarative fiber, in other word the ability to express research, bequeath it to the organization to limit the good way to execute it. efficiency is set by fiberistics link to rating complexness that we testament not expand on here. It should be noted, nevertheless, that the ending of an SQL 

question is always guarantee, which is not the case with a programme compose in more mighty language.

It is so vindicated that SQL is not sufficiency for the development of application, and all relational SGBDs take, from the get-go, suggest interface to consort it with more conventional lyric such as C or java. These programme interface appropriate SQL to be habituate as a tool to find information in program performing a wide miscellany of project: graphical interface, hatful sue, report or website production, etc. In a way, WE can then view SQL as an interface of access to the informationbase, integrate into a ecumenical programme lyric. This is surely its near vulgar use.

For some feature, however, the use of an "external" programme speech is poor or unsatisfactory. An evolution of the SGBD is therefore to project, inside the scheme, primitive 

programme that pay for the relative want of expressiveness of relational speechs. This chapter discover these development and their lotion to the foundation of store process and trigger. The maiden let to enrich a database diagram by reckoning or role that cannot - sometimes even in very simple fount - be obtain with SQL; the latter offer the theory of defining constraint.

aboard these pragmatic coating, the store operation simply illustrate sqL's integrating proficiency with a traditional programme language, and emphasise the limit of the use of a question language, and in finical the relational mold.

Stored procedures

aboard these practical application, the store subroutine only instance sqL's integration technique with a traditional programming terminology, and accent the limitation of the use of a quizzical terminology, and in finical the relational framework.

The role and operation of stored procedures

A store procedure run within the SGBD, which avoid the network exchange that are mandatory when the same feature are enforced in an extraneous programme communication in client/server modality with the database. The image comparing extraneous programme /store procedure instance the deviation between the two mechanism. On the depart an extraneous programme, write for exemplar in C, must beginning link to the SGBD server. The programme then run by communication with the server to execute enquiry and regain effect.  

In this architecture, each petition to execute an SQL grade involve a transmission over the network, from the programmememe to the node, come by an psychoanalysis of the question by the server, its digest and execution (In some case the petitions of the node programmememe can be pre-compiled, or "train". then, whenever the node programmememe wishing to retrieve a n-uplet from the outcome, it must make an international birdsong, via the network. All of these exchange play a substantial role in the boilersuit operation, and this impact is all the bully because network communication are slacken and/or the number of birdsongs need to deliver the programmememe is substantial.

The use of a store operation allow all SQL query to be sorted together on the host side and the processing of the recover information. The operation is compile once by the SGBD, at the meter of its universe, let it to be executed chop-chop at the meter of the foretell. In increase, meshing exchange are no long requisite as the logic of the coating 

is nearly incorporate with SQL query. The character of the external programme is then bound to associate to the host and request the executing of the operation, pass the requisite parameter as requisite.

Of course, in practise, situation are not as distinct and the external program is unremarkably ring several process, represent in a way as coordinator. piece system performance is at offspring, a judicious use of store process stay a undecomposed way to reduce customer-server traffic.The use of store process is besides justify, even in the absence 

of performance job, for highly --sensitive-- purpose, a terminus that cover (not only) the survey case:

• The function is base on complex find that must be enforce very cautiously.
• The feature update data that is essential to the application's right operation and eubstance.
• the function frequently evolve.

When modernise an application, we often use several speech count on the circumstance: C or Java lyric for lot, PHP or python treatment for the web interface, a proprietorship 

application generator for on-screen stimulation and interview, a script lyric for coverage, etc. It is important to be able to gene in the database functioning deal by these different circumstances, so as to stool them useable for the different speech use. For example, reservation a plane ticket, or execute a deposit transfer, are minutes whose objurgate functioning (not two ticket for the Same backside; no debit without urinate the match accredit) and consistent (the Same prevail moldiness be apply, regardless of the circumstance of use) moldiness invariably be check.

When develop an diligence, we much have to use several lyric depend on the context: the C or java lyric for mass march, PHP or python for the web interface, a proprietary diligence generator for comment and yield. on-screen consultation, scripting lyric for cover, etc. It is therefore important to be capable to factorise the database operation partake by these different setting, so as to prepare them availcapable for the different lyric victimized. For example the reservation of a shave ticket, or the execution of a bank transfer, 

are operation whose objurgate work (not two ticket for the Saame seat; no debit without making the fit credit) and ordered ( the Saame pattern must be practice, whatever the context of use) must forever be check.

It is easy with a store subroutine, and it leave on the one deal to implement “sensitive” use only formerly, on the former deal to guarantee the correctness, consistency and scalability by impose the systematic use of these use rather of manoeuver datum access.Finally, the last-place advantage of store subroutines is the proportional informality of programing datumbase database, in prominent section because of the very good integration with SQL. This prospect is favourable to the quality and quicken of exploitation, and too help in the distribution and facility of the software since the subroutines are compose by the DBMS and consequently employment identically on all identically.

unfortunately, there is a vis-a-vis to all these advantage: apiece DBMS editor offer its own adjective extension to pee-pee store procedure, which get these procedure ill-sorted from one system to another. This can be a baulk if you wishing to produce software that operate with all relational DBMSs.The follow description is found on oracle's PL / SQL terminology (-PL- tie-up for procedural words) which is arguably the productive of its form. The Same terminology, simplify, with about tike syntactical mutation, is propose by PostgreSQL, and the model which we give can therefore be transpose there without too much problem. The syntax of the terminologys employ by other arrangement are a little different, but 

all yet share a set of construct and a propinquity to SQL which pee-pee PL / SQL a identical interpreter example of the interest and use of store procedure.