Practice 1 Creating a Physical Standby DatabaseFull description
Full description
PL/SQL Exam 1
Creating Database Connection
midDescripción completa
midFull description
Final Exam - Database Programming With SQLFull description
Descripción completa
Creating a Logical Standby DatabaseFull description
Deskripsi lengkap
This EBOOK will help you to Create a logical Standby Database... :)Full description
Leccion 4.3 PL SQL
Pronunciation Practice Activities
goodFull description
Pronunciation Practice Activities
Pronunciation Practice Activities
Pronunciation Practice ActivitiesFull description
Leccion 4.1 PL SQLDescripción completa
CSS frameworkFull description
Omnifocus tutorial
www.oracle.com/academy
Database Programming with PL/SQL 8-1: Creating Procedures Practice Activities Vocabulary Identify the vocabulary word for each definition below:
SUBPROGRAMAS
IS OR AS
Bloque anonimo
PROCEDURALES
Named PL/SQL blocks that are compiled and stored in the database.
Indicates the DECLARE section of a subprogram. Unnamed executable PL/SQL blocks that cannot be reused or stored in the database for later use. Named PL/SQL blocks that can accept parameters and are compiled and stored in the database.
Try It / Solve It 1. What What is th e difference betwee between n the fo llowi ng tw o pieces of co de? CODE SAMPLE A DECLARE v_empid emplo yees.empl yees.empl oyee_id%TYP oyee_id%TYPE E : = 100; v_perc ent_i ncrease nc rease NUMBER(2, NUMBER(2,2) 2) := .05; .05; BEGIN UPDATE employees SET salar y = (salary * v_perc ent_i ncrease) nc rease) + salar y employee_id employee_id = v_empid;
or its affiliates. Other names may be trademarks of their
END; CODE SAMPLE B CREATE PROCEDURE pay_raise (p_empi d emplo yees employee_id%TYPE, p_percent_in crease NUMBER) IS BEGIN UPDATE employees SET salary = (salary * p_percent_in crease) + salary WHERE employee_id = p_empid; END pay_rais e; El codigo B es un pro cedimiento almacenado 2. In your own words , list the benefits of subp rograms. -
Se puede reutilizar el códi go Es fácil el mantenimiento Contiene integridad de los datos
3. In your ow n words , descri be a stored pro cedure.
permite parámetros, se compila y se almacena en la base de datos
4. The remaining question s in t his pr actice use a copy of the employees table. Create the copy by executin g the fol lowing SQL st atement:
CREATE TABLE empl oyees_dup AS SELECT * fro m employees;
A. Use the code below to create a procedure in Applicatio n Express. Save the definition of your procedure in case you need to modify it later. In the “Sa ve SQL” popup, name your saved work “My name change procedure.”
B. Execute the procedure by running the following anonymous block:
BEGIN name_change; END; C. SELECT from the table to c heck that t he procedure has executed correctl y and performed the UPDATE.
select fir st_name from employees_dup wh ere department_id = 80;
5. Create a second procedure named pay_raise whic h ch anges t he salary of all employees in employees_dup to a new value of 30000. Execute the procedure from an anonymous b lock , then SELECT from th e table to check that th e procedure has executed corr ectly.
CREATE OR REPLACE PROCEDURE pay_raise IS BEGIN UPDATE empl oyees_dup SET salary = 30000; END; BEGIN pay_raise; END; 6. Retrieve your fir st name_change procedure by cli cking on it s name in the Saved SQL window . Modify t he code to remo ve OR REPLACE fro m the CREATE statement, and int roduce a deliberate error in to th e code, for example by miss pelling a keyword: UPDAT emplo yees_dup. Execute your code to recreate the proc edure. What happens?
Se muestra un mensaje que dice que el nom bre ya está siendo usado en un objeto ya exist ente 7. Now correct the procedure co de by reinsertin g th e OR REPLACE clause and correcti ng your deliberate spelling error. Execute your cod e 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:
if t he emplo yee is i n departm ent 80, the new salary = 1000 if t he emplo yee is i n departm ent 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 wi ll learn how to avoid this . Execute your pro cedure from an anonymou s bl ock and verif y that the updates have been perform ed correctly. CREATE OR REPLACE PROCEDURE r_sal IS BEGIN UPDATE employees_dup SET salary = 1000 WHERE department_id = 80; UPDATE employees_dup SET salary = 2000 WHERE department_id = 50; UPDATE employees_dup SET salary = 3000 WHERE depart ment_id NOT IN (50,80); END; BEGIN r_sal; END;
