PL/SQL 2-1: Using Variables in PL/SQL

 Database Programming with PL/SQL

2-1: Using Variables in PL/SQL

Practice Activities

Vocabulary

Used for storage of data and manipulation of stored values. - variables

Values passed to a program by a user or by another program to customize the program. - parameters

Try It / Solve It

1. Fill in the blanks.

A. Variables can be assigned to the output of a string.

B. Variables can be assigned values in the executable section of a PL/SQL block.

C. Variables can be passed as parameters to subprograms.

2. Identify valid and invalid variable declaration and initialization:

number_of_copies PLS_INTEGER;                         valid

printer_name CONSTANT VARCHAR2(10);         invalid varchar can't be constant

deliver_to VARCHAR2(10) := Johnson;                  invalid 'Johnson' -> must have ''

by_when DATE := SYSDATE+1;                            valid

3. Examine the following anonymous block and choose the appropriate statement.

DECLARE

 fname VARCHAR2(25);

 lname VARCHAR2(25) DEFAULT 'fernandez';

BEGIN

 DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname);

END;

A. The block will execute successfully and print ‘ fernandez’.

B. The block will give an error because the fname variable is used without initializing.

C. The block will execute successfully and print ‘null fernandez’.

D. The block will give an error because you cannot use the DEFAULT keyword to initialize a

variable of the VARCHAR2 type.

E. The block will give an error because the FNAME variable is not declared.

4. In Application Express:

A. Create the following function:

CREATE FUNCTION num_characters (p_string IN VARCHAR2)

RETURN INTEGER AS

 v_num_characters INTEGER;

BEGIN

 SELECT LENGTH(p_string) INTO v_num_characters

 FROM dual;

 RETURN v_num_characters;

END;

B. Create and execute the following anonymous block:

DECLARE

 v_length_of_string INTEGER;

BEGIN

 v_length_of_string := num_characters('Oracle Corporation');

 DBMS_OUTPUT.PUT_LINE(v_length_of_string);

END;

5. Write an anonymous block that uses a country name as input and prints the highest and lowest

elevations for that country. Use the COUNTRIES table. Execute your block three times using

United States of America, French Republic, and Japan.

DECLARE

v_country_name varchar2(50):= 'United States of America';

v_lowest_elevation number(6);

v_highest_elevation number(6);

BEGIN

SELECT lowest_elevation, highest_elevation

INTO v_lowest_elevation, v_highest_elevation

FROM wf_countries

WHERE country_name = v_country_name;

DBMS_OUTPUT.PUT_LINE('The lowest elevation for '||v_country_name ||' is: '||v_lowest_elevation);

DBMS_OUTPUT.PUT_LINE('The highest elevation for '||v_country_name ||' is: '||v_highest_elevation);

END;