教程资料：http://www.w3ii.com/zh-CN/plsql/plsql_basic_syntax.html
以下示例都是基于安装Oracle时系统创建的orcl数据库emp表
Hello World
set serveroutput on -- 打开输出开关
-- pl/sql程序
declare
-- 声明部分(变量声明，光标声明，异常声明)
begin -- 程序开始
....语句序列(DML语句)
dbms_output.put_line('Hello World'); -- 打印Hello World
exception
-- 异常处理语句
end; -- 程序结束
/ -- 表示退出当前程序编辑并执行程序,在SQL Developer中要加，但是在PL/SQL Developer中不要加，set serveroutput on也是这样。
* 如果程序中有insert/update/delete语句，SQL Developer中一定要加commit， PL/SQL Developer中不需要(它是自动管理事务)
变量和常量
- 常用变量类型
char,varchar2,date,number,boolean,long
例如：
varl char(15);
pi constant number := 3.141592654;
married boolean := true;
psal number(7,2);
my_name emp.ename%type;
emp_rec emp%rowtype;
char(15):表示字符长度限定在15个以内。
constant : 定义常量。
boolean := true ： 表示赋值为true。
number(p,s): precision也叫精度，是指数中的总数字个数，默认情况下，精度为38 位，取值范围是1～38 之间。
scale是小数位数，即数中小数点右边的数字个数。其范围从-84到127，能够决定舍入规则。
如果我们不指定scale的值，默认就为0，表示该变量是个整数。
emp.ename%type: 表示引用emp表ename字段,ename是什么my_name就是什么。
emp%rowtype: 表示记录型变量，它记录了表中一行数据所有的字段，所以它的结果是一个数组。
取出方式：emp_rec.ename(表示取ename这个字段)，emp_rec.xxx（任意字段）
取出并赋值：emp_rec.ename := 'gordon'
引用型变量：
declare
-- 定义变量保存姓名和薪水
--pename varchar2(20);
--psal number; p,s都不指定表示 p,s都是默认值
pename emp.ename%type;
psal emp.sal%type;
begin
-- 得到7839的姓名和薪水，并使用 into 关键字赋值给pename,psal
select ename,sal into pename,psal from emp where empno = 7839;
-- 打印
dbms_output.put_line(pename||'的薪水是'||psal);
end;
/
记录型变量：
declare
-- 定义记录型变量：代表一行
emp_rec emp%rowtype;
begin
select * into emp_rec from emp where empno = 7839;
dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
end;
/
条件语句
- 三种方式：
IF 条件 THEN 语句1；
语句2；
END IF;
IF 条件 THEN 语句1；
ELSE 语句2；
END IF;
IF 条件 THEN 语句1；
ELSIF 条件 THEN 语句2； -- 注意这里的 ELSIF 写法，不是ELSEIF
ELSE 语句3；
END IF;
示例：
-- 接收键盘输入
accept nums prompt '请输入一个数字';
-- nums : 地址值，在该地址上保存了输入的值
declare
-- 定义变量保存输入的数字
-- 隐式转换(键盘输入的都是字符串)，& 符号表示取出地址值
pnum number := &nums;-- 将输入的数字赋值给pnum这个变量
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('你输入的数字是:' || pnum);
end if;
end;
循环语句
- 三种方式：
WHILE total <= 20000 -- 当条件成立时执行循环
LOOP
....语句序列
total := total + salary;
END LOOP;
LOOP
EXIT when 条件；-- 当条件成立时退出循环，否则继续循环
....语句序列
END LOOP;
FOR I IN 1 .. 3
-- 1..3 ： 表示可以循环3次分别是 I = 1,I = 2,I = 3。又如:5..100(表示从5到100之间的循环)
LOOP
....语句序列
END LOOP;
示例：
/*
打印 1 - 10
*/
declare
pnum number := 1;
begin
loop
exit when pnum > 10;
dbms_output.put_line(pnum);
pnum := pnum + 1;
end loop;
end;
Cursor(游标) == ResultSet(结果集)
-
游标语法
CURSOR 游标名 [(参数名 数据类型,参数名 数据类型....)] IS SELECT 语句 -
用于存储一个查询返回的多行数据
cursor cs is select ename from emp; -
打开游标： open cs;(打开游标执行查询)
-
取一行游标的值： fetch cs into pename;(取一行值并赋值给pename变量)
-
关闭游标： close cs;(关闭游标释放资源)
-
游标属性
- %isopen ： 是否打开
- %rowcount ： 有效的行数，比如游标中有100条记录但是只返回10条记录，那么rowcount就是10.
- %notfound ： 没有找到
- %found ： 找到了
- Oracle每个会话，一次只能打开300个光标。当然也可以修改此默认值，以管理员身份进入Oracle，执行alter system set open_cursors=400;
-
cursor游标和ResultSet游标的区别 ： ResultSet游标的初始位置是第一行数据的前一个位置，而cursor游标的初始位置就是第一行数据
示例：
/*
涨工资 总裁1000 经理800 其他400
*/
declare
-- 取出表中所有员工编号和职位
cursor cemp is select empno, empjob from emp;
-- 定义接收的变量
pempno emp.empno%type;
pjob emp.empjob%type;
begin
open cemp;-- 打开游标
loop
-- 取出一条记录并赋值给pempno，pjob
fetch cemp into pempno, pjob;
exit when cemp%notfound; -- 当游标取不到时退出循环
-- 判断
if pjob = 'PRESIDENT' then
update emp set sal = sal + 1000 where empno = pempno;
elsif pjob = 'MANAGER' then
update emp set sal = sal + 800 where empno = pempno;
else
update emp set sal = sal + 400 where empno = pempno;
end if;
end loop;
close cemp;-- 关闭游标
end;
带参数的cursor：
/*
查询某个部门的员工姓名
*/
declare
-- 定义游标参数dno,查询deptno = dno时此部门的员工姓名
cursor cemp(dno number) is select ename from emp where deptno = dno;
pename emp.ename%type;
begin
open cemp(10);-- 按Java的理解，上面定义的是形参，此处就是实参.表示查询10号部门
loop
fetch cemp into pename;
exit when cemp%notfound;
dbms_output.put_line(pename);
end loop;
close cemp;
end;
异常处理
- 系统定义的异常
- NO_data_found 没有找到数据
- Too_many_rows select...into 语句匹配多行(多行数据赋值给单一变量)
declare
pename emp.ename%type;
begin
select ename into pename from emp;
end;
+ Zero\_Divide 被零除，如 1/0
+ Value\_error 算术或转换错误
+ Timeout\_on_resource 连接资源超时
/*
异常处理
*/
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('算术或转换错误');
-- 其他错误 others ：表示其他所有类型的异常
when others then dbms_output.put_line('其他异常');
-- 在PL/SQL程序中应该捕获所有的异常，否则异常会给抛给数据库，可能会导致数据库异常而影响其他方面的正常使用
end;
- 自定义异常
/*
自定义异常
查询50号部门的员工姓名(数据库中没有50号部门)
*/
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
raise no_emp_found;
end if;
-- 其实在抛出异常后close cemp是不执行的，
-- 但是Oracle有一个Process monitor进程跟java虚拟机一样会定时清理闲置或垃圾资源
close cemp;
exception
when no_emp_found then dbms_output.put_line('自定义异常');
when others then dbms_output.put_line('其他异常');
end;
实例
- 实例1
/*
统计每年入职的员工人数，如：
total 1980 1981 1982 1987
15 1 10 2 2
*/
declare
-- to\_char：按一定规则转成字符串类型，这里把入职时间转成字符串并只取年份
cursor ctemp is select to\_char(hiredate, 'yyyy') from emp;
pyear varchar2(4);
pcount0 number := 0;
pcount1 number := 0;
pcount2 number := 0;
pcount7 number := 0;
begin
open ctemp;
loop
fetch ctemp into pyear;
exit when ctemp%notfound;
if pyear = '1980' then
pcount0 := pcount0 + 1;
elsif pyear = '1981' then
pcount1 := pcount1 + 1;
elsif pyear = '1982' then
pcount2 := pcount2 + 2;
else
pcount7 := pcount7 + 1;
end if;
end loop;
close ctemp;
dbms\_output.put_line('total:' ||
(pcount0 + pcount1 + pcount2 + pcount7) ||
' 1980:' || pcount0 || ' 1981:' || pcount1 ||
' 1982:' || pcount2 || ' 1987:' || pcount7);
end;
- 实例2
/*
为员工涨工资。从最低工资调起每人长10%，但工资总额不能超过5万元，
请计算长工资的人数和长工资后的工资总额
*/
declare
-- order by 默认是升序，题目要求从最低开始
cursor cemp is select empno,sal from emp order by sal;
pempno emp.empno%type;
psal emp.sal%type;
pcount number := 0;
ptotal number;
begin
-- 得到涨前工资总额
select sum(sal) into ptotal from emp;
dbms_output.put_line('涨前工资总额：'||ptotal);
open cemp;
loop
exit when ptotal > 50000;-- 当总额大于5000时退出
fetch cemp into pempno,psal;
exit when cemp%notfound;-- 当cemp找不到时退出
-- 涨工资
update emp set sal = sal * 1.1 where empno = pempno;
-- 涨工资的人数
pcount := pcount + 1;
-- 涨后的工资总额
ptotal := ptotal + psal \* 0.1;
end loop;
close cemp;
commit;
dbms_output.put_line('涨工资的人数：'||pcount||' 涨后总额: '||ptotal);
end;
注意：程序还是有问题的，多执行几次此程序每次结果都不一样。
问题原因：第一次执行是对的，但是当第二次执行时又把已加过工资的人重新取出，
而且最低的那个人涨10%还是比较少的并没有超过限定条件。第三次第四次都是这样。
解决方式：应该保证程序在一定时间段内只执行一次，前3秒涨了工资现在又涨明显不合理。
- 实例3
实现按部门分段(3000元以下，3000 ~ 6000，6000以上)，统计各工资段的职工人数，
以及各部门的工资总额（工资总额中不包括奖金），参考如下：
部门 <3000 3000~6000 >6000 工资总额
10 2 1 0 8750
20 3 2 0 10875
30 6 0 0 9400
40 0 0 0 0
-- 创建一张表记录查出的结果
drop table msg;
create table msg(deptId number,count3 number,count6 number,count9 number,saltotal number);
declare
-- 找出所有部门 根据部门号找出该部门所有人工资
cursor cdept is select deptno from dept;
pdeptno dept.deptno%type;
cursor csal(tno number) is select sal from emp where deptno = tno;
psal emp.sal%type;
pcount3 number;
pcount6 number;
pcount9 number;
ptotalSal number;
begin
open cdept;
loop
fetch cdept into pdeptno;
exit when cdept%notfound;
pcount3 := 0;
pcount6 := 0;
pcount9 := 0;
ptotalSal := 0;
open csal(pdeptno);
loop
fetch csal into psal;
exit when csal%notfound;
if psal < 3000 then
pcount3 := pcount3 + 1;
elsif psal > 3000 and psal < 6000 then
pcount6 := pcount6 + 1;
elsif psal > 6000 then
pcount9 := pcount9 + 1;
else
dbms_output.put_line('不在考虑范围内');
end if;
ptotalSal := ptotalSal + psal;
-- 还可以直接以pdeptno为条件查出该部门的总工资
-- select sum(sal) into ptotalSal from emp where deptno = pdeptno;
end loop;
close csal;
-- 插入表中保存查询记录 ,nvl(p1,p2):相当于三元运算符，如果p1是null则返回p2,否则返回p1
insert into msg values(pdeptno,pcount3,pcount6,pcount9,ptotalSal);
dbms_output.put_line(pdeptno || '部门' || ' 3k: ' || pcount3 ||
' 3-6k: ' || pcount6 || ' >6k: ' || pcount9 ||
' 总额 : ' || ptotalSal);
end loop;
close cdept;
commit;
end;
网友评论