Introduction to PL/SQL

1
Introduction to PL/SQL

• Procedural language extension to SQL
• Variable name must begin with a letter,
  limited to 30 characters
• Data types number, varchar2, date
• Reference data type can define the data type
  using a database column or row.
• Example lname faculty.flnameTYPE
• fac_row facultyrowtype
• Comments
• block comments / /
• line comments --
• Arithmetic and relational operators
  ,-,,/,,ltgt,!,gt,gt,lt,lt,,
• Handling character strings
• Concatenation

1 select sfname ' ' smi ' ' slname
Name from student SQLgt / NAME -------------------
-------------------------------------------- Sarah
M Miller Brian D Robinson Daniel Black Amanda J
Mobley Ruben R Sanchez Michael S Connoly
2
Handling character strings

• LENGTH(character string or variable) returns
  the total length of the argument

SQLgt select cadd, length(cadd) from customer
where custid 107 CADD
LENGTH(CADD) -------------
-----------------
------------ 1111 Water Street, Apt. 3
26

• UPPER (LOWER) converts the case of the argument

1 select first, upper(first), lower(first) 2
from customer where custid 107 SQLgt / FIRST
UPPER(FIRST)
LOWER(FIRST) ----------------------------
-- ------------------------------
------------ Paula
PAULA paula
Space

• INSTR (string, substring, starting position,
  nth occurrence)
• returns the position where substring occurs

1 select cadd,instr(cadd, '),instr(cadd,
',1,2) from customer SQLgt / CADD
INSTR(CADD,'')
INSTR(CADD,'',1,2) ------------------------------
--------------
------------------ 1111 Water Street, Apt. 3
5 11 4204
Garner Street 5
12 2211 Pine Drive
5
10 699 Pluto St. NW
4 10 987 Durham Rd.
4
11
3
PL/SQL program blocks

• SUBSTR(string, starting position, length)

SQLgt select substr(cadd,1,4) from
customer SUBS ---- 1111 4204 2211 699 987
1 select substr(cadd,1,instr(cadd,' ')) from
customer SQLgt / SUBSTR(CADD,1,INSTR(CADD,
')) ------------------------------ 1111 4204 2211
699 987

• PL/SQL program blocks
• General structure
• declare
• ltvariable declarationsgt
• begin
• ltbodygt
• exception
• lterror handling statementsgt
• end

SQLgt set serveroutput on 1 declare 2
total_order number 3 begin 4 select
sum(order_pricequantity) into total_order from
orderline 5 dbms_output.put_line('The total
order quantity is ' total_order) 6
end SQLgt / The total order quantity is 1615.52
4
Control structure

• IF..THEN statements
• IF ltconditiongt THEN ltstatementsgt end if
• if enrollment gt 0 then
• counter counter1
• end if
• IF ltconditiongt THEN ltstatementsgt else
  ltstatementsgt end if
• if enrollment gt 0 then
• counter1 counter11
• else counter2 counter21
• end if
• IF ltconditiongt THEN ltstatementsgt ELSIF
  ltconditiongt end if
• if enrollment gt 0 then
• counter1 counter11
• elsif enrollment lt0 then
• counter2 counter21
• elsif enrollment 0 then
• counter3 counter31
• end if

5
Iteration

• Simple loops keeps iterating until and exit or
  exit when is reached.
• Declare pi number(9,7) 3.1415926
• radius number(5)
• area number(14,2)
• begin
• radius 3
• loop
• area pipower(radius,2)
• insert into AREAS values (radius, area)
• radius radius1
• exit when area gt 100
• end loop
• end

SQLgt select from areas RADIUS AREA
--------- --------- 3
28.27 4 50.27 5
78.54 6 113.1
SQLgt desc areas Name
Null? Type -------------------------------
-------- ---- RADIUS
NUMBER(5) AREA
NUMBER(14,2)
6
Cursors

• A handle or pointer to control a number or rows
  retrieved by a query.
• Declaring a cursor CURSOR ltcursor_namegt IS
  ltselect statementgt
• Opening a cursor OPEN ltcursor_namegt
• Fetching from a cursor FETCH ltcursor_namegt
  INTO ltPL/SQL variablesgt
• Closing a cursor CLOSE ltcursor_namegt

declare counter number 0 enrl number
0 cursor get_cs is select maxenrl -
currenrl from course_section begin open
get_cs loop fetch get_cs into enrl if
enrl gt 0 then counter counter1 end if
exit when get_csNOTFOUND end loop
dbms_output.put_line('The total number of course
sections with maximum enrollment is '
counter) close get_cs end