Quick SQL Guide - Blogofant - Elefantisch gut!

Aggregation

Cube

If you have dimensions, you can use the cube function. Imagine you have the dimensions time, state and product. When you use the cube function, you can get an insight of these values (you get all possible subtotals). Also a partial cube query is possible.

SELECT …
FROM …
WHERE …
GROUP BY CUBE(product, year, state);

SELECT …
FROM …
WHERE …
GROUP BY product, CUBE(year, state);

Grouping

Rollup

The rollup clause adds subtotals and a grand total. It extends the GROUP BY clause. You should use it when using time values or countries. The rollup clause begin by the right column and goes to the left. Look at the example below:

ROLLUP(y, m, day) or ROLLUP(country, state, city)

SELECT ...
FROM ...
WHERE ...
GROUP BY ROLLUP(country, state, city);

Also a partial rollup is possible, when you just need some subtotals.

SELECT …
FROM …
WHERE …
GROUP BY col1, col2, ROLLUP(country, state, city);

Check out the blog article about the SQL aggregations.

Autonomous Transactions

PL/SQL normally treads the whole modification as a single transaction. If you want to have some part of the code as seperate transaction, you can use the functionallity of autonomous transactions. The statement PRAGMA AUTONOMOUS_TRANSACTION has to be part of the declarative section. Cause the transaction is seperate you have to seperate specify a COMMIT and ROLLBACK.

DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION; 
BEGIN
   -- Code goes here
END;

Check out the blog article about the SQL autonomous transactions..

Create Table

Create an empty table with the same structure as a reference table.

CREATE TABLE emp_temp AS 
   SELECT * 
   FROM emp 
   WHERE 1=0;

Date

Display Months between two Dates

Sometimes you want to display the months between two dates (2 years back to now or between two specific dates). You can do this with the help of the rownum.

The first example use two specific dates. You have to change the beginning date twice and the end date once.

SELECT TO_CHAR(the_month, 'MM-YYYY') Month
FROM
(
   SELECT ADD_MONTHS(TO_DATE('01.01.2017', 'dd.mm.yyyy'), rownum-1) the_month
   FROM DBA_OBJECTS
   WHERE rownum <= MONTHS_BETWEEN(TO_DATE('31.12.2018', 'dd.mm.yyyy'), ADD_MONTHS(TO_DATE('01.01.2017', 'dd.mm.yyyy'), -1))
   ORDER BY the_month
);

The second example uses sysdate as the end date plus a specific time to go two the past. In this example the output will go back one year. You get 13 values cause the actual month is twice (in this year and the old year).

SELECT TO_CHAR(the_month, 'MM-YYYY') Month
FROM
(
   SELECT ADD_MONTHS(ADD_MONTHS(SYSDATE, -12), rownum-1) the_month
   FROM DBA_OBJECTS
   WHERE rownum <= MONTHS_BETWEEN(SYSDATE, ADD_MONTHS(ADD_MONTHS(SYSDATE, -12), -1))
   ORDER BY the_month
);

JSON

Sometimes there is the need to query a json value, cause one value has more than just one information. Also for dynamic purposes json values are often needed. Here is a little tutorial, for more information see https://docs.oracle.com/en/database/oracle/oracle-database/18/adjsn/

To start, we just generate a json value with name, age and an address. Mention, that the address is again a json value with the fields city and country.

SELECT '{"UserName":"David","Age":26,"Address":{"City":"Graz", "Country":"Austria"} }' po_document
FROM DUAL;

To get a json value, you can use the json_value parameter. The first argument is the column of your table, the second argument is the path of the field value. In the first example you will get the age, in the second example the country.

SELECT json_value(po_document, '$.Age') 
FROM (SELECT '{"UserName":"David","Age":26,"Address":{"City":"Graz", "Country":"Austria"} }' po_document 
      FROM DUAL);
=> Output: 26

SELECT json_value(po_document, '$.Address.Country') 
FROM (SELECT '{"UserName":"David","Age":26,"Address":{"City":"Graz", "Country":"Austria"} }' po_document 
      FROM DUAL);
