[PL/SQL]10g PL/SQL學習筆記（一）

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

[@more@]