PL/SQL Block Structure

上一篇 / 下一篇  2010-04-05 23:11:16 / 个人分类:Oracle

1.PL/SQL is a block-structured language, meaning that programs can be divided into logical blocks. A PL/SQL block is comprised of up to three sections: declarative (optional), executable (required), and exception handling (optional). Only BEGIN and END keywords are required. You can declare variables locally to the block that uses them. Error conditions (known as exceptions) can be handled specifically within the block to which they apply. You can store and change values within a PL/SQL block by declaring and referencing variables and other identifiers.
Variables, cursors, user-defined exceptions
SQL statements
PL/SQL statements
Actions to perform. when
errors occur
2.Use of Variables
With PL/SQL you can declare variables then use them in SQL and procedural statements anywhere an expression can be used.
Temporary storage of data
        Data can be temporarily stored in one or more variables for use when validating data input for processing later in data flow process.
Manipulation of stored values
        Variables can be used for calculations and other data manipulations without accessing the database.
        Once declared, variables can be used repeatedly within an application simply by referencing them in other statements, including other declarative statements.
Ease of maintenance
        When using %TYPE and %ROWTYPE (more information on %ROWTYPE is covered in a subsequent lesson) you declare variables, basing the declarations on the definitions of database columns. PL/SQL variables or cursor variables previously declared within the current scope
may also use the %TYPE and %ROWTYPE attributes as datatype specifiers. If an underlying definition changes, the variable declaration changes accordingly at runtime. This provides data independence, reduces maintenance costs, and allows programs to adapt as the database changes
to meet new business needs.
3.Handling Variables in PL/SQL
Declare and initialize variables within the declaration section.
        You can declare variables in the declarative part of any PL/SQL block, subprogram, or package. Declarations allocate storage space for a value, specify its datatype, and name the storage location so that you can reference it. Declarations can also assign an initial value and impose the NOT NULL constraint.
Assign new values to variables within the executable section.
The existing value of the variable is replace with a new one.
Forward references are not allowed. You must declare a variable before referencing it in other statements, including other declarative statements.
Pass values into PL/SQL subprograms through parameters.
        There are three parameter modes, IN (the default), OUT, and IN OUT. You use the IN parameter to pass values to the subprogram being called. You use the OUT parameter to return values to the caller of a subprogram. And you use the IN OUT parameter to pass initial values to the subprogram being called and to return updated values to the caller. IN and OUT subprogram parameters are covered in the PL/SQL Program Units course.
View the results from a PL/SQL block through output variables.
        You can use reference variables for input or output in SQL data manipulation statements.
4.Types of Variables
Examples of some of the different variable datatypes in the above illustration are as follows.
TRUE represents a Boolean value.
25-OCT-99 represents a DATE.
The photograph represents a BLOB.
The text of a speech represents a LONG RAW.
256120.08 represents a NUMBER datatype with precision
The movie represents a BFILE.
The city name represents a VARCHAR2.
          v_hiredate            DATE;          
  v_deptno                      NUMBER(2) NOT NULL := 10;
          v_location            VARCHAR2(13) := 'Atlanta';
  c_    comm                    CONSTANT NUMBER := 1400;
v_job                           VARCHAR2(9);
v_count                 BINARY_INTEGER := 0;
v_total_sal             NUMBER(9,2) := 0;
v_orderdate             DATE := SYSDATE + 7;
c_tax_rate              CONSTANT NUMBER(3,2) := 8.25;
v_valid                 BOOLEAN NOT NULL := TRUE;
6.The %TYPE attribute
Declare a variable according to:
A database column definition
Another previously declared variable
Prefix %TYPE with:
The database table and column
The previously declared variable name
  v_ename                                       emp.ename%TYPE;
  v_balance                                     NUMBER(7,2);
          v_min_balance                 v_balance%TYPE := 10;
7.Referencing Non-PL/SQL Variables
Store the annual salary into a SQL*Plus host variable
Reference non-PL/SQL variables as host variables.
Prefix the references with a colon (:).





Open Toolbar