Oracle PLSQL Programming

1
Oracle PL/SQL Programming

• Review of Oracle data types and sqlplus commands
• Creating PL/SQL Blocks
• Declare, Execution, Exception Sections
• Implicit and Explicit Cursors
• Creating PL/SQL Procedures
• Creating PL/SQL Functions
• DBMS_OUTPUT Package and Debugging
• PL/SQL Built-in Functions
• The DECODE function
• Creating Triggers

2
Data Types

• VARCHAR2(size) - variable length character string
  having a maximum of size bytes (up to 4000)
• NUMBER - numeric column with space for 40 digits,
  plus space for a decimal point and sign.
• NUMBER(size) - numeric column of specified size
• NUMBER(size, d) - d digits after the decimal point

3
More Data Types

• CHAR(size) - fixed length character data. Maximum
  size is 2000. Default size is 1.
• DATE - valid dates range from 1/1/4712 BC to
  12/31/4712 AD
• DECIMAL - same as NUMBER, does not accept
  arguments such as size or d
• FLOAT - same as NUMBER
• INTEGER - same as NUMBER, does not accept d as
  argument

4
More Data Types

• LONG - character data of variable size up to 2GB,
  only one LONG column can be defined per table.
  LONG columns cannot be used in WHERE clauses or
  indexes. Other restrictions also apply.
• LONG RAW - raw binary data, otherwise same as
  LONG
• LONG VARCHAR - same as LONG
• BLOB - binary large object, up to 4GB
• CLOB - character large object, up to 4GB
• ..

5
Oracle sqlplus Basics

• SQLPlus commands allow a user to manipulate and
  submit SQL statements. Specifically, they enable
  a user to
• Enter, edit, store, retrieve and run SQL
  statements
• Enter, edit, store, retrieve and run PL/SQL
  blocks
• List the column definitions for any table
• Format, perform calculations on, store, and print
  query results in the form of reports
• Set different options

6
Oracle sqlplus Commands

• / - Execute the current SQL statement in the
  buffer - same as RUN
• ACCEPT - Accept a value from the user and place
  it into a variable
• ACCEPT cname PROMPT Enter Company Name
• SELECT WHERE Company cname
• AUTOTRACE - Trace the execution plan of the SQL
  statement and gather statistics
• BREAK - Set the formatting behavior for the
  output of SQL statements

7
Oracle sqlplus Commands

• BTITLE - Place a title on the bottom of each page
  in the printout from a SQL statement
• COLUMN - Change the appearance of an output
  column from a query
• COLUMN sid HEADING Student ID FORMAT 9999
• COLUMN price FORMAT 9,999.99
• COMPUTE - Does calculations on rows returned from
  a SQL statement

8
Oracle sqlplus Commands

• CONNECT - Connect to another Oracle database or
  to the same Oracle database under a different
  user name
• COPY - Copy data from one table to another in the
  same or different databases
• DESCRIBE - List the columns with data types of a
  table
• EDIT - Edit the current SQL statement in the
  buffer using an external editor such as vi or
  emacs

9
Oracle sqlplus Commands

• EXIT - Exit the SQLPlus program
• HELP - Obtain help for a SQLPlus command (In
  some installations)
• HOST - Drop to the operating system shell

10
Oracle sqlplus Commands

• QUIT - Exit the SQLPlus program
• REMARK - Place a comment following the REMARK
  keyword
• RUN - Execute the current SQL statement in the
  buffer
• SET - Set a variable to a new value
• SET LINESIZE 80
• SET PAGESIZE 25
• ...

11
Oracle sqlplus Commands

• SHOW - Show the current value of a variable
• SPOOL - Send the output from a SQL statement to a
  file
• START - Load a SQL statement located in a script
  file and then run that SQL statement
• TIMING - Used to time the execution of SQL
  statements for performance analysis
• TTITLE - Place a title on the top of each page in
  the printout from a SQL statement
• UNDEFINE - Delete a user defined variable

