Oracle PL/SQL 编程基础

来源：互联网发布：最大子数组动态规划编辑：程序博客网时间：2024/05/22 16:47

oracle中的数据类型：number,char,nchar,varchar2(varchar),nvarchar2,LOB,long
1、向控制台输出内容：dbms_output.put_line();dbms_output.put();
2、如果要在sql控制看到输出必须设置控制台有效：set serveroutput on;
3、变量定义：paramName Type;
4、赋值语句：select into和:=
5、逻辑控制语句
（1）分支if...elsif....else...end if; case.....when...end case;
（2）循环loop....end loop; while....; for.....
6、Oracle中的异常:为了pl/sql编写的程序中可能出现的错误进行提前处理（预处理）
结构：
exception
    when expType then
      handle exception
    when expType then
      handle exception
    when expType then
      handle exception
pl/sql中主要的数据类型：int,char,nchar,varchar2,integer,boolean.....
(不是所有的pl/sql中的数据类型在定义数据列的时候都能用的)

pl/sql编程：允许代码块,允许逻辑判断（分支，循环），允许数据操作，允许通过游标等操作数据

--例一：在控制台输出自己的名字

--DBMS_OUTPUT.PUT_LINE：换行输出

--DBMS_OUTPUT.PUT:不换行输出

-- || 连接符号

DECLARE
MYNAME NVARCHAR2(20) := 'XXXX';
BEGIN
DBMS_OUTPUT.PUT_LINE('我的名字是：' || MYNAME);
END;

DECLARE
--myName nvarchar2(20) := '名字';
BEGIN
DBMS_OUTPUT.PUT_LINE('我的名字是：XXXX');
END;

--例二：输入两个数并且求除法，并且处理异常

-- := 赋值符号

--ZERO_DIVIDE : 对应ORA-01476 错误、如果除数为 0 、触发该异常

--CASE_NOT_FOUND : 对应ORA-06592 错误、在CASE 语句中、如果在when子句中没有包含必须的条件分支、又无else子句、就会隐含触发该异常

--CURSOR_ALREADY_OPEN ：对应ORA-06511错误、当打开已经打开的游标时、就会隐含触发该异常

--no_data_found : 对应ORA-01403错误、当执行select into 未返回行、或者引用了索引表未初始化时、会隐含触发该异常

--too_many_rows : 对应ORA-01422错误、当执行select into 子句时、如果返回超过一行就会触发该异常

DECLARE
A NUMBER(6, 0) := &A;
B NUMBER(6, 0) := &B;
C NUMBER(6, 0);
BEGIN
C := A / B;
DBMS_OUTPUT.PUT_LINE(C);
EXCEPTION
WHEN

THEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('除数不能为零！');

END;

--PL/SQL IF 用法
DECLARE
V_NUM NUMBER(2, 0) := &V_NUM;
BEGIN
IF V_NUM = 10 THEN
    DBMS_OUTPUT.PUT_LINE('你输入的是: ' || V_NUM);
ELSIF V_NUM < 10 THEN
    DBMS_OUTPUT.PUT_LINE('你输入的数小于10,你输入的是:' || V_NUM);
ELSE
    DBMS_OUTPUT.PUT_LINE('你输入的数大于10,你输入的是:' || V_NUM);
END IF;(必须以 end if 结尾 )(if 条件 then)

END;

--PL/SQL case 用法
DECLARE
V_NUM NUMBER(2, 0) := &V_NUM;
BEGIN
CASE V_NUM
    WHEN 10 THEN
      DBMS_OUTPUT.PUT_LINE('case1,你输入的是：' || V_NUM);
    WHEN 20 THEN
      DBMS_OUTPUT.PUT_LINE('case2,你输入的是：' || V_NUM);
    WHEN 30 THEN
      DBMS_OUTPUT.PUT_LINE('case3,你输入的是：' || V_NUM);
    ELSE
      DBMS_OUTPUT.PUT_LINE('case4,你输入的是：' || V_NUM);
END CASE;(必须结尾 end case)
END;

--例子三：根据输入员工编号查询姓名和工资

DECLARE
MYNAME SCOTT.EMP.ENAME%TYPE;
MYSAL SCOTT.EMP.SAL%TYPE;
MYENO SCOTT.EMP.EMPNO%TYPE := &ENO;
BEGIN
SELECT ENAME, SAL INTO MYNAME, MYSAL FROM SCOTT.EMP WHERE EMPNO = MYENO;
DBMS_OUTPUT.PUT_LINE(MYNAME || '的工资是' || MYSAL);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('请输入正确的员工编号！');
END;

--例子四：根据输入的员工编号判断员工的工资如果在2500以上加2000，否则加1000

