Oracle

A stored procedure in Oracle is a set of SQL statements that you can save and reuse. Stored procedures are beneficial because they allow you to encapsulate complex business logic into a single database object. This can help in improving performance and security by reducing the need to send complex SQL queries from the client to the server. Once a stored procedure is created, it can be called from applications or other PL/SQL blocks.

Here’s a brief overview of how to create and use stored procedures in Oracle:

1. Creating a Stored Procedure

   • Use the CREATE PROCEDURE statement to define a new stored procedure.
   • Specify the procedure name and the SQL statements you want to execute.

   CREATE OR REPLACE PROCEDURE get_book_details (p_book_id IN NUMBER) IS
     v_title books.title%TYPE;
     v_author books.author%TYPE;
   BEGIN
     SELECT title, author
     INTO v_title, v_author
     FROM books
     WHERE book_id = p_book_id;
   
     DBMS_OUTPUT.PUT_LINE('Title: ' || v_title);
     DBMS_OUTPUT.PUT_LINE('Author: ' || v_author);
   END get_book_details;
   

2. Executing a Stored Procedure

   • You can execute a stored procedure using the EXECUTE command or by calling it from a PL/SQL block.

   EXECUTE get_book_details(1);
   

   Or within a PL/SQL block:

   BEGIN
     get_book_details(1);
   END;
   

3. Parameters in Stored Procedures

   • Stored procedures can accept parameters, which can be used to pass data into the procedure.
   • Parameters can be IN, OUT, or IN OUT to handle input, output, or both.

   CREATE OR REPLACE PROCEDURE update_book_author (
     p_book_id IN NUMBER,
     p_new_author IN VARCHAR2
   ) IS
   BEGIN
     UPDATE books
     SET author = p_new_author
     WHERE book_id = p_book_id;
   END update_book_author;
   

4. Handling Exceptions

   • You can handle exceptions within stored procedures to manage errors gracefully.

   CREATE OR REPLACE PROCEDURE delete_book (
     p_book_id IN NUMBER
   ) IS
   BEGIN
     DELETE FROM books
     WHERE book_id = p_book_id;
   
     COMMIT;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE('No book found with ID ' || p_book_id);
     WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
   END delete_book;
   

5. Privileges and Security

   • Ensure that the appropriate privileges are granted to users who need to execute or modify the stored procedures.

   GRANT EXECUTE ON get_book_details TO user_name;
   

By using stored procedures, you can modularize your database operations, making them easier to manage and more efficient.