Oracle数据库开发之PL/SQL基础

pl/sql是过程语言（Procedural  Language）与结构化查询语句（sql）结合而成的编程语言

pl/sql是对sql的扩展

支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构

可用于创建存储过程、触发器和程序包，给sql语言的执行添加程序逻辑

与Oracle服务器和Oracle工具紧密集成，具备可移植性、灵活性和安全性

======================================================================

定义基本变量：

类型：char、varchar2、date、number、boolean、long

举例：var1  char(15);

            married  boolean :=true;

            psal  number(7,2);

引用型变量：my_name emp.ename%type;

                         psal  emp.sal%type;

select ename,sal into my_name,psal from emp where empno=7839;

记录型变量：emp_rec  emp%rowtype;

select * into emp_rec from emp where empno=7839;

PL/SQL的程序结构：

declare

         说明部分（变量说明、光标申明、例外说明）

begin

          语句序列（DML语句）

exception

          例外处理语句

end;

/

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');
    else dbms_output.put_line('其他数字');
end if;
end;
/

循环语句：

WHILE  total <= 25000 LOOP

.....

total := total+salary;

END LOOP;

--使用while循环打印数字的1~10
set serveroutput on

declare

pnum number :=1;

begin

while pnum <=10 loop

--循环体

dbms_output.put_line(pnum);

--使该变量加1

pnum :=pnum + 1;

end loop;

end;
/

Loop

EXIT [when  条件];

......

End  loop;

 

--使用loop循环打印1~10
set serveroutput on

declare

pnum number :=1;

begin

loop

--退出条件：循环变量大于10

exit  when pnum > 10;

--打印该变量的值

dbms_output.put_line(pnum);

pnum := pnum + 1;

end  loop;

end;
/

 FOR  I  IN  1..3  LOOP

语句序列;

END  LOOP;

 

--使用for循环打印1~10
set serveroutput on
declare
--定义循环变量
pnum number :=1;

begin

for pnum in 1..10 loop

dbms_output.put_line(pnum);

end loop;

end;
/

 光标：

就是一个结果集（Result  Set）

修改光标数的限制：

alter  system set  open_cursors = 400 scope = both;

scope的取值：both、memory、spfile(数据库需要重启)

/*
1.光标的属性
   %found    %notfound     %isopen：判断光标是否打开    %rowcount：影响的行数    光标数的限制：默认情况下，oracle数据库只允许在同一个会话中，打开300个光标
*/

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;

--思考：1.循环什么时候退出？2.fetch不一定能取出记录
exit when cemp%notfound;

--打印
dbms_output.put_line(pename||'的薪水是'||psal);

end  loop;

close cemp;
end;
/

--给员工涨工资，总裁1000，经理800，其他400
--打开屏幕的输出开关
set serveroutput on

declare
--定义光标代表给哪些员工涨工资
-- alter table "SCOTT"."EMP" rename column "JOB" to empjob;
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 = 'PRESIENT' 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;

--对于Oracle，默认的事物隔离级别是read committed
--事物的ACID
commit;

dbms_output.put_line('涨工资完成');

end;
/

查询某个部门中员工的姓名
set serverout 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;
/

例外：

 例外是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性。

系统例外：No_data_found(没有找到数据)、Too_many_rows（select....into语句匹配多个行）、Zero_Divide(被零除)、Value_error(算数或转换错误)、Timeout_on_resource（在等待资源时发生超时）

--系统例外：no_data_found
set serveroutput on

declare
pename emp.ename%type;

begin
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;
/

--系统例外：too_many_rows
set serveroutput on

declare
pename emp.ename%type;

begin
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;
/

--系统例外之被零除 zero_divide
set serveroutput on

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;
/

--系统例外：value_error
set serveroutput on

declare
--定义一个number类型的变量
pnum number;

begin
pnum :='abc';

exception
when value_error then dbms_output.put_line('value_error exception');
when others then dbms_output.put_line('others exception');

end;
/

自定义例外：

--自定义例外：查询50号部门的员工姓名
set serveroutput 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;

--关闭光标
--oracle自动启动pmon(process monitor)
close cemp;

exception
   when no_emp_found then dbms_output.put_line('没有找到员工');
   when others then dbms_output.put_line('其他例外');
end;
/   

程序设计方法：

瀑布模型：

需求分析、设计：1.概要设计  、编码、测试、上线

                                2.详细设计

set serveroutput 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('1980:'||count80);
dbms_output.put_line('1981:'||count81);
dbms_output.put_line('1982:'||count82);
dbms_output.put_line('1987:'||count87);

end;
/

set serveroutput 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;
--%notfound
exit when cemp%notfound;
--涨工资
update emp set sal=sal*1.1 where empno=pempno;
--人数+1
countEmp :=countEmp + 1;
--涨后的工资总额=涨前的工资总额 + sal*0.1
salTotal := salTotal + psal*0.1;
end loop;
--关闭光标
close cemp;
commit;
--打印结果
dbms_output.put_line('人数:'||countEmp||    '涨后的工资总额：'||salTotal);
end;
/