1. 概述

1.1 SQL Developer安装

见安装文件

1.2 PL/SQL的作用和第一个PL/SQL程序

-- 给员工涨工资
	-- 总裁涨1000
	-- 经理涨800
	-- 其他员工涨400
	
plsql的作用：
	操作oracle数据库效率最高
	为后面存储过程打基础

-- 打印 hello world
declare 
	-- 说明部分（变量，光标或者例外）
begin 
	-- 程序体
	dbms_output.put_line(‘Hello World‘);
end;

-- 打开输出开关
set serveroutput on

1.3 PL/SQL简介

PL/SQL（Procedure Language SQL）

PL/SQL是Oracle对sql语言的过程化扩展
	指在SQL命令语言中增加了过程处理语句（如分支、循环等），使SQL语言具有过程处理能力。
	面向过程
	
不同数据库的SQL扩展
Oracle :PL/SQL
DB2:SQL/PL
SQL Server: Transac-SQL(T-SQL)

2. PL/SQL基础语法

2.1 PL/SQL基本变量类型

-- PL/SQL的程序结构

declare 
	-- 说明部分（变量说明，光标声明或者例外说明）
begin 
	-- 语句序列（DML语句）
exception
	-- 例外处理语句
end;


-- 说明部分
-- 定义基本变量
类型：char  varchar2  date  number  boolean  long
举例：
	var1 char(15);
	married boolean := true;
	psal number(7,2);


declare
    pnumber number(7,2);
   pname varchar(20);
   pdate date;
begin 
    pnumber:=1;
    DBMS_OUTPUT.put_line(pnumber);
    pname:=‘Tom‘;
    DBMS_OUTPUT.put_line(pname);
    pdate:=sysdate;
    DBMS_OUTPUT.put_line(pdate);
    --计算明天的日期
    DBMS_OUTPUT.put_line(pdate+1);
end;   

2.2 PL/SQL引用型变量和记录型变量

-- 引用型变量
-- 举例
	my_name emp.ename%type;
	
set serveroutput on
declare
    -- 定义引用型变量，查询并打印7839的项目和薪水
    -- pname varchar2(20);
    -- psal number;
    pname emp.ename%type;
    psal emp.sal%type;
begin 
    --得到7839的姓名和薪水
    select ename,sal into pname,psal from emp where empno=7839;
    -- 打印姓名和薪水
    dbms_output.put_line(pname||‘的薪水是‘||psal);
end;

-- 记录型变量
-- 举例
	emp_rec emp%rowtype;	
-- 记录型变量分量的引用
	emp_rec.ename := ‘ADAMS‘;
	
set serveroutput on
declare
    -- 定义记录型变量，查询并打印7839的项目和薪水
   emp_rec emp%rowtype;
begin 
    --得到7839的姓名和薪水
    select * into emp_rec  from emp where empno=7839;
    -- 打印姓名和薪水
    dbms_output.put_line(emp_rec.ename||‘的薪水是‘||emp_rec.sal);
end;

2.3 PL/SQL中if语句的使用

1.	IF 条件 THEN 语句1;
	语句2;
	END IF;
	
2.	IF 条件 THEN 语句序列1;
	ELSE 语句序列2;
	END IF;
	
3.	IF 条件 THEN 语句;
	ELSIF 语句 THEN 语句;
	ELSE 语句;
	END IF;
	
-- 判断用户从键盘输入的数字
set serveroutput on

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‘);
   elsif pnum=3 then dbms_output.put_line(‘你输入的数字是3‘);
   elsif pnum=4 then dbms_output.put_line(‘你输入的数字是4‘);
   elsif pnum=5 then dbms_output.put_line(‘你输入的数字是5‘);
   elsif pnum=6 then dbms_output.put_line(‘你输入的数字是6‘);
   elsif pnum=7 then dbms_output.put_line(‘你输入的数字是7‘);
   elsif pnum=8 then dbms_output.put_line(‘你输入的数字是8‘);
   elsif pnum=9 then dbms_output.put_line(‘你输入的数字是9‘);
   else dbms_output.put_line(‘你输入的为其他数字‘);
   end if;
end;

2.4 PL/SQL循环语句的使用

