Oracle10g Developer: PL/SQL Programming 1
Objectives
SQL queries within PL/SQL Host or bind variables The %TYPE attribute Include queries and control structures Embed DML statements within PL/SQL Composite data types Creating collections
Oracle10g Developer: PL/SQL Programming 2
Brewbean’s Challenge
Consider actions needed upon check out
Oracle10g Developer: PL/SQL Programming 3
Include SQL within a Block
Data query needs to identify if the customer has a saved basket
Oracle10g Developer: PL/SQL Programming 4
SQL statements can be embedded into the executable area of a PL/SQL block
SELECT statements are embedded to query needed data
An INTO clause is added to a SELECT statement to move data retrieved into variables
Include SQL within a Block
Oracle10g Developer: PL/SQL Programming 5
SQL Query – add INTO clause
Assignment Statement
Include SQL within a Block
Oracle10g Developer: PL/SQL Programming 6
Executing a Block with Errors
Common Errors– Use = rather than :=– Not declaring a variable– Misspelling a variable name– Not ending a statement with ;– No data returned from a SELECT statement
Oracle10g Developer: PL/SQL Programming 7
Not closing a statement with ;
Executing a Block with Errors
Oracle10g Developer: PL/SQL Programming 8
Host or Bind Variables
Reference host variables with a preceding colon in PL/SQL
BEGIN
:g_shopper := 25;
END;
/
WHERE idShopper = :g_shopper
AND orderplaced = 0;
Create host variable
Reference host variable
Oracle10g Developer: PL/SQL Programming 9
%TYPE Attribute
Use in variable declaration to provide data type based on a table column
Ideal for declaring variables that will hold data from the database
Minimizes maintenance by avoiding program changes to reflect database column changes
Called an anchored data type
lv_basket_num bb_basket.idBasket%TYPE;
Oracle10g Developer: PL/SQL Programming 10
Data Retrieval & Decisions
Oracle10g Developer: PL/SQL Programming 11
IF Statement Example
Oracle10g Developer: PL/SQL Programming 12
Including DML
DML statements can be embedded into PL/SQL blocks to accomplish data changes
DML includes INSERT, UPDATE, and DELETE statements
Oracle10g Developer: PL/SQL Programming 13
Including DML
Add a new shopper - INSERT
Oracle10g Developer: PL/SQL Programming 14
Composite Data Types
Stores multiple values of different data types as one unit
Record – can hold one row of data Table of records – can hold multiple rows of data
Oracle10g Developer: PL/SQL Programming 15
Record Data Type
DECLARE TYPE type_basket IS RECORD ( basket bb_basket.idBasket%TYPE, created bb_basket.dtcreated%TYPE, qty bb_basket.quantity%TYPE, sub bb_basket.subtotal%TYPE); rec_basket type_basket; lv_days_num NUMBER(3); lv_shopper_num NUMBER(3) := 25;BEGIN SELECT idBasket, dtcreated, quantity, subtotal INTO rec_basket FROM bb_basket WHERE idShopper = lv_shopper_num AND orderplaced = 0; lv_days_num := SYSDATE - rec_basket.created;END;
Oracle10g Developer: PL/SQL Programming 16
%ROWTYPE Attribute
Create record structure based on table structure
DECLARE rec_shopper bb_shopper%ROWTYPE;BEGIN SELECT * INTO rec_shopper FROM bb_shopper WHERE idshopper = :g_shopper; DBMS_OUTPUT.PUT_LINE(rec_shopper.lastname); DBMS_OUTPUT.PUT_LINE(rec_shopper.address); DBMS_OUTPUT.PUT_LINE(rec_shopper.email);END;
Oracle10g Developer: PL/SQL Programming 17
Table of Records
Oracle10g Developer: PL/SQL Programming 18
Collections
Store multiple values of the same data type Similar to arrays in other languages Index-by Tables – handle many rows of one field
One-dimensional
Can only have one column
Unconstrained Rows added dynamically as needed
Sparse A row only exists when a value is assigned; rows do not have to be assigned sequentially
Homogeneous All elements have same data type
Indexed Integer index serves as primary key of the table
Oracle10g Developer: PL/SQL Programming 19
Index-by Table Attributes
Attribute Name
Value Data type Description
COUNT NUMBER Number of rows in the table
DELETE
None Removes a row from the table
EXISTS BOOLEAN TRUE if specified row does exist
FIRST BINARY_INTEGER
Index for the first row in the table
LAST BINARY_INTEGER
Index for the last row in the table
NEXT BINARY_INTEGER
Index for the next row in the table after the row specified
PRIOR BINARY_INTEGER
Index for the row in the table before the row specified
Oracle10g Developer: PL/SQL Programming 20
Index-by Table Example
Host variables declaration and initialization
Oracle10g Developer: PL/SQL Programming 21
Index-by Table Example
Put host variable values into the table variable
A FOR loop adds all the sample measurements that have been entered
into the table variable
lv_avg_num calculates the Average measurement
Index-by table data type declaration
Index-by table variable declaration
Oracle10g Developer: PL/SQL Programming 22
Summary
SQL queries and DML statements can be embedded into a block
An INTO clause must be added to a SELECT The %TYPE attribute is used to use a column data type Composite data types can hold multiple values in a
single variable
Oracle10g Developer: PL/SQL Programming 23
Summary
A record can hold a row of data A table of records can hold multiple rows of data The %ROWTYPE attribute can be used to declare a
data type based on a table’s structure An index-by table is a collection similar to arrays The GOTO statement enables execution to jump to
specific portions of code