PL/SQL by bljmDR2O

VIEWS: 25 PAGES: 15

									             PL/SQL
Agenda:
•Basic PL/SQL block structure
•Example procedures and functions
•Executing PL/SQL from SQL*Plus
•Exceptions
•Packages
More PL/SQL - block structure

        Block Header           -- defines type of block (procedure,
                               function, anonymous) and the way it is
 IS                            called
          Declaration Header   -- declare any variables used in the block

 BEGIN
          Execution Section    -- use variables and other PL/SQL objects
                               to perform actions
 EXCEPTION
                               -- handle any problems that arise during
         Exception Section
                               execution of the block

 END;
                               REQUIRED
PL/SQL…
• Is not case sensitive
• Uses the same datatypes as SQL
   – Also has boolean, record, table, varray and LOB
   – Allows reference datatypes
      • %type and %rowtype
• Allows comments
   – /* and */ for multiline and -- for single line
• Uses := for assignment
• Uses ; to indicate end of line (instruction)
PL/SQL - example
 DECLARE
   loop_count BINARY_INTEGER := 0;
 BEGIN
   LOOP
      INSERT INTO count_table VALUES (loop_count);
      DBMS_output.put_line
        (‘loop_count is ’ || to_char(loop_count));
      loop_count := loop_count + 1;
      EXIT WHEN loop_count = 6;
   END LOOP;
 END;
Scott.GIVE_RAISE
CREATE OR REPLACE PROCEDURE give_raise (
    p_deptno IN number,
    p_raise_percent IN number )
AS
BEGIN
   update emp
      set sal = sal + (sal * p_raise_percent * .01)
   where deptno = p_deptno;

   commit;
END give_raise;
 Another example…
CREATE OR REPLACE FUNCTION get_company_name
   (comp_id_in IN company.id%TYPE)
    RETURN varchar2
IS
   cname company.name%TYPE;
BEGIN
   SELECT name INTO cname FROM company
     WHERE id = comp_id_in;
   RETURN cname;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
     RETURN NULL;
  WHEN OTHERS
     DBMS_OUTPUT.PUT_LINE (‘error code= ‘ || SQLCODE);
     DBMS_OUTPUT.PUT_LINE (‘error msg= ‘ || SQLERRM);
END;
  Cursors
• Implicit
   – Created automatically in Oracle
   – Don’t need to be declared
   – Can be used only when 1 and only 1 record is returned
• Explicit
   – Declared in declaration section
   – attributes specifying the state of the cursor
      • %notfound, %found, %rowcount, %isopen
   – Use “cursor for loop” to work with
      FOR item IN cursor LOOP … END LOOP;
      • Substitute cursor name and row variable for cursor and item
      • Opens cursor and fetches results into cursor automatically
Another PL/SQL example…
DECLARE
      current_bldg_code VARCHAR2(5);
      CURSOR location_cursor IS
            SELECT room, capacity
            FROM location
            WHERE bldg_code = current_bldg_code
            FOR UPDATE of capacity;
      location_row location_cursor%rowtype;
BEGIN
      current_bldg_code := ‘LIB’;
      FOR location_row IN location_cursor LOOP
        UPDATE location
        Set capacity = capacity +1
        WHERE CURRENT OF location_cursor;
      END LOOP;
      COMMIT;
END;
Using PL/SQL - functions
• From SQL*Plus command line
  – Functions require that you create a variable in
    SQL*Plus to hold the value returned first
      variable cname varchar2(100)
      then
      select get_company_name(1) from dual;
• valid commands to execute stored function f1
  returns number) from SQL*Plus.
  –   exec :a := f1;
  –   execute :a := f1;
  –   call f1() into :a;
  –   begin :a := f1; end;
  –   select f1 from dual;
Using PL/SQL - procedures
• From SQL*Plus command line
  – Procedures don’t return a value, so
      Execute Give_raise(20,10);


• valid commands to execute stored
  procedure p1 from SQL*Plus.
  –   exec p1;
  –   execute p1;
  –   call p1();
  –   begin p1; end;
Debugging PL/SQL
• Remember the Oracle error message facility –
  will help figure out the problem
• Comment out lines that may be causing
  problems
• Use dbms_output.put_line to show variable
  values
• Fix from the top down – 1 error may cause
  multiple error messages
• Data dictionary views: user_errors,
  user_source
Exception types
•   NO_DATA_FOUND
•   TOO_MANY_ROWS
•   DUP_VAL_ON_INDEX
•   And more…

• Create your own
  Another PL/SQL example…
FUNCTION build_name (name_in IN VARCHAR2, sex_in IN VARCHAR2)
RETURN VARCHAR2 IS
  unknown_sex EXCEPTION;
  name_out VARCHAR2(100);
BEGIN
  IF sex_in =‘M’ THEN name_out := ‘Mr. ‘ || name_in;
  ELSIF sex_in =‘F’ THEN name_out := ‘Ms. ‘ || name_in;
  ELSE
    RAISE unknown_sex
  END IF;
  RETURN name_out;
EXCEPTION
  WHEN unknown_sex THEN
       DBMS_OUTPUT.PUT_LINE
         (‘Unable to determine gender of individual!’);
END;
 Packages
• Structure of Packages
  – package header vs. package body
• Advantages of Packages
  – invalidated objects
  – hiding details of code
  – code maintainability
Oracle provided packages
•   DBMS_output
•   DBMS_job
•   DBMS_pipe
•   And more…

Set serveroutput on size 4000;
Dbms_output.put_line (‘text to be
  displayed’);

								
To top