1.	WHILE total<=25000 LOOP
 	...
 	total := total+salary;
 	END LOOP;
 
 eq:
 set serveroutput on
declare
   pnum number :=1;
begin 
  while pnum <=10 loop
    dbms_output.put_line(pnum);
    pnum := pnum+1;
    end loop;
end;
 
 
 
 2.		Loop 
 		EXIT [ WHEN 条件 ];
 			...
 		End loop;
 -- 推荐使用这种循环
 
 eq:
 set serveroutput on
declare
   pnum number :=1;
begin 
  loop 
    exit when pnum>10;
    dbms_output.put_line(pnum);
    pnum:=pnum+1;
  end loop;
end;


3.		FOR I IN 1..3 LOOP
		语句序列;
		END LOOP;
		


eq：
set serveroutput on
declare
   pnum number:=1;
begin 
  for pnum in 1..10 loop
    dbms_output.put_line(pnum);
  end loop;
end;

3. 光标（游标）

3.1 PL/SQL光标之光标的引入

-- 给员工涨工资
	-- 总裁涨1000
	-- 经理涨800
	-- 其他员工涨400
set serveroutput on
declare
   ptilte varchar(20);
begin 
  select job into ptilte from emp;
  if ptilte =‘PRESIDENT‘ then update emp set sal=sal + 1000;
  elsif ptilte =‘MANAGER‘ then update emp set sal=sal + 800;
  else update emp set sal=sal + 400;
  end if;
end;   -- error

光标
就是一个结果集（Result Set）

3.2 PL/SQL光标之光标的语法和第一个实例

-- 光标的语法
CURSOR 光标名 [(参数名 数据类型 [,参数名 数据类型] ... )]
IS SELECT 语句;

-- 一个具体的光标
cursor c1 is select ename from emp;

-- 从光标中取值
-- 打开光标 执行查询
open c1;
-- 关闭光标 释放资源
close c1;
-- 取一行光标的值
fetch c1 into pename;
fetch 的作用：
	把当前指针指向的记录返回
	将指针指向下一条记录

--光标的属性
%found  
%notfound


-- 使用光标查询员工姓名和工资，并打印
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;

3.3 实例：给员工涨工资

-- 给员工涨工资
	-- 总裁涨1000
	-- 经理涨800
	-- 其他员工涨400
	
set serveroutput on
declare
  cursor cemp is select empno,empjob from emp;
  pempno emp.empno%type;
  pjob emp.empjob%type;
  
begin 
	rollback;
  open cemp;
  
  loop 
    fetch cemp into pempno,pjob;
  exit when cemp%notfound;
    if pjob=‘PRESIDENT‘ then update emp set sal =sal+1000;
        elsif pjob=‘MANAMGR‘ then update emp set sal =sal+800;
        else update emp set sal =sal+400;
    end if;
    
  end loop;

  close cemp;
  -- 对于oracle，默认的事务隔离级别是 read committed
  commit; -- 事务提交
  dbms_output.put_line(‘涨工资完成‘); 
end;

3.4 PL/SQL光标之光标的属性和光标数的限制

--光标的属性
%found   -- true false
%notfound
%isopen -- 判断光标是否打开 打开为true 否则为false
%rowcount -- 影响的行数

-- 光标数的限制
默认情况下，oracle数据库只允许在同一个会话中，打开300个光标
show parameter cursor
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     300
session_cached_cursors               integer     50

修改光标数的限制
alter system set open_cursors =400 scope=both;
scope的取值：
	both
	menory
	spfile(数据库需要重启)

-- eq
set serveroutput on
declare
  cursor cemp is select empno,empjob from emp;
  pempno emp.empno%type;
  pjob emp.empjob%type;
  
begin 

  open cemp;
  if cemp%isopen then
    dbms_output.put_line(‘光标已经打开‘); 
  else 
    dbms_output.put_line(‘光标没有打开‘); 
  end if;
  loop 
    fetch cemp into pempno,pjob;
  exit when cemp%notfound;
   dbms_output.put_line(‘rowcount:‘||cemp%rowcount); 
  end loop;
  close cemp;
  
end;

3.5 PL/SQL光标之带参数的光标

