CHAPTER 1
INTRODUCTION TO PL/SQL
------------------------------------------------------------------------------------------------------------
PL/SQL is the Oracle's procedural
language extension to SQL, the non-procedural relational database language. PL/SQL
fully integrates modern software engineering features such as data
encapsulation, information hiding, overloading, and exception handling, and so
brings state-of-the-art programming to the ORACLE Server and a variety of
ORACLE tools.
OVERVIEW OF PL/SQL
With PL/SQL, you can use SQL statements to manipulate ORACLE
data and flow-of-control statements to process the data. Moreover, you can
declare constants and variables, define subprograms (procedures and functions),
and trap runtime errors. Thus, PL/SQL combines the data manipulating power of
SQL with the data processing power of procedural languages.
PL/SQL is a block-structured language. That is, the basic
units (procedures, functions, and anonymous blocks) that make up a PL/SQL
program are logical blocks, which can contain any number of nested sub-blocks.
Typically, each logical block corresponds to a problem or sub problem to be
solved.
It is more efficient to use SQL statements within PL/SQL
blocks because network traffic can be decreased significantly, and an
application becomes more efficient as well.
When a SQL statement is issued on the client computer, the
request is made to the database on the server, and the result set is sent back
to the client. As a result, a single SQL statement causes two trips on the
network. If multiple SELECT statements are issued, the network traffic can
increase significantly very fast. For example, four SELECT statements cause
eight network trips. If these statements are part of the PL/SQL block, there
are still only two network trips made as in case of a single SELECT statement.
OBJECTIVES
This chapter will provide a basic understanding of some of
the basics of PL/SQL language. We will briefly cover the following topics:
·
PL/SQL Block Structure
·
EXECUTING a PL./SQL program
·
Generating Output
·
Comments in PL/SQL
·
PL/SQL Terminology
·
PL/SQL Character set
o Lexical
units
·
Arithmetic and Relational operators
·
Miscellaneous Symbols
·
Variables in PL/SQL
·
Reserve words
·
Common Datatypes
·
SELECT INTO command
·
Substitution variables
PL/SQL BLOCK STRUCTURE
A block is the most basic unit in PL/SQL.
All PL/SQL programs are combined into blocks. These blocks can also be nested
within the other. Usually, PL/SQL blocks combine statements that represent a
single logical task.
PL/SQL blocks can be divided into two
groups: named and anonymous. Named blocks are used when creating
subroutines. These subroutines are procedures, functions, and packages. Anonymous PL/SQL
blocks, as you have probably guessed, do not have names. As a result, they
cannot be stored in the database and referenced later.
PL/SQL blocks contain three sections: declaration
section, executable section, and exception-handling section. The
executable section is the only mandatory section of the block. Both the
declaration and exception handling sections are optional. As a result, a PL/SQL
block has the following structure:
DECLARE
Declaration
statements
BEGIN
Executable
statements
EXCEPTION
Exception-handling
statements
END;
DECLARATION SECTION
The declaration section is the first section
of the PL/SQL block. It contains definitions of PL/SQL identifiers such as
variables, constants, cursors, and so on. PL/SQL identifiers are covered in
detail throughout the book.
EXECUTABLE SECTION
The executable section is the next section
of the PL/SQL block. This section contains some executable statements that
allow you to manipulate the variables that have been declared in the
declaration section.
EXCEPTION-HANDLING
SECTION
The exception-handling section is the last
section of the PL/SQL block. This section contains statements that are executed
when a runtime error occurs within the block. Runtime errors occur while the
program is running and cannot be detected by the PL/SQL compiler. Once a
runtime error occurs, the control is passed to the exception-handling section
of the block. The error is then evaluated, and a specific exception is raised
or executed.
The following is an example of a PL/SQL
program.
|
-- filename: DisplayAge.sql
SET SERVEROUTPUT ON
DECLARE
num_age NUMBER(3) := 20;
BEGIN
num_age := 23;
DBMS_OUTPUT_PUT_LINE(‘My
age is ‘ || num_age);
END;
/
|
The above example declares a variable named num_age
in the declaration section. This variable has a type of NUMBER, and can store
up to 3 digit long. Its default value is 20.
The executable section begins with num_age
is assigned a new value 23, followed by an output statement that displays the
value on the screen.
Notice that the program ends with a “/”,
which is a required character used to execute the program.
EXECUTING A PL/SQL PROGRAM
PL/SQL can be executed directly in SQL*Plus.
A PL/SQL
program is normally saved with an .sql extension. To execute an anonymous
PL/SQL program, simply type the following command at the SQL prompt:
SQL> @DisplayAge
Or
SQL> execute DisplayAge
in which a file named DisplayAge.sql is
executed.
GENERATING OUTPUT
Like other programming languages, PL/SQL provides a procedure (i.e.
PUT_LINE) to allow the user to display the output on the screen. For a user to
able to view a result on the screen, two steps are required.
First, before executing any PL/SQL program, type the following command
at the SQL prompt (Note: you need to type in this command only once for every
SQL*PLUS session):
SQL> SET SERVEROUTPUT ON;
or put the command at the beginning of the program, right before the
declaration section.
Second, use DBMS_OUTPUT.PUT_LINE in your executable section to
display any message you want to the screen.
Syntax for displaying a message:
DBMS_OUTPUT.PUT_LINE(<string>);
in which PUT_LINE is the procedure to generate the output on the screen,
and DBMS_OUTPUT is the package to which the PUT_LINE belongs.
If we look at the example discussed earlier, notice that the “SET SERVEROUTPUT ON”
is located right before the DECLARE block. Doing so avoid the hassle to type in
the command every time you log into SQL*PLUS.
In the executable section, the PUT_LINE function is used to display the
age after it is changed.
The following shows the result of executing DisplayAge.sql:
|
SQL> @DisplayAge
My age is 23
|
Behind the scene, the DBMS_OUTPUT.PUT_LINE
procedure writes the message to be displayed to the buffer for storage. Once a
program has been completed, the information in the buffer is displayed on the
screen. The size of the buffer is initialized at 2000 bytes, and can be set
between 2000 and 1,000,000 bytes. To change the buffer size, use the following
command instead of the default “SET SERVEROUTPUT ON”:
SQL> SET SERVEROUTPUT ON SIZE 10000;
Bear in mind that if the message to be
displayed in a program exceeds the defined buffer size, a warning message will
be prompted and the subsequent messages after the limited will not be
displayed.
To turn off the feature of displaying a
message on screen, you can type in the following command:
SQL> SET SERVEROUTPUT OFF;
COMMENTS
Single line comments begin with two hyphens.
-- This is a comment.
Multi-line comments begin with /* and end
with */.
/*
This is a multi-line
comment.
*/
TERMINOLOGY
An executable is a file or
the name of a program written using one of the computer programming languages.
The program executes when you type the name of the executable at the command
prompt. For example, when you enter sqlplus
at the command prompt, you will invoke SQLPLUS.
A character set describes the
range of characters that a computer language supports and displays in reports.
Most programming languages, including PL/SQL, can display as text and almost
any other character set.
Arithmetic operators are mathematical
operations which are use to do calculations with data. Common operators are +, ‑,
*, and /.
Relational operators are used for comparison
with data, such as comparing two dates to see their relationship to one
another. Common operators are >, <, and <>.
Variables are programmer‑defined names to
hold items of information.
A data type defines the class
or a type of a piece of information, usually a variable. Common data types
are numeric or character. The character data type contains all
representable characters from a specific alphabet. The numeric data type
contains the decimal digits 0 through 9.
A loop is a construct in a
PL/SQL‑ program where a segment of code is executed repeatedly.
An EXIT condition is incorporated as part of a loop; a test is usually
performed on data, if the test evaluates to TRUE, the loop terminates (the
program control exits the loop).
Construction structures influence
the flow of processing in a computer program. If there were two different ways
to process data, the mechanism used to decide which processing route to follow
is a control structure.
PL/SQL CHARACTER SET
CHARACTERS
SUPPORTED
The PL/SQL engine accepts four
types of characters: letters, digits, symbols
(*, +, -, = ..etc), and white
space. When elements from one of these character types are joined together,
they will create a lexical unit (these lexical units can be a combination of
character types). The lexical units are the words of the PL/SQL language.
LEXICAL UNITS
A programming language has lexical
units that are the building blocks of the language. PL/SQL lexical units
fall within one of the following five groups:
1. Identifiers.
Identifiers must begin with a letter and may be up to 30 characters long. See a
PL/SQL manual for a more detailed list of restrictions; generally, if you stay
with characters, numbers, and ‘ ‘ and avoid reserved words, you will not run
into problems.
2. Reserved Words.
Reserved words are words that PL/SQL saves for its own use (e.g., BEGIN, END,
SELECT).
3. Delimiters. These
are characters that have special meaning to PL/SQL, such as arithmetic
operators and quotation marks.
4. Literals. A literal
is any value (character, numeric, or Boolean [true/false]) that is not an
identifier. 123, “Declaration of Independence” and FALSE are examples of
literals.
5. Comments. These can
be either single-line comments (i.e., --) or multi-line comments
(i.e., /* */).
ARITHMETIC AND RELATIONAL OPERATORS
ARITHMETIC OPERATORS
The following table shows the common
arithmetic operators used in PL/SQL. They are very similar to those used in
other programming language such as C:
|
Operators
|
Meaning
|
|
+
|
Addition
|
|
-
|
Subtraction
|
|
*
|
Multiplication
|
|
**
|
Exponentiation
|
|
/
|
Division
|
RELATIONAL OPERATORS
The next table shows the PL/SQL relational
operators. If you have experience with any other programming languages, then
you have seen these before.
|
Operator
|
Meaning
|
|
<>
|
Not equal
|
|
^=
|
Not equal
|
|
!=
|
Not equal
|
|
>
|
Greater than
|
|
<
|
Less than
|
|
=
|
Equal
|
|
>=
|
Greater than or equal to
|
|
<=
|
Less than or equal to
|
MISCELLANEOUS SYMBOLS
PL/SQL also supports the following symbols.
Each symbol has its own meaning or its own use in PL/SQL‑ programming. This
table shows a partial list of symbols; they are the most commonly used symbols:
|
Symbol
|
Meaning
|
Example
|
|
()
|
list separators
|
and NAME in ('SMITH','JOHNSON)
|
|
;
|
end of statement
|
procedure_name (arg1, arg2)
|
|
.
|
item separator
|
employee.last_name
|
|
‘
|
character string enclosure
|
if string_a = ‘SMITH’
|
|
:=
|
Assignment
|
num1 := num1 + 1
|
|
||
|
Concatenation
|
full_name := ‘JOHN’||’ ‘||’SMITH’
|
|
--
|
comment delimiter
|
-- this is a comment
|
|
/* and */
|
comment delimiter
|
/* this is also a comment */
|
VARIABLES
Variables are names used in PLISQL to process
items of data. Not every word that you choose can be a variable in PL/SQL. The
variables must conform to the following rules:
·
Variables
must start with a letter (a-z, A‑Z).
·
Variables
can be optionally followed by one or more letter, numbers (0‑9), or the special
characters $, #, or _.
·
Variables
must be no longer than 30 characters.
·
There
can be no space imbedded in the variable name.
|
Variable Name
|
Valid?
|
Reason
|
|
23_skidoo
|
No
|
Must start with a letter
|
|
nature_trail
|
Yes
|
|
|
nature-trail
|
No
|
“-“ is not allowed
|
|
love boad
|
No
|
Cannot contain any white space
|
|
a_very_insignificant_variable_name
|
No
|
Longer than 30 characters
|
|
Me______and$$$$you
|
Yes
|
|
|
Lots_of_$$$$$$$$$$
|
Yes
|
|
|
23
|
No
|
Must start with a letter
|
RESERVED WORDS
Reserved words are "copyrighted" by
PL/SQL. You are NOT allowed to use a reserved word as a variable name. For
example, the word “Loop" has a special
meaning to PL/SQL, and the following segment of code would be invalid.
|
DECLARE
loop NUMBER; -- INVALID. Loop is a keyword.
BEGIN
…
END;
/
|
To see a complete set of reserved words, log on
to SQL*Plus and type help commands.
COMMON DATA TYPES
In
the table given below PL/SQL data types and sample declarations are summarized
|
Data Type
|
Usage
|
Sample Declaration
|
|
VARCHAR2
|
Variable-length character strings
|
FNAME VARCHAR2(30);
|
|
CHAR
|
Fixed-length character strings
|
StudentGender CHAR(1);
|
|
NUMBER
|
Floating, fixed-point, or integer numbers
|
SALARY NUMBER(6);
|
|
BINARY_INTEGER
|
Integers
|
StudID BINARY_INTEGER;
|
|
DATE
|
Dates
|
TodaysDate DATE;
|
|
BOOLEAN
|
True/False values
|
OrderFlag BOOLEAN;
|
|
LOB
(It has two data types BLOB and CLOB) |
Large Objects
|
Message CLOB;
|
|
%TYPE
|
Assumes the data type of the database
field
|
CustAddress customer.cadd%TYPE;
|
|
%ROWTYPE
|
Assumes the data type of a database row
|
CustOrderRecord cust_order%ROWTYPE;
|
Now,
let us look into more details of these data types in PL/SQL
VARCHAR2
The
VARCHAR2 data type holds variable-length[1]
string data up to a maximum length of 32,767 characters. When you declare this
type of variable, you must specify the maximum field length.
CHAR
The
CHAR data type is used for fixed-length[2]
character strings to a maximum of 32,767 characters. When you declare a CHAR
variable, you must specify the maximum width. When the variable is assigned a
data value, if the entire field width is not filled with characters, the
remaining is padded with blank spaces.
NUMBER
The
NUMBER data type is identical to SQL NUMBER data type, with the following
general format:
NUMBER
(<precision>, <scale>).
The
precision specifies the total length of the number, including decimal places;
the scale specifies the number of digits to the right of the decimal place.
When
you declare a NUMBER variable, you specify the precision only for integer
values, and both the precision and scale for fixed-point values.
|
DECLARE
num_pi NUMBER(9,7) := 3.1415926;
BEGIN
…
END;
/
|
In
the above example, a variable named num_pi is declared as a NUMBER
variable, and defaulted to 3.1415926.
The precision is 9 as there are 9 digits all
together including the decimal point. The scale is 7 because you want 7 digits
to be placed after the decimal point.
BINARY_INTEGER
The
BINARY_INTEGER data type can also be used to represent integer values.
Advantages
of using BINARY_INTEGER:
- Data values are stored internally in binary format, which takes slightly less storage space then the NUMBER data type.
- Calculations can be performed on BINARY_INTEGER data values more quickly than on integer NUMBER values.
DATE
The
DATE data type is same as in SQL and can store both date and time values.
BOOLEAN
The BOOLEAN data type is used to store
TRUE/FALSE values. When this data type is declared, it has the value NULL until
it is assigned TRUE or FALSE.
LOB
The
LOB is used to store binary data such as sounds or images. The two most
important types are the BLOB (Binary LOB) and CLOB (Character
LOB). Once you have declared a field (while creating the table) to be of CLOB
data type you can insert a big sentence with in single quotes into that
particular field. CLOB stores up to 4 GB of character data.
TYPE AND ROWTYPE
Two
other data types that are often used in PL/SQL are the reference data types %TYPE
and %ROWTYPE, which assumes the same data type as a referenced database
column or row.
Syntax
for %TYPE data declaration:
<Variable
name> <table name>.<field name>%TYPE;
Suppose
you want to declare a variable name var_firstName with the same data
type as the FNAME field in the EMPLOYEE table. The declaration would be written
as
var_firstname employee.fname%TYPE;
The
variable var_firstname would assume a data type of VARCHAR2(12), same as
the data type of the FNAME field in the EMPLOYEE table.
Advantages
of using the %TYPE data type:
- First,
you need not know the exact data type of
FNAME. - If
you change the database definition of FNAME (make it a longer character
string, for example), the data type of FNAME
Syntax
for %ROWTYPE data type:
<row
variable name> <table name>%ROWTYPE;
The
ROWTYPE reference data type is useful when you want to declare a variable to
hold an entire row of data. The following code will declare a variable that
holds an entire row of data that was retrieved from the EMPLOYEE table:
emprec
employee%ROWTYPE;.
This variable would consist of all ten
fields from the EMPLOYEE table, and each would have the same data type as the
associated database field. To access a specific field in this variable, for
instance, the SSN of an employee, we can use the following method:
emprec.emp_ssn
More details about using ROWTYPE will be
explained later in Chapter 2.
SELECT INTO
There are three ways of initializing or
assigning a value to a variable in a PL/SQL block. The first one is using “:=”,
which is discussed in the 1.8 Miscellaneous Symbols section. An example is:
var_age := 23;
Another way is using the SELECT INTO
command. An example follows:
|
DECLARE
var_ssn employee.emp_ssn%TYPE;
var_dpt_name
department.dpt_name%TYPE;
var_dpt_mgrssn
department.dpt_mgrssn%TYPE;
BEGIN
SELECT emp_ssn INTO var_ssn
FROM employee
WHERE emp_last_name =
‘Bordoloi’;
SELECT dpt_name, dpt_mgrssn
INTO var_dpt_name, var_dpt_mgrssn
FROM department
WHERE dpt_no = 7;
…
END;
/
|
The above example retrieves the employee’s
SSN for the employee with last name ‘Bordoloi’ from the EMPLOYEE table, and
assigns the value to the variable var_ssn.
You can also use SELECT INTO to assign
values to more than one variable, as illustrated in the second SELECT statement
in the above example.
If no value is returned from the SELECT
statement, the value of the variable is set to NULL.
SUBSTITUTION VARIABLES
The third way of assigning a value to a variable is using a substitution
variable. With the help of a substitution variable, SQL*PLUS allows a PL/SQL
block to receive input information from the user.
Substitution variables are prefixed by an ampersand (&) or a double
ampersand (&&).
Examples:
&in_emp_ssn
&&in_dept_no
To understand how substitution variables works, lets look at the following
example:
|
-- DisplayDays.sql
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Yesterday was ‘ || &in_day);
DBMS_OUTPUT.PUT_LINE(‘Today
is ‘ || &in_day2);
DBMS_OUTPUT.PUT_LINE(‘Tomorrow will be ‘ || &in_day); -- same as
the first one.
END;
/
|
When the above example is executed at the SQL prompt, you will be asked
to provide 3 values, one for &in_day, one for &in_day2,
and again another one for &in_day. In other words, you will be
prompted to provide a value for every substitution variable with a single
ampersand (&), regardless of whether the variable is reused or not. The
following is the output of the above example.
|
SQL>
@DisplayDays
Enter
value for in_day: Sunday
old
1: DBMS_OUTPUT.PUT_LINE(Yesterday was ‘||’
&in_day');
new
1: DBMS_OUTPUT.PUT_LINE(Yesterday was ‘||’
Sunday');
Enter
value for in_day2: Monday
old
2: DBMS_OUTPUT.PUT_LINE('Today is ‘||’ &in_day2');
new
2: DBMS_OUTPUT.PUT_LINE('Today is ‘||’ Monday');
Enter
value for in_day: Tuesday
old
3 DBMS_OUTPUT.PUT_LINE('Tomorrow will be ‘||’ &in_day');
new
3: DBMS_OUTPUT.PUT_LINE('Tomorrow will be ‘||’ Tuesday');
Yesterday
was Sunday
Today
is Monday
Tomorrow
will be Tuesday
PL/SQL
procedure successfully completed.
|
Let’s
look at a different example.
|
-- DisplayDays2.sql
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Yesterday was ‘ || &&in_day); -- now it
has && instead.
DBMS_OUTPUT.PUT_LINE(‘Today
is ‘ || &in_day2);
DBMS_OUTPUT.PUT_LINE(‘Tomorrow will be ‘ || &in_day); -- same as
the first one.
END;
/
|
Notice that the first output line uses a double ampersand (&&)
variable instead. When the program is executed, you will be prompted only to
provide 2 values, one for &&in_day, and another one for &in_day2.
The third substitution variable, &in_day, will use the value entered
previously, which is the value of &&in_day. The following is the
output of the above example.
|
SQL>
@DisplayDays2
Enter
value for in_day: Sunday
old
1: DBMS_OUTPUT.PUT_LINE(Yesterday was ‘||’
&in_day');
new
1: DBMS_OUTPUT.PUT_LINE(Yesterday was ‘||’
Sunday');
Enter
value for in_day2: Monday
old
2: DBMS_OUTPUT.PUT_LINE('Today is ‘||’ &in_day2');
new
2: DBMS_OUTPUT.PUT_LINE('Today is ‘||’ Monday');
old
1: DBMS_OUTPUT.PUT_LINE('Tomorrow will be ‘||’ &in_day');
new
1: DBMS_OUTPUT.PUT_LINE('Tomorrow will be ‘||’ Sunday');
Yesterday
was Sunday
Today
is Monday
Tomorrow
will be Sunday
PL/SQL
procedure successfully completed.
|
You can use substitution variables to:
1) Initialise a variable in the
declaration section.
2) Define a value, column, or
condition.
The following example shows different ways of using substitution
variables.
|
SET SERVEROUTPUT ON
DECLARE
var_ssn employee.emp_ssn%TYPE;
var_last_name
employee.emp_last_name%TYPE := ‘&in_last_name’;
var_dpt_name department.dpt_name%TYPE;
var_dpt_mgrssn
department.dpt_mgrssn%TYPE;
var_dpt_no
department.dpt_no%TYPE := &in_dpt_no;
var_col
VARCHAR2(20);
BEGIN
SELECT emp_ssn INTO var_ssn
FROM employee
WHERE emp_last_name = var_last_name
AND emp_first_name = ‘&in_first_name’;
SELECT dpt_name, dpt_mgrssn
INTO var_dpt_name, var_dpt_mgrssn
FROM department
WHERE dpt_no = var_dpt_no;
SELECT &in_col INTO
var_col
FROM department
WHERE &condition; --
make sure that &in_vol is compatible with the type of var_col
…
END;
/
|
If you prefer not to see the messages produced by the script (i.e. old
1: …), you can turn off the verification message by doing this at the SQL
prompt:
SQL> SET VERIFY OFF;
or put this command at the beginning of your program, like the following
example.
|
-- DisplayDays2.sql
SET SERVEROUTPUT ON
SET VERIFY OFF
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Yesterday was ‘ || &&in_day); -- now it
has && instead.
DBMS_OUTPUT.PUT_LINE(‘Today
is ‘ || &in_day2);
DBMS_OUTPUT.PUT_LINE(‘Tomorrow will be ‘ || &in_day); -- same as
the first one.
END;
/
|
and the output of the above example is:
|
SQL>
@DisplayDays2
Enter
value for in_day: Sunday
Enter
value for in_day2: Monday
Yesterday
was Sunday
Today
is Monday
Tomorrow
will be Sunday
PL/SQL
procedure successfully completed.
|
REVIEW EXERCISES
Learn these
Terms
1.
Block: A block is the most basic unit in PL/SQL.
2.
Declaration section: It contains definitions of PL/SQL
identifiers such as variables, constants, cursors, and so on.
3.
Executable section: This section contains executable
statements that allow you to manipulate the variables that have been declared
in the declaration section.
4.
Exception handling section: This section contains
statements that are executed when a runtime error occurs within the block.
5.
Lexical units:
are the building blocks of the language.
6.
Reserved
Words: Reserved words are words that PL/SQL saves for its own use.
7.
Delimiters:
These are characters that have special meaning to PL/SQL, such as arithmetic
operators and quotation marks.
8.
Literals:
A literal is any value (character, numeric, or Boolean [true/false]) that is
not an identifier.
9.
Substitution variables: A substitution variable is a
variable preceded by one (&) or two ampersands (&&).
Concepts
Quiz
1. SQL
statements combined into PLSQL blocks cause an increase in the network traffic
a. True
b. False
2. What
is the meaning for the arithmetic operator ** in PLSQL?
3. What
is the symbol used to concatenate two strings or variables?
4. How
do we give single line comments and multi line comments?
5. Which
of the following is a valid variable name:
a. Emp-sal
b. Emp_sal
c. Emp
sal
d. 12_Emp_sal
6. What
is the advantage of using varchar2?
7. Declare
a number data type variable of length 8 including 2 decimal places.
8. What
are the advantages of using %type?
9. What
is the syntax for declaring %type variable?
10. Name the
three sections in a PLSQL block?
11. Of the
three sections in the PLSQL block, which are mandatory?
12. What
are the advantages of using PLSQL blocks when compared to individual SQL
statements?
13. The
exception section in a PLSQL block is used to
a.
Handle compilation errors
b.
Handle run time errors
c.
Handle both runtime and compilation errors.
14. Which
of the following will declare a variable that is of the identical datatype as
the emp_ssn in the database table employee in the company database?
a.
v_id emp_ssn := 123;
b.
v_id binary integer;
c.
v_id number := 24;
d.
v_id employee.emp_ssn%type;
Coding Exercises and Questions
1.
[easy] Write a PL/SQL program to display your name, the
current semester and session of this course. Add at least one comment line at
the beginning of this program to display the current date.
2.
[easy] Use the SELECT INTO command to display the
department name, its manager’s SSN and start date for department 1 using the
DEPARTMENT table in the COMPANY database (Check out the Appendix A).
3.
[easy] Modify question 2 to display the same
information with the department number provided by the user.
4.
[medium] Write a PL/SQL program to display your name
and your age. This program should accept two values, your name and your
birthday in the format of ‘MM-DD-YYYY’. Hint: Use SYSDATE and TRUNC to
calculate your age.
5.
[easy] Write a program to calculate the following
formula:
a
= b + cd^2 – e / 4
in which b, c, d, and e are provided by the
user. Display only the result (i.e. the value of a).
6.
[easy] Write a program to accept three numbers and calculate their
average.
[1] Suppose
you are storing “John” in the variable name “FNAME” declared as FNAME
VARCHAR2(30). In this case, even though 30 spaces are allotted for this
variable, it will occupy only 4 spaces (John has 4 characters), and the rest
will be freed for some other use.
[2] In this
case, “John” occupies the first 4
spaces. However, the remaining 26 locations (out of 30) will still belong to
the variable but contain only empty spaces. This is generally a wastage of
memory resource.
No comments:
Post a Comment