PLSQL

1
PLSQL

• Dr .Hayk Melikyan
• Departmen of Mathematics and CS
• melikian_at_bambi.acc.nccu.edu

2
Program Structure

• PL/SQL like other programming languages, groups
  its
• statements into units called BLOCKS. Blocks can
  be
• unnamed (anonymous ) or named ( sub-programs).
• The subprograms can be either
• functions or
• procedures
• and they can be grouped into a packages.
• PL/SQL also allows access to databases via the
  cursors,
• triggers, stored procedures and functions.

3
Basic Structure of

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

4
What You Can and cant ?

• The only SQL statements allowed in a PL/SQL
  program are
• SELECT, INSERT, UPDATE, DELETE and several
  other
• The SELECT statement has a special format.
• CREATE, DROP, or ALTER are not allowed.
• PL/SQL is not case sensitive
• C style comments (/ ... /) or ( -- .) .
• To execute a PL/SQL program, we must follow the
  program text
• itself by a line with a single dot ("."),
• and then a line with run

5
PL /SQL Datatypes and Variables

• DESCRIPTION ( scalar data types)
• Numeric BINARY INTEGER Integer in the
  range -231 - 1 to 231 -1.
• NATURAL
  Integer in the range 0 to 231
• POSITIVE
  Integer in the range 1 to 231
• NUMBER(p,s)
  Same as Oracle SQL's NUMBER,
  
• 
  where p is the precision and s is the
  scale
• Character
• CHAR(N) Fixed-length
  character string of length N.
• VARCHAR2(N) Variable-length
  character string of maximum
• 
  length N
• Boolean BOOLEAN Boolean data typ
  (TRUE, FALSE)
• Date-Time DATE Same as Oracle SQL's
  DATE

6
Variables and Types

• That type can be
• One of the types used by SQL for database
  columns
• A generic type used in PL/SQL such as NUMBER
• Declared to be the same as the type of some
  database column
• DECLARE    
• price  NUMBER    
• myBeer VARCHAR(20)
• or
• DECLARE    
• myBeer Beers.nameTYPE

7
Variables and Types(2)

• A variable may also have a type that is a record
  with several
• fields. The simplest way to declare such a
  variable is to use
• ROWTYPE on a relation name
• DECLARE  
•    beerTuple BeersROWTYPE
• The initial value of any variable, regardless of
  its type, is NULL.

8
PL/SQL Anchored Declarations

• cnum customers.cnotype
• cname customers.cnametype
• commission REAL(5,2) 12.5
• x commissiontype
• Anchored variables are synchronized with the
  database columns. The database columns may change
  their datatypes and the PL/SQL program remains
  compatible.
• Anchored variables declared within the PL/SQL
  program are normalized, i.e. changing one data
  type does not a_ect how the corresponding
  anchored variables will be used.
• Anchored data types are evaluated at compile
  time.

9
Variables and Types(3)

• We can assign values to variables, using the ""
  operator. The
• assignment can occur either immediately after the
  type of the
• variable is declared, or anywhere in the
  executable portion of the
• program. An exampl
• DECLARE   
•   a NUMBER 3
• BEGIN    
• a a 1
• END
• .
• run

10
PL/SQL Variable/Constant Declarations

• i BINARY_INTEGER
• cno NUMBER(5) NOT NULL 1111
• cname VARCHAR2(30)
• commision NUMBER(5,2) 12.5
• MAXCOLUMNS CONSTANT INTEGER(2) 30
• hired_date DATE
• done BOOLEAN
• Any variable that is declared to be NOT NULL must
  be initialized in its declaration.
• All variables that are not defined to be NOT NULL
  are initialized to have the value NULL.

11
Simple Programs in PL/SQL

• After the SELECT clause, we must have an INTO
  clause listing
• variables, one for each attribute in the SELECT
  clause, into
• which the components of the retrieved tuple must
  be placed.