CURSOR 光标名 [(参数名 数据类型 [,参数名 数据类型] ... )]
IS SELECT 语句;


-- 查询某个部门中员工的姓名
set serveroutput on
declare
  cursor cemp(dno number) is select ename from emp where deptno=dno;
  pename emp.ename%type;
begin 
  open cemp(10);
  loop 
    fetch cemp into pename;
  exit when cemp%notfound;
   dbms_output.put_line(pename); 
  end loop;
  close cemp; 
end;

4. 例外（异常）

4.1 例外的概念和系统例外

例外是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性
系统例外
	no_data_found	没有找到数据
	too_many_rows    select...into语句匹配多个行
	zero_divide		被零除
	value_error		算术或装换错误
	timeout_on_resource		在等待资源时发生超时
自定义例外

4.2 系统例外之no_data_found

-- 没有找到数据

set  serverout on
declare
    pename emp.ename%TYPE;
begin
	-- 查询员工号是1234的员工姓名
    select ename into pename from emp where empno=1234;
    
    exception 
        when no_data_found then DBMS_OUTPUT.PUT_LINE(‘没有找到该员工‘);
        when others then DBMS_OUTPUT.PUT_LINE(‘其他例外‘);
end;
/

4.3 系统例外之too_many_rows

-- select...into语句匹配多个行

set  serverout on
declare
    pename emp.ename%TYPE;
begin
    -- 查询所有10号部门的员工姓名
    select ename into pename from emp where deptno=10;
    
    exception 
        when too_many_rows then DBMS_OUTPUT.PUT_LINE(‘select...into语句匹配多个行‘);
        when others then DBMS_OUTPUT.PUT_LINE(‘其他例外‘);
end;
/

4.4 系统例外之zero_divide

-- 被零除

set  serverout on
declare
    pnum number;
begin
    pnum:=1/0;
    
    exception 
        when zero_divide then DBMS_OUTPUT.PUT_LINE(‘0不能做除数‘); 
                                DBMS_OUTPUT.PUT_LINE(‘被零除‘);
        when others then DBMS_OUTPUT.PUT_LINE(‘其他例外‘);
end;
/

4.5 系统例外之value_error

-- 算术或转换错误

set  serverout on
declare
    pnum number;
begin
    pnum:=‘abc‘;
    
    exception 
        when value_error then DBMS_OUTPUT.PUT_LINE(‘算术或转换错误‘); 
        when others then DBMS_OUTPUT.PUT_LINE(‘其他例外‘);
end;
/

4.6 自定义例外

定义变量，类型是exception
使用raise 抛出自定义例外

-- 查询50号部门的员工姓名
set  serverout 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;
 close cemp;
    exception 
        when no_emp_found then DBMS_OUTPUT.PUT_LINE(‘没有找到员工‘); 
        when others then DBMS_OUTPUT.PUT_LINE(‘其他例外‘); 
end;
/

5. 案例集锦

5.1 运用瀑布模型完成PLSQL程序的设计

需求分析
	设计（1.概要设计   2. 详细设计）
			编码（codeing）
					测试 （testing）
						上线
						

SQL语句
变量：1.初始值是多少
	2.最终值如何得到

5.2 案例：统计每年入职的员工人数

SQL语句:
select to_char(hiredate,‘yyyy‘) from emp;
-> 光标-> 循环-> 退出条件：notfound
变量： 初始值   最终值如何得到
每年入职的员工人数
count80 number:=0;
count81 number:=0;
count82 number:=0;
count87 number:=0;

----------------------------------------------------------------------------------------

set  serverout on
declare
    cursor cemp is select to_char(hiredate,‘yyyy‘)  from emp;
    phiredate varchar2(4);
    count80 number:=0;
    count81 number:=0;
    count82 number:=0;
    count87 number:=0;
