第六章ORACLE复习提纲

第六章PL/SQL基础

一、PL/SQL块

1、分类

●无名块：动态构造，只能执行一次。

●子程序：存储在数据库中的存储过程、函数及包等。当在数据库上建立好后

可以在其他程序中调用它们。

●触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。

2、无名PL/SQL块结构

由声明部分、执行部分、异常处理部分三个部分组成，执行部分是必须的。

DECLARE

/* 声明部分: 在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数*/

BEGIN

/* 执行部分: 过程及SQL 语句, 即程序的主要部分*/ EXCEPTION

/* 执行异常部分: 错误处理*/

END;

3、PL/SQL写法

语句可以写在多行上

语法单位可以分为分隔符、标识符（包括保留字）、文字常量、注释

分隔符是有特殊意义的简单或复合符号，如：

+ - * / = @(访问远程数据库的符号) ;（语句结束符）<> != || --（单行

注释）/* */（注释开始和结束）:=

标识符

文字常量：

字符和日期文字常量必须使用单引号括起

数字常量可以是简单数值或科学计数法表示的数值

XeY表示X*10Y

二、使用变量

1、标识符

标识符名不能超过30个字符；

第一个字符必须为字母；

不分大小写；

不能用‘-’(减号);

不能是SQL保留字。

注意：一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果。

变量名不要与数据库中的列重名

本地变量或形参的名字优先于表名

数据库列名优先于本地变量的名字

2、数据类型

变量分为标量类型、复合类型、LOB类型和引用类型

1)标量数据类型

保存单个值

没有内部组件

CHAR[(maximum_length)]最大32767字节

V ARCHAR2(maximum_length)最大32767字节

LONG 32760 字节

LONG RAW 32760 字节

RAW(长度) 最大32760字节

NUMBER[(precision,scale)

BINARY_INTEGER 符号数

PLS_INTEGER符号数，占用内存更少，运算速度快

BOOLEAN，只能保存TURE、FALSE和NULL

2)BLOB、CLOB、BFILE，保存了大对象的定位器（一个指向大对象存贮位置

的指针）

3)复合数据类型

分为记录型和集合型

记录型将逻辑上相关的数据项组织成一个整体

集合型包括index by table(索引表)、nested table嵌套表、Varray变长数组，集合类型将数据集合作为一个对象来对待

?记录型

必须包括一个或多个由标量型、记录型、索引表型字段，类似于C语言中的structure,使用记录型可以方便的从表中取出一行数据进行处理

记录中可以有任意多个字段

记录中的字段可以有初始值并被定义为NOT NULL

说明为NOT NULL的变量或字段必须有初始值或默认值

V_A number not null (:=123|default 3);

可以在任何PL/SQL块的声明部分定义记录类型

记录类型可以嵌套

定义记录类型：

TYPE 记录类型名IS RECORD (字段1[,字段2]…);

字段定义：字段名{类型|变量名%TYPE|表.列%TYPE|表名%ROWTYPE} [[NOT NULL]{:=|DEFAULT}expr]

声明记录型变量：变量名记录类型名;

类型定义：定义了类型的名字，结构，但不分配内存

变量声明：定义了变量的类型，同时分配内存

举例：

●定义一个记录类型，保存职工的编号，last name和岗位

将101号职工的信息保存在记录变量中，使用dbms_output包输出信息

DECLARE

TYPE emp_record_type IS RECORD

(employee_id NUMBER(6)NOT NULL:=100,

last_name https://www.360docs.net/doc/a214389324.html,st_name%TYPE,

job_id employees.job_id%TYPE);

emp_record emp_record_type;

begin

select employee_id,last_name,job_id into emp_record from employees

where employee_id=101;

dbms_output.put_line(emp_record.employee_id);