12
PL/SQL Overview

• PL/SQL is Oracles Procedural Language superset
  of SQL. It can be used to
• Codify business rules through the creation of
  stored procedures.
• Trigger database events to occur
• Add programming logic to the execution of SQL
  commands.
• SQL is considered as a 4GL which specifies only
  the WHAT but not the HOW. PL/SQL is a 3GL that
  adds procedural constructs to SQL processing.

13
PL/SQL Blocks

• PL/SQL code is grouped into blocks where each
  block has
• a DECLARE section
• an EXECUTABLE section
• an EXCEPTION section
• A block can be an anonymous block, a function
  block or a procedure block.
• Single line comments Any characters followed by
  two dashes (--) in a line.
• Multiline comments / /

14
A Sample UNIVERSITY database

• A sample database containing the following
  relations to illustrate PL/SQL programming
• STUDENTS(id, fname, lname, major, credits)
• ROOMS(rid, building, roomno, seats, desc)
• CLASSES(dept, course, desc, maxenroll, curenroll,
  credits, rid)
• REGISTERED(sid, dept, course, grade)
• (Assume a sequence STU_SEQ has been created)

15
Creating a Sequence

• CRETE SEQUENCE stu_seq increment by 1 start with
  1000
• A sequence guarantees a unique id making it
  easier to define a table in the second normal
  form.
• You can use the NEXTVAL and CURVAL of a sequence
  to access the next value or the current value in
  a sequence.

16

• declare
• v_major varchar2(10) History
• v_fname varchar2(10) Scott
• v_lname varchar2(10) Mason
• begin
• Update STUDENTS
• set major v_newmajor
• where fname v_fname and
• lname v_lname
• if SQLNOTFOUND then
• INSERT INTO students (id, fname, lname, major)
  VALUES
• (stu_seq.nextval, v_fname, v_lname, v_major)
• end if
• end
• .
• /

17
The Declare Section

• The declaration section begins a PL/SQL block. It
  begins with the declare keyword, followed by a
  list of variable and cursor definitions.
• Declaration syntax for variables
• ltvar_namegt constant ltdata_typegt
  ltinitial_valgt
• Declaration syntax for anchored variables
• ltvar_namegt ltobjectgttype ltinitial_valgt
• ltvar_namegt ltobjectgtrowtype
  ltinitial_valgt
• where object can be a database object.

18
Declaration Examples

• declare
• amount integer(10)
• name varchar2(25)
• hiredate date
• commission real(5,2) 12.5
• pi constant number(9,7) 3.1415926
• cursor emp_cursor is
• select from employee
• emp emp_cursorROWTYPE
• ename employee.nametype

19
Cursor Variable

• A cursor is used to process multiple rows
  retrieved from the database. Using a cursor, your
  program can step through the set of rows returned
  one at a time, processing each one.

20
Variable Declaration

• PL/SQL variables can be of the same type as
  database columns
• Varchar2(20), date, number(5),
• Additional PL/SQL specific types
• Binary_integer (used to store signed integer
  values)
• Boolean
• User defined types (objects)

21
The Executable Section

• The executable section begins with the keyword
  begin. Executable statements include
• Assignments
• Programming constructs such as IF THEN ELSE
  END IF, WHILE, FOR, GOTO
• SQL data manipulation statements
• OPEN and CLOSE cursor statements
• variable expression (an assignment statement)

22
Conditional Control

• if () then end if
• if () then else end if
• if () then elsif () then ... else end
  if
• For example
• if SQLNOTFOUND then
• INSERT INTO STUDENTS (id, fname, lname, major)
  VALUES
• (stu_seq.nextval, v_fname, v_lname, v_major)
• end if

23
Cursor Attributes

• A cursor has 4 attributes each returning a value
  that can be used in expressions.
• FOUND a boolean attribute, TRUE if the previous
  FETCH returned a row, FALSE otherwise.
• NOTFOUND opposite of FOUND.
• ISOPEN TRUE if the associated cursor is open,
  FALSE otherwise.
