See How to merge statement, See Exceptions in Pl/Sql
Rownum and rownumber()? select a.*, rownum rownum_out from ( select (row_number() over (order by object_id)) ord, rownum, o.object_name, o.object_id from dba_objects o order by object_name ) a where rownum < 10 --------------------- ord rownum object_name object_id rownum_out 42260 42260 /aaac4c04_MlibAddRIF 43235 1 43402 43402 /aaac4c04_MlibAddRIF 44377 2 28139 28139 /aaafddd5_PatternUnixDot 28644 3 28140 28140 /aaafddd5_PatternUnixDot 28645 4 14901 14901 /aab67636_BasicScrollBarUIProp 15406 5 14902 14902 /aab67636_BasicScrollBarUIProp 15407 6 41356 41356 /aacd7c44_TextSampleAnn 42331 7 41635 41635 /aacd7c44_TextSampleAnn 42610 8 24845 24845 /aac138a8_StyledEditorKitStyle 25350 9 |
Multi table insert
INSERT
when ( id is null )
insert into dept_exception
values ( dept_rec.dept_id, dept_rec.dept_name, 'No employees')
when ( comm > sal )
insert into emp_exception
values ( rec.id, rec.name, rec.sal, rec.comm, rec.dept_id, 'comm greater than sal')
when ( first_dept = 'Y')
insert into new_dept
values ( dept_id, dept_name)
when ( nvl(sal,0) >= nvl(comm,0) )
insert into new_emp
values ( id, name, dept_id, sal, comm)
SELECT dept.dept_id, dept_name, emp_id id, emp_name name, sal, comm
from dept , emp
where dept.dept_id = emp.dept_id(+);
INSERT into new_dept
SELECT dept_id, dept_name from dept
MINUS
SELECT dept_id, dept_name from new_dept;
|
Column Constraints Only Alphabet Chars ALTER TABLE CUSTOMERS ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[:alpha:]]'))NOVALIDATE; Only Numbers ALTER TABLE CUSTOMERS ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[:digit:]]'))NOVALIDATE ; |
Regexp
select REGEXP_REPLACE('123.356.9101','([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1) \2-\3') from dual
-----
(123) 356-9101
--show the position of the first non alpha character
select REGEXP_INSTR('iu3','[^[:alpha:]]') from dual
-----
3
|
Rollup - The "Group By" cube condition
Normal group by
select a.mm, a.idtipooperaz, sum(importo) from (
select to_char(data, 'yyyy/mm') mm, idtipooperaz, importo from vsl_banche_partitario_sottoc
) a
where mm in ('2002/01', '2002/02')
group by (mm, idtipooperaz)
order by mm
mm |idtipooperaz|sum(importo)
2002/01| 0| -11,88
2002/02| 100310| 64089,78
2002/02| 100231| 2145,61
Rollup
select a.mm, grouping(a.mm) mm_g, a.idtipooperaz, grouping(a.idtipooperaz) tipooperaz_g, sum(importo) from (
select to_char(data, 'yyyy/mm') mm, idtipooperaz, importo from vsl_banche_partitario_sottoc
) a
where mm in ('2002/01', '2002/02')
group by rollup(mm, idtipooperaz)
order by mm
mm |idtipooperaz|sum(importo)
2002/01| 0| -11,88
2002/01| | -11,88
2002/02| 100231| 2145,61
2002/02| 100310| 64089,78
2002/02| | 66235,39
| | 66223,51
Cube
select a.mm, grouping(a.mm) mm_g, a.idtipooperaz, grouping(a.idtipooperaz) tipooperaz_g, sum(importo) from (
select to_char(data, 'yyyy/mm') mm, idtipooperaz, importo from vsl_banche_partitario_sottoc
) a
where mm in ('2002/01', '2002/02')
group by cube(mm, idtipooperaz)
----------
...all combinations... |
Connect by
select rownum from dual connect by rownum < 5
--------------
1
2
3
4
Connect by prior - Start with - The "directory style" condition
CREATE TABLE test (
id int,
Menuitem varchar2(75),
parentid int);
INSERT into test values ('3','Administrative Tools\','1');
INSERT into test values ('4','Accessories\','1');
INSERT into test values ('5','System Tools\','4');
INSERT into test values ('6','Internet Explorer.lnk','1');
INSERT into test values ('7','Address Book.lnk','4');
INSERT into test values ('8','Calculator.lnk','4');
INSERT into test values ('9','Clock.lnk','4');
INSERT into test values ('11','Notepad.lnk','4');
INSERT into test values ('12','Paint.lnk','4');
INSERT into test values ('13','Synchronize.lnk','4');
INSERT into test values ('14','Disk Cleanup.lnk','5');
INSERT into test values ('15','Disk Defragmenter.lnk','5');
INSERT into test values ('16','Files and Settings Transfer Wizard.lnk','5');
INSERT into test values ('17','Scheduled Tasks.lnk','5');
INSERT into test values ('19','Certification Authority.lnk','3');
INSERT into test values ('20','Cluster Administrator.lnk','3');
INSERT into test values ('21','Component Services.lnk','3');
INSERT into test values ('22','Computer Management.lnk','3');
INSERT into test values ('23','Connection Manager Administration Kit.lnk','3');
INSERT into test values ('24','Data Sources (ODBC).lnk','3');
INSERT into test values ('25','DHCP.lnk','3');
INSERT into test values ('26','Distributed File System.lnk','3');
INSERT into test values ('27','DNS.lnk','3');
INSERT into test values ('28','Event Viewer.lnk','3');
INSERT into test values ('29','Group Policy Management.lnk','3');
INSERT into test values ('2','Startup\','1');
INSERT into test values ('10','Entertainment','4');
INSERT into test values ('18','Authorization Manager.lnk','3');
INSERT into test values ('1','C:\Documents and Settings\All Users\Start Menu\Programs\','-1');
SELECT
id, parentid, lpad(' ',2*(parentid), ' ')|| Menuitem
FROM test
CONNECT BY PRIOR id=parentid
START WITH id=1;
|
"With check option" on views Updates on the following view will be possible only for dept='finance' CREATE OR REPLACE VIEW finance_dept AS SELECT name, salary FROM emp WHERE dept = ‘finance’ WITH CHECK OPTION |
Savepoint
CREATE TABLE product
(pcode NUMBER(2),
pname VARCHAR2(10));
begin
INSERT INTO product VALUES(1, 'pen');
INSERT INTO product VALUES (2,'pencil');
SAVEPOINT a;
end;
select * from product
begin
UPDATE product SET pcode = 10 WHERE pcode = 1;
SAVEPOINT b;
end;
select * from product
ROLLBACK TO SAVEPOINT a
select * from product
--removes any savepoint
commit; --or rollback
--error as a commit or rollback removes any savepoint
ROLLBACK TO SAVEPOINT a
drop table product |
Join conditions (not so advanced...) create table test1 ( id number primary key, Mname varchar2(50) unique ) create table test2 ( id number primary key, id_test1 number references test1(id), Cname varchar2(50) ) begin insert into test1 values(1, 'pippo'); insert into test1 values(2, 'cacco'); --no childs insert into test2 values(1, 1, 'c1'); insert into test2 values(2, 1, 'c2'); insert into test2 values(3, null, 'no parent'); --no parent end; commit; Inner Join --Only childs with a parent select test1.id id1, test1.mname, test2.id id2, test2.id_test1, test2.cname from test2 INNER JOIN test1 ON test2.id_test1 = test1.id select test1.id id1, test1.mname, test2.id id2, test2.id_test1, test2.cname from test1 INNER JOIN test2 ON test2.id_test1 = test1.id Left outer join --All childs even if no parent select test1.id id1, test1.mname, test2.id id2, test2.id_test1, test2.cname from test2 left outer join test1 ON test2.id_test1 = test1.id --All parents even if no childs select test1.id id1, test1.mname, test2.id id2, test2.id_test1, test2.cname from test1 left outer JOIN test2 ON test2.id_test1 = test1.id Cross Join --All childs combined with some/all parents in all combinations for child select test1.id id1, test1.mname, test2.id id2, test2.id_test1, test2.cname from test2 cross join (select * from test1 where id=1)test1 --All parents combined with some/all childs in all combinations for parent select test1.id id1, test1.mname, test2.id id2, test2.id_test1, test2.cname from test1 cross join (select * from test2 where id=1)test2 |
Insert all
INSERT ALL
WHEN order_total < 10000
THEN INTO small_orders
WHEN order_total > 10000 AND order_total < 20000
THEN INTO medium_orders
WHEN order_total > 2000000 THEN
INTO large_orders
SELECT order_id, order_total, customer_id
FROM orders
--They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any
--other WHEN clause.
Example
drop table test
create table test (a integer)
begin
insert into test values(1);
insert into test values(2);
insert into test values(3);
end;
create table test1 (a integer)
create table test2 (b integer)
insert all
when a >= 1 then into test1
when a >= 3 then into test2
select a from test
select * from test1
----
1
2
3
select * from test2
----
3
|
Update from another table - Two tables update drop table a1 create table a1( pk number primary key, b varchar2(50), c varchar2(50) ) create table b1( pk number primary key, b varchar2(50), c varchar2(50) ) begin insert into a1 values(1, 'a1', 'a1'); insert into a1 values(2, 'a2', 'a2'); insert into b1 values(1, 'b1', 'b1'); insert into b1 values(2, 'b2', 'b2'); commit; end; UPDATE a1 SET(b, c)=(SELECT b, c FROM b1 WHERE b1.pk = a1.pk) select * from a1 |
Select from partitionselect a.* from aldo.mypart a partition(p1) order by 1 |
With (table generation), LAG (reference to previous row/s), LEAD (reference to next row/s)
WITH TEST AS
(SELECT 1 AS a
FROM DUAL
UNION ALL
SELECT 2 AS a
FROM DUAL
UNION ALL
SELECT 3 AS a
FROM DUAL
UNION ALL
SELECT 4 AS a
FROM DUAL),
test1 AS
(SELECT *
FROM TEST)
SELECT a, LAG (a, 2, -1) OVER (ORDER BY a) AS lag,
LEAD (a, 2, -1) OVER (ORDER BY a) AS lead
FROM test1 |
Case - when - then - else
WITH TEST AS
(SELECT 1 AS a
FROM DUAL
UNION ALL
SELECT 2 AS a
FROM DUAL
UNION ALL
SELECT 3 AS a
FROM DUAL
UNION ALL
SELECT 4 AS a
FROM DUAL)
select a, case a when 1 then 'SHIT' else null end
from test
------
1 1
2 null
3 null
4 null |
Thanks to Davide Gislon for some valuable contributions