Oracle Tips
- How much longer?
- Does PUT_LINE truncate leading spaces?
- random numbers
- NULL values and "NOT IN"
- NULL values and COUNT()
- sleep/delay
- transformation via the dual table and DENSE_RANK()
- BETWEEN operator with TO_DATE()
How much longer?
-- Heigh-ho, Heigh-ho It's home from worrk we go! This SQL displays the current
-- time and the time left (hours and minnutes) until a specified end time later
-- in the day such as 5:00 PM.
SELECT TO_CHAR(end_time,'HH12:MI AM') AS end_time,
TO_CHAR(ROUND(SYSDATE, 'MI'), 'HH12:MI AM') AS cur_time,
SUBSTR(TRUNC(m/60) || ' hr ' || ROUND(MOD(m, 60)) || ' min',1,12) AS time_left
FROM (SELECT end_time, (TO_CHAR(end_time,'SSSSS')-TO_CHAR(SYSDATE,'SSSSS'))/60 AS m
FROM (SELECT TRUNC(SYSDATE) +
(TO_DATE('5:00 PM', 'HH:MI:AM') - TO_DATE('12:00 AM', 'HH:MI:AM')) AS end_time
FROM DUAL));
END_TIME CUR_TIME TIME_LEFT
-------- -------- ------------
05:00 PM 01:47 PM 3 hr 13 min
Does DBMS_OUTPUT.PUT_LINE really truncate leading spaces?
-- Under certain conditions, the leadingg spaces are removed in lines written
-- by DBMS_OUTPUT.PUT_LINE. Specify thee "FORMAT WRAPPED" option in the
-- "SET SERVEROUTPUT command to keep thee leading spaces from being truncated.
SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE('One');
DBMS_OUTPUT.PUT_LINE(' Two');
DBMS_OUTPUT.PUT_LINE('Three');
END;
/
One
Two
Three
-- If "FORMAT" is not specified then thee format last specified in the session
-- will be used. The default is "FORMATT WORD_WRAPPED", which unfortunately
-- has the side-effect of removing leadiing spaces.
SET SERVEROUTPUT ON SIZE 1000000 FORMAT WORD_WRAPPED
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE('One');
DBMS_OUTPUT.PUT_LINE(' Two');
DBMS_OUTPUT.PUT_LINE('Three');
END;
/
One
Two
Three
How do you generate random numbers?
-- Use the DBMS_RANDOM package which hass 3 procedures: initialize, random,
-- and terminate.
-- initialize() must be called first andd needs to be called only once per
-- session.
-- random() is called as many times as ddesired. It returns a random positive
-- or negative real number
-- terminate() should be called when youu are through generationg random numbers
-- in the session.
--
-- The following script creates and popuulates a table with 500 random
-- integers between 3 and 7.
DROP TABLE t;
CREATE TABLE t (n NUMBER);
DECLARE
lower_bound_v NUMBER := 3;
upper_bound_v NUMBER := 7;
random_cnt_v NUMBER := 500;
BEGIN
-- The inital seed, passed as a parameter to dbms_random.initialize(), should
-- be somewhat random because dbms_random.random() generates the same random
-- numbers if the seed remains the same.
-- Oracle recommends that the seed be at least 5 digits long.
--
-- The following seed is base upon the number of days since January 1st of
-- the current year, the number of seconds past midnight and the last 3
-- digits of the user's current session id.
DBMS_RANDOM.INITIALIZE(TO_CHAR(SYSDATE, 'DDDSSSSS') + MOD(USERENV('SESSIONID'),1000) * 100000);
FOR i IN 1 .. random_cnt_v LOOP
INSERT INTO t
SELECT ABS(MOD(DBMS_RANDOM.RANDOM,upper_bound_v-lower_bound_v+1)) + lower_bound_v FROM dual;
END LOOP;
COMMIT;
DBMS_RANDOM.TERMINATE;
END;
/
SELECT n, COUNT(*)
FROM t
GROUP BY n;
N COUNT(*)
---------- ----------
3 104
4 96
5 100
6 108
7 92
NULL values and "NOT IN"
-- The WHERE clause expression "NOT IN ((SELECT n FROM t)" does not produce the
-- expected results if n contains a NULLL value.
-- Consider the following example:
DROP TABLE t1;
DROP TABLE t2;
CREATE TABLE t1 (id NUMBER);
CREATE TABLE t2 (id NUMBER);
INSERT INTO t1 VALUES(1);
INSERT INTO t1 VALUES(2);
INSERT INTO t1 VALUES(3);
INSERT INTO t1 VALUES(4);
INSERT INTO t1 VALUES(5);
INSERT INTO t2 VALUES(1);
INSERT INTO t2 VALUES(NULL);
COMMIT;
-- The expected result for the followingg SQL is 4, not 0.
SELECT COUNT(*)
FROM t1
WHERE id NOT IN (SELECT id FROM t2);
0
-- The correct result can be found by addding a check for a NULL value
SELECT COUNT(*)
FROM t1
WHERE id NOT IN (SELECT id FROM t2 WHERE id IS NOT NULL);
4
-- Alternatively, the "NOT IN" expressioon could be eliminated by using
-- an outer join. Outer joins usually pperform faster than other ways.
SELECT COUNT(*)
FROM t1, t2
WHERE t1.id = t2.id(+)
AND t2.id IS NULL;
4
NULL values and COUNT()
-- The SQL statement, "SELECT COUNT(n) FFROM t" returns a count of all the rows
-- in t where the value of n is not nulll. COUNT(*) returns a count of all rows
-- regardless of the data values.
DROP TABLE t;
CREATE TABLE t(n NUMBER);
INSERT INTO t VALUES(1);
INSERT INTO t VALUES(NULL);
INSERT INTO t VALUES(3);
INSERT INTO t VALUES(NULL);
INSERT INTO t VALUES(5);
COMMIT;
SELECT COUNT(*), COUNT(n)
FROM t;
COUNT(*) COUNT(N)
---------- ----------
5 3
sleep/delay
-- This procedure simulates a sleep or ddelay for a specified number of
-- seconds. It does this by executing aa do-nothing "WHILE LOOP" for the
-- specified duration.
CREATE OR REPLACE
PROCEDURE proc_sleep(sleep_seconds_in NUMBER)
IS
start_date DATE := SYSDATE;
BEGIN
WHILE (((SYSDATE - start_date) * 24*60*60) < sleep_seconds_in) LOOP
NULL;
END LOOP;
END;
-- For example, the following code sleepps for 30 seconds.
EXEC proc_sleep(30);
transformation via the dual table and DENSE_RANK()
-- This tip shows how to transform rows into columns and columns into rows.
-- First, we'll set up some test data.
DROP TABLE state_t;
CREATE TABLE state_t(
state_abbr VARCHAR2(2) NOT NULL,
state_name VARCHAR2(12) NOT NULL,
capitol_name VARCHAR2(12) NOT NULL,
PRIMARY KEY (state_abbr)
);
INSERT INTO state_t VALUES('CO', 'Colorado', 'Denver');
INSERT INTO state_t VALUES('IL', 'Illinois', 'Springfield');
INSERT INTO state_t VALUES('MI', 'Michigan', 'Lansing');
INSERT INTO state_t VALUES('NE', 'Nebraska', 'Lincoln');
INSERT INTO state_t VALUES('WI', 'Wisconsin', 'Madison');
COMMIT;
COLUMN state_abbr FORMAT A10
SELECT * FROM state_t ORDER BY 1;
STATE_ABBR STATE_NAME CAPITOL_NAME
---------- ------------ ------------
CO Colorado Denver
IL Illinois Springfield
MI Michigan Lansing
NE Nebraska Lincoln
WI Wisconsin Madison
-- Now transform the results above so thhat the columns become rows and the rows
-- become columns. The use of the dual ttable and the dense_rank() function are
-- illustrated.
SELECT DECODE(line, 1, 'state_abbr', 2, 'state_name', 3, 'capitol_name') AS row_name,
MAX(DECODE(row_rank, 1, item)) AS c1,
MAX(DECODE(row_rank, 2, item)) AS c2,
MAX(DECODE(row_rank, 3, item)) AS c3,
MAX(DECODE(row_rank, 4, item)) AS c4,
MAX(DECODE(row_rank, 5, item)) AS c5 -- 5 because there are 5 distinct states in the test data
FROM (SELECT dense_rank() OVER (ORDER BY state_abbr) AS row_rank,
state_abbr,
line,
DECODE(line, 1, state_abbr, 2, state_name, 3, capitol_name) AS item
FROM state_t t1,
(SELECT 1 AS line FROM dual UNION
SELECT 2 AS line FROM dual UNION
SELECT 3 AS line FROM dual) t2 -- 3 because there are 3 attributes per state (state_abbr, state_name, capitol_name)
)
GROUP BY line
ORDER BY line;
ROW_NAME C1 C2 C3 C4 C5
------------ ------------ ------------ ------------- ------------ ------------
state_abbr CO IL MI NE WI
state_name Colorado Illinois Michigan Nebraska Wisconsin
capitol_name Denver Springfield Lansing Lincoln Madison
BETWEEN operator with TO_DATE()
-- "d BETWEEN d1 and d2" is equivalent tto "d >= d1 AND d <= d2"
-- The TO_DATE conversion function treatts the hours, minutes, and seconds part
-- of the date as 00:00:00 if they are nnot specified.
-- For example, use the 2nd or 3rd queryy below, not the first query , to
-- select all the rows with a date in MMay,2001.
DROP table t;
CREATE table t(d DATE);
INSERT INTO t VALUES (TO_DATE('2001-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO t VALUES (TO_DATE('2001-05-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO t VALUES (TO_DATE('2001-05-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO t VALUES (TO_DATE('2001-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
COMMIT;
ALTER SESSION SET nls_date_format = "YYYY-MM-DD HH24:MI:SS";
-- Query #1: This query does not return all the dates in May, 2001.
SELECT *
FROM t
WHERE d BETWEEN TO_DATE('2001-05-01', 'YYYY-MM-DD')
AND TO_DATE('2001-05-31', 'YYYY-MM-DD');
D
-------------------
2001-05-01 00:00:00
2001-05-31 00:00:00
-- Query #2: This query returns all the dates in May, 2001.
SELECT *
FROM t
WHERE d BETWEEN TO_DATE('2001-05-01', 'YYYY-MM-DD')
AND TO_DATE('2001-05-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS');
D
-------------------
2001-05-01 00:00:00
2001-05-31 00:00:00
2001-05-31 23:59:59
-- Query #3: This query also returns alll the dates in May, 2001.
SELECT *
FROM t
WHERE d >= TO_DATE('2001-05-01', 'YYYY-MM-DD')
AND d < TO_DATE('2001-06-01', 'YYYY-MM-DD');
D
-------------------
2001-05-01 00:00:00
2001-05-31 00:00:00
2001-05-31 23:59:59
...