Oracle PL/SQL（变量-条件控制-循环控制-游标-异常）

一、

SQL语句：

DML-数据操作语言【insert update delete】

DQL-数据查询语言【select】

DDL-数据定义语言【create drop truncate】

DCL-数据控制语言【grant revoke】

TCL-事务控制语言【commit rollback】

数据库命令：DESC、SHOWUSER、CALL

PL/SQL过程化语言/结构化查询语言。

二、变量

【变量的声明】

变量名 【CONSTANT】 类型 【NOT NULL】 【：=value】

--变量的命名规则：建议以v_开头，尽快可以让人望文生义

【变量的赋值】

--1、声明时直接赋值
--2、在程序执行部分begin中赋值
--(1)使用:=
--(2)使用select into 方式
--3、type 和 rowtype

示例如下：

DECLARE
 v_name emp.ename%TYPE := 'jiazi'; --类型和表中对应字段类型一直保持一致，可以通过表名.列名%TYPE来声明类型；
 v_emp_rec emp%ROWTYPE; --数据集类型,b表名%ROWTYPE，但是只能接受一行数据不能多行，多行需用后面讲到的游标；
 v_gender VARCHAR2(10) NOT NULL :='女'; --非空，必须赋值
 v_no CONSTANT NUMBER :=1; --常量 必须赋值,并且不能被更改值
 v_date DATE；
BEGIN
 SELECT hiredate INTO v_date FROM emp WHERE ename='SCOTT';
 dbms_output.put_line('hello'||v_no||'班的'||v_name||',性别是：'||v_gender||',出生日期是：'||v_date);
 v_name :='jack';

---- jack的性别和emp表中scott性别一样，scott性别是根据empno奇男偶女决定；
/*SELECT DECODE(MOD(empno,2),0,'女',1,'男')
INTO v_gender
FROM emp
WHERE ename='SCOTT';*/

--dbms_output.put_line('hello'||v_no||'班的'||v_name||',性别是：'||v_gender);
SELECT * INTO v_emp_rec FROM emp WHERE ename='SCOTT';
dbms_output.put_line('v_emp_rec变量集的值：'|| v_emp_rec.empno);
END;

三、执行顺序（程序结构）

1、顺序结构

2、条件分支结构IF

例1：给员工加薪水，规则如下：

如果全员平均工资<1000,那么涨薪20%；

10000-2000,15%

2000-3000，10%

其他情况 5%。

DECLARE
v_avgsal NUMBER(8,2);--用于存放平均工资
v_num NUMBER(8);--用于存放涨薪数值
BEGIN
SELECT AVG(sal) INTO v_avgsal FROM emp;
IF v_avgsal<=1000 THEN
v_num:=20;
ELSIF v_avgsal<=2000 THEN
v_num:=15;
ELSIF v_avgsal <=3000 THEN
v_num:=10;
ELSE
v_num:=5;
END IF;
dbms_output.put_line('涨薪'||v_num||'%');
END;

3、循环结构 LOOP WHILE FOR

（1）Loop 直接进入循环，需要有退出条件

LOOP

执行语句；

EXIT WHEN 布尔表达式；

END LOOP;

（2）While 有条件进入循环，条件不满足，退出循环

WHILE 条件 LOOP

执行语句；

END LOOP;

（3）For 直接界定循环的最低和最高值，达到条件后退出

FOR 计数器 IN [REVERSE] 最低值.. 最高值 LOOP

 执行语句；

END LOOP;

plsql循环控制

例：循环打印1到10
--LOOP方式：

DECLARE
v_num NUMBER(8) := 1;
BEGIN
LOOP
dbms_output.put_line(v_num);
v_num := v_num+1;
EXIT WHEN v_num>10;
END LOOP;
END;

--while方式

DECLARE
v_num NUMBER(8) := 1;
BEGIN
WHILE v_num <=10 LOOP
dbms_output.put_line(v_num);
v_num := v_num+1;
END LOOP;
END;
--For方式

BEGIN
FOR v_num IN 1..10 LOOP
dbms_output.put_line(v_num);
END LOOP;
END;

四、游标Cursor

---先写一个题引出：打印20部门的员工的编号、姓名、工资

DECLARE
  v_emp_rec emp%ROWTYPE;
BEGIN
  SELECT * INTO v_emp_rec FROM emp WHERE deptno=20;
  dbms_output.put_line(v_emp_rec.emp||' / ' ||v_emp_rec.ename);
END;
---打印报错值过多，因为一个变量最多只能接收一行数据；
--于是可以用游标来指代一个select查询结果集，通过对游标操作按行提取数据；

