Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

PL/SQL Stored Procedures
Oracle Tips by Burleson
 

A well-designed stored procedure is written to achieve one purpose and perform only the actions necessary to achieve that purpose. Procedures can accept and return values to their calling application with ease through the use of parameters. Table 1.1 shows stored procedure parameter types and functionality.

Table 1.1 The three types of procedure and function parameters.

Parameter Type Functionality
IN IN parameters pass a value to the procedure. This value cannot be modified by the procedure.
OUT OUT parameters are used to pass a value back to a calling block of PL/SQL code. The value in this parameter can never be read by the procedure.
IN OUT IN OUT parameters are used to pass a value to the procedure. This value can then be modified by the procedure and the resulting value passed back to the calling PL/SQL block.

Listing 1.9 presents a typical stored procedure. This procedure accepts a social security number as a parameter, queries the ENROLLED_COURSES table to determine the total number of credits and credit hours for the student, calculates the student’s grade point average by dividing the total number of credits by the total number of credit hours, and updates the student’s master record in the STUDENTS table.

Listing 1.9 A typical stored procedure.

PROCEDURE Calculate_GPA (nSSN IN     integer)

IS

   nOverallGPA     number  := 0;
   iSumCredits     integer := 0;
   iTotalHours     integer := 0;
   iLogicStep      integer := 0;

BEGIN
   SELECT sum (credit_hours),
          sum (decode (course_grade,
                       'A', 4,
                       'B', 3,
                       'C', 2,
                       'D', 1, 0))
   INTO   iTotalHours,
          iSumCredits
   FROM   ENROLLED_COURSES
   WHERE  ssn = nSSN;

   iLogicStep := 1;
   nOverAllGPA := iSumCredits / iTotalHours;

   iLogicStep := 2;
   UPDATE STUDENTS
   SET    overall_gpa = nOverAllGPA
   WHERE  ssn = nSSN;

EXCEPTION
   WHEN OTHERS THEN
        SYSTEM_LOG.Log_Error (obj_name  => 'Calculate_GPA',
                              obj_step  => iLogicStep,
                              ora_error => substr (SQLERRM, 1, 65));
END Calculate_GPA;

 

This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter