Oracle学习(十一)：PL/SQL(四)
mpno=pempno; --其他涨400
16 end if;
17 end loop;
18 close cemp;
19
20 --提交： 隔离级别
21 commit;
22
23 dbms_output.put_line('完成');
24 end;
25 /
完成
PL/SQL procedure successfully completed
SQL> --查询执行PLSQL语句后的薪水
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 1200.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 2000.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1650.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 3775.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1650.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 3650.00 30
7782 CLARK MANAGER 7839 1981/6/9 3250.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3400.00 20
7839 KING PRESIDENT 1981/11/17 6000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1900.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1500.00 20
7900 JAMES CLERK 7698 1981/12/3 1350.00 30
7902 FORD ANALYST 7566 1981/12/3 3400.00 20
7934 MILLER CLERK 7782 1982/1/23 1700.00 10
14 rows selected
SQL> --对比涨之前，可以看出涨工资成功
SQL>
SQL> --带参数的光标：查询某个部门的员工姓名
SQL> set serveroutput on
SQL> declare
2 --定义一个带参数的光标
3 cursor cemp(pdno number) is select ename from emp where deptno=pdno;
4 pename emp.ename%type;
5 begin
6 open cemp(20); --打开光标，并传递参数
7 loop
8 fetch cemp into pename;
9 exit when cemp%notfound;
10
11 dbms_output.put_line(pename);
12
13
14 end loop;
15 close cemp;
16 end;
17 /
SMITH
JONES
SCOTT
ADAMS
FORD
PL/SQL procedure successfully completed
SQL>
SQL> --举例 Zero_Divide ( 被零除)
SQL> set serveroutput on
SQL> declare
2 pnum number;
3 begin
4
5 pnum := 1/0;
6
7 exception
8 when Zero_Divide then dbms_output.put_line('1: 0不能做被除数');
9 dbms_output.put_line('2: 0不能做被除数');
10 when Value_error then dbms_output.put_line('算术错');
11 when others then dbms_output.put_line('其他例外');
12 end;
13 /
1: 0不能做被除数
2: 0不能做被除数
PL/SQL procedure successfully completed
SQL>
SQL> --自定义例外： 查询50号部门的员工姓名
SQL> set serveroutput on
SQL> declare
2 cursor cemp is select ename from emp where deptno=50;
3 pename emp.ename%type;
4
5 --自定义例外
6 no_emp_found exception;
7 begin
8 open cemp;
9 --取一个员工
10 fetch cemp into pename;
11 if cemp%notfound then
12 raise no_emp_found;
13 end if;
14
15 close cemp;
16
17 exception
18 when no_emp_found then dbms_output.put_line('没有找到员工');
19 when others then dbms_output.put_line('其他例外');
20
21 end;
22 /
没有找到员工
PL/SQL procedure successfully completed
SQL> spool off
Stopped spooling to c:\PLSQL.txt