PL/SQL

浏览数：100 / 时间：2015年06月12日

Procedural Language for SQL

query a database and manipulate data

understand the goals of the PL/SQL cursus

explain the need for PL/SQL and the benefits

identify different types of PL/SQL blocks

use the different tools used during this cursus

Oracle Certifications

SQL Certified Expert (Introduction to SQL/Program with PL/SQL)

OCA (Introduction to SQL/Oracle Database Fundamental)

OCP (Oracle Database Fundamentals/Oracle on-Hand Course)

OCM (Oracle Database Certified Master Exam)

Reviews about SQL 10g

Introduction to SQL

DML(Data Manipulation Language) (select insert update delete merge)

DDL(Data Definition Language) (create alter drop rename truncate comment)

DCL(Data Control Language) (grant revoke)

TCL(Transaction Control) (commit rollback savepoint)

Retrieving data

select column, group_function from table where condition group by group_by_expression having group_condition order by column;

eg : select last_name, department_id from employees where department_id in (20,50) order by last_name ASC; (display the last name and department number of all employees in departments 20 or 50 in ascending alphabetical order by name)

selsct last_name, hire_date from employees where hire_date LIKE ‘%594‘; (displays the last name and hire date for all employees who were hired in 1994)

select last_name, salary, commission_pct from employees where commission_pct is not null order by salary DESC, commission_pct DESC;(display the last name, salary and commission for all employees who earn commissions. Sort data in descending order of salary and commissions)

select manager_id, MIN(salary) from employees where manager_id is not null group by manager_id having MIN(salary) > 6000 order by MIN(salary) desc; (to display the manager number and the salary of the lowest-paid employee for that manager, exclude anyone whose manager is not null, exclude any groups where the minimum salary is $6000 or less, sort the output in descending order of salary)

Manipulating data

insert into table column values value : add new rows to a table by using the insert statement

modify existing rows with the update statement : update table set column = value where condition;

delete from table where condition;

Creating objects

table : basic unit of storage; composed of rows

view : logically represents subsets of data from one or more table

sequense : generates numeric values

index : improves the performance of some queries

synonym : gives alternative names to objects

varchar2(size) : variable-length character data

char(size) : fixed-length character data

number(p,s) : variable-length numeric data

data : date and time values

long : variable-length character data (up to 2 GB)

clob : character data (up to 4 GB)

raw and long raw : raw binary data

blob : binary data(up to 4 GB)

bfile : binary data stored in an external file(up to 4 GB)

rowid : a base-64 number system representing the unique address of a row in its table

create table table column datatype;

PL/SQL curriculum

Communicating with a RDBMS using SQL

introduction to PL/SQL

provides a block structure for executable unit 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

benefits of PL/SQL

modularize program development

integration with Oracle tools

Portability

Exception handling

Different types of PL/SQL block

declare (optional) : variables, cursors, user-defined exceptions

begin (Mandatory) : SQL statements / PL/SQL statements

exception (optional) : actions to perform when errors occur

end (mandatory)

block types : anonymous / procedure / function

enable output in iSQL*PLUS with the command :

SET SERVEROUTPUT ON

use a predifined Oracle package and its procedure : DBMS_OUTPUT.PUT_LINE

EG : set serveroutput on

...

DBMS_OUTPUT.PUT_LINE(‘ The First Name of the Employee is ‘ || f_name);

Declaring variables in PL/SQL

Declare PL/SQL variables

Manipulate different types of variables

Make difference between variables and constants

Compare SQL and PL/SQL variables

PL/SQL variables

Presentation of PL/SQL Variables

Variables can be used for : temporary storage of data / manipulation of stored values / reusability

Identifiers are used for : naming a variable / providing a convention for variable names : must start with a letter/can include letters or numbers/can include special characters such as dollar sign, underscore, and pound sign/must limit the length to 30 characters/must not be reserved words

Variables are : declared and initialized in the declarative section / used and assigned new values in the executable section / passed as parameters to PL/SQL subprograms / used to hold the output of a PL/SQL subprogram

Declaring and initializing PL/SQL variables

syntax : identifier [CONSTRANT] datatype [not null] [ : | DEFAULT expr];