【游标语法】
1、定义
CURSOR 游标名 IS SELECT 语句；
2、打开游标
OPEN 游标名；
3、提取游标数据
FETCH 游标名 INTO 变量；
4、处理数据
5、关闭游标
CLOSE 游标名；
开头实例正确写法：
---1、LOOP方式
DECLARE
emp_rec emp%ROWTYPE;
CURSOR cur_emp IS
SELECT * FROM emp WHERE deptno = 20;
BEGIN
OPEN cur_emp;
LOOP
FETCH cur_emp
INTO emp_rec;
EXIT WHEN cur_emp%NOTFOUND;
dbms_output.put_line(emp_rec.ename || ' 薪资：' || emp_rec.sal);
END LOOP;
CLOSE cur_emp;
END;
---2、while方式

DECLARE
emp_rec emp%ROWTYPE;
CURSOR cur_emp IS
SELECT * FROM emp WHERE deptno=20;
BEGIN
OPEN cur_emp;
FETCH cur_emp INTO emp_rec;
WHILE cur_emp%FOUND LOOP
dbms_output.put_line(emp_rec.ename || ' 薪资：' || emp_rec.sal);
FETCH cur_emp INTO emp_rec;
END LOOP;
CLOSE cur_emp;
END;

---3、for方式
DECLARE
emp_rec emp%ROWTYPE;
CURSOR cur_emp IS
SELECT * FROM emp WHERE deptno=20;
v_cnt NUMBER(8);
BEGIN
SELECT COUNT(*) INTO v_cnt FROM emp WHERE deptno=20;
OPEN cur_emp;
FOR v_in IN 1.. v_cnt LOOP
FETCH cur_emp INTO emp_rec;
dbms_output.put_line(emp_rec.ename || ' 薪资：' || emp_rec.sal);
END LOOP;
CLOSE cur_emp;
END;

--游标for循环（Oracle特有的游标写法，可以简化游标的操作，不需要open\fetch操作，重点记住）

DECLARE
CURSOR cur_emp IS
SELECT * FROM emp WHERE deptno=20;
BEGIN
FOR v_emp_rec IN cur_emp LOOP
dbms_output.put_line(v_emp_rec.ename || ' 薪资：' || v_emp_rec.sal);
END LOOP;
END;

---游标属性
----1、游标名%rowcount 返回游标fetch成功的数据条数，如果游标没有fetch，那么值为0；
----2、游标名%notfound 返回最近一次读取是否失败
----3、游标名%found 返回最近一次读取是否成功

--隐形游标，系定义的游标，没有明确的声明和操作过程，
--一般我们在insert\update\dalete数据时系统会自动创建游标；

BEGIN
INSERT INTO dept_bak SELECT * FROM dept;
dbms_output.put_line(SQL%ROWCOUNT);
END;

--作业：使用游标，删除部门表中名字相同的记录，但要保留最后一次添加的部门（部门编号最大的）

首先插入名字相同但部门号小的数据：
INSERT INTO dept_bak VALUES(8,'ACCOUNTING','A');
INSERT INTO dept_bak VALUES(12,'RESEARCH','B');
INSERT INTO dept_bak VALUES(15,'RESEARCH','C');
INSERT INTO dept_bak VALUES(25,'OPERATIONS','D');

--思路：按dname分组，筛选出count(*)大于1的，
--找出每组max(deptno),删除名字为dname但deptno不等于max（deptno）的行数据；

查询SQL:SELECT dname,MAX(deptno) AS maxdno
FROM dept_bak
GROUP BY dname
HAVING COUNT(*)>1;

测试成功后开始：
DECLARE
CURSOR cur_dept IS SELECT dname,MAX(deptno) AS maxdno
FROM dept_bak
GROUP BY dname
HAVING COUNT(*)>1;
BEGIN
FOR v_dept_rec IN cur_dept LOOP
dbms_output.put_line(v_dept_rec.dname||' ,maxno: '||v_dept_rec.maxdno);
DELETE FROM dept_bak WHERE dname=v_dept_rec.dname AND deptno!=v_dept_rec.maxdno;
END LOOP;
END;

测试：SELECT * FROM dept_bak;

五、异常

语法：
DECLARE
BEGIN
EXCEPTION
WHEN exception类型 THEN ...
WHEN exception类型 THEN ...
WHEN OTHERS THEN ...
END;

/*常见异常：
NO_DATA_FOUND select ino 没有找到数据
TOO_MANY_ROWS select into 返回多行
ZERO_DIVIED 被零除
INVALID_NUMBER 转换数字失败
*/

DECLARE
v_num NUMBER:=1;
v_result NUMBER;
BEGIN
v_result :=10/v_num;
SELECT empno INTO v_result FROM emp;
dbms_output.put_line(v_result);
EXCEPTION
WHEN zero_divide THEN dbms_output.put_line('被零除错误');
WHEN TOO_MANY_ROWS THEN dbms_output.put_line('返回多行');
WHEN OTHERS THEN dbms_output.put_line('有其它异常');
END;