dbms_output.put_line(emp_https://www.360docs.net/doc/a214389324.html,st_name);

dbms_output.put_line(emp_record.job_id);

end;

练习：

写一个PL/SQL块，输出国家信息，要求如下：

a.按照COUNTRIES表的结构声明一个PL/SQL记录型变量

b.用DEFINE命令定义一个替代变量，向PL/SQL块传递country_id

c.用DBMS_OUTPUT.PUT_LINE按下面格式输出国家信息：

国家编码：国家名称：地区编码：<2>

d.用下面的国家编码执行PL/SQL块，并检查输出的结果CA,DE,UK,US. Define v_country_id=CA

declare

v_country countries%rowtype;

begin

select * into v_country from countries where country_id='&v_country_id';

dbms_output.put_line('国家编码：('||v_country.country_id||')国家名称：('||v_country.country_name||')地区编码：('||v_country.region_id||')');

end;

索引表

TYPE 索引表类型名IS TABLE OF

{column_type|variable%TYPE|table.column%TYPE}[NOT

NULL]|table.%ROWTYPE

[INDEX BY BINARY_INTEGER|varchar2];

identifier type_name;

索引表的大小不受限制

索引表不能在声明时初始化

索引表的数据是与index by的键值内容关联的，index by是键值，通过键值直接找到数据，键值可以是整数或字符串，索引表中的数据按键值排序

举例：数据是标量型

每三人一行输出lastname,每个名字占25列，左对齐

declare

type tab_name_type is table of https://www.360docs.net/doc/a214389324.html,st_name%type index by binary_integer;

tab_name tab_name_type;

J number:=1;

begin

for I in 100..104 loop

select last_name into tab_name(i) from employees where employee_id=i; end loop;

for I in tab_name.first..tab_https://www.360docs.net/doc/a214389324.html,st loop

if mod(j,3)=0 then

DBMS_OUTPUT.PUT_LINE(rpad(tab_name(i),20,' '));

else

DBMS_OUTPUT.PUT(rpad(tab_name(i),20,' '));

end if;

J:=j+1;

end loop;

DBMS_OUTPUT.NEW_LINE;

end;

索引表的方法：

NEXT(n)返回索引值n的数据后面一个数据的索引值，如果n是最后一个数据的索引值则返回空

TRIM删除索引表中最后一个数据

trim(n)删除索引表中最后n个数据

DELETE(n)删除索引值为n的数据；DELETE删除全部数据；DELETE(m,n)

删除索引值在m和n之间的数据

EXISTS(n):如果索引值n的数据可用，返回真

COUNT返回索引表中数据个数

FIRST and LAST返回索引表第一个数据和最后一个数据的索引值

PRIOR(n)返回第n个数据前面一个数据的索引值，如果n是第一个数据的索引值则返回空

举例：数据是记录型

●每三人一行输出employee_id和lastname,每个人占25列，左对齐

declare

TYPE emp_table_type IS TABLE OF employees%ROWTYPE INDEX BY

BINARY_INTEGER;

tab_name emp_table_type;

begin

for I in 100..104 loop

select * into tab_name(i) from employees where employee_id=i;

end loop;

for I in tab_name.first..tab_https://www.360docs.net/doc/a214389324.html,st loop

if mod(I,3)=0 then

DBMS_OUTPUT.PUT_LINE(rpad(tab_name(i).employee_id||tab_name(

i).last_name,25,' '));

else

DBMS_OUTPUT.PUT(rpad(tab_name(i).employee_id||tab_name(i).last_

name,25,' '));

end if;

end loop;

DBMS_OUTPUT.NEW_LINE;

end;

练习：

●使用索引表从Departments表中提取部门名，将代码存在p5_q2.sql，

要求如下：

声明一个名字为my_dept_table的索引表，用于暂存部门名

使用循环从部门表中将部门号为10，20，40，50，70，80，100的部

门的名字写入一索引表

使用一个新的循环，输出部门名

DECLARE

TYPE dept_table_type IS TABLE OF departments.department_name%TYPE INDEX BY BINARY_INTEGER;

my_dept_table dept_table_type;

v_adder NUMBER := 0;

BEGIN

FOR i IN 1 .. 7 LOOP

IF MOD(i, 2) <> 0 THEN

v_adder := trunc(i / 2) * 10;

END IF;

SELECT department_name

INTO my_dept_table(i * 10 + v_adder)

FROM departments

WHERE department_id = i * 10 + v_adder;

END LOOP;

v_adder := my_dept_table.FIRST;

WHILE v_adder IS NOT NULL LOOP

dbms_output.put_line(my_dept_table(v_adder));

v_adder := my_dept_table.NEXT(v_adder);

END LOOP;

END;

/

4)字符串必须用单引号括起，如果字符串中包含单引号，用两个连续的’’表示，

如：Qutoavar varchar2(100):='this''s a sample';

5)使用%TYPE声明变量

●根据表中列的类型声明

变量名列名%TYPE:=初始值

●根据已经声明的变量的类型声明

变量名已声明变量名%TYPE:=初始值

注意：列的Not Null约束不会应用到使用%TYPE声明的变量

●使用%ROWTYPE可以根据现有的数据库表、游标声明记录型变量

变量中的字段名与声明时所依据的数据库表中的字段名相同

declare

rec_emp employees%rowtype;

begin