eg : declare emp_hiredate date; emp_deptno number(2) not null := 10; location varchar2(13) := ‘Atlanta‘; c_comm constraint number := 1400;

declaring and initializing PL/SQL variables :

SET SERVEROUTPUT ON

DECLARE

Myname VARCHAR2(20);

BEGIN

DBMS_OUTPUT.PUT_LINE(‘My name is : ‘|| Myname);

Myname := ‘John‘;

DBMS_OUTPUT.PUT_LINE(‘My name is : ‘|| Myname);

END;

SET SERVEROUTPUT ON

DECLARE

Myname VARCHAR2(20) := ‘John‘;

BEGIN

Myname := ‘Steven‘;

DBMS_OUTPUT.PUT_LINE(‘My name is : ‘|| Myname);

END;

Delimiters in string literals :

SET SERVEROUTPUT ON

DECLARE

event VARCHAR2(15);

BEGIN

event := q‘ !Father‘s day!‘;

DBMS_OUTPUT.PUT_LINE(‘3rd Sunday in June is : ‘||event);

event := q‘ [Mother‘s day]‘;

DBMS_OUTPUT.PUT_LINE(‘2nd Sunday in May is : ‘||event);

END;

Different types of PL/ SQL Variables

PL/SQL variables : Scalar (hold a single value) Composite Reference Large objects(LOB)

Non-PL/SQL variables : Bind variables

Guidelines for declaring and initializing PL/SQL variables :

follow naming conventions

use meaningful names for variables

initialize variables designated as NOT NULL and CONSTRAINT.

initialize variables with the assignment operator (:=) or the DEFAULT keyword (eg : Myname VARCHAR(20) := ‘John‘; Myname VARCHAR2(20) DEFAULT ‘John‘;)

declare on identifier per line for readability and code maintenance.

avoid using column names as identifiers

DECLARE

employee_id NUMBER(6);

BEGIN

SELECT employee_id

INTO employee_id

FROM employees

WHERE last_name = ‘Kochhar‘;

END;

with this syntax, only one row is selected at a time.

Base Scalar Data Types

CHAR [(fixed_length)]

VARCHAR2 (maximum_length)

LONG

LONG RAW

NUMBER [(precision, scale)]

BINARY_INTEGER

PLS_INTEGER

BOOLEAN

BINARY_FLOAT

BINARY_DOUBLE

DATE

TIMESTAMP

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE

INTERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

Declaring Boolean Variables

only the values TRUE, FALSE, and NULL can be assigned to a Boolean variable.

conditional expressions use logical operators AND, OR, and unary operator NOT to check the variable values.

the variables always yield TRUE, FALSE, or NULL

arithmetic, character, and date expressons can be used to return a Boolean value.

BINARY_FLOAT and BINARY_DOUBLE

representing floating point numbers in IEEE(Institute of Electrical and Electrnics Engineers) 754 format

offer better interoperability and operational speed

store values beyond the values that the data type NUMBER can store

offer benefits of closed arithmetic operations and transparent rounding

eg :

DECLARE

emp_job VARCHAR2(9);

count_loop BINARY_INTEGER := 0;

dept_total_sal NUMBER(9,2) := 0;

orderdate DATE := SYSDATE +7;

c_tax-rate CONSTRAINT NUMBER (3,2) := 8.25;

valid BOOLEAN NOT NULL := TRUE;

...

Composite Data Types

TABLE : to reference and manipulate collections of data as a whole object

RECORD : to treat related but dissimilar data as a logical unit

NESTED TABLE

VARRAY

LOB Data Type Variables(to store blocks of unstructured data (such as text, graphic images, video clips, and sound wave forms) up to 4 gigabytes in size)

BLOB(binary large object) : to store large unstructured or sturctured binary objects in the database

CLOB(character large object) : to store large blocks of character data in the database

BFILE(binary file) : to store large binary files outside the database (only a pointer to the BFILE is stored in the database)

NCLOB(national language character large object) : to store large blocks of single-byte or fixed-width multibyte NCHAR unicode data in the database

%TYPE Attribute is used to declare a variable according to : a database column definition / another declared variable and is prefixed with the database table and column / the name of the declared variable

