Oracle学习笔记

概念

• 数据库
  • Oracle数据库的概念与mysql的数据库概念不同，Oracle只有一个大的数据库。
• 实例
  • 一个数据库可以有n个实例，实例与实例之间互不干扰。一般只使用一个实例。
• 数据文件（dbf）
  • 数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，而一个表空间可以由一个或多个数据文件祖成，一个数据文件只能属于一个表空间。一旦数据文件被假如到某个表空间后，就不能删除这个文件，除非删除其所属的表空间才行。
• 表空间
  • 表空间是Oracle对物理数据库上相关数据文件（ORA或DBF文件）的逻辑映射。一个数据库在逻辑上被划分为一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每个数据库至少有一个表空间（称之为system表空间）。
  • 每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件（datafile）。
• 用户
  • 与mysql不同，Oracle数据库下可以有很多用户，由用户创建表，每建立一个应用需要新建一个用户。可以把Oracle的用户理解为mysql的数据库。

创建表空间

create tablespace waterboss
datafile '\waterboss.dbf'
size 100m
autoextend on
next 10m

查看所有表空间

select file#,status,name from v$datafile;

创建用户

create user wateruser # 用户名
identified by 123456 # 密码
default tablespace waterboss # 指定用户所属表空间

在Users中查看创建的用户 使用wateruser登录，报错。因为此用户没有权限

用户赋权

给用户赋予dba角色的权限（角色是权限集合的封装）

grant dba to wateruser

可以使用此账户登录了

Oracle常用数据类型

• 字符型
  • CHAR:固定长度的字符类型，最多存储2000个字节（缺点：不足长度使用空格补齐，浪费空间）
  • VARCHAR2:可变长度的字符类型，最多存储4000个字节
  • LONG:大文本类型。最大可以存储2个G。类似mysql的text
• 数值型
  • NUMBER(不指定长度默认是18):例如NUMBER(5) 最大可以存的数为 99999； NUMBER(5,2)表示总共5位，小数占两位
• 日期型
  • DATE:日期时间型，精确到秒
  • TIMESTAMP:精确到秒的小数点后9位
• 二进制型
  • CLOB:存储字符，最大可以存4个G
  • BLOB:存储图像、声音、视频等二进制数据，最多可以存4个G

表的创建

# 创建业主表
CREATE TABLE T_OWNERS(
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(30),
    ADDRESSID NUMBER,
    HOUSENUMBER VARCHAR2(30),
    WATERMETER VARCHAR2(30),
    ADDDATE DATE,
    OWNERTYPEID NUMBER
)

插入数据

insert into T_OWNERS VALUES (1,'张三丰',1,'1-1','123456',sysdate,1);
commit;

修改数据

update T_OWNERS set adddate = adddate - 3 where id=1;
commit;

删除语句

delete from t_owners where id = 1;
commit;

-- 使用truncate删除
truncate table 表名称;

区别：delete删除的数据可以rollback；delete可能产生碎片，且不是放空间；truncate是先摧毁表结构，再重构表结构

JDBC连接Oracle

• /home/oracle/app/oracle/product/11.2.0/dbhome_2/jdbc/lib下获得ojdbc5.jar

import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * 基本数据访问类（获取数据库连接）
 */
public class BaseDao {
    static {
        //加载驱动
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取数据库连接
     * @return
     * @throws SQLException
     */
    public static java.sql.Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:oracle:thin:@IP地址:1521:server name",
                "用户名","密码");
    }

    /**
     * 关闭资源
     * @param rs
     * @param stmt
     * @param conn
     */
    public static void closeAll(java.sql.ResultSet rs,java.sql.Statement stmt,java.sql.Connection conn){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

数据导出与导入

• 整库导入导出效率低，所以这里按用户和表导入导出
• 按用户导入导出

-- 导出
exp 用户名/密码 file='/home/oracle/20230902.dmp' owner=用户名

-- 导入
imp 用户名/密码 file='/home/oracle/20230902.dmp' fromuser=用户名

• 按表导入导出

-- 导出
exp 用户名/密码 file='...' tables=表名,表名....

-- 导入
imp 用户名/密码 file='...' tables=表名,表名...

基于伪列的查询

• 伪列在表中并不存储，只能查询，不能增删改

  • rowid,是一个物理地址

  select rowid,t.* from t_owners t;
  

  • rownum,每一行的行号

  select rownum,t.* from t_owners t
  

SQL笔记

--简单条件
select * from T_OWNERS where watermeter = '30408'

select * from T_OWNERS where (name like '%刘%' or housenumber like '%5%') and ...

select * from t_account where usenum >= 10000 and usenum<= 20000

select * from T_PRICETABLE where maxnum is null

--去重
select distinct addressid,ownertypeid addressid from t_owners

--排序
select * from t_account order by usenum
--伪列
select rowid,t.* from t_owners t

select rownum,t.* from t_owners t

--内连接
select o.id,o.name,ot.name 业主类型,ad.name 地址,ar.name 区域名称,op.name 收费员名称
 from t_owners o,t_ownertype ot,t_address ad,t_area ar,t_operator op 
 where o.ownertypeid = ot.id and o.addressid= ad.id and ar.id = ad.areaid and op.id = ad.operatorid

--左外
--SQL1999语法
select ow.id,ow.name,year,month,money from t_owners ow left join t_account ac
on ow.id = ac.owneruuid
--oracle语法
select ow.id,ow.name,year,month,money from t_owners ow, t_account ac
where ow.id = ac.owneruuid(+)

--右外
select ow.id,ow.name,year,month,money from t_owners ow, t_account ac
where ow.id(+) = ac.owneruuid

--子查询（嵌套查询）
--where子句中的子查询
      --单行子查询（返回一行记录，=,>=,<,>=,<>)
      select * from t_account 
      where year='2012' and month='01' 
      and usenum > (select avg(usenum) from t_account where year='2012' and month='01')
      --多行子查询(查询结果有多条记录，in:等于列表中的任何一个；any：和子查询返回的任意一个值比较；all：和子查询返回的所有值比较)
      例一：select * from t_owners where addressid in (1,3,4)
      例二：select * from t_owners where addressid in (select id from t_address where name like '%花园%')
      例三：select * from t_owners where addressid not in (select id from t_address where name like '%花园%')
