Oracle数据库之PL/SQL基础

根据课程http://www.imooc.com/learn/360所做笔记

0.pl/sql是对sql的扩展

面向过程（分支 循环）

other
DB2 sql/pl
sql server/Transac(T-sql)

1.例子：根据员工类型涨工资。

可以用java
可以用pl\sql（操作oracle数据库比java快 存储过程 事务 触发器）

2程序结构

declare
说明部分（变量 光标 例外）
begin
语句序列
exception
例外处理语句
end；
/
--打开输出开关
set serveroutput on；

3.查看包的帮助

desc dbms_output

4.数据类型

char varchar2 date number boolean long
psalmnumber := 3.4

example
declare
pnum number(7,2);
pname varchar2(10);
pdate date;
begin
pnum := -1;
dbms_output.put_line(pnum);
pname := 'Tom';
dbms_output.put_line(pname);
pdate := sysdate;
dbms_output.put_line(pdate);
dbms_output.put_line(pdate + 1);
dbms_output.put_line(pdate + 2);
end;

5.引用型变量

myname emp.name%type
example
declare
pname emp.ename%type;
psal emp.sal%type;
begin
select ename,sal into pname, psal from emp where empno=7839
dbms_output.put_line(pname
dbms_output.put_line(psal
end;

6.记录型变量

代表某一列 emp_rec emp%rowtype;
example
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;

7.if语句

num是地址 所需数的位置
set serveroutput on;
accept num prompt 'please input a number';
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(3);
end if;
end;

8.循环

(1)

declare
pnum number := 1;
begin
while pnum < 10 loop
dbms_output.put_line(pnum);
pnum := pnum + 1;
end loop;
end;

(2)

declare
pnum number := 1;
begin
loop
exit when pnum > 10;
dbms_output.put_line(pnum);
pnum := pnum + 1;
end loop;
end;

(3)

declare
begin
for pnum in 1..5 loop
dbms_output.put_line(pnum);
end loop;
end;

9.光标（属性found notfound isopen rowcount影响的行数）

(1)

declare
cursor cemp is select ename, sal from emp;
pname emp.ename%type;
psal emp.sal%type;
begin
open cemp;
loop
fetch cemp into pname, psal;
exit when cemp%notfound;
dbms_output.put_line(pname||'的薪水是'||psal);
end loop;
close cemp;
end;

(2)员工涨工资

declare
cursor cemp is select empno, empjob from emp;
pempno emp.empno%type;
pjob emp.empjob%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;
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;
commit;
dbms_output.put_line('chenggong');
end;

(3)rowcount

declare
cursor cemp is select empno, empjob from emp;
pempno emp.empno%type;
pjob emp.empjob%type;
begin
open cemp;
loop
fetch cemp into pempno, pjob;
exit when cemp%notfound;
dbms_output.put_line(cemp%rowcount);
end loop;
close cemp;
commit;
dbms_output.put_line('chenggong');
end;

(4)

每一个回话下允许打开的光标数为300个
show parameter cursor(模糊查询 open_cursors 300
alter system set open_cursors=400 scope=both;
scope范围 取值both memory spfile（数据库需要重启）

10.例外

(1)系统例外no_data_found

declare
pname emp.ename%type;
begin
select ename into pname from emp where empno=1234;
exception
when no_data_found then dbms_output.put_line('没有找该给员工');
when others then dbms_output.put_line('其他意外');
end;

(2)系统例外too_many_rows

declare
pname emp.ename%type;
begin
select ename into pname from emp where deptno=10;
exception
when too_many_rows then dbms_output.put_line('匹配了多行');
when others then dbms_output.put_line('其他例外');
end;

(3)系统例外zero_divide

declare
pnum number;
begin
pnum := 1/0;
exception
when zero_divide then dbms_output.put_line('0不能做除数');
when others then dbms_output.put_line('其他例外');
end;

(4)系统例外value_error 算数或类型转换错误

declare
pnum number;
begin
pnum := 'asd';
exception
when value_error then dbms_output.put_line('算数或转换错误');
when others then dbms_output.put_line('其他例外');
end;

(5)自定义例外 可以当成一个变量 可抛出

declare
cursor cemp is select ename from emp where deptno=500;
pname emp.ename%type;
no_emp_found exception;
begin
open cemp;
fetch cemp into pname;
if cemp%notfound then
raise no_emp_found;
end if;
close cemp;(不用担心没有关闭光标，系统会启动一个进程pmon（progress monitor来关闭它）
exception
when no_emp_found then dbms_output.put_line('没有找到该部门的员工');
when others then dbms_output.put_line('其他例外');
end;

11综合应用：统计每年入职员工人数（只有80 81 82 87年）

(1)瀑布模型

需求分析
概要设计
详细设计
编码
测试
发布

(2)编写PL/SQL程序步骤

写出需要的sql语句
创建需要的表，游标
定义需要的变量 及初始值
declare
cursor cemp is select to_char(hiredate, 'yyyy') from emp;
num80 number := 0;
num81 number := 0;
num82 number := 0;
num87 number := 0;
y varchar2(10);
begin
open cemp;
loop
fetch cemp into y;
exit when cemp%notfound;
if y = '1980' then num80 := num80 + 1;
elsif y = '1981' then num81 := num81 + 1;
elsif y = '1982' then num82 := num82 + 1;
else num87 := num87 + 1;
end if;
end loop;
close cemp;
dbms_output.put_line('Totle:'||' '||'num80'||' '||'num81'||' '||'num82'||' '||'num87');
dbms_output.put_line(num80+num81+num82+num87||' '||num80||' '||num81||' '||num82||' '||num87);
end;

12.其他

(1) nvl函数 吧null值转换成其他（比如0）
(2)@example.sql命令，在终端执行sql脚本。