Banner Oracle PL/SQL and Database Objects Training Workbook

Banner Oracle PL/SQL and Database Objects Training Workbook

January 2007 Using Oracle for Banner 7

HIGHER EDUCATION

What can we help you achieve?

Confidential Business Information

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------This documentation is proprietary information of SunGard Higher Education and is not to be copied, reproduced, lent or disposed of, nor used for any purpose other than that for which it is specifically provided without the written permission of SunGard Higher Education.

Prepared By:

SunGard Higher Education 4 Country View Road Malvern, Pennsylvania 19355 United States of America

? 2004-2008 SunGard. All rights reserved. The unauthorized possession, use, reproduction, distribution, display or disclosure of this material or the information contained herein is prohibited.

In preparing and providing this publication, SunGard Higher Education is not rendering legal, accounting, or other similar professional services. SunGard Higher Education makes no claims that an institution's use of this publication or the software for which it is provided will insure compliance with applicable federal or state laws, rules, or regulations. Each organization should seek legal, accounting and other similar professional services from competent providers of the organization's own choosing.

Without limitation, SunGard, the SunGard logo, Banner, Campus Pipeline, Luminis, PowerCAMPUS, Matrix, and Plus are trademarks or registered trademarks of SunGard Data Systems Inc. or its subsidiaries in the U.S. and other countries. Third-party names and marks referenced herein are trademarks or registered trademarks of their respective owners.

Table of Contents

Section A: Introduction ..............................................................................................................9 Overview ..................................................................................................................................9

Section B: PL/SQL Basic Structure ........................................................................................10 Overview ................................................................................................................................10 PL/SQL Overview..................................................................................................................11 PL/SQL Block Structure ........................................................................................................12 Sections of the PL/SQL Block ...............................................................................................13 Web Enabled Model...............................................................................................................14 Conventions............................................................................................................................15 Running Anonymous PL/SQL ...............................................................................................17 PL/SQL Versions/History ......................................................................................................19 Self Check ..............................................................................................................................20

Section C: Declaring Variables................................................................................................21 Overview ................................................................................................................................21 Declaring Variables................................................................................................................22 Built-in Datatypes...................................................................................................................23 Referencing Database Objects................................................................................................25 Scoping Rules.........................................................................................................................26 Self Check ..............................................................................................................................28

Section D: SQL Statements within PL/SQL...........................................................................29 Overview ................................................................................................................................29 Comments...............................................................................................................................30 Data Manipulation..................................................................................................................31 Retrieving Data.......................................................................................................................33 Process Transactions ..............................................................................................................34 Self Check ..............................................................................................................................35

Section E: Conditional, Iterative, Sequential Control...........................................................36 Overview ................................................................................................................................36 Conditional Control................................................................................................................37 Nested IF Statements..............................................................................................................41 Iterative Control .....................................................................................................................42 Sequential Control..................................................................................................................45 View Information on Screen ..................................................................................................48 Self Check ..............................................................................................................................50

Table of Contents (Continued)

Section F: Handle PL/SQL Errors ..........................................................................................52 Overview ................................................................................................................................52 Exception Handling................................................................................................................53 Named System Exceptions .....................................................................................................54 Named Programmer-Defined Exceptions ..............................................................................56 Exception Propagation ...........................................................................................................58 Unnamed System Exceptions.................................................................................................62 SQLCODE and SQLERRM ...................................................................................................63 Success or Failure? .................................................................................................................65 Forcing Program Abort ..........................................................................................................66 Debugging ..............................................................................................................................67 Self Check ..............................................................................................................................68

Section G: Cursors, Records, and Tables ...............................................................................70 Overview ................................................................................................................................70 Cursor Basics..........................................................................................................................71 Declare Cursors ......................................................................................................................72 Open Cursors..........................................................................................................................73 Fetch from Cursors.................................................................................................................74 Close Cursors .........................................................................................................................75 Cursor Attributes ....................................................................................................................76 Reference the Current Row ....................................................................................................78 Cursor FOR Loops .................................................................................................................79 Conceptual Cursor Loop Model .............................................................................................80 Statements Associated with Implicit Cursors.........................................................................81 PL/SQL Records.....................................................................................................................83 Tables..... ................................................................................................................................86 Tables vs. Arrays....................................................................................................................87 Tables of Records...................................................................................................................88 Table Attributes......................................................................................................................89 Self Check ..............................................................................................................................95

Table of Contents (Continued)

Section H: Procedures and Functions ...................................................................................101 Overview ..............................................................................................................................101 Modular Code.......................................................................................................................102 Layers of Oracle Programming ............................................................................................103 Procedure..............................................................................................................................104 Parameters ............................................................................................................................105 Executing Procedures ...........................................................................................................107 Executing Procedures Example............................................................................................108 Positional vs. Named Notation.............................................................................................110 Functions ..............................................................................................................................111 Calling a Function ................................................................................................................114 What Can Functions Do For You? .......................................................................................115 Example Function.................................................................................................................116 Handling Compilation Errors ...............................................................................................118 Locate Objects in the Database ............................................................................................121 Remove Subprograms ..........................................................................................................124 Self Check ............................................................................................................................125

Section I: Packages .................................................................................................................129 Overview ..............................................................................................................................129 Benefits of Packages ............................................................................................................130 Package Structure .................................................................................................................132 Reference Package Elements and Cursors ...........................................................................135 Unqualified Package Elements.............................................................................................136 Access to Package Elements ................................................................................................137 Synchronize the Specification and the Body........................................................................141 Public vs. Private Data Elements .........................................................................................142 Do You Really Need the Package Body?.............................................................................146 Overloading Packages ..........................................................................................................147 Recommendations for Using Packages ................................................................................148 Security.................................................................................................................................149 Self Check ............................................................................................................................150

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download