--from子句中的子查询（子查询为多行子查询）
      select * from 
      (select o.id,o.name 业主名称,ot.name 业主类型 from t_owners o,t_ownertype ot where o.ownertypeid = ot.id)
      where 业主类型='居民'
--select子句中的子查询（子查询必须为单行子查询）
      //需求：列出业主信息，包括ID，名称，所属地址
      select id,name,(select name from t_address where id=addressid) addressname from t_owners 
      //需求：列出业主信息，包括ID，名称，所属地址，所属区域
      select id,name,
      (select name from t_address where id=ow.addressid) addressname ,
      (select (select name from t_area where id = areaid) from t_address where id = ow.addressid) areaname
      from t_owners ow
      
--分页查询（需要用到伪列ROWNUM和嵌套查询）
      --简单分页
      //注意，rownum后的运算符只能是小于，小于等于，不能是等于，大于或大于等于
      select rownum,t.* from t_account t where rownum<=20
      //使用子查询解决不能使用大于，大于等于的问题
      select * from (select rownum r,t.* from t_account t) where r<=20 and r>10
      --基于排序的分页
      //错误写法，排序后rownum的顺序被打乱，导致结果错误
      select * from (select rownum r,t.* from t_account t order by usenum desc) where r<=20 and r>10
      //正确写法，使用子查询
      select * 
      from (select rownum r,t.* from (select * from t_account t order by usenum desc) t)
      where r<=20 and r>10
--单行函数
      --字符函数
            //求字符串长度(dual是伪表）
            select length('ABCD') from dual;
            //求字符串的子串(源字符串,开始位置，截取长度）
            select substr('ABCD',2,2) from dual
            //字符串拼接
            select concat('ABC','D') from dual;
            select concat(concat('ABC','D'),'EF') from dual; //不推荐concat嵌套
            select 'ABC' || 'D' || 'EF' from dual; //推荐
      --数值函数
            //四舍五入函数 round
            select round(199.234) from dual; //199
            select round(199.534) from dual; //200
            select round(199.237,2) from dual; //199.24
            //数字截取函数 trunc
            select trunc(100.456) from dual; //100
            select trunc(100.456,2) from dual; //100.45
            //取模(求余数) mod
            select mod(10,3) from dual; //1
            select mod(10,2) from dual; //0
      --日期函数
            //获得当前日期 sysdate
            select sysdate from dual; // 2023/9/3 16:27:56
            //加月函数 add_months
            select add_months(sysdate,2) from dual; // 2023/11/3 16:31:00
            select add_months(sysdate,-2) from dual; // 2023/7/3 16:31:44
            //求某时间日期所在月的最后一天 last_day，注意，没有first_day函数
            select last_day(sysdate) from dual; //2023/9/30 16:41:53
            //日期截取 trunc
            select trunc(sysdate) from dual; //2023/9/3 时间被截掉了，保留了年月日
            select trunc(sysdate,'mm') from dual; // 2023/9/1 按月截取，把日截掉，显示月份的第一天
            select trunc(sysdate,'yyyy') from dual; //2023/1/1 按年截取，显示年的第一天
            select trunc(sysdate,'hh') from dual; //2023/9/3 16:00:00 按小时截取
            select trunc(sysdate,'mi') from dual; //2023/9/3 16:52:00 按分钟截取
      --转换函数
            //数字转字符串 to_char
            select to_char(100) from dual;
            //日期转字符串 to_char
            select to_char(sysdate) from dual; //03-SEP-23
            select to_char(sysdate,'yyyy-mm-dd') from dual; //2023-09-03
            select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual; //2023-09-03 06:28:18
            //字符串转日期 to_date
            select to_date('2016-03-10','yyyy-mm-dd') from dual; //2016/3/10
            //字符串转数字 to_number
            select to_number('100')+10 from dual; //110
      --其他函数
            //1.空值处理函数 NVL(检测的值，如果为null的值）
            select nvl(null,0) from dual; //0
            //2.nvl2(检测的值，如果不为null的值，如果为null的值)
            select nvl2(20,0,100) from dual; //0
            select nvl2(maxnum,to_char(maxnum),'不限') from t_pricetable where ownertypeid = 1;
            //3.条件取值，语法：decode(条件，值1，翻译值1，值2，翻译值2...值n,翻译值n,缺省值)
            select decode(100,1,2,3,4,100,200,300) from dual; //200
            select name,decode(ownertypeid,1,'居民',2,'行政事业单位',3,'商业') from t_owners;
            //4.case when then (sql999标准，在mysql中也可以使用)
            select name,(case ownertypeid 
                        when 1 then '居民'
                        when 2 then '行政事业单位'
                        when 3 then '商业'
                          else '其他'
                   end) from t_owners;
            //或者另一种写法，适合复杂场景，更灵活
            select name,(case 
                        when ownertypeid=1 then '居民'
                        when ownertypeid=2 then '行政事业单位'
                        when ownertypeid=3 then '商业'
                          else '其他'
                   end) from t_owners;