DECLARE
ENO    SCOTT.EMP.EMPNO%TYPE := &ENO;
ESAL   SCOTT.EMP.SAL%TYPE;
ADDSAL SCOTT.EMP.SAL%TYPE;
BEGIN
SELECT SAL INTO ESAL FROM SCOTT.EMP WHERE EMPNO = ENO;
IF ESAL <= 2000 THEN
    ADDSAL := 1000;
ELSIF ESAL <= 3000 THEN
    ADDSAL := 2000;
ELSE
    ADDSAL := 5000;
END IF;
UPDATE SCOTT.EMP SET SAL = SAL + ADDSAL WHERE EMPNO = ENO;
COMMIT;
END;

DECLARE
ENO    SCOTT.EMP.EMPNO%TYPE := &ENO;
ESAL   SCOTT.EMP.SAL%TYPE;
ADDSAL SCOTT.EMP.SAL%TYPE;
BEGIN
SELECT SAL INTO ESAL FROM SCOTT.EMP WHERE EMPNO = ENO;
CASE
    WHEN ESAL <= 2000 THEN
      ADDSAL := 1000;
    WHEN ESAL <= 3000 THEN
      ADDSAL := 2000;
    ELSE
      ADDSAL := 5000;
END CASE;
UPDATE SCOTT.EMP SET SAL = SAL + ADDSAL WHERE EMPNO = ENO;
COMMIT;
END;

--例子五：根据输入部门编号判断部门如果是10给每一个加1000的佣金，如果20加2000，如果是30加3000，如果40该部门没有员工，其他的没有改部门

NVL(COMM, 0) ：方法判断空值，如果comm 为空值则返回后面的0 不为空返回本身 comm

--方法一

DECLARE
DNO SCOTT.EMP.DEPTNO%TYPE := &DNO;
BEGIN
CASE DNO
    WHEN 10 THEN
      UPDATE SCOTT.EMP SET COMM = NVL(COMM, 0) + 1000 WHERE DEPTNO = DNO;
    WHEN 20 THEN
      UPDATE SCOTT.EMP SET COMM = NVL(COMM, 0) + 2000 WHERE DEPTNO = DNO;
    WHEN 30 THEN
      UPDATE SCOTT.EMP SET COMM = NVL(COMM, 0) + 3000 WHERE DEPTNO = DNO;
    WHEN 40 THEN
      DBMS_OUTPUT.PUT_LINE('该部门没有员工！！');
    ELSE
      DBMS_OUTPUT.PUT_LINE('该部门不存在！！');
END CASE;
END;
--方法二
DECLARE
DNO SCOTT.EMP.DEPTNO%TYPE := &DNO; -- 定义一个字段与表的数据类型一样
BEGIN
CASE
    WHEN DNO = 10 THEN
      UPDATE SCOTT.EMP SET COMM = NVL(COMM, 0) + 1000 WHERE DEPTNO = DNO;
    WHEN DNO = 20 THEN
      UPDATE SCOTT.EMP SET COMM = NVL(COMM, 0) + 2000 WHERE DEPTNO = DNO;
    WHEN DNO = 30 THEN
      UPDATE SCOTT.EMP SET COMM = NVL(COMM, 0) + 3000 WHERE DEPTNO = DNO;
    WHEN DNO = 40 THEN
      DBMS_OUTPUT.PUT_LINE('该部门没有员工！！');
    ELSE
      DBMS_OUTPUT.PUT_LINE('该部门不存在！！');
END CASE;
END;
SELECT * FROM SCOTT.EMP WHERE EMPNO = 7369;

--例子六：循环打印1.。。。10
--loop类似do...while循环：先执行后判断，至少1次
DECLARE
I NUMBER(2, 0) := 11;
BEGIN
LOOP
    DBMS_OUTPUT.PUT_LINE(I);
    I := I + 1;
    EXIT WHEN I > 10;
END LOOP;
END;

DECLARE
I NUMBER(2, 0) := 11;
BEGIN
WHILE I <= 10 LOOP
    DBMS_OUTPUT.PUT_LINE(I);
    I := I + 1;
END LOOP;
END;

--for 循环练习
DECLARE
I NUMBER(2, 0) := 1;
BEGIN
FOR I IN 1 .. 10 LOOP
DBMS_OUTPUT.PUT_LINE(I);
END LOOP;
END;

--九九乘法表（控制台输出）
DECLARE
I NUMBER(1, 0) := 1;
J NUMBER(1, 0) := 1;
BEGIN
FOR I IN 1 .. 9 LOOP
    FOR J IN 1 .. I LOOP
      DBMS_OUTPUT.PUT(J || '*' || I || '=' || I * J || '     ');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

