| 
SSSSSSSSS QQQQQQQQQ LL 44 000000 000000 SS QQ QQ LL 44 44 00 00 00 00 SSSSSSSSS QQ QQ LL === 44444444 00 00 00 00 SS QQ QQ LL 44 00 00 00 00 SSSSSSSSS O QQQQQQQQQ O LLLLLLL 44 000000 000000 Q -------------------------------------------------------------------- STRUCTURED QUERY LANGUAGE = Langage structuré d'interrogation |
|
A. TERMINOLOGIE . ----------------- COLLECTION (Base de données) RELATION (Table /fichier) TUPLE (Ligne /enregistrement) ATTRIBUT (Nom de zone,définition) DOMAINE (Colonne, toutes les valeurs rencontrées) |
|
RELATION: TABLE DES STAGIAIRES !-- ATTRIBUTS (noms de zone) v ........................................................... : NUMSTG : NOM : PRENOM : AGENCE : NOTE: ENTREE : :........:.............:..........:........:.....:........: : 101 : DUBOIS : Eric : 01 : 07 : 890405 : : 107 : ERNST : Patrick : 01 : 12 : 891215 : I-> : 110 : DUPONT : Alain : 02 : 14 : 890405 : I : 102 : MERCIER : Anne : 03 : 18 : 900302 : I : 104 : BOZUFFI : Ricardo : 03 : 12 : 900302 : I : 121 : GARDEL : Sophie : 01 : 17 : 891215 : I : 130 : FLAVARD : Cecile : 02 : 09 : 890405 : I : 132 : GOUDE : Jean : 02 : 13 : 890405 : I : 103 : FORTRAN : Yves : 03 : 17 : 900302 : I : 105 : DUBROVNIK : Marie : 01 : 16 : 891215 : I :........:.............:..........:........:.....:........: I !<--DOMAINE-->! I (Colonne) I TUPLE (enregistrement) |
|
TABLE DES AGENCES -RELATION .......................... : AGENCE : LIBAGE : <--ATTRIBUTS :........:...............: TUPLE ------>: 01 : LILLE : : 02 : NANTES : : 03 : DAX : :........:...............: <-------> I----DOMAINE |
|
B. SQL/400 gestionnaire de base de données. ------------------------------------------- ORDRES SQL EXECUTABLES SUR L'ECRAN SQL-INTERACTIF (strsql) OU DANS UN PGM RPG,COBOL,C, ou PLI COMPILE PAR CRTSQLXXX. 1/ CREATION d'UNE COLLECTION CREATE COLLECTION -nom-collection--------------------------------------- |-IN ASP x-| |-WITH DATA DICTIONNARY-| (exemple SQLDB), SONT CREES : Objet créé ! NOM ! Type OS/400 ------------------------------------------------------ Bibliotheque ! SQLDB ! *LIB ! ! Journal ! SQLDB/QSQJRN ! *JRN ! ! récepteur ! SQLDB/QSQJRN0001! *JRNRCV |
|
|- ! ! -| si | Dictionnaire ! SQLDB/SQLDB ! *DTADCT (cf IDDU) | WITH DATA | ! ! | DICTIONNARY| + fichiers ! QIDCT.... ! *FILE | | ! ! | |- ! composant le dictionnaire -| + Catalogue SQL comprenant: -l'utilisation des fichiers de références croisées + QADB.... références croisées système -fichiers logiques en relation avec les fichiers de références croisées : + SYSTABLES 1 enreg par table + SYSCOLUMNS 1 enreg par colonne de table + SYSVIEWS 1 enreg par vue + SYSVIEWDEP 1 enreg par dépendance vue-->table + SYSINDEXES 1 enreg par index + SYSKEYS 1 enreg par clé d'index + SYSREFCST 1 enreg par contrainte + SYSPROCS et SYSPARMS procédures cataloguées. |
|
Contenu initial d'une collection SQL Objet Type Attr Texte ------- ------- ---- ------------------------------- QSQJRN0001 *JRNRCV Base de données créée par SQL QSQJRN *JRN Base de données créée par SQL plus les fichiers suivants constituant le catalogue SQL SYSCOLUMNS *FILE LF une ligne par colonne de table SYSCST *FILE LF une ligne par contrainte SYSCSTCOL *FILE LF une ligne par colonne de contrainte SYSCSTDEP *FILE LF une ligne par dépendance de contrainte SYSINDEXES *FILE LF une ligne par index SYSKEYCST *FILE LF une ligne par clé de contrainte SYSKEYS *FILE LF une ligne par clé d'index SYSPACKAGE *FILE LF une ligne par package (pgm contenant du SQL) SYSREFCST *FILE LF une ligne par référence de contrainte SYSTABLES *FILE LF une ligne par table SYSVIEWDEP *FILE LF une ligne par dépendance vue/table SYSVIEWS *FILE LF une ligne par vue SYSPROCS *FILE LF une ligne par procédure cataloguée SYSFUNCS *FILE LF une ligne par fonction SQL (UDF) SYSPARMS *FILE LF une ligne par paramètre (proc ou fonction) |
|
2/ CREATION D'OBJETS (Possible depuis la V3 dans toute bibliothèque) a) TABLE (Fichier physique sans clé ) CREATE TABLE nom !--> (nomcolonne--FOR COLUMN-nom--type(lg)-------------- ! ! >------------------------------------------------- x ! ! ! ! !-Clause WITH DEFAULT--! fois ! ! >---------------------------------------------------) ! ! ! --- !--contrainte liée à une colonne--! >---------------------------------------------------. ! ! !--contrainte liée à plusieurs colonnes--! |
|
>--+----------------------+-------------------------------------> '-NOT LOGGED INITIALLY-' .-NOT VOLATILE--+ >--+------------------------------------------+----------+-+---> | | '-RCDFMT--nom format-' |-UNIT ANY-| '-VOLATILE------+ '-UNIT SSD-' Exemple : CREATE TABLE SQLDB/STGTBL (NUMEROSTAGIAIRE for column NOSTAG DEC(3, 0) not null , NOMSTAGIAIRE for column NOM CHAR(15) not null , PRENOM CHAR(15) not null with default, AGENCE NUMERIC(2, 0) not null REFERENCES FAGENCE, NOTE NUMERIC(4, 2) not null with default, ENTREE DEC(6, 0) ) RCDFMT STGTBLF |
|
Explications : Les noms longs sont acceptés depuis la V3R10 : NUMEROSTAGIAIRE est le nom SQL [30 c pour les colonnes, 128 c pour les objets] NOSTAG est le nom système il sont gérés comme des ALIAS pour les colonnes, pour les tables le nom OS/400 est généré à partir des cinq premiers caractères. le nom de format est par défaut identique au nom de la table NOT LOGGED INITIALLY indqiue une table non journalisée VOLATIBLE indique une table dont le contenu varie rapidement (information destinée à l'optimisateur de requêtes) UNIT SSD, foce le stockage sur un disque SSD (si présent) |
|
Types de variable admis: (l=longueur, d=nbr de décimales) + NUMERIC(l, d) numérique étendu + DECIMAL(l, d) ou DEC(l, d) numérique packé + SMALLINT binaire sur 2 octets + INTEGER binaire sur 4 octets + BIGINT binaire sur 8 octets (V4R50) + CHARACTER(l) ou CHAR(l) alphanumérique + VARCHAR(x) ALLOCATE(y) alphanumérique à lg variable x est la plus grande longueur admise y est la longeur minimum allouée par le système. [DSPFFD et DSPPFM montre la zone de longueur X] |
|
+ FLOAT et REAL numérique virgule flottante (scientifique) + DATE une date + TIME un horaire + TIMESTAMP horodatage (date+heure+microsecondes) AS ROW CHANGE TIMESTAMP -> ce champ reflète automatiquement la dernière insertion ou mise à jour + BLOB(n K|M) champ binaire (souvent image) dont la taille peut atteindre 2 Go. + CLOB champ texte (notion de CCSID) dont la taille peut atteindre 2 Go. |
|
+ (un-type) champ faisant référence à un type créé par l'utilisateur avec CREATE DISTINCT TYPE exemples : CREATE DISTINCT TYPE IMAGE AS BLOB(512K) CREATE DISTINCT TYPE EUROS AS DECIMAL(9, 2) CREATE DISTINCT TYPE FRANCS AS DECIMAL(9, 2) vous pouvez maintenant créer une table par CREATE TABLE VOITURES (CODE CHAR(10) NOT NULL PRIMARY KEY, PRIXf FRANCS NOT NULL, PRIXe EUROS, PHOTO IMAGE) |
|
la particularité de ces types est qu'ils sont fortement typés c'est à dire que l'on ne PEUT PAS COMPARER DES FRANCS et DES EUROS. WHERE PRIXF > PRIXE est syntaxiquement invalide !!! WHERE PRIXF > 10000 aussi ! le système assure une convertion avec le type d'origine,(CAST ou "type()") WHERE CAST(PRIXF as DECIMAL(9, 2)) > 10000 est admis ainsi que WHERE PRIXF > FRANCS(10000) |
|
type de données(suite) + DATA LINK champ contenant un lien vers un fichier >--------LINK TYPE URL-----> -------NO LINK CONTROL----------------------------------- >----- - -- ---FILE LINK CONTROL---- - | INTEGRITY ALL * | | READ PERMISSION FS | | READ PERMISSION DB | |- WRITE PERMISSION FS -| WRITE PERMISSION BLOCKED RECOVERY NO ON UNLINK RESTORE ON UNLINK DELETE * indique si le fichier reste accessible au système de fichier et avec quels droit (FS : les droits du système de fichier, DB : les droits de la base uniquement) |
|
Avec FILE LINK CONTROL, le système vérifie la présence du fichier externe
et garantie son intégrité, tant qu'il est référencé par la base
Il ne peut être référencé que par une seule ligne.
sur AS/400, il faut
initialiser le gestionnaire DLFM (une fois par machine) par INZDLFM
démarrer ce service :
STRTCPSVR SERVER(*DLFM)
indiquer le(les) répertoire(s) dont il faut assurer l'intégrité :
ADDPFXDLFM PREFIX(('/AF4DIR))
enregistrer la bibliothèque contenant des tables avec DataLInk :
ADDHDBDLFM HOSTDBLIB((AF400)) HOSTDB(S44xxxxx)
|
|
type de données(suite) - clé générée automatiquement, deux syntaxes : > dans les deux cas on peut préciser : --ALWAYS-------- toujours GENERATED --- ---> -- BY DEFAULT--- uniquement si la zone est nulle A/ ROWID. Il s'agit de variable VARCHAR(40) contenant des valeur généré de manière non consécutive. Create TABLE test1 (ID ROWID GENERATED ALWAYS , LIB CHAR(40), DATCRT DATE) B/ AS IDENTITY , est associé à un champ numérique sans décimale, Create TABLE test1 (ID INTEGER AS IDENTITY START WITH 100, LIB CHAR(40), DATCRT DATE) |
|
on peut alors préciser avec AS IDENTITY : --START WITH (valeur initiale)-----------------> >-INCREMENT BY (incrément)----------------------> --MINVALUE (valeur mini) - >-- -----------------> --NO MINVALUE ---------- - --MAXVALUE (valeur maxi) - >-- -----------------> --NO MAXVALUE ---------- - -- CYCLE---------- ---ORDER------- >-- ---- -------> --NO CYCLE-------- --- NO ORDER--- avec CYCLE, quand MAXVALUE est atteint on recommence à MINVALUE. NO ORDER indique une numérotation qui peut ne pas être réalisée dans l'ordre des requêtes. |
|
et enfin : -- CACHE x ---- indique que DB2/400 peut garder >---- -------------- . en mémoire les x dernières valeurs --NO CACHE---- (plus rapide) AS IDENTITY est un attribut d'un champ numérique ROWID est un nouveau type de champ Exemple : CREATE TABLE TABCDE (CDENO SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 500 INCREMENT BY 1 CYCLE), LIVREA VARCHAR (36) , CDEDAT DATE) |
|
type de données(V7)
La version 7 ammène un nouveau type : XML
Les champs de type XML peuvent faire jusqu'à 2 Go, la totalité d'une ligne
ne peut pas dépasser 3,5 Go.
Ils sont stockés dans le CCSID indiqué par SQL_XML_DATA_CCSID dans QAQQINI,
UTF-8 (1208) par défaut.
on peut insérer une chaîne de caractère contenant du XML ou bien utiliser
la fonction GET_XML_FILE
INSERT INTO POSAMPLE/CUSTOMER
VALUES( 1004 , GET_XML_FILE('/temp/client04.xml') )
le document doit être bien formé, sinon vous recevrez SQ20398
|
|
Contrôle de présence (val obligatoire/facultative) -----------------------------------------------------------------------. !-- NOT NULL---------------------------------------------------! !------------------------- WITH DEFAULT---------------------! !--'constante'----! !---NULL----------! !---USER----------! !--CURRENT_DATE---! !--CURRENT_TIME---! !CURRENT_TIMESTAMP! - pas d'indication = valeur nulle admise Vous pouvez retrouver l'information par programme en demandant le placement de la colonne INTO :ZONE :IND (IND est binaire et indique si ZONE est nulle) - NOT NULL interdit la valeur nulle - NOT NULL WITH DEFAULT initialise à valeur par dft |
|
Depuis la V5R1, la structure d'une table peut être dupliquée : + complètement CREATE TABLE CLITEMP LIKE CLIENTS ou bien CREATE TABLE CLITEMP (like CLIENTS , PLUSTOTAL DEC(11 , 2) ) + partiellement CREATE TABLE CLITEMP as (SELECT nocli, raisoc, depcli from CLIENTS)---WITH-+---+--DATA--. '-NO' |
|
Il y a alors des options de copie : .---------------------------------------------------------------. | .-COLUMN ATTRIBUTES-. | | .-EXCLUDING IDENTITY--+-------------------+-. | V | .-COLUMN ATTRIBUTES-. | | >----+-+-INCLUDING IDENTITY--+-------------------+-+-------------+-+--> | .-COLUMN-. | | .-EXCLUDING--+--------+--DEFAULTS-. | | | .-COLUMN-. | | +-+-INCLUDING--+--------+--DEFAULTS-+-----------------------+ | '-USING TYPE DEFAULTS-------------' | | .-COLUMN ATTRIBUTES-. | | .-EXCLUDING IMPLICITLY HIDDEN -+-------------------+-. | | | .-COLUMN ATTRIBUTES-. | | +-+-INCLUDING IMPLICITLY HIDDEN -+-------------------+-+----+ | .-COLUMN ATTRIBUTES-. | | .-EXCLUDING ROW CHANGE TIMESTAMP -+-------------------+-. | | | .-COLUMN ATTRIBUTES-. | | '-+-INCLUDING ROW CHANGE TIMESTAMP -+-------------------+-+-' |
|
Vous pouvez utiliser cette technique pour faire référence à un répertoire Exemple : CREATE TABLE ClientsAS (SELECT nomcli, adr1, adr2, dept, ville from REPERTP1) WITH NO DATA les REFFLD sont désormais générés pour la table clients NOCLI CONDEN 6 0 4 1 E-S N° CLIENT Texte descriptif de la zone . . . . . . . : N° CLIENT Information de référence Fichier référencé . . . . . . . . . . . : REPERTP1 Bibliothèque . . . . . . . . . . . . : FORMATION1 Format référencé . . . . . . . . . . . : REPERTF1 Zone référencée . . . . . . . . . . . . : NOCLI |
|
Vous pouvez renommer les zones par le biais d'un alias (AS) Exemple : CREATE TABLE ClientsAS (SELECT nomcli, adr1 AS ADRCLI , adr2, dept, ville from REPERTP1) WITH NO DATA les REFFLD sont désormais générés en conséquence ADRCLI CONDEN 6 0 4 1 E-S N° CLIENT Texte descriptif de la zone . . . . . . . : Adresse client Information de référence Fichier référencé . . . . . . . . . . . : REPERTP1 Bibliothèque . . . . . . . . . . . . : FORMATION1 Format référencé . . . . . . . . . . . : REPERTF1 Zone référencée . . . . . . . . . . . . : ADR1 |
|
Contraintes d'entité: - désignation d'une clé unique (pour les valeurs non nulles) -------------------------------- UNIQUE-------------------- |--CONSTRAINT nom-contrainte-| |-(zonea, zoneb)-| + si CONSTRAINT nom-contrainte n'est pas renseigné le système génère automatiquement un nom par défaut + utilisable associé directement à une colonne CREATE TABLE (zone1 dec(3,0) UNIQUE, zone2 char(30), ... ou fin de définition de table si l'unicité est demandée sur plusieurs colonnes: CREATE TABLE (zone1 dec(3, 0), zone2 char(30), zone3 dec(2, 0), UNIQUE(zone1, zone3)) |
|
un index est crée avec la table (il fait partie de la table) - désignation d'une clé primaire (identifiant univoque). -------------------------------- PRIMARY KEY--------------- |--CONSTRAINT nom-contrainte-| |-(zonea, ..)-| + les clés primaires doivent être définies NOT NULL + même syntaxe que l'unicité + il peut y avoir plusieurs contraintes d'unicité, il ne peut y avoir qu'UNE SEULE clé primaire. CREATE TABLE (zone1 dec(3, 0), ! CREATE TABLE (zone1 dec(3, 0), zone2 char(30) ! zone2 char(30), PRIMARY KEY, ! zone3 dec(2, 0), zone3 dec(2, 0) ! PRIMARY KEY(zone1, zone2)) ! |
|
Contraintes d'intégrité référentielle: Il s'agit de concrétiser le lien exitant entre deux tables. (ce qui était réalisé jusqu'ici par programme) par des déclarations de contraintes syntaxe générale ----------------------------------------------------------------------> | | | | |-CONSTRAINT nom-contrainte-| |-FOREIGN KEY-(zone,...)-| >------------REFERENCES autre-table----------------------------- | | |--(zone1 [, zone2 ...])-| >--------------------------------------------------------------- | | | | |--ON DELETE action-| |-- ON UPDATE action-| |
|
Exemple : create table command (NUMCDE DEC(6, 0) NOT NULL, ARTCDE CHAR(6) REFERENCES article, NUMCLI DEC(6, 0) REFERENCES clients) ici le code article doit exister dans la table article le n° de client doit exister dans la table clients. Comme avec les contraintes d'unicité et de clé primaire, si la clause CONSTRAINT n'est pas employée, le système génère un nom par défaut. Ou bien (si la référence porte sur plusieurs colonnes) FOREIGN KEY(zone1, zone2, ..) REFERENCES table create table livraison (NUMLIV DEC(6, 0) NOT NULL, NUMCDE DEC(6, 0) NOT NULL, NOLIGN DEC(3, 0) NOT NULL, FOREIGN KEY(numcde, nolign) REFERENCES lcommand) ici une livraison doit référencer une commande existante. |
|
ON DELETE, ON UPDATE : que faire si l'on supprime une ligne du fichier parent si l'on change la clé dans le fichier parent NO ACTION : ne rien faire,l'événement est interdit. le contrôle a lieu lors du COMMIT, la journalisation est OBLIGATOIRE. RESTRICT : ne rien faire,l'événement est interdit. le contrôle est immédiat, la journalisation facultative CASCADE : en cas de suppression, suppression des lignes associées dans la table qui référence. (en cas de suppression d'un client, suppression de toutes ses commandes) SET NULL : l'événement est autorisé et la clé étrangère de la table qui référence est mise à NULL (NULL doit être autorisé) SET DEFAULT: idem SET NULL avec la valeur/dft |
|
Check Contraints ou contraintes de domaine : il s'agit d'établir un contrôle sur une zone (doit être > à , <> de ...) devant être TOUJOURS vérifié. ces nouvelles contraintes seront vérifiés dans toutes les conditions en cas d'erreur ==> message CPF502F ---------------------------------- CHECK -----------------------> | | |-CONSTRAINT nom-contrainte-| ---( test logique valide)---. .......................................................... : Create table entcdep1 Datcde DATE , Datliv DATE : : Check (datliv > datcmd), ... : :........................................................: |
|
Il est possible d'ajouter/retirer une contrainte par l'ordre ALTER TABLE : |--ADD---> ALTER TABLE nom --- |--DROP--> |--contrainte d'unicité (même syntaxe que CREATE TABLE)---| >---ADD-- | |--contrainte référentielle(même syntaxe que CREATE TABLE-| >---DROP-----PRIMARY KEY------------------------------------------- | | |--FOREIGN KEY-| | |--UNIQUE------|-- nom de la contrainte--| |--CONSTRAINT--| (Vous pouvez aussi utiliser la commande Système WRKPFCST) |
|
Il est possible d'ajouter (ADD), retirer (DROP) ou de modifier (ALTER) une colonne, modifiant ainsi la structure même de la table : ADD ALTER TABLE nom-table-----< ALTER >-COLUMN ---------------> DROP ADD COLUMN = même syntaxe que CREATE TABLE ADD COLUMN-nom-FOR COLUMN-nom/400---Définition-------> >---------------------------------------------------> !--NOT NULL-------------! !--clause WITH DEFAULT--! >-------------------------------------------------------------------- ! ! ! ! !--CONSTRAINT--nom---UNIQUE------------! !-BEFORE--nom-colonne(V7)! !-PRIMARY KEY----! !-REFERENCES ...-! |
|
!--CASCADE---! DROP COLUMN-----nom de zone----- !------ !--RESTRICT--! CASCADE indique que tous les logiques, toutes les vues toutes les contraintes qui s'appuient sur cette colonne sont supprimées. RESTRICT indique que la colonne n'est pas retirée si un des éléments ci-dessus s'appuie sur cette colonne. ALTER COLUMN -------------------------------------------------> !-SET ---DATA TYPE- type de zone---! ! ! !--NOT NULL---------------! >----DROP ---DEFAULT-----------------------------> ! ! !-NOT NULL--! >----(clause WITH DEFAUT)------------------. |
|
La TR2 de la version 7.2 apporte une option à CREATE TABLE CREATE OR REPLACE TABLE offrant les mêmes services que ALTER TABLE ON REPLACE PRESERVE ALL ROWS conservation de toutes les lignes, y compris sur une table PRESERVE ROWS (dft) si une plage (pour un table partitionnée) est enlevée, les lignes correspondantes disparaissent DELETE ROWS aucune ligne n'est conservée. |
|
en V4R20 :
On peut indiquer des contraintes de domaine :
il s'agit de définir les valeurs pouvant être placées dans une colonne
par exemple :
CONSTRAINT service_valide CHECK (service IN (10, 20, 30, 40))
ce qui peut être indiqué par l'ordre SQL :
ALTER TABLE personnel ADD CONSTRAINT salairecst
CHECK (salaire < 1000000 AND prime <= salaire)
ou par la commande OS/400 :
ADDPFCST FILE(personnel) TYPE(*CHKCST) CST(salairecst)
CHKCST('salaire < 1000000 AND prime <= salaire')
|
|
b) INDEX (Fichier logique même format avec clé) CREATE-(UNIQUE)-INDEX --nomindex-- ON -nomtable---- ----(CLE1 ---ASC---------------, I-DESC-I -----CLE2 etc.....). UNIQUE a la même signification que UNIQUE SDD Pour chaque zone clé on peut définir un ordre de classement croissant ou décroissant. Les index sont de type b-tree. |
|
+-------+ de A à N |AN - OZ| de O à Z +-------+ / \ / \ +-------+ de A à M |AM - N.| (tout ce qui est Nxxx) +-------+ / \ +------+ (tout ce qui est NAxxx) | A. | ____________ +------+ |TES=878787| <-- NANTES / \ / +------+ | N. | +------- ____________ \ |CY =124578| <-- NANCY |
|
Si vous souhaitez ignorer la casse (différence minuscules/majuscules) lors de vos recherches : 1/ utiliser UCASE, par exemple : Select * from VINS where ucase(cepage) like 'CABERNET%' ->le problème c'est que SQL ne peut réutiliser alors, aucun index existant. 2/ Utiliser un critère de tri particulier (paramètre SRTSEQ) : *LANGIDSHR ->si vous avez créé un index avec les mêmes attributs, il est utilisé ! avec SDD, vous utilisez STRSEQ( ) et LANGID( ) sur la commande CRTLF avec SQL, indiquez cette propriété pour la session SQL : STRSQL STRSEQ(*LANGIDSHR) LANGID(FRA) sous ODBC/JDBC, onglet "Langue" - Tri par ID Langue - Poids partagé |
|
Autre possibilité, à partir de la version 6 : A/ On admet les expressions en tant que clé CREATE INDEX i1 on table T1 ( UPPER(CEPAGE) as CEPMAJ ) B/ On admet la clause WHERE sur les index : CREATE INDEX i1 on table T1 (CEPAGE) WHERE CEPAGE LIKE 'CABERNET%' C/ Vous pouvez préciser un format par la clause RCDFMT, suivie de : ADD ALL COLUMNS : toutes les colonnes du PF appartiennent au format ADD KEYS ONLY : seules les zones clés appartiennent au format ADD col1, col2 : ces zones font suite aux zones clés dans le fmt (ADD KEYS ONLY est le défaut) |
|
Ces index fonctionnent très bien quand il s'agit de retrouver un petit nombre de clés dans un grand nombre d'enregistrements (accès RPG/COBOL) mais ils sont assez lents si vous réalisez une requête retournant 80 % des enregistrements, particulièrement lors des tris. En effet les enregistrements doivent être retournés dans l'ordre des clés et ils sont stockés dans physiquement sur le n° de RANG(dans la table) Vous multipliez alors les E/S disque sauf si vous réorganisez souvent vos fichier par RGZPFM KEYFILE(xxx) ^ en indiquant le nom de l'index le plus fréquement utilisé ici ---! Ce sont les seuls utisables à la fois par SQL et par les langages (RPG, COBOL) en acces direct. |
|
EVI enfin, qui est un concept Rochester (il y a un brevet), l'AS/400 étant le premier à l'utiliser. EVI est une utilisation avancèe des index Bitmap Vecteur -------------------------- +-------------+ !rang client ville ! ........................... | code | rang | !------------------------! : Table des symboles : +------+------+ !1 ! 1 ! NANTES ! :.........................: | 1 | 1 | !2 ! 6 ! ANGERS ! :Valeur:code:Deb:Fin: nbre: | 2 | 2 | !3 ! 3 ! RENNES ! : : : : : : | 3 | 3 | !4 ! 7 ! CHOLET ! :NANTES: 1 : 1 : 6 : 2 : | 4 | 4 | !5 ! 8 ! BREST ! :ANGERS: 2 : 2 : 2 : 1 : | 5 | 5 | !6 ! 5 ! NANTES ! :RENNES: 3 : 3 : 7 : 2 : | 1 | 6 | !7 ! 4 ! RENNES ! :CHOLET: 4 : 4 : 4 : 1 : | 3 | 7 | !8 ! 2 ! VANNES ! :BREST : 5 : 5 : 5 : 1 : | 6 | 8 | ! ! ! ! :VANNES: 6 : 8 : 8 : 1 : +-------------+ !------------------------! :......:....:...:...:.....: |
|
Le vecteur contient un poste par enregistrement, la position donnant le n° de rang, MAIS on indique un code et non une valeur de clé. On indique en plus une table des symboles qui contient - la correspondance entre chaque valeur de clé et son code associé - des statistiques destinées au "Query Governor" l'optimiseur de requête Il faut simplement indiquer le nombre de valeurs différentes lors de la création afin de savoir s'il faut créér un vecteur sur 1,2 ou 4 octets si vous ne connaissez pas ce nombre de valeurs différentes SQL va les rechercher pour vous et la création de l'index sera un peu plus longu CREATE ENCODED VECTOR INDEX on fichier(clé1, clé2, ...) FOR x DICTINCT VALUES |
|
c) VIEW (Fichier logique format différent) (Projection , Sélection, jonction) CREATE--VIEW -nomvue---------------------------------- I-nom-colonne1, nom-colonne2,-I >--------------(AS SELECT ...FROM ... WHERE ....[cf SELECT]- >----------------------------------------------------------- | | | |-CASCADED-| | |-WITH ----------- CHECK OPTION--| | |-LOCAL----| | La déclaration des noms de colonnes est obligatoire si une colonne est définie 2 fois (jonction) ou n'est pas définie (résultat d'un calcul ou d'une expression). |
|
WITH CHECK OPTION : la clause WHERE doit être vérifiée aussi pour les mises à jour et les insertions. Si vous faites une restriction sur le code société = 01 l'utilisateur ne peut pas insérer(par exemple) des lignes d'une autre société. SQL autorise des vues s'appuyant sur des vues CREATE TABLE t1 .... CREATE VIEW v1 (AS SELECT ... FROM t1 ....) CREATE VIEW v2 (AS SELECT ... FROM v1 ....) avec CASCADED le contrôle est effectué AUSSI par rapport aux sélections de TOUTES les vues sous-jacentes (utilisées dans FROM). avec LOCAL le contrôle n'est effectué que pour les vues sous-jacentes ayant elles même la clause WITH CHECK OPTION. |
|
d) ALIAS (autre nom d'un fichier ou qualification d'un membre) nouveauté liée à la version 4.30. - gestion des ALIAS un ALIAS permet de renommer un fichier (lui donner un nom long) ou préciser un membre pour un fichier multi-membres. CREATE ALIAS MABIB/MBR2_ALIAS FOR MABIB/FIC1P1 (MBR2) mettre à jour MBR2_ALIAS revient à mettre à jour MBR2 dans FIC1P1 ATTTENTION : DROP TABLE nom-alias détruit le fichier PHYSIQUE il faut écrire DROP ALIAS pour supprimer l'ALIAS Un alias portant sur un fichier inconnu, peut exister. |
|
Limitations : + n'est pas supporté pas ALTER TABLE, si c'est un ALIAS de membre. + n'est pas supporté par la clause FOREIGN KEY (int. référentielle), si c'est un ALIAS de membre. + CREATE TABLE ne peut pas créer une table ayant le même nom qu'un alias + idem pour CREATE INDEX et CREATE VIEW. + DROP TABLE, GRANT et REVOKE ne sont pas admis pour un alias de membre on entend par ALIAS de membre, un alias indiquant un membre particulier |
|
3/ MISE A JOUR DU CATALOGUE SQL a/ mise à jour des labels LABEL ON TABLE STGTBL IS 'Fichier des stagiaires' PACKAGE (==> modification du texte de l'objet) LABEL ON STGTBL (NUMSTG IS 'n° de stagiaire', NOM IS 'nom du stagiaire', PRENOM IS 'prénom du stagiaire') LABEL ON COLUMN STGTBL.AGENCE IS 'n° agence ' [==> COLHDG] LABEL ON COLUMN STGTBL.AGENCE TEXT IS 'numéro agence' [==> TEXT)] |
|
3/ MISE A JOUR DU CATALOGUE SQL b/ mise à jour des commentaires COMMENT ON .... + mise à jour des commentaires dans les catalogues SQL (==> uniquement la zone REMARKS du catalogue) même syntaxe que l'ordre LABEL ON plus (depuis la V3R60) PROCEDURE nom IS .... catalogue SYSPROCS PARAMETRE procedure.paramètre IS .... catalogue SYSPARMS |
|
4/ GESTION DES AUTORISATIONS ACCORDER DES DROITS I ---ALL------(tous les droits)---I I ---ALTER----(*OBJALTER)---------I I ---DELETE---(*OBJOPR + *DELETE)-I GRANT ---I ---INDEX----(*OBJMGT)-----------I--------> I ---INSERT---(*OBJOPR + *ADD)----I I ---SELECT---(*OBJOPR + *READ)---I I ---UPDATE---(*OBJOPR + *UPD)----I I ---REFERENCE(*OBJREF)-----------I ----- ON nom(de table ou de vue)-------------------> ----- TO -nomprofil------(1 profil utilisateur)----> I-PUBLIC----I (*PUBLIC) -------------------------------------------------. I---WITH GRANT AUTORITY---I (donne le droit de gérer les droits) |
|
4/ GESTION DES AUTORISATIONS REVOQUER DES DROITS I ---ALL------(tous les droits)---I I ---ALTER----(*OBJALTER)---------I I ---DELETE---(*OBJOPR + *DELETE)-I REVOKE --I ---INDEX----(*OBJMGT)-----------I-------- I ---INSERT---(*OBJOPR + *ADD)----I I ---SELECT---(*OBJOPR + *READ)---I I ---UPDATE---(*OBJOPR + *UPD)----I I ---REFERENCE(*OBJREF)-----------I ----- ON nom(de table ou de vue)----- --- FROM -nomprofil---. (1 profil utilisateur) I-PUBLIC----I (*PUBLIC) |
|
4/ GESTION DES AUTORISATIONS à la colonne . Depuis la V4R2 il est possible de gérer les droits à la colonne : GRANT SELECT , UPDATE(numtel,email) ON TABLE personnel TO richard ici, on donne le droit de lectures (toutes colonnes) et le droit de modifier les colonnes "numtel" et "email" uniquement. les droits ne sont accordés que via SQL mais peuvent être visualisés par DSPOBJAUT, EDTOBJAUT puis F16. Ils sont modifiables aussi par OPERATION NAVIGATOR. |
|
5/ GESTION DES VERROUILLAGES (libération lors du COMMIT) LOCK TABLE -nomtable- IN --SHARE------- MODE I-EXCLUSIVE--I SHARE = ALCOBJ *SHRNUP EXCLUSIVE = ALCOBJ *EXCL 6/ SUPPRIMER I----TABLE----I DROP --I----VIEW-----I----------------------nomobjet. I----INDEX----I I-IF EXISTS-I I--COLLECTION-I I---PACKAGE---I I----ALIAS----I ATTENTION: Si vous supprimez une table, SQL supprime de lui-même tous les index et les vues en relation. |
|
7/ Création d'un script (source SQL contenant tous les ordres de création de la base) : CREATE SCHEMA nom -----------------------------------------------> -----------------------------------------------------------------> !--IN ASP x--! !--WITH DATA DICTIONNARY--! --> -----------------------------------------------------------------> ! ! ! ! ! ! ! ! ! TABLE ! ! ! ! ! ! !--CREATE INDEX----! !-COMMENT ON--! !-LABEL ON--! ! VIEW ! ! ----------------------------------------------------------. ! ! ! ! !--GRANT -----! !-- Cet ordre doit être traité par RUNSQLSTM qui exécute un ordre SQL contenu dans un fichier source. (SBMJOB admis et même recommandé). |
|
8/ Renommer --- TABLE (nom de tabvle ou de vue)-- RENAME -- ----> --- INDEX (nom d'index)-------------- >-- TO --- (nouveau-nom)------------------------------- | | | | | |--FOR SYSTEM NAME (nom OS/400)-| | | | |--SYSTEM NAME (nouveau-nom-OS/400)---------- |
|
C. SQL/400 et OS/400 --------------------- OBJET SQL ! OBJET OS/400 --------- ! ------------- COLLECTION ! *LIB + *JRN + *JRNRCV [ + *DTADCT] ! TABLE ! *FILE/PF (sans clé si créé par SQL) ! toutefois un fichier physique avec clé ! (créé par CRTPF) peut être traité comme ! une table SQL. ! VIEW ! *FILE/LF (fmt différent, sans clé) ! INDEX ! *FILE/LF (même fmt, définition de clé) |
|
+ Les ordres de créations SQL sont maintenant valides dans toute bibli. OS/400 (avant seules les COLLECTIONS SQL étaient admises) (les tables ne seront pas journalisées dans une biblothèque OS/400) Il est possible de créer dans une collection SQL des index de des vues pointant sur des fichier NON SQL d'une bibliothèque OS/400. + Il est possible de créer (CRTPF,CRTDUPOBJ) de restaurer(RSTOBJ) de déplacer(MOVOBJ) un fichier physique(NON SQL) dans une collection SQL Les catalogues SQL SONT MIS A JOUR La commande CHGOBJOWN qui change le propriétaire d'un objet modifie les catalogues SQL |
|
+ L'ordre SQL LABEL ON modifie le texte de l'objet les COLHDG des zones La commande CHGOBJD modifiant le texte de l'objet modifie le cataloque SQL + L'opération inverse(CRTDUPOBJ,RSTOBJ,MOVOBJ de COLLECTION vers bibliothèque OS/400) est possible. Les catalogues SQL SONT AUSSI MIS A JOUR (le catalogue ne réference plus cette table) + Le fichier RESTE JOURNALISE, reste un objet SQL. Depuis la V3R10, il est POSSIBLE de placer dans une collection SQL un fichier logique qui n'a pas été créé par SQL. |
|
D. SQL/400 langage relationnel de manipulation de données. ---------------------------------------------------------- ORDRES SQL EXECUTABLES SUR L'ECRAN SQL-INTERACTIF l'ORDRE SELECT (définition d'une extraction) SELECT ..........ce que je veux FROM ...........à partir de quel(s) fichier(s) WHERE ..........sélection GROUP BY .......traitement récapitulatif HAVING .........sélection sur trt récap. ORDER BY .......ordre de classement |
|
SELECT ------- * toutes les variables du(des) table(s) [ex : select * from articles] ou <expression1> [AS nom], [ <expression2> [AS nom], .... ] TOUTE VIRGULE DOIT ETRE SUIVIE D'UN ESPACE (EN FRANCE) toute expression peut se voir attribuer dynamiquement un nouveau nom qui lui servira aussi d'en-tête. - SELECT NOM, SALAIRE + COM AS TOTPAIE FROM PERSON attribue le "nom" TOTPAIE à l'expression SALAIRE + COM |
|
expressions valides: + un nom de variable + une constante + un calcul + , * , - , / ex : (QTE * PU) , (SALBRU * 13) etc ... + une fonction sous la forme Fonction(argument) toutes les fonctions acceptent comme argument une expression (au sens qui vient d'être vu). les fonctions peuvent donc être imbriquées. |
|
fonctions valides: fonctions de changement de genre DIGITS(exp) représentation en alphanumérique d'une expression numérique DIGITS(ZONDEC) DECIMAL(exp, l, d) représentation en packé d'une expression numérique DECIMAL(AVG(SALBRU), 7, 2) forcer une précision : DECIMAL((QTE * PU), 11, 3) binaire vers packé : DECIMAL(ZONBIN, 6, 0) FLOAT(exp) représent. en virgule flottante d'une expression numérique |
|
INTEGER(exp) représent. en numérique binaire d'une expression numérique ZONED(exp) représent. en numérique étendu d'une expression numérique CHAR(date) renvoie la représentation caractère d'une date. (séparateurs inclus, suivant le format en cours.) VARCHAR(expression, Lg----------------) ! ! !--CCSID---! Transforme une expression à lg fixe en colonne à lg variable avec choix du code page (CCSID) VARGRAPHIC( ) idem en DBCS (idéogrammes orientaux ou DBCS) |
|
BLOB(exp) transforme un chaine de caractères en BLOB. CHAR(exp) transforme en colonne à taille fixe un expression variable. BINARY() et VARBINARY() liées aux nouveaux types de données v5r30, proches du BLOB, les données étant sans notion de CCSID. la fonction CAST vient remplacer toutes les fonctions précédentes en proposant d'indiquer clairement le nouveau type et la longueur. CAST(expression AS type[lg]) V4R20 ATTENTION : CAST qui convertit du numérique en caractère remplace les zéros non significatifs (ceux de gauche) par des blancs, ce que ne fait pas la fonction DIGITS, qui conserve donc une particularité. |
|
types valides INT(INTEGER)--| SMALLINT----------------------------------------------- DEC(DECIMAL)---| NUMERIC---------(longueur, nb-de-décimales)-------------- FLOAT---| REAL ------------------------------------------------- DOUBLE--| CHAR(CHARACTERE)-| |--FOR BIT DATA--| VARCHAR------------(lg)---FOR-- --FOR SBCS -------------- |---n°-ccsid *---| DATE----------| TIME----------------------------------------------------- TIMESTAMP-----| * : un CSSID est un équivalent code-page associé à une donnée (france = 297) |
|
Autres fonctions: CONCAT( ) ancienne syntaxe : nom CONCAT prenom [ou !!] maintenant en plus : CONCAT(nom, prenom). SUBSTR(exp, dep, lg) extrait une chaîne de caracères depuis "dep" sur 'lg' caractères à partir d'une expression alpha. écriture V3R60 : SUBSTRING(zone FROM début ------------------------) !--FOR longueur--! si longueur n'est pas indiqué on va jusqu'à fin de zone LEFT(zone, lg) Extrait les "lg" caractères de gauche. RIGHT(zone, lg) Extrait les "lg" caractères de droite (V5R30) LENGTH(expr) donne la longueur physique d'une expression. (le résultat est donné sous forme binaire) |
|
ex :SUBSTR(nom, LENGTH(nom) -1 , 2) CHARACTER_LENGTH = nbr de caractères (zone à lg variable) ex :CHARACTER_LENGTH(trim(nom)) OCTET_LENGTH indique le nombre d'octets occupés par une colonne (V5R3) BIT_LENGTH() indique le nombre de bits occupés par une colonne (V5R3) TRANSLATE(exp) conversion minuscule/MAJUSCULE d'une chaîne ou UCASE( ) ou UPPER( ) mais aussi : |
|
TRANSLATE(chaîne-----------------------------------------------------> ! ! !---, remplacement ---------------------------! ! ! !-- , origine -----! >-------------------------------) ! ! !--PADDED WITH------! Exemple: TRANSLATE(var1 ,' ,F' , '0.$') remplace 0 par ' ' "." par "," et "$" par "F" . LOWER( ) ou LCASE( ) conversion MAJUSCULE/minuscule d'une chaîne LTRIM( ) , RTRIM( ) et TRIM( ) Supprimme les espaces à gauche, à droite ou aux deux extrémités. |
|
STRIP(exp, [BOTH] , [C]) LEADING TRAILING Alternative à TRIM (et aussi RTRIM et LTRIM) supprime le(s) caractère(s) "C" (dft = "espace") à gauche (LEADING), à droite (TRAILING) ou les deux (BOTH), de la chaîne indiquée. le résultat est de longueur variable. LOCATE(recherche, origine, ---------------) V4R20 |-départ--| indique si "recherche" est présent dans "origine" (à partir de "départ"). l'information retournée est numérique et indique la position de début. POSITION(recherche IN origine) [alias à LOCATE] |
|
REPEAT(c , nb) repète le caractère 'c', nb fois (V5R3) REPLACE(zone, org, new) remplace org par new dans zone (V5R3) INSERT(Z , deb, nb, chaine) (V5R3) insert 'chaine' à partir de 'deb' en remplacant nb caractères (0 = insertion pure). Exemple : sur une zone char(1) contenant '*' Insert(zone1 , 1 , 0 , 'XX') --> 'XX*' --on insert devant Insert(zone1 , 1 , 1 , 'XX') --> 'XX' --on remplace Insert(zone1 , 2 , 0 , 'XX') --> '*XX' --on insert derrière |
|
ENCRYPT_RC2(data, pwd , hint) (V5R3) Encrypte les données founies en premier argument en utilisant le deuxième comme clé, selon l'algorithme RC2. le mot de passe peut être fixé aussi par SET ENCRYPTION PASSWORD, avant. l'asctuce (facultative) est un "pense-bète" mémorisé avec la donnée. la zone réceptrice doit êtree CHAR FOR BIT DATA , BINARY ou BLOB V5R4 ENCRYPT_TDES(data , pwd, hint) comme ENCRYPT_RC2 mais en utilisant le cryptage "Triple DES" V6R1 ENCRYPT_TDES(data , pwd, hint) comme ENCRYPT_RC2 mais en utilisant le cryptage "AES" GETHINT() retourne l'astuce (le pense-bète) permettant de se souvenir de la clé (qui est obligatoire avec les fonctions ci-dessous) |
|
DECRYPT_BIT() décrypte une donnée cryptée et retourne du VARCHAR /BIT DATA DECRYPT_BINARY() décrypte une donnée cryptée et retourne du BINARY DECRYPT_CHAR() décrypte une donnée cryptée et retourne du VARCHAR simple DECRYPT_DB() décrypte une donnée cryptée et retourne du Double Byte (UTF-8) ABSVAL(exp) renvoie la valeur absolue d'une expression numérique SQRT(exp) renvoie la racine carrée (voir aussi sinus, cosinus,...) |
|
MOD(exp1, exp2) renvoie le reste de la division des deux arguments POWER(nombre, exposant) élévation à la puissance SIGN(colonne) retourne le signe d'une colonne -1 si négatif, 1 si positif strictement, 0 si null RAND() retourne un nombre aléatoire (< à 1) CEIL(colonne) Transforme un réel (avec décimales) en son entier immédiatement supérieur. CEIL(2,42) = 3 CEIL(2,56) = 3 FLOOR(colonne) Transforme un réel (avec décimales) en son entier immédiatement inférieur. FLOOR(2,42) = 2 FLOOR(2,56) = 2 |
|
ROUND(colonne,p) arrondi comptable d'une expression numérique avec choix de la precision. ROUND(2,42 , 1) = 2,40 ROUND(2,56 , 1) = 2,60 TRUNCATE(colonne,p) arrondi inférieur d'une expression numérique avec choix de la precision. TRUNCATE(2,42 , 1) = 2,4 TRUNCATE(2,56 , 1) = 2,5 MULTIPLY_ALT() alternative à l'opérateur *, travaille avec une plus grande précision, à utiliser avec des résultats intermédiaires de plus de 63 chiffres ! (V5R3) RRN(nom-table) renvoie le numéro de rang. DATABASE() retourne le nom du serveur comme CURRENT SERVER (V5R3) |
|
Gestion de la valeur nulle la valeur nulle (la différence entre un prix à 0 et un prix non renseigné) est bien intégrée à SQL. elle devient vite indispensable avec la gestion des dates (date de départ dans le fichier du personnel, par ex) elle est par défaut lors du CREATE TABLE et peut être précisée en SDD par le mot-clé ALWNULL. les tables contenant ces zones doivent être manipulées en RPG4 ou SQL les tests se font sous la forme : DAT_DEAPRT IS NULL PRIX IS NOT NULL |
|
VALUE(exp1, exp2 [,exp...] ) COALESCE et IFNULL alias de VALUE (IFNULL est limité à 2 arguments) renvoient la première valeur non nulle de la liste des expressions Intéressant sous la forme IFNULL(NOM, 'non précisé') pour attribuer une valeur par défaut si NOM est NULL. particulièrement lors des jointures non abouties, les colonnes de la table de droite sont initilaisées à NULL NULLIF(argument1, argument2) retourne NULL, si les deux arguments sont égaux. pour remplacer una valeur significative par la val. nulle. |
|
CAS PARTICULIER DES DATES DATE(expression) convertit "expression" au format DATE formats admis: TIMESTAMP (extrait la partie DATE) 7 ALPHA (format julien AAAAJJJ) ALPHA représentant une date éditée (AAAA-MM-JJ, par exemple) numérique représentant (nbr de jours - 1) depuis le 1er janvier de l'AN 1. DAY, MONTH, YEAR renvoient la partie concernée d'une expression date (voir ci dessus + variable de type DATE) DAYS(expression) renvoie le nombre de jours -1 séparant cette date du 1er janvier de l'an 1. |
|
MANIPULATION D'HORAIRES TIME(expression) convertit "expression" au format TIME formats admis: TIMESTAMP (extrait la partie TIME) ALPHA représentant une heure éditée (HH:MM:SS) HOUR, MINUTE, SECOND renvoient la partie concernée d'une expression TIME (voir ci dessus + variable de type TIME) TIMESTAMP (date +heure +6 décimales) TIMESTAMP(expression) convertit "expression" (horodatage édité) TIMESTAMP(date heure) produit l'horodatage correspondant (microsec. à zéro) |
|
NOTION DE DUREE: + Durées explicites basées sur un chiffre et un mot clé lui donnant du sens. - YEARS, MONTHS, DAYS - HOURS, MINUTES, SECONDS, MICROSECONDS servent aux calculs (ARRIVEE + 6 MONTHS + 15 DAYS) ainsi que - CURRENT DATE - CURRENT TIME - CURRENT TIMEZONE (fuseau horaire) (CURRENT TIMEZONE représente le décalage avec GMT ==> CURRENT TIME - CURRENT TIMEZONE = temps universel) |
|
+ Durées numériques (ou implicites) pour une date = valeur numérique 8,0 => AAAA MM JJ ainsi 615 (0000.06.15) = 6 mois et 15 jours pour une heure = valeur numérique 6,0 => HH MM SS pour un horodatage = 20,6 (décimales = microsecondes) INCREMENTATION, DECREMENTATION, DIFFERENCE AJOUT (+) TOUJOURS Date + durée = date heure + durée = heure si le mois est modifié en dépassant 12, il est ajusté et l'année est incrémentée (idem jours, minutes, secondes) |
|
RETRAIT (-) DATE - durée = DATE : il s'agit d'une décrémentation DATE - DATE = durée : il s'agit d'une différence la durée est exprimée en durée numérique à l'affichage ainsi 010514 ==> 1 an , 5 mois et 14 jours. une durée explicite peut être utilisée lors d'un calcul WHERE (DATliv - 2 MONTHS) > DATcde un calcul produit FORCEMENT un résultat de type durée numérique et doit donc être comparé avec une valeur numérique (aaaammjj ou hhmmss). WHERE (DATliv - DATcde) < 200 (soit 2 mois) |
|
Autres Fonctions DATE : CURDATE() = idem au registre CURRENT DATE CURTIME() = " " CURRENT TIME NOW() = " " CURRENT TIMESTAMP DAYOFMONTH(date) = jour dans le mois (idem DAY().) DAYOFWEEK(date) = jour dans la semaine (1=dimanche) DAYOFWEEK_ISO(date) = jour dans la semaine (1=Lundi) [V5R10] DAYOFYEAR(date) = jour (julien) dans l'année. QUARTER(date) = N° du trimestre |
|
WEEK(date) = N° de la semaine ATTENTION : 1er Janvier = semaine 1 WEEK_ISO(date) = N° de la semaine, 1er janvier = 1 ou 53. [V5R10] DAYNAME(Date) retourne le nom du jour (en Français) de Date (V5R3) MONTHNAME(Date) retourne le nom du mois (en Français) de Date (V5R3) EXTRACT(DAY from zonedate) extrait la partie JOUR de zone date, on peut demander : DAY, MONTH, YEAR d'une date HOUR, MINUTE SECOND d'une heure TIMESTAMP_ISO() convertit en TIMESTAMP : une date (l'heure est à 00:00:00) ou une heure(la date est à aujourd'hui) |
|
V5R40
LAST_DAY(date) retourne la date correspondant au dernier jour du mois
par exemple LAST_DAY('2006-04-21') = 2006-04-30
ADD_MONTHS(date, nbr) ajoute un nombre de mois à la date
Attention, si la date est au dernier jour du mois, la date
calculée est aussi au dernier jour du mois
par exemple DATE('2006-04-30') + 1 months = 2006-05-30
ADD_MONTHS('2006-04-30' , 1) = 2006-05-31
GENERATE_UNIQUE() genère une valeur unique de type CHAR(13)
(basée sur le timestamp en cours)
la fonction TIMESTAMP() peut-être utilisée pour
convertir en clair la valeur générée.
|
|
NEXT_DAY(date , 'JOUR')
retourne le timestamp de la prochaine date ayant le
jour demandé (sur 3 c ou 10c.) à partir de "date".
valeurs admises :
'MON' ou 'LUN' ou 'LUNDI' pour le prochain Lundi
'TUE' ou 'MAR' ou 'MARDI', prochain Mardi
'WED' ou 'MER' ou 'MERCREDI', prochain Mercredi
'THU' ou 'JEU' ou 'JEUDI', prochain Jeudi
'FRI' ou 'VEN' ou 'VENDREDI', prochain Vendredi
'SAT' ou 'SAM' ou 'SAMEDI', prochain Samedi
'SUN' ou 'DIM' ou 'DIMANCHE', prochain Dimanche
Exemple :
NEXT_DAY('2006-12-31' , 'DIM') => ' 2007-01-07-00.00.00.000000'
|
|
CASE : condition à la réalisation d'une expression Exemple : ...................................................... : SELECT MATRICULE, NOM, : : : : CASE SUBSTR(SERVICE, 1, 3) : : : : WHEN 'INF' THEN 'INFORMATIQUE' : : WHEN 'PER' THEN 'PERSONNEL' : : WHEN 'PRO' THEN 'PRODUCTION' : : WHEN 'COM' THEN 'COMMERCIAL' : : ELSE 'Divers ....' : : END : : : : FROM personnel WHERE ... : : : :....................................................: |
|
on peut indiquer la colonne testée sur la clause WHEN ........................................................ : SELECT CODART, LIBART, PRIX, : : CASE : : WHEN PRIX < 0 THEN 'prix négatif' : : WHEN CODART = 1557 THEN 'pas de prix' : : ELSE 'prix normal' END : : : : FROM ... WHERE ... : :......................................................: ......................................................................... : SELECT * FROM COMMANDES WHERE : : (CASE WHEN PRIXMOYEN = 0 THEN PRIXTARIF * QTE : : ELSE PRIXMOYEN * QTE END) > 10000 : :.......................................................................: le test peut être IS NULL / IS NOT NULL la valeur retournée (THEN|ELSE) peut être le mot réservé NULL |
|
Autre exemple avec CASE, il faut compter par client le nombre de réglement par type (chèque, espèce et CB). Il existe dans l'entête de facture, un code réglement 'CH', 'ES', 'CB' : .......................................................................... : : : Select nocli , : : : : sum(CASE cdreglement when 'CH' then 1 else 0 END) as CHEQUE , : : sum(CASE cdreglement when 'ES' then 1 else 0 END) as ESPECE , : : sum(CASE cdreglement when 'CB' then 1 else 0 END) as CARTE : : : : from factures GROUP BY nocli : :........................................................................: |
|
+ nouvelles fonctions V4R40, liées aux DataLink :
DLVALUE ('HTTP://S44R7480/AF4DIR/AF4SRC/cours.htm', URL, 'mon commentaire')
permet de renseigner un champ DataLink (ici, un lien vers "cours.htm")
DLURLCOMPLETE( ) -> retourne le lien complet vers le fichier stream.
......................................................
: 'HTTP://S44R7480/AF4DIR/AF4SRC/*****cours.htm' :
: ('*****' est le token pour les permissions DB) :
:....................................................:
DLURLPATH( ) -> /AF4DIR/AF4SRC/*****cours.htm
DLURLPATHONLY( ) -> /AF4DIR/AF4SRC/cours.htm
DLCOMMENT( ) -> 'mon commentaire'
DLLINKTYPE( ) -> URL (seule valeur admise aujourd'hui)
DLURLSCHEME( ) -> HTTP: [ou FILE:]
DLURLSERVER( ) -> S44xxxxx
|
|
+ gestion des champs auto-incrémentés (AS IDENTITY) IDENTITY_VAL_LOCAL() retoune la dernière valeur produite pour ce type + fonctions phonétiques SOUNDEX(colonne) fourni un algoritme retournant une représentation phonétique de "colonne" (surtout les noms propres) permettant de meilleurs comparaisons Ce codage a été utilisé aux états-unis lors de recensements voir http://www.bradandkathy.com/genealogy/overviewofsoundex.html et http://www.nara.gov/genealogy/soundex/soundex.html s'utilise sous la forme: WHERE SOUNDEX(NOM) = SOUNDEX('HENRI') [=H560] |
|
1/ conversion en majuscule 2/ conservation du premier caractère 3/ suppression de toutes les occurences des lettres dont la liste suit, à partir de la première occurence: A, E, H, I, O, U, W, Y 4/ attribution d'un code numérique, suivant le type de lettre (labiale, dentale, ....) 1 pour B F V P 4 pour L 2 " C G J K Q S X Z 5 " M N 3 " D T 6 " R (cette série est donnée pour l'anglo-américain, pour l'adaptation fançaise voir http://www.chez.com/algor/soundex/soundex.htm ) 5/ élimination de toutes les paires consécutives de chiffres dupliqués. |
|
6/ ne conserver de la chaîne que les quatres premières positions
(complétées par des "0", si besoin)
sous la forme Lccc L étant la première lettre [ cf 2/ ]
ccc étant le résultat produit en 6/
ainsi SOUNDEX('troi') retourne T600 , SOUNDEX('Troye') aussi.
mais SOUNDEX('trois') retourne T620
ce qui, en français, est un problème.
Le lab précise qu'il n'y a pas d'adaptation langage de l'algorithme.
DIFFERENCE(col1 , col2) indique la différence phonétique (de 0 à 4)
basée sur SOUNDEX, entre les deux arguments.
0: les deux arguments sont trés différents
4: ils sont très semblables.
|
|
+ nouvelles fonctions OLAP, liées à la V5R40 : ROW_NUMBER() numérote les lignes sur un critère de tri -> select row_number() over (order by nbr desc), pr_code, nbr from stat --nbr de vins/producteur Affiche : ROW_NUMBER PR_CODE NBR 1 2.432 26 2 2.455 26 3 2.486 22 ... DENSE_RANK() attribue un rang consécutif RANK() attribue un rang non consécutif (en gérant les ex-aequo) |
|
select rank() over (order by nbr desc) , pr_code, nbr , dense_rank over (order by nbr desc) from stat Affiche : RANK PR_CODE NBR DENSE_RANK 1 2.432 26 1 1 2.455 26 1 3 2.486 22 2 4 2.547 20 3 4 6.390 20 3 6 2.461 19 4 6 4.321 19 4 6 6.343 19 4 Dans tous les cas, le critère de tri à l'affichage (ORDER BY final) peut être différent du critère indiqué dans OVER. |
|
Version 7 Support du type XML et des fonctions suivantes XMLDOCUMENT production d'un flux XML à partir d'une chaine de caractère XMLPARSE production après vérification, d'un flux XML XMLVALIDATE validation d'un flux XML à l'aide d'un schéma XSD XMLTRANSFORM transforme un flux XML à l'aide de XSLT XMTEXT production d'un texte compatible XML XMLELEMENT production d'un élément XML XMLATTRIBUTES production d'un attribut XML XMLNAMESPACES production d'un balise d'espace de nommage XMLPLI production d'une balise processing instruction XMLCOMMENT production d'un commentaire XML XMLCONCAT production d'un flux XML à partir de deux XMLFOREST production d'une suite d'élements XML à partir des colonnes d'une table XMLROW production d'une ligne XML à partir des colonnes d'une table fonctions d'agrégation (récapitulatives) XMLAGG production d'une série d'éléments XML XMLGROUP production d'un flux XML valide. |
|
Fonctions de lecture des fichiers de l'IFS: GET_BLOB_FROM_FILE(chemin , option) retourne un BLOB LOCATOR, sans conversion du CCSID GET_CLOB_FROM_FILE(chemin , option) retourne un CLOB LOCATOR dans le CCSID du job GET_DBCLOB_FROM_FILE(chemin , option) retourne un DBCLOB LOCATOR dans le CCSID DBCS par défaut GET_XML_FILE(chemin) retourne un BLOB LOCATOR en UTF-8, si ce dernier ne possède pas de déclaration XML la fonction l'ajoute. la zone option peut contenir : 0 : les espaces de droite sont conservés 1 : les espaces de droite sont ignorés |
|
EXEMPLES DE SELECTIONS VALIDES : + SELECT * FROM SQLDB/STGTBL (toutes les variables) + SELECT NOM, AGENCE FROM SQLDB/STGTBL (projection) + SELECT CODART, LIBART, QTESTO, PUART, (QTESTO * PUART) as montant FROM SQLDB/STOCKP1 + SELECT MATRIC, NOM, SUBSTR(PRENOM, 1, 8), (SALBRU * 13) FROM SQLDB/PERSONP1 + SELECT NOM, (CURRENT DATE - ARRIVEE) FROM SQLDB/PERSONP1 + SELECT NOM, PRENOM, IFNULL(FONCTION, '*inconnue') FROM SQLDB/PERSONP1 + SELECT TRANSLATE(ADRESSE) ... |
|
remarque :
La qualification BIBLI/TABLE n'est valide
que si la convention d'appellation système
a été choisie sur la commande STRSQL.
Si la table n'est pas qualifiée la recherche
est faite dans *LIBL
Il faudra qualifier bibli.table si on choisit
la convention d'appellation SQL.
Si la table n'est pas qualifiée la recherche
est faite dans la bibliothèque ayant le même
nom que l'utilisateur (sur 8 c.)
Une fonction peut être testé par VALUES
+ VALUES TRANSLATE('Volubis')
|
|
FROM ------- TABLE1, TABLE2,.. (32 tables maxi) IL EST POSSIBLE DE QUALIFIER LA TABLE (suivant la convention choisie) IL EST POSSIBLE D'ASSOCIER A UNE TABLE UNE CORRELATION (un nom interne) QUI POURRA ETRE UTILISEE EN DEFINITION DE COLONNE exemple: FROM SQLDB/STGTBL A, SQLDB/AGETBL B La table STGTBL est connue sous le "nom" A AGETBL sous le "nom" B |
|
Jonction: SI la clause WHERE n'est pas utilisée SQL joint à CHAQUE enregistrement de TABLE1 TOUS les enregistrements de TABLE2. norme ISO 89: + SELECT NOM, PRENOM, STGTBL.AGENCE, LIBAGE FROM SQLDB/STGTBL, SQLDB/AGETBL WHERE STGTBL.AGENCE = AGETBL.AGENCE (la variable AGENCE étant dupliquée il devient obligatoire de qualifier la variable par le nom de la table "STGTBL.AGENCE") OU + SELECT NOM, PRENOM, A.AGENCE, LIBAGE FROM SQLDB/STGTBL A, SQLDB/AGETBL B WHERE A.AGENCE = B.AGENCE (Utilisation des corrélations) |
|
la jonction norme 92 peut être définie avec la clause JOIN de la manière suivante (disponible depuis la V3R10) + produit cartésien: SELECT ... FROM table1 CROSS JOIN table2 (équivalent à l'absence de clause WHERE) + jointure conditionnelle SELECT ... FROM table1 JOIN table2 ON zone1 = zone2 (toute expression logique est acceptée après ON) les jointures sont par défaut internes (elles n'affichent que les enregistrements en correspondance) On parle de INNER JOIN (qui est indentique à JOIN seul) |
|
+ jointure externe (OUTER JOIN) on parle de LEFT OUTER JOIN quand on désire tous les enregistrements du fichier1 (celui à gauche du mot JOIN) qu'ils soient ou non en correspondance avec le fichier2 [comme JDFTVAL/SDD ou OPNQRYF JDFTVAL(*YES)] pour SQL/400 LEFT JOIN est identique à LEFT OUTER JOIN les colonnes de table2 sont initialisées à NULL. ce qui rend très intéressantes deux fonctions : IFNULL() et VALUE() qui assignent une valeur de remplacement à une colonne contenant la valeur nulle. et la clause IS NULL à utiliser dans un test (WHERE CODE IS NULL) |
|
exemples : liste des clients, ayant passé des commandes : SELECT codcli, nomcli, numcde, datcde, datliv FROM clients c JOIN command d ON c.numcli = d.numcli liste des clients, avec, pour ceux d'entre eux ayant passé des commandes, la liste des commandes : SELECT codcli, nomcli, numcde, datcde, datliv FROM clients c LEFT OUTER JOIN command d ON c.numcli = d.numcli WHERE ... liste des stagiaires (si l'agence est inconnue on lui attribut la valeur 'invalide') SELECT NOM, PRENOM, STGTBL.AGENCE, IFNULL(LIBAGE, 'invalide') FROM STGTBL S LEFT OUTER JOIN AGETBL A ON S.NOSTAG = A.NOSTAG |
|
+ traitement des enregistrements sans correspondance SELECT ... FROM table1 EXCEPTION JOIN table2 ON zone1 = zone2 ne fournit que les enregistrements de table1 n'ayant pas d'équivalence dans table2 [comme OPNQRYF JDFTVAL(*ONLYDFT)] exemple : liste des stagiaires enregistrés sous une agence inconnue. SELECT NOM, PRENOM FROM STGTBL EXCEPTION JOIN AGETBL A ON S.NOSTAG = A.NOSTAG |
|
V5R10 : - RIGHT OUTER JOIN jointure externe à partir du fichier de droite du mot-clé JOIN select ... from clients RIGHT OUTER JOIN commandes (toutes les commandes ,même si le client est inconnu) - RIGHT EXCEPTION JOIN select ... from clients RIGHT EXCPTION JOIN commandes (toutes les commandes dont le client est inconnu) V5R30 : SELECT * from clients JOIN commandes USING( numcli ) -> si la zone porte le même nom dans les deux fichiers. |
|
WHERE ------- SELECTIONS POSSIBLES + = egal <> non egal (expression1> > <expression2> sup < inf >= sup ou egal <= inf ou egal WHERE codart = 0 .... WHERE qtecde <> 0 ... |
|
+ <expression1> BETWEEN <exp2> AND <exp3> compris entre <exp2> et <exp3> bornes incluses WHERE DEPT BETWEEN 22 and 44 !-----USER----------! + <expression1> LIKE !--:variable--------!---------------- !--chaine de carac--! ! ! !--CURRENT SERVER---! !-ESCAPE--! Un "%" dans la chaine de caractères indique que n'importe quel nombre de n'importe quel caractère peut occuper cette position Un "_" (soulignement) dans la chaine de caractère indique que n'importe quel caractère peut occuper cette position. |
|
ex: LIKE '%ABC%' est vrai si la chaine "ABC" est trouvée à n'importe quel endroit de la variable ex: LIKE 'ABC%' est vrai si la variable commence par "ABC" ex: LIKE 'A_B est vrai si la variable commence par "A" suivi d'un caractère quelconque puis par "BC" USER le contenu de la variable est comparé avec le profil utilisateur en cours CURRENT SERVER le contenu de la variable est comparé avec le nom du serveur en cours (cf CONNECT) :variable, le contenu est comparé avec une variable du pgm. ESCAPE permet de fournir un caractère d'éachappement. par ex LIKE '%A+%ABC%' ESCAPE '+' cherche "A%BC" V5R10 : les expressions sont admises dans la clause LIKE. where nom like '%' concat trim(COMMUNE) concat '%' |
|
+ <expression1> IN (<exp2>, <exp3>) Liste de valeurs à comparer ces valeurs pouvant être: I-USER----------I ------I-:variable-----I (cf LIKE) I-constante-----I WHERE DEPT IN (22, 29, 35, 44) Remarque : Il est toujours préferable de comparer une colonne avec une valeur de même définition, même longueur. TOUTES CES CONDITIONS PEUVENT être RELIEES PAR DES "OR" ET DES "AND" et/ou inversées (niées) par "NOT". |
|
EXEMPLES DE SELECTIONS VALIDES + SELECT * FROM SQLDB/STGTBL WHERE AGENCE = 01 + SELECT NUMSTG, NOM, PRENOM FROM SQLDB/STGTBL WHERE NUMSTG BETWEEN 0001 AND 0010 AND AGENCE <> 01 + SELECT NOM, AGENCE FROM SQLDB/STGTBL WHERE NOM LIKE "D%" on peut bien sur utiliser des fonctions + SELECT NOM, AGENCE FROm SQLDB/STGTBL WHERE TRANSLATE(NOM) LIKE "D%" |
|
GROUP BY ---------- Cette clause ne donne qu'une ligne par groupe d'enregistrements il n'est pas possible de demander des variables qui ne sont pas precisées dans le GROUP BY Fonctions associées : ces fonctions utilisées sans GROUP BY donnent un résultat général AVG(exp) moyenne ex. AVG(SALBRU) moyenne des salaires COUNT(*) nb d'enregistrements sélectionnés |
|
COUNT(DISTINCT col1) nb de valeurs rencontrées pour col1. ex: SELECT COUNT(DISTINCT nocli) FROM ENTETECDE WHERE DATLIV > CURRENT DATE ici, il faut compter le nombre de clients et non le nombre de lignes qui donnerait le nombre de commandes. MAX(exp) valeur la plus grande rencontrée (y compris dates) ex: MAX(SALBRU) plus grand salaire mais aussi : MAX(PRXACHAT , PRXMOYEN) la plus grande des deux colonnes MIN(exp) valeur la plus petite rencontrée (y compris dates) ex: MIN(SALBRU) plus petit salaire |
|
mais aussi : MIN(DAT1 , DAT2) idem fonction MAX. SUM(exp) somme ex: SUM(SALBRU) somme des salaires SUM(QTESTO * PUART) somme des montants VAR(exp) variance la formule est VAR(x) = SUM(x**2)/COUNT(x) - ( (SUM(x)/COUNT(x))**2) pour [10,12,7] la variance est 4,22 STDDEV(exp) écart-type la formule est STDDEV(x) = SQRT(VAR(X)) pour [10,12,7] l'écart-type est 2,04 |
|
EXEMPLES DE SELECTIONS VALIDES + SELECT COUNT(*), AGENCE FROM SQLDB/STGTBL GROUP BY AGENCE donne le nombre de stagiaires par agence + SELECT AVG(SALBRU), SERVICE, COEFF FROM SQLDB/PERSONP1 GROUP BY SERVICE, COEFF donne la moyenne des salaires par service et coef du fichier personnel EXEMPLES DE SELECTION INVALIDE + SELECT COUNT(*), NOM, AGENCE FROM SQLDB/STGTBL GROUP BY AGENCE la variable nom n'identifiant pas un groupe |
|
HAVING -------- Permet de donner des conditions sur le résultat d'un traitement récapitulatif EXEMPLE + SELECT COUNT(*), AGENCE FROM SQLDB/STGTBL GROUP BY AGENCE HAVING COUNT(*) > 20 donne le nombre de stagiaires par agence, uniquement pour les agences ayant plus de 20 stagiaires |
|
ORDER BY ---------- Permet de donner un ordre de classement ORDER BY ----nom-colonne------------------------. I--n° de déclaration-I I-ASC--I I-DESC-I Sur le résultat d'un GROUP BY les enregistrements sont classés sur le critère de traitement récapitulatif EXEMPLES DE SELECTIONS VALIDES + SELECT * FROM SQLDB/STGTBL ORDER BY NOM + SELECT MATRIC, NOM, SUBSTR(PRENOM, 1, 8) FROM SQLDB/PERSONP1 ORDER BY 3 (classement sur début du prénom) |
|
V4R40 les expressions sont maintenant admises sur les clauses GROUP BY ORDER BY à condition qu'elles soient indiquées aussi (et à l'identique) sur la clause SELECT. soit bdist dans le fichier clients contenant dep+bureau (44000 par ex.) SELECT substr(digits(bdist), 1, 2), COUNT(distinct NOCLI) from clients group by substr(digits(bdsit), 1, 2) donne le nombre de clients par département. |
|
SELECT bdist, COUNT(distinct NOCLI) from clients group by substr(digits(bdsit), 1, 2) est invalide, l'expression n'étant pas présente sur le SELECT. ORDER BY offre rigoureusement les mêmes possibilités. tout en conservant la possibilité d'indiquer un N° de colonne (N° dans le select bien sur) SELECT codcli, left(raisoc, 20) SELECT codcli, left(raisoc, 20) from clients Ou from clients ORDER BY 2 ORDER BY left(raisoc, 20) |
|
V6 : la clause GROUP BY évolue beaucoup pour implémenter des fonctions OLAP Soit un fichier CAVE et un select GROUP BY suivant : SELECT year(entreele) , cav_format, sum(prxactuel) FROM ma_cave join vins using(vin_code) GROUP BY year(entreele) , cav_format affichant YEAR CAV_FORMAT SUM 2.006 CAISSE de 6 20,82 2.008 Bouteille 5,35 2.007 Bouteille 278,15 2.008 CAISSE de 6 15,76 2.006 Bouteille 278,78 2.007 CAISSE DE 6 28,96 Le montant des vins par année et format de stockage (un niveau de rupture) Nous allons ajouter les nouvelles clauses : GROUPING SETS ROLLUP CUBE |
|
SELECT year(entreele) , cav_format, sum(prxactuel) FROM ma_cave join vins using(vin_code) GROUP BY GROUPING SETS (year(entreele) , cav_format) affiche le total par année, PUIS le total par format YEAR CAV_FORMAT SUM 2.008 - 21,11 2.006 - 299,60 2.007 - 307,11 - CAISSE DE 6 28,96 - CAISSE de 6 36,58 - Bouteille 562,28 Cette clause admet la syntaxe suivante: SELECT year(entreele), MONTH(entreele), cav_format, sum(prxactuel) FROM ma_cave join vins using(vin_code) GROUP BY GROUPING SETS ( (year(entreele) , cav_format) , (year(entreele) , month(entreele)) ) |
|
YEAR MONTH CAV_FORMAT SUM 2.006 - CAISSE de 6 20,82 2.008 - Bouteille 5,35 2.007 - Bouteille 278,15 2.008 - CAISSE de 6 15,76 2.006 - Bouteille 278,78 2.007 - CAISSE DE 6 28,96 2.007 8 - 15,59 2.006 7 - 14,40 2.008 3 - 15,76 2.007 3 - 20,82 2.007 9 - 155,48 2.007 2 - 12,19 2.007 1 - 28,96 2.006 11 - 35,00 2.006 4 - 20,82 2.007 6 - 74,07 2.006 9 - 229,38 2.008 5 - 5,35 |
|
SELECT year(entreele) , cav_format, sum(prxactuel) FROM ma_cave join vins using(vin_code) GROUP BY ROLLUP (year(entreele) , cav_format) affiche le total par année/format, puis par année, puis le total général YEAR CAV_FORMAT SUM 2.006 Bouteille 278,78 2.006 CAISSE de 6 20,82 2.006 - 299,60 2.007 Bouteille 278,15 2.007 CAISSE DE 6 28,96 2.007 - 307,11 2.008 Bouteille 5,35 2.008 CAISSE de 6 15,76 2.008 - 21,11 - - 627,82 Vous pourriez compléter le select par un ORDER BY |
|
SELECT year(entreele) , cav_format, sum(prxactuel) FROM ma_cave join vins using(vin_code) GROUP BY CUBE (year(entreele) , cav_format) affiche tous les totaux de toutes les combinaisons (un cube, donc !) YEAR CAV_FORMAT SUM 2.006 Bouteille 278,78 2.006 CAISSE de 6 20,82 2.006 - 299,60 2.007 Bouteille 278,15 2.007 CAISSE DE 6 28,96 2.007 - 307,11 2.008 Bouteille 5,35 2.008 CAISSE de 6 15,76 2.008 - 21,11 - - 627,82 - CAISSE DE 6 28,96 - CAISSE de 6 36,58 - Bouteille 562,28 |
|
Enfin, vous pouvez utiliser la syntaxe suivante : SELECT year(entreele) , month(entreele), cav_format, sum(prxactuel) FROM ma_cave join vins using(vin_code) GROUP BY GROUPING SETS( ROLLUP(year(cav_entreele) , cav_format) , ROLLUP(year(cav_entreele), month(cav_entreele)) ) affiche des totaux par année/format, contenant des sous totaux par année et un total général(ROLLUP), puis la même chose pour le couple année/mois YEAR MONTH CAV_FORMAT SUM 2.006 - Bouteille 278,78 2.006 - CAISSE de 6 20,82 2.006 - - 299,60 2.007 - Bouteille 278,15 2.007 - CAISSE DE 6 28,96 2.007 - - 307,11 2.008 - Bouteille 5,35 2.008 - CAISSE de 6 15,76 2.008 - - 21,11 - - - 627,82 |
|
2.006 4 - 20,82 2.006 7 - 14,40 2.006 9 - 229,38 2.006 11 - 35,00 2.006 - - 299,60 2.007 1 - 28,96 2.007 2 - 12,19 2.007 3 - 20,82 2.007 6 - 74,07 2.007 8 - 15,59 2.007 9 - 155,48 2.007 - - 307,11 2.008 3 - 15,76 2.008 5 - 5,35 2.008 - - 21,11 - - - 627,82 nouvelle fonction liée GROUPING(col) indique si la ligne en cours est une ligne de regroupement concernant cette colonne (information utile en programmation) |
|
Exemple SELECT year(entreele) , cav_format, sum(prxactuel) , GROUPING(cav_format) FROM ma_cave join vins using(vin_code) GROUP BY CUBE (year(entreele) , cav_format) YEAR CAV_FORMAT SUM GROUPING 2.006 Bouteille 278,78 0 2.006 CAISSE de 6 20,82 0 2.006 - 299,60 1 2.007 Bouteille 278,15 0 2.007 CAISSE DE 6 28,96 0 2.007 - 307,11 1 2.008 Bouteille 5,35 0 2.008 CAISSE de 6 15,76 0 2.008 - 21,11 1 - - 627,82 1 - CAISSE DE 6 28,96 0 - CAISSE de 6 36,58 0 - Bouteille 562,28 0 |
|
Enfin, en V5R10, l'ordre SELECT peut-être completé au final, par : FETCH FIRST x ROWS ONLY qui indique à SQL de ne retourner que les "x" premières lignes. très pratique pour obtenir les meilleurs scores (Top 10, Top 100, ...) select codart , count(*) from commandes group by codart order by 2 FETCH FIRST 50 ROWS ONLY vous donne les 50 articles les moins commandés (en nb de commandes) select codart , count(*) from commandes group by codart order by 2 DESC FETCH FIRST 50 ROWS ONLY vous donne les 50 articles les plus commandés. |
|
SELECT EN CREATION DE VUE (mémorisation d'un ordre SELECT) --------------------------- CREATE VIEW nomvue AS SELECT NOM, PRENOM FROM SQLDB/STGTBL (projection) CREATE VIEW nomvue (NM, PM) AS SELECT NOM, PRENOM FROM SQLDB/STGTBL (projection , variables renommées) CREATE VIEW nomvue (NOM, PRENOM, STGAGE, LIBAGE) AS SELECT NOM, PRENOM, X.AGENCE, LIBAGE FROM SQLDB/STGTBL X JOIN SQLDB/AGETBL Y ON X.AGENCE = Y.AGENCE (jonction de deux fichiers) Vous pouvez aussi mémoriser des fonctions récapitulatives, utilisables par pgm en lecture séquentielle : |
|
CREATE VIEW nomvue (AGENCE, NBSTAG) AS SELECT AGENCE, COUNT(*) FROM SQLDB/STGTBL GROUP BY AGENCE (traitement récapitulatif) AUTRES SELECT --------------- + SELECT DISTINCT AGENCE FROM SQLDB/STGTBL ********** * AGENCE * 1 ligne par valeur ********** * 01 * * 02 * * 03 * ********** |
|
+ SELECT UNION (ALL) place le résultat d'un ordre SELECT à la suite d'un précedent. les colonnes sélectionnées doivent être comparables pour toutes les tables. EX: soit la table des formateurs suivante: ****************************************** * AGENCE * NOFORM * NOMFORM * ****************************************** * 01 * 01 * jean-claude * * 01 * 02 * albert * * 01 * 03 * dominique * * 02 * 04 * marie * * 02 * 05 * françois * * 03 * 07 * christine * * 03 * 08 * isabelle * ****************************************** |
|
SELECT 'nb de stagiaires', COUNT(*), AGENCE FROM SQLDB/STGTBL GROUP BY AGENCE UNION ALL SELECT 'nb de formateurs', COUNT(*), AGENCE FROM SQLDB/FRMTBL GROUP BY AGENCE Donnera le nombre de formateurs par agence puis le nombre de stagiaires par agence (1er SELECT placé à la suite du 2ème) |
|
****************************************** * * COUNT( * ) * AGENCE * ****************************************** * nb de formateurs * 03 * 01 * * nb de formateurs * 02 * 02 * * nb de formateurs * 02 * 03 * * nb de stagiaires * 35 * 01 * * nb de stagiaires * 25 * 02 * * nb de stagiaires * 25 * 03 * ****************************************** SI ALL n'est pas renseigné il n'y aura pas de ligne dupliquée (sont considérées comme lignes dupliquées deux lignes venant de SELECT différents et dont TOUTES les colonnes ont le même contenu) |
|
SELECT 'nb de stagiaires', COUNT(*), AGENCE FROM SQLDB/STGTBL GROUP BY AGENCE UNION ALL SELECT 'nb de formateurs', COUNT(*), AGENCE FROM SQLDB/FRMTBL GROUP BY AGENCE Order by 3 Affiche : ****************************************** * * COUNT( * ) * AGENCE * ****************************************** * nb de formateurs * 03 * 01 * * nb de stagiaires * 35 * 01 * * nb de formateurs * 02 * 02 * * nb de stagiaires * 25 * 02 * * nb de formateurs * 02 * 03 * * nb de stagiaires * 25 * 03 * ****************************************** |
|
L'ordre SQL SELECT complet (FULL SELECT) admet en V5R30 les opérateurs INTERSECT et EXCEPT en plus de l'opérateur UNION ALL ou UNION DISTINCT INTERSECT (ou INTERSECT DISTINCT, qui est identique) affiche les enregistrements qui se trouvent dans l'une ET l'autre des requêtes. EXCEPT (ou EXCEPT DISTINCT, qui est identique) affiche les enregistremen qui se trouvent dans l'une ET PAS dans l'autre des requêtes. SELECT matricule, nom from personnel WHERE departement = 'INFO' EXCEPT SELECT matricule, nom from absence WHERE year(dateabs) = year(now()) and code = 'M' Donne la liste des lignes qui sont dans la première requête uniquement. ( les personnes du département INFO qui n'ont pas été absent cette année pour cause de maladie) INTERSECT donne la liste des lignes qui sont dans les deux requêtes |
|
récapitulatif, soit deux fichiers f1 et f2 : ...... ...... | : f1 : : f2 : | UNION UNION DISTINCT INTERSECT f1 EXCEPT f2 : A : : A : | A A A D : A : : B : | A B B E : B : : B : | A C C : C : : C : | B D : C : : F : | B E : D : : F : | B F f2 EXCEPT f1 : E : :....: | C F : E : | C :..... | C | D | E | E | F | F | |
|
SOUS SELECTIONS ----------------- SQL/400 (depuis la R 3.0) nous donne la possibilité d'utiliser un ordre SELECT dans la clause WHERE d'un ordre SELECT. Exemples: LISTE DES STAGIAIRES AYANT UNE NOTE < A LA MOYENNE SELECT * FROM STGTBL WHERE note < (SELECT avg(note) FROM STGTBL) même principe, précédé de la moyenne SELECT NOM, NOTE FROM STGTBL WHERE note <(SELECT avg(note) FROM STGTBL) UNION SELECT 'MOYENNE ', avg(note) from STGTBL |
|
il est possible d'utiliser les corrélations dans une sous-sélection. LISTE DES STAGIAIRES AYANT UNE NOTE< A LA MOYENNE DE LEUR SESSION (COMPARAISON SUR LA DATE D'ENTREE) SELECT NOM, PRENOM, NOTE FROM STGTBL S WHERE NOTE < ( SELECT avg(NOTE) from STGTBL WHERE ENTREE = S.ENTREE ) il s'agit ici d'aller réactualiser la moyenne à chaque fois que la date d'entrée change dans la version S du fichier. il est possible d'imbriquer 32 sélections (attention à la lisibilité) |
|
On peut tester l'existence d'une information dans une autre table de deux manières: LISTE DES STAGIAIRES AVEC UNE AGENCE INCONNUE SELECT * FROM STGTBL WHERE AGENCE NOT IN (SELECT AGENCE FROM AGETAB) mais cela devient compliqué si la clé est complexe (plus d'une colonne) il faudra alors utiliser la syntaxe suivante : SELECT * FROM STGTBL S WHERE NOT EXISTS (SELECT * FROM AGENCE WHERE AGENCE = S.AGENCE) |
|
Il est possible d'utiliser les opérateurs ALL, ANY ou SOME LISTE DES STAGIAIRES ENREGISTRES SOUS UNE AGENCE INCONNUE SUIVIE DE LA LISTE DES STAGIAIRES AVEC LIBELLE DE L'AGENCE SELECT S.NOM, S.PRENOM, A.LIBAGE FROM STGTBL S, AGETBL A WHERE S.AGENCE = A.AGENCE UNION SELECT NOM, PRENOM, 'INCONNUE ' FROM STGTBL S WHERE AGENCE <> ALL (SELECT * FROM AGETAB) NOT EXISTS est identique à <> ALL (SELECT ...) EXISTS est identique à = ANY (SELECT ...) EXISTS est vrai si le SELECT extrait 1 ligne ou plus est faux si le SELECT n'extrait aucune ligne En l'absence d'EXISTS, SOME, ALL ou ANY les sous-sélections ne doivent extraire qu'une ligne et une seule. |
|
V4R40 tables dérivées : on peut indiquer un ordre SELECT dans la clause from d'un ordre SELECT ou bien déclarer juste avant le SELECT une "vue temporaire" par WITH. cela permet d'éviter des créations de vues dans les cas complexes. deux écritures sont donc possibles ---------------------------------- ........................... WITH correlation AS ( select ... from) : on parle de CTE, soit : : COMMON TABLE : SELECT ... from correlation : Expression : :.........................: ou SELECT ... FROM (select ... from ...) |
|
exemple , soit un fichier des cours, chaque cours est enregistré sous module de cours. je veux le nombre de cours du module qui en a le plus. .................................................................... : WITH temp AS : : ( select count(*) as nbr from Fmodules group by codmodul ) : : : : select max(nbr) : : from temp : :..................................................................: est l'équivalent de : create view v1 as select count(*) as nbr .... from ... puis select max(nbr) from v1 cela permet d'imbriquer des fonctions MAX(COUNT( ..)) étant invalide. |
|
deuxième écriture directement dans la clause FROM soit le fichier cours (vu plus haut), le fichier des modules (donnant un texte explicatif à chaque module) un fichier des exemples, permettant d'attacher x exemples à un cours. (x pouvant être 0) je veux obtenir pour chaque module ................................................ : nom | texte | nbr de cours | nbr d'exemples : :..............................................: Nous allons donc écrire un select qui donne le nombre de cours par module ( C ) un select qui donne le nombre d'exemples par module ( E ) et réaliser une jointure entre le fichier des modules, C et E. |
|
La jointure module -> C est interne (on doit avoir au moins un cours) La jointure C -> E est externe, il peut ne pas y avoir d'exemples select m.codmodul, texte, nbcours, nbexemples from Fmodules m join (select codmodul, count(*) as nbcours from Fcours group by codmodul) as C on m.codmodul = c.codmodul left join (select codmodul, count(*) as nbexemples from Fexemples group by codmodul) as E on C.codmodul = e.codmodul |
|
Affichage des données Largeur des données . . : 79 Première ligne à afficher . . Première colonne à afficher . ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+ MODULE TEXTE NBCOURS NBEXEMPLE £CURSUSTCP Cursus TCP/IP et INTRANET 13 14 £CURSUS00 AF400 : présentation du produit 3 - £CURSUS01 Initiation à l'AS/400 & OS/400 19 5 £CURSUS02 Base de données integrée et requête 21 13 £CURSUS03 Développement en RPG (RPG,PRTF,DSPF 30 28 £CURSUS04 Programmation système (CL et principes) 20 15 £CURSUS05 Télécommunications sur AS/400 . 20 6 £CURSUS06 Programmation COBOL/400 28 25 £CURSUS220 OS/400 - V2R20 18 42 £CURSUS230 OS/400 - V2R30 32 66 £CURSUS3PR Préparation à la V3R10 (études/système) 18 15 £CURSUS3PX Préparation V3R10 (orienté exploitation) 9 10 £CURSUS310 OS/400 - V3R10 58 47 £CURSUS320 OS/400 - V3R20 19 20 £CURSUS370 OS/400 - V3R60 / V3R70 24 28 A suivre... |
|
il est possible de placer un SELECT (simple) dans la liste des colonnes d'un Select, depuis la version 5.10 uniquement. SI vous souhaitez obtenir la liste des commandes en rappelant sur chaque ligne le montant global commandé. -> SELECT codart, (qte * prix) as montant, (select sum(qte * prix) from commandes) as global FROM commandes vous pouvez utiliser dans le select imbriqué, n'importe quelle variable de n'importe quel fichier de la clause FROM du select principal, si vous l'utilisez aussi dans le WHERE ou le HAVING du select imbriqué. SI vous souhaitez obtenir la liste des commandes en rapppellant sur chaque ligne le montant global commandé pour la famille. -> SELECT codart , qte * prix as montant, (select sum(qte * prix) from commandes where famcod = c1.famcod) as totfam FROM commandes c1 |
|
Autre exemple : --------------- vous souhaitez obtenir la liste des commandes en indiquant sur chaque ligne le % représenté par ce montant, dans la famille : Select codart , qte * prix as montant, (select sum(qte * prix) from commandes c2 where c2.famcod = c1.famcod) / (c1.QTE * c1.prix) from commandes c1 quelques restrictions : ---------------------- - Le select doit produire un résultat d'UNE ligne, d'UNE colonne - Il faut attendre la V5R40 pour que GROUP BY et UNION soient valides et que cette colonne puisse être utilisée par ORDER BY. LATERAL fonctionne comme une jointure, mais en autorisant dans la requête l'utilisation de colonne(s), venant de niveau supérieur (P.pr_code) select P.pr_nom, V.compteur from PRODUCTEUR P , LATERAL (select count(*) as compteur from vins where pr_code = P.pr_code ) V |
|
V5R40 : les CTE admettent maintenant un "Full select" (UNION et ORDER BY) et si vous avez placé un ORDER BY dans la CTE, vous pouvez demander à ce que ce critère de tri soit respecté dans le requête finale par ORDER BY ORDER OF (le nom temporaire) par exemple : WITH temp AS ( select pr_code, count(*) nbr from vins group by pr_code order by nbr) select * from temp JOIN producteurs using (PR_CODE) where nbr > (select avg(nbr) from temp) ORDER BY ORDER OF temp, PR_NOM classe le résultat par nbr (comme temp) PUIS par nom. ........................................................... : Au passage la clause ORDER BY accepte maintenant : : les ALIAS de colonne (en plus des positions ordinales) : :.........................................................: |
|
Requêtes récursives -------------------- Soit le fichier suivant (vision "éclatée" d'un vélo) : CPST CPSE LIBELLE 01 cadre 00 vélo le cadre 02 fourche 01 cadre pour les roues 03 frein 02 fourche X 2 04 chaine 00 vélo à graisser ! 05 pédales 00 vélo X 2 06 patin 03 frein à surveiller 07 roue 00 vélo X 2 08 plateau 00 vélo peut-être plusieurs 09 selle 00 vélo obligatoire 10 pignon 00 vélo à graisser 11 tige 09 selle pour la selle 12 pneu 07 roue à gonfler 13 tube 01 cadre léger si possible 14 chambre 12 pneu voir rustines 15 valve 14 chambre pour gonfler |
|
Nous voulons afficher la ligne 'le cadre' et toutes les lignes dépendantes: Il faut, dans une clause WITH, afficher la ligne concernée, puis placer en dessous avec UNION ALL, le résultat de la jointure entre elle même et le fichier des liens. WITH temp (NIVEAU, CPST, CPSE, LIBELLE) as (select 1 , CPST, CPSE, libelle from TBliens where libelle = 'le cadre' UNION ALL select P.niveau+1 , F.CPST, P.CPST , f.libelle from temp P join TBliens F on P.CPST=F.CPSE ) SELECT * FROM TEMP Affiche : NIVEAU CPST CPSE LIBELLE 1 01 cadre 00 vélo le cadre 2 02 fourche 01 cadre pour les roues 2 13 tube 01 cadre léger si possible 3 03 frein 02 fourche X 2 4 06 patin 03 frein X 2 |
|
WITH temp (NIVEAU, CPST, CPSE, LIBELLE) as ( select 1 , CPST, CPSE, libelle from TBliens where CPSE = '00 vélo' UNION ALL select P.niveau+1 , F.CPST, P.CPST , f.libelle from temp P join TBliens F on P.CPST=F.CPSE ) SELECT * FROM TEMP , affiche l'arborescence complète : NIVEAU CPST CPSE LIBELLE 1 01 cadre 00 vélo le cadre 1 04 chaine 00 vélo à graisser ! 1 05 pédales 00 vélo X 2 1 07 roue 00 vélo X 2 1 08 plateau 00 vélo peut-être plusieurs 1 09 selle 00 vélo obligatoire 1 10 pignon 00 vélo à graisser 2 02 fourche 01 cadre pour les roues 2 13 tube 01 cadre léger si possible 2 12 pneu 07 roue à gonfler 2 11 tige 09 selle pour la selle 3 03 frein 02 fourche X 2 3 14 chambre 12 pneu voir rustines 4 06 patin 03 frein X 2 4 15 valve 14 chambre pour gonfler |
|
Vous remarquerez que SQL traite les liens dans l'ordre ou ils apparaissent, c'est a dire dans l'ordre des niveaux Si vous souhaitez faire apparaitre toute la profondeur d'une branche (tous les CPST de '01') avant de passer à la branche suivante, ajoutez : SEARCH DEPTH FIRST BY (un nom de colonne) SET nouvelle-colonne Indiquez 1/ un nom de colonne dont il faut analyser TOUTE la profondeur 2/ un nom de colonne virtuelle à créér, ce flag interne sert à SQL pour savoir s'il a déja traité une ligne et DOIT être le critère de tri (ORDER BY) sur la requête finale WITH temp (NIVEAU, CPST, CPSE, LIBELLE) as (select 1 , CPST, CPSE, libelle from TBliens where CPSE = '00 vélo' UNION ALL select P.niveau+1 , F.CPST, P.CPST , f.libelle from temp P join TBliens F on P.CPST=F.CPSE ) SEARCH DEPTH FIRST BY CPST SET tri Select * From Temp ORDER BY tri |
|
NIVEAU CPST CPSE LIBELLE 1 01 cadre 00 vélo le cadre 2 02 fourche 01 cadre pour les roues 3 03 frein 02 fourche X 2 4 06 patin 03 frein X 2 2 13 tube 01 cadre léger si possible 1 04 chaine 00 vélo à graisser ! 1 05 pédales 00 vélo X 2 1 07 roue 00 vélo X 2 2 12 pneu 07 roue à gonfler 3 14 chambre 12 pneu voir rustines 4 15 valve 14 chambre pour gonfler 1 08 plateau 00 vélo peut-être plusieurs 1 09 selle 00 vélo obligatoire 2 11 tige 09 selle pour la selle 1 10 pignon 00 vélo à graisser Enfin, la clause CYCLE , évite les boucles infinies (si les données le permettaient) |
|
Imaginons une erreur de saisie dans notre fichier
il a été ajoutée la ligne suivante
'06 patin' est composé de '00 vélo' (un comble !)
==> AIE, AIE, on boucle.
INSERT INTO TBLIENS values('00 vélo', '06 patin', 'on boucle')
--> sur une session 5250, il faut interrompre par appel système/2 !
pour éviter cela :
CYCLE (nom de colonne) SET vartemp = flag1 DEFAULT valeur0
quand SQL va se rendre compte qu'il boucle (une ligne déja vue)
il va attribuer à VARTEMP (nouvelle variable interne à la requête),
la valeur "flag1"(dans les autres cas, elle contient valeur0)
la ligne va quand même être affichée, mais la boucle se termine
|
|
Exemple avec : WITH temp (NIVEAU, CPST, CPSE, LIBELLE) as ( toujours le même select ... ) SEARCH DEPTH first by CPST SET tri CYCLE CPST SET flag to '1' DEFAULT '0' Select FLAG, niveau , CPST, CPSE, libelle FROM temp ORDER BY tri FLAG NIVEAU CPST CPSE LIBELLE 0 1 01 cadre 00 vélo le cadre 0 2 02 fourche 01 cadre pour les roues 0 3 03 frein 02 fourche X 2 0 4 06 patin 03 frein X 2 0 5 00 vélo 06 patin on boucle 1 6 01 cadre 00 vélo le cadre <== déja vu ! 0 6 04 chaine 00 vélo à graisser ! 0 6 05 pédales 00 vélo X 2 0 6 07 roue 00 vélo X 2 A vous donc de rajouter la CLAUSE CYCLE si vous craignez que les données puissent engendrer des boucles sans fin. |
|
ATTENTION, ces nouvelles fonctions V5R40 : + fonctions OLAP (rank(), row_number(), etc..) + requêtes recursives + ORDER OF dans ORDER BY + les sous sélections contenant des SELECTS complets (avec UNION ou ORDER BY) + ainsi que les opérateurs EXCEPT et INTERSECT (V5R30) ne sont implémentées que par SQE (le nouveau moteur, disponible en V5R20) les cas suivants impliquent une utilisation de CQE (l'ancien moteur) : -> l'utilisation de TRANSLATE, UPPER/UCASE, LOWER/LCASE -> un tri basé sur SRTSEQ(*LANGIDUNQ) ou STRSEQ(*LANGIDSHR) -> l'utilisation de fonctions TABLE (UDTF) -> l'utilisation de logiques dans la clause FROM -> l'utilisation de tables ayant des logiques avec select/omit (sauf à utiliser IGNORE_DERIVED_INDEX dans QAQQINI) SQE prend à sa charge en V5R40 l'utilisation de LIKE (pas en V5R30) |
|
par exemple, la requête suivante : ---------------------------------- WITH temp (NIVEAU, CPST, CPSE, LIBELLE) as (select 1 , CPST, CPSE, libelle from TBliens where lower(libelle) = 'le cadre' UNION ALL select P.niveau+1 , F.CPST, P.CPST , f.libelle from temp P join TBliens F on F.CPSE=P.CPST ) Select * From Temp génère le code erreur SQL0255 : Fonction incompatible avec la requête. prévoyant 9 codes raison. le notre est le 5 (à cause de la fonction lower) -- Code 5 -- Une expression récursive de table commune n'est pas compatible avec cette requête. |
|
l'ORDRE UPDATE UPDATE ..........table à modifier SET ............les modifs à faire WHERE ..........sélection (sans WHERE maj de toute la table) EXEMPLES DE MISES A JOUR VALIDES UPDATE AGETBL SET LIBAGE = 'Nantes' WHERE AGENCE = 02 UPDATE PERSONP1 SET SALBRU = (SALBRU * 1,02) WHERE COEF = 215 - la clause WHERE supporte les sous-sélections si celles ci portent sur une autre table |
|
- Les sous sélections sont admise dans la clause SET de UPDATE (V4R30) ......................................................................... : : : UPDATE command C set priha = (select pritarif from article : : WHERE codart = C.codart ) : : WHERE priha = 0 : :.......................................................................: mettre à jour le fichier command, mettre dans priha la valeur retournée par la clause select qui dit : (je vais chercher le pritarif dans le fichier article, de la ligne ayant le code article lu dans le fichier commande ) ......................................................................... :UPDATE recap R set SALAIRAN = (select sum(salair + primes) : : from personp1 : : where matricule = R.matricule) : :.......................................................................: |
|
mettre à jour le fichier recap, mettre dans salairAN la valeur retournée par la clause select qui dit (je vais chercher la somme du calcul (salair * primes) dans le fichier du personnel des lignes ayant le matricule lu dans le fichier RECAP ) l'assignation de valeurs peut se faire de deux manières: UPDATE fichier set (col1, col2) = (select zone1, zone2 from ... where ...) ou UPDATE fichier set col1 = (select zone1 from fichier2 where ...) , set col2 = (select zone2 from fichier3 where ...) |
|
l'ORDRE DELETE DELETE FROM .....table à modifier WHERE ..........sélection (sans WHERE mise a blanc de la table) EXEMPLES DE SUPRESSIONS VALIDES DELETE FROM STGTBL WHERE AGENCE = 04 DELETE FROM PERSONP1 WHERE MATRIC = 4112 DELETE FROM TABLE (sans where) = suppression de tous les enregistrements |
|
la clause WHERE supporte les sous-sélections si celles ci portent sur une autre table : SUPPRIMER les clients sans commande : Delete from client C where not exists ( select * from commande where nocli = C.nocli ) supprimer les commandes quand la clause SELECT ne trouve pas de ligne (NOT EXISTS) ayant le n° de client lu dans le fichier client (C.nocli) |
|
l'ORDRE INSERT INSERT INTO .....table à modifier (<nom-variable>, <nom-variable>, etc...) si non precisé = toutes les variables VALUES .........valeurs assignées aux variables EXEMPLES D'INSERTIONS VALIDES INSERT INTO STGTBL VALUES(14, 'BREGAY', 'Frederique', 01) OU INSERT INTO STGTBL (NUMSTG, NOM, AGENCE) VALUES(14, 'BREGAY', 01) INSERT INTO STGTEMP SELECT * FROM STGTBL WHERE AGENCE = 03 (le résulat de l'ordre SELECT est place dans la table STGTEMP) |
|
FONCTIONS DE VALIDATION INVALIDATION -------------------------------------- 1/ on indique le niveau de validation avec lequel on travail - sur la commande STRSQL (ou sur l'écran de service par F13) - sur l'ordre SELECT par SELECT (..) -------- FROM (..)-----------+--------------+------------> |-WHERE(..)----| >--WITH--+-NC--------------------+-----------------------------------> | | +-UR--------------------+ +-CS--+---------------+-+ | | | | | +-KEEP LOCKS----+ | | | +-RS--------------------+ +-RR--------------------+ |
|
NC : pas de contrôle de validation (==> validation implicite) toutes les autres valeurs indiquent des actions base de données en attente de validation (en suspend) et indiquent les différents niveaux de disponiblité de la base de données : UR : Uncomitted read seuls les enregistrements modifiés sont verrouillés CS : Cursor stability les enregistrements modifiés plus UN enregistrement par table en lecture RS : read stability les enregistrements modifiés plus TOUS les enregistrements des tables en lecture RR : repeatable read toutes les TABLES sont verrouillées |
|
NONE et NO COMMIT peuvent être utilisés à la place de NC CHG et READ UNCOMMITED, READ WRITE à la place de UR READ COMMITED à la place de CS ALL à la place de RS SERIALIZABLE à la place de RR les mots SERIALIZABLE, etc ... sont les termes ISO. (les ordres UPDATE, DELETE et INSERT possèdent la même clause) |
|
2/ on valide (ou pas) par les ordres COMMIT Validation ROLLBACK Invalidation ATTENTION, l'ordre SQL COMMIT possède un paramètre important HOLD : - Les ressources sont suspendues. Les curseurs actifs ne sont pas désactivés , les instructions SQL préparées sont conservées et toutes les ressources acquises pendant la transaction sont suspendues. (sans) - Les ressources ne seront pas suspendues. Les curseurs actifs sont désactivés, les instructions SQL préparées sont supprimées et les ressurces suspendues libérées. |
|
Un curseur peut etre déclaré WITH HOLD ou WITHOUT HOLD s'il est déclaré WITH HOLD, il ne sera pas désactivé, meme en cas de COMMIT simple. ce sont les ordres Commit et Rollback qui définissent la frontière ils représentent la fin d'une transaction ET le début de la suivante On peut changer le niveau de contrôle en cours de transaction par SET TRANSACTION SET TRANSACTION ISOLATION LEVEL UR ATTENTION: lors du COMMIT (ou ROLLBACK) on retrouve la valeur par défaut (celle définie par l'écran de service via F13) |
|
La version 6 avait apporté à l'ordre SELECT l'option SKIP LOCKED DATA cela permettait lors d'une lecture avec verrouillage d'enregistrement, c.a.d COMMIT à CS(*CS) ou plus, d'ignorer les lignes verrouillées Cette option a été étendue aux instructions UPDATE et DELETE en V7 : vous avez, en plus, le choix entre trois comportements : WAIT FOR OUTCOME Attendre que les lignes soient libérées (COMMIT par exemple) cela n'a pas d'effet sur les niveaux de COMMIT inférieurs à CS USE CURRENTLY COMMITTED Utiliser les valeurs déjà validées cela ne peut s'appliquer qu'aux COMMIT niveau CS (sans KEEP LOCKS) SKIP LOCKED DATA les lignes verrouillées sont ignorées. Comme cette clause est maintenant valide avec UPDATE/DELETE, elle peut être utilisée avec tous les niveaux de COMMIT, sauf RR (repeatable read) |
|
E. SQL/400 interactif (commande STRSQL) ---------------------------------------- STRSQL COMMIT(*NONE) (contrôle de validation) *NONE pas de contrôle de validation ^ *CHG contrôle de validation actif | seuls les enreg modifiés, ajoutés journalisation | supprimés sont verrouillés obligatoire | *CS idem *CHG plus verrouillage du dernier enreg | sélectionné (pour les tables en consultation) | *ALL contrôle de validation actif | tous les enreg modifies, ajoutes | supprimes ET SELECTIONNES V sont verrouillés NAMING(*SYS) (convention d'appellation) *SYS convention d'appellation système (LIB/OBJET) un objet non qualifié est recherché dans *LIBL |
|
*SQL convention d'appellation SQL (LIB.OBJET) un objet non qualifie est recherché dans une bibliothèque ayant le nom du USER (sur 8 c) PROCESS(*RUN) (type d'exécution) *RUN les instructions sont exécutees *VLD seules la syntaxe et la validité des instructions sont verifiées *SYN seule la syntaxe est verifiée LIBOPT(*LIBL) (liste de bibliothèque) Liste de bibliothèque utilisee par F16-F17-F18 *LIBL liste de bibliothèques du travail *USRLIBL partie utilisateur de *LIBL *ALLUSR toutes les bibliothèques user *ALL toutes les bibliothèques *CURLIB la bibliothèque en cours -NOMBIB- une bibliothèque donnée |
|
LISTTYPE(*ALL) (type de liste) type d'objets affiches par F16-F17-F18 *ALL tous les objets *SQL seuls les objets SQL (collection,table,vue,index) REFRESH(*ALWAYS) (mise à jour de l'écran) Quand réactualiser les données provenant d'un SELECT *ALLWAYS à chaque fois que l'utilisateur demande un défilement. *FORWARD uniquement à la première visualisation d'un écran (défilement avant) DATFMT( ) DATSEP( ) définissent la présentation des colonnes TIMFMT( ) de type DATE/HEURE. TIMSEP( ) |
|
Touches de commandes valides sur l'ecran SQL F3 = EXIT F4 = INVITE F6 = INSERER UNE LIGNE une ligne blanche est insérée en-dessous de la ligne ou se trouve le curseur F9 = RAPPEL d'une instruction SQL (ou sous-selection sur INVITE d'un select) F10 = COPIER UNE LIGNE la ligne où se trouve le curseur est copiée en-dessous F13 = SERVICE affiche l'écran de service permettant de modifier les paramètres de la commande STRSQL et de choisir le type de sortie. (ECRAN,FICHIER D'IMPRESSION,FICHIER BD) |
|
F14 = SUPPRIMER UNE LIGNE supprime la ligne où se trouve le curseur F15 = SCINDER UNE LIGNE place tout ce qui se trouve à droite du curseur sur une ligne en-dessous F16 = LISTE DE BIBLIOTHEQUES F17 = LISTE DE FICHIERS D'UNE BIBLIOTHEQUE F18 = LISTE DES ZONES D'UN FICHIER F24 = AUTRES TOUCHES F6, F10, F14 et F15 sont valides aussi sur l'écran d'invite. |
|
F. CATALOGUE SQL (dans QSYS2) SYSTABLES ! une ligne par table/PF SYSCOLUMNS ! une ligne par colonne de fichier SYSCST ! contraintes SYSKEYCST ! clés de contraintes (UNIQUE et Primary KEY) SYSREFCST ! contraintes de type RI SYSCSTCOL ! colonnes de contraintes RI SYSCSTDEP ! dépendance parent/enfant (RI) SYSCHKCST ! contraintes de type CHECK SYSINDEXES ! une ligne par index SYSKEYS ! une ligne par clé d'index SYSVIEWS ! une ligne par vue SYSVIEWDEP ! dépendance table/PF -> vue SYSLANGS ! compatibilité avec norme ISO (niveau supporté) SYSPACKAGE ! package SQL (DRDA) |
|
SYSPROCS ! une ligne par procédure cataloguée. SYSPARMS ! une ligne par paramètre de procédure SYSFUNCS ! une ligne par fonction (V4R40) SYSROUTINE ! contient l'ensemble des routines SQL (proc + fonctions) SYSTYPES ! une ligne par type créé par l'utilisateur SYSTRIGGER ! une ligne par Trigger SYSSEQ ! une ligne par SEQUENCE (V5R30) ! -------------------------------------------------------------------------- |
|
G. SQL/400 et programmation en langage HLL(RPG, COBOL ,etc) ----------------------------------------------------------- ORDRES SQL INCLUS DANS LE SOURCE DU LANGAGE RPG/400 et ILE/RPGIV -------------------- C/EXEC SQL C+ ORDRE SQL EN C+ FORMAT LIBRE C/END-EXEC COBOL: ------ EXEC SQL ORDRE SQL EN FORMAT LIBRE END-EXEC. |
|
Des paramètres peuvent aussi être indiqués dans le code par SET OPTION (ce doit être la première instruction SQL) SET OPTION----+-ALWCPYDTA = copie des données admise ? *NO une copie des données n'est pas admise *YES une copie est admise quand cela est nécessaire *OPTIMIZE, à chaque fois que cela permet d'aller plus vite +-COMMIT = niveau de COMMIT *NONE hors contrôle de validation *CHG verrouillage des lignes modifiées *CS idem *CHG + une ligne lue (test de disponibilité) *ALL verrouillage de toutes les ligneslues et modifiées +-CONACC = concurrence d'accès *DFT mode de fonctionnement par défaut (cf QAQQINI) *WAIT Attendre (60s) que l'enregistrement se libère *CURCMT travailler avec la dernière version validée |
|
+-DATFMT = *ISO | *EUR | *DMY ... +-DATSEP = (un séparateur) +-TIMFMT = *ISO | *EUR | *HMC ...-----+ +-TIMSEP = (un séparateur) +-DBGVIEW = *STMT | *SOURCE +-USRPRF = *USER | *OWNER | *NAMING profil de référence pour les droits *USER, l'utilisateur ayant lancé le pgm *OWNER le propriétaire du pgm *NAMING si convention *SQL = *OWNER, sinon *USER +-DYNUSRPRF = *USER | *OWNER même notion pour les instructions dynamiques +-NAMING = *SYS | *SQL convention d'appellation |
|
+-RDBCNNMTH = nom RDB (enregistré par WRKRDBDIRE) connexion automatique à une base éloignée +-SRTSEQ séquence de classement *HEX l'EBCDIC fait référence *LANGIDSHR tenir compte de la langue, mais é <> ê *LANGIDUNQ tenir compte de la langue et e=é=è=ê +-LANGID code langage (FR par exemple) +-TGTRLS = version cible |
|
VARIABLES HOST -------------- UNE VARIABLE "HOST" est une variable du programme . SQL les reconnait car elles commencent par ":" ex :SALBRU, :NUMSTG, etc... une augmentation du salaire en fonction d'un coefficient, s'écrira : UPDATE personp1 set SALBRU = SALBRU * :augmentation where COEF = :coef SQL Communication area SQLCA ----------------------------- EN RPG c'est une data structure générée automatiquement par le précompilateur en RPG4, la meme zone est définie 2 fois : - une fois en nom sur 6 lettres (pour compatibilité RPG III) - une fois en nom long pour etre à la norme |
|
D* SQL Communications area en V5 D SQLCA DS D SQLCAID 8A INZ(X'0000000000000000') D SQLAID 8A OVERLAY(SQLCAID) D SQLCABC 10I 0 D SQLABC 9B 0 OVERLAY(SQLCABC) D SQLCODE 10I 0 D SQLCOD 9B 0 OVERLAY(SQLCODE) D SQLERRML 5I 0 D SQLERL 4B 0 OVERLAY(SQLERRML) D SQLERRMC 70A D SQLERM 70A OVERLAY(SQLERRMC) D SQLERRP 8A D SQLERP 8A OVERLAY(SQLERRP) D SQLERR 24A D SQLER1 9B 0 OVERLAY(SQLERR:*NEXT) D SQLER2 9B 0 OVERLAY(SQLERR:*NEXT) D SQLER3 9B 0 OVERLAY(SQLERR:*NEXT) D SQLER4 9B 0 OVERLAY(SQLERR:*NEXT) D SQLER5 9B 0 OVERLAY(SQLERR:*NEXT) D SQLER6 9B 0 OVERLAY(SQLERR:*NEXT) |
|
D SQLERRD 10I 0 DIM(6) OVERLAY(SQLERR) D SQLWRN 11A D SQLWN0 1A OVERLAY(SQLWRN:*NEXT) D SQLWN1 1A OVERLAY(SQLWRN:*NEXT) D SQLWN2 1A OVERLAY(SQLWRN:*NEXT) D SQLWN3 1A OVERLAY(SQLWRN:*NEXT) D SQLWN4 1A OVERLAY(SQLWRN:*NEXT) D SQLWN5 1A OVERLAY(SQLWRN:*NEXT) D SQLWN6 1A OVERLAY(SQLWRN:*NEXT) D SQLWN7 1A OVERLAY(SQLWRN:*NEXT) D SQLWN8 1A OVERLAY(SQLWRN:*NEXT) D SQLWN9 1A OVERLAY(SQLWRN:*NEXT) D SQLWNA 1A OVERLAY(SQLWRN:*NEXT) D SQLWARN 1A DIM(11) OVERLAY(SQLWRN) D SQLSTATE 5A D SQLSTT 5A OVERLAY(SQLSTATE) D* End of SQLCA En COBOl il faut déclarer par un INCLUDE : EXEC SQL INCLUDE SQLCA END-EXEC. |
|
01 SQLCA. 05 SQLCAID PIC X(8). 05 SQLCABC PIC S9(9) COMP-4. 05 SQLCODE PIC S9(9) COMP-4. 05 SQLERRM. 49 SQLERRML PIC S9(4) COMP-4. 49 SQLERRMC PIC X(70). 05 SQLERRP PIC X(8). 05 SQLERRD OCCURS 6 TIMES PIC S9(9) COMP-4. 05 SQLWARN. 10 SQLWARN0 PIC X. 10 SQLWARN1 PIC X. 10 SQLWARN2 PIC X. 10 SQLWARN3 PIC X. 10 SQLWARN4 PIC X. 10 SQLWARN5 PIC X. 10 SQLWARN6 PIC X. 10 SQLWARN7 PIC X. 05 SQLEXT PIC X(8). |
|
variables de SQLCA importantes: ------------------------------- +SQLCODE ou SQLCOD 0 = pas d'erreur >0 = ordre exécuté mais avec erreur (ex +100 = fin de fichier) <0 = erreur, ordre non exécuté +SQLERRMC Texte du message SQL d'erreur si SQLCODE<0 +SQLERRML longueur significative de SQLERRMC +SQLERRD ou SQLERR (ERREUR) 6 fois 4 octets binaires ==> en RPG4, COBOL et PLI une table ==> en RPG, aussi 6 zones de SQLER1 à SQLER6 |
|
SQLERRD(1) ou SQLER1 contient le N° message CPF si SQLCODE < à 0. SQLERRD(2) ou SQLER2 contient le N° message CPD si SQLCODE < à 0. SQLERRD(3) ou SQLER3 donne le nb de lignes affectées par un ordre UPDATE,DELETE, ou INSERT +SQLWARN ou SQLWRN (WARNING) 8 indications alpha contenant ' ' ou 'W' SQLWARN0 ou SQLWN0 contient 'W' si une des indications contient 'W' SQLWARN1 ou SQLWN1 contient 'W' si une colonne a été tronquée SQLWARN3 ou SQLWN3 contient 'W' si le nb de variables HOST est invalide SQLWARN4 ou SQLWN4 contient 'W' si un ordre PREPARE pour UPDATE ou DELETE ne contient pas la clause WHERE |
|
+SQLSTATE ou SQLSTT SQLSTATE est ammené à remplacer SQLCOD/SQLCODE (orientation ISO) SQLSTATE est composé de CINQ caractères (chiffres ou lettres) les deux premiers caractères représentent la classe 00 Sucess (terminé noprmalement) 01 Warning (SQLCOD positif) 02 No data (particulièrement 02000, équivalent à SQLCOD 100) 03 à ZZ Error (dans les faits 07 à 58) représentent une erreur grave (SQLCOD < 0) un etat SQLSTATE peut correspondre à plusieurs SQLCODE |
|
ORDRES SQL de gestion d'erreur ------------------------------ I-NOT FOUND---I I-CONTINUE-I WHENEVER ----I-SQLERROR----I----I-GOTO-----I I-SQLWARNING--I I-GO TO----I NOT FOUND => SQLCOD = +100 SQLWARNING => SQLCOD >0 et <> 100 SQLERROR => SQLCOD <0 (= MONMSG) ordre CONTINUE (instruction suivante) ou GOTO (GO TO) nom-de-label Label du programme en format du langage HLL |
|
Exemples: EXEC SQL WHENEVER SQLERROR GO TO GESTERREUR END-EXEC EXEC SQL WHENEVER SQLWARNING CONTINUE END-EXEc EXEC SQL WHENEVER NOT FOUND GOTO FINFICHIER END-EXEC Vous pouvez aussi utiliser SQLCOD (SQLCODE) avec les ordres IF , DOW/DOU , PERFORM -> IF SQLCOD = 0 -> DOU (ou PERFORM UNTIL) SQLCOD > 0 , etc ... |
|
SQL STATIQUE ---------------- INSERT C/EXEC SQL INSERT INTO SQLDB/STGTBL C+ (NUMSTG, NOM, PRENOM, AGENCE) C+ VALUES(:NOSTAG, :NMSTG, :PRESTG, :AGENCE) C/END-EXEC UPDATE C/EXEC SQL UPDATE SQLDB/STGTBL C+ SET NOM = :NMSTG, PRENOM = :PRESTG C+ WHERE NUMSTG = :NOSTAG C/END-EXEC Ou C/EXEC SQL UPDATE SQLDB/STGTBL C+ SET ROW = :DataStructure C+ WHERE NUMSTG = :NOSTAG C/END-EXEC |
|
DELETE C/EXEC SQL DELETE FROM SQLDB/STGTBL C+ WHERE NUMSTG = :NOSTAG C/END-EXEC SELECT (cette syntaxe n'est valide que si l'ordre ne permet de retrouver qu'UNE LIGNE à la fois) C/EXEC SQL SELECT NOM, PRENOM, AGENCE C+ INTO :NMSTG, :PRESTG, :AGENCE C+ FROM SQLDB/STGTBL C+ WHERE NUMSTG = :NOSTAG C/END-EXEC OU |
|
IENREG DS (en GAP3) I 1 15 NMSTG I 16 30 PRESTG I 31 320AGENCE C/EXEC SQL SELECT NOM, PRENOM, AGENCE C+ INTO :ENREG C+ FROM SQLDB/STGTBL C+ WHERE NUMSTG = :NOSTAG C/END-EXEC OU IENREG DS I P 1 30NOSTAG I 4 18 NMSTG I 19 33 PRESTG I 34 350AGENCE (ou: DS externe utilisant la description du fichier) IENREG E DSSTGTBL C/EXEC SQL SELECT * C+ INTO :ENREG C+ FROM SQLDB/STGTBL C+ WHERE NUMSTG = :NOSTAG C/END-EXEC |
|
D ENREG DS (en GAP4) D NMSTG 15 D PRESTG 15 D AGENCE 2 0 C/EXEC SQL SELECT NOM, PRENOM, AGENCE C+ INTO :ENREG C+ FROM SQLDB/STGTBL C+ WHERE NUMSTG = :NOSTAG C/END-EXEC OU D ENREG DS D nostag 5P 0 D NMSTG 15 D PRESTG 15 D AGENCE 2 0 (ou: DS externe utilisant la description du fichier) D ENREG E DS EXTNAME(STGTBL) C/EXEC SQL SELECT * C+ INTO :ENREG C+ FROM SQLDB/STGTBL C+ WHERE NUMSTG = :NOSTAG C/END-EXEC |
|
ATTENTION, cette syntaxe suppose que les variables lues n'acceptent pas la valeur nulle. (en cas de valeur nulle vous recevrez un message SQL et SQLCOD <>0) Pour gérer le valeur nulle, écrivez : C/EXEC SQL SELECT NOM, PRENOM, AGENCE C+ INTO :NMSTG:NMSTGi, :PRESTG:PRESTGi, :AGENCE:AGENCEi C+ FROM SQLDB/STGTBL C+ WHERE NUMSTG = :NOSTAG C/END-EXEC NMSTGi, PRESTGi et AGENCEi étant des variables binaires sur 2 octets (en rpg "4B 0" ou "5I 0") si la variable binaire (SQL parle d'indicateur HOST) contient : 0 : la variable associée à un contenu significatif -1 : la variable contient la valeur nulle -2 : SQL a assigné la valeur nulle suite à une erreur de mappage. |
|
EXTRACTION DE DONNEES MULTIPLES -------------------------------- UTILISATION D'UN CURSEUR Un curseur est un fichier intermédiaire géneré par SQL, rempli par le résultat d'un ordre SELECT et destiné à être lu séquentiellement par l'ordre FETCH. 1/ déclaration du curseur ---------------------- C/EXEC SQL C+ DECLARE [SENSITIVE | INSENSITIVE] nomcur CURSOR FOR C+ SELECT * FROM SQLDB/STGTBL WHERE AGENCE = :AGENCE C+ [WITH HOLD | WITHOUT HOLD] C+ [FOR UPDATE OF NMSTG, PRESTG] C/END-EXEC |
|
Un curseur peut etre déclaré WITH HOLD ou WITHOUT HOLD s'il est déclaré WITH HOLD, il ne sera pas désactivé, meme en cas de COMMIT simple (sans paramètre). En effet, l'ordre SQL COMMIT possède un paramètre important HOLD : - Les ressources sont suspendues. Les curseurs actifs ne sont pas désactivés , les instructions SQL préparées sont conservées et toutes les ressources acquises pendant la transaction sont suspendues. (sans) - Les ressources ne seront pas suspendues. Les curseurs actifs sont désactivés sauf WITH HOLD , les instructions SQL préparées sont supprimées et les ressources suspendues libérées. |
|
Un curseur peut etre déclaré "sensitif" ou non SENSITIVE CURSOR le curseur est construit dynamiquement, il reflète les modifications effectuées sur les données non encore lues de la base, par le pgm en cours ou éventuellement par d'autre,suivant le niveau d'isolation INSENSITIVE CURSOR le curseur est construit une fois pour toutes avec des données copiées. le pgm est insensible aux modification effectuées sur la base (y compris par lui). le SELECT ne doit pas contenir FOR UPDATE et ALWCPYDTA <> de *NO ASENSITIVE CURSOR le curseur sera sensitif ou pas suivant la requête et le degré d'optimisation (paramètre ALWCPYDTA) |
|
2/ ouverture du curseur -------------------- C/EXEC SQL C+ OPEN nomcur C/END-EXEC (SELECT exécuté et résultat placé dans le curseur) 3/ lecture séquentielle du curseur ------------------------------- A/ avec WHENEVER et GOTO (hélas !) B/ en utilisant SQLCOD (programmation structurée) C EXSR Lecture C DOW SQLCOD <> 0 .... EXSR lecture ENDDO |
|
C LECTURE BEGSR C/EXEC SQL C+ FETCH nomcur INTO :ENREG C/END-EXEC C ENDSR 4/ mise à jour et suppression (éventuellement) --------------------------- C/EXEC SQL C+ UPDATE SQLDB/STGTBL C+ SET PRENOM = :PRESTG C+ WHERE CURRENT OF nomcur C/END-EXEC C/EXEC SQL C+ DELETE FROM SQLDB/STGTBL C+ WHERE CURRENT OF nomcur C/END-EXEC ATTENTION , il s'agit ici de manipuler le DERNIER enregistrement Lu |
|
5/ fermeture du curseur (dans tous les cas) -------------------- C/EXEC SQL C+ CLOSE nomcur C/END-EXEC EXEMPLE A partir d'un fichier produit affichage par sous-fichier du nombre de produits et de la quantité moyenne stockée par sous-famille. Le premier écran demande la famille de produit. Le deuxième écran affiche le sous-fichier. |
|
FICHIER ECRAN
---------------
A REF(PRODUIP1)
INDARA
A R TITRE
A 1 2'NOM-PGM'
A 1 18' Titre'
A DSPATR(UL)
A 1 68DATE
A EDTCDE(Y)
A R F1
A CA03(03 'exit')
A OVERLAY ERASE(SFL CLT)
A 4 4'FAMILLE DU PRODUIT:'
A FAMPRO R Y I 4 25
A 40 ERRMSG('Famille en erreur' 40)
A R SFL SFL
A SFAPRO R O 7 6
A NBPRO 6 0O 7 13
A MOYEN 9 2O 7 24
|
|
A EDTCDE(3) A 7 2'!' A DSPATR(HI) A R CTL SFLCTL(SFL) A OVERLAY A PROTECT A 30 SFLDSP A 30 SFLDSPCTL A N30 SFLCLR A 30 SFLEND A SFLSIZ(0029) A SFLPAG(0028) A SFLLIN(0008) A 6 2'Entète sous-fichier' A DSPATR(UL) |
|
PROGRAMME RPG ------------- Frpgsqle cf e workstn indds(indic) F sfile(sfl : rang) dIndic DS D f3 n overlay(indic : 3) D sfldsp n overlay(indic : 30) D erreur_famille n overlay(indic : 40) DRang s 4 0 C/EXEC SQL C+ DECLARE C1 CURSOR FOR C+ SELECT SFAPRO, COUNT(*), AVG(QTSPRO) C+ FROM PRODUIP1 WHERE FAMPRO = :FAMPRO C+ GROUP BY SFAPRO C/END-EXEC * * Corps du programme (boucle sur image 1) * C write TITRE |
|
C exfmt F1 C DOw not f3 C exsr Principal C exfmt F1 C ENDDO C eval *inlr = *on * * * Sous pgm traitement d'un produit * C Principal BEGSR * Ouverture curseur (le SELECT est exécuté) C/EXEC SQL C+ OPEN C1 C/END-EXEC C exsr LECTURE C IF SQLCOD <> 0 C eval erreur_famille = *on C ELSE C eval sqldsp = *off C WRITE CTL C eval sqldsp = *on |
|
C eval rang = 0 C DOu sqlcod <> 0 C eval rang = rang + 1 C write SFL C exsr LECTURE C ENDDO C exfmt CTL C ENDIF * * fermeture du curseur * C/EXEC SQL C+ CLOSE C1 C/END-EXEC C ENDSR * * LECTURE SEQUENTIELLE DU CURSEUR * C LECTURE BEGSR C/EXEC SQL C+ FETCH C1 INTO :SFAPRO, :NBPRO, :MOYEN C/END-EXEC |
|
SQL/400 permet de manipuler plusieurs enregistrements à la fois par l'intermèdiaire de tableaux de structure: (V2R20). - en RPG : DATA STRUCTURE à occurences multiples - en COBOL : Table (OCCURS) Utilisation: ° INSERT--INTO --nom--------------------VALUES :var-host ! ! ! ! !--X------ROWS---! !-var--! ° FETCH--nom-curseur----------------------------INTO :var-host ! ! ! ! !-FOR---X------ROWS---! !-var--! |
|
SQLCA: SQLERRD(3) = nb d'enregistrements (lus ou insérés) SQLERRD(4) = lg enreg SQLERRD(5) = indicateur signalant si le dernier poste de la structure a été rempli par FETCH. En parallèle: Il est possible d'indiquer un facteur d'optimisation) (= OPTIMIZE sur OPNQRYF) SELECT ------------idem-----------------------------------------> ! ! !--OPTIMIZE-FOR x ROWS---------! (Pour un traitement par sous-fichier , indiquer SFLPAG.) |
|
CURSEURS FLOTTANTS : SQL donne la possibilité de se repositionner dans un curseur. DECLARE-------idem---------------------------------CURSOR--------> ! ! !----------------SCROLL-----! !-DYNAMIQUE-! SCROLL seul = en lecture pure DYNAMIQUE SCROLL = en mise à jour. (FOR UPDATE). ce qui permet d'utiliser FETCH de la manière suivante: FETCH--------------------------------curseur-------> !--NEXT--------------------! !--PRIOR-------------------! !--FIRST-------------------! !--LAST--------------------! !--BEFORE------------------! !--AFTER-------------------! !--CURRENT-----------------! !--RELATIVE----entier------! |
|
MANIPULATION DES BLOB --------------------------- Vous pouvez maintenant manipuler vos variables HOST par un ordre SQL SET :variable = expression ou bien VALUE expression INTO :variable par exemple C/EXEC SQL C+ SET :Result = LEFT(:NOM, 10) CONCAT :PRENOM C/END-EXEC C/EXEC SQL C+ VALUE POSSTR(:RAISOC , 'Assurance') INTO :debut |
|
> Une colonne de type LOB peut-être manipulée par -son contenu (si votre langage supporte des variables aussi grandes) vous devez déclarer en RPG par: DMYBLOB S SQLTYPE(BLOB:500) ce qui génère : D MYBLOB DS D MYBLOB_LEN 10U 0 D MYBLOB_DATA 500A -vous pouvez utiliser un identifiant appelé "LOCATOR", qui permet : + de ne pas transférer les data dans le programme (donc sur le PC) |
|
vous devez déclarer en RPG par: D MYCLOB S SQLTYPE(CLOB_LOCATOR) ce qui génère : D MYCLOB S 10U 0 vous pouvez utiliser l'identifiant en lieu et place de la colonne par les nouvelles instructions SET et VALUE. C/EXEC SQL C+ VALUE POSSTR(:MYCLOB, 'formation') INTO :debut C/END-EXEC -vous pouvez utiliser un fichier appelé "FILE LOCATOR", qui permet : + de copier le contenu d'un LOB dans un fichier IFS + de renseigner un LOB à partir |
|
vous devez déclarer en RPG par: D MYFILE S SQLTYPE(CLOB_FILE) ce qui génère : D MYFILE DS D MYFILE_NL 10U 0 [lg du nom] D MYFILE_DL 10U 0 [lg des Data] D MYFILE_FO 10U 0 [file permission] * SQL génère SQFRD (2) ouverture en lecture * SQFCRT (8) création (erreur si existe déja) * SQFOVR(16) création si inexistant (ou écrasement) * SQFAPP(32) ouverture en ajout D MYFILE_NAME 255A [nom du fichier] Exemple en COBOL |
|
en Working Storage Section : 01 rapport USAGE IS SQL TYPE IS CLOB-FILE * SQL-FILE-READ * SQL-FILE-CREATE * SQL-FILE-OVERWRITE * SQL-FILE-APPEND puis en traitement move "RAPPORT.TXT" to rapport-NAME. move 11 to rapport-LENGTH. move SQL-FILE-OVERWRITE to rapport-FILE-OPTIONS. EXEC SQL SELECT rapport INTO :rapport FROM ... WHERE ... END-EXEC. ce traitement place copie du contenu de la colonne "rapport" dans le fichier "RAPPORT.TXT" qui est un fichier IFS. |
|
un ordre INSERT aurait renseigné la colonne par copie du fichier. Exemple en RPG-IV * un champs de type BLOB_FILE contient non pas une image * mais le chemin d'une image * a/ à lire lors d'un insert ou d'un update * a/ à écrire lors d'un select * * d image s SQLTYPE(BLOB_FILE) d articles e ds C/exec sql C+ DECLARE C1 CURSOR FOR SELECT noart FROM articles FOR UPDATE C+ OF image C/end-exec C/exec sql C+ open c1 C/end-exec c dou sqlcod <> 0 C/exec sql C+ fetch c1 into :noart C/end-exec |
|
c if sqlcod <> 0 c leave c endif c eval image_fo = SQFRD * les images sont dans /html/tools/GIF/(noart).gif c eval image_name = '/html/TOOLS/GIF/' + c %trimr(noart) + '.gif' c eval image_nl = %len(%trim(image_name)) * l'image est physiquement copié dans le champ de cet enregistrement C/exec sql C+ update articles set image = :image C+ where current of c1 C/end-exec c enddo C/exec sql C+ close c1 C/end-exec C/exec sql C+ commit C/end-exec |
|
SQL DYNAMIQUE ------------------- Les ordres SQL (ou certains) ne sont plus compilés par le pré-compilateur mais interprètés et exécutés au moment de l'exécution du programme. EXECUTE IMMEDIATE exécution dynamique d'un ordre SQL Exemple en cobol: *------------------------ WORKING-STORAGE SECTION. *------------------------ 01 VAR pic X(150). EXEC SQL INCLUDE SQLCA END-EXEC. LINKAGE SECTION. 01 TABL PIC X(10). |
|
*------------------------------------------- PROCEDURE DIVISION USING VARMODIF VARSELECT. *-------------------------------------------- PGM. EXEC SQL WHENEVER SQLERROR GO TO PROBLEM END-EXEC. *-VARMODIF contient la modification à réaliser *-VARSELECT contient la sélection des enregistrements à modifier STRING "UPDATE SQLDB/STGTBL SET" VARMODIF "WHERE " VARSELECT DELIMITED BY SIZE INTO VAR. EXEC SQL EXECUTE IMMEDIATE :VAR END-EXEC. GO TO FIN. PROBLEM. DISPLAY "ERREUR PENDANT LA MISE A JOUR". FIN. |
|
PREPARE et EXECUTE Si un ordre SQL dynamique doit être exécuté plusieurs fois il est plus performant de demander à SQL de l'interpréter une fois par PREPARE et de demander son exécution x fois par EXECUTE. Il faut donner un nom au résultat du PREPARE et utiliser ce nom dans l'ordre EXECUTE. Il n'est pas possible d'utiliser des variables "HOST" dans l'ordre PREPARE.Celles ci seront remplacées par "?" et on utilisera les variables dans l' EXECUTE Exemple en RPG-IV: |
|
D requete s 512 D famille s 2 0 D fichier s 10 * * mise en place de l'ordre SQL dans requete * C eval requete = 'DELETE FROM SQLDB/' + C fichier + ' WHERE FAMPRO = ?' * * préparation de l'ordre SQL sous le nom P1 * C/EXEC SQL C+ PREPARE P1 FROM :requete C/END-EXEC * C for famille = 10 to 25 C/EXEC SQL C+ EXECUTE P1 USING :FAMILLE C/END-EXEC |
|
PREPARE et EXECUTE avec un ordre SELECT. Ceci n'est possible que si les variables extraites par l'ordre SELECT sont TOUJOURS les mêmes. (Même nombre de variables, même définition) Exemple RPGIV C *entry plist C parm fichier C eval var = 'SELECT ZON1, ZON2 FROM SQLDB/' + C fichier + 'WHERE ZON1 > ? and ZON2 = ?' * * préparation de l'ordre SQL sous le nom P1 * C/EXEC SQL C+ PREPARE P1 FROM :VAR C/END-EXEC |
|
* * déclaration d'un curseur résultant de P1 * C/EXEC SQL C+ DECLARE C1 CURSOR FOR P1 C/END-EXEC * * ouverture du curseur (zone remplacera le "?") * C/EXEC SQL C+ OPEN C1 USING :ZONA, :ZONB C/END-EXEC * * traitement du curseur comme précédement * par l'ordre FETCH puis fermeture par CLOSE C EXSR lecture C if sqlcod <> 0 C eval *in40 = *on C else * préparation sous fichier (clear) C eval *in30 = *off C write CTL |
|
C eval *in30 = *on C eval rang = 0 * boucle sur toutes les lignes du curseur C dou sqlcod <> 0 C eval rang = rang + 1 C write SFL C exsr lecture C enddo C exfmt CTL C endif * Fermeture curseur C/EXEC SQL CLOSE C1 C/END-EXEC * * LECTURE SEQUENTIELLE DU CURSEUR * C lecture BEGSR C/EXEC SQL FETCH C1 INTO :ZONE1, :ZONE2 C/END-EXEC C ENDSR |
|
même exemple en COBOL : * *------------------------------ PROCEDURE DIVISION USING TABL. *------------------------------ PGM. * * mise en place de l'ordre SQL dans var * STRING "SELECT ZON1, ZON2 FROM SQLDB/" TABL " WHERE ZON1 > ?" DELIMITED BY SIZE INTO VAR. * * préparation de l'ordre SQL sous le nom P1 * EXEC SQL PREPARE P1 FROM :VAR END-EXEC. * * déclaration d'un curseur résultant de P1 |
|
* EXEC SQL DECLARE C1 CURSOR FOR P1 END-EXEC. * * ouverture du curseur (zone remplacera le "?") * EXEC SQL OPEN C1 USING :ZONE END-EXEC. * * traitement du curseur comme précédement * par l'ordre FETCH puis fermeture par CLOSE * * (même principe) Si les variables extraites par l'ordre SELECT ne sont pas toujours les mêmes cette technique n'est plus utilisable Il faut passer par l'ordre DESCRIBE qui place dans SQLDA la liste des zones et leur définition après un ordre PREPARE. |
|
Il faudra allouer de l'espace mémoire de manière dynamique (pointeurs) 1°/ Mise en place de l'ordre SELECT dans VAR 2°/ PREPARE p1 FROM :var 3°/ Allocation d'espace mémoire pour SQLDA (suivant nbre de colonnes) 4°/ DESCRIBE p1 INTO :sqlda 5°/ DECLARE c1 CURSOR FOR p1 6°/ OPEN c1 7°/ Allocation d'espace mémoire (devant recevoir un enregistrement) et stockage de l'adresse dans SQLDA 8°/ jusqu'à EOF - FETCH c1 USING DESCRIPTOR :sqlda traitement de la ligne lue - fin jusqu'à. 9°/ CLOSE c1. |
|
FONCTIONS DE VALIDATION INVALIDATION -------------------------------------- 1/ on indique le niveau de validation avec lequel on travail - sur la commande de compilation (voir plus bas) 2/ on valide (ou pas) par les ordres COMMIT Validation ROLLBACK Invalidation ATTENTION, l'ordre SQL COMMIT possède un paramètre important HOLD : - Les ressources sont suspendues. Les curseurs actifs ne sont pas désactivés , les instructions SQL préparées sont conservées et toutes les ressources |
|
(sans) - Les ressources ne seront pas suspendues. Les curseurs actifs sont désactivés, les instructions SQL préparées sont supprimées et les ressources suspendues libérées. - Un curseur déclaré WITH HOLD ne sera pas désactivé ce sont les ordres Commit et Rollback qui définissent la frontière ils représentent la fin d'une transaction ET le début de la suivante - sauf à utiliser la notion de SAVEPOINT (V5R20) Cette notion permet de matérialiser des étapes dans une transaction offrant la possibilité de revenir à une étape précise et non au début de la transaction en cas de ROLLBACK. |
|
un point de reprise est posé par l'instruction SAVEPOINT (un SEUL AS/400) UPDATE client ... ; SAVEPOINT MAJ ON ROLLBACK RETAIN CURSORS ; DELETE FROM CLIENTS ... ; DELETE FROm COMMANDES ; SAVEPOINT DLT ON ROLLBACK RETAIN CURSORS ; INSERT INTO ... ; IF xxx ROLLBACK TO SAVEPOINT MAJ ELSE RELEASE SAVEPOINT ; ON ROLLBACK RETAIN CURSORS, permet de garder le(les) curseur(s) ouverts ON ROLLBACK RETAIN LOCKS , permet de garder les verrouillages/ligne |
|
Procédures cataloguées [STORED PROCEDURES] : ----------------------------------------------- SQL permet maintenant de demander l'exécution d'un programme sur un serveur distant (normalement devant réaliser une action sur la base de ce serveur) Cette fonction n'est pas encore normalisée (mais largement répandue). - certains SGBD n'autorisent que des procédures SQL. - DB2/400 autorise l'appel à n'importe quel programme. + avec passage de paramètres. + ces paramètres pouvant être renseignés par le pgm distant. Syntaxe CALL nom-procédure---------------------------------------------- | | |-(param1,[param2,...])-| |
|
nom-procédure = nom du pgm à appeler ou procédure telle que définie par DECLARE PROCEDURE (voir ci-dessous) la procédure est recherchée dans la collection en cours pour qualifier : BIB/PGM avec la convention système BIB.PGM avec la convention SQL voir: STRSQL paramètre NAMING CRTSQLxxx paramètre OPTION paramx = paramètre envoyé (ou reçu) qui peut être + une constante + une variable du pgm (:var) (par défaut :var est en entrée/sortie avec SQL statique en entrée (envoyée) avec SQL dynamique) + NULL + une valeur spéciale (CURRENT DATE/TIME/TIMEZONE , SERVER) (USER) |
|
Il est possible de déclarer une procédure afin de préciser certaines options DECLARE -nom-proc-PROCEDURE------------------------------------------------> | |-IN----| | |-(param---OUT-----type de données(lg)-----| |-INOUT-| |-EXTERNAL------------------| >------------------------------LANGUAGE--langage--------------------------- |-EXTERNAL NAME-nom-externe-| |-SIMPLE CALL-----------| |-SIMPLE CALL WITH NULL-| définition des paramètres : IN le paramètre est uniquement reçu par la procédure OUT le paramètre est renvoyé par la procédure INOUT le paramètre est utilisé dans les deux sens type de donnée(lg) : idem CREATE TABLE CHAR(xx) INT(lg, dec) etc ... |
|
EXTERNAL NAME permet de définir le nom réel du pgm sur le serveur, si cette clause n'est pas renseignée c'est le nom de procédure qui est utilisé. LANGUAGE permet de définir le langage d'origine du pgm à exécuter C, CL, COBOL, COBOLLE, RPG, RPGLE, REXX, ... ceci est obligatoire avec une procédure REXX si cette clause n'est pas utilisé le server recherche l'attribut du pgm et par défaut le considère comme un pgm C. SIMPLE CALL WITH NULL signifie que le pgm doit recevoir les indicateurs indiquant les valeurs nulles (254 param maxi) SIMPLE CALL le pgm ne reçoit pas ces indicateurs (255 param maxi) l'instruction DECLARE PROCEDURE doit être utilisée pour appeler des procédures REXX, et doit être écrite AVANT l'instruction CALL. |
|
Particularité de SQL : il ne reconnait pas les membres.
vous pouvez quand même outrepasser en utilisant les procédures :
EXEC SQL
CALL QSYS.QCMDEXC ('OVRDBF FIC MBR(xxx) OVRSCOPE(*JOB)',
0000000035,00000)
END-EXEC.
Attention
le point (dans QSYS.CMDEXC) doit être le qualifiant en cours
la virgule (dans 0000000035,00000) doit être la marque décimale.
Cette solution peut-être utilisée aussi avec ODBC.
|
|
Avec ODBC vous pouvez même renvoyer un groupe d'enregistrements en une seule fois : Dans la procédure appelée, ajoutez : SET RESULT SETS ----------------------------------------------------- ! ! !-----CURSOR nomcurseur-----------------! ! ! ! ! !--ARRAY nomdetableau -----FOR x ROWS---- Le curseur indiqué (il doit être ouvert) ou l'ensemble des enregistrements placés dans le tableau (ou la table en COBOL) représentent les valeurs en retour retournées au micro. Cet ordre n'est valide qu'avec ODBC, il n'y a pas d'invite SQL interactif. |
|
Vous pouvez enfin préparer tout cela à l'avance en déclarant une fois pour toute la procédure et ses particularités ( ce qui est conseillé ) CREATE PROCEDURE nom-proc-- (même syntaxe que DECLARE----)----> ----------------------------------------------. ! ! !--DYNAMIC RESULT SETS -----n-----------! n indique le nombre de "result sets" retournés La définition est enregistrée dans les fichiers SYSPROCS et SYSPARMS du catalogue. |
|
Fonctions Définies par l'utilisateur (V4R40) : ------------------------------------------------ une fonction est un programme ou une procédure dans un programme de service enregistré(e) dans les catalogues SQL par CREATE FUNCTION. par exemple : CREATE FUNCTION AF4TEST/DT8_CHAR8 (DEC(8 , 0) ) RETURNS CHAR(8) EXTERNAL NAME 'AF4TEST/DT_SQL(DT8_CHAR8)' (1) PARAMETER STYLE GENERAL (2) RETURNS NULL ON NULL INPUT ; (3) (1) fait référence à DT8_CHAR8 dans DT_SQL (2) le passage de paramètres se fait sans gestion de la val. nulle (3) la fonction retourne nul si un des argument est nul (il n'y aura pas d'appel) |
|
voici le source RPG de la procédure associée à cette fonction : H nomain * prototype ========================================================= D dt8_char8 pr 8 D 8 0 * fonction ========================================================== Pdt8_char8 b export d pi 8 d dt8_recue 8 0 d date s D datfmt(*dmy/) c *iso test(d e) dt8_recue c if %error c return '01/01/40' c else c *iso move dt8_recue date c return %char(date) c endif p e |
|
la création se réalise par : CRTRPGMOD MODULE(DT8_CHAR8) et CRTSRVPGM SRVPGM(DT_SQL) MODULE(DT8_CHAR8) EXPORT(*ALL) ___________________________________________________________________________ cela permet maintenant de passer des ordres SQL comme : [soit un fichier client avec DATCRT DEC(8 , 0) ] SELECT * FROM CLIENTS WHERE DT8_CHAR8(DATCRT) <> '01/01/40' SELECT * FROM CLIENTS WHERE DATE( DT8_CHAR8(DATCRT) ) = current date .................................................................. : : : la fonction SQL date attend une chaîne de caractères au format : : du JOB, soit en interactif 'JJ/MM/AA'. : : : :................................................................: |
|
Deuxième exemple retournant une date au format caractère en ISO d dt8_char10 pr 10 d 8 0 Pdt8_char10 b export d pi 10 d dt8_recue 8 0 d date s D datfmt(*iso) c *iso test(d e) dt8_recue c if %error c return '0001-01-01' c else c *iso move dt8_recue date c return %char(date) c endif c P e |
|
déclaration : CREATE FUNCTION AF4TEST/DT8_CHAR10 (DEC(8 , 0) ) RETURNS CHAR(10) EXTERNAL NAME 'AF4TEST/DT_SQL(DT8_CHAR10)' PARAMETER STYLE GENERAL RETURNS NULL ON NULL INPUT ; lors de la déclaration de la fonction vous pouvez convertir (caster) un/les argument(s) ou la valeur retour CREATE FUNCTION AF4TEST/DT8_DATE (DEC(8 , 0) ) RETURNS DATE CAST FROM CHAR(10) EXTERNAL NAME 'AF4TEST/DT_SQL(DT8_CHAR10)' PARAMETER STYLE GENERAL RETURNS NULL ON NULL INPUT ; ce qui permet directement : |
|
SELECT * FROM CLIENTS WHERE DT8_DATE(DATCRT) = current date Vous pouvez aussi créer des fonctions à l'aide du PL/SQL (ou en full SQL) à condition d'avoir le compilateur C. En effet une fonction écrite complétement en SQL génère un source C contenant du SQL (dans QTEMP) et lance la commande CRTSQLCI afin de créer un programme de service. exemple : create function af4test/dec_franc_euros (VALEUR decimal(9, 2)) returns decimal(9, 2) LANGUAGE SQL RETURNS NULL ON NULL INPUT BEGIN DECLARE VALEUROS DECIMAL ( 9 , 2 ) ; SET VALEUROS = VALEUR / 6.55957; RETURN VALEUROS ; END; C'est le même langage que pour l'ordre CREATE PROCEDURE. |
|
et bien évidement la fonction inverse : create function af4test/dec_euro_francs (VALEUR decimal(9, 2)) returns decimal(9, 2) LANGUAGE SQL RETURNS NULL ON NULL INPUT BEGIN (1) (3) DECLARE VALFRANCS DECIMAL ( 9 , 2 ) ;(2) (4) SET VALFRANCS= VALEUR * 6.55957; (5) RETURN VALFRANCS ; END; (1) le corps de la fonction est encadré de BEGIN - END; (2) chaque ligne se termine par ; (3) les déclarations se font par DECLARE (même types que CREATE TABLE) (4) les manipulations se font par SET ou VALUES |
|
(5) RETURN termine la fonction en indiquant la valeur retournée. En ce qui concerne les UDT ,francs et euro par exemple, le plus simple est d'écrire des fonctions. une fonction de conversion francs/euros une fonction de conversion euros/francs create function af4test/franc_euros (VALEUR FRANCS) returns EUROS LANGUAGE SQL RETURNS NULL ON NULL INPUT STATIC DISPATCH BEGIN DECLARE VALEUROS DECIMAL ( 9 , 2 ) ; SET VALEUROS = CAST(VALEUR as decimal(9,2) ) / 6.55957; RETURN CAST(VALEUROS as euros); END; L'option STATIC DISPATCH est obligatoire quand une fonction manipule des UDT |
|
create function af4test/euro_francs (VALEUR EUROS) returns FRANCS LANGUAGE SQL RETURNS NULL ON NULL INPUT STATIC DISPATCH BEGIN DECLARE VALFRANCS DECIMAL ( 9 , 2 ) ; SET VALFRANCS= cast(VALEUR as decimal(9,2) ) * 6.55957; RETURN cast(VALFRANCS as FRANCS); END; > select * from voitures where prixe = prixf Opérandes de l'opérateur = incompatibles. > select * from voitures where prixe <> franc_euros(prixf) donne un résultat , ainsi que > update voitures set prixe = franc_euros(prixf) where prixe = cast(0 as euros) |
|
il vous faut aussi, refédinir toutes les fonctions SUM, AVG, etc... on parle alors de fonctions sourcées.(parlerons nous de surcharge ??) il peut donc y avoir plusieurs fonctions ayant le même nom, si elles acceptent des types de données différents.(ou bien de polymorphisme) SQL choisi la bonne fonction, suivant le type manipulé. create function af4test/SUM (francs) returns francs source QSYS2/SUM (decimal() ) ; create function af4test/AVG (francs) returns francs source QSYS2/AVG (decimal() ) ; create function af4test/MAX (francs) returns francs source QSYS2/MAX (decimal() ) ; create function af4test/MIN (francs) returns francs source QSYS2/MIN (decimal() ) ; |
|
Toutes les fonctions SQL sont réputées pour être stockées dans QSYS2. (ne cherchez pas un programme physique, il n'y en a pas) toutes vos fonctions, tous vos types : 1/ peuvent être qualifiés 2/ seront recherchés dans *LIBL, sinon. (a condition que vous soyez en convention système) 3/ vous pouvez préciser le chemin au préalable par SET PATH ..... et retrouver cette valeur par le registre CURRENT_PATH |
|
COMMANDES CRTSQLxxx (RPG,CBL,C,PLI = OPM ------------------------ RPGI, CBLI = ILE) PARAMETRES IMPORTANTS COMMIT *NONE pas de contrôle de validation (DFT) *CHG contrôle de validation actif *CS *ALL même signification que sur STRSQL Attention la valeur par défaut (*CHG), provoquera une erreur à l'exécution si vos fichiers ne sont pas journalisés. |
|
OPTION *SRC liste du Source des ordres SQL (automatique en cas d'erreur) (DFT) *NOSRC pas de liste source *XREF références croisees (DFT) *NOXREF pas de références croisees (DFT) *SYS convention d'appellation système (BIBLI/TABLE) *SQL convention d'appellation SQL (BIBLI.TABLE) DATFMT() DATSEP() format des variables DATE/HEURE dans la base. TIMFMT() TIMSEP() RDB() Choix de la base DRDA (CONNECT automatique) |
