World Wide Tutorial

1. A brief presentation of SQL 3
The filing of the standard of the new version of SQL, SQL entitled 3 (1999) took place shortly before the arrival of 2000. By a what its essential elements are located within the main RDBMS it would take several years. We are in 2001 and only the publishers Oracle, IBM and Informix have started to communicate on the subject.
We will see that SQL 3 presents significant progress, particularly in terms of types of data in the programming of RDBMS and the concept of relational object.

1.1. Triggers (Triggers)
Non-existent in the standard SQL 2, but most of the time established by manufacturers RDBMS, SQL triggers with 3 become an inescapable reality. Briefly recall what a trigger: a trigger is a simple interceptor event-specific databases. His granularity is defined at the table.

The syntax of a trigger SQL 3 is as follows:
CREATE TRIGGER NAME_trigger
BEFORE | AFTER
INSERT | DELETE | UPDATE [OF liste_column]
ON tablename
[REFERENCING OLD [ROW] [AS] NAME_anciennes_valeurs_row
| NEW [ROW] [AS] NAME_news_valeurs_row
| OLD TABLE [AS] NAME_anciennes_valeurs_table
| NEW TABLE [AS] NAME_news_valeurs_table ]
FOR EACH STATEMENT | ROW
[WHEN condition]
BEGIN [ATOMIC]
code_trigger
END
It is noticeable that the triggers SQL 3 have a fine enough granularity they can ask executable code before or after the events of insertion, deletion or modification, and in this last case, with a filter execution on the columns changed.

Example:
CREATE TRIGGER verif_PRICES_surrendere
BEFORE UPDATE (PRICES_uht, quantity discount)
ON command
REFERENCING OLD ROW AS old_row_values
NEW ROW AS new_row_values
FOR EACH ROW
WHEN new_row_values.surrender> old_row_values.PRICES_uht * new_row_values.quantite
BEGIN ATOMIC
DECLARED message VARCHAR (255)
new_row_values.surrender = old_row_values.surrender
SET message = 'Table ORDER, client' ||
cast (new_row_values.cli_id as CHAR (32))
INSERT INTO ALERT (USER, TROUBLE, DATE_TIME, LOCATION)
VALUES (CURRENT_USER, 'Overtaking surrender acceptable',
CURRENT_TIMESTAMP, Message)
END
The trigger is triggered if an update comes on the table ORDER and only if at least one of the columns PRICES_UHT, IN REMISSION or is modified. It reallocates the new value of the rebate if the latter exceeds the old price multiplied by the amount present. He also inserts a record in the table ALERT.

The key word ATOMIC, said that the main trigger must be executed as a transaction, i.e. in one fell swoop without competition (atomic execution).
They may point out that this type of trigger is much closer to those of Oracle as those of SQL Server.

1.2. Extension of language
As a result of the introduction of triggers and many other extensions, SQL 3 gets a syntax provided. It becomes a real procedural language is very close to the syntax of Ada or Pascal.

Thus, there is the concept of block, defined by the keywords BEGIN and END. The declaration of variable done using the keyword DECLARED and assignment using the keyword SET. Conditional new structures are emerging: IF, FOR, LOOP, REPEAT, DO WHILE, DO UNTIL, WHILE, LEAVE. The management of exceptions is now a reality, especially since the return codes of anomalies have been expanded, and it is possible to propagate errors to containers using the key words and RESIGNAL SIGNAL, a little the way RAISE ERROR language Ada.

Finally, the external visibility, as encapsulation in languages guests are strengthened through SQL / CLI (Call-Level Interface, already present in version 2 of SQL), a real interface between applications and dynamic SQL.

In conclusion, say that the most audacious in the field of relational object, Persistent Stored Module (PSM), real libraries procedures and routines that can perform functions use a different levels.

1.3. Library code (SHP - Persistent Stored Module)
Since SQL 2 existed the concept of "module" to launch SQL said "dynamic". This concept becomes more important because of the breakthrough of the object and relational SQL 3 responds with the PSM (Persistent Stored Module) encompassing functions and procedures. In both cases, it is close to the UDF or User Define Function, including introduced by Borland InterBase.

A PSM that we call library, built by example in the following manner:
MODULE gestion_ACCOUNT LANGUAGE SQL;
DECLARE total DECIMAL (16.2);
FUNCTION balance (SQLCODE, client_id) RETURNS FLOAT;
SELECT SUM (CPT_CREDIT) - SUM (CPT_DEBIT) INTO total
FROM ACCOUNT_BANCAIRE
WHERE CLI_ID client_id =;
RETURN total;
PROCEDURE credited (SQLCODE, client_id, amount DECIMAL (16.2));
INSERT INTO ACCOUNT (CLI_ID, CPT_CREDIT, DATE_TIME_TRANSACTION)
VALUES (client_id, amount, CURRENT_TIMESTAMP);
PROCEDURE charged (SQLCODE, client_id, amount DECIMAL (16.2))
INSERT INTO ACCOUNT (CLI_ID, CPT_DEBIT, DATE_TIME_TRANSACTION)
VALUES (client_id, amount, CURRENT_TIMESTAMP);
END MODULE;
This small module brings together the functions to calculate the balance, credit or debit the account of a client. It can be done in another language SQL, among those provided by the standard: Ada, C, COBOL, FORTRAN, NUMPS, PASCAL and PL1. The module is absolutely committed, or to a client (and therefore outside a specific database) or the schema of a database (server module).
It is in a PSM score that we will define procedures common to all bases as the test of whether an integer is the first.

Write comment
Name:
Email:	do not notifynotify
Website:
Title:
UBBCode:	-color-aquablackbluefuchsiagraygreenlimemaroonnavyolivepurpleredsilvertealwhiteyellow -size-tinysmallmediumlargehuge
	Please input the anti-spam code that you can read in the image.

3.22 Copyright (C) 2007 Alain Georgette / Copyright (C) 2006 Frantisek Hliva. All rights reserved."