Advanced Features Of Oracle Sql

D

Advanced Features of Oracle SQL

Copyright © 2004, Oracle. All rights reserved.

Objectives After completing this appendix, you should be able to: • Explain and create LOBs • Explain the need and benefits of PL/SQL • Identify the different types of PL/SQL blocks • Use regular expression support in SQL to search, match, and replace strings all in terms of regular expressions • Employ proactive tuning methodologies

D-2

Copyright © 2004, Oracle. All rights reserved.

Understanding Large Object Types Types: • CLOB: Character • NCLOB: Multibyte character • BLOB: Binary • BFILE: Binary file

D-3

Copyright © 2004, Oracle. All rights reserved.

Differentiating Between LOB, LONG, and LONG RAW Types

D-4

LOB

LONG and LONG RAW

Stores up to 128 TB of data

Stores up to 2 GB of data

A table can contain multiple LOB columns

A table can have only one LONG or LONG RAW column

LOBs can be accessed in random order

LONG and LONG RAW data can only be accessed sequentially

Copyright © 2004, Oracle. All rights reserved.

Using CLOBs and BLOBS Creating a table having a CLOB column: CREATE TABLE employee( emp_id INTEGER, emp_details CLOB);

Initializing a CLOB: INSERT INTO employee(emp_id,emp_details) VALUES(101,EMPTY_CLOB());

Inserting values into a CLOB: UPDATE employee SET emp_details = ‘Long sentence’ WHERE emp_id = 101;

D-5

Copyright © 2004, Oracle. All rights reserved.

Using BFILES Creating a table having a BFILE column: CREATE TABLE department( dep_id INTEGER, dep_details BFILE);

Creating a directory object: CREATE OR REPLACE DIRECTORY file_location AS ‘C:\dept\files’;

Inserting values into a BFILE: INSERT INTO department(dep_id,dep_details) VALUES(10, BFILENAME(‘file_location’,’details.doc’));

D-6

Copyright © 2004, Oracle. All rights reserved.

What Is PL/SQL? PL/SQL: • Stands for Procedural Language extension to SQL • Is Oracle Corporation’s standard data access language for relational databases • Seamlessly integrates procedural constructs with SQL

D-7

Copyright © 2004, Oracle. All rights reserved.

About PL/SQL PL/SQL: • Provides a block structure for executable units of code. Maintenance of code is made easier with such a well-defined structure. • Provides procedural constructs such as: – Variables, constants, and types – Control structures such as conditional statements and loops – Reusable program units that are written once and executed many times

D-8

Copyright © 2004, Oracle. All rights reserved.

PL/SQL Environment

PL/SQL engine PL/SQL block

procedural SQL

Procedural statement executor

SQL statement executor Oracle database server

D-9

Copyright © 2004, Oracle. All rights reserved.

Benefits of PL/SQL • •

Integration of procedural constructs with SQL Improved performance SQL 1 SQL 2 …

SQL IF...THEN SQL ELSE SQL END IF; SQL D-10

Copyright © 2004, Oracle. All rights reserved.

Benefits of PL/SQL • • • •

D-11

Modularized program development Integration with Oracle tools Portability Exception handling

Copyright © 2004, Oracle. All rights reserved.

Benefits of PL/SQL

D-12

Copyright © 2004, Oracle. All rights reserved.

PL/SQL Block Structure DECLARE (Optional) Variables, cursors, user-defined exceptions BEGIN (Mandatory) - SQL statements - PL/SQL statements EXCEPTION (Optional) Actions to perform when errors occur END; (Mandatory)

D-13

Copyright © 2004, Oracle. All rights reserved.

PL/SQL Block Structure

D-14

Copyright © 2004, Oracle. All rights reserved.

Block Types Anonymous Function

D-15

Procedure

[DECLARE]

PROCEDURE name IS

BEGIN --statements

BEGIN --statements

[EXCEPTION]

[EXCEPTION]

FUNCTION name RETURN datatype IS BEGIN --statements RETURN value; [EXCEPTION]

END;

END;

END;

Copyright © 2004, Oracle. All rights reserved.

Block Types

D-16

Copyright © 2004, Oracle. All rights reserved.

Program Constructs

D-17

Tools Constructs

Database Server Constructs

Anonymous blocks

Anonymous blocks

