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未返回行

...beginexception    when NO_DATA_FOUND then    dbms_output.put_line('没有返回数据');end;如果在再此基础上添加一个超过结果集的异常,那么...beginexception    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 varchar2is    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)isbegin    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  :newInsert      所有字段都是空(null)          将要插入的数据Update      更新以前该行的值          更新后的值delete      删除以前该行的值          所有字段都是空(null)

前置触发器案例:

创建语句级触发器insertEmpTrigger，当对表【emp】进行增加【insert】操作前【before】，显示"hello world"

create or replace tirgger insertEmpTriggerbeforeinserton empbegin    dbms_output.put_line('hello wordl');end;