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

Info iconThis preview shows pages 1–13. 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 6 Functions
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: Functions Creating a stored function in SQL*Plus Using OUT parameters in functions Including multiple RETURN statements in a function Using a RETURN statement in a procedure Using constraints of actual and formal parameters
Background image of page 2
P L / S Q L Oracle10g Developer: PL/SQL Programming 3 Chapter Objectives (continued) After completing this lesson, you should be able to understand (continued): Understanding and controlling the passing of parameter values Working with function purity levels Referencing the data dictionary for program units Deleting program units
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 •Need program module to check a user login
Background image of page 4
P L / S Q L Oracle10g Developer: PL/SQL Programming 5 Brewbean’s Challenge (continued) •Need program module to calculate shipping cost based on the number of items in the basket
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 Rebuild your database Load and run “c6DBcreate.sql” Oracle10g Developer: PL/SQL Programming 6
Background image of page 6
P L / S Q L Review A named program unit is a PL/SQL block saved with a name so it can be reused. Procedure is one type of program unit Used to accomplish one or more tasks Return none or many values Used only in PL/SQL statement 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 Oracle10g Developer: PL/SQL Programming 8 Introduction to Functions A function is similar to a procedure in that it can accomplish a task and retrieve/return values A function is part of an expression, not an entire statement such as a procedure Can be used in both PL/SQL and SQL statements Same as Oracle-supplied functions (ROUND, TO_CHAR) Contains a RETURN statement
Background image of page 8
P L / S Q L Oracle10g Developer: PL/SQL Programming 9 Example of Oracle-Supplied Function SELECT idProduct, price, ROUND(price, 0) FROM bb_product WHERE idProduct < 4;
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 Quiz 1. Give an example of an Oracle-supplied function. Answer: ROUND, TO_CHAR, etc. 2. What is one determining factor as to when to create a procedure versus a function? Answer: The number of values to be returned Oracle10g Developer: PL/SQL Programming 10
Background image of page 10
P L / S Q L Oracle10g Developer: PL/SQL Programming 11 Function Create Statement
Background image of page 11

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

View Full DocumentRight Arrow Icon
P L / S Q L Function Create Statement In the header, a line is added to indicate the data type of the value to be returned. In the function body, a RETURN statement in the executable section indicates which value is returned. The RETURN statement must be included to indicate the data type of the value that is returned The data type does not include any sizing information. At least one RETURN statement must be included in a
Background image of page 12
Image of page 13
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 57

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

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

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