Application procedures or functions

Stored procedures or functions

Application packages

Stored packages

Application triggers

Database triggers

Object types

Object types Copyright © 2004, Oracle. All rights reserved.

Program Constructs

D-18

Copyright © 2004, Oracle. All rights reserved.

Creating an Anonymous Block Enter the anonymous block in the iSQL*Plus workspace:

D-19

Copyright © 2004, Oracle. All rights reserved.

Executing an Anonymous Block Click the Execute button to execute the anonymous block:

PL\SQL procedure successfully completed.

D-20

Copyright © 2004, Oracle. All rights reserved.

Test the Output of a PL/SQL Block •

Enable output in iSQL*Plus with the command SET SERVEROUTPUT ON.

•

Use a predefined Oracle package and its procedure: – DBMS_OUTPUT.PUT_LINE SET SERVEROUTPUT ON … DBMS_OUTPUT.PUT_LINE(' The First Name of the Employee is ' || f_name); …

D-21

Copyright © 2004, Oracle. All rights reserved.

Test the Output of a PL/SQL Block

D-22

Copyright © 2004, Oracle. All rights reserved.

Regular Expression Overview A multilingual regular expression support for SQL and PLSQL string types

ABC A method of describing both simple and complex patterns for searching and manipulating

D-23

Several new functions to support regular expressions

Copyright © 2004, Oracle. All rights reserved.

Meta Characters Symbol

Description

*

Matches zero or more occurrences

|

Alteration operator for specifying alternative matches

^/$

D-24

Matches the start-of-line/end-of-line

[]

Bracket expression for a matching list matching any one of the expressions represented in the list

{m}

Matches exactly m times

{m,n}

Matches at least m times but no more than n times

[: :]

Specifies a character class and matches any character in that class

\

Can have four different meanings: 1. Stand for itself. 2. Quote the next character. 3. Introduce an operator. 4. Do nothing.

+

Matches one or more occurrence

?

Matches zero or one occurrence

.

Matches any character in the supported character set, except NULL

()

Grouping expression, treated as a single subexpression

[==]

Specifies equivalence classes

\n

Back-reference expression

[..]

Specifies one collation element, such as a multicharacter element

Copyright © 2004, Oracle. All rights reserved.

Using Meta Characters Problem: Find 'abc' within a string: Solution: 'abc' Matches: abc Does not match: 'def'

1

Problem: To find 'a' followed by any character, followed by 'c' Meta Character: any character is defined by '.' Solution: 'a.c' 2 Matches: abc Matches: adc Matches: alc Matches: a&c Does not match: abb Problem: To find one or more occurrences of 'a' Meta Character: Use'+' sign to match one or more of the previous characters 3 Solution: 'a+' Matches: a Matches: aa Does not match: bbb D-25

Copyright © 2004, Oracle. All rights reserved.

Notes Only

D-26

Copyright © 2004, Oracle. All rights reserved.

Regular Expression Functions

Function Name

Description

REGEXP_LIKE

Similar to the LIKE operator, but performs regular expression matching instead of simple pattern matching

REGEXP_REPLACE Searches for a regular expression pattern and replaces it with a replacement string

D-27

REGEXP_INSTR

Searches for a given string for a regular expression pattern and returns the position where the match is found

REGEXP_SUBSTR

Searches for a regular expression pattern within a given string and returns the matched substring

Copyright © 2004, Oracle. All rights reserved.

The REGEXP Function Syntax

REGEXP_LIKE

(srcstr, pattern [,match_option])

REGEXP_INSTR

(srcstr, pattern [, position [, occurrence [, return_option [, match_option]]]])

REGEXP_SUBSTR (srcstr, pattern [, position [, occurrence [, match_option]]]) REGEXP_REPLACE(srcstr, pattern [,replacestr [, position [, occurrence [, match_option]]]])

D-28

Copyright © 2004, Oracle. All rights reserved.

Performing Basic Searches

SELECT first_name, last_name FROM employees WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$');

D-29

Copyright © 2004, Oracle. All rights reserved.

Checking the Presence of a Pattern

SELECT street_address, REGEXP_INSTR(street_address,'[^[:alpha:]]') FROM locations WHERE REGEXP_INSTR(street_address,'[^[:alpha:]]')> 1;

D-30

