oracle语句编写,Oracle编程

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的作用是标准此触发器是行级触发器，不写的话是语句级触发器