Oracle基础进阶
PL/SQL
是Oracle对sql语言的过程化扩展,指在sql命令语言中增加了过程处理语句,是sql语言具有过程处理能力.
语法:
[declare
--声明变量
]
begin
--逻辑代码
[exception
--异常处理
]
变量:
声明变量的语法:变量名 类型(长度)
变量赋值的语法:变量名:=变量值
例如
declare v_price number(10,2);--单价 v_usenum number;--水费数字 v_usenum2 number(10,2);--吨数 v_money number(10,2);--总价 begin v_price:=2.45; v_usenum:=3306; v_usenum2:=round(v_usenum/1000,2); v_money:=v_usenum2*v_price; DBMS_output.put_line('总价:'||v_money); end;
对于变量的赋值,还 可以用:select into方式赋值 select 列名 into 变量名 from 表名 where 条件
但是这种方式的结果必须是一条记录,有多条记录和没有记录都会报错
这里就可以吧上面的水费的数字改成从已经有的表中查找出来
select usernum into v_usenum from t_account where owneruuid=1;
属性类型分为引用型和记录型,首先介绍引用型
%type(引用型):引用某表某列的字段类型
还是上面的例子,水费数字的number可以改为指定表中指定列的引用型类型
v_usenum t_account.usenum%type;
%rowtype(记录型):表示某个表的行记录类型
依旧上面的例子,水费数字等数据可以被替代为:
v_account t_account%rowtype;
这个赋值可以看做是为一个对象赋值一行数据,而这个对象在这里就是v_account
赋值语句:
select * into v_account from t_account where owneruuid = 1;
这样在后面使用的时候直接点调用里面需要的数据
比如要调用水费数字:
v_account.usenum;这就得到了水费数字在原表中的数据
异常:
在运行程序时出现的错误叫做异常
发生异常后,语句将停止执行,控制权转移到PL/SQL块的异常处理部分.
在这里介绍两个预定义异常,预定义异常就是当PL/SQL程序违反Oracle规则或超越系统限制时隐式引发
首先介绍第一个:
NO_DATA_FOUND:使用select into未返回行
TOO_MANY_ROWS:执行select into时,结果集超过一行
exception
when 异常类型 then
异常处理逻辑
还是以上面的为例子,模拟在给水费数字赋值的时候,select into未返回行
... begin exception when NO_DATA_FOUND then dbms_output.put_line('没有返回数据'); end; 如果在再此基础上添加一个超过结果集的异常,那么 ... begin exception when NO_DATA_FOUND then dbms_output.put_line('没有返回数据'); when TOO_MANY_ROWS then dbms_output.put_line('返回信息过多'); end;
条件判断
基本语法1:
if 条件 then
业务逻辑
end if;
基本语法2:
if 条件 then
业务逻辑
else
业务逻辑
end if;
基本语法3:
if 条件 then
业务逻辑
elsif 条件 then
业务逻辑
else
业务逻辑
end if;
下面是一个前面综合的完整列子:
5 吨以下 2.45 元/吨 5 吨到 10 吨部分 3.45 元/吨 ,超过 10 吨部分 4.45 ,根据使用水费的量来计算阶梯水费。
declare v_price1 number(10,2);-- 不足 5 吨的单价 v_price2 number(10,2);-- 超过 5 吨不足 10 吨单价 v_price3 number(10,2);-- 超过 10 吨单价 v_account T_ACCOUNT%ROWTYPE;-- 记录型 v_usenum2 number(10,2);-- 使用吨数 v_money number(10,2);-- 水费金额 begin -- 对单价进行赋值 v_price1:=2.45; v_price2:=3.45; v_price3:=4.45; -- 给v_account赋值 select * into v_account from T_ACCOUNT where year='2012' and month='01' and owneruuid=1; -- 使用吨数 v_usenum2:= round(v_account.usenum/1000,2); -- 计算金额 ( 阶梯水费 ) if v_usenum2<=5 then-- 第一个阶梯 v_money:=v_price1*v_usenum2; elsif v_usenum2>5 and v_usenum2<=10 then -- 第二个阶梯 v_money:=v_price1*5 + v_price2*( v_usenum2-5); else -- 第三个阶梯 v_money:=v_price1*5 +v_price2*5 + v_price3*( v_usenum2-10 ); end if; DBMS_OUTPUT.put_line('金额' || v_money); exception when NO_DATA_FOUND then DBMS_OUTPUT.put_line('没有找到数据'); when TOO_MANY_ROWS then DBMS_OUTPUT.put_line('返回的数据有多行'); end;
循环:
下面是用1-100的循环例子解释三种不同的循环
无条件循环(语法):
loop
-- 循环语句
end loop;
第一种解法:
declare v_num number:=1; begin loop dbms_output.put_line(v_num); v_num:=v_num+1; exit when v_num>100; end loop; end ;
条件循环(语法):
while 条件
loop
end loop;
第二种解法:
declare v_num number:=1; begin while v_num<=100 loop dbms_output.put_line(v_num); v_num:=v_num+1; end loop; end ;
for循环(语法):
for 变量 in 起始值..终止值
loop
end loop;
第三种解法:
begin for v_num in 1..100 loop dbms_output.put_line(v_num); end loop; end;
游标:
游标是系统为用户开设的一个数据缓冲区,存放sql语句的执行结果,可以把游标理解为PL/SQL中的结果集
声明游标的语法:
cursor 游标名称 is SQL 语句;
使用游标的语法:
open 游标名称
loop
fetch 游标名称 into 变量
exit when 游标名称%notfound
end loop;
close 游标名称;
下面的例子使用scott用户操作Oracle中的默认emp表
declare --定义游标 cursor cemp is select ename,sal from emp; --定义变量 vename emp.ename%type; vsal emp.sal%type; begin --打开游标,这时游标位于第一条记录之前 open cemp; --循环 loop --向下移动游标一次 fetch cemp into vename,vsal; --退出循环,当游标下移一次后,找不到记录时,则退出循环 exit when cemp%notfound; --输出结果 dbms_output.put_line(vename||'--------'||vsal); end loop; --关闭游标 close cemp; end;
存储函数
存储函数就是自定义函数,可以接收一个或多个参数,返回一个结果,在函数中我们可以使用P/SQL进行逻辑的处理
创建或修改存储过程的语法:
CREATE [ OR REPLACE ] FUNCTION 函数名称 (参数名称 参数类型, 参数名称 参数类型, ...) RETURN 结果变量数据类型 IS 变量声明部分; BEGIN 逻辑部分; RETURN 结果变量; [EXCEPTION 异常处理部分] END;
下面几个例子:
创建存储函数,根据地质id查询地址名称
create function fn_getaddress(v_id number) return varchar2 is v_name varchar2(30); begin select name into v_name from t_address where id=v_id; return v_name; end;
测试函数:
select fn_getaddress(3) from dual;
存储过程
存储过程是被命名的PL/SQL块,存储于数据库中,是数据库对象的一种.
存储过程语法如下:
CREATE [ OR REPLACE ] PROCEDURE 存储过程名称 (参数名 类型, 参数名 类型, 参数名 类型) IS|AS 变量声明部分; BEGIN 逻辑部分 [EXCEPTION 异常处理部分] END;
过程参数的三种模式:
IN 传入参数(默认)
OUT 传出参数,主要用于返回程序运行结果
IN OUT 传入传出参数
带传出参数的存储过程
-- 增加业主信息序列 create sequence seq_owners; create or replace procedure pro_owners_add ( v_name varchar2, v_addressid number, v_housenumber varchar2, v_watermeter varchar2, v_type number, v_id out number ) is begin select seq_owners.nextval into v_id from dual; insert into T_OWNERS values( v_id,v_name,v_addressid,v_housenumber,v_watermete r,sysdate,v_type ); commit; end;
PL/SQL 调用该存储过程:
declare v_id number;-- 定义传出参数的变量 begin pro_owners_add('王旺旺',1,'922-3','133-7',1,v_id); DBMS_OUTPUT.put_line('增加成功,ID:'||v_id); end;
触发器
数据库触发器是一个与表相关联的,存储的PL/SQL程序,每当一个特定的数据操作语句(insert,update,delete)在
指定的表上发出时,Oracle自动地执行触发器中定义的语句序列
触发器分为前置触发器(before)和后置触发器(after)
创建触发器的语法:
CREATE [or REPLACE] TRIGGER 触发器名 BEFORE | AFTER [DELETE ][[or] INSERT] [[or]UPDATE [OF 列名]] ON 表名 [FOR EACH ROW ][WHEN(条件) ] declare …… begin PLSQL 块 End ;
在触发器中触发语句与伪记录变量的值
触发语句 :old :new Insert 所有字段都是空(null) 将要插入的数据 Update 更新以前该行的值 更新后的值 delete 删除以前该行的值 所有字段都是空(null)
前置触发器案例:
创建语句级触发器insertEmpTrigger,当对表【emp】进行增加【insert】操作前【before】,显示"hello world"
create or replace tirgger insertEmpTrigger before insert on emp begin dbms_output.put_line('hello wordl'); end;