PL/SQL User's Guide and Reference:PL/SQL Architecture

Block Structure

The basic units (procedures, functions, and anonymous blocks) that make up a
PL/SQL program are logical blocks, which can be nested inside one another.

A block groups related declarations and statements. You can place declarations close to
where they are used, even inside a large subprogram. The declarations are local to the
block and cease to exist when the block completes, helping to avoid cluttered
namespaces for variables and procedures.

As Figure 1–2 shows, a PL/SQL block has three parts: a declarative part, an executable
part, and an exception-handling part that deals with error conditions. Only the
executable part is required.

First comes the declarative part, where you define types, variables, and similar items.
These items are manipulated in the executable part. Exceptions raised during
execution can be dealt with in the exception-handling part.

    You can nest(嵌套) blocks in the executable and exception-handling parts of a PL/SQL block
or subprogram but not in the declarative part. You can define local subprograms in the
declarative part of any block. You can call local subprograms only from the block in
which they are defined.(只可以在执行部分和异常处理部分嵌套PL/SQL语句块，可以在声明部分定义任何子程序(函数， 过程))，但是，只可以在本语句块中调用。

 匿名语句块和子程序:

DECLARE

--子程序函数

FUNCTION TEST1 RETURN VARCHAR2 IS

BEGIN

--嵌套语句快

BEGIN

NULL;

END;

RETURN '';

END;
BEGIN

NULL;

--执行部分嵌套语句块

BEGIN

NULL;

END;
EXCEPTION

WHEN OTHERS THEN

--异常处理部分嵌套语句块

BEGIN

NULL;

END;
END;

Variables and Constants

Declaring Variables(声明)
Variables can have any SQL datatype, such as CHAR, DATE, or NUMBER, or a
PL/SQL-only datatype, such as BOOLEAN or PLS_INTEGER(BOOLEAN 和 PLS_INTEGER属于PL/SQL类型). For example, assume that
you want to declare a variable named part_no to hold 4-digit numbers and a variable
named in_stock to hold the Boolean value TRUE or FALSE. You declare these
variables as follows:

part_no NUMBER(4);
in_stock BOOLEAN;

You can also declare nested tables, variable-size arrays (varrays for short), and records
using the TABLE, VARRAY, and RECORD composite datatypes.

Assigning Values to a Variable(赋值)

赋值有三种方式:
1、:=,

2、select intofetchbulk collect into

3、函数或者过程的输出参数:outin out

Declaring Constants(声明常量) 

Declaring a constant is like declaring a variable except that you must add the keyword
CONSTANT(声明常量需要使用关键字CONSTANT) and immediately assign a value to the constant. No further assignments to the constant are allowed(声明为常量不可以再为其赋值). The following example declares a constant:
credit_limit CONSTANT NUMBER := 5000.00;
credit_limit CONSTANT NUMBER DEFAULT 5000.00; 

Object Types
PL/SQL supports object-oriented programming through object types. An object type
encapsulates a data structure along with the functions and procedures needed to
manipulate the data. The variables that form the data structure are known as
attributes. The functions and procedures that manipulate the attributes are known as
methods.

Object types reduce complexity by breaking down a large system into logical entities.
This lets you create software components that are modular, maintainable, and
reusable.

Object-type definitions, and the code for the methods, are stored in the database.
Instances of these object types can be stored in tables or used as variables inside
PL/SQL code.
  

CREATE OR REPLACE TYPE BANK_ACCOUNT AS OBJECT
(

ACCT_NUMBER INTEGER(5),

BALANCE
REAL,

STATUS
VARCHAR2(10),

MEMBER PROCEDURE OPEN(AMOUNT IN REAL),

MEMBER PROCEDURE VERIFY_ACCT(NUM IN INTEGER),

MEMBER PROCEDURE CLOSE(NUM IN INTEGER, AMOUNT OUT REAL),

MEMBER PROCEDURE DEPOSIT(NUM IN INTEGER, AMOUNT IN REAL),

MEMBER PROCEDURE WITHDRAW(NUM IN INTEGER, AMOUNT IN REAL),

MEMBER FUNCTION CURR_BAL(NUM IN INTEGER) RETURN REAL
)
;

PL/SQL中的反义字符的使用:

Doubling the quotation marks within a complicated literal, particularly one that
represents a SQL statement, can be tricky. You can also use the following notation to
define your own delimiter characters for the literal. You choose a character that is not
present in the string, and then do not need to escape other single quotation marks
inside the literal:

-- q'!...!' notation lets us use single quotes inside the literal.
string_var := q'!I'm a string, you're a string.!';

