1.什么是PL/SQL

  PL/SQL是结合了Oracle过程语言和结构化查询语言的一种扩展语言。是一种块结构的语言。

2.PL/SQL的组成?

  • 声明部分
  • 执行部分
  • 异常处理部分

3.PL/SQL的优点

  • 支持事务控制和SQL数据操作命令
  • 支持SQL的所有数据类型,并且在此基础上扩展了新的数据类型,也支持SQL的函数和运算符
  • PL/SQL可以存储在Oracle服务器中,提高程序的运行性能
  • 服务器上的PL/SQL程序可以使用权限进行控制
  • 良好的可移植性

 

/*1.PL/SQL结构演示*/

[DECLARE]
--声明部分,在此声明PL/SQL用到的变量、类型及游标,以及局部的存储过程和函数
BEGIN
--执行部分:过程及SQL语句,即程序的主要部分
[EXCEPTION]
--异常处理部分:错误处理
END;
--执行部分不可省略

 

/*2.PL/SQL变量与常量语法演示*/

变量名 数据类型[(范围大小)][:= 值]; --变量声明赋值语法

常量名 CONSTANT 数据类型 := 值; --常量声明赋值语法

--例:
DECLARE
v_ename VARCHAR2(20);
v_rate NUMBER(7,2);
c_rate_incr CONTRANT NUMBER(7,2) := 1.10;
BEGIN
--赋值方式一,通过SELECT INFO
SELECT ename,sal* c_rate_incr INTO v_ename, v_rate
FROM employee
WHERE empno = '7788';

--赋值方式二,通过赋值操作符“:=”
v_ename := 'SCOTT';

END;

 

/*3.PL/SQL控制语句演示*/
p77-p79:控制语法

--3.1条件控制
--3.1.1
BEGIN
IF false THEN
DBMS_OUTPUT.PUT_LINE('t142');
ELSE
DBMS_OUTPUT.PUT_LINE('AAAAAA');
END IF;
END;

--3.1.2
DECLARE
v_num NUMBER(2,0) := 10;

BEGIN
IF v_num>10 THEN
DBMS_OUTPUT.PUT_LINE('100');
ELSIF v_num=10 THEN
DBMS_OUTPUT.PUT_LINE('10');
ELSE
DBMS_OUTPUT.PUT_LINE('0');
END IF;

END;

--3.2.1循环控制-LOOP
DECLARE
v_num NUMBER(2,0) := 1;

BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(v_num);
v_num+:=1;
EXIT WHEN v_num=11;
END LOOP;
END;

--3.2.2循环控制-FOR
DECLARE
v_num NUMBER(2,0) := 1;
v_num2 NUMBER(2,0);
BEGIN
FOR v_num2 IN REVERSE 1 .. 5 LOOP
DBMS_OUTPUT.PUT_LINE(v_num2);
END LOOP;
END;

4.异常处理

  异常:在运行程序时出现的错误叫做异常

/*
===========================================================
| 预定义异常
============================================================
*/
create table employee as select * from scott.emp;

select * from employee;


--未进行异常处理
DECLARE
v_ename employee.ename%TYPE;
BEGIN
SELECT ename INTO v_ename
FROM employee
WHERE empno=1234;
dbms_output.put_line('雇员名:'||v_ename);
END;

--进行异常处理
DECLARE
v_ename employee.ename%TYPE;
BEGIN
SELECT ename INTO v_ename
FROM employee
WHERE empno=1234;
dbms_output.put_line('雇员名:'||v_ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('雇员号不正确');
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('查询只能返回单行');
WHEN OTHERS THEN
dbms_output.put_line('错误号:'||SQLCODE||'错误描述:'||SQLERRM);
END;

/*
===========================================================
| 查询编号为7788的雇员的福利补助(comm列)。
============================================================
*/
DECLARE
v_comm employee.comm%TYPE;
e_comm_is_null EXCEPTION; --定义异常类型变量
BEGIN
SELECT comm INTO v_comm FROM employee WHERE empno=7788;
IF v_comm IS NULL THEN
RAISE e_comm_is_null;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('雇员不存在!错误为:'||SQLCODE||SQLERRM);
WHEN e_comm_is_null THEN
dbms_output.put_line('该雇员无补助');
WHEN others THEN
dbms_output.put_line('出现其他异常');
END;

5.游标:

  用来处理使用select语句从数据库中检索到的多行记录的工具

  分类:

    • 显示游标:返回多条记录时,使用显示游标逐行读取

    • 隐式游标:PL/SQL自动为DML语句创建隐式游标,包含一条返回记录

  属性:

    • %found:用于检验游标是否成功,通常在FETCH语句前使用,当游标按照条件查询出一条记录时,返回true
    • %isopen:判断游标是否处于打开状态,视图打开一个已经打开或者已经关闭的游标,将会出现错误
    • %notfound :与%found的作用相反,当按照条件无法查询到记录时,返回true 
    • %rowcount :循环执行游标读取数据时,返回检索出的记录数据的行数

--显示游标使用步骤

declare
v_ename employee.ename%type;
v_sal employee.sal%type;
--声明游标
cursor cursor_emp is
select ename,sal from employee
for update of sal;
begin
--打开游标
open cursor_emp;
--提取游标
/*loop
fetch cursor_emp into v_ename,v_sal;
exit when cursor_emp%notfound;
dbms_output.put_line('雇员姓名:'||v_ename||' 雇员薪水:'||v_sal);
end loop;*/

loop
fetch cursor_emp into v_ename,v_sal;
exit when cursor_emp%notfound;
update employee set sal = sal+200
where current of cursor_emp;
end loop;

close cursor_emp;

end;

6.存储过程

  示例代码:

CREATE OR REPLACE PROCEDURE add_employee(

eno employee.empno%type, --输入参数,雇员编号
name employee.ename%type, --输入参数,雇员名称
salary employee.sal%type, --输入参数,雇员薪水
job employee.job%type DEFAULT 'CLERK', --输入参数,雇员工种默认'CLERK'
dno employee.deptno%type, --输入参数,雇员部门编号
on_Flag OUT number, --执行状态
os_Msg OUT VARCHAR2 --提示信息
)
IS
BEGIN
INSERT INTO employee (empno,ename,sal,job,deptno)VALUES (eno,name,salary,job, dno);
on_Flag:=1;
os_Msg:='添加成功';
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
on_Flag:=-1;
os_Msg:='该雇员已存在。';
WHEN OTHERS THEN
on_Flag:=SQLCODE;
os_Msg:=SQLERRM;
END;

DECLARE
on_Flag NUMBER;
os_Msg VARCHAR2(100);
BEGIN
--按位置传递参数
add_employee(2111,'MARY',2000,'MANAGER',10,on_Flag,os_Msg);
dbms_output.put_line(on_Flag||os_Msg);
END;