Compare the Difference Between Similar Terms

Difference Between

Home / Technology / IT / Database / Difference Between Stored Procedure and Function

Difference Between Stored Procedure and Function

November 16, 2011 Posted by Admin

Stored Procedure vs Function
 

Stored procedures and functions are two types of programming blocks. Both of them must have calling names. Those calling names are used to call them inside another programming block like procedures functions and packages or SQL queries. Both of these object types accept parameters and perform the task behind those objects. This is the syntax (in ORACLE) to create a stored procedure,

 create or replace procedure procedurename (parameters)

 as

 begin

     statements;

 exception

     exception_handling

 end;

And here is the syntax to create a function (in ORACLE),

create or replace function function_name (parameters)

return return_datatype

as

begin

statements;

return return_value/variable;

exception;

exception_handling;

end;

Stored Procedures

As mentioned above stored procedures are named programming blocks. They accept parameters as user input and process according to the logic behind the procedure and give the result (or perform a specific action). These parameters can be IN, OUT and INOUT types. Variable declarations, variable assignments, control statements, loops, SQL queries and other functions/procedure/package calls can be inside the body of procedures.

Functions

Functions also are named programming blocks, which must return a value using RETURN statement, and before it returns a value, its body performs some actions too (according to the given logic). Functions also accept parameters to run. Functions can be called inside the queries. When a function is called inside a SELECT query, it applies to each row of the result set of the SELECT query. There are several categories of ORACLE functions. They are,

  • Single row functions (returns a single result for each and every row of the query)

There are sub categories of a single row functions.

  • Numeric function (Ex : ABS, SIN, COS)
  • Character function (Ex: CONCAT, INITCAP)
  • Date time function (Ex: LAST_DAY, NEXT_DAY)
  • Conversion functions (Ex: TO_CHAR, TO_DATE)
  • Collection function (Ex: CARDINALITY, SET)
  • Aggregate functions (Returns a single row, based on a group of rows. Ex: AVG, SUM, MAX)
  • Analytic  functions
  • Object reference functions
  • Model functions
  • User defined functions

 

What is the difference between function and Stored Procedure?

• All functions must return a value using RETURN statement. Stored procedures do not return values using RETURN statement. RETURN statement inside a procedure will return its control to the calling programme. OUT parameters can be used to return values from stored procedures.

• Functions can be called inside the queries, but stored procedures cannot be used inside the queries.

• RETURN data type must be included to create a function, but in stored procedure DDL, it is not. 

 

Related posts:

Difference Between View and Stored Procedure Difference Between Triggers and Stored Procedures Difference Between MS SQL Server 2008 and 2008 R2 Difference Between DBMS and RDBMS Difference Between ODBC and ADO

Filed Under: Database Tagged With: Function, function vs, Functions, functions vs, Oracle functions, stored procedure, stored procedure in oracle, stored procedure vs, stored procedures, stored procedures vs

About the Author: Admin

Coming from Engineering cum Human Resource Development background, has over 10 years experience in content developmet and management.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Request Article

Featured Posts

Difference Between Coronavirus and Cold Symptoms

Difference Between Coronavirus and Cold Symptoms

Difference Between Coronavirus and SARS

Difference Between Coronavirus and SARS

Difference Between Coronavirus and Influenza

Difference Between Coronavirus and Influenza

Difference Between Coronavirus and Covid 19

Difference Between Coronavirus and Covid 19

You May Like

Difference Between HTC 10 and iPhone 6S

Difference Between HTC 10 and iPhone 6S

Difference Between Temporal Arteritis and Trigeminal Neuralgia

Difference Between Temporal Arteritis and Trigeminal Neuralgia

What is the Difference Between Gel and Paper Electrophoresis

What is the Difference Between Gel and Paper Electrophoresis

Difference Between Liner and Shader Tattoo Gun

Difference Between Physiological and Pathological Jaundice

Difference Between Physiological and Pathological Jaundice

Latest Posts

  • What is the Difference Between Glutaric Acidemia Type 1 and 2
  • What is the Difference Between Allopurinol and Colchicine
  • What is the Difference Between Neurogenic and Vascular Claudication
  • What is the Difference Between Hepatocellular Carcinoma and Cholangiocarcinoma
  • What is the Difference Between Medulla Oblongata and Spinal Cord
  • What is the Difference Between CHF and Pulmonary Edema
  • Home
  • Vacancies
  • About
  • Request Article
  • Contact Us

Copyright © 2010-2018 Difference Between. All rights reserved. Terms of Use and Privacy Policy: Legal.