--例子七：根据输入的员工编号查询员工信息，并且显示
DECLARE
EMPROW SCOTT.EMP%ROWTYPE; --定义一个指定表的行类型的变量
BEGIN
SELECT * INTO EMPROW FROM SCOTT.EMP WHERE ENAME LIKE '%&name%';
DBMS_OUTPUT.PUT_LINE('姓名：' || EMPROW.ENAME);
DBMS_OUTPUT.PUT_LINE('工资：' || EMPROW.SAL);
EXCEPTION
WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('没有查到数据');
WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('查询了不止一行的数据');
END;

SELECT * FROM SCOTT.EMP WHERE EMPNO = 7369;

--例子八:根据输入员工编号，调换部门
DECLARE
EXCP_NOT_DATA_UPDATED EXCEPTION;
EXCP_FK_NOT_FOUND     EXCEPTION;
PRAGMA EXCEPTION_INIT(EXCP_FK_NOT_FOUND, -2291);
BEGIN
UPDATE SCOTT.EMP SET DEPTNO = &DNO WHERE EMPNO = &ENO;
IF SQL%NOTFOUND THEN
    RAISE EXCP_NOT_DATA_UPDATED;
END IF;
EXCEPTION
WHEN EXCP_FK_NOT_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('该部门没有存在！');
WHEN EXCP_NOT_DATA_UPDATED THEN
    DBMS_OUTPUT.PUT_LINE('没有任何数据行被修改！');
END;

--例子九：查询包含输入内容的名字的所有员工，并且打印到控制台

&name ：弹出框输入值

DECLARE
EMPROW SCOTT.EMP%ROWTYPE; --定义一个指定表的行类型的变量
BEGIN
SELECT * INTO EMPROW FROM SCOTT.EMP WHERE ENAME LIKE '%&name%';
DBMS_OUTPUT.PUT_LINE('姓名：' || EMPROW.ENAME || '工资：' || EMPROW.SAL);
EXCEPTION
WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('没有查到数据');
WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('查询了不止一行的数据');

END;

--游标的定义
DECLARE
EMPROW SCOTT.EMP%ROWTYPE;
CURSOR CUR_EMP IS
    SELECT * FROM SCOTT.EMP WHERE ENAME LIKE '%&name%';
--截止到上一步只是做了游标结果集的预定义，并没有执行
BEGIN
IF NOT CUR_EMP %ISOPEN THEN
    --如果游标没有被打开
    OPEN CUR_EMP; --打开游标：执行sql语句
END IF;

--处理游标(提取数据)
LOOP
    FETCH CUR_EMP
      INTO EMPROW;
    EXIT WHEN CUR_EMP%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('第' || CUR_EMP%ROWCOUNT || '个员工，姓名：' ||
                         EMPROW.ENAME || '工资：' || EMPROW.SAL);
    --close cur_emp;
END LOOP;
CLOSE CUR_EMP;
END;

--游标的定义
DECLARE
EMPROW SCOTT.EMP%ROWTYPE;
CURSOR CUR_EMP IS
    SELECT * FROM SCOTT.EMP WHERE ENAME LIKE '%&name%';
--截止到上一步只是做了游标结果集的预定义，并没有执行
BEGIN
FOR EMPROW IN CUR_EMP LOOP
    --自动打开
    DBMS_OUTPUT.PUT_LINE('姓名：' || EMPROW.ENAME || '工资：' || EMPROW.SAL);
END LOOP; --自动关闭
END;

--如果员工的工资少于1500，加成2000
DECLARE
EMPROW SCOTT.EMP%ROWTYPE;
CURSOR CUR_EMP IS
    SELECT * FROM SCOTT.EMP WHERE ENAME LIKE '%&name%' FOR UPDATE OF SAL;
--截止到上一步只是做了游标结果集的预定义，并没有执行
BEGIN
FOR EMPROW IN CUR_EMP LOOP
    --自动打开
    IF EMPROW.SAL < 1500 THEN
      UPDATE SCOTT.EMP SET SAL = 2000 WHERE CURRENT OF CUR_EMP; --修改游标的当前行数据
    END IF;
END LOOP; --自动关闭
END;

SELECT * FROM SCOTT.EMP;

--带参数的游标
DECLARE
EMPROW SCOTT.EMP%ROWTYPE;
CURSOR CUR_EMP(MYNAME NVARCHAR2) IS
    SELECT * FROM SCOTT.EMP WHERE ENAME LIKE '%' || MYNAME || '%';
--截止到上一步只是做了游标结果集的预定义，并没有执行
BEGIN
FOR EMPROW IN CUR_EMP('S') LOOP
    --自动打开
    DBMS_OUTPUT.PUT_LINE('姓名：' || EMPROW.ENAME || '工资：' || EMPROW.SAL);
END LOOP; --自动关闭
END;

0 0