Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

Oracle Functions and Procedures

By Saurabh Gupta | on June 7, 2011 |
Oracle 11g Tutorials

Encapsulation and modularity are the few of the generic and recommended language features, which enhance efficiency and scalability of the language in the application. Oracle database language implements these features through stored subprograms. These subprograms (or subroutines) are the program units which hold a business or computational component and can be invoked at any junction in the application. This promotes code re-usability and logical sampling of code.

In Oracle, subprograms can be of two types; namely, Procedures and Functions. These are named PL/SQL blocks, which physically occupy disk space. They can be called from other program units, packages and anonymous PL/SQL blocks in database. These subprograms can also be declared locally to a PL/SQL block. In such cases, the subprogram carries the scope of its parent block only.

In this tutorial, we shall study the architecture, usage and benefits of Oracle subprograms in schema and real time applications.

Subprograms

As discussed above, subprograms are the named PL/SQL blocks, whose definition consists of Specification and body. The ‘specification’ or header is the mandatory part which contains subprogram info like name, prototype and parameter list. And the ‘body’ is optional part that contains the variable declarations and programmable logic.

Subprograms can accept list of parameters, which can be used within its body. Parameters can be passed in two modes i.e. Pass by Value and Pass by Reference.

Oracle identifies two types of subprograms in a schema, Procedures and Functions.

Procedures and functions have the same structure and physical architecture. But they both achieve different motives. While procedures are meant for the outlining a process or functional logic in the application, functions are meant for computational tasks. Besides this, they have few usage differences, which we shall discuss later in this tutorial.

Properties of subprograms

Before going in details of each subprogram, we shall study the similar properties of schema subprograms. Both procedures and functions follow the below listed features.

Subprogram Namespace

Namespace is a category of the schema objects, which uniquely identifies their name. The schema objects which fall in a category must have a unique name. Procedures and functions share their namespace with tables, views, materialized views, sequences, packages, and object types. This implies that a procedure and a table cannot have same name.

Example [1]: A function F_GET_ID is available as standalone function in the schema. User attempts to create a table with the same name. Oracle identifies the violation and prevents such action by raising ORA-0955 error.

SQL> CREATE TABLE F_GET_ID( a NUMBER);
CREATE TABLE F_GET_ID( a NUMBER)
             *
ERROR at line 1:
ORA-00955: name IS already used BY an existing object

Other namespace available is the one which can hold unique names for Indexes, triggers, constraints, db links, and clusters.

Storing subprogram metadata

Oracle schema contains multiple dictionary views to hold the metadata of all the objects created within the schema. The metadata of the Oracle subprograms is contained with two views i.e. USER_OBJECTS and USER_PROCEDURES

USER_OBJECTS holds metadata of all the existing schema objects, while USER_PROCEDURES holds the metadata for subprograms i.e. procedures and functions. Refer the screen dumps.

A procedure P_UPD_SALARY is queried in USER_PROCEDURES and USER_OBJECTS respectively. Note the columns in the views; schema level columns in USER_OBJECTS and object level columns in USER_PROCEDURES.

Removal of subprograms

Obsolete functions and procedures can be removed from the database using DROP command. Note that DROP is a DDL command and carries the feature of ‘Auto Commit’.

Syntax [1]

DROP [PROCDURE | FUNCTION] [SUBPROGRAM NAME]

Example [2]

SQL> DROP PROCEDURE P_GET_PART;
 
PROCEDURE dropped.
 
SQL> DROP FUNCTION F_GET_ID;
 
FUNCTION dropped.

Passing parameters in a subprogram

Parameters can be passed to a subprogram in three distinct modes. These modes can be ‘pass by value’ and ‘pass by reference’. Oracle implements the passage of parameters in the form of IN, OUT and IN OUT modes. While IN mode is way to pass parameters as ‘Pass by Reference’, OUT and IN OUT modes follow ‘Pass by Value’ method.

Syntax [2]

CREATE [OR REPLACE] PROCEDURE [NAME]
(PARAMETER1 [IN | OUT | IN OUT] [DATA TYPE] [DEFAULT],
 PARAMETER2 [IN | OUT | IN OUT] [DATA TYPE] [DEFAULT],
…
PARAMETER N)

I shall list the key differences between the two passage methods.

Calling subprograms

Before the release of Oracle 11g version, there used to be two methods of subprogram call i.e. Positional or Named. Oracle 11g has introduced hybrid method of calling subprograms, which is known as Mixed notation. Mixed notation allows making use of both positional and named methods of subroutine call. This enhances the feature flexibility and proves quite useful when a subprogram has mandatory placed at the start and optional parameters at the end.

