Java相关课程系列笔记之三PLSQL学习笔记.docx

Java相关课程系列笔记之三PLSQL学习笔记.docx

《Java相关课程系列笔记之三PLSQL学习笔记.docx》由会员分享，可在线阅读，更多相关《Java相关课程系列笔记之三PLSQL学习笔记.docx（76页珍藏版）》请在冰豆网上搜索。

Java相关课程系列笔记之三PLSQL学习笔记

PL/SQL学习笔记

Java相关课程系列笔记之三

笔记内容说明

PL/SQL（薛海璐老师主讲，占笔记内容100%）；

目录

一、PL/SQL简介1

1.1什么是PL/SQL1

1.2PL/SQL程序结构1

1.3PL/SQL运行过程1

1.4注释2

二、变量与数据类型3

2.1数据类型3

2.2标量类型3

2.3变量声明3

三、流程控制语句5

3.1条件语句5

3.2循环语句6

四、PL/SQL中的SQL9

4.1PL/SQL中的SQL分类9

4.2DML（insert，update，delete）和TCL（commit，rollback）9

4.3DDL9

五、PL/SQL中的select11

5.1select语句的实现11

5.2record类型11

5.3%rowtype12

5.4record变量的引用12

5.5cursor的概念13

5.6cursor的分类13

5.7显式cursor的处理13

5.8显式cursor的属性14

5.9隐式cursor的属性14

5.10cursor的声明14

5.11opencursor14

5.12fetchcursor15

5.13结果集提取的控制15

5.14closecursor15

六、集合19

6.1什么是collection19

6.2什么是关联数组19

6.3Associativearrays的定义19

6.4声明Associativearrays类型和变量19

6.5关联数组的操作20

6.6Associativearrays的方法20

6.7关联数组的遍历21

6.8Associativearrays的异常22

6.9批量绑定22

七、异常24

7.1Oralce错误24

7.2Oralce错误处理机制24

7.3异常的类型24

7.4PL/SQL中的异常24

7.5异常捕获24

7.6异常的捕获规则25

7.7Oralce预定义错误的捕获25

7.8非Oracle预定义异常26

7.9用户自定义异常26

7.10异常处理总结27

7.11sqlcode和sqlerrm27

7.12异常的传播28

八、子程序29

8.1子程序29

8.2有名子程序29

8.3有名子程序的分类29

8.4有名子程序的优点29

九、过程procedure30

9.1语法30

9.2创建存储过程30

9.3形参和实参30

9.4形参的种类31

9.5调用存储过程31

9.6存储过程中的参数31

9.7对实际参数的要求32

9.8形式参数的限制32

9.9带参数的过程调用32

9.10使用缺省参数32

9.11存储过程中的DDL语句33

9.12变量33

9.13PL/SQL中的SQL分类34

9.14再一次来看SQL语句的处理过程36

9.15软分析和硬分析36

9.16对过程procedure的基本操作36

9.17案例36

十、函数function38

10.1语法38

10.2创建函数38

10.3调用函数38

10.4对函数function的基本操作39

10.5过程和函数的比较39

10.6匿名块中的过程和函数声明39

10.7案例39

十一、包package41

11.1什么是package41

11.2包的组成41

11.3包的优点41

11.4package声明的语法41

11.5packagebody声明的语法42

11.6编译包和包体42

11.7案例42

十二、触发器trigger44

12.1面临问题44

12.2DML触发器的组成44

12.3DML触发器的类型44

12.4DML触发器的触发顺序44

12.5DML行级触发器44

12.6:

OLD和:

NEW44

12.7触发器的重新编译46

12.8触发器的状态46

十三、其他注意事项47

13.1PL/SQL的特点47

13.2写PL/SQL的好处47

13.3命名建议47

13.4搞清楚如下内容47

13.5保证所有对象的状态都是valid47

13.6declare中都可声明什么47

13.7数据库对象47

1、PL/SQL简介

1.1什么是PL/SQL

PL/SQL（ProceduralLanguage/SQL）是Oracle在标准SQL的基础上增加了过程化处理，把DML和select语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作，实现复杂的功能或者计算的程序语言。

扩展：

变量和类型、控制结构、过程与函数。