Declaring variables with the %TYPE Attribute

Syntax: identifier table.column_name%type;

eg :

...

emp_name employees.last_name%type;

balance NUMBER(7,2);

min_balance balance%TYPE := 1000;

SQL variables

Bind variables

created in the environment

also called hosted variables

create with the VARIABLE keyword

used in SQL statements ans PL/SQL blocks

accessed even after the PL/SQL block is executed

reference with a preceding colon (":")

note : Bind variables are created in teh environment and not in the declarative section of a PL/SQL block. Variables declared in a PL/SQL block are available only when you execute the block.

create a bind variable in iSQL*Plus or in SQL*Plus

eg : VARIABLE return_code NUMBER

VARIABLE return_msg VARCHAR2(30)

use bind variables

eg :

VARIABLE result NUMBER

BEGIN

SELECT(SALARY*12) + NVL(COMMISSION_PCT,0) INTO :result

FROM employees WHERE employee_id = 144;

END;

PRINT result

Printing Bind Variables

eg :

VARIABLE emp_salary NUMBER

BEGIN

SELECT salary INTO :emp_salary

FROM employees WHERE employee_id = 178;

END;

PRINT emp_salary

SELECT first_name, last_name

FROM employees

WHERE salary=:emp_salary;

eg :

VARIABLE emp_salary NUMBER

SET AUTOPRINT ON

BEGIN

SELECT salary INTO :emp_salary

FROM employees WHERE employee_id = 178;

END;

note : use the command SET AUTOPRINT ON to automatically display the bind variables used in a successful PL/SQL block

Subsititution variables

used to get input at run time

referenced within a PL/SQL block with a preceding ampersand

used to avoid hard coding values that can be obtained at run time

eg :

VARIABLE emp_salary NUMBER

SET AUTOPRINT ON

DECLARE

empno NUMBER(6) :=&empno;

BEGIN

SELECT salary INTO :emp_salary

FROM employees WHERE employee_id = empno;

END;

Prompt for Substitution Variables

eg :

SET VERIFY OFF

VARIABLE emp_salary NUMBER

ACCEPT empno PROMPT ‘Please enter a valid employee number: ‘

SET AUTOPRINT ON

DECLARE

empno NUMBER(6) := &empno;

BEGIN

SELECT salary INTO :emp_salary GROM employees

WHERE employee_id = empno;

END;

User variables

Using DEFINE fro User Variable

eg :

SET VERIFY OFF

DEFINE lname = Urman

DECLARE

fname VARCHAR2(25);

BEGIN

SELECT first_name INTO fname FROM employees

WHERE last_name = ‘&lname‘;

END;

Executable section in PL/SQL

Write executable statements in a PL/SQL block

Write nested bocks

Define the scope and visibility of variables in the nested blocks

Qualify nested blocks with labels

Writing executable statements

Identify PL/SQL Block

Lexical units:

are building blocks of any PL/SQL block

are sequences of characters including letters, digits, tabs, spaces, returns, and symbols

can be classified as Identifiers / Delimiters / Literals / Comments

PL/SQL Block Syntax and Guidelines

Literals : Character and date literals must be enclosed in single quotation marks.

eg : name := ‘Henderson‘;

numbers can be simple values or scientific notation

note : a literal is an explicit numeric, character string, date or Boolean value that is not represented by an identifier. Character literals include all the printable characters in the PL/SQL character set : letters, numerals, spaces and special symbols. Numeric literals can be represented either by a simple value or by a scientific notation

Commenting Code

Prefix single-line comments with two dashes (--).

Place multiple-line comments between the symbol "/*" and "*/".

eg :

DECLARE

...

annual_sal NUMBER (9,2);

BEGIN -- Begin the executable section

/*Compute the annual salary based on the monthly salary input from the user */

annual_sal := monthly_sal *12;

END; -- This is the end of the block

SQL Functions in PL/SQL

Available in procedual statements :

Single-row number

Single-row character

Data type conversion

Date

Timestamp

GREATEST and LEAST

Miscellaneous functions

Not availabe in procedual statements

DECODE

Group functions

Get the length of a string :

desc_size INTEGER(5)