--行列转换
//按月份统计2012年各地区的水费
select (select name from t_area where id=areaid) 区域,
       sum(case when month='01'then money else 0 end) 一月，
       sum(case when month='02'then money else 0 end) 二月，
       sum(case when month='03'then money else 0 end) 三月，
       sum(case when month='04'then money else 0 end) 四月，
       sum(case when month='05'then money else 0 end) 五月，
       sum(case when month='06'then money else 0 end) 六月，
       sum(case when month='07'then money else 0 end) 七月，
       sum(case when month='08'then money else 0 end) 八月，
       sum(case when month='09'then money else 0 end) 九月，
       sum(case when month='10'then money else 0 end) 十月，
       sum(case when month='11'then money else 0 end) 十一月，
       sum(case when month='12'then money else 0 end) 十二月
from t_account where year='2012' group by areaid;

//按季度统计2012年各地区的水费
select (select name from t_area where id=areaid) 区域,
       sum(case when month>='01' and month<= '03' then money else 0 end) 第一季度，
       sum(case when month>='04' and month<= '06' then money else 0 end) 第二季度，
       sum(case when month>='07' and month<= '09' then money else 0 end) 第三季度，
       sum(case when month>='10' and month<= '12' then money else 0 end) 第四季度
from t_account where year='2012' group by areaid;

--分析函数（用于排名）
//值相同则排名相同，序号跳跃 rank
select rank() over(order by usenum desc) 排名,t.* from t_account t
//值相同则排名相同，序号连续 dense_rank
select dense_rank() over(order by usenum desc) 排名,t.* from t_account t
//序号连续，不管值是否相同 row_number
select row_number() over(order by usenum desc) 排名,t.* from t_account t
//利用row_number分页
select * from (select row_number() over(order by usenum desc) rownumber,t.* from t_account t) 
where rownumber <= 20 and rownumber >10

--集合运算（集合运算就是将两个或者多个结果集组合成为一个结果集）
      --union all:并集，返回各个查询的所有记录，包括重复记录
      select * from t_owners where id>5
      union all
      select * from t_owners where id<8
      --union:并集，返回各个查询的所有记录，不包括重复记录
      --intersect:交集，返回两个查询共有的记录
      select * from t_owners where id>5
      intersect
      select * from t_owners where id<8
      --minus:差集，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录
      select * from t_owners where id>5
      minus
      select * from t_owners where id<8
      --使用差集做分页
      select rownum,t.* from t_account t where rownum<=20
      minus
      select rownum,t.* from t_account t where rownum<=10

Oracle对象（视图，物化视图，序列，同义词，索引）

视图

• 是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表
• 视图可以简化数据操作
• 视图提供了一个简单有效的安全机制，可以定制不同用户对数据的访问权限
• 笔记

--创建简单视图
create view view_owners1 as 
select * from t_owners where ownertypeid=1
--查询简单视图
select * from view_owners1
--修改视图的数据（会影响基表t_owners的数据）
update view_owners1 set name='饭饭' where id=1;
commit;

--带检查约束的视图
create view view_address2 as
select * from t_address where areaid=2
with check option
--无法修改成功的语句，因为该视图的条件是areaid=2
update view_address2 set areaid=3 where id=4; //无法修改

--只读视图（or replace表示如果视图已存在则替换）
create or replace view view_owners1 as 
select * from t_owners where ownertypeid=1
with read only
--修改只读视图，报错
update view_owners1 set name='饭2' where id=1;
commit;

--创建带错误的视图
create force view view_test as
select * from t_test //并没有t_test这个表

--复杂视图的创建和使用（sql语句含有聚合函数，多表关联）
create or replace view view_owners as
select ow.id 业主编号,ow.name 业主名称,ot.name 业主类型 
from t_owners ow,t_ownertype ot
where ow.ownertypeid=ot.id
--查询复杂视图
select * from view_owners where 业主类型='居民'
--修改复杂视图的数据(也会修改基表的数据，只能修改键保留表含有的字段)
update view_owners set 业主名称='玲玲' where 业主编号=4;
commit;
--键保留表：把主键保留下来的那个表；业主编号作为主键，这个主键来自t_owners表，t_owners表就是键保留表
update view_owners set 业主类型='商业' where 业主编号=4; //报错，业主类型并不来自键保留表，无法修改