◆注意事项：

vjava中是写方法，把复杂的业务逻辑写入方法中，再调用方法。

vPL/SQL是写过程、函数，把复杂的业务逻辑写入过程、函数中，再调用它们。

1.2PL/SQL程序结构

PL/SQL块：

包含三部分，声明部分：

declare；执行部分：

begin；异常处理：

exception

eg1：

语法

declare

v_AccountIDnumber（5）:

=1001;

v_RealNamevarchar（20）;

begin

selectreal_nameinfov_RealNamefromaccountwhereid=v_AccountID;

exception

whenon_data_foundthen

insertinfoFee_Log（desrc）values（'Account1001decenotexit!

'）

commin;

end;--（不需要理解代码的具体含义！

）

◆注意事项：

最简写的方式为beginend;

eg2：

打印HelloWorld

begin

dbms_output.put_line（'HelloWorld'）;

end;

◆注意事项：

vdbms_output是系统提供的包package，包含多个过程、函数。

其中的过程put_line实现的是输出功能，只有一个参数，只能为“字符类型”（日期和数值也可，系统自动转换！

布尔类型不行！

但java中可以），用于接收需输出的字符串。

在sql工作表子窗口中可以调用存储过程。

v想要在屏幕上输出需要写：

setserveroutputon（在begin上面写）。

v如何调用过程：

begin包名.过程名（）;所有过程都是没有返回值的，即java中的void。

1.3PL/SQL运行过程

如下图所示：

1.4注释

增加可阅读性，使程序更容易理解。

编译时将被忽略。

1）单行注释：

由两个连字符“--”开始，到行尾都是注释。

2）多行注释：

由“/*”开头，由“*/”结尾。

2、

变量与数据类型

2.1数据类型

标量类型：

数字型、字符型、日期型、布尔型；可以直接用的。

复合类型：

record、associativearray、nestedtable、varray；需要自己去定义的。

2.2标量类型

1）数字类型：

①number②number的子类型dec（38）、float（38）、real（18）…

③binary_integer（只能在PL/SQL中用），按10进制赋值，但存的时候会换成2进制存。

优势是计算快。

2）字符类型：

①varchar2、varchar（长度：

1.~32767）②string（只能在PL/SQL中用，长度：

1.~32767）③char（长度：

1~32767）④long

3）日期类型：

date

4）布尔类型：

boolean

①用于存储逻辑值true,false,null（java中只有true,false）。

②不能向数据库中插入boolean类型的数据。

③不能将列值保存到boolean变量中。

④只能对boolean变量执行逻辑操作。

2.3变量声明

1）语法：

var_nametype[constant][notnull][:

=value];

◆注意事项：

PL/SQL规定没有初始化的变量为null。

2）直接定义类型

declare

v_n1number:

=1;--赋初值要有“冒号”，PL/SQL中赋值和等号是区分开的

v_c1varchar2（10）;

v_d1date:

=sysdate;--sysdate是函数，有返回值

begin

◆注意事项：

vPL/SQL中没返回值的叫过程；有返回值的叫函数，且必须有指向（要么打印出，要么赋值给变量）。

所以，过程和函数的调用是不一样的。

vjava中的方法一种没有返回值，一种是有返回值；有返回值的如果没返回，则会丢弃。

但PL/SQL不行！

3）%type方式：

变量具有与数据库的表中的某列或其他变量相同的类型。

eg1：

三种声明方式

declarev_RealNamevarchar2（20）;

declarev_RealNameaccount.real_name%type;

declarev_TempVarnumber（7,3）notnull:

=12.3;

v_AnotherVarv_TempVar%type:

=12.3;

eg2：

定义变量，打印系统时间，并输出它们的值

setserveroutputon

declare

v_d1date:

=sysdate;

v_c1varchar2（20）;

begin

v_c1:

=to_char（v_d1,'yyyymmddhh24:

mi:

ss'）;

dbms_output.put_line（'Currentdateis'||v_c1）;

--put_line只有一个参数，所以必须拼接！

end;

4）复合类型

record类型、集合类型。

详细内容见后面的第五、六章！

3、

流程控制语句

条件语句：

if、case；循环语句：

loop、while、for

