Oracle编程

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/wangchunyang51/article/details/79087257

1.PL/SQL

--基本语法结构
[declare
--声明变量
]
begin
--代码逻辑
[
--异常处理
]
end;

--变量
声明变量:变量名 类型(长度);
赋值变量:变量名:=变量值
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:=9213
v_usenum2:=round(v_usenum / 1000 ,2)
v_money := v_price * v_usenum2;
DBMS_OUTPUT.putline('account:' || v_money);

end;

–select 列名 into 变量名

declare
v_price Number(10,2);--单价
v_usenum number;--水费字数
v_num0 number;--上月字数
v_num1 number;--本月字数
v_usenum2 number(10,2)--使用吨数
v_money number(10,2);--水费金额
begin
v_price :=2.45
//从数据库提取
select usenum,num0,num1 into v_usenum,v_num0,v_num1 from T_CCOUNT
where year=‘2012And month=‘01And owneruuid = 1;

v_usenum2:=round(v_usenum / 1000 ,2)
v_money := v_price * v_usenum2;
DBMS_OUTPUT.putline('account:' || v_money);

end;

–属性类型(引用型 表名.列名%type)

declare
v_price Number(10,2);--单价
v_usenum t_account.usenum%type;--水费字数
v_num0 t_account.num0%type;--上月字数
v_num1 t_account.num1%type;--本月字数
v_usenum2 number(10,2)--使用吨数
v_money number(10,2);--水费金额
begin
v_price :=2.45
select usenum,num0,num1 into v_usenum,v_num0,v_num1 from T_CCOUNT
where year=‘2012And month=‘01And owneruuid = 1;

v_usenum2:=round(v_usenum / 1000 ,2)
v_money := v_price * v_usenum2;
DBMS_OUTPUT.putline('account:' || v_money);

end;

–属性类型(纪录性:代表某个表的某一行的类型 表名%type)

declare
v_price Number(10,2);--单价

v_usenum2 number(10,2)--使用吨数
v_money number(10,2);--水费金额
v_account t_account%rowtype;

begin
v_price :=2.45
select * into v_account from T_CCOUNT
where year=‘2012And month=‘01And owneruuid = 1;

v_usenum2:=round(v_account.usenum / 1000 ,2)
v_money := v_price * v_usenum2;
DBMS_OUTPUT.putline('account:' || v_money);

end;

–异常
1.预定义异常:当PLSQL程序违反Oracle规则或超越系统限制时隐式引发。
2.用户定义异常:用户可以在PLSQL块的声明部分定义异常,自定的异常通过RAISE语句显式引发。

declare
v_price Number(10,2);--单价

v_usenum2 number(10,2)--使用吨数
v_money number(10,2);--水费金额
v_account t_account%rowtype;

begin
v_price :=2.45
select * into v_account from T_CCOUNT
where year=‘2012And month=‘01And owneruuid = 1000;
//报错:no data found


v_usenum2:=round(v_account.usenum / 1000 ,2)
v_money := v_price * v_usenum2;
DBMS_OUTPUT.putline('account:' || v_money);

exception
when NO_DATA_FOUND then DBMS_OUTPUT.putline('没有找到相应的数据')
when too_many_rows then DBMS_OUTPUT.putline('返回多行数据')

end;

–判断语句

if ... then 
elsif ... then
else
end if;

–循环语句

loop
--无条件循环
END loop;

eg:无条件循环输出1-100
declare 
v_num number := 1;
begin
loop
 DBMS_OUTPUT.putline(v_num);
 v_num:=v_num+1;
 exit when v_num > 100;
 end loop;
 end;

–条件循环

while 条件
loop
END loop;

eg:有条件循环输出1-100
declare 
v_num number;
begin
    v_num:=1;
    while v_num<=100
    loop
     DBMS_OUTPUT.putline(v_num);
     v_num:=v_num+1;
    end loop
end;

–for 循环

declare

begin
    for v_num 1..100
    loop
    DBMS_OUTPUT.putline(v_num);
    end loop;
end

–游标语法
游标是系统为用户开设的一个数据缓冲区,存放SQL语句的执行结果,我们可以把游标理解为PL/SQL中的结果集。

在声明区声明游标,语法如下:
cursor 游标名称 is SQL语句;
使用游标语法
Open 游标名称
loop
    fetch 游标名称 into 变量
    exit when 游标名称%notfound
END loop;
close 游标名称

eg:
declare
    cursor cur_pricetable is select * from t_pricetable where ownertypid=1;
begin
 open cur_pricetable;
     loop
         fetch cur_pricetable into v_pricetable
         exit when cur_pricetable%notfound;
         DBMS_OUTPUT.putline('price:' || v_pricetable);

    end loop;
end;

eg:带参数的游标
eg:
declare
    cursor cur_pricetable(v_ownertype number) is select * from t_pricetable where ownertypid=v_ownertype;
    v_pricetable t_pricetable%rowtype;
