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;