oracledatabase11gplsql编程实战笔记分解.docx

资源描述

oracledatabase11gplsql编程实战笔记分解.docx

《oracledatabase11gplsql编程实战笔记分解.docx》由会员分享，可在线阅读，更多相关《oracledatabase11gplsql编程实战笔记分解.docx（161页珍藏版）》请在冰点文库上搜索。

oracledatabase11gplsql编程实战笔记分解.docx

oracledatabase11gplsql编程实战笔记分解

Chap1

DML语句是select、insert、update、delete和merge

DDL语句是create、alter、drop、rename、truncate、comment

DCL语句是grant、revoke

TCL语句是commit、rollback和savepoint

sql16个基本命令——参考书《OCA认证考试指南（IZ0-051）》清华大学出版社

《oracledatabasesqllanguagereference11g》有非遵循格式字符串依赖于格式掩码

chap2

2.1.3关于语句中有多个单引号时处理：

1、

select'It''sabird,noplancan''tbe'aspharse

fromdual;此处两个单引号即为一个单引号

2、只能用q再加’（语句）’

selectq'（It'sabird,noplancan'tbe）'aspharse

fromdual;

均输出

PHARSE

----------------------------

It'sabird,noplancan'tbe

2.1.4

定义变量与申明变量的区别：

定义变量即为变量分配名称并指定数据类型；申明变量首先需要定义变量，然后为其赋值。

（赋值也称为初始化）

替代变量前面要加&前缀且若替代变量为字符型时要加两个单引号如’&a’

declare

lv_whomvarchar2（20）;/*lv-whom为申明变量，a为替代变量，a没有变量类型*/

begin

lv_whom:

='&a';

end;

或者

declare

lv_whomvarchar2（20）;

begin

lv_whom:

=&a;

end;但是要在输入框中字符加两个单引号

替代变量用define申明，且定义时不可以指定类型，默认为char型

Definex=emp;

Select*from&x;/*调用要用&，此处不加单引号，解析后即为emp表*/

definex=adasd;

select'&x'zfromdual;/*此处解析后变为select'adasd'zfromdual，此处必须要有单引号，使adasd作为直接变量，否则出错*/

所以替代变量是否加单引号要根据解析的值来确定

注意：

避免在声明块中将任何实参赋给局部变量（constant变量除外），且替代变量与绑定变量都不应该在声明块中赋值

定义为constant的变量，必须在申明块中申明，这意味着常量必须先定义，再给他赋一个不变的值

绑定变量（bind）用var或variable申明

使用冒号（:

）作为前缀

varanumber

begin

=22;

end;

上面也可以输出结果，下面也可以输出结果，但二者不能同时放一块

printa;/*此处a前面不能加冒号，print可用来输出所有类型的变量*/

另外也可通过exec直接给绑定变量赋值：

SQL>varxnumber/*必须要先定义再赋值，而替代变量定义时不需指定类型，直接赋值即可*/

SQL>varynumber

SQL>exec:

=1;:

=2;/*exec等价于begin…end*/

PL/SQLproceduresuccessfullycompleted

---------

也可以先定义好绑定变量，再将其赋给其他变量：

varanumber

begin

=22;

end;

现将其运行再

declare

lv_anumber;

begin

lv_a:

dbms_output.put_line（'hello，'||lv_a||'.'）;

end;

结果：

hello,22.

将上面的代码放入到d:

\q.sql中用@d:

\q.sql;调用即可，但是里面的代码要正确

2.1.5命名块

1、过程块

createorreplaceprocedureabc（aavarchar2）is

begin

dbms_output.put_line（'sdhl'||aa）;

end;

execabc（'dkjdhhdj'）;

可能出现

错误一：

【ORA-00955:

名称已由现有对象使用】

这个错误说的不仅仅是有可能你有存储过程使用了【abc】，还有可能是你有表名叫【abc】或者其他oracle对象叫【abc】。

说明：

其实由于你已经使用了【createorrepalaceprocedure】即使有存储过程叫【abc】也会被覆盖掉，你用下面的命令查询一下，有什么对象叫【abc】，删掉它或者换一个存储过程名字。

【SELECTOBJECT_NAME,OBJECT_TYPEFROMUSER_OBJECTSWHEREOBJECT_NAME='STUDENT2';】

错误二：

过程中的参数不能指定具体长度

2、函数块

错误一:

