ORACLE 4: Introduction to PLSQL - PowerPoint PPT Presentation

1 / 67
About This Presentation
Title:

ORACLE 4: Introduction to PLSQL

Description:

Interpreter flags the line number and character location of syntax errors. If error message appears and the flagged line appears correct, the error usually ... – PowerPoint PPT presentation

Number of Views:165
Avg rating:3.0/5.0
Slides: 68
Provided by: jason3
Category:

less

Transcript and Presenter's Notes

Title: ORACLE 4: Introduction to PLSQL


1
ORACLE 4Introduction to PL/SQL
  • Jason C. H. Chen, Ph.D.
  • Professor of MIS
  • School of Business Administration
  • Gonzaga University
  • Spokane, WA 99258
  • chen_at_jepson.gonzaga.edu

2
Lesson A Objectives
  • After completing this lesson, you should be able
    to
  • Learn the fundamentals of the PL/SQL programming
    language
  • Write and execute PL/SQL programs in SQLPlus
  • Understand PL/SQL data type conversion functions
  • Manipulate character strings in PL/SQL programs
  • Learn how to debug PL/SQL programs

3
What is PL/SQL?
  • Procedural programming Language
  • Uses detailed instructions
  • Processes statements sequentially
  • Combines SQL commands with procedural
    instructions
  • Used to perform sequential processing using an
    Oracle database

4
Why PL/SQL?
ORACLE (programming language components)
SQL
D.B.
______
______ Components (reports, forms, graphics etc.)
5
Using SQL Commands in PL/SQL Programs
6
Fundamentals of PL/SQL
  • Full-featured programming language
  • Execute using Oracle 10g utilities
  • SQLPlus
  • Forms Builder
  • An interpreted language
  • Semicolon ends each command
  • Reserved words
  • Type in editor, execute in SQLPlus

7
Table 4-1 PL/SQL command capitalization styles
(p.191)
8
Identifiers and VariablesRules for Names and
Properties
  • From 1 to 30 characters
  • Only alphanumeric characters, and special
    characters ( _ )
  • Must begin with a letter and can not contain
    blank spaces or hyphens
  • And sure they cannot be reserved words (e.g.,
    BEGIN)

9
Variables and Data Types
  • Variables
  • Used to store numbers, character strings, dates,
    and other data values
  • Avoid using keywords, table names and column
    names as variable names
  • Must be declared with data type before use
  • variable_name data_type_declaration
  • e.g., current_s_id NUMBER(6)

10
Language ElementsPL/SQL Data Types
  • Scalar
  • References a single value
  • Composite
  • References a data structure
  • Reference
  • References a specific database item (e.g., TYPE,
    ROWTYPE)
  • LOB
  • References a large binary object

11
Scalar Data Types
  • Represent a single value
  • Database scalar data types

12
Scalar Data Types (cont.)
  • Represent a single value
  • Non-database scalar data types

13
Composite and Reference Variables
  • Composite variables
  • RECORD contains multiple scalar values, similar
    to a table record
  • TABLE tabular structure with multiple columns
    and rows
  • VARRAY variable-sized array
  • Reference variables
  • Directly reference a specific database field or
    record and assume the data type of the associated
    field or record
  • TYPE same data type as a database field
  • ROWTYPE same data type as a database record

14
Tables 4-2, 4-3 PL/SQL data types
c_address customer.customer_addressTYPE
15
Block Structure
PL/SQL is a block structured language. That
just means everything you do is done in terms of
blocks of code. All PL/SQL blocks share the same
basic structure.
-- Figure 4-1 p.195 DECLARE /HEADER
ltvariable declarationsgt BEGIN ltbody
executable codegt EXCEPTION ltexception/error
handling codegt END
Comments Not executed by interpreter
Enclosed between / and / On one line
beginning with --
16
Comment Statements
There are two types of comments in PL/SQL
Single-line and multi-line. Single Line x 4
-- assign 4 to the variable x
  • Multi-line
  • / Assign 4 to
  • the variable x /
  • x 4

17
PL/SQL Arithmetic Operators in Describing Order
of Precedence
18
Assignment Statements
  • Assigns a value to a variable
  • variable_name value
  • Value can be a literal
  • current_s_first_name 'John'
  • Value can be another variable
  • current_s_first_name s_first_name

19
Executing a PL/SQL Program in SQLPlus
  • Create program in text editor (using Notepad)
  • Paste into SQLPlus window
  • Press Enter, type / then enter to execute

