文章目录
一、条件语句
1 IF语句
- 语法
IF 条件 THEN
语句
ELSIF 条件 THEN
语句
ELSE
语句
END IF;
- 例子
-- 例子
指定部门编号
如果该部门存在且有人,删除该部门下所有员工,并打印:已整体辞退
如果部门存在但没有人,打印:该部门下没有人
如果该部门不存在,打印:部门不存在
DECLARE
V_DEPTNO NUMBER(2) :=&请指定部门;
V_DCT NUMBER;
V_ECT NUMBER;
BEGIN
SELECT COUNT(1) INTO V_DCT FROM DEPT WHERE DEPTNO = V_DEPTNO;
SELECT COUNT(1) INTO V_ECT FROM EMP WHERE DEPTNO = V_DEPTNO;
IF V_DCT=1 AND V_ECT>0 THEN
DELETE FROM EMP WHERE DEPTNO = V_DEPTNO;
DBMS_OUTPUT.PUT_LINE('已整体辞退');
ELSIF V_DCT=1 AND V_ECT=0 THEN
DBMS_OUTPUT.PUT_LINE(V_DEPTNO||'部门没有人');
ELSE
DBMS_OUTPUT.PUT_LINE(V_DEPTNO||'部门不存在');
END IF;
END;
2 CASE WHEN 语句
当第一个表达式为true则执行第一个语句,若第一个表达式为false则判断第二个表达式,如果第二个表达式为true执行第二个语句,若第二个表达式为false则判断第三个表达式……直到最后一个表达式判断仍为false,则执行ELSE中的语句。
- 语法
CASE WHEN 条件 THEN
语句
WHEN 条件 THEN
语句
......
ELSE 语句
END CASE;
- CASE WHEN 用于语句内部组成DML和SELECT语句 THEN 后面跟数据
DECLARE
V_EMPNO NUMBER(4) :=&指定工号;
BEGIN
UPDATE EMP SET SAL = CASE WHEN DEPTNO = 10 THEN SAL+1000
WHEN DEPTNO = 20 THEN SAL+2000
WHEN DEPTNO = 30 THEN SAL+3000
END
WHERE EMPNO = V_EMPNO;
END;
- CASE WHEN 用于流程控制,不组成语句本身
DECLARE
V_EMPNO NUMBER(4) :=&指定工号;
V_DEPTNO NUMBER(2);
BEGIN
SELECT DEPTNO INTO V_DEPTNO FROM EMP WHERE EMPNO = V_EMPNO;
CASE WHEN V_DEPTNO = 10 THEN
UPDATE EMP SET SAL = SAL+1000 WHERE EMPNO = V_EMPNO;
DBMS_OUTPUT.PUT_LINE(V_EMPNO||'工资1000');
WHEN V_DEPTNO = 20 THEN
UPDATE EMP SET SAL = SAL+2000 WHERE EMPNO = V_EMPNO;
DBMS_OUTPUT.PUT_LINE(V_EMPNO||'工资每加2000');
WHEN V_DEPTNO = 30 THEN
UPDATE EMP SET SAL = SAL+3000 WHERE EMPNO = V_EMPNO;
DBMS_OUTPUT.PUT_LINE(V_EMPNO||'工资加3000');
END CASE;
END;
注意:
条件语句中可以继续嵌套条件语句,但不建议嵌套太多层。
条件语句的最后和条件语句中的执行部分,最后都要按要求添加英文分号
二、循环语句
1 LOOP循环
循环体至少执行一次 (先执行一次,再进行判断)
- 语法
LOOP
语句
EXIT WHEN 结束条件;
END LOOP;
- 例子
-- 计算:S= 1+3+5+7+....+99 打印S值
DECLARE
N NUMBER :=1;
S NUMBER :=0;
BEGIN
LOOP
S := S + N;
N := N + 2;
DBMS_OUTPUT.PUT_LINE(N);
EXIT WHEN N > 99;
END LOOP;
DBMS_OUTPUT.PUT_LINE(S);
END;
2 WHILE循环
先进行条件判断,当条件为真才会执行循环体
- 语法
WHILE 条件 LOOP
语句
END LOOP;
- 例子
-- 计算:S= 1+3+5+7+....+99 打印S值
DECLARE
N NUMBER :=1;
S NUMBER :=0; -- 要赋予起始值 不要输出为空
BEGIN
WHILE N<=99 LOOP
S := S + N;
N := N + 2;
DBMS_OUTPUT.PUT_LINE(S);
END LOOP;
END;
3 FOR循环
- 语法
FOR VA_COUNTER IN [REVERSE] 下限 .. 上限 LOOP -- 边界值 小值在前 大值在后
语句
END LOOP;
-- VA_COUNTER是一个循环计数器 通常为整形 且不需要声明
-- REVERSE 从大到小
- 例子
-- 计算:S= 1+3+5+7+....+99 打印S值
DECLARE
S NUMBER :=0;
BEGIN
FOR N IN 1 .. 100 LOOP -- 循环体是连续的
IF MOD(N,2) = 1 THEN -- 是奇数加起来 不是奇数不管
S := S + N;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(S);
END;
4 GOTO语句
DECLARE
X NUMBER;
BEGIN
X := 1;
<<A>> -- 跳转标识符 GOTO到此标识符
DBMS_OUTPUT.PUT_LINE(X);
X := X + 1;
IF X < 10 THEN
GOTO A; -- 无条件的跳转到A
END IF;
END;
三、游标
游标是一种从表中检索数据并每次指向其中一条记录进行交互的机制。
游标打开之后,进入待读取的第一条数据,当前指向空。
游标一旦打开(OPEN),到游标关闭(CLOSE)前,游标面向的结果集是固定不变的。
1 游标的属性
%FOUND:布尔类型,如果SQL语句能影响到至少一行数据,则该属性为true,否则为false。[当前行,即游标当前所处的位置是否有数据]
%NOTFOUND:布尔类型,与FOUND相反。[当前行,游标当前所处的位置是否有数据]
%ISOPEN:布尔类型,当游标处于打开状态为true,否则为false。[判断游标是否打开,当游标关闭之后仍然可以使用该属性]
%ROWCOUNT:数值型属性,返回受SQL影响的数据的行数。[查询结果的行数,到当前行一共几条数据]
除了ISOPEN在游标关闭后仍然可以使用该属性,其他的均不能使用。
2 游标的分类
静态游标
显示游标
隐式游标
动态(REF)游标
1 显示游标
声明游标
打开游标
读取游标
关闭游标
- LOOP循环 游标(%NOTFOUND)
DECLARE
CURSOR A IS SELECT EMPNO,ENAME,JOB FROM EMP; -- 声明一个游标 表连接 子查询什么都行
B A%ROWTYPE; -- 效仿游标的类型 记录型
BEGIN
OPEN A; --打开游标
LOOP
FETCH A INTO B; -- 读取游标
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(B.EMPNO||' '||B.ENAME||' '||B.JOB);
END LOOP;
CLOSE A; -- 关闭游标
END;
- WHILE循环 游标(%FOUND)
-- WHILE循环
DECLARE
CURSOR A IS SELECT EMPNO,ENAME,JOB FROM EMP; -- 声明一个游标 表连接 子查询什么都行
B A%ROWTYPE; -- 效仿游标的类型 记录型
BEGIN
OPEN A; --打开游标
FETCH A INTO B; -- 读取游标 指到第一行数据
WHILE A%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(B.EMPNO||' '||B.ENAME||' '||B.JOB);
FETCH A INTO B; -- 读取游标 指到下一行数据
END LOOP;
CLOSE A; -- 关闭游标
END;
- FOR循环 游标 [省略打开 读取 关闭游标 只需要在最开始声明游标即可]
-- FOR 循环 省略打开 读取 关闭游标 只需要在最开始声明游标即可
DECLARE
CURSOR A IS SELECT EMPNO,ENAME,JOB FROM EMP; -- 声明一个游标 表连接 子查询什么都行
BEGIN
FOR C IN A LOOP -- 循环计数器是一个其他的东西 不是一个整数 边界直接是一个A
DBMS_OUTPUT.PUT_LINE(C.EMPNO||' '||C.ENAME||' '||C.JOB);
END LOOP;
END;
2 游标的参数
- 语法
CURSOR 游标名字(参数1 [IN] 类型 [:=参数值1],参数2 [IN] 类型) IS 查询语句 WHERE 条件=参数1 AND 条件=参数2; --定义游标
OPEN(参数值1,参数值2..); -- 打开游标
- 例子
DECLARE
CURSOR A(I_DEPTNO IN NUMBER :=10,I_SAL NUMBER) IS SELECT EMPNO,ENAME,JOB FROM EMP WHERE DEPTNO = I_DEPTNO AND SAL > I_SAL;
B A%ROWTYPE; -- 效仿游标A类型
BEGIN
OPEN A(10,1500);
LOOP
FETCH A INTO B;
DBMS_OUTPUT.PUT_LINE(B.EMPNO||' '||B.ENAME||' '||B.JOB);
EXIT WHEN A%NOTFOUND OR A%ROWCOUNT > 5;
END LOOP;
CLOSE A;
END;
- 传递实参的方法
DECLARE
CURSOR A(I_DEPTNO IN NUMBER) IS SELECT * FROM EMP WHERE DEPTNO = I_DEPTNO;
B A%ROWTYPE; -- 效仿游标A类型
BEGIN
OPEN A(10);
-------------------------------------------------------------------------------------------------------
DECLARE
CURSOR A IS SELECT * FROM EMP WHERE DEPTNO = &请输入一个部门编号;
B A%ROWTYPE; -- 效仿游标A类型
BEGIN
OPEN A;
-------------------------------------------------------------------------------------------------------
DECLARE
CURSOR A(I_DEPTNO IN NUMBER :=&请输入一个部门编号) IS SELECT * FROM EMP WHERE DEPTNO = I_DEPTNO;
B A%ROWTYPE; -- 效仿游标A类型
BEGIN
OPEN A;
-------------------------------------------------------------------------------------------------------
DECLARE
CURSOR A(I_DEPTNO IN NUMBER) IS SELECT * FROM EMP WHERE DEPTNO = I_DEPTNO;
B A%ROWTYPE; -- 效仿游标A类型
BEGIN
OPEN A(&请输入一个部门编号);
-------------------------------------------------------------------------------------------------------
DECLARE
CURSOR A IS SELECT * FROM EMP WHERE DEPTNO = V_DEPTNO;
V_DEPTNO NUMBER(2) :=&请输入一个部门编号;
B A%ROWTYPE; -- 效仿游标A类型
BEGIN
OPEN;
3 隐式游标
隐式游标主要是处理数据操纵语句的执行结果,有些情况下也可以处理SELECT语句的查询结果。
隐式游标也有着自己的属性,在使用隐式游标的属性时需要加上隐式游标的默认名称——SQL。
在PL/SQL编程中,隐式游标常用来判断数据的DML操作结果。 DDL
- 作用
BEGIN
DML语句;
INSERT INTO 日志表(涉及的数据量) VALUES (SQL%ROWCOUNT);
COMMIT;
END;
- 例子
-- 隐式游标的FOR循环
BEGIN
FOR 游标计数器 IN SQL语句 LOOP
输出语句;
END LOOP;
END;
-- 例子
BEGIN
FOR A IN (SELECT * FROM EMP) LOOP
DBMS_OUTPUT.PUT_LINE(A.EMPNO||' '||A.ENAME||' '||A.JOB);
END LOOP;
END;
BEGIN
UPDATE EMP SET SAL = SAL + 2000;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
END;
4 动态游标
- 语法
DECLARE
TYPE REF_CUR IS REF CURSOR;-- 声明动态游标
CUR_A REF_A;
BEGIN
END;
- 例子
-- 动态游标 后面 跟上动态SQL语句
DECLARE
TYPE REF_CUR IS REF CURSOR;
CUR_A REF_CUR;
V_SQL VARCHAR2(2000);
N VARCHAR2(1) :='&请输入1(员工信息)2(部门信息)';
A VARCHAR2(20);
BEGIN
IF N = '1' THEN
V_SQL := 'SELECT ENAME FROM EMP';
ELSIF N = '2' THEN
V_SQL := 'SELECT DNAME FROM DEPT';
END IF;
OPEN CUR_A FOR V_SQL;
LOOP
FETCH CUR_A INTO A;
EXIT WHEN CUR_A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(A);
END LOOP;
CLOSE CUR_A;
END;
四、异常处理
语法
BEGIN
执行语句
EXCEPTION
WHEN THEN
WHEN THEN
WHEN OTHERS THEN
END;
1 预定义异常
预定义异常在使用时完全不需要用户声明,由ORACLE自行引发自行判定。
DECLARE
V_ENAME VARCHAR2(10);
BEGIN
SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO = 1134;
DBMS_OUTPUT.PUT_LINE(V_ENAME);
SELECT ENAME INTO V_ENAME FROM EMP WHERE DEPTNO = 60;
DBMS_OUTPUT.PUT_LINE(V_ENAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN -- 异常的名字
DBMS_OUTPUT.PUT_LINE('这个员工不存在');
INSERT INTO ERR_INFO VALUES ('这个员工不存在',TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));
COMMIT;
RAISE; -- 抛出错误
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('值过多');
INSERT INTO ERR_INFO VALUES ('值过多',TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));
COMMIT;
RAISE; -- 抛出
WHEN OTHERS THEN
INSERT ERR_INFO VALUES ('程序异常',TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));
COMMIT;
END;
2 非预定义异常
- 语法
DECLARE
ERR_NAME EXCEPTION; -- 定义的错误的名字
PRAGMA EXCEPTION_INIT(ERR_NAME,ERR_CODE); -- 错误名字,错误编码(-00937)
BEGIN
执行语句;
EXCEPTION
WHEN ERR_NAME THEN
错误语句;
END;
- 例子
DECLARE
V_DEPTNO NUMBER;
V_SAL_SUM NUMBER;
ERROR_1 EXCEPTION;
PRAGMA EXCEPTION_INIT(ERROR_1,-00937);
BEGIN
SELECT DEPTNO,SUM(SAL) INTO V_DEPTNO,V_SAL_SUM FROM EMP;
EXCEPTION
WHEN ERROR_1 THEN
DBMS_OUTPUT.PUT_LINE('出错了');
END;
- 注意
如果非预定异常使用预定异常的名字,exception中使用非预定异常。
如果非预定异常使用预定异常的编号,优先选用预定异常的名字作为抛出异常。
3 自定义异常
RAISE常常用来引发错误,具体的用法分为以下两种:
RAISE err_name 转到异常处理部分;
RAISE_APPLICATION_ERROR(err_code,err_mess),引发弹窗报错。
err_code:自定义错误编号,限制在-20001到-20999之间
err_mess:自定义错误信息,字符型内容
DECLARE
V_ENAME VARCHAR2(10) :='&指定员工的姓名';
SMITH EXCEPTION; -- 定义一个异常名字
PRAGMA EXCEPTION_INIT(SMITH,-20001); -- 将错误的编码 异常名字关联起来
BEGIN
IF V_ENAME = 'SMITH' THEN
--RAISE SMITH; -- 弹窗报错 用户定义的异常错误未得到处理
RAISE_APPLICATION_ERROR(-20001,'SMITH是元老 不能走'); -- 弹窗报错 报错信息为:SMITH是元老 不能走
ELSE
DBMS_OUTPUT.PUT_LINE(V_ENAME);
END IF;
EXCEPTION
WHEN SMITH THEN
DBMS_OUTPUT.PUT_LINE('错误');
RAISE; -- 抛出错误
END;
- 捕获异常信息
DECLARE
V_DEPTNO NUMBER;
V_SAL_SUM NUMBER;
V_ERRM VARCHAR2(1000);
BEGIN
SELECT DEPTNO,SUM(SAL) INTO V_DEPTNO,V_SAL_SUM FROM EMP;
EXCEPTION
WHEN OTHERS THEN
V_ERRM :=SQLERRM; -- SQLERRM错误信息 直接可以使用
DBMS_OUTPUT.PUT_LINE(SQLERRM); -- 错误信息
DBMS_OUTPUT.PUT_LINE(SQLCODE); -- 错误编码 -937 最简形式
--INSERT INTO ERR_INFO VALUES (SQLERRM,TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));-- 报错 必须承接变量
INSERT INTO ERR_INFO VALUES (V_ERRM,TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'));
COMMIT;
END;
4 异常的作用范围
DECLARE
V_TEST CHAR(3);
BEGIN
<<INTNER_BLOCK>>
BEGIN
V_TEST :=1234;
DBMS_OUTPUT.PUT_LINE('过长');
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('内层捕获错误');
END INNER_BLOCK;
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('外层捕获错误');
END;
补充
1 表类型
1 语法
TYPE 表类型的名字 IS TABLE OF 表中字段的类型(只能是一个 可以是记录类型 VARCHAR2(必须指定精度)) INDEX BY BINARY_INTEGER/PLS_INTEGER/VARCHAR2(精度);
-- 声明一个表变量
变量的名字 表类型名字;
2 用法
表变量名字(index) --就可以通过下面的语法应用PLSQL表中的元素了
语法 | 返回类型 | 描述 |
---|---|---|
表变量名字.COUNT | NUMBER | 返回PLSQL表中元素个数 |
表变量名字.DELETE | N/A | 删除PLSQL表中所有元素 |
表变量名字.DELETE(i) | N/A | 删除表中索引号为i指定的元素 |
表变量名字.DELETE(i,j) | N/A | 删除表中索引号[i,j]之间的元素 |
表变量名字.EXISTS(i) | BOOLEAN | 如果PLSQL表中的索引号i存在,则返TRUE |
表变量名字.FIRST | BINARY_INTEGER | 返回表中元素索引号最小的索引号 |
表变量名字.LAST | BINARY_INTEGER | 返回表中元素索引号最大的索引号 |
表变量名字.NEXT(i) | BINARY_INTEGER | 返回表汇总索引号为i的后继一个元素的索引 |
表变量名字.PRIOR(i) | BINARY_INTEGER | 返回表汇总索引号为i的前一个元素的索引 |
3 例子
-- 输出一串字符串 按照顺序排序
DECLARE
N VARCHAR2(1000) := '&指定一串字符串' ;
TYPE TYP_REC IS RECORD(
LETTER VARCHAR2(256), --记录字母
SEQ PLS_INTEGER --记录字母出现次数
);
TYPE TAB_REC IS TABLE OF TYP_REC INDEX BY BINARY_INTEGER; -- 表类型
TABR TAB_REC; -- 表变量
BEGIN
FOR I IN 1 .. LENGTH(N) LOOP
TABR(ASCII(SUBSTR(N, I, 1))).LETTER := SUBSTR(N,I,1);
TABR(ASCII(SUBSTR(N, I, 1))).SEQ := NVL(TABR(ASCII(SUBSTR(N, I, 1))).SEQ,0) + 1;
END LOOP;
FOR I IN TABR.FIRST .. TABR.LAST LOOP
IF TABR.EXISTS(I) THEN -- 判断数据是否存在
FOR J IN 1 .. TABR(I).SEQ LOOP
DBMS_OUTPUT.PUT(TABR(I).LETTER||' ');
END LOOP;
END IF;
END LOOP;
DBMS_OUTPUT.NEW_LINE ;
END;
-- 输入N个数,按照从小到大排序
--表变量
DECLARE
TYPE INT_ARRARY_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
MY_ARRAY INT_ARRARY_TYPE; -- 表变量
N NUMBER := &请输入你要输入的数字的个数;
V_NUMBER_COUNT VARCHAR2(1000) :=TRIM('&请输入多个数字以空格隔开');
A VARCHAR2(10) ;
B NUMBER ; -- 统计前面总共字符串的长度
TMP NUMBER ; -- 暂存表变量中的数据
BEGIN
-- 去除两侧空格
FOR I IN 1 .. N LOOP
IF I = 1 THEN
A := SUBSTR(V_NUMBER_COUNT,1,INSTR(V_NUMBER_COUNT,' ',1)-1);
B := LENGTH(A)+1;
ELSIF I = N THEN
A := SUBSTR(V_NUMBER_COUNT,B+1);
ELSE
A := SUBSTR(V_NUMBER_COUNT,B+1,INSTR(V_NUMBER_COUNT,' ',B+1)-B-1); -- 位置- B - 1为截取的长度
B := B+LENGTH(A)+1;
END IF;
-- 向表变量中 添加数据
MY_ARRAY(I) := A;
END LOOP;
-- 排序之前的结果
DBMS_OUTPUT.PUT_LINE('排序之前的结果:');
FOR I IN 1 .. MY_ARRAY.COUNT LOOP
DBMS_OUTPUT.PUT(MY_ARRAY(I)|| ' ');
END LOOP;
DBMS_OUTPUT.NEW_LINE;
-- 冒泡排序
FOR I IN 1 .. N LOOP
FOR J IN 1 .. N-I LOOP
IF MY_ARRAY(J) > MY_ARRAY(J+1) THEN
TMP := MY_ARRAY(J);
MY_ARRAY(J) := MY_ARRAY(J+1);
MY_ARRAY(J+1) := TMP;
END IF;
END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE('排序后的结果为:');
FOR I IN 1 .. MY_ARRAY.COUNT LOOP
DBMS_OUTPUT.PUT(MY_ARRAY(I)||' ');
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END;
2 游标WHERE CURRENT OF
--Where Current Of语句允许你更新或者是删除最后由cursor取的记录
-- 经理加薪2000 其他加薪1000
DECLARE
CURSOR CUR_EMP IS SELECT * FROM EMP FOR UPDATE;
BEGIN
FOR V_EMP IN CUR_EMP LOOP
IF V_EMP.JOB = 'MANAGER' THEN
UPDATE EMP SET SAL = SAL + 2000 WHERE CURRENT OF CUR_EMP;
ELSE
UPDATE EMP SET SAL = SAL + 1000 WHERE CURRENT OF CUR_EMP;
END IF;
END LOOP;
END;
SELECT * FROM EMP;