Creating Stored Procedures from Sets of SQL in Oracle

As a developer, we often find ourselves with complex sets of SQL statements that need to be executed as a single unit. In such cases, creating stored procedures or functions can greatly simplify our workflow and improve maintainability.

In this article, we’ll explore how to create stored procedures from sets of SQL in Oracle using the CREATE OR REPLACE PROCEDURE statement. We’ll also delve into the concept of PL/SQL (Procedural Language/Structured Query Language), which is used for creating stored procedures and functions.

Introduction to PL/SQL

PL/SQL is a procedural language designed for managing relational databases, particularly Oracle Database. It’s a hybrid language that combines elements of SQL and procedural languages like C++ or Java. With PL/SQL, you can create complex applications with reusable code blocks, known as stored procedures and functions.

Creating Stored Procedures in Oracle

To create a stored procedure in Oracle, you use the CREATE OR REPLACE PROCEDURE statement. Here’s an example:

-- Create or replace a procedure named 'do_some_work'
create or replace procedure do_some_work
as
begin
  -- Paste your code here;
end;

Notice that we’re using the AS keyword to specify the beginning of the procedure body. The BEGIN keyword marks the start of the code block, and the END; statement signals its end.

Running Stored Procedures in Oracle

To run a stored procedure, you use the EXECUTE statement:

-- Run the 'do_some_work' procedure
EXECUTE do_some_work;

You can also pass input parameters to a stored procedure using the following syntax:

-- Create or replace a procedure named 'add_numbers'
create or replace procedure add_numbers(p_num1 in integer, p_num2 in integer)
as
begin
  -- Add two numbers and return the result
  dbms_output.put_line(p_num1 + p_num2);
end;

To run this procedure with input parameters, use the following statement:

-- Run the 'add_numbers' procedure with input parameters
EXECUTE add_numbers(5, 3);

Creating Stored Procedures from Sets of SQL

Now that we’ve covered the basics of creating and running stored procedures in Oracle, let’s explore how to create a stored procedure from a set of SQL statements.

Suppose you have a complex set of SQL statements like this:

-- Create table 'test' as a copy of 'test1'
create table test as select * from test1;

DELETE FROM test WHERE e_id LIKE '%00-01';

MERGE INTO test tgt USING (
  SELECT 
    LISTAGG(e_val, ':') WITHIN GROUP(
      ORDER BY 
        e_id
    ) OVER(PARTITION BY unique_id, line) e_val, 
    CASE WHEN e_id IN ('BHT03-02', 'BHT03-03') THEN 'Y' ELSE 'N' END del 
  FROM 
    test 
  WHERE 
    e_id IN (
      'ABC03-01'
    )
) src ON (
  tgt.unique_id = src.unique_id 
  AND tgt.line = src.line 
  AND tgt.e_id = src.e_id
)
WHEN MATCHED THEN 
UPDATE 
SET 
  tgt.e_value = src.e_value DELETE 
WHERE 
  src.del = 'Y';

To create a stored procedure from this set of SQL statements, you can enclose the code in a CREATE OR REPLACE PROCEDURE statement like this:

-- Create or replace a procedure named 'do_complex_work'
create or replace procedure do_complex_work
as
begin
  -- Paste your complex SQL code here;
end;

Here’s an example of what the complete stored procedure might look like:

-- Create or replace a procedure named 'do_complex_work'
create or replace procedure do_complex_work
as
begin
  begin
    create table test as select * from test1;

    delete from test where e_id like '%00-01';

    merge into test tgt using (
      SELECT 
        listagg(e_val, ':') within group(
          order by 
            e_id
        ) over(partition by unique_id, line) e_val, 
        case when e_id in ('BHT03-02', 'BHT03-03') then 'Y' else 'N' end del 
      from 
        test 
      where 
        e_id in (
          'ABC03-01'
        )
    ) src on (
      tgt.unique_id = src.unique_id 
      and tgt.line = src.line 
      and tgt.e_id = src.e_id
    )
    when matched then 
    update 
    set 
      tgt.e_value = src.e_value delete 
    where 
      src.del = 'Y';
  exception
    when other then
      dbms_output.put_line('Error: ' || sqlerrm);
  end;
end;

Best Practices for Creating Stored Procedures

Here are some best practices to keep in mind when creating stored procedures:

1. Keep it concise: Avoid including unnecessary code or comments that may make the procedure harder to understand.
2. Use meaningful names: Choose descriptive and consistent naming conventions for your procedures, variables, and packages.
3. Use comments effectively: Include relevant comments to explain complex logic or calculations.
4. Test thoroughly: Run unit tests and integration tests to ensure the procedure works as expected.
5. Avoid SQL injection: Use parameterized queries or prepared statements to prevent SQL injection vulnerabilities.

Conclusion

Creating stored procedures from sets of SQL in Oracle is a valuable skill for any developer. By understanding how to use PL/SQL, create and run stored procedures, and follow best practices, you can simplify your workflow, improve maintainability, and write more efficient code.

Last modified on 2023-06-04