Practice Figures 4-3 (4-4) p.200
20
PL/SQL Data Conversion Functions
21
Data Type Conversion Functions
Function Description
Example TO_DATE Converts a character
string TO_DATE(07/14/01,MM/DD/YY)
to a date TO_NUMBER
Converts a character string TO_NUMBER(2)
to a number TO_CHAR
Converts either a number or TO_CHAR(2)
a date to a character
string TO_CHAR(SYSDATE) if today is
return March
TO_CHAR(SYSDATE, MONTH) Tuesday,
return Tuesday
TO_CHAR(SYSDATE, DAY) March 15,
return 15
TO_CHAR(SYSDATE, DD) 2006
return 1030 AM
TO_CHAR(curr_time, HHMI AM)
22
Manipulating Character Strings with PL/SQL
  • To concatenate two strings in PL/SQL, you use the
    double bar () operator
  • new_string string1 string2
  • To remove blank leading spaces use the LTRIM
    function
  • string LTRIM(string_variable_name)
  • To remove blank trailing spaces use the RTRIM
    function
  • string RTRIM(string_variable_name)
  • To find the number of characters in a character
    string use the LENGTH function
  • string_length LENGTH(string_variable_name)

Practice Figures 4-5 p.203
23
Manipulating Character Strings with PL/SQL
  • To change case, use UPPER, LOWER, INITCAP
  • INSTR function searches a string for a specific
    substring and return the starting position
  • start_position INSTR(original_string,
    substring)
  • SUBSTR function extracts a specific number of
    characters from a character string, starting at a
    given point
  • extracted_string SUBSTR(string_variable,
    starting_point, number_of_characters)

