Download - Oracle 9i & 10g Enhancements
![Page 1: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/1.jpg)
1.
10g PL/SQL10g PL/SQL
• SQL Enhancements
• SQLPLUS Enhancements
• External Tables
• Timestamp Datatypes
• PL/SQL enhancements
![Page 2: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/2.jpg)
2.
Final Schedule For Oracle10g Workshop
DATE DAY Topics Covered Conducted By
20-Sep-06 1 10g SQL & PL/SQL – Hands on Harsh & Vibhuti
20-Sep-06 1 AS Forms 10g – Hands on (Part 1) Pranali
21-Sep-06 2 AS Forms 10g – Hands on (Part 2) Pranali
21-Sep-06 210g Architecture, Grid & RAC10g Backup, Recovery & Network
Vibhuti
22-Sep-06 3 10g Performance Tuning Pranali
22-Sep-06 3 Final Test, Quiz and Games Vibhuti, Harsh, Pranali
![Page 3: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/3.jpg)
3.
SQL EnhancementsSQL Enhancements
![Page 4: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/4.jpg)
4.
• Multi-Table Insert
• MERGE Statement
• GROUPBY Clause Enhancements
• CASE enhancements
• JOINS – new syntax
• Introduction To Analytical Functions
• MODEL Clause
• Regular Expression
![Page 5: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/5.jpg)
5.
Multi-Table InsertMulti-Table Insert
• Using this feature the INSERT … SELECT
statement can be used to insert rows into multiple tables as part of a singe DML statement
Types:• Unconditional Insert • Conditional Insert• Conditional First Insert• Pivoting Insert
![Page 6: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/6.jpg)
6.
Example:
INSERT ALL Multi table InsertWHEN SAL > 10000 THENINTO sal_hist VALUES ( EMPID ,
HIREDATE,SAL)WHEN MGR > 200 THENINTO mgr_hist VALUES (EMPID , MGR ,SAL )
SELECT empno EMPID , hire_date HIREDATE ,Sal SAL , mgr MGR
FROM emp_old WHERE empno > 20 ;
![Page 7: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/7.jpg)
7.
Current Scenario :
INSERT INTO revenue (person,month,revenue)SELECT person, 'Jan', rev_jan FROM revert UNION ALLSELECT person, 'Feb', rev_feb FROM revert UNION ALLSELECT person, ‘Apr‘ ,rev_mar FROM revert ;
In 9i/10g :Insert ALL into revenue values (person ,'JAN', rev_jan) into revenue values (person, 'FEB', rev_feb) into revenue values (person,‘ MAR', rev_mar)select person, rev_jan, rev_feb, rev_mar, rev_apr, rev_may,
rev_jun from revert;
![Page 8: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/8.jpg)
8.
Example 1:Unconditional INSERT
INSERT ALL
INTO hbg2 VALUES (emp_no,name )
INTO hbg1 VALUES (emp_no,name)
SELECT emp_no, name
FROM hbg
WHERE emp_no > 2;
![Page 9: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/9.jpg)
9.
Example 2: Conditional INSERT ALL
INSERT ALLWHEN emp_no > 4 THENINTO hbg2 VALUES (emp_no,name )WHEN emp_no <= 4 THENINTO hbg1 VALUES (emp_no,name)SELECT emp_no,nameFROM hbgWHERE emp_no > 2;
![Page 10: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/10.jpg)
10.
Example 3: Conditional INSERT FIRSTINSERT FIRSTWHEN name ='Harsh' THENINTO hbg1 VALUES (emp_no,name) WHEN emp_no = 2 THENINTO hbg2 VALUES (emp_no,name) WHEN emp_no >2 and emp_no < 4 THENINTO hbg3 VALUES (emp_no,name) ELSEINTO hbg4 VALUES (emp_no,name)SELECT emp_no, name FROM hbg;
![Page 11: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/11.jpg)
11.
Example 4: Conditional INSERT ALLINSERT ALLWHEN name ='Harsh' THENINTO hbg1 VALUES (emp_no,name) WHEN emp_no = 2 THENINTO hbg2 VALUES (emp_no,name) WHEN emp_no >0 and emp_no < 4 THENINTO hbg3 VALUES (emp_no,name) ELSEINTO hbg4 VALUES (emp_no,name)SELECT emp_no, name FROM hbg;
![Page 12: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/12.jpg)
12.
Example 5: Pivoting INSERT
INSERT ALL
INTO sales VALUES (emp_id,name,region1)
INTO sales VALUES (emp_id,name,region2)
INTO sales VALUES (emp_id,name,region3)
SELECT emp_id, name, region1,region2, region3
FROM sales_main;
![Page 13: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/13.jpg)
13.
• Specifying FIRST tells oracle to skip subsequent WHEN clauses after it finds one true for the row to be evaluated.
• We can also use an ELSE clause to tell oracle what to do if none of the WHEN clauses evaluates to true
![Page 14: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/14.jpg)
14.
• Example 6: Using Table Aliases
INSERT ALL WHEN ottl < 100000 THEN INTO small_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 100000 and ottl < 200000 THEN INTO medium_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 200000 THEN INTO large_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 290000 THEN INTO special_orders SELECT o.order_id oid, o.customer_id cid, o.order_total ottl, o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem FROM orders o, customers c WHERE o.customer_id = c.customer_id;
![Page 15: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/15.jpg)
15.
Uses
• Multi table inserts are useful in data warehouse scenario.
• Instead of using sqlldr to load data into multiple table , multi table inserts coupled with an external table can be used.
![Page 16: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/16.jpg)
16.
MERGE StatementMERGE Statement
• This specialized statement combines insert and update into a single operation. It is intended for data warehousing applications that perform particular patterns of inserts and updates.
• Previous approach for achieving the same was:- Insert, if failed on primary key then update- Update, if SQL%NOTFOUND then insert.
• Avoid Multiple update statements.• Now, a single SQL statement does the job.
![Page 17: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/17.jpg)
17.
Syntax :We can conditionally insert rows into table B from table A with help of single SQL statement.
MERGE INTO high_score_copy B Target table
USING high_score A - source (can be a table or view or subquerry)
ON ( A. name=B.name) condition to either update or insert
WHEN MATCHED THENUPDATE SET B.score=A. score
WHEN NOT MATCHED THEN INSERT VALUES (A.name,A.score);
![Page 18: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/18.jpg)
18.
Example 1:
Merge into hbg1
using hbg
on (hbg.emp_no = hbg1.emp_no)
when matched
then update
set hbg1.name = hbg.name
when not matched
then insert (hbg1.emp_no, hbg1.name) values (hbg.emp_no, hbg.name)
![Page 19: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/19.jpg)
19.
Example 2 :
Merge into hbg1
using hbg
on (hbg.emp_no = hbg1.emp_no)
when matched
then update
set hbg1.name = 'hbg'
when not matched
then insert (hbg1.emp_no, hbg1.name) values (hbg.emp_no, hbg.name)
![Page 20: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/20.jpg)
20.
Example 3 :Merge into hbg1using hbgon (hbg.EMP_NO = hbg1.EMP_NO) when matched then update set hbg1.name = hbg.namewhen not matched then insert (emp_no, name) values (hbg.emp_no,
hbg.name)
![Page 21: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/21.jpg)
21.
Example 4:
Merge into hbg1 h
using hbg g
on (h.EMP_NO = g.EMP_NO)
when matched
then update
set h.name = g.name
when not matched
then insert (emp_no, name) values (g.emp_no, g.name)
![Page 22: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/22.jpg)
22.
Enhancements in 10g :
• We can now specify BOTH in one SQL instead of update or (and) insert separate SQL statements
• Can also delete rows from the target table during update operation instead of writing a separate SQL statements
![Page 23: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/23.jpg)
23.
Advantages:
• Simpler to use than procedural code• Considerably faster than using procedural code• Can be used in data warehousing applications
![Page 24: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/24.jpg)
24.
GROUP BY Clause EnhancementsGROUP BY Clause Enhancements
• ROLLUP
Used to calculate sub-totals
• CUBE
Used to get cross-tabulation results
![Page 25: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/25.jpg)
25.
![Page 26: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/26.jpg)
26.
![Page 27: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/27.jpg)
27.
![Page 28: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/28.jpg)
28.
ROLLUP• Used to calculate sub-totals.
Example :
SELECT EMP_ID, NAME, SUM(REGION)
FROM sales
GROUP BY ROLLUP(emp_id,name);
This will display for group by from right to left.
So the grouping will be done first of name and
then of empid
![Page 29: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/29.jpg)
29.
![Page 30: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/30.jpg)
30.
![Page 31: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/31.jpg)
31.
• Grouping Sets : Simple extension to GROUP BY to facilitate multiple groupings
Example :
select DEPTNO,JOB,MGR,avg(sal)
from emp_old
group by grouping sets
((deptno,job,mgr), (deptno,mgr), (job,mgr));
![Page 32: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/32.jpg)
32.
![Page 33: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/33.jpg)
33.
GROUPING SETS• The example in the slide displays the
following:
• Total salary for every department (labeled 1)
• Total salary for every department, job ID, and manager (labeled 2)
• Grand total (labeled 3)
![Page 34: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/34.jpg)
34.
Advantages
• All the group by enhancements make only one pass over the base table
• Code readability is improved
![Page 35: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/35.jpg)
35.
CASE StatementCASE StatementFollowing are new CASE expression types
• Searched CASE expression• NULLIF• COALESCE
![Page 36: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/36.jpg)
36.
![Page 37: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/37.jpg)
37.
![Page 38: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/38.jpg)
38.
![Page 39: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/39.jpg)
39.
CASE• In a simple CASE expression, Oracle searches
for the first WHEN ... THEN pair for which expr is equal to comparison_expr and returns return_expr. If none of the WHEN ... THEN pairs meet this condition, and an ELSE clause exists, then Oracle returns else_expr. Otherwise, Oraclereturns null.
• You cannot specify the literal NULL for all the return_exprs and the else_expr
![Page 40: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/40.jpg)
40.
NULLIF• Syntax
• NULLIF (expr1, expr2)
• In the syntax:
• expr1 is the source value compared to expr2
• expr2 is the source value compared with expr1. (If it is not equal to expr1, expr1
• is returned.)
![Page 41: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/41.jpg)
41.
![Page 42: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/42.jpg)
42.
COALESCE• Syntax• COALESCE (expr1, expr2, ... exprn)• In the syntax:• expr1 returns this expression if it is not null• expr2 returns this expression if the first expression is
null and this expression is not• null• exprn returns this expression if the preceding
expressions are null
![Page 43: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/43.jpg)
43.
![Page 44: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/44.jpg)
44.
JoinsJoins
• Oracle 9i provides support for SQL:1999 syntaxNew keywords like :
– CROSS JOIN– NATURAL JOIN– USING CLAUSE– ON CLAUSE
• Full outer join is possible from 9i
![Page 45: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/45.jpg)
45.
![Page 46: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/46.jpg)
46.
![Page 47: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/47.jpg)
47.
![Page 48: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/48.jpg)
48.
![Page 49: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/49.jpg)
49.
• LEFT JOINSQL> SELECT e.ename,e.deptno,d.dname FROM emp_old e LEFT JOIN dept d ON ( e.deptno=d.deptno );
• RIGHT JOINSQL> SELECT e.ename,e.deptno,d.dname FROM emp_old e RIGHT JOIN dept d ON ( e.deptno=d.deptno );
• FULL JOINSQL> SELECT e.ename,e.deptno,d.dname
FROM emp_old e FULL JOIN dept dON ( e.deptno=d.deptno );
![Page 50: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/50.jpg)
50.
Introduction To Analytical FunctionsIntroduction To Analytical Functions
• Analytic functions compute an aggregate value based on a group of rows.
• They differ from aggregate functions in that they return multiple rows for each group.
![Page 51: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/51.jpg)
51.
Common Analytic FunctionsCommon Analytic Functions
• RANK• DENSE_RANK• ROW_NUMBER• FIRST_VALUE / LAST_VALUE• LEAD• LAG
![Page 52: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/52.jpg)
52.
RANKRANK
• RANK calculates the rank of a value in a group of values.
• Rows with equal values for the ranking criteria receive the same rank.
• The PARTITION keyword is used to define where the rank resets. The specific column that is ranked is determined by the ORDER BY clause. If no partition is specified, ranking is performed over the entire result set. RANK assigns a rank of 1 to the smallest value unless descending order is used .
![Page 53: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/53.jpg)
53.
Example 1:
Partition is optional and Order By is compulsory
SELECT emp_no, name salary,
RANK() OVER
( ORDER BY salary DESC)
AS r1
FROM test1 ;
![Page 54: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/54.jpg)
54.
Example 2:
SELECT emp_no, name, salary,
RANK() OVER
(PARTITION BY emp_no
ORDER BY salary DESC)
AS r1
FROM test1
![Page 55: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/55.jpg)
55.
Dense RankDense Rank• The difference between RANK and DENSE_RANK is
that DENSE_RANK leaves no gaps in ranking sequence when there are ties.
• That is, if you were ranking a competition using DENSE_RANK and had three people tie for second place, you would say that all three were in second place and that the next person came in third. The RANK function would also give three people in second place, but the next person would be in fifth place.
![Page 56: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/56.jpg)
56.
Advantage
• Can be used to obtain top N or bottom N ranking
(1) SELECT EMP_NO, NAME, salary,
RANK() OVER( ORDER BY emp_no) AS r2
FROM test1
(2) SELECT EMP_NO, NAME, salary,
DENSE_RANK() OVER( ORDER BY emp_no) AS r2
FROM test1
![Page 57: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/57.jpg)
57.
LEAD/LAG• LAG (value_expression [,offset] [,default]) OVER
([query_partition_clase] order_by_clause)
• LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clase] order_by_clause)
• value_expression - Can be a column or a built-in function, except for other analytic functions.
• offset - The number of rows preceding/following the current row, from which the data is to be retrieved. The default value is 1.
• default - The value returned if the offset is outside the scope of the window. The default value is NULL.
![Page 58: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/58.jpg)
58.
Examples(1) SELECT empno, ename, sal, LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev, sal - LAG(sal, 1, 0 ) OVER (ORDER BY sal) AS
sal_diff FROM emp;(2) SELECT empno, ename, sal, LEAD(sal, 1, 0) OVER (ORDER BY sal) AS
sal_next, LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS
sal_diff FROM emp;
![Page 59: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/59.jpg)
59.
FIRST_VALUE /LAST_VALUE
FIRST_VALUE / LAST_VALUE is an analytic function. It returns the first / last value in an ordered set of values.
If the first / last value in the set is null, then the function returns NULL unless you specify IGNORE NULLS.
If you specify IGNORE NULLS, then FIRST_VALUE / LAST_VALUE returns the first non-null value in the set, or NULL if all values are null.
![Page 60: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/60.jpg)
60.
ExamplesLowest value
(1) SELECT empno, ename ,sal, LAST_VALUE (ename) OVER (ORDER BY sal desc ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING ) AS lvFROM (SELECT * FROM emp WHERE empno <6 );
(2) SELECT empno, ename ,sal, FIRST_VALUE (ename) OVER (ORDER BY sal asc ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING ) AS lvFROM (SELECT * FROM emp WHERE empno <6 );Partition by can be used
![Page 61: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/61.jpg)
61.
Examples Highest value
(1) SELECT empno, ename ,sal, FIRST_VALUE(ename) OVER
(ORDER BY sal desc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS hv
FROM (SELECT * FROM emp WHERE empno <6 );
(2) SELECT empno, ename ,sal, LAST_VALUE(ename) OVER
(ORDER BY sal asc ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS hv
FROM (SELECT * FROM emp order by sal desc);
![Page 62: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/62.jpg)
62.
ROWNUMBER• ROW_NUMBER is an analytic function. It assigns a
unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1.
• Use of the function lets you implement top-N, bottom-N query.
![Page 63: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/63.jpg)
63.
Examples
SELECT department_id, last_name, employee_id, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id
FROM employees;
![Page 64: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/64.jpg)
64.
MODEL ClauseMODEL Clause
• SQL Model clause, a powerful new extension to the SQL SELECT statement
• MODEL clause, is a scalable and manageable way of computing business models in the database
• With the SQL Model clause, we can view query results in the form of multidimensional arrays & then apply formulas to calculate new array values.
• The formulas can be sophisticated interdependent calculations with inter-row and inter-array references.
![Page 65: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/65.jpg)
65.
• SYNTAX<prior clauses of SELECT statement>MODEL [main][reference models][PARTITION BY (<cols>)]DIMENSION BY (<cols>)MEASURES (<cols>)[IGNORE NAV] | [KEEP NAV][RULES[UPSERT | UPDATE][AUTOMATIC ORDER | SEQUENTIAL ORDER][ITERATE (n) [UNTIL <condition>] ]( <cell_assignment> = <expression> ... )
![Page 66: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/66.jpg)
66.
EXAMPLE 1SELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15) prod,
year, sales
FROM sales_view
WHERE country IN ('Italy','Japan')
MODEL RETURN UPDATED ROWS
PARTITION BY (country)
DIMENSION BY (prod, year)
MEASURES (sale sales)
RULES ( sales['Bounce', 2002] = sales['Bounce', 2001] +
sales['Bounce', 2000],
sales['Y Box', 2002] = sales['Y Box', 2001],
sales['2_Products', 2002] = sales['Bounce', 2002] + sales['Y Box', 2002])ORDER BY country, prod, year;
![Page 67: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/67.jpg)
67.
![Page 68: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/68.jpg)
68.
EXAMPLE 2 : FOR LOOPSELECT SUBSTR(country,1,20) country, SUBSTR(prod,1,15)
prod, year, salesFROM sales_viewWHERE country='Italy'MODEL RETURN UPDATED ROWSPARTITION BY (country)DIMENSION BY (prod, year)MEASURES (sale sales)RULES (sales[FOR prod IN (Prod1', ‘Prod2'),2005] = 1.3 * sales[CV(prod), 2001] )ORDER BY country, prod, year;
![Page 69: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/69.jpg)
69.
• Partition columns define the logical blocks of the result set in a way similar to the partitions of the analytical functions
• Dimension columns define the multi-dimensional array and are used to identify cells within a partition
• Rules in the MODEL clause are applied to each partition independent of other partitions.
• Partitions serve as a boundary point for parallelizing the model computation
![Page 70: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/70.jpg)
70.
Advantages Of MODEL Clause
• Performance:Model clause processing eliminates the need for many SQL join and union operations
• Scalability:The Model clause leverages Oracle parallelism, efficiently using all system resources made available to it.
• Manageability:Computations are centralized close to the data, so it reduces inconsistency & poor security
![Page 71: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/71.jpg)
71.
Regular ExpressionsRegular Expressions• Regular expressions specify patterns to search for in
string data• A regular expression can specify complex patterns of
character sequences• Regular expression support in SQL/PLSQL is
implemented with functions like :– REGEXP_LIKE, – REGEXP_REPLACE– REGEXP_INSTR
![Page 72: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/72.jpg)
72.
Example
SQL> SELECT first_name, last_name FROM employees WHERE REGEXP_LIKE (first_name,'^Ste(v|ph)en$');
Examine the string, looking for the firstsubstring bounded by commasSQL> SELECT REGEXP_SUBSTR('500 Oracle Parkway,
Redwood Shores, CA', ',[^,]+,') "REGEXPR_SUBSTR" FROM DUAL;
![Page 73: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/73.jpg)
73.
EXAMPLES
(1) SELECT zip FROM zipcode
WHERE REGEXP_LIKE(zip, '[^[:digit:]]');
Result : ab123
123xy
(2) SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234', '[[:digit:]]{5}$') AS rx_instr FROM dual;
Result : 45
(3) SELECT REGEXP_SUBSTR('first field, second field , third field', ', [^,]*,') FROM dual;
Result : , second field ,
![Page 74: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/74.jpg)
74.
EXAMPLES
(4) SELECT REPLACE('Joe Smith',' ', ' ') AS replace FROM dual;
Result : Joe Smith
(5) SELECT REGEXP_REPLACE('Joe Smith', '( ){2,}', ' ') AS RX_REPLACE FROM dual;
Result : Joe Smith (6) SELECT REGEXP_REPLACE('Ellen Hildi Smith',
'(.*) (.*) (.*)', '\3, \1 \2');
Result : Smith, Ellen Hildi
![Page 75: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/75.jpg)
75.
SQL PLUS EnhancementSQL PLUS Enhancement
• We have enabled displaying database name at SQL-Prompt
• But from SQL prompt, if one connects to different database, the new database name is not displayed
• This security flaw is addressed in 10gNCSSLV> conn username/password@HISTLV
Connected. HISTLV>
![Page 76: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/76.jpg)
76.
• Spool command is enhanced– Append or replace possible to existing spool file
• New DEFINE variables– _DATE default the _DATE variable is dynamic and
shows the current date
– _PRIVILEGE The _PRIVILEGE variable indicates the privilege level of the current connection
– _USER The _USER variable contains the user name connected
• set sqlprompt '&_user &_privilege &_date SQLPLUS> '
![Page 77: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/77.jpg)
77.
External TablesExternal Tables
![Page 78: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/78.jpg)
78.
What are External Tables?What are External Tables?
• These are like regular tables except that:- They are read-only tables- DML operations / Index creation not possible- Data is stored externally in flat-files
• Can be queried like a normal table• Supports parallelism• Stores only the mapping info between table
columns and flat-file contents
![Page 79: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/79.jpg)
79.
• Create the table ascreate table emp_load
( EMPNO NUMBER(4) ,DEPTNO NUMBER(2) )ORGANIZATION EXTERNAL ( type oracle_loader default directory data_dir access parameters ( badfile ‘emp.bad’
logfile ‘emp.log’fields terminated by ',' )
location ('emp.dat')
![Page 80: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/80.jpg)
80.
• Prior to Oracle 10g, external tables were read-only. However, in 10g, external tables can also be written to.
• Loading and unloading could be done in parallelism. (PARALLEL clause)
![Page 81: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/81.jpg)
81.
Benefits
• Generate flat files on the target system serially or in parallel
• Helps in cases where External table driver cannot handle complex ETL( extraction transformation loading)
• In this data is internally converted from oracle internal representation to native external representation.
![Page 82: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/82.jpg)
82.
Usage
• Used in TSAT to generate graphs based on trading-system data
• Can be used in DataWareHousing E(xtraction)T(ransformation)L(oading) process
• Can do away with TRADES Capture process in OPMS and INDEX
![Page 83: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/83.jpg)
83.
Date-Time supportDate-Time support
![Page 84: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/84.jpg)
84.
Introduction
Date-time data types – TIMESTAMP– TIMESTAMP WITH TIME ZONE– TIMESTAMP WITH LOCAL TIME ZONE
Interval data types– INTERVAL DAY TO SECOND– INTERVAL YEAR TO MONTH
![Page 85: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/85.jpg)
85.
TIMESTAMP Data TypesTIMESTAMP Data Types• The TIMESTAMP datatype is an extension of
the DATE datatype.
• It also stores fractional part of seconds.
• Specify the TIMESTAMP datatype as follows:TIMESTAMP
[(fractional_seconds_precision)]where fractional_seconds_precision is
number of digits in fractional part of SECOND field.
![Page 86: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/86.jpg)
86.
Interval Data Types
• Used to store the difference between datetime values.
• There are two classes of intervals :– Year-month intervals– Day-time intervals
• Interval date types are useful for flexible arithmetic operations .
![Page 87: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/87.jpg)
87.
We can add various date data types to each other
Example: Interval day-to-second data type
SQL> select interval '120' day(3) + interval '5:10' hour to minute + interval '40' second
2 from dual;INTERVAL'120'DAY(3)+INTERVAL'5:10'HOURTOMINUTE+INTERVAL'40'SECOND
----------------------------------------------------
+000000120 05:10:40.000000000
![Page 88: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/88.jpg)
88.
Usage
• Used in INDEX to find trade time lag between stratus and OLTP databases
• Possible usage in OPMS , INDEX and other OLTP applications, if sub-second data is required
• Usage also in ORDERS application
![Page 89: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/89.jpg)
89.
PL / SQL PL / SQL EnhancementsEnhancements
![Page 90: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/90.jpg)
90.
ASSOCIATIVE ARRAYSASSOCIATIVE ARRAYS• Associative arrays are sets of key-value pairs, where
each key is unique and is used to locate a corresponding value in the array.
• Starting with Oracle 9i , the key can be a string data type.
• Eg: • type population_type is TABLE of NUMBER
INDEX BY VARCHAR2(20);• state_population population_type;• state_population('TAMILNADU'):=20000;
![Page 91: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/91.jpg)
91.
DECLARE TYPE country_tab IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(5); t_country country_tab;
BEGIN -- Populate lookup t_country ('UK') := 'United Kingdom'; t_country ('US') := 'United States of America';
t_country ('FR') := 'France'; t_country ('DE') := 'Germany'; -- Find country name for ISO code "DE"
DBMS_OUTPUT.PUT_LINE('ISO code "DE" = ' || t_country ('DE'));
END;
![Page 92: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/92.jpg)
92.
• Uses And Advantages
• Like SQL table : retrieve values based on primary key– Appropriate for small lookup tables– It helps in fast lookup for an individual element
without knowing its position within the array and without having to loop through all the array elements
– Subscripts can be negative and non-sequential– PL/SQL automatically converts between host arrays
and associative arrays– Bulk-binding of host arrays i/o to associative arrays
using PL/SQL blocks
![Page 93: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/93.jpg)
93.
PL/SQL Native CompilationPL/SQL Native Compilation
• Speeds up PL/SQL procedures by compiling them into native code residing in shared libraries.
• Procedures are translated into C code, compiled and linked into the oracle process
• Bypasses the runtime interpretation giving faster runtime performance
• Debugging tools for PL/SQL do not handle procedures compiled for native execution.
![Page 94: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/94.jpg)
94.
Setup And ImplementationSetup And Implementation• Create a directory for storing the shared library and
set this path as PLSQL_NATIVE_LIBRARY_PATH• Alter Session set plsql_code_type=‘NATIVE ‘• Alter Session set plsql_compiler_flags = 'NATIVE';• Create or Replace or alter the Subprograms• Run any script which creates a set of Oracle Supplied
packages.• Create a DB using a preconfigured init file with
plsql_code_type = NATIVE.• SELECT object_name, param_name, param_value
FROM user_stored_settings WHERE param_name LIKE 'plsql%‘And object_name IN ('LOADER', 'DBRX_UTIL');
![Page 95: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/95.jpg)
95.
Table FunctionsTable Functions
• Used to produce a collection of rows that can be queried like a database table or assigned to a PL/SQL collection variable.
• Input is a collection of rows in form of VARRAY, PL/SQL table or REF CURSOR.
• Are invoked from SELECT statement’s FROM clause.
• The collection of rows returned can be pipelined.
• The execution of a table function can be parallelized.
![Page 96: Oracle 9i & 10g Enhancements](https://reader033.vdocuments.us/reader033/viewer/2022061105/543e54d7afaf9f7b0c8b526e/html5/thumbnails/96.jpg)
96.