rec_emp.first_name:='aaaa';

dbms_output.put_line(rec_emp.first_name);

end;

注意使用记录型变量中字段的方法

3、三种变量

1)在Declare部分声明的变量

在Declare部分声明变量和初始化

在执行部分给变量赋值

通过参数将变量值传递给其他PL/SQL块

通过输出变量的内容查看结果

变量在每次进入程序块时初始化，默认为Null

标识符[CONSTANT] 数据类型[NOT NULL][:=初始值|DEFAULT expr];

不能在一行上声明多个变量

赋值运算符:=

2)绑定变量

在sqlplus中可以使用替代变量或绑定变量与PL/sql交换数据，再使用input 和print命令输入输出替代变量的内容

绑定变量是一个在宿主环境中声明的变量

在sqlplus中使用variable命令声明绑定变量

在PL/SQL中通过在变量名前加":"引用宿主环境中的绑定变量

在Java和其他语言中都提供了相应的方法向PL/SQL传递绑定变量

在sqlplus中声明绑定变量的方法：variable 变量名类型

显示内容：print 变量名

例如：从表中读取数据存放到变量中

variable g_n number

begin

select salary*12*(1+nvl(commission_pct,0)) into :g_n

from employees where employee_id=144;

end;

/

print g_n

使用DBMS_OUTPUT.PUT_LINE显示变量：

Oracle提供的包

在PL/sql中显示数据

必须要将sqlplus的Serveroutput设置为ON

设置Serveroutput=on以后，DBMS_OUTPUT.put_line可以直接在屏幕上显示结果，这实际上是sqlplus提供的一种功能,PL/SQL本身没有输入输出的能力。

练习：

声明2个绑定变量v_char和v_num，写一个PL/SQL块，为绑定变量赋值，v_char=计算结果是42，v_num=v_char的最后两个字符

提示：substr(串,起始位置,长度)

Variable v_char varchar2(20)

Variable v_num number

Declare

Begin

:v_char:=’计算结果是42’;

:v_num:=substr(:v_char,-2,2);

End;

/

Print v_char

Print v_num

Set serveroutput on

Variable v_char varchar2(20)

Variable v_num number

Declare

Begin

:v_char:=’计算结果是42’;

:v_num:=substr(:v_char,-2,2);

Dbms_output.put_line(:v_char);

Dbms_output.put_line(:v_num);

End;

/

Print v_char

Print v_num

3)替代变量

练习：

写一个PL/SQL块，接受替代变量p_num1和p_num2的值，在屏幕上输出p_num1/p_num2+p_num2的值。

Define p_num1=100

Define p_num2=200

Begin

Dbms_output.put_line(&p_num1/&p_num2+&p_num2);

End;

/

●写一个PL/SQL块，计算全年补贴，全年工资使用替代变量p_sal提供，

补贴率用p_bonus提供，如：

Define p_sal=36000

define p_bonus=10 表示补贴率为10%

则输出结果为3600

Define p_sal=36000

Define p_bonus=10

Begin

Dbms_output.put_line(&p_sal*&p_bonus/100);

End;

三、操作符

与SQL相同的：

逻辑运算AND OR NOT

算术运算+ - * /

连接||

关系运算> >= = <= < <> is null is not null between like in

用括号可以改变运算顺序

比SQL多了幂运算**

四、程序的嵌套

可以在执行部分和异常处理部分使用嵌套块

嵌套的块被作为一个语句处理

变量的范围：

●在一个程序块中声明的变量是他所在块的本地变量，是该程序块所有子

块的全局变量

●同一个变量不能在同一个块中声明两次

●内部块中的变量可以与外部块中的变量重名，此时需要使用限定符引用

外部变量

●外部块不能使用内部块中的变量

举例：

Define old_date

<> --给外部块起一个名字

declare

birthdate date:=&old_date;

begin

<> --给内部块起名

declare

birthdate date:=sysdate;

begin

outer.birthdate:=birthdate-10;

dbms_output.put_line(birthdate);

dbms_output.put_line(outer.birthdate);

end;

dbms_output.put_line(birthdate);

end;

五、与ORACLE服务器交互

PL/sql可以通过DML(select/update/insert/delete)语句与服务器交互使用Select从数据库提取数据

使用Update/insert/delete修改数据库中的数据

可以使用commit/rollback/savepoint控制事务

使用隐性游标属性判断DML的执行结果

不能在PL/sql中直接使用DDL语句，如：

create table/view、alter table/drop table view/

不能使用grant和revoke语句

1、使用select语句

SELECT select_list

