1 / 8

Advancement in SQL

Learn advanced SQL concepts such as procedures, functions, and triggers. Understand how to declare variables, use cursors, handle exceptions, and create efficient PL/SQL programs. Explore examples and best practices for database development.

hcatherine
Download Presentation

Advancement in SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Advancement in SQL Programing in SQL environment Procedures Functions Cursors Triggers

  2. PL/SQL basicshttp://digitus.itk.ppke.hu/~fodroczi/dbs/plsql/PL-SQL%20_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.PID%TYPE; -- variable according to some row BEGIN SELECT (pid,pname) INTO ( NTEMP,VCTEMP ) INTO FROM PARTS WHERE pid = 1; IFNTEMP > 0 THEN UPDATE PARTS SET PNAME=‘egyes’ WHERE pid = 1; ELSE VCTEMP:=‘nemegyes’; -- variable assignment UPDATE PARTS SET PNAME = VCTEMP WHERE pid = NTEMP; END IF; COMMIT; EXCEPTION WHENNO_DATA_FOUNDTHEN INSERT INTO PARTS (PID,PNAME,COLOR) VALUES(1,’egyes’,‘sarga'); WHENTO_MANY_ROWSTHEN DBMS_OUTPUT.PUT_LINE(‘Primary key violation with pid: ' || NTEMP); WHENOTHERSTHEN DBMS_OUTPUT.PUT_LINE(‘Unknown Error with pid: ' || NTEMP); END;

  3. Saving our programs - procedures Create procedure: CREATE OR REPLACE PROCEDURE PROCNAME(PARAMNAME [IN|OUT] 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/firstproc.sql -- check syntax Compile into database: @/path_to/file_where_you_saved.sql Run: exec procname(parameters);

  4. Saving our programs - functions Create function: CREATE OR REPLACE FUNCTION FUNCNAME(PARAMNAME [IN|OUT] 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/holvegyem.sql -- check syntax Compile into database: @/path_to/file_where_you_saved.sql Run: select holvegyem('Traktor belso') from dual; v. valtozo:=holvegyem(‘Traktor belso’);

  5. Cursorshttp://digitus.itk.ppke.hu/~fodroczi/dbs/plsql/PL-SQL%20_intro_0.6.htmCursorshttp://digitus.itk.ppke.hu/~fodroczi/dbs/plsql/PL-SQL%20_intro_0.6.htm create or replace function beosztott(aFonok IN number,aBeosztott IN number) return number is Result number; cursor beosztis select * from hierarchia where felettes=aFonok; begin for i in beoszt loop – a cursorba agyazaott select minden egyes sorat megvizsgaljuk if ( i.beosztott=aBeosztott ) 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 ( result=1 ) 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,’hh24:mi’) NOT BETWEEN ’08:30’ AND ’18:30’) 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,ACTIONDATE) VALUES (‘del’,:OLD.SID, :OLD.PID,:OLD.COST, SYSDATE); ELSIF INSERTING THEN INSERT INTO CATLOG (ACTION, SID,PID,COST,ACTIONDATE) VALUES (‘insert’,:NEW.SID, :NEW.PID,:NEW.COST, SYSDATE); ELSIF UPDATING (‘COST’) THEN INSERT INTO CATLOG (ACTION, SID,PID,COST,ACTIONDATE) VALUES (,’updatecost’,:OLD.SID, :OLD.PID, :NEW.COST, SYSDATE); ELSE -- UPDATE TO ANYTHING ELSE THAN COST INSERT INTO CATLOG (ACTION, SID,PID,COST,ACTIONDATE) 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.sql

  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 O’Reilly Oracle PL/SQL Programming: http://www.unix.org.ua/orelly/oracle/prog2/

More Related