05ch - EnhancedGuidetoOracle8i Chapter5:...

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

View Full Document Right Arrow Icon
1 Enhanced Guide to Oracle8i Enhanced Guide to Oracle8i Chapter 5: Advanced PL/SQL Programming
Background image of page 1

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

View Full DocumentRight Arrow Icon
2 Anonymous PL/SQL  Programs Write code in text editor, execute it in  SQL*Plus Code can be stored as text in file system Program cannot be called by other  programs, or executed by other users Cannot accept or pass parameter values
Background image of page 2
3 Named PL/SQL Programs Can be created: Using Procedure Builder Can be stored: As compiled objects in database As source code libraries in file system Can be called by other programs Can be executed by other users
Background image of page 3

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

View Full DocumentRight Arrow Icon
4 Named Program Locations Server-side Stored in database as database objects Execute on the database server Client-side Stored in the client workstation file system Execute on the client workstation
Background image of page 4
5 Named Program Types Program Units (client or server-side) Procedures Functions Libraries (client-side) Packages (client or server-side) Triggers (server-side)
Background image of page 5

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

View Full DocumentRight Arrow Icon
6 Program Units Procedures Can receive and pass multiple parameter  values Can call other program units Functions Like procedures, except they return a  single value
Background image of page 6
7 Variables used to pass data values  in/out of program units Declared in the procedure header  Parameter values are passed when the  procedure is called from the calling  program Parameters
Background image of page 7

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

View Full DocumentRight Arrow Icon
8 Parameter Modes IN Incoming values, read-only OUT Outgoing values, write-only IN OUT Can be both incoming and outgoing
Background image of page 8
9 Creating a Procedure  CREATE OR REPLACE PROCEDURE procedure_name ( parameter1 mode datatype, parameter2 mode datatype , … ) IS | AS local variable declarations BEGIN program statements EXCEPTION exception handlers END; header body
Background image of page 9

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

View Full DocumentRight Arrow Icon
10 Executing a Procedure  EXECUTE procedure_name ( parameter1_value , parameter2_value , …);
Background image of page 10
11 Formal parameters:  declared in procedure  header Actual parameters:  values placed in  parameter list when procedure is called Values correspond based on order Procedure Header: PROCEDURE calc_gpa (s_id IN NUMBER, term_id IN NUMBER, gpa OUT NUMBER); Procedure Call: calc_gpa (current_s_id, 4, current_gpa); Parameter Types Formal Parameters Actual Parameters
Background image of page 11

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

View Full DocumentRight Arrow Icon
12 Debugging Named Program  Units in SQL*Plus Isolate program lines causing errors and  then fix them Use SHOW ERRORS command to view  compile error details Use DBMS_OUTPUT.PUT_LINE  commands to view intermediate values  and track program flow
Background image of page 12
13 Creating a Function CREATE OR REPLACE FUNCTION function_name ( parameter1 mode datatype, parameter2 mode datatype , … ) RETURN function_return_data_type IS local variable declarations BEGIN program statements RETURN return_value ; EXCEPTION exception handlers RETURN EXCEPTION_NOTICE; END; header body
Background image of page 13

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

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

This note was uploaded on 07/15/2011 for the course ECO 2023 taught by Professor Mr.raza during the Summer '10 term at FAU.

Page1 / 73

05ch - EnhancedGuidetoOracle8i Chapter5:...

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

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