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