PLSQLl编程
plsql是Oracle在标准的sql语言上的扩展
特点：可以在数据库中定义变量，常量，还可以使用条件语句和判断语句以及异常等
PLSQL程序组成部分
PLSQL由 声明部分、执行部分、异常处理部分组成
DECLARE -- 声明部分
-- 在此声明pl/sql用到的变量
BEGIN -- 执行
-- 在此写执行语句
EXCEPTION
-- 执行异常
END;
简单的PLSQL块
DECLARE -- 声明并赋值 变量名 类型 := 值; ：= 表示赋值 = 表示条件判断
V_NUM NUMBER := 10; -- PLSQL在声明变量时在前面加v_ 代表变量
BEGIN
V_NUM := V_NUM + 1; -- 对V_NUM加1
DBMS_OUTPUT.put_line('NUM' || V_NUM); -- 对结果进行输出
END;
SELECT...INTO、%TYPE、 %ROWTYPE、常量
SELECT...INTO...
[1] 声明变量赋值为员工表中Smith的员工编号 SELECT ...INTO
SELECT...INTO可以将表中查询到的数据赋值给变量V_NUM
DECLARE
V_NUM NUMBER; -- 声明
BEGIN
SELECT EMPNO INTO V_NUM FROM EMP WHERE ENAME = 'SMITH';
DBMS_OUTPUT.put_line('NUM' || V_NUM);
END;
%TYPE
[2] 声明变量,变量的类型是scott用户下emp表empno字段的字段类型 %TYPE
DECLARE
V_NUM SCOTT.EMP.EMPNO%TYPE; -- 声明类型是SCOTT用户下的EMP表的EMPNO列对应的类型
BEGIN
SELECT EMPNO INTO V_NUM FROM EMP WHERE ENAME = 'SMITH';
DBMS_OUTPUT.put_line('NUM' || V_NUM);
END;
%ROWTYPE
[3] 取一行数据 %rowtype
DECLARE
V_EMPROW SCOTT.EMP%ROWTYPE; -- 拿到SCOTT用户下的EMP表的所有列数据的类型
BEGIN
SELECT * INTO V_EMPROW FROM EMP WHERE ENAME = 'SMITH';
DBMS_OUTPUT.put_line(V_EMPROW.ENAME || V_EMPROW.EMPNO);
END;
常量
DECLARE
-- 声明
C_PI CONSTANT NUMBER := 3.1415; -- CONSTANT: 常量，一旦设定，不能改变 命名规范：常量前面加C
V_R NUMBER := 20;
V_AREA NUMBER;
BEGIN
V_AREA := C_PI*V_R*V_R;
DBMS_OUTPUT.put_line('圆的面积：' || V_AREA);
END;
判断语句
IF判断
DECLARE
V_SCORE NUMBER;
BEGIN
V_SCORE := '&请输入成绩'; -- &表示输入
IF(V_SCORE >= 90) THEN
DBMS_OUTPUT.PUT_LINE('去玩游戏');
ELSIF (V_SCOE >= 80) THEN
DBMS_OUTPUT.PUT_LINE('去报补习班');
ELSIF (V_SCORE >= 70) THEN
DBMS_OUTPUT.PUT_LINE('去报两个补习班');
ELSE
DBMS_OUTPUT.PUT_LINE('去写作业');
END IF;
END;
SWITCH判断
DECLARE
V_SCORE NUMBER;
BEGIN
V_SCORE := '&请输入成绩';
CASE
WHEN V_SCORE > 90 THEN DBMS_OUTPUT.PUT_LINE('去玩游戏');
WHEN V_SCORE > 80 THEN DBMS_OUTPUT.PUT_LINE('去报补习班');
WHEN V_SCORE > 70 THEN DBMS_OUTPUT.PUT_LINE('去报两个补习班');
ELSE DBMS_OUTPUT.PUT_LINE('去写作业');
END CASE;
END;
循环结构
LOOP循环
-- 要求一个变量,每次循环减1,并输出结果 ,当变量小于3时,退出循环
DECLARE
V_COUNT NUMBER := 10;
BEGIN
LOOP -- 循环开始
V_COUNT := V_COUNT - 1; -- 减1
EXIT WHEN V_COUNT < 3; -- 当V_COUNT小于3时，退出循环
DBMS_OUTPUT.PUT_LINE(V_COUNT);
END LOOP; -- 结束循环
END; DECLARE
V_COUNT := 10;
BEGIN
LOOP
V_COUNT : V_COUNT - 1;
IF V_COUNT < 3 THEN
EXIT; -- 满足条件，退出循环
DBMS_OUTPUT.PUT_LINE(V_COUNT);
END LOOP;
END;
WHILE循环
-- 声明变量1,每次循环加1,并输出结果,当变量大于20的时候,退出循环
DECLARE
V_NUM NUMBER := 1; -- 声明变量并赋值
BEGIN
WHILE V_NUM < 20 LOOP -- 开始循环
DBMS_OUTPUT_PUT_LINE(V_NUM);
V_NUM := V_NUM + 1;
END LOOP; -- 结束循环
END;
FOR循环
-- 变量从0开始,变量等于10,每次循环+1,算循环的次数
DECLARE
V_NUM NUMBER := 10;
BEGIN
FOR I IN 0..10 LOOP -- 0..10表示0到10
V_NUM := V_NUM + 1;
DBMS_OUTPUT.PUT_LINE(V_NUM);
END LOOP;
END;
PLSQL之异常处理
使用EXCEPTION处理异常
DECLARE
V_TEMP NUMBER(4);
BEGIN
SELECT EMPNO INTO V_TEMP FROM EMP WHERE DEPNO = 100; -- 这里DEPNO等于10的列不只一条，而V_TEMP只能接收一条数据，所以会造成异常
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('返回的数据太多了');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有数据啊');
WHEN OTHERS THEN -- 使用OTHERS表示其他异常
DBMS_OUTPUT.PUT_LINE('其他异常');
END;
自定义异常
DECLARE
V_NUMBER1 NUMBER; -- 声明变量
V_NUMBER2 NUMBER;
V_RESULT NUMBER;
E_NONUMBER EXCEPTION; -- 声明异常 PRAGMA EXCEPTION_INIT (E_NONUMBER, -6502); -- 将错误的代码跟自定义的异常名称绑定，并注册到系统里面
BEGIN
V_NUMBER1 := '& 请输入被除数';
V_NUMBER2 := '& 请输入除数';
V_RESULT := V_NUMBER1 / V_NUMBER2;
DBMS_OUTPUT.PUT_LINE(V_RESULT);
EXCEPTION
WHEN E_NONUMBER THEN
DBMS_OUTPUT.PUT_LINE('你输入的不是数字');
END;
建立错误日志表
-- 创建表
CREATE TABLE ERROLOG( --错误日志
ERROCODE NUMBER, -- 出错的编码 (ORA-XXXX)
ERROMSG VARCHAR2 (1024), --出错的信息
ERRODATE DATE --出错的时间
) -- 记录错误日志
DECLARE
V_DEPTNO DEPT.DEPTNO%TYPE := 10; --声明一个变量v_deptno 是dept 表的deptno类型
ERROCODE NUMBER; --出错的编码
ERROMSG VARCHAR2(1024); --出错的信息 BEGIN
DELETE FROM DEPT WHERE DEPTNO = 90;
DELETE FROM DEPT WHERE DEPTNO = V_DEPTNO;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK; --回滚到之前的状态
ERROCODE := SQLCODE; --打印错误编码
ERROMSG := SQLERRM; --打印错误的信息
INSERT INTO ERROLOG VALUES (ERROCODE,ERROMSG,SYSDATE);
COMMIT;
END;