Oracle 8 Training

1
Oracle 8 Training

• PL/SQL

2
PL/SQL

• What is PL/SQL?
• PL/SQL stands for "Procedural Language extensions
  to SQL. PL/SQL look very similar to the SQL
  language but it adds the programming constructs
  similar to other languages.

3
PL/SQL Block

• DECLARE
• / Declarative section variables, types, and
  local subprograms. /
• BEGIN
• / Executable section procedural and SQL
  statements go here. /
• / This is the only section of the block that is
  required. /
• EXCEPTION
• / Exception handling section error handling
  statements go here. /
• END

4

PL/SQL Engine

5
Language constructs in PL/SQL

• Variables and constants
• Cursors
• Exceptions

6

Advantages of PL/SQL

• PL/SQL is a completely portable, high-performance
  transaction processing language that offers the
  following advantages
• support for SQL
• support for object-oriented programming
• better performance
• higher productivity
• full portability
• tight integration with Oracle
• security

7
Fundamentals - Comments

• PL/SQL is case insensitive, that is uppercase is
  treated the same as lowercase.
• We have two ways to comment code.
• 1a. We can use the double '-' to comment a single
  line of code. Everything after the -- is ignored
  until the end of the line.
• e.g..
• -- Function to calculate sales commission.IF
  sales gt 0 THENcommission sales 0.1END IF

8
Fundamentals - Comments

• We have two ways to comment code.
• 1b. We can also place our comment on a line that
  contains code to be executed.
• e.g..
• IF sales gt 0 THENcommission sales 0.1 --
  The commission is hard coded to 10 percent.END
  IF

9
Fundamentals - Comments

• 2. Now when we want to comment multiple lines
  within PL/SQL we can use the following characters
  / /. Everything between these characters is
  ignored and treated as a comment.
• e.g.
• / Procedure update_balanceAuthor
  CTSDescription Test Procedure/

10
Identifiers

• An identifier is the name for a PL/SQL object.
  Objects e.g. Variables,Constants
• PL/SQL identifiers must start with a letter,
  cannot contain spaces, may contain the following
  characters , _, and . The maximum length of an
  identifier is 30 characters.

11
Reserved Words

• Naming variables Avoid the following BEGIN,
  END, IF, ELSE, END IF, WHILE, LOOP, OPEN, CLOSE,
  FETCH
• Literal A literal is a value which is not
  represented by an identifier. A literal con be
  composed of numbers eg. 123, 98 34.56 , Strings
  eg. 'Hello World and Boolean values eg. TRUE and
  FALSE.

12
The Statement Delimiter

• Every PL/SQL program consists of many statements.
  Our statements are terminated with the ''
  character. An end of line is not the end of a
  statement, look at our IF-THEN statement as an
  example.
• eg.
• IF sales gt 0 THENcommission sales 0.1END
  IF

13
The Statement Delimiter

• Our first semicolon terminates the single
  statement commission sales 0.1 and the
  second semicolon terminated the IF-THEN-END-IF
  statement. The same piece of code could be
  written like this
• e.g.
• IF sales gt 0 THEN commission sales 0.1 END
  IF
• Functionally the two statements are identical,
  the first is much more preferred for readability.
  

14
Declaring Variables

• A constant is a special kind of variable, it has
  a datatype and its value is set at creation.
• When choosing a variable name
• 1. choose one that resembles as close as possible
  the purpose of the variable
• 2. always try to use a readable name.
• PL/SQL allows the use of the underscore character
  '_' so it is very easy to create readable names.

15
Declaring Variables

• All constants and variables contain a datatype.
  It is this datatype that determined the storage
  format, the restrictions on how the variable can
  be used and what values the variable may contain.
• A scalar datatype is atomic, this means that it
  is not made up of other datatypes.
• Scalar datatypes belong to one of four types
  Number, Character, Boolean and Date datatypes.
• Initialized to Null

16
Variable Declarations

• Before you can reference a variable, it must be
  first defined. When you declare the variable
  PL/SQL allocates memory to the storage of the
  variable. The syntax for the declaration of
  variables is as follows.
• variable_name datatype optional assignment
• eg.
• Age_of_student number 16

17
Variable Declarations

• Our declarations can be bounded or unbounded.
• This means we can specify the magnitude of the
  value that can be assigned to the variable. Our
  Number variable supports up to 38 digits,
• if unbounded PL/SQL will allocate all the memory
  that is required to store up 38 digits.
• If we bound the variable by using number(2), then
  PL/SQL will only allocate the memory to store the
  two digits.

18
Anchored Declarations

• Anchoring variables refers to the using of the
  TYPE declaration.
• What PL/SQL is doing is anchoring the datatype of
  one variable to that of another data structure,
  generally that of a column of a table.
• The benefit of anchoring datatypes of variables
  in PL/SQL programs with database columns is when
  the underlying declarations change, the PL/SQL
  code does not require modifications.
• e.g.FirstName employee.fnametype

19
Tips for using Variables

• Always use clear names and try to establish a
  naming convention for different types of
  variables.