--分组聚合统计的复杂视图(聚合函数例如sum(money)必须起别名)
create view view_accountsum as
select year,month,sum(money) money
from t_account 
group by year,month 
order by year,month

select * from view_accountsum where month='03'
--修改视图,报错，原因：无键保留表
update view_accountsum set money=10000 where month='03'

物化视图

• 视图是一个虚拟表，基于它创建时指定的查询语句返回的结果集。每次访问它都会导致这个查询语句被执行一次。为了避免每次访问都执行这个查询，可以将这个查询结果集存储到一个物化视图（也叫实体化视图）
• 物化视图是建立的副本，它类似于一张表，需要占用存储空间。
• 语法：

  --build immediate 在创建物化视图的时候就生成数据（默认方式）
  --build deferred 在创建时不生成数据，以后根据需要再生成数据。
  --refresh 指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步
  --complete 完全刷新（重新生成数据）
  --fast 增量更新，只刷新上次刷新以后进行的修改
  --force 自动选择增量还是完全（默认方式）
  --on commit 在基表做提交操作时刷新物化视图，自动刷新
  --on demand 手动刷新（默认方式）
  create meterialized view view_name
  [build immediate | build deferred]
  refresh [fast | complete | force]
  [
   on [commit | demand] | start with (start_time) next
   (next_time)
  ]
  as
  subquery
  

• 笔记

--1.创建手动刷新的物化视图
create materialized view mv_address1 as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid = ar.id
--删除
drop materialized view view_address1
--查询
select * from mv_address1
--向基表插入数据
insert into t_address values(9,'西四旗','2','2');
commit;
--查询基表
select * from t_address;
--执行下列PLSQL语句来进行刷新，'C'是complete完全刷新的首字母
begin
  dbms_mview.refresh('mv_address1','C');
end;
--也可以再命令窗口执行命令刷新
SQL> exec dbms_mview.refresh('mv_address1','C');
PL/SQL procedure successfully completed


--2.创建自动刷新的物化视图 -基表发生commit操作自动刷新物化视图（要求数据强实时性）
create materialized view mv_address2 
refresh on commit
as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid = ar.id
--向基表插入数据
insert into t_address values(10,'西5旗','2','2');
commit;
--查询
select * from mv_address2

--3.创建不生成数据的物化视图
create materialized view mv_address3
build deferred
refresh on commit
as
select ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid = ar.id
--查询,没有数据
select * from mv_address3
--向基表插入数据
insert into t_address values(12,'西7旗','2','2');
commit;
--第一次必须手动执行PLSQL语句来进行刷新，往后就会在commit的时候自动刷新了
begin
  dbms_mview.refresh('mv_address3','C');
end;

--4.创建增量刷新的物化视图（这里做成手动刷新是为了能看到物化视图日志内容）
--前提：必须创建物化视图日志，用于记录基表发生的变化，用这些记录去更新物化视图
create materialized view log on t_address with rowid //这里基于rowid,还有其他方式例如基于主键，略
create materialized view log on t_area with rowid
--创建物化视图中的语句中，必须有基表的rowid
create materialized view mv_address4
refresh fast
as
select ad.rowid adrowid,ar.rowid arrowid,ad.id,ad.name,ar.name arname from t_address ad,t_area ar
where ad.areaid = ar.id
--查询
select * from mv_address4
--向基表插入,删除数据
insert into t_address values(15,'西10旗','2','2');
commit;
insert into t_address values(14,'西9旗','2','2');
commit;
delete from t_address where id=13;
commit;
--手动刷新
begin
  dbms_mview.refresh('mv_address4','C');
end;

序列

• 序列是Oracle提供的用于产生一系列唯一数字的数据库对象。语法：create sequence 序列名称
• 刚建立序列后，无法提取当前值，只有先提取下一个值时才能再次提取当前值。
• 笔记

  --创建简单序列
  create sequence seq_test
  
  --查询序列的下一个值
  select seq_test.nextval from dual;
  --查询序列的当前值
  select seq_test.currval from dual;
  
  --创建复杂序列（步长（默认1），初始值，最大值，最小值，是否循环（默认不循环），是否缓存到内存中（默认   cache 20））
  --1.有最大值的非循环序列
  create sequence seq_test1
  maxvalue 20;
  
  select seq_test1.nextval from dual;
  --2.有步长，初始值，最大值的非循环序列
  create sequence seq_test2
  increment by 10
  start with 10
  maxvalue 30;
  
  select seq_test2.nextval from dual;
  --3.循环的序列
  -- 报错：number to CACHE must be less than on cycle，原因：默认缓存20，步长为10，会缓存到200，而最大值  为100，没有循环就不会有这个问题
  create sequence seq_test3
  increment by 10
  start with 10
  minvalue 10
  maxvalue 100
  cycle;
  --改正
  create sequence seq_test3
  increment by 10
  start with 10
  minvalue 10
  maxvalue 210 //写201也可以
  cycle;
  
  select seq_test3.nextval from dual;
  --删除序列
  drop sequence seq_test3
  

