Creating a PL/SQL Stored Procedure for execute a regular tasks

PL/SQL Stored Procedure creationProblem

There is a Oracle database task that you are performing on a regular basis. Rather than executing a script that contains lines of PL/SQL code each time you execute the task, you want to store the code in the database so that you can simply execute the task by name or so that you can schedule it to execute routinely via Oracle Scheduler.



Place the code that is used to perform your task within a stored procedure. The following example creates a procedure named INCREASE_WAGE to update the employee table by giving a designated employee a pay increase. Of course, you will need to execute this procedure for each eligible employee in your department. Storing the code in a procedure makes the task easier to perform.

                                           pct_increase IN NUMBER,
                                           upper_bound IN NUMBER) AS

  emp_count    NUMBER := 0;
  emp_sal      employees.salary%TYPE;
  Results   VARCHAR2(50);


    SELECT salary
    INTO emp_sal
    FROM employees
    WHERE employee_id = empno_in; 

    IF emp_sal < upper_bound
    AND round(emp_sal + (emp_sal * pct_increase), 2) < upper_bound THEN

        UPDATE employees
        SET salary = round(salary + (salary * pct_increase),2)
        WHERE employee_id = empno_in;

        results := 'SUCCESSFUL INCREASE';



    END IF;


    RAISE_APPLICATION_ERROR(-20001, 'No employee match for the given ID');


The following are the results from executing the procedure for employee number 198. In the example, the employee is being given a 3 percent increase and an upper bound of $5,000.





Statement processed.


How It Works

In the example, the procedure accepts three parameters: the employee number, the percent of increase they will receive, and an upper salary bound. You can then invoke the procedure by name, passing in the required parameters.

The procedure first searches the database for the provided employee number. If a record for that employee is found, then the employee record is queried for the current salary. If the salary is less than the upper bound and the resulting new salary will still be less than the upper bound, then the increase will be applied via an UPDATE statement. If the employee is not found, then an alert message will be displayed. As you can see, this procedure can be called for any individual employee, and it will increase their wage accordingly as long as the increase stays within the bound.

Stored procedures can be used to encapsulate functionality and store code in the database data dictionary. Much like a function, they can accept zero or more values as parameters, including collections. A stored procedure is structured in much the same way as a function in that it includes a header, an executable section, and an optional exception-handling block. However, a procedure cannot include a RETURN clause in the header, and it does not return a value.

For example, in the solution to this recipe, the procedure contains the following header:

                                           pct_increase IN NUMBER,
                                           upper_bound IN NUMBER) AS


The header uses the OR REPLACE clause to indicate that this procedure should replace any procedure with the same name that already exists. The procedure accepts three parameters, and although all of them are NUMBER type, any datatype can be accepted as a parameter. The declaration section comes after the header, and any cursors, variables, or exceptions that need to be declared should be taken care of in that section. Next, the actual work that the procedure will do takes place between the BEGIN and END keywords. Note that the header does not contain a RETURNS clause since procedures cannot return any values.

The advantage of using procedures is that code can be encapsulated into a callable named routine in the data dictionary and can be called by many users. To create a procedure in your schema, you must have the CREATE PROCEDURE system privilege. You can create a stored procedure in another schema if you have the CREATE ANY PROCEDURE system privilege.



Вас заинтересует / Intresting for you:

PL/SQL package: Collecting Rel...
PL/SQL package: Collecting Rel... 648 views sepia Sat, 01 Dec 2018, 10:54:57
Describe the Basic PL/SQL Prog...
Describe the Basic PL/SQL Prog... 889 views Андрей Волков Wed, 12 Sep 2018, 15:23:39
Why Oracle DBAs learn PL/SQL a...
Why Oracle DBAs learn PL/SQL a... 866 views Андрей Волков Wed, 12 Sep 2018, 14:43:12
Translating PL/SQL to JavaScri...
Translating PL/SQL to JavaScri... 1370 views Гвен Sun, 03 Jun 2018, 11:54:40