Docstoc

Oracle PL_SQL

Document Sample
Oracle PL_SQL Powered By Docstoc
					Oracle PL/SQL

Eyad Husni Elshami
                         Why PL/SQL
• Block Structures:
    – PL/SQL consists of blocks of code, which can be nested within each
      other. Each block forms a unit of a task or a logical module. PL/SQL
      Blocks can be stored in the database and reused.
•   Procedural Language Capability:
    – PL/SQL consists of procedural language constructs such as conditional
      statements (if else statements) and loops like (FOR loops).
•   Better Performance:
    – PL/SQL engine processes multiple SQL statements simultaneously as a
      single block, thereby reducing network traffic.
• Error Handling:
    – PL/SQL handles errors or exceptions effectively during the execution of
      a PL/SQL program. Once an exception is caught, specific actions can be
      taken depending upon the type of the exception or it can be displayed
      to the user with a message.
          Basic Structure of PL/SQL
DECLARE
 /* Declarative section: variables, types, and local subprograms. It is an
  optional section*/
BEGIN
  /* Executable section: procedural and SQL statements go here. */
 /* This is the only section of the block that is required. It is an
   mandatorysection */
EXCEPTION
/* Exception handling section: error handling statements go here. It is an
   optional section */
END;
     PL/SQL Variables and Types
• Information is transmitted between a PL/SQL
  program and the database through variables.
  Every variable has a specific type associated
  with it. That type can be one of the types used
  by SQL for database columns.
  – A generic type used in PL/SQL such as NUMBER,
    CHAR, DATE, …etc
  – Declared to be the same as the type of some
    database column
        PL/SQL Variables and Types
Declar
  Std_id number(2);
  Std_name Students.sname%TYPE;
  Std_record Students%ROWTYPE;
Begin
  select sid into std_id from students where sname=‘ALI’;
  select sname into std_name from students where sid=12345;
  select * into std_record from students where sid=12345;
End
      PL/SQL Control Structures
• Conditional statement:
  – IF Statement


• Iterative Statements:
  – Simple Loop
  – While Loop
  – For Loop
       PL/SQL Conditional statement
IF condition THEN      Declare
                       day char(3):= to_char(sysdate,'DY');
   Statements          Begin
                       If day = 'FRI' then