同义词

• 同义词实质上是指定方案对象的一个别名。通过屏蔽对象的名称和所有者以及对分布式数据库的远程对象提供位置透明性，同义词可以提供一定程度的安全性。同时，同义词的易用性比较好，降低了数据库用户的SQL语句的复杂度。同义词允许基对象重命名或者移动，这时，只需要对同义词进行重定义，基于同义词的应用程序可以继续运行而无需修改。
• 可以创建公共同义词和私有同义词。其中，公共同义词属于public特殊用户组，数据库的所有用户都能访问；而私有同义词包含在特定用户的方案中，只允许特定用户或者有基对象访问权限的用户进行访问。
• 同义词本身不涉及安全，当你赋予一个同义词对象权限时，实质上是在给同义词的基对象赋予权限，同义词只是基对象的一个别名。
• 语法：create [public] SYNONYM synonym for object; //synonym表示同义词的名称；object表示表，视图，序列等我们要创建同义词对象的名称。

--创建私有同义词
create synonym owner for t_owners;
--使用
select * from owner;
--拥有dba角色的用户可以访问这个私有同义词
select * from wateruser.owner; 

--创建共有同义词
create public synonym owners2 for t_owners;
--使用
select * from owners2;

• varchar是内置同义词，是varchar2的同义词，为了兼容其他数据库语言

索引

--创建普通索引
create index index_owners_name on t_owners(name);
select * from t_owners where name='美廉美超市';

--性能测试 创建一个表，向表中插入100万条数据
create table t_indextest(id number,name varchar2(30));

begin
  for x in 1..1000000
    loop
      insert into t_indextest values(x,'AA'||x);
    end loop;
    commit;
end;
--创建索引
create index index_test on t_indextest(name);

select * from t_indextest where id=765432;
select * from t_indextest where name='AA765432';

--创建唯一索引
create unique index index_owners_meter on t_owners(watermeter);

--创建复合索引
create index index_owners_ah on t_owners(addressid,housenumber);

--反向键索引（应用场景：当某字段为连续增长的值，标准索引会形成歪脖子树，从而增加查询的层数，性能下降。反向键索引可以使索引的值变得不规则，从而使索引树能够均匀分配
       -语法：create index 索引名 on 表名(列名) reverse;

--位图索引（使用场景：适合创建在低基数列上，像性别这种就是低基数列；缺点：只能用等号查询，不能做范围查询）
       -语法：create bitmap 索引名 on 表名(列名);

Oracle编程

PL/SQL

• PL/SQL是Oracle对sql语言的过程化扩展，指在sql命令语言中增加了过程处理语句，使sql语言具有过程处理能力。
• 语法结构

[
    declare
    --声明变量
]
begin
--代码逻辑
[
    exception
    --异常处理
]
end;

• 声明变量的语法：变量名 类型(长度);
• 变量赋值：变量名:=变量值
• 笔记

--变量声明与赋值
declare
  --声明
  v_price number(10,2); --单价
  v_usenum number; --水费字数（number不定义长度默认是18）
  v_usenum2 number(10,2); --吨数
  v_money number(10,2); --金额
begin
  --赋值
 v_price:=2.45;
 v_usenum:=9123;
 v_usenum2:=round(v_usenum/1000,2);
 v_money:=v_price * v_usenum2;
 --打印
 dbms_output.put_line('金额：' || v_money);
end;

--select 列名 into 变量名

declare
  --声明
  v_price number(10,2); --单价
  v_usenum number; --水费字数
  v_usenum2 number(10,2); --吨数
  v_money number(10,2); --金额
  v_num0 number; --上月水表数
  v_num1 number; --本月水表数
begin
  --赋值
 v_price:=2.45;
 --v_usenum:=9123;
 select usenum,num0,num1 into v_usenum,v_num0,v_num1 from t_account 
 where year='2012' and month='01' and owneruuid=1;
 v_usenum2:=round(v_usenum/1000,2);
 v_money:=v_price * v_usenum2;
 --打印
 dbms_output.put_line('水费字数：'||v_usenum||'，金额：' || v_money);
end;



--属性类型（引用型:  表名.列名%type）
--如果知道变量是从哪个表的哪个列取出来的值，这时就可以不写变量的具体类型，而是引用列的类型
declare
  --声明
  v_price number(10,2); --单价
  v_usenum t_account.usenum%type; --水费字数
  v_usenum2 number(10,2); --吨数
  v_money number(10,2); --金额
  v_num0 t_account.num0%type; --上月水表数
  v_num1 t_account.num1%type; --本月水表数
begin
  --赋值
 v_price:=2.45;
 select usenum,num0,num1 into v_usenum,v_num0,v_num1 from t_account 
 where year='2012' and month='01' and owneruuid=1;
 v_usenum2:=round(v_usenum/1000,2);
 v_money:=v_price * v_usenum2;
 --打印
 dbms_output.put_line('水费字数：'||v_usenum||'，金额：' || v_money);
end;


