Oracle编程

2017-10-19 本文已影响0人 Percy_Gemini

Oracle编程

序

任意语言的三个基本结构：顺序结构、分支结构、循环结构。任意语言都拥有变量和常量。

PL/SQL

PL/SQL的基本概念

PL/SQL（Procedure Language/SQL），是Oracle对Sql语言的过程化拓展，是一门语言，可以让SQL具备逻辑，具有过程处理能力。

基本语法结构

[declare --声明变量]
begin
[plsql代码]
[exception --异常处理]
end;
--常见的简单模式
begin
    plsql
end

基本元素

变量

注意事项：

变量名变量类型
赋值符号 :=
declare后边声明变量，而变量的赋值在begin和end之间
内置存储函数（过程）：DBMS_OUTPUT.PUTLINE()

案例

--变量声明与赋值
declare --声明
    v_price number(10,2);
    v_usenum number;
    v_usenum2 number(10,2);
    v_money(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.put_line('金额：'||v_money);--内置存储函数，可以输出
end;

--第二种赋值方法

declare --声明
    v_price number(10,2);
    v_usenum number;
    v_usenum2 number(10,2);
    v_money(10,2);
    v_num0 number;
    v_num1 number;
begin
    v_price:=2.45;
    --语句：select 【数据库中的列名】 into 变量名称 
    select usenum into v_usenum from t_account where year='2012' and monyh='01' and owneruuid=1;
    v_usenum:=9213;
    v_usenum2:=round(v_usenum/1000,2);
    v_money:=v_price*v_usenum2;
end

引用类型

语法格式：表名.列名%type

declare --声明
    v_price number(10,2);
    --可以直接查找到表中的值的类型
    v_usenum t_account.usenum%type;
    v_usenum2 number(10,2);
    v_money(10,2);
    v_num0 number;
    v_num1 number;
begin
    v_price:=2.45;
    select usenum into v_usenum from t_account where year='2012' and monyh='01' and owneruuid=1;
    v_usenum:=9213;
    v_usenum2:=round(v_usenum/1000,2);
    v_money:=v_price*v_usenum2;
end

记录类型

语法格式：变量名称表名%rowtype

使用时语句：变量名称 . 列名

declare --声明
    v_price number(10,2);
    --可以直接查找到表中的值的类型
    v_usenum t_account.usenum%type;
    v_usenum2 number(10,2);
    v_money(10,2);
    v_num0 number;
    v_num1 number;
begin
    v_price:=2.45;
    select usenum into v_usenum from t_account where year='2012' and monyh='01' and owneruuid=1;
    v_usenum:=9213;
    v_usenum2:=round(v_usenum/1000,2);
    v_money:=v_price*v_usenum2;
end

异常

在运行程序时出现的错误叫做异常（也叫例外）

预定义异常
自定义异常

预定义异常共有21个

--语法格式
exception
    when 异常类型 then
    异常处理逻辑

案例

declare
    v_price number(10,2);-- 水费单价
    v_usenum T_ACCOUNT.USENUM%type; -- 水费字数
    v_usenum2 number(10,3);-- 吨数
    v_money number(10,2);-- 金额
begin
    v_price:=2.45;-- 水费单价
    select usenum into v_usenum from T_ACCOUNT where
    owneruuid=1 and year='2012' and month='01';
    -- 字数换算为吨数
    v_usenum2:= round( v_usenum/1000,3);
    -- 计算金额
    v_money:=round(v_price*v_usenum2,2);
    dbms_output.put_line('单价：'||v_price||'吨
    数:'||v_usenum2||'金额：'||v_money);
exception
    --只要查到这个异常就会处理
    when NO_DATA_FOUND then
    dbms_output.put_line('未找到数据，请核实');
    when TOO_MANY_ROWS then
    dbms_output.put_line('查询条件有误，返回多条信息，请核实');
end;

记忆两个

NO_DATA_FOUND：没有找到数据

TOO_MANY_ROWS：结果集超过一行

分支结构

条件判断

--sql中的条件判断语句
if 条件 then
end if;

if 条件 then
else
end if；

if 条件 then
elsif 条件 then
。。。
else
end if；

案例：

declare
    v_price1 number();
    v_price2 number();
    v_price3 number();
    v_usenum2 number();
    v_money number();
    v_account t_account%rowtype;
--阶梯水费计算
begin
    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*(v_usenum2-5)+v_price3*(v_usenum2-10)

循环

循环关键字：loop 循环语句 end loop;

--loop无条件循环
loop
    循环语句
end loop;

--案例
declare
    v_num number;
begin
    v_num:=1;
    loop
        dbms_output.put_line(v_num);
        v_num:=v_num+1;
        --这是一个死循环，加条件推出
        if v_num>100 then
            exit;
        (或者：exit when v_num>100;)
    end loop;
end;

--loop有条件循环
while 满足循环的条件
loop
    循环语句
end loop;

--案例
declare
    v_num number;
begin
    v_num:=1;
    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;

--案例
declare
    v_num number;
begin
    for v_num in 1..100
    loop
        dbms_output.put_line(v_num);
    end loop;
end;

游标

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。

游标的作用

速度快！2017年开发人员基本不用游标了，但是在今后的学习中还要涉及到。

申明游标语法

cursor 游标名称 is SQL语句

使用游标语法

open 游标名称
loop
    fetch 游标名称 into 变量
    --返回一个布尔值
    exit when 游标名称%notfound
end loop；
close 游标名称

案例

--打印业主类型为1的价格表
--查表语句
select * from t_pricetable where ownertypeid=1

--游标
declare
    cursor cur_pricetable is select * from t_pricetable where ownertypeid=1;--声明游标
    v_pricetable t_pricetable%rowtype;
begin
    open cur_pricetable;
        loop 
            fetch cur_pricetable into v_pricetable;
            exit when cur_pricetable%notfound;
            dbms_output.put_line('价格：'||v_pricetable.price||'吨位：'||v_pricetable.minnum||'到'||v_pricetable.maxnum);
        end loop；
    close cur_pricetable;

附：步骤

PLSQL结构

声明游标

打开--关闭游标

循环取记录

带参数的游标

声明的时候指定参数的类型和名称，打开游标的时候需要传入指定类型的参数即可。

案例：

declare
    v_pricetable T_PRICETABLE%rowtype;-- 价格行对象
    cursor cur_pricetable(v_ownertypeid number) is select *from T_PRICETABLE where ownertypeid=v_ownertypeid;-- 定义游
标
begin
    open cur_pricetable(2);-- 打开游标
    loop
        fetch cur_pricetable into v_pricetable;-- 提取游标到变量
        exit when cur_pricetable%notfound;-- 当游标到最后一行下面退出循环
        dbms_output.put_line('价格:'||v_pricetable.price ||'吨位：'||v_pricetable.minnum||'-'||v_pricetable.maxnum );
    end loop;
    close cur_pricetable;-- 关闭游标
end ;

For循环游标[掌握使用]

declare

begin
    for v_pricetable in cur_pricetable(3)
end

存储函数/存储过程

存储函数

Oracle中提供的使用PLSQL语言自定义的一些函数称之为存储函数

语法格式

CREATE [or replace] function 函数名称
（参数名称 参数类型，参数名称 参数类型，...）
    return 结果变量数据类型
is
    变量声明部分
begin
    逻辑部分
    return 结果变量
[Exception]
end；

案例

--通过ID查找小区
create or replace 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 id,name,fn_getaddress(addressid) from t_owners

存储过程

概念

存储过程和存储函数差不多，和存储函数相比，它没有返回值，可以通过传出参数返回多个值；存储过程不能在select语句中直接使用，它多数是被应用程序调用。

存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如下：

存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过传出参数返回多个值。

存储函数可以在 select 语句中直接使用，而存储过程不能。过程多数是被应用程序所调用。

存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码。

语法格式

create [or replace] procedure procedure名称 (参数名 参数类型，...)
is[as]--都可以用
    变量声明
begin
    具体逻辑
[Exception]
end;

参数只指定类型，不指定长度
过程参数的三种模式：
IN 传入参数（默认）
OUT 传出参数，主要用于返回程序运行结果
IN OUT 传入传出参数

案例

--创建不带传出参数的存储过程：添加业主信息
--增加业主信息
create or replace procedure pro_owners_add()

使用

--调用不带参数的存储过程
--一
call 存储过程名(实际参数。。。);
--二
begin
    存储过程的名称（实参）
end;

用JDBC调用

--带传出参数的存储过程
create or replace procedure pro_owners_add()

触发器

已经淘汰，现在都用消息队列

对特定表（增删改---生产者消费者思想---中介思想）

概念： 数据库触发器就是一段sql程序

触发器可用于

数据确认
实施复杂的安全性检查
做审计，跟踪表上所做的数据操作等
数据的备份和同步

触发器的分类

前置触发器（BEFORE）
后置触发器（AFTER）

面向切面的AOP思想

创建触发器的语法

create [or replace] trigger 触发器名
    before|after
    [delete][[or] insert][[or]update[of 列名,...]]
    on 表名
    [for each row][when(条件)]
declare
    ...
begin
    PLSQL 块
end；
--for each row的作用是标准此触发器是行级触发器，不写的话是语句级触发器

Oracle编程

Oracle编程

序

PL/SQL

PL/SQL的基本概念

基本语法结构

基本元素

分支结构

存储函数/存储过程

存储函数

存储过程

触发器

猜你喜欢

热点阅读