oracle pl/sql编程基础
(一)pl/sql块优点:
(1)提高运行效率
(2)提高模块化的程序设计功能
(3)允许标示符定义
(4)具有过程语言控制结构
(5)良好的兼容性(内置程序包)
(6)处理运行错误
2.SQL语言包括:
(1)数据查询语句(select)
(2)DML数据操纵语句(insert,update,delete)
(3)事务控制语言(commit,rollback,savepoint)
(4)数据定义语言(create,alter,drop)
(5)DCL数据控制语言(grant,revoke)等
注意:当编写PL/SQL应用程序时,只能直接嵌入select语句,dml语句和事务控制语句
3.PL/SQL块语法:
declare
变量,常量,游标,例解
begin
执行PL/SQL,SQL语句
exception
end;
4.PL/SQL块分为:无名块,匿名块,有名块(包含在存储过程或者函数中)
(1)例如:有名的存储过程块无参数的存储过程
create or replace procedure proc_name
as
begin
dbms_output.put_line(systimestamp);
end;
declare
begin
proc_name;
end;
结果为:27-12月-13 06.22.00.484000000 下午 +08:00
(2)无名块
declare
v_ename varchar2(20);
begin
select ename into v_ename from scott.emp where empno=&empno;
dbms_output.put_line('员工姓名:'||v_ename);
exception
when no_data_found then
dbms_output.put_line('请输入正确的员工号!') ;
end;
输入7369,结果为:员工姓名:SMITH
(二) 标量类型(varchar2(),char(),number(p,s),date,timestamp,boolean)
(1)计算员工的工资所得税(普通版)
declare
v_ename varchar2(20);
v_sal number(6,2);
c_tax_rate constant number(3,2) :=0.03;
v_tax_sal number(6,2);
begin
select ename,sal into v_ename,v_sal from scott.emp where empno=&empno;
v_tax_sal :=v_sal * c_tax_rate;
dbms_output.put_line('员工姓名:'||v_ename);
dbms_output.put_line('员工工资:'||v_sal);
dbms_output.put_line('所得税:'||v_tax_sal);
end;
输入7369,结果为:员工姓名:SMITH,员工工资:800,所得税:24
(2)使用 %type 属性计算员工的工资所得税(%type版本)
declare
v_ename scott.emp.ename%type; --记录员工姓名
v_sal number(6,2); --记录员工工资
c_tax_rate constant number(3,2):=0.03; --设置所得税税率
v_tax_sal number(6,2); --计算后的所得税
begin
/*******************************
输入员工编号通过计算输出员工所得税
***********************************/
select ename,sal into v_ename,v_sal from scott.emp where empno=&empno;
v_tax_sal:=v_sal * c_tax_rate;
dbms_output.put_line('员工姓名:'||v_ename);
dbms_output.put_line('员工工资:'||v_sal);
dbms_output.put_line('所得税:'||v_tax_sal);
end;
输入7369,结果为:员工姓名:SMITH,员工工资:800,所得税:24
(3)record的使用
declare
type r_name is record(
v_dname scott.dept.dname%type,
v_loc scott.dept.loc%type
);
res r_name;
begin
select dname,loc into res.v_dname,res.v_loc
from scott.dept where scott.dept.deptno=10;
dbms_output.put_line(res.v_dname||res.v_loc);
end;
结果为:ACCOUNTINGNEW YORK
(三)PL/SQL 控制结构(PL/SQL不能嵌入sql语句)
(1)条件分支语句(语法)
/*
if 条件 then
begin
处理语句
end;
elsif
begin
处理语句
end;
end if;
*/
例题:为工资小于2000的员工增加工资200元
declare
v_sal number(6,2);
begin
select sal into v_sal from scott.emp where ename=trim('&ename');
if v_sal < 2000 then
update scott.emp set sal=v_sal + 200 where ename = trim('&ename');
end if;
end;
select * from scott.emp;
(2)case语句(单一使用)
/*语法:
case selector --sekector为条件选择符
when 表达式 then 处理语句
when 表达式 then 处理语句
[else sequence_of_statementN+1;]
end case;
*/
例题:在case语句中使用单一条件,更新相应部门的员工的补贴(根据deptno)
declare
v_deptno scott.emp.deptno%type;
begin
v_deptno := &deptno;
case v_deptno
when 10 then
update scott.emp set comm=100 where deptno=v_deptno;
when 20 then
update scott.emp set comm =80 where deptno=v_deptno;
when 30 then
update scott.emp set comm =60 where deptno=v_deptno;
else
dbms_output.put_line('不存在该部门');
end case;
end;
select * from scott.dept;
case语句中使用多种条件
/*
语法:
case 条件选择符
when 条件 then 处理语句
when 条件 then 处理语句
......
when 条件 then 处理语句
[else sequence_of_statementN+1;]
end case;
*/
例题:依据不同的工资金额来更新员工的补贴(改变comm值)
declare
v_sal scott.emp .sal%type;
v_ename scott.emp.ename%type;
begin
select ename,sal into v_ename,v_sal from scott.emp where empno=&empno;
case
when v_sal < 2000 then
update scott.emp set comm=100 where ename=v_ename;
when v_sal < 3000 then
update scott.emp set comm=80 where ename=v_ename;
when v_sal < 4000 then
update scott.emp set comm=50 where ename=v_ename;
end case;
end;
select * from scott.emp;
(四)循环语句
(1)loop循环(至少会执行一次)
/*
语法:loop statement1
exit when 表达式
end loop;
*/
例题:基本循环(1-9)
declare
i int :=1;
begin
loop
dbms_output.put_line(i);
i :=i+1;
exit when i=10;
end loop;
end;
(2)while循环
/*
语法:
while 表达式 loop
处理语句
end loop;
*/
例题:while循环(1-9)
declare
i int :=1;
begin
while i<10 loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;
(3)for循环
/*
语法:
for 循环控制变量 in [reverse] lower_bound..upper_bound loop
处理语句
end loop;
*/
例题:for循环(1-10)
declare
i int :=1;
begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;
(五)异常处理
1.预定义异常
(1)case_not_found:对应ORA-06529,case语句中没有分支
(2)cursor_already_open:对应ORA-06511,游标已经打开,不能再打开
(3)invalid_number:对应ORA-01722,数字转换异常
(4)too_many_rows:对应ORA-01422,当执行select into字句时,如果返回超过一行
就出发该异常
(5)zero_divide:对应ORA-01476,除数为0时
(6)no_data_found:对应ORA-01403,执行select into未返回行,或引用索引表未初始化元素
2.非预定义异常
3.自定义异常
/*语法:
exception
when 异常 then
...
when 异常 then
...
when other then
*/
(1)例题:处理预定义异常
declare
v_ename scott.emp.ename%type;
begin
select ename into v_ename from scott.emp where empno=&empno;
dbms_output.put_line(v_ename);
exception
when no_data_found then
dbms_output.put_line('员工编号不正确,请输入正确的员工编号');
end;
输入一个scott.emp表中没有的编号2563,触发异常
结果为:员工编号不正确,请输入正确的员工编号
(2):处理自定义异常
declare
e_integerity exception ;--定义非预定义异常
e_no_employee exception; --定义自定义异常
pragma exception_init(e_integerity,-2291);--关联非预定义异常
begin
update scott.emp set deptno=40 where empno=-1;
if SQL%notfound then
raise e_no_employee; --显示触发自定义异常
end if;
exception
when e_integerity then
dbms_output.put_line('该部门不存在');
when e_no_employee then
dbms_output.put_line('该员工不存在');
end;
结果为:该员工不存在
(六)游标
