Database Programming with PL/SQL
2-4: Using Scalar Data Types
Practice Activities
Vocabulary
A datatype that stores one of the three possible values used for logical calculations: TRUE, FALSE, or NULL. - Boolean
Attribute used to declare a variable according to another previously declared variable or database column. - %TYPE
Try it / Solve it
1. Declarations:
A. Which of the following variable declarations are valid?
Declaration Valid or Invalid
a number_of_students PLS_INTEGER; Valid
b STUDENT_NAME VARCHAR2(10) = Johnson; Invalid
c stu_per_class CONSTANT NUMBER; Invalid
d tomorrow DATE := SYSDATE+1; Valid
B. For the invalid declarations above, describe why they are invalid.
b is invalid because string literals should be in single quotation marks ('Johnson') and := is used to assign values, not =
c is invalid because constant variables must be initialized during declaration.
Write an anonymous block in which you declare and print (on the screen) each of the variables in 1A
above, correcting the invalid declarations and adding information as needed.
DECLARE
number_of_students PLS_INTEGER := 30;
student_name VARCHAR2(10) := 'Johnson';
stu_per_class CONSTANT NUMBER := 1;
today DATE := SYSDATE + 1;
BEGIN
DBMS_OUTPUT.PUT_LINE ('The number of students is:'||number_of_students||'.');
DBMS_OUTPUT.PUT_LINE ('The name of the students is:'||student_name||'.');
DBMS_OUTPUT.PUT_LINE ('The number of students per class is:'||stu_per_class||'.');
DBMS_OUTPUT.PUT_LINE ('Tomorrows date is: '||today||'.');
END;
2. Evaluate the variables in the following code. Answer the following questions about each variable.
Is it named well? Why or why not? If it is not named well, what would be a better name and why?
DECLARE
country_name VARCHAR2(50);
median_age NUMBER(6, 2);
BEGIN
SELECT country_name, median_age INTO country_name, median_age
FROM countries
WHERE country_name = 'Japan';
DBMS_OUTPUT.PUT_LINE('The median age in '|| country_name || ' is ' || median_age || '.');
END;
Both variables have the same name as database table columns. We can use v_ for variables and c_ for constants, for example v_country_name and v_median_age but any other name than the table columns or reserved words are better.
3. Change the declarations in #2 above so they use the %TYPE attribute.
country_name wf_countries.country_name%TYPE;
median_age wf_countries.median_age%TYPE;
4. In your own words, describe why using the %TYPE attribute is better than hard-coding data types.
Can you explain how you could run into problems in the future by hard-coding the data types of
the country_name and median_age variables in question 2?
The %TYPE attribute is useful to reduce the mismatches between the data types or data sizes.
Also, this attribute is necessary if the data type in the table will change.
5. Create the following anonymous block:
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END;
A. Add a declarative section to this PL/SQL block. In the declarative section, declare the following
variables:
• A variable named TODAY of datatype DATE. Initialize TODAY with SYSDATE.
• A variable named TOMORROW with the same datatype as TODAY. Use the %TYPE attribute
to declare this variable.
DECLARE
today DATE:=SYSDATE;
tomorrow today%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END;
B. In the executable section, initialize the TOMORROW variable with an expression that
calculates tomorrow’s date (add 1 to the value in TODAY). Print the value of TODAY and
TOMORROW after printing ‘Hello World’.
DECLARE
today DATE:=SYSDATE;
tomorrow today%TYPE;
BEGIN
tomorrow := today + 1;
DBMS_OUTPUT.PUT_LINE('Hello World');
DBMS_OUTPUT.PUT_LINE(today);
DBMS_OUTPUT.PUT_LINE(tomorrow);
END;
0 Comments