INTO{variable_name[,variable_name]...|record_name}

FROM table

[WHERE condition];

select_list是列的列表，可以是表达式

variable_name是保存结果的标量类型

record_name是保存结果的记录类型

表，条件

在每个SQL语句后面加";"

当Select嵌入在PL/sql中使用时，必须有into子句

查询必须只返回一行数据，不返回数据或返回数据多于一行会产生异常，分别是NO_DATA_FOUND和TOO_MANY_ROWS

举例：

●求Sales部门的部门号、部门名、办公地点，并使用dbms_output输出为标

签形式如下：

+----------------------+

|部门号：xxxx |

|部门名：yyyy |

|办公地点：nnnn |

+-----------------------+

declare

v_deptno char(30);

v_deptname char(30);

v_location_id char(30);

begin

select '部门号:'||department_id,'办公地点:'||location_id,'部门名:'||department_name into

v_deptno,v_location_id,v_deptname

from departments

where department_name='Sales';

dbms_output.put_line('+'||lpad('-',30,'-')||'+');

dbms_output.put_line('|'||v_deptno||'|');

dbms_output.put_line('|'||v_deptname||'|');

dbms_output.put_line('|'||v_location_id||'|');

dbms_output.put_line('+'||lpad('-',30,'-')||'+');

end;

●提取雇员的参加工作日期和工资

Declare

v_hire_date employees.hire_date%TYPE;

v_salary employees.salary%TYPE;

begin

SELECT hire_date,salary INTO v_hire_date,v_salary

FROM employees

WHERE employee_id=100;

dbms_output.put_line(v_hire_date||','||v_salary);

end;

●用替代变量输入部门号，显示部门的工资总额

SET SERVEROUTPUT ON

DECLARE

v_sum_sal NUMBER(10,2);

v_deptno NUMBER NOT NULL:=&bv_deptno;

BEGIN

SELECT SUM(salary)--group function INTO v_sum_sal

FROM employees

WHERE department_id=v_deptno;

DBMS_OUTPUT.PUT_LINE(’The sum salary is’||TO_CHAR(v_sum_sal)); END;

2、使用insert语句

举例：

●使用insert向employees表增加一个职工

BEGIN

INSERT INTO employees

(employee_id,first_name,last_name,email,hire_date,job_id,salary) V ALUES

(employees_seq.NEXTV AL,’Ruth’,’Cores’,’RCORES’,sysdate,’

AD_ASST’,4000);

END;

3、使用update语句

举例：

●增加所有仓库保管(ST_CLERK)的工资

DECLARE

v_sal_increase employees.salary%TYPE:=800;

BEGIN

UPDATE employees SET salary=salary+v_sal_increase

WHERE job_id=’ST_CLERK’;

END;

4、使用delete语句

举例：

●从my_emp表中删除部门10的职工

DECLARE

v_deptno employees.department_id%TYPE:=10;

BEGIN

DELETE FROM employees WHERE department_id=v_deptno;

END;

注意：与select语句不同，insert/update/delete如果未影响任何行，不会产生异常。

练习：

●写一个pl/sql块，查询部门表中最大的部门号并存到绑定变量中，使用print

语句输出变量的内容，将pl/sql块存到p3q1.sql。

Variable v_deptno number

Begin

Select max(department_id) into:v_deptno from departments;

End;

Print v_deptno

Save d:\p3ql.sql

●修改上一个块，将提取的最大部门号加10做为新部门号，存到绑定变量

中，Define一个替代变量，向pl/sql传递值部门值"Education",办公地点未定，向部门表增加一个部门。使用返回的绑定变量查询新增加的部门

Variable v_deptno number

Variable v_depno number

Variable v_depname varchar2(20)

Define v_deptname=’Education’

Begin

Select max(department_id)+10 into:v_deptno from departments;

Insert into departments(department_id, department_name)

values(:v_deptno,’& v_deptname’);

commit;

select department_id,department_name into :v_depno,:v_depname

from departments where department_id=:v_deptno;

dbms_output.put_line(:v_depno||','||:v_depname);

End;

●define一个替代变量，赋值为新的办公地点号1700，用前面返回在绑定变

量中的部门号修改该部门的办公地点，使用绑定变量查询新修改后的部门define v_location_id=1700

variable v_loca_id number

begin

update departments set location_id=&v_location_id

where department_id=:v_depno;

commit;

select department_id,department_name,location_id

into :v_depno,:v_depname,:v_loca_id

from departments where department_id=:v_depno;

dbms_output.put_line(:v_depno||','||:v_depname||:v_loca_id);

