What is the difference between function and procedure in PL SQL

Question

User · Answer

In the few words - function returns something  You can use function in SQL query  Procedure is part of code to do something with data but you can not invoke procedure from query  you have to run it in PL SQL block

User · Answer

Both stored procedures and functions are named blocks that reside in the database and can be executed as and when required   The major differences are    A stored procedure can optionally return values using out parameters  but can also be written in a manner without returning a value  But  a function must return a value  A stored procedure cannot be used in a SELECT statement whereas a function can be used in a SELECT statement    Practically speaking  I would go for a stored procedure for a specific group of requirements and a function for a common requirement that could be shared across multiple scenarios  For example  comparing between two strings  or trimming them or taking the last portion  if we have a function for that  we could globally use it for any application that we have

User · Answer

In dead simple way it makes this meaning   Functions     These subprograms return a single value  mainly used to compute and return a value   Procedure    These subprograms do not return a value directly  mainly used to perform an action   Example Program   CREATE OR REPLACE PROCEDURE greetings  BEGIN   dbms output put line  Hello World      END       Executing a Standalone Procedure    A standalone procedure can be called in two ways        Using the EXECUTE keyword     Calling the name of procedure from a PL SQL block   The procedure can also be called from another PL SQL block   BEGIN  greetings  END      Function   CREATE OR REPLACE FUNCTION totalEmployees  RETURN number IS total number 3     0  BEGIN  SELECT count    into total  FROM employees  RETURN total   END      Following program calls the function totalCustomers from an another block  DECLARE  c number 3   BEGIN  c    totalEmployees    dbms output put line  Total no  of Employees       c   END

User · Answer

The following are the major differences between procedure and function    Procedure is named PL SQL block which performs one or more tasks  where function is named PL SQL block which performs a specific action  Procedure may or may not return value where as function should return one value  we can call functions in select statement where as procedure we cant

User · Answer

A procedure does not have a return value  whereas a function has   Example   CREATE OR REPLACE PROCEDURE my proc     p name IN VARCHAR2     John   as begin     end  CREATE OR REPLACE FUNCTION my func     p name IN VARCHAR2     John   return varchar2 as begin     end   Notice how the function has a return clause between the parameter list and the  as  keyword  This means that it is expected to have the last statement inside the body of the function read something like   return my varchar2 local variable     Where my varchar2 local variable is some varchar2 that should be returned by that function

User · Answer

A function can be in-lined into a SQL statement  e g   select foo        fn bar  foo    from foobar   Which cannot be done with a stored procedure   The architecture of the query optimiser limits what can be done with functions in this context  requiring that they are pure  i e  the same inputs always produce the same output    This restricts what can be done in the function  but allows it to be used in-line in the query if it is defined to be  pure    Otherwise  a function  not necessarily deterministic  can return a variable or a result set   In the case of a function returning a result set  you can join it against some other selection in a query   However  you cannot use a non-deterministic function like this in a correlated subquery as the optimiser cannot predict what sort of result set will be returned  this is computationally intractable  like the halting problem

User · Answer

we can call a stored procedure inside stored Procedure Function within function  StoredProcedure within function but we can not call function within stored procedure  we can call function inside select statement  We can return value from function without passing output parameter as a parameter to the stored procedure    This is what the difference i found  Please let me know if any

[sql] What is the difference between function and procedure in PL/SQL?

Examples related to sql

Examples related to stored-procedures

Examples related to function

Examples related to plsql