JumpStart PL/SQL - PowerPoint PPT Presentation

About This Presentation

Title:

JumpStart PL/SQL

Description:

PL/SQL is an extension to SQL with design features of programming languages. Data manipulation and query statements of ... Enclosed in single quotation marks. ... – PowerPoint PPT presentation

Number of Views:466

Avg rating:3.0/5.0

Slides: 50

Provided by: Wong7

Category:

more less

Transcript and Presenter's Notes

Title: JumpStart PL/SQL

1
JumpStart PL/SQL

Wang Hao

2
Outline

PL/SQL Programming
SQLPlus and SQL commands

3
PL/SQL Programming

PL/SQL is an extension to SQL with design
features of programming languages.
Data manipulation and query statements of SQL are
included within procedural units of code.

4
Benefits of PL/SQL

Integration of applications, shared library and
Oracle server.
Improve performance.

application
Other DBMSs
SQL
IF THEN SQL ELSE SQL END IF
Oracle with PL/SQL
application
5
PL/SQL Block Structure

DECLARE Optional
Variables, cursors, uer-define exceptions
BEGIN Mandatory
SQL statements
PL/SQL statements
EXCEPTION Optional
Actions to perform when errors occur
END - Mandatory

6
PL/SQL Block Structure(1)

DECLARE
v_variable VARCHAR2(5)
BEGIN
SELECT column_name
INTO v_variable
FROM table_name
EXCEPTION
WHEN exception_name THEN
END

7
Block Types

Procedure
PROCEDURE name IS
BEGIN
-- statements
EXCETPION
END

Function FUNCTION name RETURN datatype
IS BEGIN -- statements RETURN
value EXCETPION END
DECLARE BEGIN -- statements EXCETPION E
ND
8
Program Constructs

Anonymous Block
Unnamed PL/SQL block that is embedded within an
application or is issued interactively
Stored procedure or function
Named PL/SQL block stored in Oracle Server that
can accept parameters and be invoked repeatedly
by name.
Package
Named PL/SQL module that groups related
procedures, functions and identifiers. Stored in
Oracle server or Oracle Developer components
for example, Forms.

9
Handling Variables in PL/SQL

Declare and initialize variables in the
declaration section.
Assign new values to variables in the executable
section.
Pass values into PL/SQL blocks through
parameters.
View results through output variables.

10
Types of PL/SQL Variables

Scalar
BOOLEAN, DATE, NUMBER, VARCHAR2
Composite
Such as records.
Reference
Pointers
LOB (large objects)
Used to hold large objects such as images.
Non-PL/SQL variables bind and host variables.

11
Declaring PL/SQL Variables

Examples
Declare
v_hiredate DATE
v_deptno NUMBER(2) NOT NULL 10
v_location VARCHAR(13) New York
v_tax_rate NUMBER(3,2) 3.0
c_comm CONSTANT NUMBER 1400
Guidelines
Follow naming conventions.
Initialize variables designated as NOT NULL.
Declare at most one identifier per line.

12
Base Scalar Datatypes

VARCHAR2(maximum_length)
No default size max 32767 bytes
NUMBER(precision, scale)
For fixed and floating numbers.
CHAR(maximum_length)
For fixed-length character string. Default to 1.
BOOLEAN
TURE, FALSE, or NULL
BINARY_INTEGER
Base type of integers between -2,147,483,647 and
2,147,483,647.
PLS_INTEGER
Same range as BINARY_INTEGER but requires less
storage and are faster than NUMBER and
BINARY_INTEGER values.

13
Declaring Variables with the TYPE attribute

Declare a variable according to
A database column definition
Another previously declared variables.
Prefix TYPE
Examples
v_ename emp.enameTYPE
v_balance NUMBER(7,2)
v_min_balance v_balanceTYPE 10

14
PL/SQL Block Syntax and Guidelines