=> Output: Austria

Last Day

Often you need to now the last day. If you have the last day. You can get the last day with the help of trunc. So you add a month and truncate the date. This means you have the first date of the next month which will be the first of month x. Now you can subtract one day and have the last day. But more, there is a function called LAST_DAY which will help you.


SELECT 
   DISTINCT(last_day(TO_DATE(da.end_date + 1 - rownum))) Last_Day
FROM 
   all_objects,
   (SELECT TO_DATE('01.01.2018', 'dd.mm.yyyy') start_date
          ,TO_DATE('31.12.2018', 'dd.mm.yyyy') end_date
    FROM DUAL) da
WHERE 
   TRUNC(da.end_date + 1 - rownum,'MM') >= TRUNC(da.start_date,'MM')
ORDER BY 1

Default if no row is returned (SQL)

Sometimes default values are necassary. Normally you can use NVL or NVL2 when you get NULL values as return value. But it is also possible, that no row is returned. In this case, NVL or NVL2 is not working, cause you get no value back – not even NULL. Therefore, there are some options – like producing a NULL value.

-- Use the COALESCE statement, MAX produces automatic a NULL value
SELECT COALESCE(MAX(dummy), 'Z') result
FROM DUAL
WHERE DUMMY = 'Y'; -- 'X'

-- Use the NVL statement, MAX produces automatic a NULL value
SELECT NVL(MAX(dummy), 'Z') result
FROM DUAL
WHERE DUMMY = 'Y'; -- 'X'

-- Use the EXISTS statement in combination with CASE
SELECT CASE
    WHEN EXISTS(SELECT 1
                FROM DUAL
                WHERE DUMMY = 'Y') -- 'X'
    THEN (SELECT DUMMY
          FROM DUAL
          WHERE DUMMY = 'Y') -- 'X'
    ELSE 'Z' result
    END
FROM DUAL;

-- Use the COUNT function in combination with CASE
-- MIN is needed to identify the correct value
SELECT CASE
    WHEN COUNT(1) > 0
        THEN MIN(DUMMY)
        ELSE 'Z'
    END result
FROM DUAL
WHERE DUMMY = 'Y'; -- 'X'

Check out the blog article about the SQL default value if no row is returned.

Keywords: SQL Default if no rows returned, oracle sql select default value if null

Dupletten / Doppelte Werte

SELECT #column(s), COUNT(*) 
FROM #tableName
GROUP BY #column(s)
HAVING COUNT(*) > 1;

Exceptions

SQL Customer Exceptions

Customer Exceptions can have an error number betweenn -20000 and -20999 (1.000 possibilites). After the error number you can define an error text.

THEN RAISE_APPLICATION_ERROR(-20001,'My exception was raised');

SQL Exceptions in Loop