函数里面要有return，不是dbms_output.put_line（但实验之后二者均可以成功创建函数，但是调用时只有return的能没有错误的执行）

createorreplacefunctionabc（aavarchar2）

returnvarchar2is

begin

dbms_output.put_line（'sdhl'||aa）;

end;

改为

createorreplacefunctionabc（aavarchar2）returnvarchar2is

begin

return'sdhl'||aa;

end;

但函数不能像过程一样来执行，因为execute命令不允许管理函数返回值。

必须要用call或begindbms...end命令将返回值放入一个绑定变量（要先定义）中，在输出。

（必须要先执行函数，在执行下面语句，不能一起运行）

variableresultvarchar2（20）;

callabc（'akdhak'）into:

result;--call后面只能用绑定变量？

或者：

SQL>begin

2dbms_output.put_line（abc（'du'））;

3end;

sdhldu

此处若用print则绑定变量不能加冒号

只能为printresult;

但可以查询select:

resultfromdual;

（exec是sqlplus的命令，只能在sqlplus中使用。

call是sql命令，任何工具都可以使用）

2.1.6嵌套块

命名块还可以嵌套在其他命名块或匿名块中，但嵌套命名块并不是已经发布的，这意味着在调用一个命名块时，被调用的命名块可能还没有定义

declare

procedureais

begin

dbms_output.put_line（b||'hello!

'）;/*此处b为被调用函数，但之前没有被解析，所以运行会出错*/

enda;

functionbreturnvarchar2is

begin

return'hello!

endb;

begin

end;

注：

所有匿名块都会在程序都会在实际执行前进行分析，分析是一个编译过程。

分析过程将会识别标识符（保留字）、预定义标识符、引用标识符、用户定义变量、子例程或UDT。

命名块也是标识符，PL/SQL按照自顶向下的次序，将标识符读取到内存中。

上例中函数b因为在过程a的下面还没被解析，所以会出错。

使用“前向引用”可以修正这个问题，函数或过程的前向引用只需要函数或过程的署名，不需要同时包括署名和实现，这些署名在PL/SQL中叫做“占位程序（stub）”。

占位程序允许编译过程在实现命名块之前接受其标识符名称。

更正：

declare

procedurea;

functionbreturnvarchar2;

procedureais

begin

dbms_output.put_line（b||'hello!

'）;

enda;

functionbreturnvarchar2is

begin

return'hello!

endb;

begin

end;

2.2变量：

类型、赋值和运算符

2.2.1文本数据类型

用伪列来隐式地定义数据类型。

伪列如%type

Char（20）为一个定长的类型，不管其中变量的长度有没有达到20，最后显示长度均为20字符

Varchar2（20）则要根据实际情况来确定其长度

Clob（characterlargeobject字符大对象）同varchar2

2.2.2日期和时间戳类型

1、日期

有两种字符串字面值赋值支持到date类型的隐式转换

Lv_datedate:

=’22-mon-75’；

或者Lv_datedate:

=’22-mon-1975’；

除了上面两种，任何其他字符串字面值都要求使用to_date内置SQL函数来覆盖格式掩码

如lv_date_1date:

=to_date（‘19750430’,’YYYYMMDD’）;

2、间隔

间隔子类型允许将天的间隔表示秒，将年的间隔表示月份数

Intervaldaytosecond数据类型的默认值在两个date相减时能起作用，只要在执行减法之前将其转化为timestamp，因为to_timestamp函数保留了date的精度，而该精度低于timestamp

将天的间隔转换为秒的数据类型为intervalday/day（4或其他数）tosecond

declare

lv_intervalintervaldaytosecond;

lv_end_daydate:

=sysdate;---sysdate与date对应

lv_start_daydate:

='18-4月-2012';

begin

lv_interval:

=to_timestamp（lv_end_day）-to_timestamp（lv_start_day）;

dbms_output.put_line（lv_interval）;

end;

结果

-2014:

56:

54.000000

PL/SQLproceduresuccessfullycompleted

Date数据类型默认支持2位数字表示的天，timestamp的精度要求使用9位数字表示的天

或者

declare

lv_intervalintervalday（9）tosecond;

lv_end_daytimestamp:

=systimestamp;---systimestamp与timestamp对应

lv_start_daytimestamp:

='18-4月-2012';

begin

lv_interval:

=lv_end_day-lv_start_day;

dbms_output.put_line（lv_interval）;

