CS178 Database Management PLSQL

1
CS178 Database ManagementPL/SQL

• session 8

References ORACLE 9i PROGRAMMING A Primer
Rajshekhar Sunderraman
2
What is PL/SQL ?

• Oracles procedural extension to SQL
• superset of the SQL language, including
  high-level programming features such as
• structures, types
• variables
• constants
• assignment statements
• conditional statements
• loops
• customized error handling
• structured data

3
Data types and variables

• all the sql data types
• variables declaration
• ltvariable-namegt ltdatatypegt not null
  ltinitial valuegt
• ex. sid number(5) not null 1111
• sname varchar2(30)
• grade real(5,2) 2.5
• constants declaration
• ltconstant-namegt constant ltdata-typegt ltvaluegt
• ex. maxcolumns constant integer(2) 30
• anchor variables
• ltvariable-namegtltobjectgttype not null
  ltinitial-valuegt
• ex. cnum customers.sidtype
• ctable customersrowtype -- creates a
  variable of type table that has the same fields
  as the customers table

4
Comments

• Single line comments --
• Block comments C like
• / this is a comment /

5
Assignments

• ltvariablegt ltexpressiongt
• ex. ii1
• sname Jones
• sales priceqty

6
Example

• In SQLPLUS run the following command
• set SERVEROUTPUT on

DECLARE i INTEGER sid NUMBER(5) NOT NULL
1111 sname VARCHAR2(30) grade REAL(5)
12.5 MAXCOLUMNS CONSTANT INTEGER(2)
30 BEGIN i 35 sname 'Jones' sid
2000 DBMS_OUTPUT.PUT_LINE('i ' i)
DBMS_OUTPUT.PUT_LINE('sid ' sid)
DBMS_OUTPUT.PUT_LINE('sname ' sname)
DBMS_OUTPUT.PUT_LINE('grade ' grade)
DBMS_OUTPUT.PUT_LINE('MAXCOLUMNS '
MAXCOLUMNS) END /
7
Conditional statements

• if-then
• if ltconditiongt then ltstatementgt end if
• ex. if (grade gt 70) and (grade lt90) then ii1
  end if
• if-then-else
• if ltconditiongt then ltstmt1gt else ltstmt2gt end if
• if-then-elseif
• if ltcondition1gt then ltstmt1gt
• elseif ltcondition2gt then ltstmt2gt
• elseif ltconditionngt then ltstmtngt
• else ltstmtn1gt
• endif

8
Loops

• Basic loop
• loop
• ltstmtgt
• end loop
• ex. loop
• ii1
• if (igt10) then exit
• end if
• sum sum i
• end loop
• Alternatively we can have exited with exit when
  i gt10

9
Loops

• For loop
• for ltloop-countergt in reverse ltlowergt..ltuppergt
  loop
• ltstatementgt
• end loop
• ex. for i in 1..10 loop
• sum sum i
• end loop
• While loop
• while ltconditiongt loop
• ltstatementgt
• end loop
• ex. while (ilt10) loop
• sum sum i
• i i1
• end loop

10
Program structure

• Anonymous programs
• declare
• --type and variable declarations
• begin
• --executable section
• null
• exception
• -- exception handlers
• when others then
• null --default handler for all untreated
  exceptions
• end
• Procedures and functions
• procedure ltproc-namegt ( ltp1gt,..,ltpngt ) is
• declarations
• begin
• --executable section
• exception
• --exception handlers

where ltp1gt has the following syntax ltvariable-na
megt in out in out ltdatatypegt
11
Functions and procedures

• Procedure will not return a result
• Function will return a value after execution
• ex.
• function myfunc(
• param1 IN number)
• return number
• is
• grade number
• begin
• gradeparam1
• return (grade)
• end

procedure myproc( param1 IN number, param2 out
number) is begin param2param1 end
12
How to call the function ?

• declare
• function myfunc(
• param1 IN number)
• return number
• is
• grade number
• begin
• gradeparam1
• return (grade)
• end
• begin
• DBMS_OUTPUT.PUT_LINE('The function returned
  ' myfunc(10))
• end
• /
• Exercise WRITE THE CODE FOR CALLING THE
  PROCEDURE

13
Use the select statement in PL/SQL(only if the
select returns one single row as result )

• declare
• name varchar2(100)
• id number
• begin
• select sid, fname
• into id,name
• from students
• where sid 1111
• end
• /

14
Cursors

• When the result of a select statement consists of
  more than one row the select into statement can
  not be used.
• A PL/SQL cursor allows a program to fetch and
  process information one row at a time
• Declaration
• cursor ltsnamegt is ltselect statementgt

15
Cursor example

• DECLARE
• CURSOR c1 IS
• select sid,fname
• from students
• c1_rec c1rowtype
• BEGIN
• if not c1isopen then
• open c1
• end if
• fetch c1 into c1_rec
• while c1found loop
• dbms_output.put_line('Row Number '
  c1rowcount 'gt '
• c1_rec.sid ' ' c1_rec.fname)
• fetch c1 into c1_rec
• end loop

16
How to work with cursors

• declare the cursor
• declare a variable rec_name of type
  cursorrowtype
• open c_name
• fetch row by row fetch c_name into rec_name
• close cursor
• c_namefound returns true if there are still
  records , false otherwise
• c_nameisopen - returns true if the cursor is
  open, false otherwise

17
Cursor for example

• DECLARE
• CURSOR c1 IS
• select sid,fname
• from students
• BEGIN
• for c1_rec in c1 loop
• dbms_output.put_line('Row Number '
  c1rowcount 'gt '
• c1_rec.sid ' ' c1_rec.fname)
• end loop
• END
• /
• When using for loops the cursor does not have
  to be explicitly opened and fetched from.

18
Stored Procedures

• Syntax
• create or replace procedure ltproc_namegt
• (ltparameter_listgt) as
• ltdeclarationsgt
• begin
• --executable section
• exception ltexception-sectiongt
• end

19
why needed ?

• most of the time the stored procedures contain
  the entire application logic
• Ex create a report with all the courses on all
  the years, average grade of the curse, students
  enrolled in the course, their grades on all the
  components of the courses and their final grade.

20
Exceptions

• when an error occurs during the execution of a
  PL/SQL program a exception is raised
• program control is transferred to the exception
  section

21
Common exception

• NO_DATA_FOUND -- select into failed because the
  it resulted in no row
• TOO_MANY_ROWS -- select into failed because the
  it resulted more than one row
• INVALID_NUMBER -- to_number(string) has invalid
  input parameter
• ZERO_DEVIDE -- a division by 0 occured

22
Views

• A view is a named query , virtual table
• Views are created, dropped or granted access to,
  identical to a table.

23
How do views differ from tables?
From http//www.cdoug.org/docs/views-1099.pdf
24
Syntax

• create view ltview_namegt as
• ltselect statementgt
• drop view ltview_namegt
• ex.
• create view vCourses as
• select catalog.ctitle, courses.term,
  courses.lineno from catalog, courses
• where catalog.cnocourses.cno
• select from vCourses

25
Sql Injection

• http//www.unixwiz.net/techtips/sql-injection.html
  
• SELECT fieldlist FROM table WHERE field
  'EMAIL'
• SELECT fieldlist FROM table WHERE field
  'anything' OR 'x''x'
• SELECT email, passwd, login_id, full_name FROM
  members WHERE email 'x' UPDATE members SET
  email 'steve_at_unixwiz.net' WHERE email
  'bob_at_example.com'
• SELECT email, passwd, login_id, full_name FROM
  members WHERE email 'x' DROP TABLE members
  --'