Statements can continue over several lines.
Identifiers
Can contain up to 30 characters.
Must begin with alphabetic character.
Literals
Enclosed in single quotation marks.
A PL/SQL block is terminated by a slash / on a
line by itself.
Commenting code
Single-line comments with two dashes (--).
Place multi-line comments between / and /

15
Operators

Arithmetic
, -, , / , ,
Comparison
, lt, gt, gt, lt, !, IS NULL, LIKE, BETWEEN, IN
Assignment
Statement terminator
String concatenation
Logical
NOTG, AND , OR

16
SQL Statements in PL/SQL

Extract a row of data from the database by using
the SELECT command. Only a single set of values
can be returned.
Make changes to rows in the database by using DML
commands. (INSERT, UPDATE, DELETE).
Control a transaction with the COMMIT, ROLLBACK
or SAVEPOINT command.
Determine DML outcome with implicit cursors.
Does not support DDL (CREATE TABLE, ALTER TABLE,
or DROP TABLE) and DCL (GRANT, REVOKE)

17
SELECT Statements in PL/SQL

Retrieve data from the database with SELECT.
Example
DECLARE
v_dept_no NUMBER(2)
v_loc VARCHAR2(15)
BEGIN
SELECT deptno, loc
INTO v_deptno, v_loc
FROM dept
WHERE dnameSALES
END

18
Manipulating Data Using PL/SQL

INSERT
BEGIN
INSERT INTO emp(empno, ename, job, deptno)
VALUES (empno_sequence.NEXTVAL, Jack, CTO,
10)
END
UPDATE
BEGIN
UPDATE emp
SET sal sal 10
WHERE job ANALYST
END

19
Manipulating Data Using PL/SQL

DELETE
BEGIN
DELETE FROM emp
WHERE name JACK
END

20
Writing Control Structures

Conditional IF statements.
IF v_dept_no 10 THEN
v_bonus 5000
ELSIF v_dept_no20 THEN
v_bonus 7500
ELSE
v_bonus 2000
END IF

21
Writing Control Structures

Iterative control loop statements
Basic loop
FOR loop
WHIE loop
Basic loop
BEGIN
LOOP
INSERT INTO item(ordid, itemid)
VALUES (610, v_counter)
v_counter counter 1
EXIT WHEN v_counter gt 10
END LOOP
COMMIT
END

22
Writing Control Structures

FOR loop
BEGIN
FOR i IN 1..100 LOOP
END LOOP
END
WHILE loop
BEGIN
WHILE i lt 100 LOOP
END LOOP
END
Nested loop is allowed.

23
SQL Cursor

A cursor is a private SQL work area. Each SQL
statement executed by the Oracle Server has an
individual cursor associated with it.
There are two types of cursors
Implicit cursors
Explicit cursors
The Oracle Server uses implicit cursors to parse
and execute your SQL statements.
Explicit cursors are explicitly declared by the
programmer.

24
SQL Cursor Attributes

Using SQL cursor attributes, you can test the
outcome of your SQL statements.

SQLROWCOUNT Number of rows affected by the most recent SQL statement. (an integer value).
SQLFOUND Boolean attribute that evaluates to TRUE if the most recent SQL statement affects one or more rows.
SQLNOTFOUND Boolean attribute that evaluates to TRUE if the most recent SQL statement does not affect any row.
SQLISOPEN Always evaluates to FALSE because PL/SQL closes implicit cursors immediately after they are executed.
25
Writing Explicit Cursors

Use explicit cursors to individually process each
row returned by a multi-row SELECT statement.

Active Set
7369 SMITH CLERK
7430 JONES MANAGER
7788 SCOTT ANALYST
7789 JACK CTO
Cursor
Current Row
26
Writing Explicit Cursors Example

DECLARE
v_empno emp.empnoTYPE
v_ename emp.enameTYPE
CURSOR emp_cursor IS
SELECT empno, ename
FROM emp
BEGIN
OPEN emp_cursor
LOOP
FETCH emp_cursor INTO v_empno, v_ename
EXIT WHEN emp_cursorNOTFOUND
END LOOP
CLOSE emp_cursor

