[PL\SQL] Select a random row from a database table

There is some way to select a random record or row from a database table.

SELECT col1, col2, col3  FROM (SELECT * FROM tcard SAMPLE(1) ORDER BY dbms_random.VALUE)
          WHERE branch = 1
                CANCELDATE > SYSDATE
                AND ROWNUM <= 100);

[PL\SQL] Increasing results

data

MNTH SM 1 10 2 20 3 30 4 40

WITH t AS (
SELECT 01 mnth,10 sm FROM dual UNION ALL
SELECT 02, 20 FROM dual UNION ALL
SELECT 03, 30 FROM dual UNION ALL
SELECT 04, 40 FROM dual
)
SELECT mnth, sm,
       SUM(sm) over(ORDER BY mnth ROWS UNBOUNDED PRECEDING) FROM t;

result

MNTH SM TOTAL 1 10 10 2 20 30 3 30 60 4 40 100

[PL\SQL] Pivot Query

data

mnth CITY SUMMA 1 11 1 1 22 25 1 33 1 2 11 52 2 22 3 2 33 3 3 11 3 3 22 65 3 33 6

WITH t AS
(
SELECT 1 mnth, 11 city, 1 summa FROM dual UNION ALL
SELECT 1 mnth, 22 city, 25 summa FROM dual UNION ALL
SELECT 1 mnth, 33 city, 1 summa FROM dual UNION ALL
SELECT 2 mnth, 11 city, 52 summa FROM dual UNION ALL
SELECT 2 mnth, 22 city, 3 summa FROM dual UNION ALL
SELECT 2 mnth, 33 city, 3 summa FROM dual UNION ALL
SELECT 3 mnth, 11 city, 3 summa FROM dual UNION ALL
SELECT 3 mnth, 22 city, 65 summa FROM dual UNION ALL
SELECT 3 mnth, 33 city, 6 summa FROM dual
) select city
      , sum(decode(mnth, 1, summa, 0)) month1
      , sum(decode(mnth, 2, summa, 0)) month2
      , sum(decode(mnth, 3, summa, 0)) month3
      , sum(sum(summa)) over(partition by city) total
  from t
 group by city;

result

/ mnth mnth mnth … mnth12 city 1 2 3 … 12 11 1 52 3 … x 22 25 3 65 … y 33 1 3 6 … z

[PL\SQL] How to Join on top (First) row

SELECT *
  FROM (SELECT ROWNUM t FROM DUAL CONNECT BY LEVEL < 6)
  JOIN (SELECT v, ROW_NUMBER() OVER(PARTITION BY v ORDER BY 1) rwnm
                     FROM (SELECT 1 v
                              FROM DUAL
                            UNION ALL
                            SELECT 1 FROM DUAL
                            UNION ALL
                            SELECT 2 FROM DUAL)) ON t = v
                                                    AND rwnm = 1;

performance

DECLARE
 l_start NUMBER;
 l_end   NUMBER;
BEGIN
 ...
 l_start := dbms_utility.get_time;
 ...
 l_end   := round((dbms_utility.get_time - l_start) / 100, 2);
 dbms_output.put_line(l_end || ' seconds...');
END;

(PL\SQL) How to get MAX(num)+DISTINCT str

CLEAR;  
with t as  
 (
 select '11' num, 'string1' str, '1' DATA from dual union all  
 select '12' num, 'string1' str, '3' DATA from dual union all  
 select '15' num, 'string1' str, '3' DATA from dual union all  
 select '25' num, 'string2' str, '10' DATA from dual union all  
 select '26' num, 'string2' str, '2' DATA from dual union all  
 SELECT '29' num, 'string2' str, '4' DATA from dual  
 )  
SELECT * FROM t  
WHERE num IN (SELECT MAX(num) FROM t tt WHERE t.str = tt.str);

(PL\SQL) How to parse a full name into first, middle and last names?

1st Method

