Transcript Writing Executable Statement

Program with PL/SQL
Lesson 5
Working with Composite Data
Types
Composite Data Types
• Are of two types
– PL/SQL RECORDS
– PL/SQL Collections
• INDEX BY Table
• Nested Table
• VARRAY
• Contain internal components
• Are reusable
PL/SQL Records
• Must contain one or more components of
any scalar, RECORD, or INDEX BY table
data type, called fields
• Are similar in structure to records in a third
generation language (3GL)
• Are not the same as rows in a database
table
• Treat a collection of fields as a logical unit
• Are convenient for fetching a row of data
from a table for processing
Creating a PL/SQL Record
Syntax:
TYPE type_name IS RECORD
(field_declaration[, field_declaration] …);
Identifier
type_name;
Where field_declaration is:
field_name
{field_type | variable%TYPE
| table.column%TYPE | table%ROWTYPE}
[[NOT NULL] {:= | DEFAULT} expr]
PL/SQL Record Structure
Field1 (data type)
Field2 (data type)
Field3 (data type)
Field1 (data type)
Field2 (data type)
Field3 (data type)
Employee_id number(6)
Last_name varchar2(25)
Job_id varchar2(10)
Example:
100
King
AD_PRES
The %ROWTYPE Attribute
• Declare a variable according to a collection
of columns in a database table or view
• Prefix %ROWTYPE with the database table
• Fields in the record take their names and
data types from the columns of the table or
view
Advantages of Using %ROWTYPE
• The number and data types of the
underlying database columns need not be
known
• The number and data types of the
underlying database column may change
at run time
• The attribute is useful when retrieving a
row with the SELECT * statement
INDEX BY Tables
• Are composed of two components:
– Primary key of data type BINARY_INTEGER
– Column of scalar or record data type
• Can increase in size dynamically because
they are unconstrained
Creating an INDEX BY Table
Syntax:
TYPE type_name IS TABLE OF
{column_type | variable%TYPE
| table.column%TYPE} [NOT NULL]
| table.%ROWTYPE
[INDEX BY BINARY_INTEGER];
Identifier
type_name;
Declare an INDEX BY table to store names
Example: . . .
TYPE ename_table_type IS TABLE OF
employee.last_name%TYPE
INDEX BY BINARY_INTEGER;
Ename_table
ename_table_type;
INDEX BY Table Structure
Unique identifier
Column
...
...
1
2
3
...
Jones
Smith
Maduro
...
BINARY_INTEGER
Scalar
Using INDEX BY Table Methods
The following methods make INDEX BY
tables easier to use:
–
–
–
–
–
–
–
EXISTS
COUNT
FIRST and LAST
PRIOR
NEXT
TRIM
DELETE
Writing Executable Statement
PL/SQL Block Syntax and Guidelines
• Statements can be split across line, but
keywords must not split
• Lexical units can be classified as:
– Delimiters
– Identifiers
– Literals
• Character and date literals must be enclosed in
single quotation marks
• Numbers can be simple values or scientific notation
– Comments
Identifiers
• Can contain up to 30 characters
• Must begin with an alphabetical character
• Can contain numerals, dollar signs,
underscores, and nuber signs
• Cannot contain characters such as
hyphens, slashes, and spaces
• Should not have the same name as a
database table column name
• Should not be reserved words
Commenting Code
• Prefix single-line comments with two
dashes (--)
• Place multiple-line comments between the
symbols /* and */
SQL Functions in PL/SQL
• Available in procedural statements:
–
–
–
–
–
–
–
Single-row number
Single-row character
Data type conversion
Date
Timestamp
GREATEST and LEAST
Miscellaneous functions
• Not available in procedural statements
– DECODE
– Group functions
Nested Blocks and Variable
Scope
• PL/SQL blocks can be nested wherever an
executable statement is allowed
• A nested block becomes a statements
• An exception section can contain nested
blocks
• The scope of an identifier is that region of
a program unit (block, subprogram, or
package) from which you can reference
the identifier
Identifier Scope
An identifier is visible in the regions where
you can reference the identifier without
having to qualify it:
– A block can look up to the enclosing block
– A block cannot look down to enclosed
blocks
Qualify an Identifier
• The qualifier can be the label of an
enclosing block
• Qualify an identifier by using the block
label prefix
Determining Variable Scope
<<outer>>
DECLARE
v_sal
NUMBER(7,2) := 60000;
v_comm
NUMBER(7,2) := v_sal * 0.20;
v_message
VARCHAR2(255) := ‘eligible for
commision’;
BEGIN
DECLARE
v_sal
NUMBER(7,2) := 50000;
v_comm
NUMBER(7,2) := 0;
v_total_comp NUMBER(7,2) := v_sal + v_comm;
BEGIN
v_message := ‘CLERK not’||v_message;
outer.v_comm := v_sal * 0.30;
END;
v_message := ‘SALESMAN’||v_message;
END;
Operators in PL/SQL
•
•
•
•
Logical
Arithmetic
Concatenation
Parentheses to control order of operations
• Exponential operator (**)
Rules of Precedence
Operator
Operation
**
Exponentiation
+, -
Identity, negation
*, /
Multiplication, division
+. -, ||
Addition, subtraction,
concatenation
=, <. >, <=, >=, <>, !=, ~=, ^=,
IS NULL, LIKE, BETWEEN, IN
Comparison
NOT
Logical negation
AND
Conjunction
OR
Inclusion
Programming Guidelines
Make code maintenance easier by:
• Documenting code with comments
• Developing a case convention for tho code
• Developing naming conventions for
identifies and other objects
• Enhancing readability by indenting