However, we shall have look at all the three notations of subprogram call as below.

Positional Notation – Every parameter appears at the same position as that in the prototype.

Example [3a]

SELECT FUN_TEST(1,2) FROM DUAL
Named Notation – Parameters can be passed in jumbled order by explicitly specifying the actual parameter along with its value.

Example [3b]

SELECT FUN_TEST(b=>2, a=>1) FROM DUAL
Mixed Notation – Oracle 11g opened the gates for the mixed usage of positional and named notation.

Example [3c]

SELECT FUN_TEST(1,b=>2) FROM DUAL

Oracle Procedures: The Business Managers

A procedure is a named PL/SQL block which is used to implement business logic within the application. As its name suggests, it is used to embed an active process or business logic in the application. For this reason, it would be worth referring them as ‘Business Development Manager’ in Oracle.

It can optionally accept list of parameters and amazingly, it can even return values to the host environment through the same parameter list. Though it is not really meant for returning values, but at times, their primary objective can be extended to return values also.

Syntax [3]: The user must possess CREATE ANY PROCEDURE privilege to create a new procedure.

CREATE [OR REPLACE] PROCEDURE [NAME][(parameter list)]
[IS | AS]
[Declarative SECTION]
BEGIN
[Executable SECTION]
END [PROCEDURE name];

In the syntax, REPLACE keyword preserves the privileges associated with the procedure, if the procedure is dropped and recreated.

Example [4] – Every employee’s salary has to be incremented based on his job cadre

CREATE OR REPLACE PROCEDURE P_UPD_SALARY (P_EMPID NUMBER)
IS
CURSOR C IS
   SELECT JOB_ID
   FROM EMPLOYEES
   WHERE EMPLOYEE_ID = P_EMPID;
L_JOBID VARCHAR2(10);
L_INC_SAL NUMBER := 0;
BEGIN
OPEN C;
FETCH C INTO L_JOBID;
CLOSE C;
IF L_JOBID = 'MGR' THEN
   L_INC_SAL := 1000;
ELSIF L_JOBID = 'DEV' THEN
   L_INC_SAL := 800;
ELSIF L_JOBID = 'ANL' THEN
   L_INC_SAL := 500;
ELSE L_INC_SAL := 200;
END IF;
UPDATE EMPLOYEES
SET SALARY = SALARY + L_INC_SAL
WHERE EMPLOYEE_ID = P_EMPID;
END;
/
 
PROCEDURE created.

Execution of procedures

Procedures can be executed in two ways

Call within a PL/SQL block as a PL/SQL construct

The screen dump below executes the above procedure P_UPD_SALARY to recalculate the salary of employee id 100. Note that it calls the procedure within a BEGIN…END block.

The ‘Pass by Reference’ parameters can be passed as actual or as local variables. Any ‘Pass by Value’ mode parameter can be declared in DECLARE section and captured after the procedure call.

In SQL* Plus, using EXECUTE command

EXECUTE command in SQL*plus is used to invoke a subprogram. It is analogous to the previous execution.

Parameters can be declared as bind variables in the session and used during the procedure call.

Functions: The real workers

Functions are the schema objects, which are used for the implementation of computational tasks and functions. They optionally accept parameters and mandatorily return a definite value.

Usage Notes:

  • Function must have one logical RETURN statement
  • Similar to procedure, functions can be invoked from other subprograms or anonymous PL/SQL blocks
  • Functions are callable from SELECT statements, provided they should not modify the database purity state

Syntax [5]

CREATE OR REPLACE FUNCTION [FUNCTION NAME] [PARAMETERS]
RETURN [RETURN TYPE]
IS
[DECLARATIVE SECTION]
BEGIN
[EXECUTABLE SECTION]
END;

Example [5] – The function below calculates number of employees which match the input criteria. The criterion is based on their Job function and salary.

CREATE OR REPLACE FUNCTION F_SEARCH_EMP (P_SAL NUMBER,
						     P_JOBID VARCHAR2)
RETURN NUMBER
IS
   L_COUNT NUMBER := 0;
BEGIN
   SELECT COUNT(*) 
   INTO L_COUNT
   FROM EMPLOYEES 
   WHERE SALARY = P_SAL
   AND JOB_ID = P_JOBID;
 
   RETURN NVL(L_COUNT,0);
END F_SEARCH_EMP;
/
 
FUNCTION created.

Execution of Functions

Functions can be executed in similar way as that of a procedure. They can be invoked from any other subprogram, or anonymous block.

Calling function within a PL/SQL block