ELSIF condition THEN   DBMS_OUTPUT.PUT_LINE('Week
                           end');
   Statements          Elsif day = 'SAT' then
                        DBMS_OUTPUT.PUT_LINE('New
ELSE                       work week');
                       Else
   Statements          DBMS_OUTPUT.PUT_LINE('have a
                           nice day');
END IF;                End if;
                       End;
                       /
      PL/SQL Iterative Statements
Simple loop syntax:                  DECLARE
                                       i NUMBER := 1;
LOOP                                 BEGIN
   <loop_body> ;                       LOOP
                                             INSERT INTO T1 VALUES(i,i);
   EXIT; /* EXIT WHEN condition;*/
                                             i := i+1;
END LOOP;                                    EXIT WHEN i>100;
                                       END LOOP;
                                     END;
     PL/SQL Iterative Statements
While loop syntax:     DECLARE
                         i NUMBER := 1;
WHILE condition LOOP   BEGIN
  < loop_body> ;         WHILE i<100 LOOP
                               INSERT INTO T1
END LOOP;                         VALUES(i,i);
                               i := i+1;
                         END LOOP;
                       END;
     PL/SQL Iterative Statements
For loop syntax:    DECLARE
                      i NUMBER := 1;
FOR counter IN      BEGIN
  val1..val2 LOOP     FOR i IN 1..100 LOOP
                            INSERT INTO T1
  < loop_body> ;              VALUES(i,i);
END LOOP;             END LOOP;
                    END;
               PL/SQL Cursors
• What are Cursors?
  – A cursor is a temporary work area created in the
    system memory when a SQL statement is executed.
  – A cursor contains information on a select statement
    and the rows of data accessed by it. This temporary
    work area is used to store the data retrieved from the
    database, and manipulate this data.
  – A cursor can hold more than one row, but can process
    only one row at a time. The set of rows the cursor
    holds is called the active set.
         PL/SQL Implicit Cursors:
• When you execute DML statements or SELECT
  statements, implicit cursors are created to process
  these statements, there are attributes for each cursor:
   – %FOUND:
      • Return TRUE value if DML statement affect at least one row or the
        select statement return at least on (example SQL%FOUND)
   – %NOTFOUND:
      • Return FLASE value if DML statement affect at least one row or the
        select statement return at least on (example SQL%NOTFOUND)
   – %ROWCOUNT:
      • Return the number of rows affected by the DML/SELECT statement
        (example SQL%ROWCOUNT)
   – %ISOPEN:
      • Return TRUE value if the cursor is already open in the program.
           PL/SQL Implicit Cursors:
DECLARE
    rows_count number(5);
BEGIN
    UPDATE students SET saverage= 40
    where saverage is null ;
    IF SQL%NOTFOUND THEN
         dbms_output.put_line('None of the averages where updated');
    ELSIF SQL%FOUND THEN
         rows_count := SQL%ROWCOUNT;
         dbms_output.put_line(‘Averages for ' || rows_count|| ‘ students
           are updated');
    END IF;
END;
         PL/SQL Explicit Cursors
• An explicit cursor is defined in the declaration
  section of the PL/SQL Block. It is created on a
  SELECT Statement which returns more than one
  row .
• There are four steps in using an Explicit Cursor.
   – DECLARE the cursor in the declaration section.
   – OPEN the cursor in the Execution Section.
   – FETCH the data from cursor into PL/SQL variables or
     records in the Execution Section.
   – CLOSE the cursor in the Execution Section before you
     end the PL/SQL Block.
            PL/SQL Explicit Cursors
DECLARE
   CURSOR std_cur IS
      SELECT sid, sname, saverage FROM students;
      std_rec std_cur%rowtype;
   BEGIN
      IF NOT std_cur%ISOPEN THEN
        OPEN std_cur;
      END IF;
      LOOP
        FETCH std_cur INTO std_rec;
        EXIT WHEN std_cur%NOTFOUND;
        dbms_output.put_line(std_rec.sid || ' ' ||std_rec.sname || '   '
      ||std_rec.saverage+5);
      END LOOP;
   END;
           PL/SQL Procedures
• A stored procedure or in simple a procedure is a
  named PL/SQL block which performs one or more
  specific task.
• This is similar to a procedure in other programming
  languages.
• A procedure is similar to an anonymous PL/SQL Block
  but it is named for repeated usage.
• We can pass parameters to procedures in three ways:
  1) IN-parameters
  2) OUT-parameters
  3) IN OUT-parameters
            PL/SQL Procedures
CREATE OR REPLACE PROCEDURE update_name IS
BEGIN
  UPDATE students
  SET sname = UPPER(sname);

  UPDATE collge
  SET cname = UPPER(cname);

  COMMIT;
END;
            PL/SQL Procedures
CREATE OR REPLACE PROCEDURE upadet_average
  (push_value IN NUMBER) IS
BEGIN
  UPDATE students
  SET saverage = saverage +push_value
  WHERE saverage BETWEEN 55 and 59;

  COMMIT;
END;
            PL/SQL Procedures
CREATE OR REPLACE PROCEDURE student_details IS
  CURSOR std_cur IS
  SELECT sname, cid, saverage FROM students;
  std_rec std_cur%rowtype;
BEGIN
  FOR std_rec in std_cur LOOP
    dbms_output.put_line(std_cur.sname || ' ' ||std_cur.cid|| '
    ' ||std_cur.saverage);
  END LOOP;
END;
        PL/SQL Procedures
Execute procedurename;
            PL/SQL Functions
• A function is a named PL/SQL Block which is
  similar to a procedure. The major difference
  between a procedure and a function is, a
  function must always return a value, but a
  procedure may or may not return a value.
                     PL/SQL Functions
CREATE OR REPLACE FUNCTION need_to_pass (std_id IN NUMBER)
RETURN NUMBER
IS
   point NUMBER;
BEGIN
   SELECT SAVERAGE INTO point
   FROM students
        WHERE sid = std_id;

          IF (point<60) THEN
          RETURN (60-point);
   ELSE
         RETURN 0;
   END IF;
END;
             PL/SQL Functions
• Execution for function like:
  1. Since a function returns a value we can assign it
     to a variable.
        X := need_to_pass(12345);
  2. As a part of a SELECT statement
        SELECT sname,saverage, need_to_pass(sid)
        FROM STUDENTS;
  3. In a PL/SQL Statements like,
        dbms_output.put_line(need_to_pass(sid));
                 PL/SQL Triggers
• A trigger is a pl/sql block structure which is fired when
  a DML statements like Insert, Delete, Update is
  executed on a database table.
      CREATE OR REPLACE TRIGGER trigger_name
      {BEFORE | AFTER | INSTEAD OF }
      {INSERT | UPDATE | DELETE}
      [OF col_name] ON table_name
      [REFERENCING OLD AS o NEW AS n]
      [FOR EACH ROW]
      WHEN (condition)
      BEGIN
      --- sql statements
      END;
             PL/SQL Triggers
CREATE or REPLACE TRIGGER saverage_history
BEFORE UPDATE OF saverage ON stdudents
FOR EACH ROW /* WHEN :new.saverage>90*/
BEGIN
INSERT INTO archive_average VALUES (:old.sid,
  :old.saverage, :new.saverage, sysdate, user);
END;
PL/SQL Exception Handling



      We’ll study it later
create function grade(stdid in number) return char
is
    savg stduents.saverage%type;
Begin
    select saverage into asvg from stduents
    where sid= stdid;
if savg>=65 then
    return 'A‘;
Else
    Return ‘B’;
End if;
End;

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:9
posted:12/28/2011
language:
pages:27