• CREATE TABLE T1( e INTEGER, f INTEGER )
• DELETE FROM T1
• INSERT INTO T1 VALUES(1, 3)
• INSERT INTO T1 VALUES(2, 4)
• Above is plain SQL below
• .

12
NEXT

• DECLARE    
• a NUMBER    
• b NUMBER
• BEGIN    
• SELECT e,f INTO a,b FROM T1 WHERE egt1   
• INSERT INTO T1 VALUES(b,a)
• END
• .
• run
• /this is the PL/SQL program

13
Control Flow in PL/SQL

• There are three version
• of of statement in PL/SL
• if-then
• IF ltconditiongt THEN
• ltstatement_listgt
• END IF
• if-then-else
• IF ltconditiongt THEN ltstatement_listgt
• ELSE
• ltstatement_listgt
• END IF

if (cnum gt 10) and (cnum lt 90) then i i
1 dbms_output.put_line('Cust ' cnum) end if
if (cnum gt 10) and (cnum lt 90) then i i
1 dbms_output.put_line('Valid Cust ' cnum)
else j j 1 dbms_output.put_line('Invalid
Cust ' cnum) end if
14
if then- elsif statement
if (score gt 90) then na na 1 elsif
(score gt 80) then nb nb 1 elsif
(score gt 70) then nc nc 1 elsif
(score gt 60) then nd nd 1 else
nf nf 1 end if

• IF ltcondition_1gt THEN
• statement-list-1
• ELSIF ltcondition_2gt THEN
• statement-list-2
• ELSIF ltcondition_ngt THEN
• statement-list-n
• ELSE
• statement-list-n1
• END IF

15
Exampole

• DECLARE    
• a NUMBER   
•   b NUMBER
• BEGIN    
• SELECT e,f INTO a,b FROM T1 WHERE e gt 1    
• IF b1 THEN  INSERT INTO T1 VALUES(b,a)
•    ELSE    INSERT INTO T1 VALUES(b10,a10)
•   END IF
• END
• .
• run

16
Loops

• Loops are created with the following
• 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 EXIT WHEN
  ltconditiongt
• The loop breaks if ltconditiongt is true. For
  example, here is a
• way to insert each of the pairs (1, 1) through
  (100, 100) into T1
• of the above two examples

17
Example

• DECLARE    
• i NUMBER 1
• BEGIN     LOOP        
• INSERT INTO T1 VALUES(i, i)   
•    i i1        
• EXIT WHEN igt13    
• END LOOP
• END
• .
• run

loop i i 1 if i gt 10 then
exit end if sum sum i end loop
18
Other loop-forming statements

• 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
• A simple FOR loop can be formed with
•     FOR ltvargt IN ltstartgt..ltfinishgtLOOP  
•   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.

19

• Cursors
• A cursor is a variable that runs through the
  tuples of some relation.
• This relation can be a stored table, or it can be
  the answer to some
• query. By fetching into the cursor each tuple of
  the relation, we can
• write a program to read and process the value of
  each such tuple. If
• the relation is stored, we can also update or
  delete the tuple at the
• current cursor position.
• Cursorlt cnamegt return ltreturn-specgt is
• ltselect statementgt
• Once a cursor has been declared, it can
  beproccessed using open, fetch and close
  statements
• Open ltcnamegt
• Fetch ltcnamegt into lt record Variablrlistgt
• Close ltcnamegt

20

• The nest example illustrates a cursor loop. It
  uses
• our example relation T1 (e,f) whose tuples are
  pairs of
• integers. The program will delete every tuple
  whose first
• component is less than the second, and insert the
• reverse tuple into T1.
• DECLARE       
•   a T1.eTYPE
•   b T1.fTYPE   
•  CURSOR T1 cursor IS  
• SELECT e, f   FROM T1  WHERE e lt f FOR UPDATE

21
BEGIN OPEN T1Cursor   LOOP    FETCH T1Cursor
INTO a, b   EXIT WHEN T1CursorNOTFOUND DELETE
FROM T1 WHERE CURRENT OF T1Cursor       INSERT
INTO T1 VALUES(b, a)  END LOOP    CLOSE
T1Cursor END . run

22
PL/SQL Cursors

• CURSOR c1 RETURN customersROWTYPE IS
• select from customers
• CURSOR c2 IS
• select pno, pname, pricemarkdown sale_price
• from parts
• _ Cursor c1 uses the RETURN clause which is
  consistent with the select list of the select
  statement in the SQL query associated with the
  cursor.
• _ Cursor c2 uses a PL/SQL variable markdown in
  the select statement associated with the cursor.
• Cursor attributes are
• found, notfound, rowcount, isopen
• Example

23

• DECLARE
• CURSOR c1 IS
• select cno,cname,city
• from customers,zipcodes
• where customers.zip zipcodes.zip
• 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.cno ' ' c1_rec.cname ' '
  c1_rec.city)