• ROWCOUNT the number of rows fetched by the
  cursor so far.

24
Implicit Cursor vs Explicit Cursor

• In general, FOUND is appended to the name of an
  explicitly defined cursor to reference the value.
• These explicit cursors are used to process SELECT
  statements that return more than one row.
• INSERT, UPDATE, DELETE and single row SELECT
  INTO statements use implicit cursors for
  processing. In this case, FOUND is appended to
  SQL to access the value of the attribute.

25
Loop Control

• loop ... end loop
• Make sure you have an exit when statement in
  the loop.
• for count in 1..7 loop end loop
• count is declared as an variable, does not need
  to be initialized or incremented.
• for rad_val in rad_cursor loop end loop
• rad_cursor must be declared as a cursor. rad_val
  must be declared as an anchored variable. Do not
  need to open or close the cursor. Do not need to
  fetch explicitly.
• while () loop end loop

26
Using SQL in PL/SQL

• The only SQL statements allowed in a PL/SQL
  program are DML and transaction control
  statements.
• DML SELECT, INSERT, UPDATE, DELETE
• Transaction Control COMMIT, ROLLBACK, SET
  TRANSACTION, SAVEPOINT

27
Transaction Control

• COMMIT
• SET TRANSACTION
• Update ACCOUNTS set blnce blnce t_amt
• where actno from_acct
• Update ACCOUNTS set blnce blnce t_amt
• where actno to_acct
• Savepoint A
• Insert into ..
• if (condition) then rollback to savepoint A
• Savepoint B
• more SQL commands
• if (condition2) then rollback to savepoint B
• COMMIT

28
The Exception Handling Section

• When user-defined or system-related exceptions
  occur, the control of the PL/SQL block is
  transferred to the exception handling section
  which always begin with the exception keyword.
  The exception handling section must precede the
  end keyword that terminates the executable
  section.

29
Sample Exceptions

• CURSOR_ALREADY_OPEN
• DUP_VAL_ON_INDEX (during insert)
• INVALID_CURSOR (cursor not open)
• INVALID_NUMBER (e.g. 34some)
• NO_DATA_FOUND (select .. Into returns no rows)
• TOO_MANY_ROWS (select returns more than 1 row)
• VALUE_ERROR (assignment or select into)
• ZERO_DIVIDE
• OTHERS

30
declare x number 5 y number z
number begin z sqrt(x) y 1 / z z x
y Exception when others then
handle_error() end . /
31
A PL/SQL Block

• declare
• ltdeclaration sectiongt
• begin
• ltexecutable sectiongt
• exception
• ltexception handling sectiongt
• end

32
Different Kinds of Blocks

• Anonymous block constructed dynamically and
  executed only once.
• Named block anonymous blocks with a label that
  gives the block a name.
• Subprograms procedures and functions that are
  stored in database. Typically executed many times
  explicitly via a call.
• Triggers named blocks stored in the database.
  Executed implicitly when the triggering event
  occurs.

33
Assume These Database Tables in the Programming
Examples

• customers (cno, cname, street, zip, phone)
• zipcodes (zip, city)
• radius_vals (radius)
• areas (radius, area)
• worker (name, age, lodging)

34
PL/SQL Programming - Anonymous Block

• declare
• pi constant NUMBER(9,7) 3.1415926
• radius INTEGER(5)
• area NUMBER(14,2)
• begin
• radius 3
• area pi power(radius, 2)
• INSERT INTO areas VALUES (radius, area)
• end
• .
• /

35

• ltltins_one_rad_areagtgt
• declare
• pi constant NUMBER(9,7) 3.1415926
• area NUMBER(14,2)
• cursor rad_cursor is
• SELECT FROM radius_vals
• rad_val rad_cursorROWTYPE
• begin
• open rad_cursor
• fetch rad_cursor INTO rad_val
• area pi power(rad_val.radius, 2)
• INSERT INTO areas VALUES (rad_val.radius,area)
• close rad_cursor
• end
• .
• /

