"Oracle"-Primary PL/SQL

In the recent work to use stored procedures and functions, simply take PL/SQL as a whole to look at. I've seen the basic books and the blog posts in the garden, and I'll summarize what we've learned here.

First, the basic statement

1. Case

2, separators--;

3. Reference string-"'

4, Brackets--()

Ii. Types of data

1. Scalar type

1> numeric Type--number, etc.

2> character Type--CHAR VARCHAR

3> Date Type--date TIMESTAMP INTERVAL

4> Line Identification Type--ROWID Urowid

5> Boolean Type--TURE FALSE NULL

2. Composite type

1> record--similar to C language structure

2> Union Array

3> Nested tables

4> variable length Array

3. Reference type

1> Cursors

2>ref type

4. LOB Type

III. Basic Structure

1. Declaration part--Declare opening

2. Execution part-beginning of begin, end ending

3, exception handling--Exception start

Iv. Constants

Constant name CONSTANT type identifier [not NULL]: = value;

PI CONSTANT Number (9): = 3.1415926;

V. variables

Variable name type identifier [not null]:= value;

%type make a variable get the data type of the field

%rowtype make a variable get the data type of the entire record

Vi. expressions

Numeric expression: +-*/* * (exponentiation)

Character expression: by connector | | Composed of

Relational expressions:< > = = <= >=! = All any between EXISTS in IS-like

Logical expression: not OR and

Seven, operator

Set operator: INTERSECT minus UNION ALL

Row operator: All DISTINCT PRIOR (returns the parent row of the current row when a tree query)

Viii. Conditions of control

1. If condition

If Then END IF;

If Then else END IF;

If Then elsif Then else END IF;

2. Case conditions

Case expression

When value 1 Then

···············;

When value 2 Then

···············;

········

ELSE

···············;

END case;

3. Search-type case conditions

Case

When expression 1 Then

·····················;

When expression 2 Then

····················;

··············

ELSE

·················;

END case;

Nine, Cycle control

1, Loop loop

<<outer>>

Loop Loop loop

······;　　　　　　　　　　　　　　　　············; ···········;

EXIT;　　　　　　　　　　EXIT when conditional expression; LOOP

END LOOP;　　　　　　　　　　　　　　　　　　END LOOP; ············;

EXIT outer when conditional expression; --(two loops are exited)

END LOOP;

END LOOP outer;

2, while LOOP

While-expression loop

············;

END LOOP;

3, for LOOP

For I in 1..5 LOOP

············;

END LOOP;

Ten, other functional characteristics

1. Cursors

2. Process

3. Functions

4. Package

5. Collection

6. Error handling

　　

"Oracle"-Primary PL/SQL