begin
 open cur_pricetable;
     loop
         fetch cur_pricetable into v_pricetable
         exit when cur_pricetable%notfound;
         DBMS_OUTPUT.putline('price:' || v_pricetable);

    end loop;
end;

eg:for循环带参数游标
declare 
cursor cur_pricetable(v_type number) is select * from t_pricetable where ownertypeid=v_type;
begin
for v_pricetable in cur_pricetable(1);
loop
 DBMS_OUTPUT.putline('price:' || v_pricetable);
 end loop;
 end;

2.存储函数

--存储函数:自定义函数,可以接受一个或者多个参数,返回一个结果。在函数中我们可以使用PLSQL进行逻辑处理。

--存储函数的语法结构
create [or replace] FUNCTION foo_name(参数名称 参数类型,参数名称 参数类型)

return 结果变量数据类型
IS
变量声明部分;
BEGIN
逻辑部分;
RETURN 结果变量;
[Exception 异常处理部分]
end;

eg:创建存储函数,根据地址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;

TEST:select fn_getaddress(3) from dual;

3.存储过程

存储过程是被命令的PLSQL块,存储与数据库中,是数据库对象的一种。
应用程序可以调用存储过程,执行相应的逻辑。
存储过程与存储函数都可以封装一定的业务逻辑并返回结果,存在区别如下:
1.存储函数中有返回值,且必须返回;而存储过程没有返回值,可以通过传出参数返回多个值。
2.存储函数可以在select语句中直接使用,而存储过程不能,过程过多时被应用程序多调用。
3.存储函数一般都是封装一个查询结果,而存储过程一般都封装一段事务代码。

--存储过程语法结构
CREATE [OR REPLACE] PROCEDURE 存储过程名称
(参数名 类型,参数名 类型,参数名 类型)
IS|AS
变量声明部分;
BEGIN
逻辑部分
[EXCEPTION 异常处理部分]
END;
参数指定类型,不指定长度
过程参数的三种模式:
IN 传入参数(默认)
OUT 传出参数,主要用于返回程序运行结果
IN OUT 传入传出参数

--案例
1.创建不带传出参数的存储过程
create sequence seq_owners start with 11;
create or replace procedure pro_owners_add
(v_name varchar2,v_addressid number,v_housenumber varchar2,v_watermeter varchar2,v_ownertypeid number)
is
begin
insert into t_owners values(seq_owners.nextval,v_name,v_addressid);
commit;
end;

调用不带传出参数的存储过程
call pro_owners_add(‘tommy’,2,‘23333’,‘67777’,1);

2.创建带传出参数的存储过程
create sequence seq_owners start with 11;
create or replace procedure pro_owners_add
(v_name varchar2,v_addressid number,v_housenumber varchar2,v_watermeter varchar2,v_ownertypeid 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);
commit;
end;

--调用传出参数的存储过程
declare
v_id number;
begin
    pro_owners_add('tommy2',2,'2333','2333333',1,vid);
    DBMS_output.putline(v_id);
end

4.触发器

触发器是一个与表相关联的,存储的PLSQL程序,每当一个特定的数据操作语句(insertupdatedelete)在指定的表上发出时,Oracle自动的执行触发器中定义的语句序列。

触发器可用于:

 - 数据确认
 - 实施复杂的安全检查
 - 做审计,跟踪表上所有的数据操作等
 - 数据的备份和同步
 触发器分类:

 - 前置触发器
 - 后知触发器

 --创建触发器的语句
 CREATE [OR REPLACE] TRIGER 触发器名称
 BEFORE | AFTER
 [DELETE ] [(or) INSERT] [[OR] UPDATE [OR 列名]]
 ON 表名
 [FOR EACH ROW ][WHEN 条件]
 declare
 ...
 begin
 PLSQL 块
 End;
//FOR EACH ROW作用是标注此触发器是行级触发器, 语句触发器
在触发器中触发语句与未记录变量的值
Insert 所有字段都是空 将要插入的数据
Update 更新以前该行的值 更新后的值
delete 删除以前该行的值 所有字段都是空

--前置触发器
create or replace trigger tri_account_num1
before 
update of num1
on t_account
for each row
declare

begin
--通过未记录变量修改usenum字段的值
:new.usenum:= :new.num1-:new.num0;
end; 

--后置触发器
create table t_owner_log(
updatetime datee,
ownerid number,
oldname varchar2(30),
newname varchar2(30)
);
--创建后置触发器,自动记录业主更改前后日志
create trigger tri_owners_log
after
Update of name
on t_owners
for each row
declare

begin
--向日志表插入记录
insert into t_owners_log values(sysdate,:new.id,:old.name,:new.name);

end;

--验证
update t_owners set name='tommy' where id=4;
展开阅读全文

没有更多推荐了,返回首页