36

• ltltIns_Many_Rad_Areagtgt
• declare
• pi constant NUMBER(9,7) 3.1415926
• area NUMBER(14,2)
• cursor rad_cursor is
• SELECT FROM radius_vals
• rad_val rad_cursorROWTYPE
• begin
• for rad_val in rad_cursor
• loop
• area pi power(rad_val.radius, 2)
• INSERT INTO areas
• VALUES (rad_val.radius,area)
• end loop
• end
• .
• /

37
Cursors and Bind Variables

• Declare
• v_sid students.idtype
• v_fname students.fnametype
• v_lname students.lnametype
• v_major students.majortype History
• cursor studentCur is
• select id, fname, lname from students
• where major v_major
• Begin
• open studentCur
• loop
• fetch studentCur into v_sid, v_fname, v_lname
• exit when studentCurNOTFOUND
• end loop
• close studentCur
• End

38
Parameterized Cursors

• Declare
• cursor studentCur(p_major students.majortype)
  is
• select id, fname, lname
• from students
• where major p_major
• Begin
• open studentCur(Computer Science)
• loop
• fetch studentCur into v_sid, v_fname, v_lname
• exit when studentCurNOTFOUND
• end loop
• close studentCur
• End

39
Creating a PL/SQL Procedure

• CREATE PROCEDURE new_worker
• (pname IN VARCHAR2)
• AS
• (declaration if any)
• BEGIN
• INSERT INTO worker (name, age, lodging)
• VALUES (pname, null, null)
• END
• /
• EXECUTE new_worker(John Smith)

40

• Create procedure insert_area
• (rad_val in varchar2, area_val in varchar2)
• as
• begin
• insert into areas values
• (to_number(rad_val),to_numer(area_val))
• exception
• when INVALID_NUMBER then
• dbms_output.put_line(numeric error)
• dbms_output.put_line(
• sqlcode -- sqlerrm)
• when OTHERS then
• dbms_output.put_line(other error)
• dbms_output.put_line(
• sqlcode -- sqlerrm)
• end
• /

41
Formal Parameters

• Oracle PL/SQL program parameters have modes
• IN input parameter (default)
• OUT output parameter
• INOUT input/output parameter
• You can also specify a default value for an input
  parameter

42
Create or replace procedure AddNewStudent(
p_fname students.fnametype p_lname
students.lnametype p_major students.majortype
defualt Economics) as begin INSERT
INTO students (id, fname, lname, major)
VALUES (stu_seq.nextval,p_fname,p_lname,p_major
) commit end /
43
Actual Parameters

• Using positional notation (only the last
  parameters can have default values)
• Begin
• AddNewStudents(Beth, Woods)
• End
• Using named notation (any parameter can adopt the
  default value(s))
• execute AddNewStudents(p_fname gt Beth,
• p_lname gt Woods)

44
Creating a PL/SQL Function

• CREATE OR REPLACE FUNCTION get_city
• (cnum IN customers.cnoTYPE)
• RETURN zipcodes.cityTYPE
• AS
• rcity zipcodes.cityTYPE
• BEGIN
• SELECT city INTO rcity
• FROM customers, zipcodes
• WHERE cno cnum and
• customers.zip zipcodes.zip
• RETURN (rcity)
• END
• /

45
Create function almostfull ( p_dept
classes.depttype, p_course classes.coursetype
) Return boolean is v_ccount number, v_mcount
number, v_return boolean, v_almostfull
constant number 95 begin select curenroll,
maxenroll into v_ccount, v_mcount from
classes where dept p_dept and course
p_course if (v_ccount / v_mcount 100) gt
v_almostfull then v_return TRUE else
v_return FALSE end if return
v_return End
46
declare cursor classCur is select dept,
course from classes classRec
classCurrowtype begin for classRec in
classCur loop if almostfull(classRec.dept,
classRec.course) then dbms_output.put_line(
classRec.dept
classRec.course is 95
full.) end if end loop End
47
PL/SQL Programming - Debugging

