Oracle Programming Using PL/SQL Level 1

Short Description
data with logical programming structures to control your data processing. This course introduces. you to PL/SQL, which extends the capabilities of SQL by …

Website: www.micros.umsl.edu | Filesize: 136kb

Content
Oracle Programming Using PL/SQL: Level 1
www.micros.umsl.edu
Oracle Programming Using PL/SQL: Level 1
What You Will Learn………………………………………………………………………………………………………. 1
Working with PL/SQL…………………………………………………………………………………………………….. 2
Understanding the Benefits of PL/SQL ………………………………………………………………………… 2
Storing Your PL/SQL Code………………………………………………………………………………………… 2
Constructing Anonymous PL/SQL Blocks……………………………………………………………………. 3
Executing Anonymous Blocks…………………………………………………………………………………….. 4
Exercise 1: Working with PL/SQL ……………………………………………………………………………………. 6
Declaring Variables ………………………………………………………………………………………………………… 7
Declaring Scalar Variables in PL/SQL…………………………………………………………………………. 7
Initializing Variables………………………………………………………………………………………………….. 9
Declaring Constants…………………………………………………………………………………………………. 12
Using Variables in Executable Statements ………………………………………………………………….. 12
Using Substitution Variables …………………………………………………………………………………….. 15
Anchoring Scalar Variables ………………………………………………………………………………………. 16
Understanding PL/SQL Programming Conventions ………………………………………………………….. 17
Commenting Your Code …………………………………………………………………………………………… 18
Exercise 2: Writing Simple Programs………………………………………………………………………………. 19
Selecting and Manipulating Data…………………………………………………………………………………….. 20
Selecting Data …………………………………………………………………………………………………………. 20
Manipulating Data …………………………………………………………………………………………………… 23
Controlling Transactions…………………………………………………………………………………………… 26
Exercise 3: Selecting and Manipulating Data……………………………………………………………………. 28
Writing Conditional IF Statements………………………………………………………………………………….. 30
Writing IF-THEN Statements …………………………………………………………………………………….. 30
Adding an ELSE Clause…………………………………………………………………………………………….. 31
Nesting IF Statements ……………………………………………………………………………………………… 32
Using the ELSIF Clause ……………………………………………………………………………………………. 32
Dealing with NULL Values………………………………………………………………………………………. 34
Using Oracle Data to Make Decisions………………………………………………………………………… 36
Exercise 4: Writing IF Statements…………………………………………………………………………………… 40
Using CASE Statements and Expressions ………………………………………………………………………….. 42
Writing Simple CASE Statements ……………………………………………………………………………….. 42
Writing Searched CASE Statements…………………………………………………………………………….. 43
Understanding CASE…