Oracle Stored Procedure Example

Introduction

I thought I would put together a quick post with some Oracle stored procedure examples.  I wanted some results to compare when writing similar queries/programs in other languages.  I plan to use the same NFL games data set with MapReduce, R, SparkR and other programs.

Prerequisites

Need to set up Oracle tables and load data – Information below.

Using SQL Loader / Oracle XE

  1. Load the data set from the Data Science Examples blog post “Oracle SQL Loader Example – NFL 2016 Games” into an Oracle database.

Using a different Oracle instance

  1. zip – zip file with all data and scripts for 2016 NFL games Oracle examples. Zip file can be obtain from the following Github repository:

https://github.com/mndatascienceexamples/datascienceexamples

  1. zip – extract zip file to c:\ drive. For consistency, I will place the extracted files in c:\Oracle_2016NFLSeason directory.  C:\Oracle_2016NFLSeason will be the working directory for the Oracle SQL Loader Example.
  2. Open Windows command prompt and change directory to C:\Oracle_2016NFLSeason
  3. Run Oracle SQL client and login.
  4. Create Oracle NFL Game tables by running the following script

@createNFLOraTables.sql

Create Oracle stored procedures for validating and loading NFL Game data from staging table to transaction table.

@nflDataLoad.pks
@nflDataLoad.pkg

Load Stored Procedure

From the Oracle SQL prompt, load NFLGames stored procedures

@nflGames.pks
@nflGames.pkg

Executing Oracle Stored Procedures

To run these examples, the first thing you will need to do is turn on server output.  These stored procedures will write results to the server’s console.  At the SQL command prompt, type

set serveroutput on;

To execute one of these stored procedures, type “exec nflgames.”, followed by the stored procedure name.  For example,

exec nflgames.road_team_record;

General NFLGames Stored Procedure Notes

  • There are five sets of statistics
    • Team Record
    • Team Points Scored
    • Team Points Allowed
    • Team Turnover Ratio
    • Team Record After First Quarter
  • Each set of statistics will have four stored procedures
    • Wrapper procedure
    • Three other stored procedures to generate results

Team Record

Team record computes a team’s road, home or both records.  A wrapper procedure (TEAM_RECORD) is used with a character input parameter of ‘R’, ‘H’ or ‘A’.  (If an input parameter is not specified, then ‘A’ will be used by default.)

Team Point Scored

Team points scored computes a team’s road, home or both points scored.  A wrapper procedure (TEAM_POINTS_SCORED) is used with a character input parameter of ‘R’, ‘H’ or ‘A’.  (If an input parameter is not specified, then ‘A’ will be used by default.)

Team Point Allowed

Team points allowed computes a team’s road, home or both points allowed.  A wrapper procedure (TEAM_POINTS_ALLOWED) is used with a character input parameter of ‘R’, ‘H’ or ‘A’.  (If an input parameter is not specified, then ‘A’ will be used by default.)

Team Turnover Ratio

Team turnover ratio computes a team’s road, home or both turnover ratio.  A wrapper procedure (TEAM_BEST_TO_RATIO) is used with a character input parameter of ‘R’, ‘H’ or ‘A’.  (If an input parameter is not specified, then ‘A’ will be used by default.)

Team Record after First Quarter

Team record after first quarter stored procedures computes a team’s road, home or both record when leading, trailing or tied after first quarter.  A wrapper procedure (TEAM_BEST_TO_RATIO) is used with a character input parameter of ‘A’, ‘B’ or ‘T’.  (If an input parameter is not specified, then ‘A’ will be used by default.)

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: