{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

PL-SQL1 - Programming in Oracle with PL/SQL Procedural...

Info iconThis preview shows pages 1–11. Sign up to view the full content.

View Full Document Right Arrow Icon
    Programming in Oracle with PL/SQL Procedural Language Extension to SQL
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
    PL/SQL Allows using general programming tools with SQL,  for example: loops, conditions, functions, etc. This allows a lot more freedom than general SQL, and  is lighter-weight than JDBC. We write PL/SQL code in a regular file, for example  PL.sql, and load it with @PL in the sqlplus console.
Background image of page 2
    PL/SQL Blocks PL/SQL code is built of Blocks, with a unique structure. There are two types of blocks in PL/SQL: 1. Anonymous Blocks:  have no name (like scripts) can be written and executed immediately in SQLPLUS can be used in a  trigger 2.    Named Blocks: Procedures Functions
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
    :Anonymous Block Structure DECLARE         (optional) /* Here you declare the variables you will use in this block */ BEGIN           (mandatory) /* Here you define the executable statements (what the block  DOES!)*/ EXCEPTION     (optional) /* Here you define the actions that take place if an exception is  thrown during the run of this block */ END;                  (mandatory) / Always put a new line with only a / at the  end of a block! (This tells Oracle to run the  block)  A correct completion of a block will  :generate the following message PL/SQL procedure successfully completed
Background image of page 4
   
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
      DECLARE Syntax Examples identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr ]; Declare birthday DATE; age NUMBER(2) NOT NULL := 27; name VARCHAR2(13) := 'Levi'; magic CONSTANT NUMBER := 77; valid BOOLEAN NOT NULL := TRUE;  Notice that PL/SQL includes all …SQL types, and more
Background image of page 6
     Declaring Variables with the %TYPE Attribute Examples DECLARE sname Sailors.sname%TYPE; fav_boat VARCHAR2(30); my_fav_boat fav_boat%TYPE := 'Pinta'; ...  Accessing  column sname in table Sailors  Accessing another variable
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
     Declaring Variables with the %ROWTYPE Attribute .Declare a variable with the type of a ROW of a table ?And how do we access the fields in  reserves_record reserves_record Reserves%ROWTYPE; reserves_record.sid:=9; Reserves_record.bid:=877;  Accessing table Reserves
Background image of page 8
    Creating a PL/SQL Record  A record  is a type of variable which we can define )like ’struct‘ (in C or ’object‘ in Java DECLARE TYPE sailor_record_type IS RECORD (sname VARCHAR2(10), sid VARCHAR2(9), age NUMBER(3), rating NUMBER(3)); sailor_record sailor_record_type; ... BEGIN Sailor _ record.sname:=‘peter’; Sailor _ record.age:=45;
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
    Creating a Cursor We create a Cursor when we want to go over a result of a  query  (like  ResultSet  in JDBC) Syntax Example:   DECLARE    cursor c is  select * from sailors;      sailorData sailors%ROWTYPE;      BEGIN        open c;        fetch c into  sailorData;  sailorData is a  variable that can  hold a ROW from the sailors table  Here the first row  of sailors is  inserted into sailorData
Background image of page 10
Image of page 11
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}