 |
| Creating Procedures |
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 order total.
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. Examine this code:
CREATE OR REPLACE PROCEDURE add_dept
( p_dept_name departments.department_name%TYPE DEFAULT ‘Unknown’,
p_loc dedartments.location_id%TYPE DEFAULT 1700 )
IS
BEGIN
INSERT INTO departments ( department_id, department_name,
location_id )
VALUES ( dept_seq.NEXTVAL, p_name, p_loc ) ;
END add_dept ;
Your created the add_dept procedure above, and you now invoke the procedure in iSQL*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
06. Examine this procedure:
CREATE OR REPLACE PROCEDURE upd_bat_stat
( v_id IN NUMBER DEFAULT 10, v_ab IN NUMBER DEFAULT 4)
IS
BEGIN
UPDATE player_bat_stat
SET at_bats = at_bats + v_ab
WHERE player_id = v_id ;
COMMIT ;
END ;
Which two statements will successfully invoke this procedure in iSQL*Plus? (Choose two)
A. EXECUTE upd_bat_stat ;
B. EXECUTE upd_bat_stat ( v_ab => 10, v_id => 31 ) ;
C. EXECUTE upd_bat_stat (31, ‘FOUR’, ‘TWO’) ;
D. upd_bat_stat (v_ab => 10, v_id => 31 )
E. RUN upd_bat_stat ;
Answer: A, B
07. What is true about stored procedures?
A. A stored procedure uses the declare keyword in the procedure specifications 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
08. 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_loaction_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 now invoke the insert_location procedure using the following command:
EXECUTE insert_location ( 19, ‘San Francisco’ )
What is the result in this execute command?
A. The locations, departments, and employees table 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 table are empty.
D. The locations table and the departments table both have one row.
The employees table is empty.
Answer: A
09. Examine this code:
CREATE OR REPLACE PROCEDURE audit_emp
( p_id in emp_empno%TYPE )
IS
v_id NUMBER ;
PROCEDURE log_exec
IS
BEGIN
INSERT INTO log_table ( user_id, log_delete )
VALUES ( user, sysdate ) ;
END log_exec ;
v_name VARCHAR(20) ;
BEGIN
DELETE FROM emp
WHERE empno = p_id;
log_exec ;
SELECT ename, empno
INTO v_name, v_id
FROM emp
WHERE empno = p_id ;
END audit_emp ;
Why does this code cause an error when compiled?
A. A statement is not allowed in a subprogram declaration.
B. Procedure log_exec should be declared before declaring the log_exec procedure.
C. Variable v_name should be declared before declaring the log_exec procedure.
D. The log_exec procedure should be invoked as execute log_exec with the audit_emp procedure.
Answer: C
10. Examine this procedure
CREATE OR REPLACE PROCEDURE add_player
(v_id IN NUMBER, v_last_name VARCHAR2)
IS
BEGIN
INSERT INTO player (id, last_name).
VALUES (v_id, v_last_name);
COMMIT;
END;
This procedure must invoke the upd_bat_stat procedure and pass a parameter.
Which statement, when added to the above procedue will successfully invoke the upd_bat_stat procedure?
A. EXECUTE upd_bat_stat (v_id);
B. upd_bat_stat (v_id);
C. RUN upd_bat_stat (v_id);
D. START upd_bat_stat (v_id);
Answer: A
11. Examine the procedure:
CREATE OR REPLACE PROCEDURE insert_team
( v_id IN NUMBER, v_city IN VARCHAR2 DEFAULT ‘AUSTIN’,
v_name IN VARCHAR2)
IS
BEGIN
INSERT INTO team (id, city, name)
VALUES (v_id, v_city, v_name);
COMMIT;
END;
Which two statements will successfully invoke this procedure in SQL*Plus? (Choose two)
A. EXECUTE insert_team ;
B. EXECUTE insert_team ( 3, v_name => ‘LONGHORNS’, v_city => ‘AUSTIN’ ) ;
C. EXECUTE insert_team ( 3, ‘AUSTIN’, “LONG HORNS” ) ;
D. EXECUTE insert_team ( v_id := 3, v_name := ‘LONGHORNS’, v_city := ‘AUSTIN’ ) ;
E. EXECUTE insert_team ( 3, ” LONG HORNS” ) ;
Answer: B,C
12. Examine this procedure:
CREATE OR REPLACE PROCEDURE delete_player
(v_id IN NUMBER)
IS
BEGIN
DELETE FROM player
WHERE v_id = 31;
EXCEPTION
WHEN stat_exist_exception THEN
DBMS_OUTPUT.PUT_LINE (‘can’t delete this player, child
records exist in player_bat_stat table’);
END;
What prevents this procedure from being created successfully?
A. A comma has been left off after the stat_exist_exception exception.
B. The stat_exist_exception has not been declared as a number.
C. The stat_exist_exception has not been declared as an exception.
D. Only predefined exceptions are allowed in the EXCEPTION section.
Answer: C
13. 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: A
14. Which type of argument passes a value from a calling environment?
A. VARCHAR2
B. BOOLEAN
C. OUT
D. IN
Answer: C
0 Comments