begin
  open cemp;
    loop
        fetch cemp into phiredate;
        exit when cemp%notfound ;
        if phiredate=‘1980‘  then count80:=count80+1;
        elsif phiredate=‘1981‘  then count81:=count81+1;
        elsif phiredate=‘1982‘  then count82:=count82+1;
        else count87:=count87+1;
        end if;
    end loop;
 close cemp;
 DBMS_OUTPUT.PUT_LINE(‘Total:‘||(count80+count81+count82+count87)); 
 DBMS_OUTPUT.PUT_LINE(‘count80:‘||count80); 
 DBMS_OUTPUT.PUT_LINE(‘count81:‘||count81); 
 DBMS_OUTPUT.PUT_LINE(‘count82:‘||count82); 
 DBMS_OUTPUT.PUT_LINE(‘count87:‘||count87); 
    exception 
        when no_data_found then DBMS_OUTPUT.PUT_LINE(‘没有找到员工‘); 
        when others then DBMS_OUTPUT.PUT_LINE(‘其他例外‘); 
end;
/

5.3 案例：员工涨工资问题

为员工涨工资。从最低工资涨起每人涨10%，但工资总额不能超过5万元，请计算涨工资的人数和涨工资后的工资总额，并输入涨工资人数及工资总额。

SQL语句
select empno,sal from emp order by sal ;
-> 光标-> 循环-> 退出条件：1.工资总额>5W   2. %notfound

变量： 初始值   最终值如何得到
涨工资的人数
countEmp number:=0;
涨后的工资总额:
	salTotal number;
	1.select sum(sal) into salTotal from emp;
	2.涨后的工资总额=涨前的工资总额+sal*0.1;
	
-----------------------------------------------------------------------------------------------------------------
set  serverout on
declare
    cursor cemp is select  empno,sal  from emp order by sal;
    pempno emp.empno%type;
    psal emp.sal%type;
    
    countEmp number :=0;
    salTotal number;
    
begin
    select sum(sal) into salTotal from emp;
  open cemp;
    loop
        exit when salTotal>50000; 
        	fetch cemp into pempno,psal;
        exit when cemp%notfound; 
        	update emp set sal=sal*1.1 where empno=pempno;
        	countEmp:=countemp+1;
        	salTotal:=salTotal +psal*0.1;
    end loop;
 close cemp;
 commit;
 	DBMS_OUTPUT.PUT_LINE(‘人数：‘||countEmp); 
 	DBMS_OUTPUT.PUT_LINE(‘salTotal: ‘||salTotal); 
end;
/

5.4 案例：涉及两张表的员工涨工资问题

按部门分段（6000以上、（6000,3000）、3000元以下）统计各工资段的职工人数、以及各部门的工资总额（工资总额中不包括奖金）


SQL语句
1.有哪些部门
select deptno  from dept;
--> 光标-> 循环-> 退出条件：%notfound
2.部门中员工的薪水
select sal from emp where deptno=?  
--> 带一个参数的光标-> 循环-> 退出条件：%notfound
变量： 初始值   最终值如何得到
每个段的员工人数
count1 number;
count2 number;
count3 number;
每个部门的工资总额：
saltotal number;
	1.select sum(sal) into saltotal from emp where deptno=???
	2.累加
	

----------------------------------------------------------------------------------
create table msg(deptno number,count1 number,count2 number,count3 number,saltotal number);

set  serverout on
declare
   cursor cdept is select deptno from dept;
    pdeptno dept.deptno%type;
    cursor cemp(dno number) is select sal from emp where deptno =dno;
    psal emp.sal%TYPE;
    count1 number;
    count2 number;
    count3 number;
    saltotal number;
begin
   open cdept;
    loop
        fetch cdept into pdeptno;
        exit when cdept%notfound;
        count1:=0;count2:=0;count3:=0;
        select sum(sal) into saltotal from emp where deptno=pdeptno;
        open cemp(pdeptno);
            loop
                fetch cemp into psal;
                exit when cemp%notfound;
                    if psal <3000 then count1:=count1+1;
                        elsif psal>=3000 and psal <6000 then count2:=count2+1;
                        else count3:=count3+1;
                    end if;
            end loop;
        close cemp;
     insert into msg values(pdeptno,count1,count2,count3,nvl(saltotal,0));
     
    end loop;
    
    
   close cdept;
   commit;
 DBMS_OUTPUT.PUT_LINE(‘统计完成‘); 
end;
/

PL/SQL基础

标签：超时   etc   eve   基础   概述   output   timeout   otf   varchar2