12. Stored Procedure

A Stored Procedure is a set of SQL statements that performs an operation or sequence of operations. Think of it as a complete program stored on the database server.

Example Use Case: Automated Order Processing

A procedure might be used to finalize an order

1. Update the Inventory table (UPDATE).

2. Insert a new record into the Sales table (INSERT).

3. Commit the transaction.

Oracle PL/SQL Stored Procedure

CREATE OR REPLACE PROCEDURE Finalize_Order ( ... )

• Purpose: Defines a stored procedure in Oracle.

• CREATE OR REPLACE: Creates a new procedure or replaces an existing one with the same name.

• Finalize_Order: The name of the stored procedure.

• (...): The parameter list—used to pass input values into the procedure.

CREATE OR REPLACE PROCEDURE Finalize_Order(

p_product_id   IN NUMBER,
p_quantity     IN NUMBER,
p_customer_id  IN NUMBER

)

• Each line defines a parameter:

  • p_product_id: product being sold.

  • p_quantity: how many units are sold.

  • p_customer_id: the buyer’s ID.

• IN → means data flows into the procedure (input-only parameter).

• NUMBER → defines the datatype.

IS

IS

• Marks the start of the procedure body in PL/SQL.

• Similar to saying “beginning of declaration and code section.”

• Use SET SERVEROUTPUT ON; in SQL Developer or SQL*Plus to see the output.

---

BEGIN

BEGIN

• Marks the start of executable statements.

• All SQL logic (update, insert, etc.) goes between BEGIN and END.

• Use SET SERVEROUTPUT ON; in SQL Developer or SQL*Plus to see the output.

---

 Update Inventory

UDATE Inventory

SET quantity = quantity - p_quantity

WHERE product_id = p_product_id;

• Reduces stock quantity by the amount sold.

• Inventory → table name.

• SET quantity = quantity - p_quantity → subtracts ordered quantity from available stock.

• WHERE product_id = p_product_id → ensures only that product’s row is updated.

---

 Insert Sale Record

INSERT INTO Sales(sales_id, product_id, customer_id, 

quantity, sale_date)

VALUES (sales_id.NEXTVAL, p_product_id, 

p_customer_id, p_quantity, SYSDATE);

• Adds a new row into the Sales table for this transaction.

• sales_seq.NEXTVAL → uses a sequence named sales_seq to generate a unique sale ID automatically.

• SYSDATE → system date/time (when the sale happens).

---

Commit Transaction

COMMIT;

• Makes the changes permanent in the database (update + insert).

• Without COMMIT, changes wouldn’t persist after the procedure ends.

COMMIT;

---

Success Message

DBMS_OUTPUT.PUT_LINE('Order finalized successfully.');

• Prints a message to the SQL console (for debugging/confirmation).

• DBMS_OUTPUT is an Oracle package for displaying messages.

• Use SET SERVEROUTPUT ON; in SQL Developer or SQL*Plus to see the output.

DBMS_OUTPUT.PUT_LINE('Order finalized successfully.');

Full code:

CREATE OR REPLACE PROCEDURE Finalize_Order(

p_product_id   IN NUMBER,
p_quantity     IN NUMBER,
p_customer_id  IN NUMBER

)

IS
BEGIN
  -- Step 1 : Update Inventory
  UDATE Inventory
  SET quantity = quantity - p_quantity
  WHERE product_id = p_product_id;
  -- Step 2 : Insert Sales Values 
  INSERT INTO Sales(sales_id, product_id,
 customer_id, quantity, sale_date)
  VALUES (sales_id.NEXTVAL, p_product_id,
 p_customer_id, p_quantity, SYSDATE);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Order finalized successfully.');
END Finalize_Order;

To Run it: 

SET SERVEROUTPUT ON;
EXEC Finalize_Order(101, 2, 501);

Fl Key Characteristics

• Purpose: To execute a sequence of DML (INSERT, UPDATE, DELETE) or DDL (CREATE, ALTER) commands, or complex retrieval tasks.

• Return Value: Can return zero, one, or multiple result sets (tables of data). It can also return a single numeric value (an exit code or status code) to indicate success or failure.

• Execution: Executed using the EXEC or EXECUTE command.

• Transactional Control: Procedures can manage transactions (using BEGIN TRAN, COMMIT, ROLLBACK), making them ideal for ensuring multi-step processes either fully succeed or completely fail.