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. The following table outlines the similarities and differences between procedures and functions.

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.
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.

In Oracle databases, both PL/SQL blocks and stored procedures are used to execute PL/SQL code, which is Oracle's procedural extension to SQL. However, they differ in their scope, usage, and characteristics:

1. PL/SQL Block:
   • A PL/SQL block is the basic unit of a PL/SQL program. It can be an anonymous block that is not stored in the database and does not have a name. It is compiled each time it is executed.
   • PL/SQL blocks can be written and executed in SQL*Plus (a command-line tool for interacting with Oracle Database) or in any integrated development environment (IDE) that supports Oracle.
   • A PL/SQL block contains three sections: a declaration section (optional) where variables and constants are defined, an execution section (mandatory) where the PL/SQL code is placed, and an exception-handling section (optional) to handle any runtime errors.
   • Since it's not stored in the database, an anonymous PL/SQL block needs to be sent to the database server every time it is executed, which can increase network traffic and reduce performance for complex and frequently used operations.
2. Stored Procedure:
   • A stored procedure is a named PL/SQL block that is stored in the database and can be reused. Once compiled, its compiled form is stored in the database, which can improve performance as it does not need to be recompiled each time it is executed.
   • Stored procedures are created with the `CREATE PROCEDURE` statement and can be executed with the `CALL` statement or by referencing their name in PL/SQL blocks, other stored procedures, and triggers.
   • Stored procedures can accept parameters, which makes them more flexible than anonymous blocks. These parameters can be input (IN), output (OUT), or both (IN OUT).
   • Stored procedures are part of the database schema, and they enhance code reusability, maintainability, and security. They can encapsulate complex business logic and can be invoked by applications connected to the database.

In summary, the key differences lie in how they are stored, their reusability, and their intended use. Anonymous PL/SQL blocks are useful for executing ad-hoc queries and operations, whereas stored procedures are better suited for operations that are complex, reused frequently, and require performance optimization. Stored procedures also provide better encapsulation and abstraction of business logic within the database.

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.