--属性类型（记录型： 类似java的实体类，语法：表名%rowtype） 
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_account 
 where year='2012' and month='01' and owneruuid=1;
 v_usenum2:=round(v_account.usenum/1000,2);
 v_money:=v_price * v_usenum2;
 --打印
 dbms_output.put_line('水费字数：'||v_account.usenum||'，金额：' || v_money);
end;


--异常也叫例外，分为预定义异常和用户自定义异常。Oracle预定义了21个异常
 --no_data_found : 使用select into未返回行
 --too_many_rows : 执行select into时，结果集超过一行
 --语法结构：
          exception
            when 异常类型 then
              异常处理逻辑
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_account 
 where year='2012' and month='01' and owneruuid=200;
 v_usenum2:=round(v_account.usenum/1000,2);
 v_money:=v_price * v_usenum2;
 --打印
 dbms_output.put_line('水费字数：'||v_account.usenum||'，金额：' || v_money);
 exception
   when no_data_found then
     dbms_output.put_line('没有找到数据');
end;
--异常处理2:同时监测多个异常
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_account 
 where year='2012' and month='01';
 v_usenum2:=round(v_account.usenum/1000,2);
 v_money:=v_price * v_usenum2;
 --打印
 dbms_output.put_line('水费字数：'||v_account.usenum||'，金额：' || v_money);
 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;
        
--例子
declare
  --声明
  v_price1 number(10,2); --单价
  v_price2 number(10,2); --单价
  v_price3 number(10,2); --单价
  v_usenum2 number(10,2); --吨数
  v_money number(10,2); --金额
  v_account t_account%rowtype; --台账行记录类型
begin
  --赋值
 v_price1:=2.45; --5吨以下
 v_price2:=3.45; --5-10吨的
 v_price3:=4.45; --超过10吨的
 select * into v_account from t_account 
 where year='2012' and month='01' and owneruuid=1;
 v_usenum2:=round(v_account.usenum/1000,2);
 --v_money:=v_price * v_usenum2;
 --阶梯水费的计算
 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_usenum2 - 10) * v_price3;
 end if;
 
 --打印
 dbms_output.put_line('水费字数：'||v_account.usenum||'，金额：' || v_money);
 exception
   when no_data_found then
     dbms_output.put_line('没有找到数据');
   when too_many_rows then
     dbms_output.put_line('返回了多行数据');
end;

--循环
     --无条件循环语法：
        loop
          循环语句
        end loop;
     --条件循环语法：
        while 条件
        loop 循环语句
        end loop;
     --for循环语法：
        for 变量 in 起始值..终止值
        loop 循环语句
        end loop;

--无条件循环：输出从1到100
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 if;
  end loop;
end;
--有条件循环：输出从1到100
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循环
declare

begin
  for v_num in 1..100   //这里的v_num相当于局部变量
    loop
      dbms_output.put_line(v_num);
    end loop;
end;

--游标案例：打印业主类型为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; --关闭游标
end;
--带参数的游标，（sql语句某部分的值可能会变动，通过参数传参）
declare
 cursor cur_pricetable(v_ownertype number) is select * from t_pricetable where ownertypeid=v_ownertype; --声明带参数的游标
 v_pricetable t_pricetable%rowtype;
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
 cursor cur_pricetable(v_ownertype number) is select * from t_pricetable where ownertypeid=v_ownertype; --声明带参数的游标
 --v_pricetable t_pricetable%rowtype; --使用for循环时，v_pricetable的声明也可以省略
begin
  for v_pricetable in cur_pricetable(1)
     loop
      dbms_output.put_line('价格：' || v_pricetable.price || '元--->'
       || '吨位：' || v_pricetable.minnum || '-' || v_pricetable.maxnum);
     end loop;
end;

游标

• 游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。可以把游标理解为PL/SQL中的结果集。弥补了select into 只能返回一行记录的缺陷。

• 分类：

  • 静态游标
    • 显性游标：
      • 定义游标、打开游标、提取游标数据（fetch每次提取一行数据）、处理该记录、关闭游标
      • 声明游标语法：cursor 游标名称 is SQL语句，注意，如果有参数，不要写类型长度
      • 属性：%notfound(fetch成功则为false) %found(fetch成功则为true) %rowcount(fetch成功的数据条数) %isopen(游标已打开时返回true)
    • 隐性游标
      • 没有明确的游标声明和操作过程
      • 对于非查询语句，如新增、修改、删除操作，则由Oracle系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐性游标，隐性游标的名字为SQL，这是由Oracle系统定义的。对于隐性游标的操作，如定义、打开、取值及关闭操作，都由Oracle系统自动完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐性游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条SQL语句所包含的数据。隐性游标属性调用为：SQL%游标属性名
      • 属性：%notfound %found %rowcount %isopen(总是false)
  • 动态游标
    • 动态游标 = 显性游标 + 动态sql

• 使用游标语法：

open 游标名称;
loop
fetch 游标名称 into 变量;

    exit when 游标名称%notfound;
end loop;
close 游标名称;

• 笔记

