PL/SQL 编程

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;