OCL2 Oracle 10 g SQL PLSQL Session #7 by daw95820

VIEWS: 0 PAGES: 41

									OCL2 Oracle 10g:
SQL & PL/SQL
Session #7
                 Matthew P. Johnson
                   CISDD, CUNY
                   January, 2005


      Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   1
Agenda
   Last time:
       Programming for SQL
       Pro*C, JDBC


   This time:
       SPs in PL/SQL


   Next time:
       More PL/SQL
       Triggers

                 Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   2
Step back
   Recall basic problem: need SQL plus
    stronger programming lang
        need to connect the two langs


   In all these cases (and in the web app case),
    idea is: put SQL in (traditional-lang) programs

   Another way: put programs in SQL
       i.e., store programs on the DBMS
       “stored procedures”
                  Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   3
Next topic: SPs
   “Persistent, Stored Modules” / “Stored Procedures /
    “PL/SQL programs” (in Oracle)

   Another way to connect application programming
    language and SQL

   Supports usual things:
       Declare, set vars to vals of expressions
       Print output
       Define (optional) procedures, functions
       Cursors


   PL/SQL can compute n!
                     Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   4
Integration with SQL
  DECLARE
   l_book_count INTEGER;
  BEGIN
   SELECT COUNT(*)
     INTO l_book_count
     FROM books
     WHERE author LIKE '%FEUERSTEIN, STEVEN%';
   DBMS_OUTPUT.PUT_LINE (
      'Steven has written (or co-written) ' || l_book_count ||
      ' books.');
   -- Oh, and I changed my name, so...
   UPDATE books
      SET author = REPLACE (author, 'STEVEN', 'STEPHEN')
   WHERE author LIKE '%FEUERSTEIN, STEVEN%';
  END;
                 Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   5
PL/SQL
   “Procedural Language/SQL”
       Oracle‟s language for stored procedures
   Simple, interpreted, procedural language

   But Pascal-like:
       BEGIN END, not { }
       AND OR, not && ||
       vars defined at top of procedre
       how return works


                   Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   6
PL/SQL
   Generally speaking can be used wherever
    SQL can be
       sqlplus
       embeded SQL
       JDBC


   Can store programs in files (.sql), run later
       @myprog.sql runs code in myprog.sql



                  Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   7
Scripting languages
   Big problems v. small problems
   Big solutions v. small solutions

   Programming languages:
     C/C++, Java, etc.

   Scripting languages:
     PL/SQL, Perl, PHP, Unix shell, DOS batch files, Python, Excel
      macros, VBA, JavaScript
   Usual properties of scripting languages:
     Interpreted

           Though now compiled to bytecode or (optionally) to native
       Don‟t require functions/procedures
           Though now supported
       Weakly typed
           Lots of auto-conversion

                        Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   8
PL/SQL: Hello, World
   http://pages.stern.nyu.edu/~mjohnson/dbms/eg/lec19/hello.sql



    BEGIN
      -- print out message
      DBMS_OUTPUT.PUT_LINE('Hello World, from PL/SQL');
    END;
    /




                   Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   9
Hello, World
   Try again…

    SET SERVEROUTPUT ON
    BEGIN
      -- print out message
      DBMS_OUTPUT.PUT_LINE('Hello World, from PL/SQL');
    END;
    /




                Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   10
Use start-up script
   Go to <Orahome>\sqlplus\admin\glogin.sql
   Start-up script run upon login to SQL*Plus
   Add “SET SERVEROUTPUT ON” to it

   If running non-i version of SQL*Plus, also
    looks in current dir for login.sql script




               Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   11
How to run code
   The code before ended with a forward slash
   Not SQL and not PL/SQL – just for SQL*Plus
    to tell it to run the code entered
   Must go on its own line
       O.w., will be ignored and then interpreted as part
        of code, causing an error
   To call a procedure in SQL*Plus, can also
    use execute/exec:
exec DBMS_OUTPUT.PUT_LINE('Hello World, from PL/SQL')


                   Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   12
