PL/SQL block structure

Declare（可选）

Begin

Executable（必须）

sql、pl/sql

Exception（可选，嵌在executable部分）

end;

block types

1. Anonymous

2. procedure

3. fuction（必须返回值）

在isql*plus中输出

Set serveroutput on

Delimiters in String Literals

event VARCHAR2(15):='Father''s day';

You can specify any character that is not present in the string as delimiter. The slide shows how to use the q' notation to specify the delimiter. The examples use ‘!’ and ‘[‘ as delimiters. Consider the following example:

event := q'!Father's day!';

PL/SQL variables type

– Scalar

– Composite

– Reference

– Large object (LOB)

%TYPE Attribute

用于声明一个变量，它的类型与某一列或某一个变量类型相同。列中的not null属性并不被继承。

emp_lname employees.last_name%TYPE;

balance NUMBER(7,2);

min_balance balance%TYPE := 1000;

Bind Variables

VARIABLE return_code NUMBER

Substitution Variables

PL/SQL执行之前值就代入，所以无法在循环中使用。

Using DEFINE for a User Variable

可以使用DEFINE定义一个用户变量，只能是char类型

DEFINE lname= Urman

SELECT first_name INTO fname FROM employees

WHERE last_name='&lname';

Lexical Units in a PL/SQL Block

– Identifiers

– Delimiters

– Literals

– Comments

Commenting Code

单行：--

多行：/* … */

Variable Scope and Visibility

一个变量只在声明的块中可见（包括nested block）

对于同名变量，首先搜索当前块，未找到再去找outer block

Programming Guidelines

• Documenting code with comments

• Developing a case convention for the code

• Developing naming conventions for identifiers and other objects

• Enhancing readability by indenting