Oracle PL/SQL 编程基础

 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;