What's the Key Difference Between PL/SQL Procedures and Functions?

Distinguishing Function and Procedure in PL/SQL

PL/SQL, like many other programming languages, provides mechanisms for encapsulating code into modular units known as procedures and functions. While these constructs share similarities, they exhibit a crucial difference: their ability to return values.

A procedure is a subroutine that performs specific tasks without explicitly returning a value. It primarily serves to modify input parameters, manipulate data, or execute operations. Consider the following example:

CREATE OR REPLACE PROCEDURE my_proc
(p_name IN VARCHAR2 := 'John')
AS
BEGIN
  -- Do something with p_name
END;

In contrast, a function is a subroutine that, in addition to performing tasks, also returns a value. It accepts parameters like a procedure, but it is required to have a return clause. The return value is assigned to the variable that calls the function. Here's an example:

CREATE OR REPLACE FUNCTION my_func(p_name IN VARCHAR2 := 'John') RETURN VARCHAR2 AS
BEGIN
  -- Do something with p_name
  RETURN 'Hello, ' || p_name;
END;

Note the inclusion of the RETURN clause after the parameter list in the function. The last statement in the function body typically assigns a value to the local variable that will be returned, such as my_varchar2_local_variable in this example.

The above is the detailed content of What's the Key Difference Between PL/SQL Procedures and Functions?. For more information, please follow other related articles on the PHP Chinese website!