3.1条件语句

1）if语句

方式一：

ifthen

statement;

endif;//最简写的方式

方式二：

ifthen

statement;

else

statement1;

endif;

方式三：

ifboolean_exprthen

statement;

elsifboolean_exprthen

statement1;

else

statement2;

endif：

◆注意事项：

elseif在PL/SQL中的写法是elsif！

eg：

输出布尔类型的变量的值

declare

v_b1boolean:

=false;

begin

ifv_b1then

dbms_output.put_line（'true'）;

elsifv_b1isnullthen

dbms_output.put_line（'null'）;

else

dbms_output.put_line（'false'）;

endif;

end;--注意：

put_line中的参数只能为字符类型，但日期和数值会隐式转换，布尔类型不行！

2）case语句

casewhenthen

whenthen

else

end;

3.2循环语句

1）loop循环（无条件进入）

语法：

loop

statement1;

statement2;

exitwhen

endloop;

◆注意事项：

vexitwhen子句是必须的，否则循环将无法停止。

vwhen后面直接写条件，没有尖括号或圆括号。

eg1：

循环输出数字1～10

declarev_indexbinary_integer:

=1;

begin

loop

dbms_output.put_line（v_index）;

v_index:

=v_index+1;

exitwhenv_index>10;

endloop;

end;

eg2：

循环插入10条记录（静态）

declarev_indexbinary_integer:

=1;

begin

loop

insertintotest_changvalues（v_index）;

v_index:

=v_index+1;

exitwhenv_index>10;

endloop;

end;

eg3：

循环输出语句insertintotest_changvalues（'i'），i为字符。

declarev_indexbinary_integer:

=1;

begin

loop