--显性游标
declare
cursor cur_owners is select * from t_owners order by id;
v_owner t_owners%rowtype;
begin
  open cur_owners;
    loop
      fetch cur_owners into v_owner;
      --dbms_output.put_line('第'||cur_owners%rowcount||'行：'||v_owner.id||'--->'||v_owner.name);
      exit when cur_owners%notfound;
      dbms_output.put_line('第'||cur_owners%rowcount||'行：'||v_owner.id||'--->'||v_owner.name);
    end loop;
  close cur_owners;
end;
--游标使用for循环(在 PL/SQL 中，游标的 FOR 循环会自动打开和关闭游标，因此不需要显式使用 OPEN 和 CLOSE 语句。)
declare
cursor cur_owners_for is select id,name from t_owners order by id;
v_owner t_owners%rowtype;
begin
 for v_owner in cur_owners_for loop
   dbms_output.put_line('第'||cur_owners_for%rowcount||'行：'||v_owner.id||'--->'||v_owner.name);
 end loop;
end;

--带参数的显性游标
declare
cursor cur_owners_e(v_ownertypeid number) is 
  select id,name from t_owners where ownertypeid=v_ownertypeid order by id ;
v_owner t_owners%rowtype;
begin
 for v_owner in cur_owners_e(1) loop
   dbms_output.put_line('第'||cur_owners_e%rowcount||'行：'||v_owner.id||'--->'||v_owner.name);
 end loop;
end;

--隐性游标（DML、DQL例如select into 等语句）
 begin
   update t_owners set name='狗子' where id=16;
   if sql%notfound then
     dbms_output.put_line('修改失败');
   elsif sql%found then
     dbms_output.put_line('更新'||sql%rowcount||'条数据');
     commit;
   end if;
 end;

 --动态游标案例
declare
--声明动态游标类型(语法：type 名称 is ref cursor)
type refcur is ref cursor;
--定义动态游标变量
v_cur refcur;
v_test a_test%rowtype;
begin
  open v_cur for 'select * from a_test where id > :id' using 0; --打开动态游标，将动态sql查询结果赋值给游标变量
  loop
   fetch v_cur into v_test;
   exit when v_cur%notfound;
   dbms_output.put_line(v_test.id||'--->'||v_test.name);
  end loop;
  close v_cur;
end;

存储函数

• 存储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。在函数中我们可以使用PL/SQL进行逻辑的处理。
• 存储函数语法：

--创建或修改存储函数的语法如下
create [or replace] function 函数名称
    (参数名称 参数类型,参数名称 参数类型,...)
return 结果变量数据类型(不需要写长度)
is
    变量声明部分;
begin
    逻辑部分;
    return 结果变量;
[
    Exception
    异常处理部分
]
end;

• 笔记

--案例1：根据地址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 fn_getaddress(3) from dual;

--案例2：查询业主id,业主名称，业主地址，要用到案例1创建的函数
select id,name,fn_getaddress(addressid) 业主地址 from t_owners;

存储过程

• 存储过程是被命名的PL/SQL块，存储于数据库中，是数据库对象的一种。应用程序可以调用存储过程，执行相应的逻辑。
• 存储过程和存储函数的区别
  • 存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过传出参数返回多个值。
  • 存储函数可以在select语句中直接使用，而存储过程不能。存储过程多数是被应用程序所调用。
  • 存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码。
• 存储过程语法：

--创建或修改存储过程的语法如下
--参数只指定类型，不指定长度
--过程参数的三种模式：
    -- in 传入参数（默认的，可以省略）
    -- out 传出参数，主要用于返回程序运行结果
    -- in out 传入传出参数
create [or replace] procedure 存储过程名称
    (参数名称 参数类型,参数名称 参数类型,...)
is|as
    变量声明部分;
begin
    逻辑部分
[
    Exception
    异常处理部分
]
end;

• 笔记

--案例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,v_housenumber,v_watermeter,sysdate,v_ownertypeid);
  commit;
end;

--调用不带传出参数的存储过程
  --方法一
  call pro_owners_add('王一',2,'2333','6777',1);
  --方法二
  begin
    pro_owners_add('王小一',2,'24444','64444',1);
  end;

/**
 * JDBC调用存储过程
 */
public static void add(Owners owners){
    Connection conn = null;
    CallableStatement stmt = null;

    try {
        conn = BaseDao.getConnection();
        stmt = conn.prepareCall("{call pro_owners_add(?,?,?,?,?)}");
        stmt.setString(1,owners.getName());
        stmt.setLong(2,owners.getAddress());
        stmt.setString(3,owners.getHousenumber());
        stmt.setString(4,owners.getWatermeter());
        stmt.setLong(5,owners.getOwnertypeid());
        stmt.execute();
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }finally {
        BaseDao.closeAll(null,stmt,conn);
    }
}

--案例2：创建带传出参数的存储过程（添加业主信息，传出参数为新增业主的id）
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,v_housenumber,v_watermeter,sysdate,v_ownertypeid);
  commit;
end;

--调用带传出参数的存储过程，就不能用call的方式了
declare
  v_id number;
begin
  pro_owners_add('马哈哈',2,'22456','66777',1,v_id);
  dbms_output.put_line(v_id);
end;

