PL-SQL1

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

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

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

Unformatted text preview: Programming in Oracle with PL/SQL Procedural Language Extension to SQL 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. 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 :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 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 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 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 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; 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...
View Full 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 / 42

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

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

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