Database Programming With PL/SQL 8-1: Practice Activities: Creating Procedures

www.oracle.
com/academy
Database Programming with PL/SQL

8-1: Creating Procedures Practice
Activities
Vocabulary
Identify the vocabulary word for each definition below:
Subprograms Named PL/SQL blocks that are compiled and stored in the
database.
Indicates the DECLARE section of a subprogram.
Anonymous Blocks Unnamed executable PL/SQL blocks that cannot be reused or

stored in the database for later use.
Procedures Named PL/SQL blocks that can accept parameters and are
compiled and stored in the database.
Try It / Solve It
1. What is the difference between the following two pieces of code?
CODE SAMPLE A
DECLARE
v_empid employees.employee_id%TYPE := 100;
v_percent_increase NUMBER(2,2) := .05;
BEGIN
UPDATE employees
SET salary = (salary * v_percent_increase) + salary
WHERE employee_id = v_empid;
END;
Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their
respective owners.
2
Code A is an anonymous block which is an unnamed block of code.

- Is not stored in the database
- Complied each time it is used
- Cannot return values
- Cannot take parameteres
CODE SAMPLE B
CREATE PROCEDURE pay_raise

(p_empid employees employee_id%TYPE,
p_percent_increase NUMBER)
IS
BEGIN
UPDATE employees
SET salary = (salary * p_percent_increase) + salary
WHERE employee_id = p_empid;
END pay_raise;
Code B is a subprogram named pay_raise.

- Complied once and stored in the database
- Invoked by name in applications
- Can have parameters passed to it
- Can return values
2. In your own words, list the benefits of subprograms.

- help keep the code simple, and easy to maintain
- improve readability of code
- improve data security and data integrity
3. In your own words, describe a stored procedure.

A procedure is a named subprogram that is compiled and stored in the database as an object in a
schema. It can accept parameters and return values. A procedure performs an action and can be re-
used.
respective owners.
3
4. The remaining questions in this practice use a copy of the employees table. Create the copy by
executing the following SQL statement:
CREATE TABLE employees_dup AS SELECT * from employees;
A. Use the code below to create a procedure in Application Express. Save the definition of your
procedure in case you need to modify it later. In the “Save SQL” popup, name your saved work
“My name change procedure.”
CREATE OR REPLACE PROCEDURE name_change IS

BEGIN
UPDATE employees_dup
SET first_name = 'Susan'
WHERE department_id = 80;
END name_change;
B. Execute the procedure by running the following anonymous block:
BEGIN
name_change;
END;
C. SELECT from the table to check that the procedure has executed correctly and performed the
UPDATE.
SELECT first_name, department_id FROM employee_dup;
respective owners.
4
5. Create a second procedure named pay_raise which changes the salary of all employees in
employees_dup to a new value of 30000. Execute the procedure from an anonymous block, then
SELECT from the table to check that the procedure has executed correctly.
CREATE OR REPLACE PROCEDURE pay_raise IS
BEGIN
UPDATE employee_dup
SET SALARY = 30000;
END pay_raise;
BEGIN
Pay_raise;
END;
SELECT employee_id, salary FROM employee_dup;
6. Retrieve your first name_change procedure by clicking on its name in the Saved SQL window.
Modify the code to remove OR REPLACE from the CREATE statement, and introduce a
deliberate error into the code, for example by misspelling a keyword: UPDAT employees_dup.
Execute your code to recreate the procedure. What happens?
CREATE PROCEDURE name_change IS
BEGIN
UPDAT employees_dup
SET first_name = ‘Susan’
END pay_raise;
An ORA-00955 error is returned because the procedure is already stored in the database
respective owners.
5
7. Now correct the procedure code by reinserting the OR REPLACE clause and correcting your
deliberate spelling error. Execute your code to recreate the procedure. Now what happens?
The execution is successful and the modified procedure is recreated.
8. Create, save, and execute a procedure which updates the salary of employees in
employees_dup according to the following rules:
- if the employee is in department 80, the new salary = 1000

- if the employee is in department 50, the new salary = 2000
- if the employee is in any other department, the new salary = 3000.
You will need to include three UPDATE statements, one for each of the above rules. In a later
lesson you will learn how to avoid this. Execute your procedure from an anonymous block and
verify that the updates have been performed correctly.
CREATE OR REPLACE PROCEDURE sal_change IS
BEGIN
SET salary = 1000
SET salary = 2000
SET salary = 3000
WHERE department_id NOT IN (80, 50);
END sal_change;
BEGIN
Sal_change;
END;
SELECT employee_id, department_id, salary FROM employees_dup;
respective owners.

Database Programming With PL/SQL 8-1: Practice Activities: Creating Procedures

Uploaded by

Copyright:

Available Formats

Database Programming With PL/SQL 8-1: Practice Activities: Creating Procedures

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Database Programming With PL/SQL 8-1: Practice Activities: Creating Procedures

Uploaded by

Copyright:

Available Formats

www.oracle.

Database Programming with PL/SQL

Indicates the DECLARE section of a subprogram.

Anonymous Blocks Unnamed executable PL/SQL blocks that cannot be reused or

Code A is an anonymous block which is an unnamed block of code.

CREATE PROCEDURE pay_raise

Code B is a subprogram named pay_raise.

2. In your own words, list the benefits of subprograms.

- improve readability of code

- improve data security and data integrity

3. In your own words, describe a stored procedure.

CREATE TABLE employees_dup AS SELECT * from employees;

CREATE OR REPLACE PROCEDURE name_change IS

B. Execute the procedure by running the following anonymous block:

The execution is successful and the modified procedure is recreated.

- if the employee is in department 80, the new salary = 1000

You might also like