• Avoid recycling variables. Read this statements
  as NEVER recycle variables. Reusing variable
  names within the sample PL/SQL program can cause
  more problems than you could ever imagine. Just
  try to debug one some day!.
• Use named constants wherever possible and avoid
  the use of hard coded values.

20
Tips for using Variables

• Remove unused variables from your PL/SQL
  programs. Most PL/SQL programs evolve, in many
  cases, over several years and many versions. When
  variables are no longer used, remove them from
  the program. It will make the code much easier to
  understand.
• If the variable represents a database column then
  anchor the variable to the database column using
  the type declaration.

21

• Variable declaration
• Declare
• Name varchar2(25)
• Age number(2)
• Salary Number(7,2)3500
• Empno number(4) NOT NULL7869
• DOB date
• Stock boolean
• Avail booleanfalse
• Constant declaration
• Declare
• Credit_limit Constant real5000.00

22

Control Structures

• They are normally used to update,delete or update
  batches of data
• The structures are common to any 3GL
  IFTHEN-ELSE, FOR,WHILE, GOTO, EXIT WHEN.

23
Conditional Controls

• The format of the IF-THEN statement is as follows
• IF ltconditiongt THENexecutable statementsEND
  IF
• The value of the condition is evaluated, if TRUE
  then the executable statements are processed. If
  the condition is FALSE or NULL then the
  statements are skipped.

24
Conditional Controls

• The format of the IF-ELSIF statement is as
  follows.
• IF ltcondition1gt THENltstatements1gtELSIF
  ltcondition2gt THENltstatements2gtELSIF
  ltconditionNgt THENltstatementsNgtEND IF
• The IF-ELSIF statement can be considered a
  multiple IF statements where only one condition
  can be TRUE.

25
Conditional Controls

• Nested IF Statements
• It is possible to nest any IF statements within
  another IF statement. If your level of nesting is
  more that three levels you should really look at
  reviewing the logic of your code.

26
Conditional Control

• DECLARE
• v_number_seats rooms.number_seatsTYPE
• v_comment VARCHAR2(35)
• BEGIN
• SELECT number_seats
• FROM rooms
• WHERE room_id 9000
• IF v_number_seats lt 50
• THEN v_comment 'Small'
• ELSIF v_number_seats lt 100
• THEN v_comment 'Big'
• ELSE v_comment 'Very Big'
• END IF
• END

27
Loops

• Syntax
• LOOP
• ltloop_bodygt / A list of statements. /
• END LOOP
• At least one of the statements in ltloop_bodygt
  should be an EXIT statement of the form
• Syntax
• EXIT WHEN ltconditiongt

28
Simple Loops

DECLARE v_Counter BINARY_INTEGER 1 BEGIN
LOOP INSERT INTO temp_table VALUES
(v_Counter,'Loop Index') v_Counter
v_Counter 1 EXIT WHEN v_Counter gt 50 END
LOOP END
29
More Loops

• EXIT by itself is an unconditional loop break.
  Use it inside a conditional if you like.
• A WHILE loop can be formed with
• WHILE ltconditiongt LOOP
• ltloop_bodygt
• END LOOP