27
Cursors with Parameters

Example pass the department number and job title
to the WHERE clause.
DECALRE
CURSOR emp_cursor
(p_deptno NUMBER, p_job VARCHAR2) IS
SELECT empno, ename
FROM emp
WHERE deptno v_deptno
AND job v_job
BEGIN
OPEN emp_cursor(10, CLERK)

28
Cursors with Subqueries

DECALRE
CURSOR my_cursor IS
SELECT t1.deptno, t1.dname, t2.STAFF
FROM dept t1, (SELECT deptno,
count() STAFF
FROM emp
GROUP BY deptno) t2
WHERE t1.deptno t2.deptno
AND t2.STAFF gt 5

29
Using Arrays

Item type VARRAY (short for Variable-size
arrays).
A varray can have only one dimension and must be
indexed by integers.
A varray has a maximum size.
Example
DECALRE
TYPE list IS VARRAY(25) of NUMBER(3)
v1 v_list
BEGIN
FOR i IN 1..25
v1(i) 1
END LOOP
END

30
Subprograms

Subprograms are named PL/SQL blocks that can take
parameters and be invoked. PL/SQL has two types
of subprograms called procedures and functions.
Example a procedure named debit_account, which
debits a bank account

PROCEDURE debit_account (acct_id INTEGER, amount
REAL) IS
old_balance REAL
new_balance REAL
overdrawn EXCEPTION
BEGIN
SELECT bal INTO old_balance FROM accts
WHERE acct_no acct_id
new_balance old_balance - amount
IF new_balance lt 0 THEN
RAISE overdrawn
ELSE
UPDATE accts SET bal new_balance
WHERE acct_no acct_id
END IF
EXCEPTION
WHEN overdrawn THEN
...
END debit_account

32
Writing Functions

Consider the function sal_ok, which determines if
a salary is out of range
FUNCTION sal_ok (salary REAL, title VARCHAR2)
RETURN BOOLEAN IS
min_sal REAL
max_sal REAL
BEGIN
SELECT losal, hisal INTO min_sal, max_sal FROM
sals
WHERE job title
RETURN (salary gt min_sal) AND (salary lt
max_sal)
END sal_ok

33
Parameter Modes

IN mode (the default)
passes values to a subprogram.
actual parameter is passed by reference (a
pointer to the value is passed in)
OUT mode (must be specified)
returns values to the caller.
actual parameter is passed by value (a copy of
the value is passed out) unless NOCOPY is
specified
IN OUT mode (must be specified)
passes initial values to a subprogram and returns
updated values to the caller.
actual parameter is passed by value.

34
Declaring Subprograms

You can declare subprograms in any PL/SQL block,
subprogram, or package. But, you must declare
subprograms at the end of a declarative section
after all other program items.
PL/SQL requires that you declare an identifier
before using it. Therefore, you must declare a
subprogram before calling it.

35
Packaging Subprograms

You can group logically related subprograms in a
packages, which is stored in the database.
Packaged subprograms can be shared by many
applications, while hiding implementation
details.
Package spec declares the subprograms, and
Package body implements the subprograms.
Example

Creating Package Spec
CREATE OR REPLACE PACKAGE emp_actions AS
PROCEDURE hire_employee (emp_id INTEGER, name
VARCHAR2, ...)
PROCEDURE fire_employee (emp_id INTEGER)
PROCEDURE raise_salary (emp_id INTEGER, amount
REAL)
...
END emp_actions
Creating Package Body