-- To use delimiters [, {, <, and (, pair them with ], }, >, and ).
-- Here we pass a string literal representing a SQL statement
-- to a subprogram, without doubling the quotation marks around
-- 'INVALID'.

func_call(q'[select index_name from user_indexes where status = 'INVALID']');

-- For NCHAR and NVARCHAR2 literals, use the prefix nq instead of q.
where_clause := nq'#where col_value like '%é'#';

(PL/SQL提供了默认的两种不为空的数据类型)

PL/SQL provide subtypes NATURALN and POSITIVEN that are predefined as NOT
NULL. You can omit the NOT NULL constraint when declaring variables of these types,
and you must include an initialization clause.

Using the %TYPE Attribute

(使用%type不仅可以引用本表中的某一列的数据类型，也可以引用先前定义的数据类型)

DECLARE

CREDIT
NUMBER(7, 2);

DEBIT
 CREDIT%TYPE;

DECLARATIONS

--2-10 PL/SQL User's Guide and Reference

NAME VARCHAR2(20) := 'JoHn SmItH';

-- If we increase the length of NAME, the other variables

-- become longer too.

UPPER_NAME NAME%TYPE := UPPER(NAME);

LOWER_NAME NAME%TYPE := LOWER(NAME);

INIT_NAME
NAME%TYPE := INITCAP(NAME);
BEGIN

NULL;
END;

注意:

 

%TYPE variables do not inherit the NOT NULL column constraint(没有继承他的非空属性). In the next example,
even though the database column employee_id is defined as NOT NULL, you can
assign a null to the variable my_empno:

DECLARE

MY_EMPNO EMPLOYEES.EMPLOYEE_ID%TYPE;
BEGIN

MY_EMPNO := NULL; -- this works
END;

Using the %ROWTYPE Attribute

 The %ROWTYPE attribute provides(可以使用游标，也可以使用表和视图) a record type that represents a row in a table (or view). The record can store an entire row of data selected from the table, or fetched
from a cursor or strongly typed cursor variable。

DECLARE

-- %ROWTYPE can include all the columns in a table...

EMP_REC EMPLOYEES%ROWTYPE;

-- ...or a subset of the columns, based on a cursor.

CURSOR C1 IS

SELECT DEPARTMENT_ID, DEPARTMENT_NAME FROM DEPARTMENTS;

DEPT_REC C1%ROWTYPE;

-- Could even make a %ROWTYPE with columns from multiple tables.

CURSOR C2 IS

SELECT EMPLOYEE_ID,

EMAIL,

EMPLOYEES.MANAGER_ID,

LOCATION_ID DECLARATIONS FUNDAMENTALS

FROM EMPLOYEES, DEPARTMENTS

WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID;

JOIN_REC C2%ROWTYPE;
BEGIN

-- We know EMP_REC can hold a row from the EMPLOYEES table.

SELECT * INTO EMP_REC FROM EMPLOYEES WHERE ROWNUM < 2;

-- We can refer to the fields of EMP_REC using column names

-- from the EMPLOYEES table.

IF EMP_REC.DEPARTMENT_ID = 20 AND EMP_REC.LAST_NAME = 'JOHNSON' THEN

EMP_REC.SALARY := EMP_REC.SALARY * 1.15;

END IF;
END;

注意:

Columns in a row and corresponding fields in a record have the same names and
datatypes. However, fields in a %ROWTYPE record do not inherit the NOT NULL column
constraint.(%ROWTYPE也没有继承它的非空属性)

     You can assign a list of column values to a record by using the SELECT or FETCH
statement, as the following example shows. The column names must appear in the
order in which they were defined by the CREATE TABLE or CREATE VIEW statement.

 

DECLARE

DEPT_REC DEPARTMENTS%ROWTYPE;
BEGIN

SELECT *

INTO DEPT_REC

FROM DEPARTMENTS

WHERE DEPARTMENT_ID = 30

AND ROWNUM < 2;
END;

 

Some languages allow you to declare a list of variables that have the same datatype.
PL/SQL does not allow this. You must declare each variable separately:

 (其他的编程语句允许一次性声明多个连续性的变量，但是PL/SQL不允许，只允许一个一个的单独的声明变量)：

DECLARE

-- Multiple declarations not allowed.

-- i, j, k, l SMALLINT;

-- Instead, declare each separately.

I SMALLINT;

J SMALLINT;

-- To save space, you can declare more than one on a line.

K SMALLINT;

L SMALLINT;
BEGIN

NULL;
END;