What is the difference between procedures and functions in PL/SQL? When would you use each?

**Procedures**:

- Do not return values (use OUT parameters instead)

- Can have multiple OUT parameters

- Cannot be called from SQL statements

- Used for operations that modify data (DML)

- Syntax: CREATE PROCEDURE name(param IN/OUT type) AS BEGIN ... END;

**Functions**:

- Must return a single value (RETURN statement)

- Can have only IN parameters

- Can be called from SQL statements

- Should be pure (no DML operations)

- Syntax: CREATE FUNCTION name(param IN type) RETURN type AS BEGIN ... RETURN value; END;

**When to use Procedures**:

- Operations that modify data (INSERT, UPDATE, DELETE)

- Need to return multiple values (use OUT parameters)

- Complex business logic with multiple steps

- Example: process_order, update_inventory, transfer_money

**When to use Functions**:

- Calculations that return single value

- Need to use in SQL SELECT statements

- Pure operations (no side effects)

- Example: calculate_discount, calculate_tax, format_phone_number

**Example**:

```sql

-- Procedure: Modifies data, multiple OUT parameters

CREATE OR REPLACE PROCEDURE transfer_money(

p_from_account IN NUMBER,

p_to_account IN NUMBER,

p_amount IN NUMBER,

p_transaction_id OUT NUMBER,

p_status OUT VARCHAR2

) AS

BEGIN

-- DML operations

UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_from_account;

UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to_account;

COMMIT;

p_status := 'SUCCESS';

END;