You can also use exceptions in loops. Use the following code when you want that the loop continues (just possible when an own PL/SQL Block with begin and end statement is inside the loop:

FOR ... LOOP
   BEGIN
       EXCEPTION
   END;
END LOOP;

Flashback Query

The query of a specific value from the past (often just minutes) can be reproduced with the keywords “AS OF TIMESTAMP” and a specifc date or time interval. However, the AS OF clause always refers to a table, so for a JOIN, an AS OF clause must be specified for each table.

SELECT * 
FROM emp AS OF TIMESTAMP systimestamp - INTERVAL '5' MINUTE
WHERE  empno = 1234;

Formula (Math Functions)

If you want to calculate a value with a function which is stored in the table (row or column) you can use the follwing code to execute the math formula. Replace placeholders with the values you need.

DECLARE
    v_return NUMBER;
    pi_val VARCHAR2(50) := '2*6*10';
BEGIN
    EXECUTE IMMEDIATE 'select '||pi_val||' from dual' INTO v_return;
    dbms_output.put_line(v_return);
END;

Example Code for a function:

set serveroutput on
DECLARE
    v_return NUMBER;
    pi_val VARCHAR2(50) := '2*6*10';
    pi_val2 VARCHAR2(50) := 'ROUND(x/12,2)';
    pi_x NUMBER := 12.558;
BEGIN
    -- EXECUTE IMMEDIATE 'select '||pi_val||' from dual' INTO v_return;
    -- dbms_output.put_line(v_return);
    pi_val2 := REPLACE(pi_val2, 'x', TO_CHAR(pi_x, '9G990D00', 'NLS_NUMERIC_CHARACTERS = ''.,'''));
    dbms_output.put_line(pi_val2);
    EXECUTE IMMEDIATE 'select '||pi_val2||' from dual' INTO v_return;
    dbms_output.put_line(v_return);
END;

CREATE OR REPLACE FUNCTION Calc(pi_x NUMBER, pi_formula VARCHAR2) 
RETURN NUMBER IS
    v_return NUMBER;
    BEGIN
        pi_formula := REPLACE(pi_formula, 'x', TO_CHAR(pi_x, '9G990D00', 'NLS_NUMERIC_CHARACTERS = ''.,'''));
        EXECUTE IMMEDIATE 'select '||pi_formula||' from dual' INTO v_return;
        RETURN v_return;
    END;
    
SELECT Calc(12.558, 'ROUND(x/12,2)')
FROM DUAL;

Check out the blog article about the SQL mathematic function.

Grant

To grant privilages to a specific user.

GRANT privilege_name 
ON object_name 
TO {user_name |PUBLIC |role_name} 
[WITH GRANT OPTION];  

f.e.
GRANT ALL
ON table_name
TO user_name;

IN List

The normal IN List can handle a maximum of one thausand values. But there is an option (the multi value IN Liste) where you can add as much values as you want.

SELECT * 
FROM emp 
WHERE 1=1 
   AND empNo IN (1, 2, ... <maximum 1000 values>);

If you want to use the multi value IN List it is important, that you use multiple values. Use f.e. 1 as first value and compare it with the other 1 as first value in your multi value In List. 1=1 is always true, so just the second parameter is used for comparing.

SELECT * 
FROM emp 
WHERE 1=1  
   AND (1,empNo) IN ((1, 1), (1,2));

Level

Organisations Chart

SELECT LPAD(last_name, LENGTH(last_name) + (LEVEL*2) - 2, '_') AS chart
FROM employees
START WITH emp_id = 1234 --CEO
CONNECT BY PRIOR emp_id = manager_id

LOOP

Label

Loops can be labeled with the statement <<label>>, where label is the name. Labeling should happen in nested loops. Labeling will be necassary when using the GO TO or EXIT statement.

<<OUTER_LOOP>>
LOOP
   <<INNER_LOOP>>
   LOOP
      -- EXIT OUTER_LOOP WHEN 1=1
   END LOOP;
END LOOP;

CONTINUE

The CONTINUE statement jumps out of the current loop and start the next iteration. Is is possible to use CONTINUE as well as CONTINUE WHEN. It is also possible to jump to an outer loop (labeled). The CONTINUE statement will skip the code following and will start the next iteration.

BEGIN 
   <<outerLoop>> 
   FOR outer_index IN 1 .. 3 
   LOOP 
      CONTINUE WHEN MOD (outer_index, 2) = 0; 
 
      DBMS_OUTPUT.put_line ('OUTER: ' || outer_index); 
 
      FOR inner_index IN 1 .. 3 
      LOOP 
         CONTINUE outerLoop WHEN inner_index = 2; 
 
         DBMS_OUTPUT.put_line ('INNER ' || inner_index); 
      END LOOP; 
 
      DBMS_OUTPUT.put_line ('Rest of outer loop'); 
   END LOOP outer; 
END; 

OUTER 1
INNER 1
OUTER 3
INNER 1

GO TO

It is possible to jump to a labeled statement. Transfer can only be done within the subprogram. Is is not possible to jump from the exception part to the execution part.

EXIT WHEN

The EXIT statement exits the current loop. If the loop is nested and the inner loop has an EXIT statement, just the inner loop is exited – but not the outer loop. EXIT WHEN <boolean> = true will exit the current loop.

NULL Values

NVL

The normal NVL statement checks if a value is NULL. If so, the second statement value is outputet, otherwise the value which is checked (in case the value is not NULL). The datatypes have to be the same. NVL handels the following code.

IF 
   expr1 IS NOT NULL     
THEN expr1 
END IF; 

IF 
   exp1 IS NULL     
THEN expr2 
END IF;

NVL2

When using NVL2 you have one more possibility: You can also define a value, when the test value IS NOT NULL. So you have two parameters (NULL and NOT NULL) plus your test value, which you want to test. The NVL2 statement does nothing else then the following code:

IF 
   expr1 IS NOT NULL     
THEN expr2
END IF; 

IF 
   expr1 IS NULL     
THEN expr3 
END IF;

Use the follwing code example as comparision between NVL and NVL2:

NVL(expr1, expr2) = NVL2(expr1, expr1, expr2)

LISTAGG

LISTAGG(#col1#, ',') WITHIN GROUP(ORDER BY  #col1# )

SQL Distinct LISTAGG

The distinct SQL Listagg is used, when the normal Listagg produces too much values. With the Distinct Listagg each value occurs just once.

RTRIM(REGEXP_REPLACE(LISTAGG(#col1#, ',') WITHIN GROUP(ORDER BY  #col1# ) OVER (PARTITION BY  
#col2# ), '([^,]+)(,\1)+', '\1'), ',') as "Name"

ORDER BY

Conditional ORDER BY

Important when using conditional ORDER BY is, that on the same level exists the same data type. There is one big problems: Converting Numbers to Char and order them. But there is an easy solution.

SELECT myCharValue, myText, myNumber
FROM testTable
WHERE 1=1
ORDER BY myCharValue, 
   CASE
      WHEN myCharValue IS NOT NULL
      THEN myTest
      ELSE lpad(TO_CHAR(myNumber),50) -- 50 is the maximal length of the number
   END;

If you want to sort the numbers from the highest number to the lowest, you can use the following work around:

SELECT myCharValue, myText, myNumber
FROM testTable
WHERE 1=1
ORDER BY myCharValue, 
   CASE
      WHEN myCharValue IS NOT NULL
      THEN myTest
      ELSE lpad(TO_CHAR(10000-myNumber),50) -- 50 is the maximal length of the number, 10000 is the highest possible number
   END;

OUTPUT

Activate Display Output

To activate the display output for Oracle, PL/SQL and Oracle SQL Developer just use the following code snippet.

SET SERVEROUTPUT ON;

Display Output

To output number, char oder date values you can use the dmbs_output.put_line statement.

DBMS_OUTPUT.PUT_LINE(v_result);

It is also possible to not start a new line. Then you need to serve the following code snippet.

DBMS_OUTPUT.PUT(v_result);

If you just want to test some output code (f.e. not inside a PL/SQL block) you have to use the execute statement.

EXECUTE DBMS_OUTPUT.PUT_LINE('Not in a PL/SQL block');

Example Code to see the functionality:

BEGIN
    dbms_output.put_line('Hello');
    dbms_output.put_line('how');
    dbms_output.put('are');
    dbms_output.put('you');
    dbms_output.put_line('?');
END;

-- Output
Hello
how
areyou?

Puffer

The default size of the output puffer is 20.000 bytes, but you can increase the size to a maximum of 1.000.000 bytes. You can do this with the following statement:

DBMS_OUTPUT.ENABLE(1000000);

OVER Clause

Preceding / Following

INTERVAL '1' MONTH FOLLOWING

PL/SQL Block

The simplest form of the PL/SQL block is BEGIN and END. You can also declare variables in the DECLARE section.

DECLARE
   v_return NUMBER;
   v_string VARCHAR2(50 char) := 'xyz'
BEGIN
   -- code goes here
END;

Primary Key

SYS_GUID()

If you don’t want to generate an Sequence or Id by your own, you can use SYS_GUID(). It generates a globally unique identfier (RAW, 16 Bytes). Its generated using host identfier , process or thread identfier and a non repeating value.

INSERT INTO my_primary_table(primary_key_id) VALUES(SYS_GUID());

Rekursion

REGEXP

SQL REGEXP_LIKE

If you need to use the LIKE clause but you have a pattern – so you don’t know exactly the value. If you don’t know the exact value, you can’t use the LIKE clause. Instead you can use the REGEXP_LIKE clause. The first argument is the expression and the second parameter the pattern.

‚^A(*)‘	Value has to start with an A
‚(*)n$‘)	Value has to end with an n
‚Anders(o\|e\|a)n‘)	Value has to be Anderson, Andersen or Andersan

The whole guide is available at Oracle.

 WHERE regexp_like(#col#,'^Sta|^Anf|^Abc');

The third parameter is the match parameter. Use ‚i‘ for case-insensitive matching and ‚c‘ for case-sensitive matching.

Trigger

Compound Trigger

A compound trigger has four timing points:

before the trigger is firing
before each row that the firing statement affects
after each row that the firing statement affects
after the trigger has fired

When using compund triggers, statement level as well as row level actions can be but in a single trigger. The
compound trigger has to be an DML trigger.

CREATE OR REPLACE TRIGGER co_trigger
FOR [INSERT|DELETE]UPDATE [OF column] ON table
COMPOUND TRIGGER

BEFORE STATEMENT IS
BEGIN
   NULL;
END BEFORE STATEMENT;

BEFORE EACH ROW IS
AFTER EACH ROW IS
AFTER STATEMENT IS

END co_trigger;

Another possibility is to declare the trigger autonomous. You can do this by adding the following statement:

DECLARE 
    pragma autonomous_transaction;

Total

If you want to calculate the total of the preceding rows you can use the PRECEDING statement. This will select all the rows between the defined rows (current row, rows from beginning, …). The following example will output the total with the next id.

SELECT id, accid, val_date, amount, 
    SUM(amount) OVER (PARTITION BY accid ORDER BY val_date, id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) total
FROM
(
SELECT 1 id, 8000 accid, sysdate val_date, 1 amount FROM DUAL
UNION ALL
SELECT 2 id, 8000 accid, sysdate val_date, 2 amount FROM DUAL
UNION ALL
SELECT 3 id, 8000 accid, sysdate - 1 val_date, 3 amount FROM DUAL
UNION ALL
SELECT 4 id, 5000 accid, sysdate val_date, 3 amount FROM DUAL
);

If you want to ouput the balance in the same row, you can use the follwoing code (delete the 1 PRECEDING argument). In this example you have the commulative total (but seperated per account id).

SELECT id, accid, val_date, amount, 
    SUM(amount) OVER (PARTITION BY accid ORDER BY val_date, id ROWS UNBOUNDED PRECEDING ) total
FROM
(
SELECT 1 id, 8000 accid, sysdate val_date, 1 amount FROM DUAL
UNION ALL
SELECT 2 id, 8000 accid, sysdate val_date, 2 amount FROM DUAL
UNION ALL
SELECT 3 id, 8000 accid, sysdate - 1 val_date, 3 amount FROM DUAL
UNION ALL
SELECT 4 id, 5000 accid, sysdate val_date, 3 amount FROM DUAL
);

View

Materialized View

A normal view is just a virtual construct. When you update or insert a value into a view, the database automatically insert the value into the table in the background. If you just display the view, the query is executed (computing time and power is needed). Another option is to create a materialized view. This is a view which is in fact a copy of the table (or the SQL Query). That means, that storage is needed (you trad computing time with storage). The view updates periodically, meaing that some data can be out of date and some data is not yet available.

CREATE MATERIALIZED VIEW my_mat_view
   REFRESH FAST 
   START WITH SYSDATE
   NEXT SYSDATE + 1 AS 
      SELECT * 
      FROM <table_name>;