Advancement in SQL

1
Advancement in SQL

• Programing in SQL environment
• Procedures
• Functions
• Cursors
• Triggers

2
PL/SQL basicshttp//digitus.itk.ppke.hu/fodroczi
/dbs/plsql/PL-SQL20_intro_0.1.htm

• DECLARE
• NTEMP NUMBER(10,2) -- declare a numeric
  variable
• CTEM CHAR(20) -- fixed size character variable
• VCTEMP VARCHAR(20) -- variable length character
  variable
• PIDLIKE PARTS.PIDTYPE -- variable according to
  some row
• BEGIN
• SELECT (pid,pname) INTO ( NTEMP,VCTEMP ) INTO
  FROM PARTS WHERE pid 1
• IF NTEMP gt 0 THEN
• UPDATE PARTS SET PNAMEegyes WHERE pid 1
• ELSE
• VCTEMPnemegyes -- variable assignment
• UPDATE PARTS SET PNAME VCTEMP WHERE pid
  NTEMP
• END IF
• COMMIT
• EXCEPTION
• WHEN NO_DATA_FOUND THEN
• INSERT INTO PARTS (PID,PNAME,COLOR)
  VALUES(1,egyes,sarga')
• WHEN TO_MANY_ROWS THEN

3
Saving our programs - procedures

• Create procedure
• CREATE OR REPLACE PROCEDURE PROCNAME(PARAMNAME
  INOUT PARAMTYPE,) IS
• // declaration is automatic
• // variables used in your program
• BEGIN
• // place your program here
• END PROCNAME
• Drop procedure
• drop procedure PROCNAME
• Example
• http//digitus.itk.ppke.hu/fodroczi/dbs/firstpro
  c.sql -- check syntax
• Compile into database
• _at_/path_to/file_where_you_saved.sql
• Run
• exec procname(parameters)

4
Saving our programs - functions

• Create function
• CREATE OR REPLACE FUNCTION FUNCNAME(PARAMNAME
  INOUT PARAMTYPE,) RETURN rettype IS
• // declaration is automatic
• // variables used in your program
• BEGIN
• // place your program here
• END FUNCNAME
• Drop function
• drop function FUNCNAME
• Example
• http//digitus.itk.ppke.hu/fodroczi/dbs/holvegye
  m.sql -- check syntax
• Compile into database
• _at_/path_to/file_where_you_saved.sql
• Run
• select holvegyem('Traktor belso') from dual
  v. valtozoholvegyem(Traktor belso)

5
Cursorshttp//digitus.itk.ppke.hu/fodroczi/dbs/p
lsql/PL-SQL20_intro_0.6.htm

• create or replace function beosztott(aFonok IN
  number,aBeosztott IN number) return number is
• Result number
• cursor beoszt is select from hierarchia where
  felettesaFonok
• begin
• for i in beoszt loop a cursorba agyazaott
  select minden egyes sorat megvizsgaljuk
• if ( i.beosztottaBeosztott ) then
• -- ha a beosztottkent megtalaljuk a keresett
  embert visszaadunk 1-et
• return 1
• else
• -- ha ez a beosztott nem az akit keresunk
  nezzuk meg
• --az o beosztottjai kozt megtalaljuk-e..
• result beosztott(i.beosztott,aBeosztott)
• if ( result1 ) then -- ha megtalaltunk
  visszaterunk 1-el return 1
• end if
• end if
• end loop -- ha itt vagyunk nem talaltuk
• return 0
• end beosztott

6
Triggers I

• Statement trigger
• CREATE OR REPLACE TRIGGER MYTRIG1
• BEFORE DELETE OR INSERT OR UPDATE ON PARTS
• BEGIN
• IF (TO_CHAR(SYSDATE,dy) IN (sat,sun)) OR
  (TO_CHAR(SYSDATE,hh24mi) NOT BETWEEN 0830
  AND 1830) THEN
• RAISE_APPLICATION_ERROR(-20500,Table is
  secured)
• END IF
• END

7
Triggers II

• Row trigger
• CREATE OR REPLACE TRIGGER CATALOG_ARCHIVER
• AFTER DELETE OR INSERT OR UPDATE ON CATALOG FOR
  EACH ROW BEGIN
• IF DELETING THEN
• INSERT INTO CATLOG (ACTION, SID,PID,COST,ACTIOND
  ATE) VALUES (del,OLD.SID, OLD.PID,OLD.COST,
  SYSDATE)
• ELSIF INSERTING THEN
• INSERT INTO CATLOG (ACTION, SID,PID,COST,ACTION
  DATE) VALUES (insert,NEW.SID,
  NEW.PID,NEW.COST, SYSDATE)
• ELSIF UPDATING (COST) THEN
• INSERT INTO CATLOG (ACTION, SID,PID,COST,ACTION
  DATE) VALUES (,updatecost,OLD.SID,
  OLD.PID, NEW.COST, SYSDATE)
• ELSE -- UPDATE TO ANYTHING ELSE THAN COST
• INSERT INTO CATLOG (ACTION, SID,PID,COST,ACTION
  DATE) VALUES (update_other,OLD.SID,
  OLD.PID, OLD.COST, SYSDATE)
• END IF
• END
• http//digitus.itk.ppke.hu/fodroczi/dbs/plsql/PL-
  SQL_intro_3_trigger.htm
• http//digitus.itk.ppke.hu/fodroczi/dbs/trigger.s
  ql

8
Links

• Basic PL/SQL Block Structure
• Scope of Block Objects
• Declaring Variables And Constants
• PL/SQL Records
• PL/SQL Assignments
• Cursors
• Exception (error) Handling
• Processing Control Structures
• Procedures and Functions
• Database Triggers
• OReilly Oracle PL/SQL Programming
• http//www.unix.org.ua/orelly/oracle/prog2/