Oracle pl sql

Solo disponible en BuenasTareas
  • Páginas : 11 (2647 palabras )
  • Descarga(s) : 0
  • Publicado : 8 de septiembre de 2012
Leer documento completo
Vista previa del texto
Using Oracle PL/SQL 

[pic]

Basic Structure of PL/SQL

Variables and Types

Simple PL/SQL Programs

Control Flow in PL/SQL

Cursors

Procedures

Discovering Errors

Printing Variables

Note: The material on triggers that was formerly in this document has been moved to A New Document on Constraints and Triggers.


Basic Structure of PL/SQL

PL/SQL stands for ProceduralLanguage/SQL. PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other. Typically, each block performs a logical action in he program. A block has the following structure:

    DECLARE

    /*Declarative section: variables, types, and local subprograms. */

    BEGIN

    /* Executable section: procedural and SQL statements go here. */

    /* This is the only section of the block that is required. */

    EXCEPTION

    /* Exception handling section: error handling statements go here. */

    END;

Only the executable section is required. The other sections are optional.The only SQL statements allowed in a PL/SQL program are SELECT, INSERT, UPDATE, DELETE and several other data manipulation statements plus some transaction control. However, the SELECT statement has a special form in which a single tuple is placed in variables; more on this later. Data definition statements like CREATE, DROP, or ALTER are not allowed. The executable section also contains constructssuch as assignments, branches, loops, procedure calls, and triggers, which are all described below (except triggers). PL/SQL is not case sensitive. C style comments (/* ... */) may be used.
To execute a PL/SQL program, we must follow the program text itself by
A line with a single dot ("."), and then
A line with run;
As with Oracle SQL programs, we can invoke a PL/SQL program either bytyping it in sqlplus or by putting the code in a file and invoking the file in the various ways we learned in Getting Started With Oracle.

Variables and Types

Information is transmitted between a PL/SQL program and the database through variables. Every variable has a specific type associated with it. That type can be
One of the types used by SQL for database columns
A generic type used inPL/SQL such as NUMBER
Declared to be the same as the type of some database column
The most commonly used generic type is NUMBER. Variables of type NUMBER can hold either an integer or a real number. The most commonly used character string type is VARCHAR(n), where n is the maximum length of the string in bytes. This length is required, and there is no default. For example, we might declare:DECLARE

    price  NUMBER;

    myBeer VARCHAR(20);

Note that PL/SQL allows BOOLEAN variables, even though Oracle does not support BOOLEAN as a type for database columns.
Types in PL/SQL can be tricky. In many cases, a PL/SQL variable will be used to manipulate data stored in a existing relation. In this case, it is essential that the variable have the same type as the relation column. Ifthere is any type mismatch, variable assignments and comparisons may not work the way you expect. To be safe, instead of hard coding the type of a variable, you should use the %TYPE operator. For example:

DECLARE

    myBeer Beers.name%TYPE;

gives PL/SQL variable myBeer whatever type was declared for the name column in relation Beers.
A variable may also have a type that is a record withseveral fields. The simplest way to declare such a variable is to use %ROWTYPE on a relation name. The result is a record type in which the fields have the same names and types as the attributes of the relation. For instance:

DECLARE

    beerTuple Beers%ROWTYPE;

makes variable beerTuple be a record with fields name and manufacture, assuming that the relation has the schema Beers(name,...
tracking img