How to run code
   EXEC is just short-hand:

         SQL> exec dbms_output.put_line('hi ');
         dbms_output.put_line('there'




                Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   13
PL/SQL operators/symbols
   ; end statement
   % attribute indicator (cursor attributes like
    %ISOPEN and indirect declaration attributes
    like %ROWTYPE
   : host variable indicator
   <> and !=         not-equal-to
   = equal-to
   := assignment op
   ** exponentiation operator
   -- , /* and */, rem comments
               Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   14
Var names
   identifiers limited to 30 alpha-num chars
       Must start with letter, $, _, or #
   E.g.: abc, $a$, $$$

   PL/SQL is case Insensitive
       abc, ABC, AbC all the same
       Unless you use double-quotes…




                    Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   15
Literals
   Numbers: 123, 12.3, 3.05E19, 12e-5, null
   String: „abc‟, „AbC‟, null
       String comparison is case-SENSitive
   Boolean: true, false, null
       true != „true‟


   No date literals, as in regular SQL
       To_date('31-JAN-94')
   Escape single-quotes in strings with two single-
    quotes
   „it‟‟s‟       it‟s
   ''''''        ''
                         Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   16
Blocks
   PL/SQL is a block-structured language

   Block = seq. of instructions, with scope
   Can have anonymous blocks
   And named blocks
       Procedures
       Functions




                 Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   17
Structure of a block
          header              --if named
          DECLARE             --optional
          --var declarations
          BEGIN
          --executable statements
          --queries/updates, etc.
          EXCEPTION --optional
          --catch exceptions
          END;
          /                   --to execute
   As in Pascal, var declars precede body
                Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   18
PL/SQL code examples
   One example:
       Likes(drinker, beverage)

        BEGIN
                 INSERT INTO Likes VALUES(„Izzy', „milk');
                 DELETE FROM Likes
                 WHERE drinker = „Izzy' AND
                        beverage = „Beaujolais Nouveau ';
                 COMMIT;
        END; /

   Another example:
       http://pages.stern.nyu.edu/~mjohnson/dbms/eg/lec19/age.sql

                       Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   19
Procedures
   Stored database objects that use a PL/SQL
    statement(s) in their body
   Create/drop similar to other SQL objects:
       ALTER PROCEDURE… in MySQL
          CREATE PROCEDURE <my-proc>
                (<params>) AS
          <procedure body as above>;

          CREATE OR REPLACE PROCEDURE
          <my-proc>(<params>) AS
          <procedure body as above>;

          DROP PROCEDURE <my-proc>;
                Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   20
Example procedure
   Define the procedure:
      CREATE PROCEDURE testProcedure AS
      BEGIN
           INSERT INTO Student VALUES (5, „Joe‟);
           COMMIT;
      END;

   Now we can call it:
        EXEC testProcedure



                Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   21
More details on procedures
   Parameter list has name-mode-type triples:
   Modes: IN, OUT, or IN OUT
       Fulfills role similar to pass-by-value v. pass-by-
        reference
       Default is IN


   Types must match, so can get exact field
    type:
            relation.attribute%TYPE


                   Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   22
Procedure example
   A procedure to take a beer and price and add
    it to Joe's menu: Sells(bar, beer, price)
         CREATE PROCEDURE izzyMenu(
                   b IN Sells.beer%TYPE,
                   p IN Sells.price%TYPE) AS
         BEGIN
              INSERT INTO Sells
              VALUES(„Izzy‟‟s', b, p);
         END;
         /

               Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   23
Functions
   Like procedures but with return values
        CREATE FUNCTION <functionName>
              (<paramList>) RETURN type AS
        <localDeclarations>
        BEGIN
        <functionBody>
        END;

        DROP FUNCTION <functionName>;

   Big strength: can be called from SQL
               Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   24
Function example
   Like procedures but with return values
       drop in same way
       Big strength: can be called from SQL
          CREATE OR REPLACE FUNCTION maxval(a IN
          int, b IN int) RETURN int AS
          BEGIN
             IF a > b THEN RETURN a;
             ELSE RETURN b;
             END IF;
          END maxval;

          INSERT INTO R VALUES(“abc”, maxval(5,10));

   http://pages.stern.nyu.edu/~mjohnson/dbms/eg/lec19/maxval.sql

                    Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   25
How to run scripts
   Don‟t want to type ftns into sqlplus by hand
   Define them in a .sql file
   In sqlplus, execute .sql file
        SQL> @maxval.sql
       Runs commands in file
       Here, defines function
   Now, we can call functions
             SQL> exec DBMS_OUTPUT.PUT_LINE
             (maxval(5,10))
   See
    http://pages.stern.nyu.edu/~mjohnson/dbms/eg/lec19/plsql.txt

                   Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   26
How to run scripts
   Can also use the start command:
     SQL> START maxval.sql

   If no file extension is given, .sql is assumed
   Can use full paths:
        SQL> @c:\somewhere\maxval.sql
   Scripts can call other scripts
   Use @ for current dir, @@ for dir of current script
   Scripts are not (by default) echoed. Can use:
       SQL> SET ECHO ON

                  Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   27
          CREATE OR REPLACE FUNCTION wordcount (str IN
Word      VARCHAR2)
             RETURN PLS_INTEGER AS
count     /* words PLS_INTEGER := 0; ***Commented out for
          intentional error*** */

program      len PLS_INTEGER := NVL(LENGTH(str),0);
             inside_a_word BOOLEAN;
          BEGIN
             FOR i IN 1..len + 1 LOOP
               IF ASCII(SUBSTR(str, i, 1)) < 33 OR i > len
               THEN
                  IF inside_a_word
                  THEN
                     words := words + 1;
                     inside_a_word := FALSE;
                  END IF;
               ELSE
                  inside_a_word := TRUE;
               END IF;
             END LOOP;
             RETURN words;
                                                             28
               Matthew
          END; P. Johnson, OCL2, CISDD CUNY, January 2005
Intermission
   Fill out evals

   Scottish Parliament/Outer join exercises

   Work on exercises 1-3 of lab 7




                Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   29
Getting errors
   Simple says:
      Warning: Function created with compilation
      errors.

   To get actual errors, say SHOW ERR(ORS)
   Can also get errors per object:
      SQL> show errors function wordcount


   Warning: must get object type right!
   Can also look at user_errors tbl directly
                Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   30
Calling functions and procedures
   Procedures can simple executed, ftns can‟t
   How to just call a ftn?
   Can use dbms_output, as seen
   Can also select the ftn value from dual
     SQL> select(wordcount(„hi there‟) from dual;

   Also, can describe ftns and procs:
     SQL> describe wordcount


                Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   31
Agenda
   Go through rest of SQL join lab exercises
   A little more PL/SQL lecture
   Evals
   Continue PL/SQL lab
   Later: go through some PL/SQL exercises…




              Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   32
Look up procedures, functions
   In Oracle, functions & procedures in
    user_procedures:
     SELECT object_name from user_procedures;




               Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   33
Subblocks
Blocks may contain blocks, for narrower scope:
CREATE OR REPLACE PROCEDURE calc_totals IS
 year_total NUMBER;
BEGIN
 year_total := 0;
 /* Nested anonymous block */
 DECLARE
    month_total NUMBER;
 BEGIN
    month_total := year_total / 12;
 END;
END;
            Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   34
Branching
   IF–THEN statements use THEN
   Must end with END IF
   Use ELSIF in place of ELSE IF
                        IF <condition> THEN
                              <statement(s)>
                        ELSIF
                              <statement(s)>
                        END IF;
   Example:
       http://pages.stern.nyu.edu/~mjohnson/dbms/eg/lec19/maxval.sql


                     Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   35
More ifs

    IF <condition>                      IF <expression>
    ELSE                                ELSEIF <expression>
    END IF;                             ELSE
                                        END IF;




             Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   36
Multiple elsifs
   An if statement can have multiple elseifs:
      IF salary >= 10000 AND salary <= 20000
      THEN give_bonus(employee_id, 1500);
      ELSIF salary > 20000 AND salary <= 40000
      THEN give_bonus(employee_id, 1000);
      ELSIF salary > 40000
      THEN give_bonus(employee_id, 400);
      END IF;



                Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   37
Nested ifs                                               IF condition1
                                                         THEN
   As usual, if statements                                IF condition2
    can be nested:                                         THEN
                                                             statements2
                                                           ELSE
   Can often be replaced                                    IF condition3
                                                             THEN
    with an ANDed condition                                    statements3
                                                             ELSIF condition4
                                                             THEN
                                                               statements4
                                                             END IF;
                                                           END IF;
                                                         END IF;

               Matthew P. Johnson, OCL2, CISDD CUNY, January 2005               38
Loop example
    DECLARE
         i NUMBER := 1;
    BEGIN
         LOOP
              INSERT INTO T1
         VALUES(i,i);
              i := i+1;
              EXIT WHEN i>100;
         END LOOP;
    END;
    /

          Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   39
More loops
                             LOOP
   Infinite loop:
                              executable_statements;
                             END LOOP;




                         WHILE condition
                         LOOP
   while loop:
                          executable_statements;
                         END LOOP;


                  Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   40
More loops
   Numerical for loop:
      FOR for_index IN low_value .. high_value
      LOOP
       executable_statements;
      END LOOP;

   Cursor for loop:
           FOR record_index IN my_cursor
           LOOP
            executable_statements;
           END LOOP;
                 Matthew P. Johnson, OCL2, CISDD CUNY, January 2005   41

								
To top