SELECT Statements in PL/SQL

必须使用INTO，只能选出一条记录

Naming Conventions

避免变量名与列名相同，如相同，优先选择列名。在where语句中会导致歧义。

DECLARE

hire_date employees.hire_date%TYPE;

sysdate hire_date%TYPE;

employee_id employees.employee_id%TYPE := 176;

BEGIN

SELECT hire_date, sysdate

INTO hire_date, sysdate

FROM employees

WHERE employee_id = employee_id;

END;

/

SQL Cursor

• A cursor is a pointer to the private memory area

allocated by the Oracle server.

• There are two types of cursors:

– Implicit: Created and managed internally by the

Oracle server to process SQL statements

– Explicit: Explicitly declared by the programmer

三种SQL Cursor Attributes

SQL%FOUND

SQL%NOTFOUND

SQL%ROWCOUNT

IF Statements

IF condition THEN

statements;

[ELSIF condition THEN

statements;]

[ELSE

statements;]

END IF;

CASE Expressions

CASE selector

WHEN expression1 THEN result1

WHEN expression2 THEN result2

...

WHEN expressionN THEN resultN

[ELSE resultN+1]

END;

/

TRUE AND NULL èNULL

TRUE OR NULL èTRUE

FALSE AND NULL èFALSE

FALSE OR NULL èNULL

NOT NULL èNULL

Basic Loops

LOOP

statement1;

. . .

EXIT [WHEN condition];

END LOOP;

WHILE Loops

WHILE condition LOOP

statement1;

statement2;

. . .

END LOOP;

FOR Loops(counter无需定义)

FOR counter IN [REVERSE]

lower_bound..upper_bound LOOP

statement1;

statement2;

. . .

END LOOP;

Guidelines for Loops

Basic loop:循环体中的语句至少需要被执行一次

While loop:必须将条件判断放在每次循环之前

For loop:预先知道迭代次数

Nested Loops and Labels

循环可欠套使用，不同层的循环可使用labels来表示

<>

END LOOP outer_loop;

Creating a PL/SQL Record

TYPE type_name IS RECORD

(field_declaration[, field_declaration]…);

identifier type_name;

field_declaration:

field_name {field_type | variable%TYPE

| table.column%TYPE | table%ROWTYPE}

[[NOT NULL] {:= | DEFAULT} expr]

%ROWTYPE Attribute(无须另外定义record type)

DECLARE

identifier reference%ROWTYPE;

Updating a Row in a Table by Using a Record

SET SERVEROUTPUT ON

SET VERIFY OFF

DEFINE employee_number = 124

DECLARE

emp_rec retired_emps%ROWTYPE;

BEGIN

SELECT * INTO emp_rec FROM retired_emps;

emp_rec.leavedate:=SYSDATE;

UPDATE retired_emps SET ROW = emp_rec WHERE

empno=&employee_number;

END;

/

SELECT * FROM retired_emps;

Creating an INDEX BY Table

TYPE type_name IS TABLE OF

{column_type | variable%TYPE

| table.column%TYPE} [NOT NULL]

| table%ROWTYPE

[INDEX BY PLS_INTEGER | BINARY_INTEGER

| VARCHAR2()];

identifier type_name;

注意点：1.主键为interger型或string型(PLS_INTEGER or BINARY_INTEGER)

2.column为一个标量或一个record型

3.表的记录数受主键的数据类型的范围限制，可为负数

4.主键和column都不可以被命名

Using INDEX BY Table Methods

EXISTS

COUNT

FIRST and LAST

PRIOR

NEXT

DELETE

Nested Tables

TYPE type_name IS TABLE OF

{column_type | variable%TYPE

| table.column%TYPE} [NOT NULL]

| table.%ROWTYPE

说明：计数列为正数，从1开始引用。属于数据库的数据类型的一种

可使用constructor直接赋值

TYPE location_type IS TABLE OF locations.city%TYPE;

offices location_type;

offices := location_type('Bombay', 'Tokyo','Singapore','Oxford');

VARRAY

TYPE location_type IS VARRAY(3) OF locations.city%TYPE;

offices location_type;

说明：声明时指定了最大值，可使用constructor直接赋值