end;

结果

-00000294302:

52:

41.860000

PL/SQLproceduresuccessfullycompleted

将年的间隔转化为月份数的数据类型为intervalyeartomonth

从一个日期中提取年

用to_char（extract（yearfromlv_end_day））

完整代码：

declare

lv_adate:

='20-4月-2009';

avarchar2（12）;

begin

=to_char（extract（yearfromlv_a））;

dbms_output.put_line（a）;

end;

结果为：

2009

3、时间戳（timestamp）

Timestamp数据类型精度要比date数据类型精度高

2．2.3数值类型

可以将number数据类型隐性转化为intger类型，可能会丢失一些小数点

Binary_float32位浮点数

Binary_double是一个64位浮点数

2.2.4复合变量类型

SQLUDT用于保存一个数据结构

2.3控制结构

1、If语句，ifelsifelse

2、case语句分为简单的case语句case…when…then…else…endcase（break隐式存在）与搜索型的case语句casewhen…then…else…endcase

简单case语句可使用char、nchar、varchar2数据类型，而搜索case语句可以使用任意布尔表达式，搜索case语句不局限于等值匹配

2.3.3、循环结构

Loop循环

退出要借助exit或exitwhen语句

Forloop循环：

分为范围循环和游标循环

插入：

关于游标（见E:

\sas\sql\各类知识要点\游标）

A、范围循环即foriin1..4loop

B、游标循环1、隐式foriin（select语句）loop

2、显式foriincur_v（游标）loop

其中第2种要比第一种要有更好的可读性（以后用这种）

C、wherecurrentof字句

while循环

while（...）loop

内可含continue/goto语句

Simple循环语句

即利用隐式游标属性来进行循环判断如ifSQL%FOUNDthen...else...endif;

2．4批量操作（见E:

\sas\sql\各类知识要点\游标）

批量处理是进行成批处理和大批量处理的默认选择

本章小结：

1、始终在执行块中进行赋值或初始化。

除非局部变量被视为常量

2、在实现任何局部块命名之前，始终未其定义前向引用规范，从而确保这些局部命名块能够互相调用

3、应尽可能使用表集合。

避免用varray，因为前者不受固定大小的限制。

Varray通常需要更改大小限制，这将花费时间和金钱

4、显式游标应尽可能使用形参

5、应尽可能使用SQL数据类型作为函数的返回值。

SQL数据类型不需要编写管道化表函数对其进行封装就可以在SQL中使用，并且数据类型总是可检测的，因为它是目录中的一个命名集合或结构

本章的问题

1、在程序块中至少包括一个null语句，否则会编译错误

2、PL/SQL中必须定义一个record结构，因为该结构是仅适用于PL/SQL环境的变量

3、for循环必须知道其范围ide上界，而游标集返回的行数设置了游标for循环的上边界

4、可以使用bulkcollectinto语句讲一个完整游标或游标的某些行选择到一个记录结构集合中。

可以使用limit关键字来限制行集

5、在循环内部可以使用forall语句，但该语句没有结束块语句，所以需要endforall或endloop或endforallloop语句来结束forall语句。

Chap3事物作用域

3.1数据库ACID测试

Atomicity原子性、consistency一致性、isolation隔离性、durability持久性

3.2多版本并发控制

MVCC（multiversionedconcurrencycontrol）使用数据库快照为用户提供数据库的永久内存副本

3.3

3.3.1保存点、提交和事务回滚

若有2个保存点，且回滚到了第一个保存点，则第二个保存点就不存在了

3.3.2提交

注意：

A、数据字典作强制隐式的提交

B、Commit命令中的注释将写入到dba_pc2_pending字典视图中

C、必须具有forcetransaction或forceanytransaction系统权限才能强制提交

D、Nowait选项不会验证对重做和归档日志文件的写入

E、Work选项将会在所有commit语句中有效

例1、标准commit

UPDATEhr.employees

SETsalary=salary*1.03

WHEREdepartment_idin（20,30,40）;

Commit

或将commit改为COMMITWORKWRITEIMMEDIATEWAIT;

例2、含有nowait和batch选项的commit

使用nowait选项将不会验证对重做和归档日志的写入

避免重写oracle的提交等待过程。

这样做可能会使自己的事物陷入危险

例3、强制commit语句并写入注释

INSERT

INTOhr.job_history

