Oracle 1Z0-147 問題集

質問 1：
Examine this code:
CREATE OR REPLACE STORED FUNCTION get_sal
(p_raise_amt NUMBER, p_employee_id employees.employee_id%TYPE)
RETURN NUMBER
IS
v_salary NUMBER;
v_raise NUMBER(8,2);
BEGIN
SELECT salary
INTO v_salary
FROM employees
WHERE employee_id = p_employee_id;
v_raise := p_raise_amt * v_salary;
RETURN v_raise;
END;
Which statement is true?
A. This statement creates a stored procedure named get_sal.
B. This statement returns a raise amount based on an employee id.
C. This statement creates a stored function named get_sal with a status of invalid.
D. This statement creates a stored function named get_sal.
E. This statement fails.
正解：E
解説: (Topexam メンバーにのみ表示されます)

質問 2：
Which three statements are true regarding database triggers? (Choose three)
A. With a schema, triggers fire for each event for all users; with a database, triggers fire for each event for that specific user.
B. A database trigger executes implicitly whenever a particular event takes place.
C. A database trigger fires whenever a data event (such as DML) or system event (such as logon, shutdown) occurs on a schema or database.
D. A database trigger needs to be executed explicitly whenever a particular event takes place.
E. A database trigger is a PL/SQL block, C, or Java procedure associated with a table, view, schema, or the database.
正解：B,C,E
解説: (Topexam メンバーにのみ表示されます)

質問 3：
Examine this package specification:
CREATE OR REPLACE PACKAGE concat_all
IS
v_string VARCHAR2(100);
PROCEDURE combine (p_num_val NUMBER);
PROCEDURE combine (p_date_val DATE);
PROCEDURE combine (p_char_val VARCHAR2, p_num_val NUMBER);
END concat_all; /
Which overloaded COMBINE procedure declaration can be added to this package specification?
A. PROCEDURE concat_all
(p_num_val VARCHAR2, p_char_val NUMBER);
B. PROCEDURE combine (p_val_1 VARCHAR2, p_val_2 NUMBER;
C. PROCEDURE combine (p_no NUMBER);
D. PROCEDURE combine;
正解：D
解説: (Topexam メンバーにのみ表示されます)

質問 4：
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
PROCEDURE UPD_PLAYER_STAT
(V_ID IN NUMBER, V_AB IN NUMBER DEFAULT 4, V_HITS IN NUMBER)
IS
BEGIN
UPDATE PLAYER_BAT_STAT
SET AT_BATS = AT_BATS + V_AB,
HITS = HITS + V_HITS
WHERE PLAYER_ID = V_ID;
COMMIT;
END UPD_PLAYER_STAT;
PROCEDURE ADD_PLAYER
(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY 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;
You make a change to the body of the BB_PACK package. The BB_PACK body is recompiled.
What happens if the stand alone procedure VALIDATE_PLAYER_STAT references this package?
A. VALDIATE_PLAYER_STAT is invalidated.
B. VALIDATE_PLAYER_STAT cannot recompile and must be recreated.
C. VALIDATE_PLAYER_STAT is not invalidated.
D. VALIDATE_PLAYER_STAT and BB_PACK are invalidated.
正解：C
解説: (Topexam メンバーにのみ表示されます)

質問 5：
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 VARCHAR2(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. The LOG_EXEC procedure should be invoked as EXECUTE log_exec with the AUDIT_EMP procedure.
B. Variable v_name should be declared before declaring the LOG_EXEC procedure.
C. An insert statement is not allowed in a subprogram declaration.
D. Procedure LOG_EXEC should be declared before any identifiers.
正解：B
解説: (Topexam メンバーにのみ表示されます)

質問 6：
Which statement is true?
A. Stored functions can increase the efficiency of queries by performing functions in the query rather than in the application.
B. Stored functions do not permit calculations that involve database links in a distributed environment.
C. Stored functions cannot manipulate new types of data, such as longitude and latitude.
D. Stored functions can be called from the SELECT and WHERE clauses only.
正解：A
解説: (Topexam メンバーにのみ表示されます)