• fetch c1 into c1_rec
• end loop
• close c1
• END

24
Cursor for loop

• DECLARE
• CURSOR c1 IS
• select cno,cname,city
• from customers,zipcodes
• where customers.zip zipcodes.zip
• BEGIN
• for c1_rec in c1 loop
• dbms_output.put_line('Row Number '
  c1rowcount 'gt ' c1_rec.cno ' '
• c1_rec.cname ' ' c1_rec.city)
• end loop
• END
• here we have implicit cursors
• for i in (select from employees) loop
• ...
• ...
• end loop

25
Functions

• DECLARE
• enum customers.cnotype
• total number(10,2)
• status boolean
• function total_emp_sales(emp_no IN
  employees.enotype)
• return number is sales number
• begin
• select sum(priceqty) into sales from orders,
  odetails, parts
• where orders.eno emp_no and orders.ono
  odetails.ono and
• odetails.pno parts.pno
• return (sales)
• end

26
Example1

• BEGIN
• enum 1000
• total total_emp_sales(enum)
• DBMS_OUTPUT.PUT_LINE('Total sales for employee '
  enum ' is ' total)
• END

27
Stored Procedure and Functions

• CREATE or replace procedure lt proced_namegt
• (lt parameter_listgt) as
• ltdeclarationsgt
• BEGIN
• ltexecutable_sectiongt
• exception lt exception_handlergt
• END

28
Function (Example_1)

• create or replace function lt
  func_namegt(ltparameter_listgt)
• return ltdatatyprgt as
• ltdeclarationsgt
• begin
• lt executable sectiongt
• exception
• ltexception_sectiongt
• end

29
Function Example_2

• Create or replace function get_city(cname in
  customers.cnotype)
• return zipcodes.citytype as ccity
  zipcodes.citytype
• Begin
• select city into ccity from customers,
  zipcodes
• where cno cnum and customers.zip
  zipcodes.sip
• return (ccity)
• End

30
Triggers

• An SQL trigger is a mechanism that automatically
  executes a specified PL/SQL block ( triggers
  action) when a triggering event occurs on table.
  The triggering event may be one of
• insert, delete or update.
• The trigger is associated with DB table and is
  fired when the triggering event takes place on
  the table.
• Triggers are created in Oracle using the
• Create trigger statement whose syntax is

31
Create Trigger(syntax)

• create or replace trigger lttrig_namegt
• before after
• delete insert update of column , column
  
• or delete insert update of column ,
  column
• ON lttable_namegt
• referencing old as ltoldgt new as
  ltnewgt new asltnew gt
• old as ltoldgt
• For each row
• when (codition)
• PL/SQL_ blo ck

32
Example

• create or replace trigger update_price_of_parts
• after update of price on parts
• for each row
• begin
• insert into parts_log
• values(old.pno, user, sysdate, old.price,
  new.price)
• end