Requête utile
Liste de requête utiles pour un SGBD Oracle
-- Requêtes Utile
-- Modification Mot de passe utilisateur
-- On modifie l'utilisateur WIKI_PROD et on remplace le mdp DORP_IKIW par IKIW
ALTER USER WIKI_PROD IDENTIFIED BY IKIW REPLACE DORP_IKIW;
-- Inversement
ALTER USER WIKI_PROD IDENTIFIED BY DORP_IKIW REPLACE IKIW;
-- Débloquer le compte (se bloque au bout de 10 tentatives)
ALTER USER WIKI_PROD ACCOUNT UNLOCK;
-- Récuperer toutes les contraintes existantes pour une table
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = '<tablename>';
-- Inner join avec COUNT(*)
0 != (SELECT COUNT(*) FROM TABLE2 WHERE TABLE2.ID_PERSONNEID_USER = TABLE1.USER.ID)
-- Hiérarchie des Entités
SELECT LPAD(ENTITY.TITLE_0,TITRE, LENGTH(ENTITY.TITLE_0)TITRE) + (LEVEL - 1) * 3, '+') AS Organigramme, ENTITY.ID, ENTITY.ID_ENTITY_FATHER1,CODE, ID_ENTITY_TYPE,ENTITY.ID_ENTITY_FATHER, LEVEL FROM ENTITY
CONNECT BY ENTITY.ID = PRIOR ENTITY.ID_ENTITY_FATHER1ID_ENTITY_FATHER
START WITH ENTITY.ID = 78121
ORDER BY LEVEL DESC;
-- Récupération des informations de la BDD
SELECT
col.table_name,
col.column_name,
col.data_type,
col.data_length
FROM
sys.all_tab_columns col
INNER JOIN sys.all_tables t ON
col.owner = t.owner
AND col.table_name = t.table_name
WHERE
col.owner = '<schemaname>'
AND col.table_name = '<tablename>' -- optionnel
ORDER BY
col.table_name;
-- Parcours d'un XML avec condition
-- Si on effectue le traitement sur une colonne de type XMLTYPE le resultat pourra s'auto formater en XML (ne pas oublier d'enlever GetClobVal)
-- Ici XML_COLUMN est la colonne au format XML qui est dans TABLE1.
SELECT id, code,
XMLQuery(
' for $i in /Root/Options/Option
where $i/Item[@code="WHEN_COME_FROM_PREVIOUS"MON_CODE_ITEM"]
return $i/Item/Value/@value ' PASSING Xmltype(XML_PARAM)XML_COLUMN) RETURNING CONTENT).GetClobVal() AS res
FROM process_stepTABLE1 WHERE ID_PROCESSID IN (SELECT idID FROM PROCESSTABLE2 WHERE id IN (SELECT ID_PROCESS FROM TSESSION WHERE MOCODE = 'CLASSIC'DEMO' AND MODELTYPE = '0')1');
-- Procedure pour retourner le résultat d'une requête
-- Avec 2 rqtsrequêtes
DECLARE
value NVARCHAR2(2000);
codeProcesscode NVARCHAR2(255);
c1 SYS_REFCURSOR;
CURSOR cursorData IS SELECT CODE FROM PROCESSTABLE1 WHERE CATEGORY = 'START_ON_STEP'MY_CATEGORY' AND TEMPLATE = 'EXPORT_PDF'EXPORT';
BEGIN
OPEN cursorData;
LOOP
FETCH cursorData
INTO codeProcess;code;
EXIT
WHEN cursorData%NOTFOUND;
value := 'XML_PARAMCODE LIKE ''%"' || codeProcesscode || '"%'' OR ' || value;
END LOOP;
value := SUBSTR(value,1,LENGTH(value)-3);
CLOSE cursorData;
open c1 for 'SELECT ID, CODE, ID_PROCESS, MO, MODEL, TITLE_0TITRE
FROM TSESSIONTABLE2 WHERE
ID_PROCESSTEMPLATE IN (SELECT ID_PROCESSCODE FROM PROCESS_STEPTABLE3 WHERE ' || value ||')
AND MOCATEGORY = ''CLASSIC'MY_CATEGORY'' AND MODELTYPE = ''1''';
-- Retourne le résultat de la 2eme requête
DBMS_SQL.RETURN_RESULT(c1);
END;
/
-- Procedure Avecpour parcourir le résultat des 2 rqtsrequêtes
-- Dans ce cas la procédure parcours toutes les tables contenant la colonne "CODE_RESOURCE"
DECLARE
value NVARCHAR2(255);
res NVARCHAR2(255);
c1 SYS_REFCURSOR;
CURSOR cursorData IS SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME = 'ID_RESOURCE_IOF'CODE_RESOURCE';
BEGIN
OPEN cursorData;
LOOP FETCH cursorData INTO value;
EXIT WHEN cursorData%NOTFOUND;
--
DBMS_OUTPUT.PUT_LINE(value);
open c1 for 'SELECT COUNT(*) FROM ' || value ||' WHERE ID_RESOURCE_IOFCODE_RESOURCE = 97'''MY_CODE''';
LOOP FETCH c1 INTO res;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(res ||' ==> ' || value);
END LOOP;
CLOSE c1;
END LOOP;
CLOSE cursorData;
END;
-- Parcourir tous les schéma
DECLARE
value NVARCHAR2(255);
res NVARCHAR2(500);
c1 SYS_REFCURSOR;
CURSOR cursorData IS SELECT USERNAME FROM dba_users WHERE DEFAULT_TABLESPACE = 'USERS' AND ACCOUNT_STATUS = 'OPEN' AND USERNAME NOT IN ('ABSOLUT7'WIKI_TEST', 'LVMH032022'DEMO', 'LVMH_2020', 'PALATINE_PROD', 'RDR_2', 'SADE_L2', 'MAIFDEV', 'STD_DEMO', 'OTIS', 'CEBFC'TEST');
BEGIN
OPEN cursorData;
LOOP FETCH cursorData INTO value;
EXIT WHEN cursorData%NOTFOUND;
--DBMS_OUTPUT.PUT_LINE(value);
DBMS_OUTPUT.PUT_LINE('----------- ' || value ||' --------------');
open c1 for 'SELECT URI_SAVE FROM ' || value ||'.UPLOAD WHERE ID_DOCUMENT IN (SELECT ID_DOCUMENT FROM ' || value ||'.DOC_FILES df WHERE ID_FILES IN (2,3,4))';
LOOP FETCH c1 INTO res;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( value || ' ==> ' || res);
END LOOP;
CLOSE c1;
END LOOP;
CLOSE cursorData;
END;
-- Récupère l'historique des requêtes avec leurs temps d'éxécution et la date d'éxécution
select LAST_LOAD_TIME, ELAPSED_TIME, MODULE, SQL_TEXT elapsed from v$sql order by LAST_LOAD_TIME DESC;
-- ACTIVER/DESACTIVER UN TRIGGER
ALTER TRIGGER update_job_historyupdate_history DISABLE;
ALTER TRIGGER update_job_historyupdate_history ENABLE;
create or replace procedure correctObj(codeSession IN VARCHAR2) AS
evo NVARCHAR2(255);
value NVARCHAR2(255);
total NUMBER(38,0);
nbComp NUMBER(38,0);
resultat NUMBER(38,2);
idEvalue NVARCHAR2(255);
idSession NVARCHAR2(255);
c1 SYS_REFCURSOR;
CURSOR cursorData IS SELECT ID_USED FROM EVALUE
WHERE ID_SESSION = (SELECT ID FROM TSESSION WHERE code = codeSession)
AND CURRENT_STEP IN (SELECT CODE FROM PROCESS_STEP WHERE ID_PROCESS = (SELECT ID_PROCESS FROM TSESSION WHERE code = codeSession) AND ORDER_STEP NOT IN (1,7,8));
BEGIN
SELECT ID INTO idSession FROM TSESSION WHERE CODE = codeSession;
OPEN cursorData;
LOOP
FETCH cursorData INTO idEvalue;
EXIT WHEN cursorData%NOTFOUND;
-- Parcours des evalue + initialisation des vars
-- DBMS_OUTPUT.PUT_LINE(idEvalue);
total := 0;
nbComp := 0;
resultat := 0;
open c1 for 'SELECT DECODE(E7136, ''1'',3,''3'',1,''2'',2, NULL, 0) FROM VALEUR_GRP_ED_575 WHERE E7136 IS NOT NULL AND ID_SESSION = ' || idSession ||' AND ID_USED = ' || idEvalue;
LOOP FETCH c1 INTO value;
EXIT WHEN c1%NOTFOUND;
-- Parcours des objectifs de l'evalue
nbComp:= nbComp + 1;
total := total + value;
IF (nbComp != 0) THEN
resultat := total / nbComp;
END IF;
--DBMS_OUTPUT.PUT_LINE(value || ' TOTAL = ' || total || '/' || nbComp || ' = ' || resultat);
END LOOP;
CLOSE c1;
IF (resultat = 0) THEN
evo := 'NULL';
ELSIF (resultat <= 1.499) THEN
evo := '3';
ELSIF (resultat > 2.5) THEN
evo := '1';
ELSIF (resultat >= 1.5) THEN
evo := '2';
END IF;
EXECUTE IMMEDIATE 'UPDATE VALEUR_GRP_ED_598 SET E7821 = ' || evo || ' WHERE ID_USED = ' || idEvalue || ' AND ID_SESSION = ' || idSession;
END LOOP;
CLOSE cursorData;
END;
/
CALL correctObj('Leistung 2023');
/
DROP PROCEDURE correctObj;