• Author / Uploaded
• Dorin Chioibas

Citation preview

Test: Section 8 Quiz Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer. Section 8 Quiz (Answer all questions in this section) 1. A programmer creates a PL/SQL subprogram which is compiled and stored in the database. Two separate users then execute an application which invokes this subprogram four times. How many times must the subprogram be recompiled? Mark for Review (1) Points Two times Eight times Zero times (*) Four times One time

Correct

Correct

2. A programmer wants to create a PL/SQL procedure named EMP_PROC. What will happen when the following code is executed? CREATE OR REPLACE PROCEDURE emp_proc IS v_salary employees.salary%TYPE; BEGIN SELECT salary INTO v_salary FROM employees WHERE employee_id = 999; DBMS_OUTPUT.PUT_LINE('The salary is: ' || v_salary); END; Mark for Review (1) Points The statement will fail because you cannot declare variables such as v_salary inside a procedure. The statement will raise a NO_DATA_FOUND exception because employee_id 999 does not exist. The procedure will be created successfully. (*)

The statement will fail because the last line of code should be END emp_proc; The statement will fail because the procedure does not have any parameters.

Correct

Correct

3. Which of the following keywords MUST be included in every PL/SQL procedure definition? (Choose three.) Mark for Review (1) Points (Choose all correct answers) REPLACE IS or AS (*) END (*) BEGIN (*) DECLARE

Correct

Correct

4. The following are the steps involved in creating, and later modifying and re-creating, a PL/SQL procedure in Application Express. Which step is missing? Type the procedure code in the SQL Commands window Click on the "Save" button and save the procedure code Retrieve the saved code from "Saved SQL" in SQL Commands Modify the code in the SQL Commands window Execute the code to re-create the procedure Mark for Review (1) Points Invoke the procedure from an anonymous block Enter parameters and data type Execute the procedure from USER_SOURCE data dictionary view Execute the code to create the procedure (*)

Correct

Correct

5. Which of the following are benefits of using PL/SQL subprograms rather than anonymous blocks? (Choose three.) Mark for Review (1) Points (Choose all correct answers) Do not need to declare variables Easier code maintenance (*) Better data security (*) Faster performance (*) Easier to write

Correct

Correct

Page 1 of 3 Next

Summary

Test: Section 8 Quiz Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer. Section 8 Quiz (Answer all questions in this section) Review (1) Points

6.

What are the three parameter modes for procedures?

IN, OUT, IN OUT (*) COPY, NOCOPY, REF R(ead), W(rite), A(ppend) CONSTANT, VARIABLE, DEFAULT

Mark for

Correct

Correct

7. Three IN parameters for procedure ADD_EMPLOYEE are defined as: (p_name VARCHAR2 , p_salary NUMBER := 1000, p_hired DATE DEFAULT SYSDATE) The procedure is invoked by: add_employee('Jones'); What is the value of P_SALARY when the procedure starts to execute? Mark for Review (1) Points NULL The call will fail because P_SALARY is a required parameter 1000 (*) The procedure will not compile because P_SALARY should have been coded as DEFAULT 1000

Correct

Correct

8. Which of the following statements about IN OUT parameters are true? (Choose two.) Mark for Review (1) Points (Choose all correct answers) The data type for the parameter must be VARCHAR2. The parameter value can be returned as a new value that is set within the procedure. (*) The parameter value passed into the subprogram is always returned unchanged to the calling environment. The parameter value can be returned as the original unchanged value. (*)

Correct

Correct

be listed? (1) Points

9. When creating a procedure, where in the code must the parameters Mark for Review

Before the procedure name After the keyword IS or AS After the procedure name (*) After the keyword PROCEDURE

Correct

Correct

10. The following procedure has been created: CREATE OR REPLACE PROCEDURE defproc (A IN NUMBER := 50, B IN NUMBER, C IN NUMBER DEFAULT 40) IS ..... Which one of the following will invoke the procedure correctly? Mark for Review (1) Points defproc(30, 60 => C); defproc(40, 70); (*) defproc; defproc(10 => A, 25 => C); defproc(30 => A);

Correct Previous

Correct Page 2 of 3 Next

Summary

Test: Section 8 Quiz Review your answers, feedback, and question scores below. An asterisk (*) indicates

a correct answer. Section 8 Quiz (Answer all questions in this section) 11. Which of the following best describes the difference between a parameter and an argument? Mark for Review (1) Points A parameter is a variable that accepts a value that is passed to it, while an argument is the value that is passed. (*) A parameter is the name of a variable, while an argument is the datatype of that variable. They are both names of variables. A parameter is passed into the procedure, while an argument is passed out of the procedure. There is no difference; parameters and arguments are the same thing.

Correct

Correct

12. You have created procedure MYPROC with a single parameter PARM1 NUMBER. Now you want to add a second parameter to the procedure. Which of the following will change the procedure successfully? Mark for Review (1) Points ALTER PROCEDURE myproc ADD (parm2 NUMBER); CREATE OR REPLACE PROCEDURE myproc (parm1 NUMBER, parm2 NUMBER); (You do not need to repeat the detailed code of the procedure, only the header) REPLACE PROCEDURE myproc (parm1 NUMBER, parm2 NUMBER) IS BEGIN ... CREATE OR REPLACE PROCEDURE myproc (parm1 NUMBER, parm2 NUMBER) IS BEGIN ... (*) The procedure cannot be modified. Once a procedure has been created, the number of parameters cannot be changed.

Correct

Correct

13. What is the correct syntax to create procedure MYPROC that accepts two number parameters X and Y? Mark for Review (1) Points CREATE PROCEDURE (x NUMBER, y NUMBER) myproc IS ... CREATE PROCEDURE myproc (x NUMBER, y NUMBER) IS ... (*) CREATE PROCEDURE myproc IS (x NUMBER, y NUMBER) ... CREATE PROCEDURE IS myproc (x NUMBER, y NUMBER) �

Correct

Correct

14. Procedure TESTPROC accepts one parameter P1, whose value is up to 1000 characters in length. Which one of the following declares this parameter correctly? Mark for Review (1) Points CREATE PROCEDURE testproc DECLARE p1 VARCHAR2(100); BEGIN .... CREATE PROCEDURE testproc (p1 VARCHAR2)

IS BEGIN .... (*)

CREATE PROCEDURE testproc p1 VARCHAR2

IS BEGIN ....

CREATE PROCEDURE testproc IS

p1 VARCHAR2(100); BEGIN .... CREATE PROCEDURE testproc (p1 VARCHAR2(100) )

IS BEGIN ....

Incorrect

Incorrect. Refer to Section 8 Lesson 2.

15. Which one of the following statements about formal and actual parameters is true? Mark for Review (1) Points Formal and actual parameters must have the same name. An actual parameter is declared within the called procedure. Formal and actual parameters must have different names. A formal parameter is declared within the called procedure, while an actual parameter is declared in the calling environment. (*)

Correct

Correct

Previous

Page 3 of 3 Summary