oraclePLSQL学习笔记文档格式.docx

oraclePLSQL学习笔记文档格式.docx

《oraclePLSQL学习笔记文档格式.docx》由会员分享，可在线阅读，更多相关《oraclePLSQL学习笔记文档格式.docx（41页珍藏版）》请在冰豆网上搜索。

v_BeginDateDate;

b）引号标识符（quotedidentifier）

如果想要标识符大小写敏感，如在标识符中出现空格这样的字符或使用一个保留字，那么必须使用

引号标识符。

如果想对含有名为“exception”的列的表进行查询：

SELECT"

EXCEPTION"

FROMTABLENAME

2.2.2分界符（delimiter）

+、-、*、/：

加减乘除

＝、<

>

（or!

=or^=or~=）、>

、<

、>

=、<

=：

比较操作符

（、）：

表达式分界符

：

绑定变量指示符

；

语句终结符

%：

属性指示符

.：

组件选择符

：

项目分隔符

"

引号标识符的分界符

**：

指数操作符

@：

数据库链接指示符

:

赋值操作符

=>

联结操作符（干什么的？

）

..：

范围操作符

||：

字符串连接符

<

<

标签分界符

--,/**/：

注释分界符

space>

<

tab>

cr>

空格、制表符、回车

2.2.3文字（literal）

字符型：

以"

'

"

为分隔符，如'

1234'

数字型：

指数表示，如1.345E+12,E（e）表示指数（exponent）

布尔型：

TRUE,FALSE,NULL

2.2.4注释（comment）

多行注释不能嵌套

2.3变量声明（variabledeclare）

2.3.1声明语法

variable_nametype[CONSTANT][NOTNULL][:

=value（初始值）]

例：

DECLARE

v_TempVarNUMBERNOTNULL:

=0;

（此处不加初始值是不正确的）

c_MinimumStudentIDCONSTANTNUMBER（5）:

=10000;

如果需要可以使用DEFAULT代替:

=。

c_MinimumStudentIDCONSTANTNUMBER（5）DEFAULT10000.

在声明部分，每一行只能有一个变量声明。

2.4PL/SQL类型

标量（scalar）、复合（composite）、引用（reference）和LOB类型。

2.4.1标量类型（scalartype）

分为7个类：

数字（numeric）、字符（character）、原生（raw）、日期（date）、

行标识（rowid）、布尔（boolean）、可信任的（trusted）

2.4.2使用%TYPE（一种良好的编程风格）

v_FirstNamestudents.first_name%TYPE;

v_RoomIDclasses.room_id%TYPE;

--NUMBER（5）;

v_RoomID2v_RoomID%TYPE;

v_TmpVarNUMBER（7,3）NOTNULL:

=12.3;

v_AnotherVarv_TmpVar%TYPE;

--NUMBER（7,3）

如果%TYPE应用于一个限制为NOTNULL的列或变量，那么它返回的类型没有这个限制。

2.4.3用户定义子类型

SUBTYPEnew_type（新类型）ISoriginal_type（原类型）

（1）

SUBTYPEt_LoopCounterISNUMBER;

--定义新的子类型

v_LoopCountert_LoopCounter;

--声明一个子类型的变量

（2）

SUBTYPEt_NameTypeISstudents.first_name%TYPE;

SUBTYPE定义不能在定义中直接进行限制（constrain），可以有两种办法来解决。

v_DummyVarNUMBER（4）;

--dummyvariable（哑变量）

SUBTYPEt_LoopCounterISv_DummyVar%TYPE;

v_Countert_LoopCounter;

SUBTYPEt_NumericISNUMBER;

v_CounterISt_Numeric（5）;

2.4.4类型转换（typeconvert）

隐式的（implicit）、显式的（explicit）

相关函数

TO_CHAR（）--将来要把语法补上

TO_DATE（）

TO_NUMBER（）

2.5表达式和运算符（expressionandoperator）

2.5.1赋值（assignment）

左值（lvalue）“：

＝”右值（rvalue）

2.5.2

字符运算符：

“||”,逻辑运算符：

“AND”、“OR”

判断：

ISNULL、LIKE（“_”、“%”）、BETWEEN、IN

2.6PL/SQL控制结构

2.6.1IF_THEN_ELSIF_ELSE_ENDIF

注意：

1）此处的ElseIf没有e，即ElsIf）

2）ENDIF之间有空格

3）与Pascal语法不一样的是：

Else前的语句要以“；

”结束。

4）任何数与NULL比较的结果都为NULL。

2.6.2循环

1）简单循环

LOOP

EXIT[WHENcondition];

ENDLOOP;

v_CounterBINARY_INTEGER:

=1;

BEGIN

INSERTINTOtmp_tableVALUES（v_Counter,'

）;

v_Counter:

=v_Counter+1;

EXITWHENv_Counter>

50;

2）While循环

WHILEconditionLOOP

[EXIT[WHENconditioni]]（如果需要）

1）LOOP不是Pascal语法的“do”，

2）此处不需要Pascal的“begin”和“end”

