Answer bc a procedure is a named plsql block that can

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: values, control execution, and return values to the calling environment. Answer: B,C A procedure is a named PL/SQL block that can accept parameters (sometimes referred to as arguments), and be invoked. Generally speaking, you use a procedure to perform an action. A procedure has a header, a declaration section, an executable section, and an optional exception-handling section. A procedure can be compiled and stored in the database as a schema object. Procedures promote reusability and maintainability. When validated, they can be used in any number of applications. If the requirements change, only the procedure needs to be updated. WWW.ExamMagic.COM - 40: Examine this code: CREATE OR REPLACE PROCEDURE add_dept ( p_name departments.department_name%TYPE DEFAULT 'unknown', p_loc departments.location_id%TYPE DEFAULT 1700) IS BEGIN INSERT INTO departments(department_id, department_name, loclation_id) VALUES(dept_seq.NEXTVAL,p_name, p_loc); END add_dept; / You created the add_dept procedure above, and you now invoke the procedure in SQL *Plus. Which four are valid invocations? (Choose four) A. EXECUTE add_dept(p_loc=>2500) B. EXECUTE add_dept('Education', 2500) C. EXECUTE add_dept('2500', p_loc =>2500) D. EXECUTE add_dept(p_name=>'Education', 2500) E. EXECUTE add_dept(p_loc=>2500, p_name=>'Education') Answer: A,B,C,E Explanation: 1Z0-147 A. This statement correctly uses named notation. A Default value is defined for both parameters in the procedure so it is not necessary to pass any parameters. B. This statement correct uses positional notation. C. This statement correctly uses mixed notation. E. This statement correctly uses named notation. Incorrect Answers D. When using mixed notation to pass the values, all the parameters specified with positional notation must precede the parameters specified with named notation in the subprogram call. If the parameters specified with positional notation do not precede the parameters specified with named notation, the following error is generated at run time: PLS-00312: a positional parameter association may not follow a named WWW.ExamMagic.COM - 41: Which three are valid ways to minimize dependency failure? (Choose three) A. Querying with the SELECT * notification. B. Declaring variables with the %TYPE attribute. C. Specifying schema names when referencing objects. D. Declaring records by using the %ROWTYPE attribute. E. Specifying package.procedure notation while executing procedures. Answer: A,B,D Explanation: A. Use the SELECT * notation. This will minimize recompilation failures because SELECT * will select all the columns of the table. If you add or remove any column from the table, the SELECT statement will not be impacted by the change in the table and will require no changes to the program unit. B, D. Use the %ROWTYPE to declare records and %TYPE to declare variables. This allows you to declare records and variables that inherit the data types of the underlying columns of the base tables, rather than defining the data type of each variable in the program unit. The %ROWTYPE and %TYPE attributes provide you the flexibility to change the data types of the columns of the table without having to modify the data types specified in the program unit. 1Z0-147 WWW.ExamMagi c.COM - 42: Which two does the INSTEAD OF clause in a trigger identify? (Choose two) A. The view associated with the trigger. B. The table associated with the trigger. C. The event associated with the trigger. D. The package associated with the trigger. E. The statement level or for each row association to the trigger. Answer: A,C Explanation: Answer A is correct. You must specify the View. INSTEAD OF TRIGGERS are created on views to allow DML statements on an on-updateable view. Answer C is correct. An INSETED OF Trigger can fire for all three Triggering Events (INSERT, UPDATE and UPDATE). Answer B is incorrect INSTEAD of Triggers can only be created for Views they can't be created on Tables. Answer D is incorrect. You can't call a package. Answer E is incorrect. INSTEAD OF TRIGGERS must always fire FOR EACH ROW; STATEMENT Level Triggers are not valid for Views WWW.ExamMagic.COM - 43: Examine this package: CREATE OR REPLACE PACKAGE manage_emps IS tax_rate CONSTANT NUMBER(5,2) := .28; v_id NUMBER; PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER); PROCEDURE delete_emp; PROCEDURE update_emp; FUNCTION calc_tax (p_sal NUMBER) RETURN NUMBER; END manage_emps; / CREATE OR REPLACE PACKAGE BODY manage_emps IS PROCEDURE update_sal (p_raise_amt NUMBER) IS 1Z0-147 BEGIN UPDATE emp SET sal = (sal * p_raise_emt) + sal WHERE empno = v_id; END; PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER) IS BEGIN INSERT INTO emp(empno, deptno, sal) VALYES(v_id, p_depntno, p_sal); END insert_emp; PROCEDURE delete_emp IS BEGIN DELETE FROM emp WHERE empno = v_id; END delete_emp; PROCEDURE update_emp IS v_sal NUMBER(10, 2); v_raise NUMBER(10, 2); BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = v...
View Full Document

This note was uploaded on 01/05/2011 for the course COMS 131 taught by Professor Dad during the Spring '10 term at University of Mumbai.

Ask a homework question - tutors are online