VALUES（100,'01-Jan-2000',trunc（sysdate）,'AD_PRES',90）;

COMMIT

COMMENT'In-doubttransactionforcedbyprocessxyzondate123';

Commitforce‘2.33.192’;

发出commitforce需要一个具有dba权限的账户。

单引号（’）内的信息代表可疑的事物id。

3.4DML锁定和隔离控制

为了防止不可重复读取或影子读取

a、设置事物级别为读取一致（在跟踪分布式事物时，设置事物语句命名将会非常有益）

settransactionname可更好地监控长时间的事务。

SETTRANSACTIONREADONLYNAME'DistributedtoNYC';

--添加到该设置事物语句的’distributedtoNYC’注释将会保存在dba_pc2_pending字典视图中（http:

//www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_10005.htm）

SELECTproduct_id

warehouse_id

quantity_on_hand

FROMoe.inventories@nyc_001

WHEREproduct_id=3246;

COMMIT;

b、锁定

用户具有锁定自己所拥有的全部表的权限，如果想要锁定另一个模式中的表，则必须具有lockanytable系统权限

B．1在游标内使用forupdate

DECLARE

CURSORc_employeesIS

SELECT*

FROMhr.employees

FORUPDATEWAIT10;--该行锁定c_employees游标中所引用的行。

其中wait10表示，若游标所引用的行已经存在锁定，则等待10秒，若前一次锁定持续时间大于10秒，则此次更新将会被取消，也可以用nowait关键字，表示存在锁定的情况下立即返回事物

BEGIN

FORr_employeesINc_employeesLOOP

UPDATEhr.employees

SETsalary=salary*1.025

WHERECURRENTOFC_employees;--该语句引用游标中的最新行，最后锁定会一直持续，直至发出commit或rollback命令

ENDLOOP;

END;

B．2发起locktable语句

LOCKTABLEhr.employeesINEXCLUSIVEMODENOWAIT;

UPDATEhr.employees

SETsalary=salary*1.025

WHEREdepartment_id=10;

COMMIT;

与forupdate语句不同，locktable命令将会锁定表中的每一行。

此外，短语inexclusivemode将会锁定除select语句之外的全部操作

有效的锁定模式有：

rowexclusive模式，限制最少的锁定级别，允许行共享，并防止用户锁定整个表或锁定到rowshare模式

rowshare模式，除了没有独占共享的限制之外，该模式等同于rowexclusive

share模式，允许select但是不允许其他更新

sharerowexclusive模式，等同于share模式，但是同时禁止用户锁定到share模式

exclusive模式限制最多的级别，阻止除select之外的所有DML操作

C、改善事物性能

利用批量DML操作的功能

3.5调用者和定义者权限

以程序所有者的身份来保留程序执行的全部权限：

在命名程序头使用authiddefiner关键字

例1发出定义者权限

CREATEORREPLACEFUNCTIONhr.quarterly_sales

（pi_employee_idinnumber

pi_quarterindate

）

AUTHIDDEFINER

...declarativecodegoeshere...

BEGIN

...dosomethinghereandreturn...

EXCEPTION

...handletheexceptionhereandreturn...

END;

例2.发出调用者权限

以程序调用者的身份来调用程序，用关键字authidcurrent_user

CREATEORREPLACEFUNCTIONhr.give_raise

（pi_employee_idinnumber）

AUTHIDCURRENT_USER

...declarativecodegoeshere...

BEGIN

...dosomethinghereandreturn...

EXCEPTION

...handletheexceptionhereandreturn...

END;

本章小结

1、确保自己有一个良好的备份。

仅仅激活像rman这样的备份程序是不够的

2、使用简洁而有意义的名称来命名保存点

3、强制可疑事务要求深入了解oracle系统更改号（systemchangenumber,SCN）和数据字典。

只有当事务无法再次尝试时才使用commitforce命令

4、使用settransactionname参数对分布式事务命名

测验小结

1、原子性意味着一个事务全部写入到永久存储中或任何部分都没有写入到永久存储中

2、一致性意味着一个并发多用户系统中为所有事务分配相同的服务cpu时间量和内存

3、隔离性意味着一个事务的任何部分在事务完成并提交之前都不可见

4、持久性意味着事务在完成之后写入到冗余磁盘阵列

5、Undo_retention参数（还不是很熟悉），设置语句在内存中运行的时间长度

Chap4错误管理

数

展开阅读全文