end;

●修改pl/sql块，删除绑定变量中指定的部门

begin

delete from departments where department_id=:v_depno;

commit;

end;

●注意每个练习都要提交修改的结果

六、游标

游标是SQL语句解析和执行的内存区，分为显式游标和隐式游标。显式游标是由程序员声明的游标，可以任意取名

1、隐式游标

SQL游标是隐式游标，Oracle Server使用隐式游标来解析和执行SQL语句，隐式游标不能任意取名，不需要声明。

SQL游标的四个属性：

●SQL%ROWCOUNT 最近执行的SQL语句影响的行数

●SQL%FOUND 布尔值，如果最近执行的SQL语句至少影响了一行则返回

真

●SQL%NOTFOUND 布尔值，如果最近执行的SQL语句未影响任何数据，

则返回真

●SQL%ISOPEN 总是返回FALSE，因为PL/SQL在SQL语句执行完成后立

刻关闭隐式游标

举例：删除176号雇员，并用绑定变量输出删除的行数

V ARIABLE rows_deleted V ARCHAR2(30)

DECLARE

v_employee_id employees.employee_id%TYPE:=576;

BEGIN

DELETE FROM employees

WHERE employee_id=v_employee_id;

:rows_deleted:=(SQL%ROWCOUNT||'row deleted.');

END;

/

PRINT rows_deleted

2、显示游标

显式游标允许我们用一行一行的方式处理多行查询结果集中的数据

使用游标的步骤：

声明游标—打开游标—提取当前行的数据到内存变量并处理数据—关闭游标游标是Oracle Server内存中保存结果集的一个区域，游标有一个指向结果集中当前行的指针

声明游标的语法：

Cursor 游标名is select语句;

Select语句可以排序，但不能有Into子句

在declare部分声明游标

使用显示游标：

?打开游标

open 游标名;

打开游标的同时执行SQL语句

SQL语句的结果集为空时不产生异常

使用游标属性测试每次Fetch的结果

打开游标后，游标指针指向第一行的前面

?从游标中提取数据

Fetch 游标名into 变量1,变量2,……,变量n|记录变量

变量的数量和类型或记录中字段的数量和类型必须与声明游标时的SELECT语句中列的数量和类型相同

使用Fetch后必须使用游标属性判断游标的状态

Fetch后游标指针后移一行

?关闭游标

close 游标名

关闭游标会释放Server中为游标分配的内存

游标关闭后不能再使用fetch

?检测游标状态

游标名%ISOPEN 当游标状态为打开时为真

游标名%NOTFOUND 如果最近的fetch没有返回行则为真

游标名%FOUND 如果最近的fetch返回了行则为真

游标名%ROWCOUNT 返回到目前为止游标累计返回的行数举例：

SET SERVEROUTPUT ON

DECLARE

v_empno employees.employee_id%TYPE;

v_ename https://www.360docs.net/doc/a214389324.html,st_name%TYPE;

CURSOR emp_cursor IS

SELECT employee_id,last_name FROM employees;

BEGIN

OPEN emp_cursor;

FOR i IN 1..10 LOOP

FETCH emp_cursor INTO v_empno,v_ename;

DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_empno)||' '||v_ename); END LOOP;

close emp_cursor;

END;

SET SERVEROUTPUT ON

DECLARE

v_empno employees.employee_id%TYPE;

v_ename https://www.360docs.net/doc/a214389324.html,st_name%TYPE;

CURSOR emp_cursor IS

SELECT employee_id,last_name FROM employees;

BEGIN

OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO v_empno,v_ename;

exit when emp_cursor%notfound;

DBMS_OUTPUT.PUT_LINE(TO_CHAR(v_empno)||' '||v_ename);

END LOOP;

DBMS_OUTPUT.PUT_LINE('共读取'||emp_cursor%rowcount||'行');

close emp_cursor;

END;

根据游标声明记录：

DECLARE

CURSOR emp_cursor IS

SELECT employee_id,last_name FROM employees;

emp_record emp_cursor%ROWTYPE; --游标名%rowtype可以声明一个记录变量，记录变量的结构与游标的select语句列的数量和类型相同

BEGIN

OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO emp_record;

……

end loop;

end;

游标FOR循环：

语法

FOR 记录变量名IN 游标名LOOP

statements;

END LOOP;

游标For循环是处理显式游标数据的便捷方式

记录变量是隐式声明的，不要在declare部分声明

游标在进入循环时自动打开，在退出循环时自动关闭

游标在每次循环开始自动执行Fetch操作，并把数据存到记录变量中