WHILEv_Counter<

=50LOOP

3）For循环

FORloop_counterIN[REVERSE]low_bound..high_boundLOOP

1）循环变量loop_counter被隐式声明为BINARY_INTEGER类型，在循环前面没必要声明，

2）如果声明，将隐蔽外部的声明（即循环变量相当于一个内部变量）。

3）此处不需要Pascal的“begin”和“end”

4）LOOP不是Pascal语法的“do”

v_CountNUMBER:

=7;

INSERTINTOtmp_tableVALUES（v_Count,'

--此处v_Count为7

FORv_CountIN20..30LOOP

--此处v_Count为20..30

4）尽量不要使用GOTO语句

5）pargma是一些编译器的指令。

类似于C语言中的＃pargma或＃define或Delphi的{$}

2.7PL/SQL风格指南

2.7.1注释风格

1）在每个块和过程的开始放置注释，解释要做什么。

特别对于过程，列出哪个变量或参数的意义。

另外列出被访问的数据库也是个好主意。

2）在块的每个主要部分前添加注释

2.7.2变量名风格

要有描述性。

v_VariableName程序变量

e_ExceptionName用户定义的异常（错误）类型

t_TypeName用户定义的类型

p_ParameterName函数或过程的参数

c_ConstantValue常量

2.7.3大写风格

1）保留字大写--BEGINENDDECLAREELSIF

2）内置函数大写--SUBSTRCOUNTTO_CHAR

3）预定义类型大写--NUMBER（7,3）BOOLEANDATE

4）SQL关键字大写--SELECTINSERTUPDATEDELETEWHERE

5）数据库对象小写--log_tableclassesstudents

6）变量名使用大小写混合，名字的每个单词都大写

--v_HireDatee_tooManyStudentst_StudentRecordType

2.7.4缩进风格

每个块向内缩2个空格。

3第三章记录和表

3.1PL/SQL记录

记录类似于C中的结果，提供了处理分立的但又是作为一个整体单元的相关变量的一种机制。

语法：

TYPErecord_typeISRECORD（

fieldtype1[NOTNULL][:

=expr1],

fieldtype2[NOTNULL][:

=expr2],

…

）;

TYPEt_SampleRecordISRECORD（

CountNUMBER（4）,

NameVARCHAR2（10）:

='

Scott'

EffectiveDateDATE,

DescriptionVARCHAR2（45）NOTNULL:

Unknown'

v_Sample1t_SampleRecord;

v_Sample2t_SampleRecord;

v_Sample1.Count:

=9;

--使用方法：

用“.”（dotnotation）

3.1.1记录赋值

1）相同记录类型的变量可以互相赋值，

如上面的2个记录变量可以这样

v_Sample1:

=v_Sample2;

这样的赋值将使用“复制语义”（copysemantics[语义学]）

2）不同记录类型必须使用字段赋值方式,即使字段名和类型一样：

如

TYPEt_Rec1ISRECORD（

Field1NUMBER）;

TYPEt_Rec2ISRECORD（

v_R1t_Rec1;

v_R2t_Rec2;

/*这样是错误的，因为这两个变量不是一种记录类型*/

v_Rec1:

=v_Rec2;

/*这样是正确的*/

v_Rec1.Field1:

=v_Rec2.Field1;

v_Rec1.Field2:

=v_Rec2.Field2;

3）也可以使用SELECT语句对记录赋值，但查询选择结果列表中的字段吆喝记录中的字段顺序相匹配。

/*定义一个记录，注意%TYPE的用法*/

TYPEt_StudentRecordISRECORD（

FirstNamestudents.first_name%TYPE,

LastNamestudents.last_name%TYPE,

Majorstudents.major%TYPE）;

/*声明一个接收数据的变量*/

v_Studentt_StudentRecord;

/*注意查询返回的列的次序必须和记录的字段的次序相符合*/

SELECTfirst_name,last_name,major

INTOv_Student

FROMstudents

WHEREID=10000;

3.1.2使用%ROWTYPE

在PL/SQL中将一个记录声明维具有相同类型的数据库行的做法是很常见的。

所以提供了

%ROWTYPE运算符。

与%TYPE类似，%ROWTYPE将返回一个基于表定义的类型。

v_StudentRecordstudent%ROWTYPE--它是一个记录类型，记录的定义相当于。

/*它的结构：

first_namechar（10）]

last_namechar（10）

majorchar

（2）*/

1）表中的列的限制，如NOTNULL等的约束不会被包含进来，但字段类型的精度和长度包含进来。

2）如果表定义变了，那么%TYPE,%ROWTYPE也会随之改变。

3）对于匿名块，每次提交给PL/SQL引擎都要对%TYPE,%ROWTYPE求值，并且每次都要对已存储的对象

进行编译。

3.2表

TYPEtabletypeISTABLEOFtype[INDEXBYBINARY_INTEGER（为了兼容PL/SQL2）];

TYPEt_NameTableISTABLEOFstudent.first_anem%TYPE;

