Oracle 1Z0-147 問題集 : Oracle9i program with pl/sql

You need to create a DML trigger. Which five pieces need to be identified? (Choose five)

• A. Trigger name
• B. Package name
• C. System event
• D. Trigger body
• E. Trigger timing
• F. Table
• G. Package body
• H. DML event

You need to create a trigger on the EMP table that monitors every row that is changed and places this information into the AUDIT_TABLE.What type of trigger do you create?

• A. Statement-level trigger on the AUDIT_TABLE table.
• B. FOR EACH ROW statement-level trigger on the EMPtable.
• C. Statement-level trigger on the EMP table.
• D. FOR EACH ROW trigger on the EMP table.
• E. FOR EACH ROW trigger on the AUDIT_TABLE table.

Which two statements about functions are true? (Choose two.)

• A. Client-side functions can be used in SOL statements
• B. A function must have a return statement in its body to execute successfully
• C. A stored function increases efficiency of queries by performing functions on the server rather than in the application
• D. A stored function that is called from a SOL statement can return a value of any PL/SOL variable data type
• E. From SOL*Plus, a function can be executed by giving the command EXECUTE functionname;

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
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_id; IF v_sal < 500 THEN v_raise := .05; ELSIP v_sal < 1000 THEN v_raise := .07; ELSE v_raise := .04; END IF; update_sal(v_raise); END update_emp; FUNCTION calc_tax (p_sal NUMBER) RETURN NUMBER IS BEGIN RETURN p_sal * tax_rate;
END calc_tax;
END manage_emps;
/
How many public procedures are in the MANAGE_EMPS package?

• A. One
• B. Three
• C. Five
• D. Four
• E. Two

Examine this package:
CREATE OR REPLACE PACKAGE pack_cur IS CURSOR c1 IS SELECT prodid FROM poduct ORDER BY prodid DESC; PROCEDURE proc1; PROCEDURE proc2; END pack_cur; /
CREATE OR REPLACE PACKAGE BODY pack_cur IS v_prodid NUMBER; PROCEDURE proc1 IS BEGIN OPEN c1;
LOOP
FETCH c1 INTO v_prodid;
DBMS_OUTPUT.PUT_LINE('Row is: ' || c1%ROWCOUNT);
EXIT WHEN c1%ROWCOUNT >= 3;
END LOOP;
END proc1;
PROCEDURE proc2 IS
BEGIN
LOOP
FETCH c1 INTO v_prodid;
DBMS_OUTPUT.PUT_LINE('Row is: ' ||c1%ROWCOUNT);
EXIT WHEN c1%ROWCOUNT >= 6;
END LOOP;
CLOSE c1;
END proc2;
END pack_cur;
/
The product table has more than 1000 rows. The SQL *Plus SERVEROUTPUT setting is turned
on in your session.
You execute the procedure PROC1 from SQL *Plus with the command:
EXECUTE pack_cur.proc1
What is the output in your session?

• A. ERROR at line 1:
• B. Row is: Row is: Row is:
• C. Row is: 4 Row is: 5 Row is: 6
• D. Row is: 1 Row is: 2 Row is: 3