30
While Loop
DECLARE v_Counter BINARY_INTEGER 1 BEGIN
WHILE v_Counter lt 50 LOOP INSERT INTO
temp_table VALUES (v_Counter,'Loop) v_Counter
v_Counter 1 END LOOP END

31
More Loops

• A simple FOR loop can be formed with
• FOR ltvargt IN ltstartgt..ltfinishgt LOOP
• ltloop_bodygt
• END LOOP
• Here, ltvargt can be any variable it is local to
  the for-loop and need not be declared. Also,
  ltstartgt and ltfinishgt are constants.

32
For Loop
BEGIN FOR v_Counter IN 1..50 LOOP INSERT INTO
temp_table values (v_Counter, 'Loop Index')
END LOOP END BEGIN FOR v_Counter IN
REVERSE 10..50 LOOP INSERT INTO temp_table
values (v_Counter,'Loop Index') END
LOOP END
33
For Loop

• Break
• Continue
• Sequential controls using labels ltltlabelgtgt
• And goto label

34
PL/SQL Attributes

• TYPE
• ROWTYPE

35
TYPE

• This attrribute provides the datatype of a
  variable or database column.
• Declare
• Name emp.enameTYPE
• Age number(2)
• Myage ageTYPE
• If the datatype of ename in the table changes,
  the datatype of name changes accordingly at run
  time

36
ROWTYPE

• This attribute provides a record type that
  represents a a row in a table. The record can
  store an entire row of data selected from the
  table or fetched from a cursor or cursor variable
• Declare
• dept_rec deptROWTYPE
• To reference the fields,
• my_deptnodept_rec.deptno

37
Cursors

• When you execute an SQL statement in PL/SQL
  Oracle creates a private work area for the
  statement. This is where data that is returned
  from the SQL statement is stored. The cursor name
  is basically a pointer to this area.
• Now we will create a cursor.
• CURSOR c_customers isSELECT from CUSTOMERS

38
Cursors

• We can open the cursor.
• OPEN c_customers
• We can select data from the cursor.
• FETCH c_customers into customers_rec
• And we can close the cursor.
• CLOSE c_customers
• When we select values from a cursor we are
  actually selecting the data from a virtual table
  defined by the cursor definition.

39
Implicit Explicit Cursors

• Every SQL data manipulation statements including
  queries that return only one row is an implicit
  cursor. An explicit cursor is what we create. For
  queries that return more than one row, you must
  declare an explicit cursor
• Note In your PL/SQL, always use explicit
  cursors.

40

Diff between implicit and explicit

• In order to process an SQL statement,Oracle
  allocates an area of memory known as the context
  area.
• This area contains information necessary to
  complete the processing, including the number of
  rows processed by a statement, a pointer to the
  parsed representation of the statement, and in
  the case of query an active set , which is the
  set of rows returned by the query.
• A cursor is a handle,or pointer to the context
  area .Through the cursor, a PL/SQL program can
  control the context area and what happens to it
  as the statement is processed. An explicit cursor
  is when a cursor name is explicitly assigned to a
  select statement via the CURSOR..IS. An implicit
  cursor is used for all other SQL statements

41

DECLARE v_StudentID students.idTYPE
v_FirtsName students.first_nameTYPE
v_LastName students.last_nameTYPE v_Major
students.majorTYPE 'Computer Science'
CURSOR c_Students IS SELECT id,first_name,
last_name FROM students WHERE major v_Major
BEGIN OPEN c_Students LOOP FETCH c_Students
INTO v_StudentID, v_FirstName,v_LastName
EXIT WHEN c_StudentsNOTFOUND END LOOP CLOSE
c_Students END

42
Cursor Attributes

• The SQL cursor attributes are -
• ROWCOUNT The number of rows processed by a SQL
  statement.
• FOUND TRUE if at least one row was processed.
• NOTFOUND TRUE if no rows were processed.
• ISOPEN TRUE if cursor is open or FALSE if
  cursor has not been opened or has been closed.
  Only used with explicit cursors.

43
Cursor Attributes

• IMPLICIT cursors are known as SQL cursor , thus
  if you wish to use the attributes from an
  implicit cursor (which in other words is EVERY
  SELECT statement that returns more than one row)
  you use SQLNOTFOUND, SQLROWCOUNT...etc,etc...

44

Use a PL/SQL record variable

• DECLARE
• CURSOR c_AllStudents IS
• SELECT from students
• v_StudentInfo
• c_AllStudentsROWTYPE
• BEGIN
• ....
• END

45

Cursor FOR loop
DECLARE CURSOR c_HistoryStudents IS SELECT id,
first_name, last_name FROM students WHERE
major'History' BEGIN --Begin Loop the cursor
is implicitly FOR v_StudentData in
c_HistoryStudents LOOP --An implicit fecth
INSERT INTO registered_students (student_id,
department,course) VALUES ( v_StudentData.ID,'HIS'
,301) INSERT INTO temp_table (num_col,char_col)
VALUES (v_StudentData.ID, v_StudentData.first_name
' ' v_StudentData.last_name) END LOOP
COMMIT END

46

Advanced Explicit Cursor

• Concepts

47

cursor that uses parameters
CURSOR c_students (p_Department
classes.departmentTYPE p_Course
classes.departmentTYPE ) IS SELECT
FROM classes WHERE department
p_Department AND course
p_Course To call the cursor OPEN
c_students('CS',101)

48

Cursors for update

• The syntax for this parameter in the SELECT
  statement is
• SELECT ... FROM ... FOR UPDATE OF
  column_reference NOWAIT
• where column_reference is a column in the table
  against which the query is performed. A list of
  columns can also be used.

49

Examplefor update
DECLARE CURSOR c_AllStudents IS SELECT FROM
students FOR UPDATE OF first_name,
last_name Or the cursor can select every column
by not specifing a range DECALRE CURSOR
c_AllStudents IS SELECT FROM students
FOR UPDATE

50

NOWAIT
If another session already has locks on the rows
in the active set, then the SELECT FOR UPDATE
will hang until the other session releases the
lock. To handle this situation the parameter
NOWAIT is available, which in case the rows are
locked,OPEN will return the error ORA-54 resource
busy and acquire with NOWAIT specified

51

Fetching Across COMMITS

• Note that the COMMIT statement is done after the
  fetch loop is complete.
• This is done because COMMIT will release any
  locks held by the session. Since the FOR UPDATE
  clause acquires locks, these will be released by
  the COMMIT.
• When this happens, the cursor is invalidated.Any
  subsequent fetches will return the Oracle error
  ORA-1002 fetch out of sequenece .
• if there is a COMMIT inside a SELECT FOR UPDATE
  fetch loop, any fetches done after the COMMIT
  will fail.
• it is not advisable to use a COMMIT inside the
  LOOP. If the cursor is not defined with a SELECT
  FOR UPDATE then there is no problem.