TYPEt_DateTableISTABLEOFDATE;

v_Namest_NameTable;

v_Datest_DateTable;

/*一旦声明了类型合变量后，我们可以使用下述语法引用PL/SQL表中的单个元素*/

/*TableName（Index）,Index是BINARY_INTEGER的变量

或可以转换为BINARY_INTEGER类型的变量的表达式*/

v_Names

（1）:

;

v_Dates（-4）:

=SYSDATE-1;

--"

-4"

是指在索引值

3.2.1表和数组

表的处理方式与数组类似，但PL/SQL中的表类似于数据库表，

它有两列：

KeyBINARY_INTEGER--主键

Value类型是在定义中指定的类型。

1）表是不受约束限制的。

关于行的唯一限制是BINARY_INTEGER所能表示的数值的范围。

2）表中的元素不必按特定的次序排列，因为它们不像数组那样连续存储在内存中，

所以可以按照任意键值插入。

3）键值（Key）不必是顺序的（sequential）。

所有BINARY_INTEGER数值或表达式都可以被用作表索引。

/*实际是个记录表*/

TYPEt_StudentTableISTABLEOFstudent%ROWTYPEINDEXBYBINARY_INTEGER;

v_Studentst_StudentTable;

SELECT*

INTOv_StudentTable（10001）

FROMstudent

WHEREid=10001;

因为这个表的每个元素都是一个记录，所以可以使用以下语法来引用这些字段：

table（index）.field

如：

v_Students（10001）.first_name:

Larry'

DBMS_OUTPUT.PUT_LINE（v_Students（10001）.first_name）;

3.2.2表属性

所有表的属性引用的语法：

table.property

属性返回类型说明

COUNTNUMBER返回表中行的数目

EXISTSBOOLEAN判断表项在表中是否存在

DELETEN/A删除表中的行

FIRSTBINARY_INTEGER

LASTBINARY_INTEGER

NEXTBINARY_INTEGER

PRIORBINARY_INTEGER

说明：

1）COUNT的语法：

table.COUNT例：

v_Total:

=v_Students.COUNT;

2）EXISTS的语法：

Exists（index）例：

IFv_Students.EXISTS

（1）THEN

3）DELETE的语法：

a）DELETE删除所有行例：

v_Students.DELETE

b）DELETE（i）删除索引为的行例：

v_Students.DELETE（100）

c）DELETE（i,j）删除索引位于i-j之间的所有行例：

v_Students.DELETE（1,3）

4）特别注意：

FIRST,LAST,NEXT,PRIOR仅仅返回索引，而不返回索引对应的值。

3.2.3PL/SQL表使用指南

1）最好给表设置一个单独的变量作为行的计数器，便于程序跟踪。

2）索引从1开始，按顺序加1，使程序控制更容易。

3）在显式为表赋值前，对表中元素的调用将引发ORA-1403错误。

4）除DELETE外，没办法删除所有行，但一个表刚被创建时时个空表，

所以如果想删一个表中的所有数据，可以把另一个空表赋值给它。

TYPEt_EmptyTableISTABLEOFstudent%ROWTYPE;

v_Emptyst_EmptyTable;

/*一些代码*/

v_Students:

=v_Emptys;

/************************************2003/1/10--2003/1/120:

20*********************************/

4第四章在PL/SQL中使用SQL

4.1SQL

可分为6个类别

1）数据操纵语言（Datamanipulationlanguage[manipulationn.处理,操作,操纵,被操纵]--DML）

SELECT,INSERT,UPDATE,DELETE,SETTRANSACTIONI,EXPLAINPLAN

2）数据定义语言（Datadefinitoinlanguage--DDL），创建，删除，更改一个对象的结构（包括更改权限）

DROP,CREATE,ALTER,GRANT,REVOKE

3）事务控制（Tranctioncontrol），保证数据的一致性

OMMIT,ROLLBACK,SAVEPOINT

4）会话控制（Sessioncontrol），改变单个数据库连接的设置

ALTERSESSIOIN,SETROLE

5）系统控制（Systemcontrol），改变整个数据库的设置

ALTERSYSTEM

6）嵌入式SQL，用在Oracle的预编译程序中

CONNECT,DECLARECURSOR,ALLOCATE

4.2表引用、数据库链接和同义词

[schema.]table[@dblink]

1）schema[模式、计划、图解、图表]，此处指用户

2）dblink是数据库链接：

CREATEDATABASELINKlink_name

CONNECTTOusernameIDENTIFIEDBYpassword

USINGsqlnet_string;

--sqlnet_string是指一个有关远程数据库的有效连接字符串。

CREATEDATABASELINKexample_backup

CONNECTTOsystemIDENTIFIEDBYmanager

USING'

backup_database'

然后就可以这样使用：

UPDATEstudent@example_backup

SETmajor='

Music'

WHEREid=10005;

3）同义词（synonym）--[antonym反义词]

CREATESYNONYMsynonym_nameFORreference

CREATESYNONYMbackup_studentsFORstudents@example_backup;

4.3伪列（pseud