prod_description VARCHAR2(70) :=‘You can use this product with your radios for higher frequency‘;

-- get the length of the string in

-- prod_description

desc_size := LENGTH(PROD_DESCRIPTION);

Convert the employee name to lowercase:

emp_name := LOWER (emp_name)

Date Type Conversion

convert data to comparable data types

are of two types

implicit conversions

explicit conversions

xome conversion functions

some conversion functions

TO_CHAR

TO_DATE

TO_NUMBER

TO_TIMESTAMP

Data Type Conversion

date_of_joining DATE:= ‘02_Fev-2000‘;

date_of_joining DATE:= ‘February 02, 2000‘;

date_of_joining DATE:= TO_DATE(‘ February 02, 2000‘, ‘Month DD, YYYY‘);

Nested Block

PL/SQL blocks can be nested

an executable section(BEGIN ... END) can contain nested blocks

an exception section can contain blocks

eg :

DECLARE outer_variable VARCHAR2(20) := ‘GLOBAL BARAIBLE‘;

BEGIN

DECLARE

inner_variable VARCHAR2(20) := ‘LOCAL VARIABLE‘;

BEGIN

DBMS_OUTPUT.PUT_LINE(inner_variable);

DBMS_OUTPUT.PUT_LINE(outer_variable);

END;

DBMS_OUTPUT.PUT_LINE(outer_variable);

END;

Variable Scope and Visibility

eg :

DECLARE

father_name VARCHAR2(20) :=‘Patrick‘;

date_of_birth DATE:=‘20-April-1972‘;

BEGIN

DECLARE

child_name VARCHAR2(20) :=‘Mike‘;

date_of_birth DATE:= ‘12-Dec-2002‘;

BEGIN

DBMS_OUTPUT.PUT_LINE(‘Father‘ ‘s Name: ‘ || father_name);

DBMS_OUTPUT.PUT_LINE(‘Date of birth: ‘ || date_of_birth);

DBMS_OUTPUT.PUT_LINE(‘Child‘ ‘s name: ‘|| child_name);

END;

DBMS_OUTPUT.PUT_LINE(‘Date of Birth: ‘ || date_of_birth);

END;

Qualify an Identifier

<<outer>>

DECLARE

father_name VARCHAR2(20) :=‘Patrick‘;

date_of_birth Date:=‘20-Apr-1972‘;

BEGIN

DECLARE

child_name VARCHAR2(20) := ‘Mike‘;

date_of_birth DATE:=‘12-Dec-2002‘;

BEGIN

DBMS_OUTPUT.PUT_LINE(‘Father‘ ‘s Name: ‘ || father_name);

DBMS_OUTPUT.PUT_LINE(‘Date of Birth: ‘ || outer.date_of_birth);

DBMS_OUTPUT.PUT_LINE(‘Date of Birth: ‘ || date_of_birth);

END;

Operators in PL/SQL

Logical

Arithmetic

Concatenation

Parentheses to control order of operations

Exponential operator

loop_count := loop_count +1; (Increment the counter for a loop)

good_sal := sal BETWEEN 50000 AND 150000; (Set the value of a Boolean flag)

valid := (empno IS NOT NULL); (Set the value of a Boolean flag.)

Programming Guidelines

Make code maintenance easier by :

Documenting code with comments

Developing a case convention for the code

Developing naming conventions for identifiers and other objects

Enhancing readability by indenting

Indenting Code

for clarity, indent each level of code

eg :

BEGIN

IF x=0 THEN

y :=1;

END IF;

END;

EG :

DECLARE

deptno NUMBER(4);

location_id NUMBER(4);

BEGIN

SELECT department_id;

local_id

INTO deptno, location_id

FROM departments

WHERE department_name = ‘Sales‘;

...

END;

Labs: Executable section in PL/SQL

04 Cursors, logical structures and composite data types

Use SQL statements in PL/SQL

Use SQL cursors in PL/SQL

Identify the uses and types of control structures

Construct and identify different loop statements

Create user-defined PL/SQL records

Create an INDEX BY table of records

Declare and control explicit cursors

Displaying data from multiple tables

Using subqueries to solve queries

Using the set operators

Interacting with the Oracle server

