ch03 - P L / S Q L Oracle10g Developer: PL/SQL Programming...

Info iconThis preview shows pages 1–12. Sign up to view the full content.

View Full Document Right Arrow Icon
P L / S Q L Oracle10 g Developer: PL/SQL Programming Chapter 3 Handling Data in PL/SQL Blocks
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
P L / S Q L Oracle10g Developer: PL/SQL Programming 2 Chapter Objectives 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
Background image of page 2
P L / S Q L Chapter Objectives (continued) 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
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
P L / S Q L Oracle10g Developer: PL/SQL Programming 4 Brewbean’s Challenge Consider actions needed upon check out
Background image of page 4
P L / S Q L Potential Processing Task Potential processing tasks for checking out Calculating taxes Calculating shipping charges Checking / updating product inventory Determine if the shopper already has credit cards information stored Oracle10g Developer: PL/SQL Programming 5
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
P L / S Q L Oracle10g Developer: PL/SQL Programming 6 Include SQL within a Block Data query needs to identify if the customer has a saved basket
Background image of page 6
P L / S Q L Example To determine if the shopper credit card information is already stored: A query on BB_SHOPPER is required To calculate shipping charges A query is required in BB_BASKETITEM to determine the quantity of items in the basket. Furthermore, An UPDATE statement is required to modify the order data to reflect the change (if the order has been completed or not). Note, the ORDER_PLACED column in the BB_BASKET table is set to 1 to indicate the order has been placed. Oracle10g Developer: PL/SQL Programming 7
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
P L / S Q L Rebuild your database Please load and execute the new file “c3Dbcreate.sql”. Oracle10g Developer: PL/SQL Programming 8
Background image of page 8
P L / S Q L Oracle10g Developer: PL/SQL Programming 9 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
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
P L / S Q L First Example DECLARE lv_qty_num NUMBER(3); BEGIN SELECT SUM(quantity) INTO lv_qty_num FROM bb_basketitem WHERE idBasket=9; DBMS_OUTPUT.PUT_LINE(lv_qty_num); END; Oracle10g Developer: PL/SQL Programming 10
Background image of page 10
L / S Q L Quick Quiz 1. True or False: SQL queries can be embedded in a PL/SQL block without any modifications. Answer: False 2. What is the difference between an SQL query executed in SQL*Plus versus a PL/SQL block? Answer: INTO clause 3. What symbol is used to indicate a value assignment? Answer: :=
Background image of page 11

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 12
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 01/17/2012 for the course GCIS 511 taught by Professor Liu during the Fall '10 term at Gannon.

Page1 / 67

ch03 - P L / S Q L Oracle10g Developer: PL/SQL Programming...

This preview shows document pages 1 - 12. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online