14039 oracle,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脚本。