lec13.365

lec13.365 - . . Fall 2007 CPE/CSC 365: Introduction to...

Info iconThis preview shows pages 1–4. 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
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: . . Fall 2007 CPE/CSC 365: Introduction to Database Systems Alexander Dekhtyar . . PL/SQL: Introduction Overview PL/SQL is a programming language-like extension of SQL. Its implementation is available on Oracle DBMS servers. Features of PL/SQL: Procedural extension of SQL. Variables Assignments Program control (if-then-else, loops) Use of SQL update and query statements Cursors, rows, tables Anonymous blocks, procedures, functions Compiled needs static database schema no database schema alteration (DDL) commands stored procedures library packages I/O dynamic schemas (allows for use of DDL commands) Anonymous Blocks and Basic Language Features An anonymous block is the most basic type of PL/SQL program. The syntax of an anonymous block is 1 DECLARE <declaration-section> BEGIN <executable-section> [EXCEPTION <exception-handling section>] END; Declaration Section, Variables, Type System The content of the declaration section consists of different declarations . All dec- larations must end with a semicolumn (;). The following declaration types exist in SQL/PL. Constant declaration. All constants used in the anonymous block must be declared. The syntax of a constant declaration is <ConstantName> constant <Type> := <Value>; <ConstantName> : name of the constant; <Type> : type of the constant (see below); <Value> : value of the constant; Variable declaration. All global variables in PL/SQL must be declared in the declaration section 1 . The format of the variable declaration is <VarName> <Type> [not null] [:= <Value>]; <VarName> : name of the variable; <Type> : type of the variable (see below); not null : (optional) if present, variable must be initialized and may not be null <Value> : (optional) initial value of the variable. Procedure declaration. SQL/PL procedures are similar to procedures in Pascal , or C/Java void functions/methods. Procedures are discussed below. Function declaration. SQL/PL functions have a return value. Functions are discussed below. Type declaration. Declarations of user-defined types in SQL/PL. Discussed in detail below. Atomic Data Types SQL/PL has a type system that is different from the type system of SQL . SQL types have corresponding SQL/PL type, but the names of the types may differ. 1 SQL/PL allows ad-hoc use of variables with local scope in loops. Such variables need not be declared at the beginning of the block. 2 SQL/PL uses the following atomic types. Category SQL/PL Type SQL Type Explanation Numeric binary integer INT natural unsigned integer, 0 to 2 3 1 positive positive integer, 1 to 2 3 1 number(m,n) NUMBER(m,n) Character char(n) CHAR(n) varchar2(n) VARCHAR2(n) Boolean boolean BOOLEAN Date/Time date DATE Note: a single variable or constant declaration declares exactly one variable or constant....
View Full Document

This note was uploaded on 05/19/2008 for the course CSC 365 taught by Professor Dekhtyar during the Spring '08 term at Cal Poly.

Page1 / 9

lec13.365 - . . Fall 2007 CPE/CSC 365: Introduction to...

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

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