• Sqlplus command show errors
• Will display the line and column numbers of each
  error as well as the text of each error message
• While debugging, it is best to deal with one
  function/procedure at a time
• The USER_ERRORS table
• SELECT line, position, text
• FROM USER_ERRORS
• WHERE name funname AND
• type FUNCTION

48
PL/SQL Programming - Debugging

• The USER_SOURCE table
• SELECT text
• FROM USER_SOURCE
• WHERE name new_worker AND
• type PROCEDURE
• ORDER BY line

49
Using the DBMS_OUTPUT package

• The dbms_output package itself does not contain
  any mechanism for printing. It simply implements
  a first in, first out data structure.
• set serveroutput on size 100000
• Try these dbms_output procedures
• dbms_output.new_line
• dbms_output.put(emp_rec.eno) (you can put any
  varchar2, number or date argument)
• dbms_output.put_line(Total sales for employee
  
• ename
  is total )
• DBMS_OUTPUT is not designed for reporting.

50
Useful String Functions

• Set (e.g. .) represents a set of characters
• rpad(string, length, set) right pad a string
  using a given set of characters. Set defaults to
  the blank character.
• lpad() left pad a string
• rtrim(string, set) trim off the given set of
  characters from the right. Set defaults to the
  blank character.
• ltrim() trim off from the left
• length(string) returns the length of the string

51
Useful String Functions

• substr(string, start, count) extracts the
  substring from the given start position. Count
  defaults to from start to end.
• instr(string, set, start, occurrence) look for
  the nth occurrence of a set of characters in a
  string from the given starting position. Start
  defaults to 1. Occurrence also defaults to 1.
• Soundex(string) find words that sound like the
  given string regardless of spelling.
• SELECT name from worker where
• soundex(name) soundex(vansant)

52
Useful Numeric Functions

• Single value functions
• abs(val) power(val, exponent)
• ceil(val) round(val, precision)
• cos(val) sign(val) 1 or -1
• cosh(val) sin(val)
• exp(val) sinh(val)
• floor(val) sqrt(val)
• ln(val) tan(val)
• log(x, y) tanh(val)
• mod(val, divisor) trunc(val, precision)

53
Useful Group Functions

• avg(val) average of vals for a group of rows
• count(val) count of rows for column
• max(val) maximum of all vals for group of rows
• min(val) minimum of all vals for group of rows
• stddev(val) standard deviation of all vals for
  group of rows
• sum(val) sum of all vals for group of rows
• variance(val) variance of all vals for group of
  rows

54
Other Useful Functions

• greatest(val1, val2, )
• least(val1, val2, )
• decode(base_expr, if1, then1,
• if2, then2,
• else)

55
Useful Date Functions

• SYSDATE a pseudo column that returns the current
  date if selected
• add_months(date, n) adds n months to date
• Last_day(date) the date of the last day of the
  month that contains the given date
• months_between(d1, d2) months between two dates
• Date arithmetic you can add/subtract a number to
  a date and subtract two dates

56
Useful Conversion Functions

• to_char(date, format)
• sample format
• mm/dd/yy
• dd-mon-yyyy
• mm/dd/yyyy hhmiss
• .
• to_date(string, format)

57
The DECODE Function

• DECODE(baseval, if1, then1, if2, then2, , else)
• the decode function can take any number of
  arguments. The given value is compared against
  the if1 value, if they are equal, the then1
  is returned. Similarly, if val equals if2, the
  then2 value is returned, and so on. If the
  given baseval does not equal any of the given
  values, then the else value is returned.

58
Example Flipping a table on its side

• Invoice(cname, idate, amount)
• clientname date amount
• ------------- --------- --------
• ELBERT TALBOT 23-OCT-01 5.03
• JOHN PEARSON 09-NOV-01 2.02
• DICK JONES 12-SEP-01 11.12
• ADAH TALBOT 17-NOV-01 8.29
• JOHN PEARSON 13-OCT-01 12.41
• ...
• ...