Copyright © 2004, Oracle. All rights reserved.

Example of Extracting Substrings

SELECT REGEXP_SUBSTR(street_address , ' [^ ]+ ') "Road" FROM locations;

…

D-31

Copyright © 2004, Oracle. All rights reserved.

Replacing Patterns

SELECT REGEXP_REPLACE( country_name, '(.)', '\1 ') "REGEXP_REPLACE" FROM countries;

…

D-32

Copyright © 2004, Oracle. All rights reserved.

Regular Expressions and Check Constraints ALTER TABLE emp8 ADD CONSTRAINT email_addr CHECK(REGEXP_LIKE(email,'@'))NOVALIDATE ;

1

INSERT INTO emp8 VALUES (500,'Christian','Patel', 2 'ChrisP2creme.com', 1234567890, '12-Jan-2004', 'HR_REP', 2000, null, 102, 40) ;

D-33

Copyright © 2004, Oracle. All rights reserved.

Proactive Tuning Methodology • • • • • • • •

D-34

Simplifying design Modeling data Optimizing table and index design Using views Writing efficient SQL Sharing cursors Using bind variables Using dynamic SQL

Copyright © 2004, Oracle. All rights reserved.

Simplifying Application Design • • • •

D-35

Simple tables Well-written SQL Indexing only as required Retrieving optimum information

Copyright © 2004, Oracle. All rights reserved.

Modeling Data • • • •

D-36

Accurately represent business practices Focus on most frequent and important business transactions Use modeling tools Normalize the data

Copyright © 2004, Oracle. All rights reserved.

Optimizing Table Design •

Compromise between flexibility and performance – Principally normalize – Selectively denormalize

•

Use Oracle performance features – – – –

•

D-37

Default values Check constraints Materialized views Clusters

Focus on business-critical tables

Copyright © 2004, Oracle. All rights reserved.

Optimizing Index Design •

Index keys – Primary key – Unique key – Foreign keys

• •

D-38

Index often-queried data Use SQL as a guide to index design.

Copyright © 2004, Oracle. All rights reserved.

D-39

Copyright © 2004, Oracle. All rights reserved.

Using Views • • •

D-40

Simplifies application design Is transparent to the end user Can cause suboptimal execution plans

Copyright © 2004, Oracle. All rights reserved.

Increasing the Efficiency of SQL Execution • • • •

D-41

Good database connectivity Using cursors Minimizing parsing Using bind variables

Copyright © 2004, Oracle. All rights reserved.

Bind Variable Peeking •

•

D-42

During the first invocation of a cursor, the query optimizer peeks at user-defined bind variable values. During subsequent invocations, no peeking takes place, and the cursor is shared.

Copyright © 2004, Oracle. All rights reserved.

Sharing Cursors • • •

D-43

Reduces parsing Dynamically adjusts memory Improves memory usage

Copyright © 2004, Oracle. All rights reserved.

Writing SQL to Share Cursors •

Create generic code using the following: – Stored procedures and packages – Database triggers

• •

Any other library routines and procedures Writing to format standards – – – – –

D-44

Case White space Comments Object references Bind variables

Copyright © 2004, Oracle. All rights reserved.

Dynamic SQL •

• •

Provides the ability to dynamically execute SQL statements whose complete text is unknown until execution time Can place dynamic SQL statements directly into PL/SQL blocks Consists of: – – – – – –

• D-45

DML statements Queries PL/SQL anonymous blocks DDL statements Transaction control statements Session control statements

Should be used cautiously Copyright © 2004, Oracle. All rights reserved.

Implementing the Application • • • • • • •

D-46

Use an appropriate development environment. Isolate software components. Check that the application is complete and self sufficient. Use programming languages for procedural logic. Cache frequently accessed relatively constant data. Ensure seamless integration of application components and scalability. Enforce referential integrity using foreign keys.

Copyright © 2004, Oracle. All rights reserved.

D-47

Copyright © 2004, Oracle. All rights reserved.

Summary In this appendix, you should have learned how to: • Explain and create LOBs • Explain the need and benefits of PL/SQL • Identify the different types of PL/SQL blocks • Use regular expression support in SQL to search, match, and replace strings all in terms of regular expressions • Employ proactive tuning methodologies

D-48

Copyright © 2004, Oracle. All rights reserved.