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:
Named PL/SQL blocks that are compiled and stored in the

SUBPROGRAMAS database.
IS OR AS Indicates the DECLARE section of a subprogram.
Unnamed executable PL/SQL blocks that cannot be

Bloque anonimo reused or stored in the database for later use.
Named PL/SQL blocks that can accept parameters and are

PROCEDURALES 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;
Copyright © 2016, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/
respective owners.
2
or its affiliates. Other names may be trademarks of their
END;
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;
El codigo B es un procedimiento almacenado
2. In your own words, list the benefits of subprograms.
- Se puede reutilizar el código

- Es fácil el mantenimiento
- Contiene integridad de los datos
3. In your own words, describe a stored procedure.
permite parámetros, se compila y se almacena en la base de datos
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;
Copyright © 2016, 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.
3
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 from employees_dup where department_id = 80;
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
SET salary = 30000;
END;
BEGIN
pay_raise;
END;
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?
Se muestra un mensaje que dice que el nombre ya está siendo usado en un

objeto ya existente
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?
Funciona
8. Create, save, and execute a procedure which updates the salary of employees in
employees_dup according to the following rules:
respective owners.
4
- 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 r_sal IS

BEGIN
SET salary = 1000
SET salary = 2000
SET salary = 3000
WHERE department_id NOT IN (50,80);
END;
BEGIN
r_sal;
END;
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

What are subprograms and what are their benefits?

What are subprograms and what are their benefits?

What is a stored procedure?

What is a stored procedure?

www.oracle.

Database Programming with PL/SQL

Named PL/SQL blocks that are compiled and stored in the

IS OR AS Indicates the DECLARE section of a subprogram.

Unnamed executable PL/SQL blocks that cannot be

Named PL/SQL blocks that can accept parameters and are

or its affiliates. Other names may be trademarks of their

CREATE PROCEDURE pay_raise

El codigo B es un procedimiento almacenado

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

- Se puede reutilizar el código

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

permite parámetros, se compila y se almacena en la base de datos

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:

select first_name from employees_dup where department_id = 80;

CREATE OR REPLACE PROCEDURE pay_raise IS

Se muestra un mensaje que dice que el nombre ya está siendo usado en un

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

CREATE OR REPLACE PROCEDURE r_sal IS

You might also like