dbms_output.put_line（'insertintotest_changvalues（'''||v_index||'''）'）;

v_index:

=v_index+1;

exitwhenv_index>10;

endloop;

end;

2）while循环

语法：

whileloop

statement1;statement2;

endloop;

①循环语句执行的顺序是先判断的真假，如果为true则循环执行，否则退出循环。

②在while循环语句中仍然可以使用exit或exitwhen子句。

◆注意事项：

while后面直接写条件，没有尖括号或圆括号。

eg1：

循环输出数字1～10

declarev_indexbinary_integer:

=1;

begin

whilev_index<=10loop

dbms_output.put_line（v_index）;

v_index:

=v_index+1;

endloop;

end;

eg2：

循环插入10条记录（动态）

declarev_indexbinary_integer:

=1;

begin

whilev_index<=10loop

executeimmediate'insertintotest_changvalues（'||v_index||'）';

v_index:

=v_index+1;

endloop;

end;

3）for循环

语法：

for循环计数器in下限..上限loop

statement1;

statement2;

endloop;

①循环计数器是一个变量，这个变量不需要声明（和java不一样）。

它的作用域仅是在循环中。

②每循环一次，循环变量自动加1；使用关键字reverse，循环变量自动减1。

③可以使用exit或者exitwhen子句退出循环。

◆注意事项：

v跟在in、inreverse后面的数字必须是从小到大的顺序，但不一定是整数，可以是能够转换成整数的变量或表达式。

v不能用null作上限或下限下标！

eg1：

循环输出数字1～10

begin

foriin1..10loop

dbms_output.put_line（i）;

endloop;

end;

eg2：

使用inreverse循环输出数字1～10

begin

foriinreverse1..10loop

dbms_output.put_line（i）;

endloop;

end;

eg3：

循环插入10条记录（静态）

begin

foriin1..10loop

insertintotest_changvalues（i）;

endloop;

end;

4、

PL/SQL中的SQL

4.1PL/SQL中的SQL分类

1）静态SQL

在PL/SQL块中使用的SQL语句在编译时是明确的，执行的是确定对象，即SQL语句是在PL/SQL编译阶段编译的。

效率高。

能用静态就不用动态！

2）动态SQL

在PL/SQL块编译时SQL语句是不确定的，如根据用户输入的参数的不同而执行不同的操作。

编译程序对动态语句部分不进行处理，只是在程序运行时动态的创建语句、对语句进行语法分析并执行该语句。

效率低。

4.2DML（insert，update，delete）和TCL（commit，rollback）

1）它们可以直接在PL/SQL中使用标准的SQL语句。

2）语法：

begin

insertintohost（id）values（'10.0.0.11'）;

commit;

end;

4.3DDL

1）不能原封不动的像DML和TCL那样直接写。

2）所有的DDL语句要在PL/SQL中操作，必须用如下的语法形式（转成字符串），即本地动态SQL是使用executeimmediate语句来实现的。

eg1：

可执行

begin

executeimmediate'createtabletest_chang（c1number）';

end;--本地动态SQL执行DDL语句

eg2：

不可执行

begin

executeimmediate'createtabletest_chang（c1number）';

insertintotestvalues

（1）;

commit;

end;--报错！

是编译错误，说test_chang不存在！

说明表还没有创建，只是在编译期编译。

eg3：

可执行

begin

executeimmediate'createtabletest_chang（c1number）';

executeimmediate'insertintotest_changvalues

（1）';

commit;

end;--编译，执行都通过！

使用动态SQL

◆注意事项：

在一段PL/SQL程序中，先create再insert，必须用executeimmediate（静态sql和动态sql）

eg4：

用循环向test_chang表中插入10条记录。

（从1到10）

begin

executeimmediate'createtabletest_chang（c1number）';

foriin1..10loop

executeimmediate'insertintotest_changvalues（'||i||'）';

endloop;

commit;

end;

5、

PL/SQL中的select

5.1select语句的实现

根据select语句返回的记录数，将select语句的实现分为两类：

1）当且仅当只返回“一条”记录：

用select…into…语句实现（将结果放入到变量中）。

2）返回“0条或多条”记录：

用cursor实现。

◆注意事项：

selectinto确认select只返回一条记录，才不报错，否则加异常处理（没有返回记录或返回记录太多）。

eg：

提供客户ID，打印客户姓名和年龄

declare

v_realnameaccount.real_name%type;

v_agenumber（3）;

begin

selectreal_name,round（（sysdate-birthdate）/365）//减出的是天数

intov_realname,v_age

fromaccount

whereid=1005;--换成where1=2报错：

异常，无数据；换成where1=1报错：

返回记录太多

dbms_output.put_line（'姓名：

'||v_realname||'年龄：

'||v_age）;

end;

3）select...into后的查询结果集种类

①若查询结果是单行单列，into子句后用标量类型（标准类型），与select子句后的目标数据类型一致即可。

②若查询结果是单行多列，into子句后的变量个数、顺序、每个变量的数据类型，应该与select子句后的目标数据相匹配；也可以用记录类型的变量。

5.2record类型

记录类型，处理单行多列数据。

eg1：

语法

declare

typet_cost_recidrecord（base_costcost.base_cost%type,--t_cost_rec是记录类型

base_durationcost.base_duration%type,--base_cost是成员

unix_costcost.unit_cost%type）;

v_costt_cost_rec;--v_cost是记录类型的变量

v_cost_1t_cost_rec;

eg2：

打印每个客户的名字，年龄，身份证

declare

typet_account_recisrecord（real_nameaccount.real_name%type,

agenumber（3）,idcard_nochar（18））;

v_accountt_account_rec;

begin

selectreal_name,round（（sysdate-birthdate）/365）,idcard_nointov_account

fromaccount

whereid=1005;

dbms_output.put_line（'姓名：

'||v_account.real_name）;

dbms_output.put_line（'年龄：

'||v_account.age）;

dbms_output.put_line（'身份证：

'||v_account.idcard_no）;

end;--t_account_rec是记录类型，real_name是成员，v_account是记录类型的变量。

5.3%rowtype

用表结构或视图结构定义变量

当使用%rowtype定义记录变量时，record成员的名称和类型与表或视图的列名称和类型完全相同。

eg：

语法

declarev_costcost%rowtype;

5.4record变量的引用

1）记录类型变量的属性引用方法是“.”引用，即变量名.属性名。

eg1：

语法

declare

v_cost.base_cost:

=5.9;

v_cost.base_duration:

=20;

v_cost.unit_cost:

=0.4;

v_cost_1:

=v_cost;

selectbase_cost,base_duration,unit_costintov_cost_1fromcostwhereid=2