CLEAR;
with t as (
select 'fabc iabc oabcd' fio from dual union all
select ' fabc        iabc oabcd' fio from dual union all
select 'fabc iabc     oabcd  ' fio from dual union ALL
select '   Baimendinov Salimjan     Danauly   ' fio
       from dual union ALL
select ' fabc     iabc     oabcd   ' fio from dual union all
select 'fabc iabc    ' fio from dual union ALL
select '  fabc   ' fio from dual union ALL
SELECT 'fabc  iabc  ' fio from dual
)
SELECT substr(fio, 1, instr(fio, ' ')) AS LastName,
       substr(fio, instr(fio, ' ') + 1,
       instr(substr(fio, instr(fio, ' ') + 1), ' ')) 
       FirstName,
       substr(substr(fio, instr(fio, ' ') + 1),
       instr(substr(fio, instr(fio, ' ') + 1), ' ') + 1,
       instr(substr(substr(fio, instr(fio, ' ') + 1),
       instr(substr(fio, instr(fio, ' ') + 1),
       ' ') + 1), ' ')) MiddleName
  FROM
  (
  SELECT
    TRIM(REPLACE(
         REPLACE(REPLACE(fio, ' ', '@!'), '!@'),
         '@!', ' '))
    || ' ' fio FROM t) t;

2nd Method

CLEAR;
with t as (
select 'fabc iabc oabcd' fio from dual union all
select ' fabc        iabc oabcd' fio from dual union all
select 'fabc iabc     oabcd  ' fio from dual union ALL
select '       fabc iabc     oabcd   ' fio from dual union ALL
select 'fabc     iabc     oabcd   ' fio from dual union ALL
select '   Baimendinov Salimjan     Danauly   ' fio
       from dual union ALL
select 'fabc iabc ' fio from dual union ALL
SELECT 'fabc  iabc' fio from dual)
SELECT
trim(substr(trim(fio),1,instr(trim(fio),' ',1,1))) 
      AS LastName,
CASE WHEN nvl(length(trim(substr(TRIM(substr(trim(fio),
         instr(trim(fio),' ',1,1),length(trim(fio)))),1,instr
         (TRIM(substr(trim(fio),instr(trim(fio),' ',1,1),
         length(trim(fio)))),' ',1,1)))),0) = 0
    THEN trim(substr(trim(fio),instr(trim(fio),' ',-1,1),
         length(trim(fio))))
    ELSE trim(substr(TRIM(substr(trim(fio),
         instr(trim(fio),' ',1,1),length(trim(fio)))),1,
         instr(TRIM(substr(trim(fio),instr(trim(fio),' ',1,1),
         length(trim(fio)))),' ',1,1))) END AS FirstName,
CASE WHEN nvl(length(trim(substr(TRIM(substr(trim(fio),
         instr(trim(fio),' ',1,1),length(trim(fio)))), 1,
         instr(TRIM(substr(trim(fio),instr(trim(fio),' ',1,1),
         length(trim(fio)))),' ',1,1)))),0) = 0
    THEN 'null'
     ELSE trim(substr(trim(fio),instr(trim(fio),' ',-1,1),
          length(trim(fio)))) END AS MiddleName FROM t;

ResultSet

LASTNAME	FIRSTNAME	MIDDLENAM
——–	———	———
fabc	iabc	oabcd
fabc	iabc	oabcd
fabc	iabc	oabcd
fabc	iabc	oabcd
fabc	iabc	oabcd
Baimendinov	Salimjan	Danauly
fabc	iabc	null
fabc	iabc	null

Dynamic SQL statement or an anonymous PL/SQL block

Some programs must build and process SQL statements where some information is not known in advance. A reporting application might build different SELECT statements for the various reports it generates, substituting new table and column names and ordering or grouping by different columns. Database management applications might issue statements such as CREATE, DROP, and GRANT that cannot be coded directly in a PL/SQL program. These statements are called dynamic SQL statements.

Dynamic SQL statements built as character strings built at run time. The strings contain the text of a SQL statement or PL/SQL block. They can also contain placeholders for bind arguments. Placeholder names are prefixed by a colon, and the names themselves do not matter. For example, PL/SQL makes no distinction between the following strings:
'DELETE FROM emp WHERE sal > :my_sal AND comm < :my_comm'
'DELETE FROM emp WHERE sal > :s AND comm < :c'
To process most dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. To process a multi-row query (SELECT statement), you use the OPEN-FOR, FETCH, and CLOSE statements.

The following PL/SQL block contains several examples of dynamic SQL: