 |
| Creating Stored Procedures and Functions |
01. Examine this code:
CREATE OR REPLACE PROCEDURE add_dept
( p_dspt_name VARCHAR2 DEFAULT ‘Placeholder’,
p_location VARCHAR2 DEFAULT ‘Boston’ )
IS
BEGIN
INSERT INTO departments
VALUES ( dept_id_seq.NEXTVAL, p_dept_name, p_location ) ;
END add_dept ;
Which three are valid calls to the add_dept procedure? (Choose three)
a. add_dept ;
b. add_dept ( ’Accounting’ ) ;
c. add_dept (, ‘New York’ ) ;
d. add_dept (p_location => ‘New York’);
Answer: A, B, D
02. You want to create a PL/SQL block of code that calculates discounts on customer orders. This code will be invoked from several places, but only within the program unit ordertotal.
What is the most appropriate location to store the code that calculates the discounts?
a. A sorted procedure on the server.
b. A block of code in a PL/SQL library.
c. A standalone procedure on the client machine.
d. A block of code in the body of the program unit ordertotal.
e. A local subprogram defined within the program unit ordertotal
Answer: E
03. Which type of argument passes a value from a procedure to the calling environment?
a. VARCHAR2.
b. BOOLEAN.
c. OUT
d. IN
Answer: C
04. Which two describe a stored procedure? (Choose two)
a. A stored procedure is typically written in SQL.
b. A stored procedure is a named PL/SQL block that can accept parameters.
c. A stored procedure is a type of PL/SQL subprogram that performs an action.
d. A stored procedure has three parts: the specification, the body, and the exception handler part.
e. The executable section of a stored procedure contains statements that assign values, control execution, and return values to the calling environment.
Answer: B, C
05. Why do stored procedures and functions improve performance? (Chose two)
a. They reduce network round trips.
b. They postpone PL/SQL parsing until run time.
c. They allow the application to perform high speed processing locally.
d. They reduce the number of calls to the database and decrease network traffic by bundling commands.
e. They reduce the number of calls to the database and decrease network traffic by using the local PL/SQL engine.
Answer: A, D
06. When creating store procedures and functions which construct allows you to transfer values to and from the calling environment?
a. Local variables.
b. Formal arguments.
c. Boolean variables.
d. Substitution variables.
Answer : B
07. Under which situation do you create a server side procedure?
a. When the procedure contains no SQL statements.
b. When the procedure contains no PL/SQL commands.
c. When the procedure needs to be used by many client applications accessing several remote databases.
d. When the procedure needs to be used by many users accessing the same schema objects on a local database.
Answer: D
08. When creating procedures, local variables should be placed after which key words?
a. IS.
b. BEGIN.
c. DECLARE.
d. PROCEDURE.
Answer: A
09. When creating procedures, arguments should be placed before which keyword?
a. IS.
b. BEGIN.
c. DECLARE.
d. PROCEDURE.
Answer: A
10. Given the header of a procedure account_transaction:
CREATE OR REPLACE PROCEDURE account_transaction
IS
BEGIN
< additional code >
END;
Which command will execute the PROCEDURE account_transaction from the iSQL*Plus prompt?
a. account_transaction ;
b. RUN account_transaction ;
c. START account_transaction ;
d. EXECUTE account_transaction ;
Answer: D
11. Examine this procedure:
CREATE OR REPLACE PROCEDURE add_player
(v_id IN NUMBER, v_last_name VARCHAR2(30))
IS
BEGIN
INSERT INTO player (id, last_name)
VALUES (v_id, v_last_name);
COMMIT;
END;
Why does this command fail when executed?
a. When declaring arguments length is not allowed.
b. When declaring arguments each argument must have a mode specified.
c. When declaring arguments each argument must have a length specified.
d. When declaring a VARCHAR2 argument it must be specified.
Answer: A
12. Examine this anonymous block:
SQL> BEGIN
2 validate_trans ('value', 'value', 'value');
3 END;
4 /
Which method is used to pass parameters to the validate_trans procedure?
a. Global.
b. Positional.
c. Combination.
d. Named association.
Answer: B
0 Comments