New Features In Oracle 10g

1

Introduction to 10g New Features of SQL

Oracle 10g Report

NEW DATATYPES ADDED TO ORACLE 10g •BINARY_FLOAT •BINARY_DOUBLE •SDO_GEORASTER •SI_STILLIMAGE and six more related data types to it which are as follows: •SI_COLOR •SI_AVERAGECOLOR •SI_COLOURHISTOGRAM •SI_TEXTURE •SI_POSITIONALCOLOR •SI_FEATURELIST Oracle 10g SQL

MERGE STATEMENT MERGE INTO bonuses D USING (SELECT employee_id, salary, department_id FROM employees WHERE department_id = 80) S ON (D.employee_id = S.employee_id) WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01 DELETE WHERE (S.salary > 8000) WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus) VALUES (S.employee_id, S.salary*0.1) WHERE (S.salary <= 8000); Oracle 10g SQL

Improvement in Merge Statement 1)

The update and insert statement is optional.

2)

You can do a conditional UPDATE for a merge.

3)

You can perform a conditional INSERT using the WHERE clause.

4)

It has a new ON constant filter predicate to insert all rows to the target without joining source and target tables.

5)

An optional DELETE clause can remove rows while performing UPDATE. Oracle 10g SQL

MERGE statement with both INSERT and UPDATE clause separately 1)

2)

MERGE INTO employees e USING new_employees n On (e.department_id=n.department_id) WHEN MATCHED THEN UPDATE SET e.last_name=n.last_name e.salary=n.salary ; MERGE INTO employees e USING new_employees n On (e.department_id=n.department_id) WHEN NOT MATCHED THEN INSERT VALUES ( n.last_name,n..salary) ; Oracle 10g SQL

UNCONDITIONAL INSERTS

You can insert rows from the source table to the target table without joining the source and target MERGE INTO employees e UI|SING new_employees n ON(1=0) WHEN NOT MATCHED THEN INSERT VALUES(n.employeet_id,n.last_name,n.email) WHERE n.last_name=‘King’; Oracle 10g SQL

New DELETE Clause Here we merge the rows from NEW_EMPLOYEES into EMPLOYEES and delete any rows from EMPLOYEES whose JOB_ID=SA_REP MERGE INTO employees e USING new_employees n ON (e.employee_id=n.employee_id) WHEN MATCHED THEN UPDATE SET e.last_name=n.last_name, e.job_id=n.job_id DELETE WHERE (e.job_id=‘SA_REP’) WHEN NOT MATCHED THEN INSERT VALUES(n.employee_id,n.last_name,n.job_id) Oracle 10g SQL

Working with Regular Expressions Functions There are four functions that support regular expressions which are as follows:1) REGEXP_LIKE 2) REGEXP_INSTR 3) REGEXP_REPLACE 4) REGEXP_SUBSTR Oracle 10g SQL

REGEXP_LIKE Find the employees whose first name starts with a vowel, whose second character is an L, and whose first name is at least six character long Select first_name, last_name from EMPLOYEES WHERE REGEXP_LIKE(first_name, ‘^[aeiou] l ’, ‘i’) and REGEXP_LIKE(first_name ‘[ [ :alpha: ]] {6,} ’)

Oracle 10g SQL

REGEXP_INSTR The Syntax of the following function is as follows: REGEXP_INSTR(source_string,pattern[,position[,occur rence[,return_option [,match_parameter] ] ] ] ) Show the position of the second occurrence of a vowel in the first name of employees , with caseinsensitive searching from the first character. Select first_name, REGEXP_INSTR (first_name, ‘ [aeiou] ’ ,1,2,0,’i’ ) REGINSTR from EMPLOYEES WHER last_name like ‘S%’ Oracle 10g SQL

Case and Accent- Insensitive Queries The following SQL clauses support the NLS_SORT setting: 1) WHERE 2) ORDER BY 3) START WITH 4) HAVING 5) IN and NOT IN 6) BETWEEN 7) CASE…… WHEN Oracle 10g SQL

Quote Operator By using the quote operator we can use the ‘ ! And ! ‘ instead of using the single quotation marks for the string literals. The syntax is as follows:-

SELECT q ‘ ! Don’t look at john’s bike ! Wow!’ string From DUAL;

Oracle 10g SQL

REGEXP_REPLACE It extends the functionality of the replace function. The syntax is as follows:REGEXP_REPLACE (Source_string,pattern[,position [ ,occurrence [,match_parameter] ] ] ) SELECT REGEXP_REPLACE(‘ANDREW, HUDSON’, ’ (.+ ) ,( .* ) ’, ’ \2 \1’ ) FIRST from DUAL; SELECT REGEXP_REPLACE(‘ANDREW, HUDSON’, ’ ( .+ ) ,( .* ) ’, ’ \2 \1’ ) SECOND from DUAL Oracle 10g SQL

REGEXP_SUBSTR This function extends the functionality of the substr function and is quite similar to REGEXP_REPLACE. The syntax is as follows: REGEXP_SUBSTR(source_string ,pattern [,position {,occurrence [,match_parameter ] ] ] ) SELECT REGEXP_SUBSTR(‘[email protected]’, ’@.* ’ ) E-MAIL from DUAL;

Oracle 10g SQL

Some More Character Function

REPLACE FUNCTION The following example replaces occurrences of J with BL: SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL; Changes -------------BLACK and BLUE Oracle 10g SQL