Chapter 1. Declaring Variables and Naming Elements

Variables allow you to store and manipulate data—such as column values, counters, or calculations—inside your PL/SQL programs. There are two types of variables: scalar, which are made up of a single value, and composite, which are made up of multiple pieces (a record, for example, is represented with a composite variable). Whether composite or scalar, every variable has a name (also called an identifier), a datatype (such as NUMBER or VARCHAR2), and a value. This chapter tests your ability to work with these most basic PL/SQL elements.

Beginner

1-1.

Which of the following identifiers are valid, which are invalid, and why?

  1. my_variable2

  2. my-variable2

  3. my^variable

  4. MyVariable

  5. my_variable_for_many_many_of_usages

  6. 123myvariable

  7. “123myvariable”

1-2.

Which of the following blocks will compile, and which will fail to compile? Why?

  1. DECLARE
   lastdate DATE;
   lastDate NUMBER;
BEGIN
   NULL;
END;
  2. DECLARE
  lastdate DATE := sysdate;
  lastDate NUMBER := 50;
BEGIN
  dbms_output.put_line(lastdate);
  dbms_output.put_line(lastDate);
END;

1-3.

Declare a date variable with an initial value of SYSDATE (show both valid formats).

1-4.

Declare a local variable to have the same datatype as the last_name column in the employee table.

1-5.

Explain what is wrong with each of the following declarations:

DECLARE
   INTEGER year_count;
   VARCHAR2(100) company_name, employee_name;
   delimiters VARCHAR2;
   curr_year CONSTANT INTEGER;
   invalid_date EXCEPTION := VALUE_ERROR;

1-6.

What datatype is most appropriate to store the following data items?

'This is a test'
Values from 1 to 10
5.987
'This is a test    '
A string that is always nine characters long
January 10, 2000
A binary file
TRUE or FALSE
The internal identifier of a row in a table

Intermediate

1-7.

Declare a local variable to have the same datatype as the dollar_amount variable in the types package.

1-8.

Which of the following statements about the DATE datatype are true?

  1. The maximum (latest) date allowed in a date variable is January 1, 4712.

  2. The earliest date allowed in a date variable is January 1, 4712 BC (or BCE).

  3. A date variable contains both date and time information.

  4. A date variable records the time down to the nearest hundredth of a second.

  5. The DATE datatype stores only those numbers of digits for the year you specify when you assign a value to the variable.

1-9.

Declare a local variable to have the same datatype as the dollar_amount_t SUBTYPE in the types package.

1-10.

Explain what is wrong with the following anchored declaration:

DECLARE
   dollar_amt CONSTANT NUMBER (20, 2) := 0;
   other_dollar_amt dollar_amt%TYPE;

1-11.

Explain what is wrong with the following SUBTYPE declaration:

DECLARE
   SUBTYPE small_number IS NUMBER (3);

1-12.

What error is raised when you try to execute this block of code? What is the problem?

DECLARE
   your_choice SIGNTYPE := 2;
BEGIN
   NULL;
END;

1-13.

Which of the following statements describe accurately how the “anchoring” of a variable’s datatype to a data structure improves the maintainability of that code?

  1. To anchor, you have to use %TYPE or %ROWTYPE, which involves more typing and that means you have more time to think about what you are typing and can get it right.

  2. If you anchor against a table or column in a table, when that table changes, your compiled code will be marked invalid. After recompilation, your code automatically reflects the latest structure in the table.

  3. When you anchor against a PL/SQL variable, you make sure to define your type of data only once and then reuse that definition. So if the definition changes, you have to change your code in only one place.

1-14.

Name at least three different objects to which you could anchor a variable.

1-15.

What datatype would you expect a function of the name “is_value_in_list” to return?

Expert

1-16.

Why does this code compile?

DECLARE
   sysdate NUMBER;
BEGIN
   sysdate := 1;
END;

But this code does not?

DECLARE
   then NUMBER;
BEGIN
   then := 1;
END;

1-17.

How can you get around this restriction and declare SUBTYPEs which are, in effect, constrained? To be specific, I want to declare a subtype called primary_key_t that limits the size of any variable declared with that type to NUMBER(6).

1-18.

The NULL value in Oracle is handled differently from other values. One could even say that there is no such thing as a “NULL value.” (NULL means “indeterminate.”) Name three rules that apply to NULLs when doing comparisons.

1-19.

What special operators does Oracle provide for dealing with NULLs?

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.145.65.134