Difference between an Oracle function and Procedure

A procedure is a set of PL/SQL commands stored in the database with a name. A procedure is similar to a function because both are stored in the database. A procedure, however, is more versatile and can accomplish more. A procedure is a named set of PL/SQL commands stored in the database, and while it shares similarities with a function, there are key differences that make procedures more versatile. Procedures are designed to perform actions and can include a variety of PL/SQL statements like

1. inserts,
2. updates,
3. deletes,
4. or complex logic.

They don’t necessarily return a value, though they can use OUT parameters to pass data back to the caller. Functions, on the other hand, are primarily designed to compute and return a single value (though in some cases they can return collections). They are typically used in expressions or queries. A procedure's versatility comes from its ability to handle multiple tasks, manipulate data in various ways, and interact with the database without being constrained to returning a single result. For example, a procedure could 1) update multiple tables, 2) log an action, and 3) send a notification while a function is more limited in scope.

Oracle7 introduced stored procedures written in PL/SQL, the proprietary language of Oracle for writing application logic. These procedures are stored in the database and executed by clients issuing remote procedure calls (RPCs) as opposed to executing SQL statements. Instead of issuing multiple SQL calls, occasionally with intermediate logic to accomplish a task, the client issues one procedure call, passing in the required parameters. The database executes all the required SQL and logic using the parameters it receives. Stored procedures can also shield the client logic from internal changes to the data structures or program logic. As long as the parameters the client passed in and received back do not change, no changes are required in the client software. Stored procedures move a portion of the application logic from the client to the database server. By doing so, stored procedures can reduce the network traffic considerably. This capability increases the scalability of two-tier systems.
The following table outlines the similarities and differences between procedures and functions.
Procedure vs. Function

Attribute	Procedure	Function
Stored in the database and sharable	Yes	Yes
Able to insert, update, and delete data	Yes	No
Accepts parameters	Yes	Yes
Able to return zero, one, or more than one value	Yes	No
Required to return one value	No	Yes

You usually create a function when you want to perform some complex calculations and then use the calculations in other sub-programs or in SQL commands. You usually create a procedure when you need to make changes to data within the sub-program. The function has limitations that the procedure does not, so there are more instances in which a procedure is appropriate.

The main difference between an Oracle PL/SQL block and a stored procedure lies in persistence, reusability, and how they are executed. Let’s break it down:
1) PL/SQL Block
A PL/SQL block is a piece of procedural code that is anonymous and not stored in the database permanently. It is executed immediately and is often used for one-time executionand
Characteristics of a PL/SQL Block:

• Anonymous: It does not have a name and is not stored in the database.
• Execution: It is executed once and disappears after execution.
• Structure:
  • DECLARE (optional) → Variable and cursor declarations
  • BEGIN → Main execution logic
  • EXCEPTION (optional) → Error handling
  • END; → Block termination
• Common Use Case: Used in SQL*Plus, scripts, or inside triggers.

Example:

DECLARE
    v_message VARCHAR2(50) := 'Hello, PL/SQL Block!';
BEGIN
    DBMS_OUTPUT.PUT_LINE(v_message);
END;
/

- This block runs immediately and does not persist.
2) Stored Procedure
A stored procedure is a named, compiled, and stored PL/SQL program unit inside the Oracle database. It can be executed multiple times and is used to encapsulate business logic.
Characteristics of a Stored Procedure:

• Named and Stored: It is stored permanently in the database.
• Reusable: It can be executed multiple times using EXEC or called in other programs.
• Supports Parameters: It can accept IN, OUT, or IN OUT parameters.
• Compiled Once: It is compiled and stored, making execution faster.
• Security and Privileges: Access can be restricted using GRANT statements.

Example:

CREATE OR REPLACE PROCEDURE greet_user (p_name VARCHAR2) AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END;
/

- This procedure can be executed as:

EXEC greet_user('John');

Key Differences

Feature	PL/SQL Block	Stored Procedure
Persistence	Temporary	Stored in DB
Naming	Anonymous	Has a name
Compilation	Compiled at runtime	Compiled once, stored in DB
Execution	Runs once, disappears	Can be executed multiple times
Performance	Slower (recompiles each time)	Faster (precompiled)
Use Case	One-time execution, debugging	Encapsulating logic, automation
Parameter Support	No parameters	Supports IN, OUT, IN OUT

When to Use Which?

• Use a PL/SQL block when you need a quick one-time script, debugging, or simple operations.
• Use a stored procedure when you need reusable, optimized, and structured business logic.

Here's a breakdown of how to pass WHERE conditions dynamically within PL/SQL stored procedures, along with explanations, examples, and best practices:
Core Technique: Dynamic SQL
The fundamental technique involves using Dynamic SQL, which means building your SQL query as a string and executing it at runtime. Here's the general structure:

PROCEDURE dynamic_where_proc (
   p_where_clause IN VARCHAR2,
   ... -- Other parameters if needed
) IS
   v_sql_stmt VARCHAR2(2000);
BEGIN
   v_sql_stmt := 'SELECT * FROM my_table WHERE ' || p_where_clause; 

   EXECUTE IMMEDIATE v_sql_stmt; 
   -- Or use EXECUTE IMMEDIATE ... INTO ... to fetch into variables
END;

Example

PROCEDURE update_values (
    p_column_name IN VARCHAR2,
    p_new_value   IN NUMBER,
    p_condition   IN VARCHAR2
) IS
    v_sql_stmt VARCHAR2(2000);
BEGIN
    v_sql_stmt := 'UPDATE my_table SET ' || p_column_name 
	|| ' = :new_val WHERE ' || p_condition;

    EXECUTE IMMEDIATE v_sql_stmt USING p_new_value;
END;

How to Call:

update_values('salary', 15000, 'employee_id = 101');

Key Considerations and Best Practices

• SQL Injection Vulnerabilities: Dynamic SQL is susceptible to SQL Injection if you don't sanitize input. To mitigate this:
  • Use bind variables for parameter substitution (see example).
  • Thoroughly validate any user-supplied input.
• Performance: Dynamic SQL can have overhead compared to static SQL. Consider the following if performance is crucial:
  • Are there a limited number of known WHERE variations? You might be able to use static SQL with IF/ELSEIF conditions.
  • If absolutely necessary, explore `DBMS_SQL` for more complex dynamic execution.
• Testing: Thoroughly test stored procedures using dynamic SQL with various inputs to ensure correct behavior and prevent security issues.

Additional Tips

• Error Handling: Include robust error handling using `EXCEPTION` blocks.
• Ref Cursors: For dynamic queries returning multiple rows, use `EXECUTE IMMEDIATE ... INTO` with a ref cursor to process the result set.

In the next lesson, you will learn how to create a procedure.