Evaluate the PL/SQL block below and determine the value of each of the following
ID: 3747123 • Letter: E
Question
Evaluate the PL/SQL block below and determine the value of each of the following variables according to the rules of scoping. DECLARE weight NUMBER(3) := 600; message VARCHAR2(255) := 'Product 10012'; BEGIN DECLARE weight NUMBER(3) := 1; message VARCHAR2(255) := 'Product 11001'; new_locn VARCHAR2(50) := 'Europe'; BEGIN weight := weight + 1; new_locn := 'Western ' || new_locn; -- Position 1 -- 2 END; weight := weight + 1; message := message || ' is in stock'; -- Position 2 -- END; A. The value of weight at position 1 is: B. The value of new_locn at position 1 is: C. The value of weight at position 2 is: D. The value of message at position 2 is: E. The value of new_locn at position 2 is: 2. Enter and run the following PL/SQL block, which contains a nested block. Look at the output and answer the questions. DECLARE v_employee_id employees.employee_id%TYPE; v_job employees.job_id%TYPE; BEGIN SELECT employee_id, job_id INTO v_employee_id, v_job FROM employees WHERE employee_id = 100; DECLARE v_employee_id employees.employee_id%TYPE; v_job employees.job_id%TYPE; BEGIN SELECT employee_id, job_id INTO v_employee_id, v_job FROM employees WHERE employee_id = 103; DBMS_OUTPUT.PUT_LINE(v_employee_id || ' is a(n) ' || v_job); END; 3 DBMS_OUTPUT.PUT_LINE(v_employee_id || ' is a(n) ' || v_job); END; A. Why does the inner block display the job_id of employee 103, not employee 100? B. Why does the outer block display the job_id of employee 100, not employee 103? C. Modify the code to display the details of employee 100 in the inner block. Use block labels.
Explanation / Answer
Evaluate the PL/SQL block below and determine the value of each of the following
variables according to the rules of scoping.
DECLARE
weight NUMBER(3) := 600;
message VARCHAR2(255) := 'Product 10012';
BEGIN DECLARE
weight NUMBER(3) := 1;
message VARCHAR2(255) := 'Product 11001';
new_locn VARCHAR2(50) := 'Europe';
BEGIN weight := weight + 1;
new_locn := 'Western ' || new_locn; -- Position 1 -- 2
END;
weight := weight + 1;
message := message || ' is in stock'; -- Position 2 --
END;
A. The value of weight at position 1 is: 2
B. The value of new_locn at position 1 is: Western Europe
C. The value of weight at position 2 is: 3
D. The value of message at position 2 is: Product 11001 is in stock
E. The value of new_locn at position 2 is: Western Europe
2. Enter and run the following PL/SQL block, which contains a nested block.
Look at the output and answer the questions.
DECLARE
v_employee_id employees.employee_id%TYPE;
v_job employees.job_id%TYPE;
BEGIN
SELECT employee_id, job_id INTO v_employee_id, v_job FROM employees WHERE employee_id = 100;
DECLARE v_employee_id employees.employee_id%TYPE;
v_job employees.job_id%TYPE;
BEGIN
SELECT employee_id, job_id INTO v_employee_id, v_job FROM employees WHERE employee_id = 103;
DBMS_OUTPUT.PUT_LINE(v_employee_id || ' is a(n) ' || v_job);
END;
DBMS_OUTPUT.PUT_LINE(v_employee_id || ' is a(n) ' || v_job);
END;
A. Why does the inner block display the job_id of employee 103, not employee 100?
ANS: We have declared same variable names for inner block and outer block.
Inner block will table variables most recent variable names, due to which it displays 103 details
B. Why does the outer block display the job_id of employee 100, not employee 103?
ANS: We have declared same variable names for inner block and outer block.
outer block will take variables which are declared out side of Begin block
C. Modify the code to display the details of employee 100 in the inner block
DECLARE
v_employee_id employees.employee_id%TYPE;
v_job1 employees.job_id%TYPE;
BEGIN
SELECT employee_id, job_id INTO v_employee_id, v_job1 FROM employees WHERE employee_id = 100;
DECLARE v_employee_id employees.employee_id%TYPE;
v_job2 employees.job_id%TYPE;
BEGIN
SELECT employee_id, job_id INTO v_employee_id, v_job FROM employees WHERE employee_id = 103;
DBMS_OUTPUT.PUT_LINE(v_employee_id || ' is a(n) ' || v_job1);
END;
DBMS_OUTPUT.PUT_LINE(v_employee_id || ' is a(n) ' || v_job1);
END;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.