P / L S Q L Oracle10g Developer: PL/SQL Programming

Chapter 3

Handling Data in PL/SQL Blocks

Chapter Objectives

P / L S Q L

• After completing this lesson, you should be able to understand: – SQL queries within PL/SQL – Host or bind variables – The %TYPE attribute – Expanding block processing to include queries and control structures Oracle10g Developer: PL/SQL Programming 2

Chapter Objectives (continued)

P / L S Q L

• After completing this lesson, you should be able to understand (continued): – Embedding DML statements within PL/SQL – Composite data types – Creating collections – Understanding the GOTO statement Oracle10g Developer: PL/SQL Programming 3

Brewbean’s Challenge

P / L S Q L

• Consider actions needed upon check out Oracle10g Developer: PL/SQL Programming 4

P / L S Q L

Include SQL within a Block

• Data query needs to identify if the customer has a saved basket Oracle10g Developer: PL/SQL Programming 5

P / L S Q L

Include SQL within a Block (continued)

• 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 Oracle10g Developer: PL/SQL Programming 6

P / L S Q L

Include SQL within a Block (continued)

SQL Query – add INTO clause Assignment Statement Oracle10g Developer: PL/SQL Programming 7

Executing a Block with Errors

P / L S Q L

• 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 8

P / L S Q L

Executing a Block with Errors (continued)

• Not closing a statement with ; Oracle10g Developer: PL/SQL Programming 9

Host or Bind Variables

P / L S Q L

• 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 10

%TYPE Attribute

P / L S Q L

• 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 11

P / L S Q L

Data Retrieval with Decision Structures

Oracle10g Developer: PL/SQL Programming 12

P / L S Q L

IF Statement Example

Oracle10g Developer: PL/SQL Programming 13

Including DML

P / L S Q L

• 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 14

P / L S Q L

Including DML (continued)

• Add a new shopper - INSERT Oracle10g Developer: PL/SQL Programming 15

Composite Data Types

P / L S Q L

• 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 16

P / L S Q L

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 END;

rec_basket.created

; Oracle10g Developer: PL/SQL Programming 17

%ROWTYPE Attribute

P / L S Q L

• 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 18

P / L S Q L

Table of Records

Oracle10g Developer: PL/SQL Programming 19

Collections

P / L S Q L

• 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 Unconstrained Sparse Homogeneous Indexed Can only have one column Rows added dynamically as needed A row only exists when a value is assigned; rows do not have to be assigned sequentially All elements have same data type Integer index serves as primary key of the table

Oracle10g Developer: PL/SQL Programming 20

Index-by Table Attributes

P / L S Q L Attribute Name COUNT DELETE EXISTS FIRST Value Data type NUMBER None BOOLEAN BINARY_INTEGER LAST NEXT PRIOR BINARY_INTEGER BINARY_INTEGER BINARY_INTEGER Description Number of rows in the table Removes a row from the table TRUE if specified row does exist Index for the first row in the table Index for the last row in the table Index for the next row in the table after the row specified Index for the row in the table before the row specified

Oracle10g Developer: PL/SQL Programming 21

P / L S Q L

Index-by Table Example

Host variables declaration and initialization Oracle10g Developer: PL/SQL Programming 22

P / L S Q L

Index-by Table Example

Index-by table data type declaration Index-by table variable declaration 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 23 Oracle10g Developer: PL/SQL Programming

GOTO Statement

P / L S Q L

• Jumping control that instructs the program to move to another area of code to continue processing • Most developers discourage the use of GOTO as it complicates the flow of execution Oracle10g Developer: PL/SQL Programming 24

Summary

P / L S Q L

• 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 25

P / L S Q L

Summary (continued)

• 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 Oracle10g Developer: PL/SQL Programming 26