59
First, Create a View

• Create view ClientByDate as
• select idate,
• sum(decode(cname, ADAH TALBOT, amount, 0))
  ATALBOT
• sum(decode(cname, ELBERT TALBOT, amount, 0))
  ETALBOT
• sum(decode(cname, JOHN PEARSON, amount, 0))
  JPEARSON
• sum(decode(cname, DICK JONES, amount, 0))
  DJONES
• from invoice
• group by idate

60
Then, Select from the View

• Select idate, atalbot, etalbot, jpearson, djones
  from ClinetByDate
• Date ATALBOT ETALBOT JPEARSON DJONES
• --------- ------- ------- -------- ------
• 12-SEP-01 0 0 0 11.12
• 13-OCT-01 0 0 12.41 0
• 23-OCT-01 0 5.03 0 0
• 09-NOV-01 0 0 2.02 0
• 17-NOV-01 8.29 0 0 0

61
Triggers

• Trigger defines an action the database should
  take when some event occurs. They can be used to
• supplement declarative integrity
• enforce complex business rules
• audit changes to data
• The code within a trigger is made up of PL/SQL
  blocks.
• A trigger is executed implicitly and it does not
  accept any arguments.
• There are 14 types of triggers grouped into ROW
  level triggers and STATEMENT level triggers

62
Types of Triggers

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE
• INSTEAD OF

63
Syntax of Triggers

• Create or replace trigger trigger_name
• before after instead of
• delete insert update of column , column
  
• on table_name view_name
• for each row statement when (condition)
• PL/SQL block
• The WHEN clause is valid for row-level trigger
  only

64
LEDGER Table

• actiondate date of action
• action bought, sold,
• item what item
• quantity how many
• quantitytype pound? gallon? bushel?
• rate how much per quantitytype
• amount total amount
• person to/from whom

65
Sample Trigger to Audit a Change

• Create trigger ledger_bef_upd_row
• before update on LEDGER
• for each row
• when (new.amount/old.amount gt 1.1)
• begin
• insert into LEDGER_AUDIT values
• (old.actiondate, old.action, old.item,
  old.quantity, old.quantitytype, old.rate,
  old.amount, old.person)
• end

66
Back to the UNIVERSITY Database

• STUDENTS(id, fname, lname, major, credits)
• ROOMS(rid, building, roomno, seats, desc)
• CLASSES(dept, course, desc, maxenroll, curenroll,
  credits, rid)
• REGISTERED(sid, dept, course, grade)
• MAJORSTAT(major, scount, ccount)
• scount is the total student count
• ccount is the total credit count

67
Sample Trigger to Update Statistics

• Create trigger upd_maj_stat
• after insert or delete or update on STUDENTS
• declare
• cursor statCur is
• select major, count() tstud, sum(credits)
  tcred
• from STUDENTS group by major
• statVal statCurrowtype
• begin
• for statVal in statCur loop
• update MAJORSTAT set scount
  statVal.tstud,
• ccount statVal.tcred
• where major statVal.major
• if SQLNOTFOUND then
• insert into MAJORSTAT ...
• end if
• end loop
• end

68
Order of Trigger Firing

• Triggers are fired as the DML statement is
  executed. The algorithm is given below
• Execute the BEFORE statement-level trigger if any
• For each row affected by the statement
• Execute the BEFORE row-level trigger if any
• Execute the statement itself
• Execute the AFTER row-level trigger if any
• Execute the AFTER statement-level trigger if any

69
Updating CLASSES rows

• dept course desc maxenroll curenroll credits rid
• ---- ------ ---- --------- --------- -------
  -----
• HIS 101 60 0 3 1003
• HIS 301 30 0 3 0172
• CS 101 50 45 3 9342
• CS 320 40 40 3 3438
• ENGL 100 ...
• Update CLASSES
• set credits 4
• where dept in (HIS, CS)