Oracle Procedures.

Preamble

Oracle Procedure is a subprogram that performs a specific action.

Learn how to create and delete procedures in Oracle / PLSQL with syntax and examples.

CREATE PROCEDURE

As with other programming languages, you can create your own procedures in Oracle.

Syntax

CREATE [OR REPLACE] PROCEDURE name_procedures
[ (parameter [, parameter, ...]) ] IS
[local ads]
BEGIN
executable offers
[EXCEPTION
exception handlers]
END [procedure_name];

When creating a procedure or function, you can define three types of parameters that can be declared:

• IN – A parameter can refer to a procedure or function. The value of the parameter cannot be changed by the procedure or function.
• OUT – The parameter cannot refer to a procedure or function, but the parameter value can be changed by the procedure or function.
• IN OUT – The parameter can reference the procedure or function and the parameter values can be changed by the procedure or function.

Example
Let’s look at an example of how to create a procedure in Oracle.

CREATE OR REPLACE Procedure UpdateCourse
( name_in IN varchar2 )
IS
cnumber number;

cursor c1 is
SELECT course_number
FROM courses_tbl
WHERE course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;
end if;

INSERT INTO student_courses
( course_name,
course_number )
VALUES
( name_in,
cnumber );

commit;

close c1;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE|' -ERROR- '||SQLERRM);
END;

DROP PROCEDURE

Once you have created your Oracle procedure, you may need to remove it from the database.

Syntax for deleting the procedure in Oracle

DROP PROCEDURE procedure_name;

• procedure_name – is the name of the procedure you want to delete.

Example: .
Let’s take a look at an example of how to remove a procedure in Oracle.

DROP PROCEDURE UpdateCourse;

In this example, we remove a procedure called UpdateCourse.

About Enteros

​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​