PLSQL MCQ's Set 2
16.You need to drop a table from within a stored procedure. How do you implement this?
A. You cannot drop a table from a stored procedure.
B. Use the DROP command in the procedure to drop the table.
C. Use the DBMS_DDL packaged routines in the procedure to drop the table.
D. Use the DBMS_SQL packaged routines in the procedure to drop the table.
E. Use the DBMS_DROP packaged routines in the procedure to drop the table.
Answer: D
17.Which data dictionary views gives you the names and the source code of all the procedures that you have created?
A. USER_SOURCE
B. USER_OBJECTS
C. USER_PROCEDURES
D. USER_SUBPROGRAMS
Answer: A
18.Examine this package
CREATE OR REPLACE PACKAGE BB_PACK IS
V_MAX_TEAM_SALARY NUMBER(12,2);
PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME) VARCHAR2(V_SALARY NUMBER);
END BB_PACK;
/
CREATE OR REPLACE PACKAGE BODY BB_PACK IS
V_PLAYER_AVG NUMBER(4,3); PROCEDURE UPD_PLAYER_STAT
V_ID IN NUMBER, V_AB IN NUMBER DEFAULT4, V_HITS IN NUMBER) IS
BEGIN
UPDATE PLAYER_BAT_STAT
SET ADD_BAT=ADD_BATS+V_AB, HITS=HITS+V_HITS
WHERE PLAYER_ID=V_ID; COMMIT; VALIDATE_PLAYER_STAT(V_ID); END UPD_PLAYER_STAT;
PROCEDURE ADD_PLAYER
(V_ID IN NUMBER, V_LAST_NAME, VARCHAR2, V_SALARY IN NUMBER); IS
BEGIN
INSERT INTO PLAYER (ID, LAST_NAME, SALARY) VALUES(V_ID, V_LAST_NAME, V_SALARY); UPD_PLAYER_STAT(V_ID,0,0);
END ADD_PLAYER;
END BB_PACK;
Which kind of packaged variables is V_MAX_TEAM_SALARY?
A. PRIVATE
B. PUBLIC
C. IN
D. OUT
Answer: B
19.Examine this trigger.
CREATE OR REPLACE TRIGGER UPD_TEAM_SALARY AFTER INSERT OR UPDATE OR DELETE ON PLAYER FOR EACH ROW
BEGIN UPDATE TEAM
SET TOT_SALARY=TOT_SALARY+:NEW SALARY. WHERE ID=:NEW:TEAM_ID;
You will be adding additional coat later but for now you want the current block to fire when updated the salary column.
Which solution should you use to verify that the user is performing an update on the salary column?
A. ROW_UPDATE(‘SALARY’)
B. UPDATING(‘SALARY’)
C. CHANGING(‘SALARY’)
D. COLUMN_UPDATE(‘SALARY’)
Answer: B
20.Examine this package
CREATE OR REPLACE PACKAGE discounts IS G_ID NUMBER:=7839;
DISCOUNT_RATE NUMBER O. 00;
PROCEDURE DISPLAY_PRICE (V_PRICE NUMBER); END DISCOUNTS;
/
CREATE OR REPLACE PACKAGE BODY discounts IS
PROCEDURE DISPLAY_PRICE (V_PRICE_NUMBER) IS
BEGIN DBMS_OUTPUT.PUT_LINE(‘DISCOUNTED||2_4 (V_PRICE*NVL(DISCOUNT_RATE, 1)))
END DISPLAY_PRICE;
BEGIN DISCOUNT_RATE;=0. 10; END DISCOUNTS;
/
Which statement is true?
A. The value of DISCOUNT_RATE always remain 0. 00 in a session.
B. The value of DISCOUNT_RATE is set to 0. 10 each time the package are invoked in a session.
C. The value of DISCOUNT_RATE is set to 1 each time the procedure DISPLAY_PRICE is invoked.
D. The value of DISCOUNT_RATE is set to 0. 10 when the package is invoked for first time in a session.
Answer: B
21.Examine this function:
CREATE OR REPLACE FUNCTION get_budget (v_studio_id IN NUMBER)
RUTURN number
IS BEGIN
END;
v_yearly_budget NUMEBER;
SELECT yearly_budget INTO v_yearly_budget FROM studio
WHERE id = v_studio_id;
To compile this function successfully, what additional code must be added to the executable section?
A. RETURN;
B. RETURN get_budget;
C. OUT v_yearly_budget;
D. RETURN v_yearly_budget;
Answer: D
22.Within the current application, a COBOL program calculates the annual allowable budget for each studio based on historical analysis. This value, stored in a COBOL variable, will be passed to your function as an argument. Which function will your create?
A. CREATE OR REPLACE FUNCTION set_budget
v_studioo_id IN NUMBER, v_new_budget IN NUMBER) RETURN number
IS BEGIN
UPDATE studio
SET yearly_budget = v_new_budget WHERE id = v_studio_id; COMMIT;
RETURN SQL%ROWCOUNT; END;
B. CREATE OR REPLACE FUNCTION set_budget
(v_studio_id IN NUMBER, :cobol_variable_budget IN NUMBER) RETURN number
IS BEGIN
UPDATE studio
SET yearl_budget = : cobol_variable_budget WHERE id = v_studio_id;
COMMIT;
RETURN SQLROWCOUNT; END;
C. CREATE OR REPLACE FUNCTION set_budget
(v_studio_id IN NUMBER, v_new_budget IN NUMBER := :cobol_variable_budget) RETURN number
IS BEGIN
UPDATE studio
Set yearly_budget = v_new_budget WHERE id = v_studio_id; COMMIT;
RETURN SQL%ROWCOUNT; END;
D. CREATE OR REPLACE FUNCTION set_budget
(v_studio_id IN NUMBER, :cobol_variable_budget IN NUMBER) RETURN number
IS
V_new_budget NUMBER := :cobol_variable_budget; BEGIN
UPDATE studio
SET yearly_budget = v_new_budget WHERE id = v_studio_id; COMMIT;
RETURN SQL%ROWCOUNT; END;
Answer: A
23.Which two statements are true? (Choose two.)
A. A function must return a value.
B. Functions and procedures must return a value.
C. Functions and procedures must contain IN arguments.
D. A function can be invoked from within a PL/SQL expression.
E. A procedure must be invoked from within a PL/SQL expression.
Answer: A & D
24.Examine this function:
CREATE OR REPLACE FUNCTION get_budget
(v_studio_id IN NUMBER, v_max_budget IN NUMBER) RETURN number
IS BEGIN
v_yearly_budget NUMBER;
SELECT yearly_budget INTO v_yearly_budget FROM studio
WHERE id = v_studio_id;
IF v_yearly_budget > v_max_budget THEN RETURN v_max_budget;
ELSE
RETURN v_yearly_budget;
END IF;
END;
Which set of statements will successfully invoke this function within SQL*Plus?
A. SELECT id, name, get_budget(id,200) FROM studio;
B. VARIABLE g_yearly_budget NUMBER
EXECUTE g_yearly_budget := get_budget(11, 4000000000);
C. VARIABLE g_yearly_budget NUMBER
RUN :g_yearly_budget := get_budget(v_studio_id => 11,v_max_budget => 4000000000);
D. VARIABLE g_yearly_budget NUMBER
EXECUTE g_yearly_budget := get_budget(v_max_budget => 4000000000, v_studio_id => 11);
Answer: A
25.Examine this function:
CREATE OR REPLACE FUNCTION get_budget RETURN number
IS BEGIN
END;
v_yearly_budget NUMBER;
SELECT yearly_budget INTO v_yearly_budget FROM studio
WHERE id = v_studio_id; RETURN v_yearly_budget;
What additional code is needed to compile this function successfully?
A. Remove the first RETURN statement.
B. Change “RETURN number” to “OUTPUT number”.
C. Add “(v_studio_id IN NUMBER)” right after the IS keyword.
D. Add “(v_studio_id IN NUMBER)” right before the RETURN statement of the header.
Answer: D
26.A stored function can be invoked in many difference ways. Which invocation example is NOT valid?
A. executing the stored function within a INSERT statement
B. Executing the stored function within a server-side function
C. Executing the stored function within a client-side function
D. Executing the stored function within a CHECK constraint of a table.
Answer: D
27.Which code successfully calculates commission returning it to the calling environment?
A. CREATE OR REPLACE FUNCTION calc_comm (v_emp_id IN NUMBER)
RETURN number BEGIN
SELECT SUM(ord.total) INTO v_total
FROM ord,customer
WHERE ord_custid = customer.custid AND customer_repid = v_emp_id; END;
B. CREATE OR REPLACE FUNCTION calc_comm (v_emp_id IN NUMBER)
IS
v_total number;
BEGIN
SELECT SUM(ord.total) INTO v_total
FROM ord,customer
WHERE ord_custid = customer.custid AND customer_repid = v_emp_id; RETURN (v_total * .20);
END;
C. CREATE OR REPLACE FUNCTION calc_comm (v_emp_id IN NUMBER)
IS
RETURN number v_total number; BEGIN
SELECT SUM(ord.total) INTO v_total
FROM ord,customer
WHERE ord_custid = customer.custid AND customer_repid = v_emp_id; RETURN (v_total * .20);
END;
D. CREATE OR REPLACE FUNCTION calc_comm (v_emp_id IN NUMBER)
RETURN number IS
v_total number;
BEGIN
SELECT SUM(ord.total) INTO v_total
FROM ord,customer
WHERE ord_custid = customer.custid AND customer_repid = v_emp_id; RETURN (v_total * .20);
END;
Answer: D
28.The GET_BUDGET function is no longer needed and should be removed. Which command will successfully remove this function from the database?
A. GROP get_budget;
B. REMOVE get_budget;
C. DROP FUNCTION get_budget;
D. ALTER get_budget DROP function;
Answer: C
29.Which two subprogram headers are correct? (Choose two.)
A. CREATE OR REPLACE PROCEDURE get_sal IS
(v_sal IN number)
B. CREATE OR REPLACE PROCEDURE get_sal (v_sal IN number)
IS
C. CREATE OR REPLACE PROCEDURE get_sal RETURN number
(p_amnt IN number)
D. CREATE OR REPLACE PROCEDURE get_sal (p_amnt IN number)
RETURN number
E. CREATE OR REPLACE PROCEDURE get_sal (p_amnt IN number(3,2))
RETURN number
Answer: B & D
30.Procedures and functions can be created and stored in the database or in an Oracle Developer application. How is performance improved when storing procedures and functions in the database?
A. Network roundtrips are reduced.
B. The object code is created during execution.
C. Network traffic is decreased by bundling commands.
D. The source code is stored externally, and the object code is stored internally.
Answer: A