Execution in SQL* Plus. Note the use of bind variables

Using functions in SQL expressions

Functions can be invoked from the SQL statement expressions. A valid stated function can be called in WHERE clause condition. In such situations, certain conditions must be satisfied, as listed below.

  • All the parameters must be IN mode. Parameters should be of SQL supported type (no BOOLEAN or COLLECTION type)
  • Parameters and return output must be of SQL supported data type
  • User must have EXECUTE privilege on the function

Example [6]: The function F_COUNT_STR counts the appearance of a string (search string) in another string (parent string). Note its call in the SELECT query.

CREATE OR REPLACE FUNCTION F_COUNT_STR (P_STR VARCHAR2, 
						     P_SRCH_STR VARCHAR2)
RETURN NUMBER
IS
   L_COUNT NUMBER;
BEGIN
   SELECT REGEXP_COUNT(P_STR,P_SRCH_STR)
   INTO L_COUNT
   FROM DUAL;
 
RETURN L_COUNT;
END;
/
 
FUNCTION created.
 
SQL> SELECT EMPLOYEE_ID, EMPLOYEE_NAME
  2  FROM EMPLOYEES
  3  WHERE F_COUNT_STR(EMPLOYEE_NAME,'A') > 0
  4  /
 
EMPLOYEE_ID EMPLOYEE_N
----------- ----------
        110 KATE
        140 LARRY

Calling functions from SELECT statement

Functions can also be selected from the SELECT statements, but with utter alert and care. Functions must ensure the database purity level. Below are the purity rules which must be obeyed at any instant, when calling a function from SELECT statement.

  • When a function is called from a SELECT statement or a parallel UPDATE or DELETE statement, the function cannot update database tables.
  • When a function is called from an UPDATE or DELETE statement, the function cannot query or modify database tables modified by that statement.
  • When a function is called from a SELECT, INSERT, UPDATE, or DELETE statement, the function cannot execute directly or indirectly through another subprogram or SQL transaction control statements

Example [7]: The function F_UPD_SALARY updates the salary of a given employee by some amount.

CREATE OR REPLACE FUNCTION F_UPD_SALARY( P_EMPID NUMBER,
						      P_INC_SAL NUMBER)
RETURN NUMBER 
IS
BEGIN
   UPDATE EMPLOYEES
   SET SALARY = SALARY + P_INC_SAL
   WHERE EMPLOYEE_ID=P_EMPID;
   RETURN 1;
END;

Now, the below screen dump shows the testing the above function by calling it in a SELECT statement.

 

Thus, these rules must be recapitulated and followed to ensure the purity level of the database.

« « How to Build Professional Rapport
Oracle 11g Exception Handling » »

Author Description

Avatar

Free Training

RSSSubscribe 394 Followers
  • Popular
  • Recent
  • Oracle 11g SecureFiles

    September 4, 2011 - 0 Comment
  • Manage Oracle dependencies

    August 16, 2011 - 0 Comment
  • Oracle 11g Virtual Columns

    May 30, 2011 - 0 Comment
  • UTL_FILE

    August 10, 2011 - 0 Comment
  • Oracle 11g Invisible Index

    May 27, 2011 - 0 Comment
  • Compound Triggers in Oracle 11g

    May 9, 2011 - 0 Comment
  • Oracle 11g Collections

    June 2, 2011 - 0 Comment
  • Oracle VPD implementation

    October 24, 2011 - 0 Comment
  • Oracle 11g Result Cache

    April 28, 2011 - 0 Comment
  • PL/Scope and PL/SQL Hierarchical Profiler

    November 6, 2011 - 0 Comment
  • Oracle XML Storage

    June 19, 2012 - 0 Comment
  • Oracle Analytic Enhancements

    June 19, 2012 - 0 Comment
  • Oracle Partitioning Overview

    January 17, 2012 - 0 Comment
  • PL/Scope and PL/SQL Hierarchical Profiler

    November 6, 2011 - 0 Comment
  • Oracle VPD implementation

    October 24, 2011 - 0 Comment
  • Oracle 11g SecureFiles

    September 4, 2011 - 0 Comment
  • Manage Oracle dependencies

    August 16, 2011 - 0 Comment
  • UTL_FILE

    August 10, 2011 - 0 Comment
  • Oracle Pragma

    July 6, 2011 - 0 Comment
  • Conditional compilation in Oracle PL/SQL

    June 28, 2011 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • Oracle XML Storage
  • Oracle Analytic Enhancements
  • Oracle Partitioning Overview
  • PL/Scope and PL/SQL Hierarchical Profiler
  • Oracle VPD implementation

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT