Chapter 3 Practice
1) Walkthrough the following PL/SQL code and try to guess what 3 values will be printed for
“outervar”?
SET SERVEROUTPUT ON
<<outer>>
DECLARE
outervar NUMBER := 100;
BEGIN
DECLARE
inervar NUMBER := 700;
outervar NUMBER := 500;
BEGIN
outer.outervar := inervar *2;
DBMS_OUTPUT.PUT_LINE('Local Outer variable is: ' || outervar);
DBMS_OUTPUT.PUT_LINE('Main Outer variable is: ' || outer.outervar);
END;
DBMS_OUTPUT.PUT_LINE('Main Outer variable is: ' || outervar);
END;
/
2) Write the PL/SQL code that will prompt you to enter today’s date and then will print today’s
Day Name and tomorrow’s as well. Here is the output.
Enter value for day: 18-SEP-09
Today is : Friday
Tomorrow will be : Saturday
PL/SQL procedure successfully completed.
3) You need to walkthrough this code and figure out what would be the values for all 3
variables at Post A and Post B respectively?
DECLARE
var1 NUMBER(3) := 100;
var2 VARCHAR2(100) := 'SQL is Great' ;
BEGIN
DECLARE
var1 NUMBER(3) := 5;
var2 VARCHAR2(100) := 'SQL is Fun' ;
local VARCHAR2(100) := 'Oracle rules' ;
BEGIN
var1:= var1 + 15;
local := 'I am sure that ' || local;
__________________________________ Post A
END;
var1 := var1 + 5;
var2 := var2 || ' if you come to class';
local := 'Who said that ' || local;
__________________________________ Post B
END;
You may comment out some lines here in order to be sure what these values are!
4) Prompt user to enter a valid city name of a department. Then display the message about the Id
and Name for that department. Here is one possible output.
You may enter the city name : TORONTO
In the chosen city your department id is 20 and your department name is Marketing
PL/SQL procedure successfully completed.
ANSWERS
1)
Local Outer variable is: 500
Main Outer variable is: 1400
Main Outer variable is: 1400
PL/SQL procedure successfully completed.
2)
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
v_day DATE := '&day';
BEGIN
DBMS_OUTPUT.PUT_LINE('Today is : ' || TO_CHAR(v_day,'Day'));
v_day := v_day + 1;
DBMS_OUTPUT.PUT_LINE('Tomorrow will be : ' || TO_CHAR(v_day,'Day'));
END;
/
3)
SET SERVEROUTPUT ON
DECLARE
var1 NUMBER(3) := 100;
var2 VARCHAR2(100) := 'SQL is Great' ;
BEGIN
DECLARE
var1 NUMBER(3) := 5;
var2 VARCHAR2(100) := 'SQL is Fun' ;
local VARCHAR2(100) := 'Oracle rules' ;
BEGIN
var1:= var1 + 15;
local := 'I am sure that ' || local;
DBMS_OUTPUT.PUT_LINE('VAR1 at A is: ' || var1);
DBMS_OUTPUT.PUT_LINE('VAR2 at A is: ' || var2);
DBMS_OUTPUT.PUT_LINE('LOCAL at A is: ' || local);
END;
var1 := var1 + 5;
var2 := var2 || ' if you come to class';
-- local := 'Who said that ' || local;
DBMS_OUTPUT.PUT_LINE('VAR1 at B is: ' || var1);
DBMS_OUTPUT.PUT_LINE('VAR2 at B is: ' || var2);
-- DBMS_OUTPUT.PUT_LINE('LOCAL at B is: ' || local);
END;
/
Lines 19 and 22 are commented out so that you do NOT get the error, because “local” is
NOT visible in the outer block.
VAR1 at A is: 20
VAR2 at A is: SQL is Fun
LOCAL at A is: I am sure that Oracle rules
VAR1 at B is: 105
VAR2 at B is: SQL is Great if you come to class
PL/SQL procedure successfully completed.
4)
SET SERVEROUTPUT ON
SET VERIFY OFF
ACCEPT v_city PROMPT 'You may enter the city name : '
DECLARE
dept# departments.department_id%TYPE ;
dname departments.department_name%TYPE;
loc# departments.location_id %TYPE;
BEGIN
SELECT location_id INTO loc#
FROM locations
WHERE UPPER(city) = '&v_city';
SELECT department_id, department_name
INTO dept#, dname
FROM departments
WHERE location_id = loc# ;
DBMS_OUTPUT.PUT_LINE('In the chosen city your department id is ' || dept# || ' and your
department name is ' || dname);
END;
