lect8-1

lect8-1 - Programming in Oracle Programming with PL/SQL...

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

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

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

View Full DocumentRight Arrow Icon
2 Why/When PL/SQL Why/When PL/SQL PL/SQL allows SQL to be combined with programming language constructs (e.g., if/else, loops, function declarations) This is generally "lighter-weight" than connecting with JDBC, since it is run within the database PL/SQL functions can even be called from a query!!
Background image of page 2
3 PL/SQL Blocks PL/SQL Blocks There are two types of block structures for PL/SQL. Anonymous blocks: have no name can be written and executed immediately in SQLPLUS can be used in a trigger Named PL/SQL blocks: functions procedures Important: Always put a new line with only a / at the end of a block, so that Oracle will compile it.
Background image of page 3

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

View Full DocumentRight Arrow Icon
4 Block Structure for Anonymous Block Structure for Anonymous PL/SQL Blocks PL/SQL Blocks DECLARE (optional) Declare PL/SQL objects to be used within this block BEGIN (mandatory) Define the executable statements EXCEPTION (optional) Define the actions that take place if an error arises END; (mandatory) /
Background image of page 4
5 Declaring PL/SQL Variables Declaring PL/SQL Variables 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;
Background image of page 5

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

View Full DocumentRight Arrow Icon
6 Declaring Variables with the Declaring Variables with the %TYPE Attribute %TYPE Attribute Examples ... sname Sailors.sname%TYPE; fav_boat VARCHAR2(30); my_fav_boat fav_boat%TYPE := 'Pinta'; ...
Background image of page 6
7 Creating a PL/SQL Record Creating a PL/SQL Record Declare variables to store the name, id, age and rating of a new sailor. Example ... TYPE sailor_record_type IS RECORD (sname VARCHAR2(10), sid VARCHAR2(9), age NUMBER(3), rating NUMBER(3)); sailor_record sailor_record_type;
Background image of page 7

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

View Full DocumentRight Arrow Icon
8 The %ROWTYPE Attribute The %ROWTYPE Attribute Declare a variable to store the same information about a reservation as it is stored in the Reserves table. reserves_record reserves%ROWTYPE;
Background image of page 8
SELECT Statements in PL/SQL SELECT Statements in PL/SQL INTO clause is required. Query must return exactly one row. Otherwise, a NO_DATA_FOUND or TOO_MANY_ROWS exception is thrown DECLARE v_sname VARCHAR2(10); v_rating NUMBER(3); BEGIN SELECT sname, rating INTO v_sname, v_rating FROM Sailors WHERE sid = '112'; ... END;
Background image of page 9

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

View Full DocumentRight Arrow Icon
Image of page 10
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 07/15/2011 for the course ECO 2023 taught by Professor Mr.raza during the Summer '10 term at FAU.

Page1 / 38

lect8-1 - Programming in Oracle Programming with PL/SQL...

This preview shows document pages 1 - 10. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online