B functions allow calculations on data across a

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: he DELETE statement (in the WHERE clause), and in the INSERT statement (in the VALUES clause). B. Functions allow calculations on data across a database link. C. Functions can be used to manipulate these data types. WWW.ExamMagic.COM - 53: Examine the trigger: CREATE OR REPLACE TRIGGER Emp_count AFTER DELETE ON Emp_tab FOR EACH ROW DELCARE n INTEGER; BEGIN SELECT COUNT(*) INTO n FROM Emp_tab; DBMS_OUTPUT.PUT_LINE(' There are now ' || a || ' employees,'); END; This trigger results in an error after this SQL statement is entered: DELETE FROM Emp_tab WHERE Empno = 7499; How do you correct the error? A. Change the trigger type to a BEFORE DELETE. B. Take out the COUNT function because it is not allowed in a trigger. C. Remove the DBMS_OUTPUT statement because it is not allowed in a trigger. D. Change the trigger to a statement-level trigger by removing FOR EACH ROW. Answer: D Explanation: A mutating table is a table against which a data manipulation statement has been issued and the corresponding trigger on the DML statement is reading from the same table, at the same time. Mutating tables are not valid for statement triggers because statement triggers fire only once for each event and allow the process to complete before the trigger is actually fired. Row triggers can www.ExamMagic.com 1Z0-147 cause a table to mutate because row triggers fire for each row. To correct this problem you change the trigger to a Statement-Level Trigger by removing FOR EACH ROW or by specifying FOR EACH STATEMENT. Incorrect Answers A. This will still result in an error and it will not achieve the intended result. B. You may use the COUNT function in a Trigger. C. The DBMS_OUTPUT statement is allowed in a Trigger . WWW.ExamMagic.COM - 54: What is true about stored procedures? A. A stored procedure uses the DELCLARE keyword in the procedure specification to declare formal parameters. B. A stored procedure is named PL/SQL block with at least one parameter declaration in the procedure specification. C. A stored procedure must have at least one executable statement in the procedure body. D. A stored procedure uses the DECLARE keyword in the procedure body to declare formal parameters. Answer: C Explanation: The executable section must contain at least one executable statement. You can include a NULL keyword to fulfill the need to have at least one executable statement in this section. For example, BEGIN NULL; END Incorrect Answers A. Local variables are declared after the IS or AS keyword and before the BEGIN keyword. The DECLARE keyword is not used. B. It is not required that a Procedure have a parameter. D. You do not use the DELCARE keyword when specifying parameters. The syntax for creating a procedure is: CREATE [OR REPLACE] PROCEDURE [parameter1 [mode1] datatype1, parameter2 [mode2] datatype2, . . .)] IS | AS ... www.ExamMagic.com 1Z0-147 BEGIN ... EXCEPTION WWW.ExamMagi c.COM - 55: Examine this code: CREATE OR REPLACE PROCEDURE insert_dept (p_location_id NUMBER) IS v_dept_id NUMBER(4); BEGIN INSERT INTO departments VALUES (5, 'Education', 150, p_location_id); SELECT department_id INTO v_dept_id FROM employees WHERE employee_id=99999; END insert_dept; / CREATE OR REPLACE PROCEDURE insert_location ( p_location_id NUMBER, p_city VARCHAR2) IS BEGIN INSERT INTO locations(location_id, city) VALUES (p_location_id, p_city); insert_dept(p_location_id); END insert_location; / You just created the departments, the locations, and the employees table. You did not insert any rows. Next you created both procedures. You new invoke the insert_location procedure using the following command: EXECUTE insert_location (19, 'San Francisco') What is the result in thisEXECUTE command? A. The locations, departments, and employees tables are empty. B. The departments table has one row. The locations and the employees tables are empty. C. The location table has one row. The departments and the employees tables are empty. D. The locations table and the departments table both have one row. The employees table is empty. www.ExamMagic.com 1Z0-147 Answer: A Explanation: All of the tables are empty. When the following statement executed: SELECT department_id INTO v_dept_id FROM employees WHERE employee_id = 9999; An error is thrown and there is no exception section in this procedure or the calling procedure, therefore all transactions are rolled back. Note: If the exception is not handled in the called procedure, the control is transferred to the exception-handling section of the calling procedure. If the exception is handled, all the statements remain intact. If the exception is not handled in the calling procedure's exception-handling section, all the statements are rolled back, and the exception propagates to the calling environment. Incorrect Answers B. This is not true because an error is occurs on the SELECT Statement and the INSERT Transaction is rolled back when the error is encountered. C. The insert into the location table is also rolled back. The insert_locaton calls the insert_dept Stored Procedure, this procedure fails and control is passed back to the calling procedure which does not have an exception section , therefore both the inse...
View Full Document

Ask a homework question - tutors are online