public static long add(Owners owners){
    Connection conn = null;
    CallableStatement stmt = null;
    long id = 0L;
    try {
        conn = BaseDao.getConnection();
        stmt = conn.prepareCall("{call pro_owners_add(?,?,?,?,?,?)}");
        stmt.setString(1,owners.getName());
        stmt.setLong(2,owners.getAddress());
        stmt.setString(3,owners.getHousenumber());
        stmt.setString(4,owners.getWatermeter());
        stmt.setLong(5,owners.getOwnertypeid());
        //注册传出参数的类型
        stmt.registerOutParameter(6, OracleTypes.NUMBER);
        stmt.execute();
        //执行sql后拿到传出参数值
        id = stmt.getLong(6);
    } catch (SQLException e) {
            throw new RuntimeException(e);
    }finally {
            BaseDao.closeAll(null,stmt,conn);
    }
    return id;
}

触发器

• 数据库触发器是一个与表相关联的、存储的PL\SQL程序。每当一个特定的数据操作语句（insert,update,delete）在指定的表上发出时，Oracle自动的执行触发器中定义的语句序列。
• 分为前置触发器和后置触发器
• 语法

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

• 触发器中触发语句与伪记录变量的值(注意，用了语句级触发器就无法使用伪记录变量，因为语句只执行一次)

  触发语句	:old	:new
  insert	所有字段都是空(null)	将要插入的数据
  update	更新以前该行的值	更新后的值
  delete	删除以前该行的值	所有字段都是空(null)

• 笔记

  --前置触发器案例：用户输入本月累计表数后，自动计算出本月使用数
  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_owners_log(
      update_time date,
      ownerid number,
      oldname varchar2(30),
      newname varchar2(30)
    );
  
  create or replace 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); --这里不需要写commit;
  end;
  
  update t_owners set name='熊大' where id=16;
  

包（package）

• 包是一组相关过程、函数、变量、常量、类型和游标等PL/SQL程序设计元素的组合。包具有面向对象设计的特点，是对这些PL/SQL程序设计元素的封装。
• 包组成：包头 + 包体；包头可以单独存在，包体必须组合包头
• 包头（包规范）：存放过程、函数、变量、常量、类型和游标等PL/SQL程序的定义
• 包体：实现包头定义部分
• 包头定义函数和过程不需要写create
• 可以创建私有的函数和过程（包头没声明但是在包体中实现了就是私有的），只能被相同包中其他函数和过程调用
• 笔记

--包头
create or replace package test2_pack is
  procedure pro_getname(v_id number,v_name out varchar2);
end test2_pack;

--包体
create or replace package body test2_pack is
  function fn_getname(v_id number) return varchar2 is
      temp_name varchar2(30);
    begin
      select name into temp_name from t_owners where id=v_id;
      return temp_name;
    end fn_getname;


  procedure pro_getname(v_id number,v_name out varchar2) is
    begin
      v_name:=fn_getname(v_id);
    end pro_getname;
end test2_pack;

--调用包
declare
  v_id number;
  v_name varchar2(30);
begin
  v_id:=4;
  test2_pack.pro_getname(v_id,v_name);
  dbms_output.put_line(v_name);
end;

goto

--goto
begin
  for num in 1..5 loop
    dbms_output.put_line(num);
    if num > 3 then
      goto flag; --跳转到<<flag>>处
    end if;
  end loop;
  <<flag>>  -- <<>>后面要有可执行语句，如果没有应该使用 null 避免报错
  null;
end;

动态sql

1. 动态sql；因为PLSQL内部正常运行DML和DQL语句，如果去运行DDL（create、truncate、drop等），需要动态sql执行
2. 动态sql不但在PLSQL中可以运行DDL,还可以运行DML、DQL
3. 动态sql语法：execute immdiate 'sql语句' [into 变量] [using 绑定参数]

• 笔记

 --动态sql案例：执行DDL语句
 declare
   v_sql varchar2(255) :='create table A_test(
       id number primary key,
       name varchar2(20) not null
   )';
 begin
   execute immediate v_sql;
 end;
 
 --动态sql案例：执行DML语句
 begin
   --execute immediate 'insert into a_test values(1,''王一'')'; --需要单引号进行转义
   --更简便的方法：:1第一个占位参数  :2第二个占位参数
   execute immediate 'insert into a_test(id,name) values(:1,:2)' using 2,'王二';--id,name和后面using后的参数位置要一一对应
   --占位参数只是用来占位,通常用数字占位
   --execute immediate 'insert into a_test(id,name) values(:x,:y)' using 2,'王二';
   commit;
 end;
 
 --动态sql案例：执行DQL语句，查询结果集合
 declare
   v_test a_test%rowtype;
 begin
    execute immediate 'select * from a_test where id > :id' into v_test using 1;--:id为占位参数
    dbms_output.put_line(v_test.id ||'--->'|| v_test.name);
 end;

异常处理

• 异常处理可以按任意次序排列，但others必须放在最后。
• 异常处理部分一般放在PL/SQL程序体的后半部分，结构为：

declare
begin
  ...
exception
    when ... then ...
    ...
    when others then ...
end;

参考文章

• oracle存储过程如何调用写好的函数或方法