根据慕课网课程oracle数据库之PL\SQL基础整理

0.pl/sql是对sql的扩展

面向过程（分支 循环）

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

  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

  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)瀑布模型

1. 需求分析
2. 概要设计
3. 详细设计
4. 编码
5. 测试
6. 发布

(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.其他

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