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; CODE SAMPLE B CREATE PROCEDURE pay_raise (p_empid employees employee_id\%TYPE, p_percent_increase NUMBER) IS BEGIN UPDATE employees SET salary =( salary ppercent_increase )+salary WHERE employee_id = p_empid; END pay_raise; Copyright 2020, Oracle andior its attliabes. AI nights reserved. Oracle and Java are registered thademarks of Cracie andior its attliabes. Other names may be trademarks of their respective owners. 2. In your own words, list the benefits of subprograms. 3. In your own words, describe a stored procedure. 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. 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. 6. etrieve 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? 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? 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