Practice Figures 4-6, 4-7 p.205,207
24
-- Chapter 4, Figure 4-6, p.205 --PL/SQL program
to display the current date DECLARE
todays_date DATE current_day
VARCHAR2(9) current_day_length
BINARY_INTEGER BEGIN todays_date
SYSDATE -- extract day portion from current
date, and trim trailing blank spaces
current_day TO_CHAR(todays_date, 'DAY')
current_day RTRIM(current_day) -- convert
day to mixed case letters with initial letter
capitalized current_day INITCAP(current_day)
-- determine length of day's character
string current_day_length
LENGTH(current_day) DBMS_OUTPUT.PUT_LINE('Toda
y''s date is ' current_day ', '
TO_CHAR(todays_date)) DBMS_OUTPUT.PUT_LINE('T
he length of the word ' current_day '
is ' TO_CHAR(current_day_length) '
characters.') END
25
Debugging PL/SQL Programs
  • Syntax error
  • Command does not follow the guidelines of the
    programming language
  • Generates compiler or interpreter error messages
  • Logic error
  • Program runs but results in an incorrect result
  • Caused by mistake in program

? RUN-TIME error
Practice Figures 4-7, 4-8 p.207, p.208
26
Finding and Fixing Syntax Errors
  • Interpreter flags the line number and character
    location of syntax errors
  • If error message appears and the flagged line
    appears correct, the error usually occurs on
    program lines preceding the flagged line
  • Comment out program lines to look for hidden
    errors
  • One error (such as missing semicolon) may cause
    more fix one error at a time

27
Finding and Fixing Logic Errors
  • Locate logic errors by viewing variable values
    during program execution
  • There is no SQLPlus debugger
  • Use DBMS_OUTPUT statements to print variable
    values

Practice Figures 4-9, 4-10 p.209,210
28
-- Chapter 4, Figure 4-11, p.212 -- Program with
a logic error and debugging statements DECLARE
curr_call_id VARCHAR2(30) 'MIS 101'
blank_space NUMBER(2) curr_dept VARCHAR2(30)
curr_number VARCHAR2(30) BEGIN blank_space
INSTR(curr_call_id,' ') curr_dept
SUBSTR(curr_call_id, 1, (blank_space - 1))
DBMS_OUTPUT.PUT_LINE('Call ID department is '
curr_dept) DBMS_OUTPUT.PUT_LINE('Original
string value ' curr_call_id)
DBMS_OUTPUT.PUT_LINE('Start position '
blank_space) DBMS_OUTPUT.PUT_LINE('Number of
characters ' (LENGTH(curr_call_id) -
blank_space)) curr_number
SUBSTR(curr_call_id, blank_space,
(LENGTH(curr_call_id) - blank_space))
DBMS_OUTPUT.PUT_LINE('Course Number is '
curr_number) END
Call ID department is MIS Original string value
MIS 101 Start position 4 Number of characters
3 Course Number is 10 PL/SQL procedure
successfully completed.
29
Break ! (Ch. 4 - Part A)Problem Solving Cases
  • In class exercise
  • - 1 () p. 217
  • - see spool instruction on the next slide
  • HW
  • 3(, SUBSTR,)
  • 4(TO_DATE, TO_CHAR with format mask)
  • -5( , TO_CHAR, with format mask)
  • Script file name (contains 3,4,5)
  • Ch4ACase_Lname_Fname.sql
  • Spooled file name
  • Oracle4A_Spool_Lname_Fname.LST

Email me with SPOOLED file ONLY
to chen_at_jepson.gonzaga.edu with subject title of
bmis441_Oracle4A
30
Spool ling SQLPlus Commnds and Output a File
(P.47)
  • After you test your HW (i.e., Ch4CaseA_Lname_Fname
    .sql) successfully, you should generate a file
    contains both your SQL commands and associated
    output.
  • Start SQLPlus
  • Click File and point to Spool then click Spool
    File
  • type Oracle4A_Spool_Lname_Fname in the File name
    text box (file extension of LST will be added
    automatically)
  • open your script file and copy one problem at
    time to the SQLPlus.
  • The SQL commands and its solutions are saved on
    the spool file.
  • Click File on the menu bar, point to Spool, the
    click Spool Off.
  • Now, both SQL and output are saved on the spooled
    file. You may use notepad to check the file.

31
Lesson B Objectives
  • After completing this lesson, you should be able
    to
  • Create PL/SQL decision control structures
  • Use SQL queries in PL/SQL programs
  • Create loops in PL/SQL programs
  • Create PL/SQL tables and tables of records
  • Use cursors to retrieve database data into PL/SQL
    programs
  • Use the exception section to handle errors in
    PL/SQL programs

32
PL/SQL Decision Control Structures
  • Sequential processing
  • Processes statements one after another
  • Decision control structures
  • Alter order in which statements execute
  • Based on values of certain variables

33
PL/SQL Comparison Operators (p.220)
34
PL/SQL Decision Control Structures
  • Use IF/THEN structure to execute code if
    condition is true
  • IF condition THEN
  • commands that execute if condition is TRUE
  • END IF
  • If condition evaluates to NULL it is considered
    false
  • Use IF/THEN/ELSE to execute code if condition is
    true or false
  • IF condition THEN
  • commands that execute if condition is TRUE
  • ELSE
  • commands that execute if condition is FALSE
  • END IF
  • Can be nested be sure to end nested statements
    (see next slide)

Practice Figures 4-12 to 4-14 p.217 to p.223
35
Nested IF/THEN/ELSE
  • Placing one or more IF/THEN/ELSE statements
    within program statements that execute after IF
    or ELSE command
  • Important to properly indent program lines

Practice Figures 4-15 p.224
36
IF/ELSIF
  • Use IF/ELSIF to evaluate many conditions
  • IF condition1 THEN
  • commands that execute if condition1 is TRUE
  • ELSIF condition2 THEN
  • commands that execute if condition2 is
    TRUE
  • ELSIF condition3 THEN
  • commands that execute if condition3 is
    TRUE
  • ...
  • ELSE
  • commands that execute if none of the
  • conditions are TRUE
  • END IF

Practice Figures 4-16 p.225 (next slide)
37
IF/ELSIF Example
4-16
38
Complex Conditions (AND, OR, and NOT)
  • Created with logical operators AND, OR and NOT
  • Order of evaluation
  • NOT
  • AND
  • OR
  • It is highly recommended to use () to set
    precedence.

Practice Figure 4-17 (logical error)
p.227 Figure 4-19 p.228
39
Using SQL Commands in PL/SQL Programs
Practice Figures 4-20 p.231 (next slide)
40
Using SQL Commands in PL/SQL Programs
  • _at_ c\OraData\chapter4\emptynorthwoods.sql
  • SELECT FROM term
  • Figure 4-20, p.231
  • SELECT FROM term

41
Loops
  • Systematically executes program statements
  • Periodically evaluates exit condition to
    determine if loop should repeat or exit
  • Pretest loop
  • Evaluates exit condition before any program
    commands execute
  • Posttest loop
  • Executes program commands before loop evaluates
    exit condition for first time
  • PL/SQL has four (4) loop structures

42
Basic LOOP Statement(couple of ways to break out)
--p.232 CREATE TABLE count_table (counter
NUMBER(2)) -- Figure 4-21, p.233 SET
SERVEROUTPUT ON DECLARE loop_count
BINARY_INTEGER 1 BEGIN LOOP
INSERT INTO count_table VALUES(loop_count)
IF loop_count 5 THEN
EXIT END IF
loop_count loop_count 1 END
LOOP END / SELECT FROM count_table
I. LOOP EXIT LOOP ltcodegt IF
(condition) THEN EXIT --true, exit
END IF ltmore_codegt -- false,
loop again END LOOP
43
Basic LOOP Statement (conti.)Please note that II
III are slightly different from the text.
II. LOOP EXIT WHEN -- see Figure 4-22,
p.234 LOOP ltcodegt EXIT WHEN
condition END LOOP -- infinite loop LOOP
ltcodegt END LOOP
DELETE FROM count_table --Figure 4-22
p.234 DECLARE loop_count BINARY_INTEGER
1 BEGIN LOOP INSERT
INTO count_table
VALUES(loop_count) loop_count
loop_count 1 EXIT WHEN
loop_count 6 END LOOP END / SELECT
FROM count_table
Posttest
44
Basic LOOP Statement (conti.)
Pretest
III. WHILE LOOP -- see Figure 4-23, p.235 WHILE
ltconditiongt LOOP ltcodegt END LOOP
IV. Numeric FOR Loop -- see Figure 4-24,
p.236 FOR ltcounter vargt IN n1 .. n2 LOOP
ltcodegt END LOOP
Preset number of iterations
-- Chapter 4, Figure 4-23, p.235 DELETE FROM
count_table DECLARE loop_count
BINARY_INTEGER 1 BEGIN WHILE loop_count lt 6
LOOP INSERT INTO count_table
VALUES(loop_count) loop_count
loop_count 1 END LOOP END SELECT FROM
count_table
DELETE FROM count_table --Figure 4-24
p.236 DECLARE BEGIN FOR loop_count IN 1..5
LOOP INSERT INTO count_table
VALUES(loop_count) END LOOP END SELECT
FROM count_table
45
Exercise time
(Write the program that uses a loop to calculate
the areas of five circles, starting with a circle
of radius 1 and ending with a circle of radius 5)
-- HINT FOR EXERCISE DECLARE ltvariables
declarationsgt BEGIN ... FOR lt gt IN 1 ..
5 LOOP ltother codes heregt
DBMS_OUTPUT.PUT_LINE( ) END LOOP END
-- A sample output For a circle with radius 1,
the circumference is 6.28 and the area is
3.14. For a circle with radius 2, the
circumference is 12.57 and the area is 12.57. For
a circle with radius 3, the circumference is
18.85 and the area is 28.27. For a circle with
radius 4, the circumference is 25.13 and the area
is 50.27. For a circle with radius 5, the
circumference is 31.42 and the area is 78.54.
46
HW
  • Write a PL/SQL and using IF/ELSIF
  • Write a command to declare a date variable named
    current_date, and assign to it the current system
    date. Depending on the day of the month, your
    program should display the following output

Day Output 1-10 It is Day ltday numbergt
of ltmonth namegt. It is early in the month. 11-20
It is Day ltday numbergt of ltmonth namegt. It is
the middle of the month. 21-31 It is Day ltday
numbergt of ltmonth namegt. It is nearly the end of
the month.
For example, It is Day 13 of November. It is the
middle of the month.
47
Cursors
In PL/SQL, a cursor is a construct (pointer),
which is a variable that contains the address of
the memory location that contains the SQL
commands context area. It is used to retrieve
and manipulate database data.
There are two types implicit and explicit. As
you might expect, one type (implicit) is rather
limited and I heartily recommend that you do not
use it. But lets look at both types.
48
Implicit Cursors
  • Context area
  • Contains information about query
  • Created by INSERT, UPDATE, DELETE, or SELECT
  • Active set
  • Set of data rows that query retrieves
  • Implicit cursor
  • Pointer to context area

49
Cursor to Return all Rows in COURSE Table
Database Server Memory
Number of rows processed
Parsed SQL command
C_ID CALL_ID C_NAME CRDDITS
1 MIS101 Intro.to info.system 3
2 MIS301 System Analysis 3
3 MIS441 Database Management 3
4 CS155 Programming in C 3
5 MIS451 Client/Server Systems 3
Figure 4-25
50
Cursor to Return all Rows in COURSE Table
Database Server Memory
Number of rows processed
Parsed SQL command
COURSE_NO COURSE_NAME CRDDITS
MIS101 Intro.to info.system 3
MIS301 System Analysis 3
MIS441 Database Management 3
CS155 Programming in C 3
MIS451 Client/Server Systems 3
Figure 4-25 (based on new database)
51
Implicit Cursors
An implicit cursor is created by PL/SQL
automatically (hence you do not need to declare)
when you execute a SQL statement directly within
a PL/SQL block. For inserts, updates, and
deletes, there is no problem using an implicit
cursor. With queries such as SELECT, there is a
problem. If the query returns more than one
record or does not return any record, it will
cause an exception (error). Thus you should
always use explicit cursors for queries.
52
Implicit Cursors (conti.)
Syntax SELECT ltdata field(s)gt INTO ltdeclared
var name(s)gt FROM lttable name(s)gt WHERE ltsearch
conditiongt -- it should return a single --
record, or error occurred
START c\OraData\Chapter4\Clearwater.sql START
c\OraData\Chapter4\Northwoods.sql --CHAPTER 4,
FIGURE 4-26, P.239 DECLARE current_f_last
faculty.f_lastTYPE current_f_first
faculty.f_firstTYPE BEGIN SELECT f_last,
f_first INTO current_f_last,
current_f_first FROM faculty WHERE f_id
1 DBMS_OUTPUT.PUT_LINE('The faculty
member''s name is ' current_f_first ' '
current_f_last) END
  • Question
  • How to declare a variable named current_f_last
    that has the same data type as the f_last field
    in the FACULTY table.

L
53
Changes to SELECT in PL/SQL using Implicit Cursor
As you may have noted in examples that used an
implicit cursor earlier in the lesson, there is
an additional clause required for SELECT
statements in PL/SQL. The data you retrieve from
a SELECT has to be put somewhere, thus you must
include an INTO clause with any fetch (be it
implicit or explicit). You must include a
variable in the INTO clause for each value
selected by the SELECT clause.
54
Another Implicit Cursor Example (with error)
-- Chapter 4, Figure 4-27, p.239 DECLARE
current_f_last faculty.f_lastTYPE
current_f_first faculty.f_firstTYPE BEGIN
SELECT f_last, f_first INTO current_f_last,
current_f_first FROM faculty WHERE f_first
LIKE 'J' DBMS_OUTPUT.PUT_LINE('The faculty
member''s name is ' current_f_first ' '
current_f_last) END ERROR at line
1 ORA-01422 exact fetch returns more than
requested number of rows ORA-06512 at line 5
(Figure 4-28, p.240) ORA-01403 No Data found
55
Changes to SELECT in PL/SQL using Implicit Cursor
As you may have noted in examples that used an
implicit cursor earlier in the lesson, there is
an additional clause required for SELECT
statements in PL/SQL. The data you retrieve from
a SELECT has to be put somewhere, thus you must
include an INTO clause with any fetch (be it
implicit or explicit). You must include a
variable in the INTO clause for each value
selected by the SELECT clause.
56
Using an Explicit Cursor
  • Declare the cursor
  • Open the cursor
  • Fetch the cursor result (data rows) into PL/SQL
    program variables
  • Close the cursor

DECLARE CURSOR cursor_name IS SELECT_statement
OPEN cursor_name
LOOP FETCH cursor_name INTO
variable_name(s) EXIT WHEN cursor_nameNOTFOUND
CLOSE cursor_name
57
Declare Explicit Cursors
Syntax CURSOR ltcursor_namegt IS ltSELECT
field(s) FROM WHERE ...gt -- when multiple
fields are referenced ltrecord_variablegt
ltcursor_namegtROWTYPE -- or ltrecord_variablegt
lttable_namegtROWTYPE
-- when a single field is referenced
ltrecord_variablegt lttable_name.field_namegtTYPE

58
Cursor to Return all Rows in COURSE Table
59
Cursor to Return all Rows in COURSE Table
Database Server Memory
Number of rows processed
Parsed SQL command
COURSE_NO COURSE_NAME CRDDITS
MIS101 Intro.to info.system 3
MIS301 System Analysis 3
MIS441 Database Management 3
CS155 Programming in C 3
MIS451 Client/Server Systems 3
Cursor location_cursor IS SELECT course_no,
course_name, credits FROM course WHRER
location_row location_cursorROWTYPE
How to reference to the data on the current
record variable location_row.course_no location_
row.course_name Location_row.credits
60
Declare Explicit Cursors
Syntax CURSOR ltcursor_namegt IS ltSELECT
statementgt DECLARE current_bldg_code
VARCHAR2(5) CURSOR location_cursor IS
SELECT room, capacity FROM
location WHERE bldg_code
current_bldg_code This gives the cursor a name
(implicit cursors also have a name SQL) and
associates a query with it.
Question Is the cursor a TYPE or ROWTYPE?
61
Explicit Cursors (cont.)
The record variable is either a single variable
or a list of variables that will receive data
from a single field (TYPE) or multiple fields
(ROWTYPE) currently being processed.
DECLARE current_bldg_code VARCHAR2(5)
CURSOR location_cursor IS SELECT room
FROM location WHERE bldg_code
current_bldg_code current_room
location.roomTYPE BEGIN current_bldg_code
LIB OPEN location_cursor LOOP
FETCH location_cursor INTO current_room
ltother codesgt EXIT WHEN
END LOOP CLOSE location_cursor END
If record variable is a single field record_varia
ble (i.e., current_room must be the same data
type as defined in the database table field,
lttablenamegt.ltfieldnamegtTYPE
NO cursor name here!!
(Figure 4-29 next slide p.243)
62
-- Chapter 4, Figure 4-29, p.243 DECLARE
current_bldg_code VARCHAR2(5) CURSOR
location_cursor IS SELECT room FROM
location WHERE bldg_code current_bldg_code
current_room location.roomTYPE BEGIN
current_bldg_code 'LIB' OPEN
location_cursor LOOP FETCH
location_cursor INTO current_room EXIT WHEN
location_cursorNOTFOUND DBMS_OUTPUT.PUT_LINE
('The current room is ' current_bldg_code '
' current_room) END LOOP CLOSE
location_cursor END
Sample Output The current room is LIB 217 The
current room is LIB 222 PL/SQL procedure
successfully completed.
63
Explicit Cursor Attributes
Cursor attributes are kind of like functions you
can run against a cursor.
64
Explicit Cursor with ROWTYPE(cont.)
DECLARE current_bldg_code VARCHAR2(5)
CURSOR location_cursor IS SELECT room,
capacity FROM location WHERE
bldg_code current_bldg_code location_row
location_currsorROWTYPE BEGIN
current_bldg_code LIB OPEN
location_cursor LOOP FETCH
location_cursor INTO location_row
ltother codesgt EXIT WHEN
END LOOP CLOSE location_cursor END
(Figure 4-30, p.244)
If record variable contains multiple
fields record_variable (i.e, location_row)
must be the same data type as the table.
lttablenamegtROWTYPE or ltcursor_namegtROWTYPE
65
CURSOR FOR LOOP
BEGIN OPEN ltcursor_namegt LOOP
FETCH ltcursor_namegt INTO ltcursor_variable(s)gt
EXIT WHEN ltcursor_namegtNOTFOUND
ltcode to process cursor fieldsgt
END LOOP CLOSE ltcursor_namegt END
Other extra examples (You must study!) Figures
4-31 (FOR .. IN), 31-a(NOTFOUND, IF),
31-b(FOUND), 31-c(ROWCOUNT) 31-d(FOR .. IN,
UPDATE)
  • Automatically opens the cursor, fetches the
    records, then closes the cursor
  • Cursor variables cannot be used outside loop

FOR variable_name(s) IN cursor_name
LOOP additional processing statements END LOOP
66
Summary
  • PL/SQL is a programming language for working with
    an Oracle database
  • Scalar, composite and reference variables can be
    used
  • The IF/THEN/ELSE decision control structure
    allows branching logic
  • Four loop constructs allow repeating code
  • Cursors are returned from queries and can be
    explicitly iterated over
  • Exception handling is performed in the exception
    section. User defined exceptions help to enforce
    business logic

67
HW Ch. 4B-2
  • HW (p.259-260)
  • 1(CURSOR, LOOP, FETCH, EXIT WHEN NOTFOUND)
  • 4(CURSOR, LOOP, FETCH, FETCH, EXIT WHEN
  • NOTFOUND, IF, ELSIF)
  • - Bonus (see assignment)
  • Script file name (include 1,4,6 and Bonus)
  • Oracle4B_Lname_Fname.sql
  • Spooled file name
  • Oracle4B_Lname_Fname_Spool.lst

Email me with the SPOOLED file ONLY
to chen_at_jepson.gonzaga.edu with subject title of
bmis441_Oracle4B
Write a Comment
User Comments (0)
About PowerShow.com