| programmera.net -> oracle -> normal för utskrift | info@programmera.net |
Introduktion till nästlade frågor i Oracle
1. Nästlade frågor
I Oracle kan man placera en SELECT-sats inuti nästan vilken annan SQL-sats som helst. Denna flexibilitet gör att man kan bygga mycket komplexa satser.
2. SUBSELECT i Elementlistan
Man kan lägga en SELECT-sats i elementlistan för en annan SELECT-sats:
Här visas avdelningens beskrivning istället för det intetsägande dep_pk.
SQL> SELECT e.emp_pk, e.last_name, (
SELECT d.dep_name
FROM dep d
WHERE e.dep_pk=d.dep_pk
) as Dep
FROM emp e
WHERE last_name='Kullberg';
EMP_PK LAST_NAME DEP
---------- ------------- --------------------
2 Kullberg Java-utveckling
3. SUBSELECT i FROM
När man placerar en SUBSELECT i FROM-elementet i en SELECT kallas det för en inline view, alltså en vy som definieras på plats. Ett exempel:
Här begränsas raderna så att man bara ser det första svaret.
SQL> SELECT *
FROM (
SELECT *
FROM emp
WHERE dep_pk=2
ORDER BY last_name
)
WHERE rownum < 2;
EMP_PK DEP_PK FIRST_NAME LAST_NAME SALARY ID_NR
---------- ---------- ------------- ------------- ---------- ----------
3 2 Per Johansson 23000 7507120101
4. SUBSELECT i WHERE
Det vanligaste är kanske att man använder en SUBSELECT inuti WHERE-delen av den bärande SELECT-satsen, ofta i kombination med IN eller EXISTS. Nedan ser vi ett exempel på hur IN fungerar:
SUBSELECT-satsen ovan sägs vara okorrelerad eftersom den inte innehåller någon referens till de tabeller som används i den omslutande SELECT-satsen. EXISTS däremot används uteslutande med en korrelerad SUBSELECT. EXISTS returnerar varje rad där SUBSELECT-satsen returnerar något, se exemplet:
SQL> SELECT d.dep_name
FROM dep d
WHERE d.dep_pk IN (
SELECT e.dep_pk
FROM emp e
WHERE e.last_name='Kullberg'
);
DEP_NAME
--------------------
Java-utveckling
SQL> SELECT d.dep_name
FROM dep d
WHERE EXISTS (
SELECT e.dep_pk
FROM emp e
WHERE e.last_name='Kullberg'
AND e.dep_pk=d.dep_pk
);
DEP_NAME
--------------------
Java-utveckling
5. SUBSELECT i ORDER BY
Även i ORDER BY-delen kan man lägga en SUBSELECT. I exemplet nedan ligger samma SUBSELECT-sats både i elementlistan och i ORDER BY.
SQL> SELECT e.emp_pk, e.last_name, (
SELECT d.dep_name
FROM dep d
WHERE e.dep_pk=d.dep_pk
) as Dep
FROM emp e
WHERE salary > 30000
ORDER BY (
SELECT d.dep_name
FROM dep d
WHERE e.dep_pk=d.dep_pk
);
EMP_PK LAST_NAME DEP
---------- ------------- --------------------
4 Magnusson Java-utveckling
1 Svensson Ledningsgruppen
6. SUBSELECT i INSERT
Även i INSERT kan man använda SUBSELECT. Här kommer ett exempel:
Man kan också utföra insert mellan tabeller som har samma kolumner på detta enkla sätt:
SQL> INSERT INTO dep (dep_pk, dep_name)
VALUES ((SELECT max(dep_pk)+1 FROM dep),'HR');
SQL> SELECT * FROM dep;
DEP_PK DEP_NAME
---------- --------------------
1 Ledningsgruppen
2 Java-utveckling
3 Microsoft-utveckling
4 Arkitektgruppen
5 Handläggare
6 HR
6 rader.
I exemplet ovan kopierar vi alla rader i en transaktionstabell till en HISTORY-tabell.
SQL> INSERT INTO XXC_C250_AS_ACCESSES_H SELECT * FROM XXC_C250_AS_ACCESSES ;
40033 rader õr skapade.
SQL> SELECT count(*) FROM XXC_C250_AS_ACCESSES_H;
COUNT(*)
----------
40033
7. SUBSELECT i UPDATE
I en UPDATE kan man också använda SUBSELECT:
SQL> UPDATE emp e
SET salary=(
SELECT e.salary*1.3
FROM emp e2
WHERE e.emp_pk=e2.emp_pk
)
WHERE e.salary IS NOT NULL;
6 rader är uppdaterade.
SQL> SELECT * FROM emp;
EMP_PK DEP_PK FIRST_NAME LAST_NAME SALARY ID_NR
---------- ---------- ------------- ------------- ---------- ----------
1 1 Janne Svensson 65000 6712123223
2 2 Olle Kullberg 27300 7402191212
3 2 Per Johansson 29900 7507120101
4 2 Magnus Magnusson 41600 6609121101
5 Karl Alp 0
6 Bertil Svensson 0
6 rader.
8. SUBSELECT i CREATE TABLE
När man skapar en tabell går det också att använda en SUBSELECT:
SQL> CREATE TABLE emp_copy AS
SELECT *
FROM emp
WHERE emp_pk < 4;
Tabellen är skapad.
SQL> SELECT * FROM emp_copy;
EMP_PK DEP_PK FIRST_NAME LAST_NAME SALARY ID_NR
---------- ---------- ------------- ------------- ---------- ----------
1 1 Janne Svensson 50000 6712123223
2 2 Olle Kullberg 21000 7402191212
3 2 Per Johansson 23000 7507120101
9. SUBSELECT i Funktioner
SUBSELECT kan användas i funktioner:
SQL> SELECT length((
SELECT first_name
FROM emp
WHERE emp_pk=3)) as Len
FROM dual;
LEN
----------
3