SQL Statements in PL/SQL

Retrive a row from the database by using the SELECT command.

Make changes to rows in the database by using DML commands

Control a transaction with the COMMIT, ROLLBACK, or SAVEPOINT command.

SELECT Statements in PL/SQL

Retrieve data from the database with a SELECT statement

syntax :

SELECT select_list

INTO {variable_name [, variable_name]...| record_name}

FROM table

[WHERE condition];

The INTO clause is required

Queries must return only one row

eg :

SET SERVEROUTPUT ON

DECLARE

fname VARCHAR2(25);

BEGIN

SELECT first_name INTO fname

FROM employees WHERE employee_id=200;

DBMS_OUTPUT.PUT_LINE(‘ First Name is : ‘ | | fname);

END;

Retrieving Data in PL/SQL

Retrieve the hire_date and the salary for the specified employee

eg :

DECLARE

emp_hiredate employees.hire_date%TYPE;

emp_salary employees.salary%TYPE;

BEGIN

SELECT hire_date, salary

INTO emp_hiredate, emp_salary

FROM employees

WHERE employee_id = 100;

END;

Return the sum of the salaries for all the employees in the specified department

eg :

SET SERVEROUTPUT ON

DECLARE

sum_sal NUMBER(10,2);

deptno NUMBER NOT NULL := 60;

BEGIN

SELECT SUM(salary) -- group function

INTO sum_sal FROM employees

WHERE department_id = deptno;

DBMS_OUTPUT.PUT_LINE (‘The sum of salary is ‘ | | sum_sal);

END;

Naming Conventions

eg :

DECLARE

hire_date employees.hire%TYPE;

sysdate hire_data%TYPE;

employee_id employees.employee_id%TYPE := 176;

BEGIN

SELECT hire_date,sysdate

INTO hire_date, sysdate

FROM employees

WHERE employee_id = employee_id;

END;

Naming Conventions

Use a naming convention to avoid ambiguity in the WHERE clause.

Avoid using database column names as identifiers

Syntax errors can arise because PL/SQL checks the database first for a column in the table.

The names of local variables and formal parameters take precedence over the names of database tables

The names of database table columns take precedence over the names of local variables.

Data Manipulation in PL/SQL

Make changes to database tables by using DML commands :

INSERT

Inserting Data

Add new employee information to the EMPLOYEES table

eg :

BEGIN

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary) VALUES (employees_seq.NEXTVAL, "Ruth‘, ‘Cores‘, ‘RCORES‘, sysdate, ‘AD_ASST‘, 4000);

END;

UPDATE

Updating data

Increase the salary of all employees who are stock clerks

eg :

DECLARE

sal_increase employees.salary%TYPE := 800;

BEGIN

UPDATE employees

SET salary = salary + sal_increase

WHERE job_id = ‘ST_CLERK‘;

END;

DELETE

Deleting data

Delete rows that belong to department 10 from the employees table.

eg :

DECLARE

deptno employees.department_idTYPE := 10;

BEGIN

DELETE FROM employees

WHERE department_id = deptno;

END;

MERGE

Merging rows

Insert or update rows in the copy_emp table to match the employees table.

DECLARE

empno employees.employee_id%TYPE := 100;

BEGIN

MERGE INTO copy_emp c USING employees e ON (e.employee_id = empno) WHEN MATCHED THEN UPDATE SET c.first_name = e.first_name, c.last_name = e.last_name, c.email = e.email, ... WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name, e.last_name, ... e.department_id);

END;

SQL Cursors

a cursor is a pointer to the private memory area allocated by the Oracle server.

There are two types of cursors :

Implicit cursors : created and manged internally by the Oracle server to process SQL statements

Explicit cursors : explicitly declared by the programmer

SQL cursor attributes for implicit cursots

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

SQL%FOUND : Boolean attribute that evaluates to TRUE if the recent SQL statement returne at least on row.

SQL%NORFOUND :Boolean attribute that evaluates to TRUE if the most recent SQL statement did not return even one row.

SQL%ROWCOUNT :An integer value that represents number of rows affected by the most recent SQL statement.

Delete rows that have the specified employee ID from the employees table. Print the number of rows deleted.