CREATE OR REPALCE PACKAGE BODY emp_actions AS
PROCEDURE hire_employee (emp_id INTEGER, name
VARCHAR2, ...) IS
BEGIN
...
INSERT INTO emp VALUES (emp_id, name, ...)
END hire_employee
PROCEDURE fire_employee (emp_id INTEGER) IS
BEGIN
DELETE FROM emp WHERE empno emp_id
END fire_employee
PROCEDURE raise_salary (emp_id INTEGER, amount
REAL) IS
BEGIN
UPDATE emp SET sal sal amount WHERE
empno emp_id
END raise_salary
...
END emp_actions

38
Packaging Subprograms

You can define subprograms in a package body
without declaring their specs in the package
spec. However, such subprograms can be called
only from inside the package.
Guidelines
Put package spec and body in separate files.
Compile package spec first before compiling
package body.

39
Referencing Package Contents

To reference the types, items, subprograms, and
call specs declared within a package spec, use
dot notation, as follows
package_name.type_name
package_name.item_name
package_name.subprogram_name
package_name.call_spec_name
Example
SQLgt CALL emp_actions.hire_employee('TATE',
'CLERK', ...)

40
Oracle Packages DBMS_OUTPUT

Package DBMS_OUTPUT enables you to display output
from PL/SQL blocks and subprograms, which makes
it easier to test and debug them.
You display the information by calling the
procedure get_line or by setting SERVEROUTPUT ON
in SQLPlus.
For example, suppose you create the following
stored procedure

CREATE PROCEDURE calc_payroll (payroll OUT
NUMBER) AS
CURSOR c1 IS SELECT sal, comm FROM emp
BEGIN
payroll 0
FOR c1rec IN c1 LOOP
c1rec.comm NVL(c1rec.comm, 0)
payroll payroll c1rec.sal
c1rec.comm
END LOOP
/ Display debug info. /
DBMS_OUTPUT.PUT_LINE('Value of payroll '
TO_CHAR(payroll))
END
In SQLPlus
SQLgt SET SERVEROUTPUT ON
SQLgt VARIABLE num NUMBER
SQLgt CALL calc_payroll(num)
Value of payroll 31225

42
Oracle Packages UTL_FILE

Package UTL_FILE allows your PL/SQL programs to
read and write operating system (OS) text files.
It provides a restricted version of standard OS
stream file I/O, including open, put, get, and
close operations.

43
UTL_FILE Functions

UTL_FILE.FOPEN (
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2)
RETURN UTL_FILE.FILE_TYPE
Open_mode 'r' , 'w, 'a
The maximum line size is the default to 1023.
To read a line of max. length gt 1023, use
UTL_FILE.FOPEN (
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER)
RETURN file_type

44
UTL_FILE Functions

UTL_FILE.IS_OPEN (file IN FILE_TYPE)
RETURN BOOLEAN
UTL_FILE.FCLOSE (file IN OUT FILE_TYPE)
UTL_FILE.GET_LINE (
file IN FILE_TYPE,
buffer OUT VARCHAR2)
Text is read up to but not including the line
terminator, or up to the end of the file.
If the line does not fit in the buffer, then a
VALUE_ERROR exception is raised. If no text was
read due to "end of file," then the NO_DATA_FOUND
exception is raised.
The maximum size of an input record is 1023
bytes.

BEGIN
v_fid utl_file.fopen('e\log','h.log','r')
BEGIN
LOOP
utl_file.get_line(v_fid,in_row)
IF substr(in_row,1,6)'Table' AND
substr(in_row,-1,1) '' THEN
h_table_name rtrim(substr(in_row,7,30),'')
ELSEIF substr(in_row,-7,7) 'loaded.' THEN
h_no_rows to_number(substr(in_row,3,instr
(in_row,'R')-4))
ENDIF
END LOOP
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL
utl_file.fclose(v_fid)
END

46
Useful Links

http//oraweb.comp.nus.edu.sg/RDBMS/appdev.816/a77
069/toc.htm PL/SQL User's Guide and Reference
http//www.elementkjournals.com/dbm/0005/dbm0053.h
tm Using the UTL_FILE package

47
Using SQLPlus