Oracle学习(十一)：PL/SQL
浏览数：27 /
时间：2015年06月12日
1.知识点：可以对照下面的录屏进行阅读
PL/SQL程序结构
declare
说明部分 （变量说明，光标申明，例外说明 〕
begin
语句序列 （DML语句〕…
exception
例外处理语句
End;
/
------------------------------------------------------------------
--第一个PL/SQL程序：HelloWorld
set serveroutput on --如果要在屏幕上输出信息，需要将serveroutput开关打开
declare
--变量的说明
begin
--程序体
--程序包package
dbms_output.put_line('Hello World');
end;
/
------------------------------------------------------------------
--引用型变量: 查询并打印7839的姓名和薪水
set serveroutput on
declare
--定义变量，
pename emp.ename%type; --将pename的类型设置为emp表中ename的类型
psal emp.sal%type; --将psal的类型设置为emp表中sal的类型
begin
--查询：
--ename,sal into pename,psal：将查询的ename，sal分别赋值给pename，psal按顺序赋值
select ename,sal into pename,psal from emp where empno=7839;
--打印
dbms_output.put_line(pename||'的薪水是'||psal);
end;
/
------------------------------------------------------------------
--记录型变量 查询并打印7839的姓名和薪水
set serveroutput on
declare
--定义变量,代表一行
emp_rec emp%rowtype;
begin
--得到一行，赋值给emp_rec
select * into emp_rec from emp where empno=7839;
dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
end;
/
------------------------------------------------------------------
--if语句：判断用户输入的数字
set serveroutput on
/*
1.提示信息
2. 接收键盘输入
num 是一个地址值
SQL优化: num绑定变量(尽量使用绑定变量)
select * from emp where deptno=10; --> 执行计划
select * from emp where deptno=20; --> 执行计划
-->
select * from emp where deptno=#
*/
accept num prompt '请输入一个数字';
declare
--变量保存输入的数字
pnum number := #
begin
--判断
if pnum = 0 then
dbms_output.put_line('您输入的是0');
elsif pnum = 1 then
dbms_output.put_line('您输入的是1');
elsif pnum = 2 then
dbms_output.put_line('您输入的是2');
else
dbms_output.put_line('其他数字');
end if;
end;
/
------------------------------------------------------------------
--循环： 打印1~10，有3中方法，见下图
set serveroutput on
declare
pnum number := 1; --给pnum赋值1，pnum类型为number
begin
loop
--退出：成立退出，不成立循环
exit when pnum > 10;
--隐式转换，number转varchar2
dbms_output.put_line(pnum);
pnum := pnum + 1;
end loop;
end;
/
------------------------------------------------------------------
光标（Cursor）==ResultSet
说明光标语法：
CURSOR 光标名 [ (参数名 数据类型[,参数名 数据类型]...)]
IS SELECT 语句;
用于存储一个查询返回的多行数据
例如：
cursor c1 is select ename from emp;
打开光标： open c1; (打开光标执行查询)
取一行光标的值：fetch c1 into pjob; (取一行到变量中)
关闭光标： close c1;(关闭游标释放资源)
注意： 上面的pjob必须与emp表中的job列类型一致：
定义：pjob emp.empjob%type;
------------------------------------------------------------------
--光标: 使用游标查询员工姓名和工资，并打印
/*
光标的3个属性：
%isopen 是否被打开：打开true，关闭false；
%rowcount 行数
%notfound 是否有值：没有值true，有值false；
*/
set serveroutput on
declare
--光标
cursor cemp is select ename,sal from emp;
pename emp.ename%type; --将用来接收的变量类型设置为和查询的类型相同
psal emp.sal%type;
begin
open cemp; --打开光标执行查询
loop
--从集合中取值
fetch cemp into pename,psal;
--光标中没有值时退出
exit when cemp%notfound;
dbms_output.put_line(pename||'的薪水是'||psal);
end loop;
close cemp;
end;
/
------------------------------------------------------------------
--光标练习：给员工涨工资 总裁1000 经理800 其他400
--光标同时能打开的最大数量默认为300个
/*
SQL> show parameters cursor
NAME TYPE VALUE
------------------------------------ ----------- --------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 20
*/
set serveroutput on
declare
--光标代表员工
cursor cemp is select empno,job from emp;
pempno emp.empno%type;
pjob emp.job%type;
begin
open cemp;
loop
fetch cemp into pempno,pjob;
exit when cemp%notfound;
--判断
if pjob = 'PRESIDENT' then update emp set sal=sal+1000 where empno=pempno; --总裁涨1000
elsif pjob = 'MANAGER' then update emp set sal=sal+800 where empno=pempno; --经理涨800
else update emp set sal=sal+400 where empno=pempno; --其他涨400
end if;
end loop;
close cemp;
--提交： 隔离级别
commit;
dbms_output.put_line('完成');
end;
/
------------------------------------------------------------------
--带参数的光标：查询某个部门的员工姓名
set serveroutput on
declare
--定义一个带参数的光标
cursor cemp(pdno number) is select ename from emp where deptno=pdno;
pename emp.ename%type;
begin
open cemp(20); --打开光标，并传递参数
loop
fetch cemp into pename;
exit when cemp%notfound;
dbms_output.put_line(pename);
end loop;
close cemp;
end;
/
------------------------------------------------------------------
Oracle的异常处理
1.系统定义例外
No_data_found (没有找到数据)
Too_many_rows (select …into语句匹配多个行)
Zero_Divide ( 被零除)
Value_error (算术或转换错误)
Timeout_on_resource (在等待资源时发生超时)
--举例 Zero_Divide ( 被零除)
set serveroutput on
declare
pnum number;
begin
pnum := 1/0;
exception
when Zero_Divide then dbms_output.put_line('1: 0不能做被除数');
dbms_output.put_line('2: 0不能做被除数');
when Value_error then dbms_output.put_line('算术错');
when others then dbms_output.put_line('其他例外');
end;
/
用户自己定义的例外
--自定义例外： 查询50号部门的员工姓名
set serveroutput on
declare
cursor cemp is select ename from emp where deptno=50;
pename emp.ename%type;
--自定义例外
no_emp_found exception;
begin
open cemp;
--取一个员工
fetch cemp into pename;
if cemp%notfound then
raise no_emp_found;
end if;
/*
if cemp%isopen then
close no_emp_found;
end if;
*/
close cemp;
exception
when no_emp_found then dbms_output.put_line('没有找到员工');
when others then dbms_output.put_line('其他例外');
end;
/
图：循环的3种写法，文中采用右上角的方法。
2.在Sqlplus下实际执行的结果录屏
SQL> --第一个PL/SQL程序：HelloWorld
SQL> set serveroutput on --如果要在屏幕上输出信息，需要将serveroutput开关打开
SQL> declare
2 --变量的说明
3 begin
4 --程序体
5 --程序包package
6 dbms_output.put_line('Hello World');
7 end;
8 /
Hello World
PL/SQL procedure successfully completed
SQL>
SQL> --引用型变量: 查询并打印7839的姓名和薪水
SQL> set serveroutput on
SQL> declare
2 --定义变量，
3 pename emp.ename%type; --将pename的类型设置为emp表中ename的类型
4 psal emp.sal%type; --将psal的类型设置为emp表中sal的类型
5
6 begin
7
8 --查询：
9 --ename,sal into pename,psal：将查询的ename，sal分别赋值给pename，psal按顺序赋值
10 select ename,sal into pename,psal from emp where empno=7839;
11
12 --打印
13 dbms_output.put_line(pename||'的薪水是'||psal);
14
15 end;
16 /
KING的薪水是5000
PL/SQL procedure successfully completed
SQL>
SQL> -记录型变量 查询并打印7839的姓名和薪水
SQL> set serveroutput on
SQL> declare
2 --定义变量,代表一行
3 emp_rec emp%rowtype;
4 begin
5 --得到一行，赋值给emp_rec
6 select * into emp_rec from emp where empno=7839;
7
8 dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
9
10 end;
11 /
KING的薪水是5000
PL/SQL procedure successfully completed
SQL>
SQL> --if语句：判断用户输入的数字
SQL> set serveroutput on
SQL> /*
2 1.提示信息
3 2. 接收键盘输入
4 num 是一个地址值
5
6 SQL优化: num绑定变量(尽量使用绑定变量)
7 select * from emp where deptno=10; --> 执行计划
8 select * from emp where deptno=20; --> 执行计划
9 -->
10 select * from emp where deptno=#
11
12 */
13 accept num prompt '请输入一个数字';
SQL> declare
2 --变量保存输入的数字
3 pnum number := #
4 begin
5 --判断
6 if pnum = 0 then
7 dbms_output.put_line('您输入的是0');
8 elsif pnum = 1 then
9 dbms_output.put_line('您输入的是1');
10 elsif pnum = 2 then
11 dbms_output.put_line('您输入的是2');
12 else
13 dbms_output.put_line('其他数字');
14 end if;
15 end;
16 /
您输入的是2
PL/SQL procedure successfully completed
SQL>
SQL> --循环： 打印1~10
SQL> set serveroutput on
SQL> declare
2 pnum number := 1; --给pnum赋值1，pnum类型为number
3 begin
4 loop
5 --退出：成立退出，不成立循环
6 exit when pnum > 10;
7
8 --隐式转换，number转varchar2
9 dbms_output.put_line(pnum);
10
11 pnum := pnum + 1;
12
13 end loop;
14 end;
15 /
1
2
3
4
5
6
7
8
9
10
PL/SQL procedure successfully completed
SQL>
SQL> -光标: 使用游标查询员工姓名和工资，并打印
SQL> /*
2 光标的3个属性：
3 %isopen 是否被打开：打开true，关闭false；
4 %rowcount 行数
5 %notfound 是否有值：没有值true，有值false；
6 */
SQL> set serveroutput on
SQL> declare
2 --光标
3 cursor cemp is select ename,sal from emp;
4 pename emp.ename%type; --将用来接收的变量类型设置为和查询的类型相同
5 psal emp.sal%type;
6 begin
7 open cemp; --打开光标执行查询
8 loop
9 --从集合中取值
10 fetch cemp into pename,psal;
11 --光标中没有值时退出
12 exit when cemp%notfound;
13
14 dbms_output.put_line(pename||'的薪水是'||psal);
15
16 end loop;
17 close cemp;
18 end;
19 /
SMITH的薪水是800
ALLEN的薪水是1600
WARD的薪水是1250
JONES的薪水是2975
MARTIN的薪水是1250
BLAKE的薪水是2850
CLARK的薪水是2450
SCOTT的薪水是3000
KING的薪水是5000
TURNER的薪水是1500
ADAMS的薪水是1100
JAMES的薪水是950
FORD的薪水是3000
MILLER的薪水是1300
PL/SQL procedure successfully completed
SQL> --光标练习：给员工涨工资 总裁1000 经理800 其他400
SQL> --先查询没涨之前的
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
SQL> --执行PLSQL语句
SQL>
SQL> set serveroutput on
SQL> declare
2 --光标代表员工
3 cursor cemp is select empno,job from emp;
4 pempno emp.empno%type;
5 pjob emp.job%type;
6 begin
7 open cemp;
8 loop
9 fetch cemp into pempno,pjob;
10 exit when cemp%notfound;
11
12 --判断
13 if pjob = 'PRESIDENT' then update emp set sal=sal+1000 where empno=pempno; --总裁涨1000
14 elsif pjob = 'MANAGER' then update emp set sal=sal+800 where empno=pempno; --经理涨800
15 else update emp set sal=sal+400 where empno=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郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
