学习Oracle数据库整理笔记(word文档图片不显示)

1 Oracle
总结
1、  DDL语句：
创建表空间：create tablespace 表空间名  
修改表空间：alter tablespace 表空间  add datafile ‘文件.dbf’ size10M 
            
删除表空间：drop tablespace 表空间  
            Drop tablespace 表空间 including contents and datafiles
创建用户： create user 用户名  identified by 密码  default tablespace 表空间名
修改用户密码  alter user 用户名  identified by 新密码
修改用户默认表空间： alter user 用户名 default tablespace 新的表空间名
删除用户：drop user 用户名
 
创建表： create table 表名
修改表,新增字段：alter table 表名  add (.. )  
修改表,修改字段类型：alter table 表名  modify ( ….  )
修改表,修改字段名称：alter table 表名 rename column 旧字段 to 新字段
修改表,删除字段： alter table 表名 drop（字段名,…）
删除表：drop table 表名
 
用户授权：
 系统权限：grant  create session,create table,alter table  to  用户
 对象权限：grant select  on 表名  to 用户名
           grantselect,insert  on 用户1.表名  to 用户2
 
2、JDBC连接ORACLE：
     驱动：oracle.jdbc.driver.OracleDriver
     连接字符串：jdbc:oracle:thin:@IP:端口号:实例名
3、数据的导出与导入：
   导出：  
         exp 用户名/密码  full=y  file=文件名.dmp   --全库导出
         exp 用户名/密码  owner=用户名  file=文件名.dmp  --按用户名导出
         exp 用户名/密码  tables=表名1,表名2,.. file=文件名.dmp  按表名导出
  导入：
         imp 用户名/密码  full=y  file=文件名.dmp   --全库导入
         imp 用户名/密码  fromuser=用户名  file=文件名.dmp  --按用户名导入
         imp 用户名/密码  tables=表名1,表名2,.. file=文件名.dmp  按表名导入
 
表空间
创建表空间
                                                    
删除表空间
     
用户 授权
创建用户的语法：
    create user是创建用户的关键字
 
 
wateruser为创建的用户名
identifiedby 用于设置用户的密码
defaulttablesapce  用于指定默认表空间名称
 
在ORACLE中，创建表之前需要先创建表空间，再创建用户，并把创建的表空间给用户(如果未给指定表空间，默认使用系统的SYSTEM表空间)
 
用户授权
ORACLE在创建用户后，新创建的用户不具备操作数据库的权限(例：登录权限)，需要给用户授权。
 
 
给用户wateruser赋予DBA权限后即可登陆
 
Oracle中的权限分为两类
系统权限：系统规定用户使用数据库的权限（系统权限是对用户而言) 
系统权限提供了用户在整个DBMS中执行操作的能力，会影响到整个数据库系统的安全
     
对象权限：是指赋予用户在特定数据库对象执行特定操作的能力（是针对表或视图而言的）
          数据库对象包括：数据表、视图、字段、序列等
语法：GRANT 对象权限[,对象权限,…] ON 用户名.对象 TO 用户;
 
表空间可以多个用户,但用户数据是独立
 
表
建表
语句与mysql相似
CREATETABLE表名称 (
  字段名   类型(长度)   primarykey ,
  字段名   类型(长度) ,
  .......
);
 
数据类型
1. 字符型
（1）CHAR :  固定长度的字符类型，最多存储2000个字节
（2）VARCHAR2  :可变长度的字符类型，最多存储4000个字节
（3）LONG : 大文本类型。最大可以存储2个G
2.数值型
NUMBER : 数值类型  
例如：NUMBER(5)   最大可以存的数为99999    
    NUMBER(5,2)  最大可以存的数为999.99   
3.日期型
（1）DATE：日期时间型，精确到秒    类似于MYSQL中：datetime -------------------常用
（2）TIMESTAMP：精确到秒的小数点后9位
4.二进制型（大数据类型）
（1）CLOB : 存储字符,最大可以存4个G
（2）BLOB：存储图像、声音、视频等二进制数据,最多可以存4个G
现在开发中的作法：把图像、声音、视频文件存储到文件服务器中(例：七牛云文件服务器)。数据表中的字段存储的是文件的URL
 
修改表
增加字段
ALTER TABLE 表名称ADD(
列名1 类型 [DEFAULT 默认值]，
列名2 类型 [DEFAULT 默认值]...
)
 
修改字段类型
ALTER TABLE 表名称 MODIFY(
列名1 类型 [DEFAULT 默认值]，
列名2 类型 [DEFAULT 默认值]...
)
 
修改字段名
ALTER TABLE 表名称RENAME COLUMN 原列名 TO 新列名
 
删除表
DROPTABLE表名称
 
数据-----与MySQL一样但是需要提交事务
插入数据
 
 
INSERT 
 INTO表名[(列名1，列名2，...)]VALUES(值1，值2，...)
实例：向业主表插入数据
 
 
Oracle数据库不同mysql  执行数据操作语句后需要提交才能生效-----commit;
 
修改数据
 
 
UPDATE
 表名 SET 列名1=值1，列名2=值2，....WHERE 修改条件；
执行UPDATE后一定要再执行commit提交事务
需求：将ID为1的业主的登记日期更改为三天前的日期
 
 
update T_OWNERS set adddate=adddate-3whereid=1;
commit;
 
删除数据
 
语法1：
 
 
DELETE
 FROM 表名   WHERE  删除条件;
执行DELETE后一定要再执行commit提交事务
需求：删除业主ID为2的业主信息
 
 
deletefrom T_OWNERS whereid=2;
commit;
语法2：
 
 
TRUNCATE 
 TABLE  表名称
比较truncat与delete实现数据删除？
1)    delete删除的数据可以rollback；truncate是不可回滚
2)    delete删除可能产生碎片，并且不释放空间；truncate不会产生碎片，会释放空间
3)    delete是逐条删除；truncate是先摧毁表，再重构表
 
JDBC连接Oracle
与mysql一样
测试代码
工具类
 
 
public class
 BaseDao {
   //静态加载驱动
   static{
      try
 {
         Class.forName("oracle.jdbc.driver.OracleDriver");
      } catch (ClassNotFoundException e)
 {
         e.printStackTrace();
      }
   }
   //返回连接
   public static
 java.sql.Connection getConnection() throws SQLException{
      return java.sql.DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521/XE", "wateruser", "qqq");
  
 }
   //释放资源
   public static void
 release(Connection con, Statement sta, ResultSet res) {
      if (res
 != null) {
         try {
            res.close();
        
 } catch (SQLException e) {
            e.printStackTrace();
         }
     
 }
      if (sta != null) {
         try
 {
            sta.close();
         } catch (SQLException e) {
            e.printStackTrace();
        
 }
      }
      if (con != null) {
         try
 {
            con.close();
         } catch (SQLException e) {
            e.printStackTrace();
        
 }
      }
   }
}
测试类
 
 
public class
 TestDao {
   @Test
   public void insert(){
     
 Owner o = new Owner();
      o.setId(1L);
      o.setName("aaa");
      o.setAddress(3L);
      o.setHousenumber("a1");
      o.setWatermeter(5L);
      o.setAdddate(new
 java.util.Date());
      o.setOwnertypeid(7L);
      Connection conn=null;
     
 PreparedStatement ps = null;
      try {
         conn
 = BaseDao.getConnection();
         ps = conn.prepareStatement("insert into t_owners2 values(?,?,?,?,?,?,?)");
         
         ps.setLong(1, o.getId());
         ps.setString(2, o.getName());
         ps.setLong(3, o.getAddress());
         ps.setString(4, o.getHousenumber());
         ps.setLong(5, o.getWatermeter());
         ps.setDate(6, new
 Date(o.getAdddate().getTime()));
         ps.setLong(7, o.getOwnertypeid());
         ps.execute();
     
 } catch (SQLException e) {
         e.printStackTrace();
      }finally
 {
         BaseDao.release(conn, ps, null);
      }
   }
 
   @Test
   public void
 update(){
      Connection con = null;
      PreparedStatement sta = null;
      try
 {
         con = BaseDao.getConnection();
         sta = con.prepareStatement("update t_owners2 set name = ? where id = 1");
         sta.setString(1, "ccc");
         sta.execute();
     
 } catch (Exception e) {
         e.printStackTrace();
      }finally
 {
         BaseDao.release(con, sta, null);
      }
   }
   
   @Test
   public void
 delete(){
      Connection con = null;
      PreparedStatement sta = null;
      try
 {
         con = BaseDao.getConnection();
         sta = con.prepareStatement("delete from t_owners2 where id = 1");
         sta.execute();
      } catch (Exception e) {
         e.printStackTrace();
     
 }finally {
         BaseDao.release(con, sta, null);
     
 }
   }
}
 
数据导入导出
dos命令当前位置
全库导出
exp  system/itcast  full=y
如果想指定备份文件的名称，则添加file参数即可，命令如下
 
 
exp 
 system/itcast  file=文件名  full=y
文件名命名：年月日+序列号+     username/full/table
 
全库导入
imp system/itcast  full=y  file=water.dmp
 
按用户导入导出
按用户导出
 
 
exp 
 system/itcast  owner=wateruser  file=wateruser.dmp
按用户导入
 
 
imp  system/itcast  file=wateruser.dmp  fromuser=wateruser
 
按表导入导出但是需要表用户登录操作
按表导出
 
 
exp 
 wateruser/itcast  file=a.dmp  tables=t_account,a_area
用tables参数指定需要导出的表，如果有多个表用逗号分割即可
按表导入
 
 
imp  wateruser/itcast  file=a.dmp  tables=t_account,a_area
 
 
查询
简单查询：
精确查询、模糊查询、范围查询、空值查询、排序查询
select 语句的查询效率问题
select * from 表名  -------------是先扫描全表再检出数据
select 列名1,列名2…..from 表名-----------是直接检出列数据,效率更高
聚合统计：
  聚合函数、group by、having
group by 分组注意
select后面的列名需要在聚合函数中出现或者group by后面出现.否则报错
having注意
having不能脱离group by单独使用,无法替代where
where的效率更高
having是先分组再过滤,相对于where先过滤再分组,效率比较低,
group by的时候有组函数则只能使用having
伪列查询：
  Rowid：存储表中每一行记录的物理地址-----------------AAADVPAAFAAAABsAAA
数据对象编号     AAADVP           数据对象指的就表
相对数据文件号   AAF                    文件号就是指数据文件编号
数据块号         AAAABs
数据行号         AAA
 
  Rownum：给查询出来的结果集加个行号。 一般用于分页查询
多表查询：
  方式：1、先确定要查询的字段属于哪个表；2、确定表和表之间的关系
  左外右外同mysql都是SQL1999标准语法
  Oracle语法在多表查询基础上where条件中,左外就在右侧的条件后面加(+)
子查询：
  相关子查询： 内部的查询不能单独执行，需要依赖外部的查询。  先查询外部，再查内部
  非相关子查询：内部的查询可以单独执行。   先查内部，再查外部
  内部子查询可以用 in  all  not in
分页查询：
  简单分页：
    Select *from  (
      Selectrownum rn,表别名.* from 表名别名 where rownum<=上限值
) where rn > 下限值
 
      基于排序的分页：  select 优先于order by先执行。    先进行排序，再获取rownum
       Select * from (
Select rownum rn, t.* from 
( Select * from 表名 order by 字段desc )  t
         Where rownum <= 上限值
       )where rn >下限值
 
单行函数： 
  字符函数：
字符串长度----length()  字符串替换----replace()  截取字符串----substr()
字符串替换----replace()
replace(‘字符串’,’需要替换的’,’替换为’)
如果第三个参数为空,则直接去除字符串中的第二个参数
 
截取字符串----substr()
 
 
selectsubstr('Jackie' , 2) from dual;--从左边位置2开始截取，截取到最后 
selectsubstr('Jackie' , 2 , 0) from dual;--从左边位置2开始截取，截取0个，就相当于没有截取。取值为空字符串
selectsubstr('Jackie' , 2.9 , 3.2) from dual; --先把小数转为整数(不会四舍五入)，再截取
selectsubstr('Jackie' , -2 ) from dual; --从右边位置开始截取2位
 
  数值函数：
四舍五入----round()  取整----trunc()  取模----mod()
  日期函数：
 
 
   函   数
     描      述
ADD_MONTHS
在日期date上增加count个月
GREATEST(date1,date2,.
 . .)
从日期列表中选出最晚的日期
LAST_DAY( date )
返回日期date 所在月的最后一天
LEAST( date1, date2,
 . . .)
从日期列表中选出最早的日期
MONTHS_BETWEEN(date2,date1)
给出 Date2 - date1 的月数(可以是小数)
NEXT_DAY(
 date,’day’)
给出日期date之后下一天的日期，这里的day为星期，如： MONDAY,Tuesday等。
NEW_TIME(date,’this’,’other’)
给出在this 时区=Other时区的日期和时间
ROUND(date,’format’)
未指定format时，如果日期中的时间在中午之前，则将日期中的时间截断为12
 A.M.(午夜，一天的开始),否则进到第二天。时间截断为12 A.M.(午夜，一天的开始),否则进到第二天。
TRUNC(date,’format’)
未指定format时，将日期截为12 A.M.( 午夜，一天的开始).
使用to_char(sysdate, ‘HH’); ‘HH’可以使用如下表中的参数替换
 
 
模板
描述
HH
一天的小时数 (01-12)
HH12
一天的小时数 (01-12)
HH24
一天的小时数 (00-23)
MI
分钟 (00-59)
SS
秒 (00-59)
SSSS
午夜后的秒 (0-86399)
AM or A.M. or PM or P.M.
正午标识（大写）
am or a.m. or pm or p.m.
正午标识（小写）
Y,YYY
带逗号的年（4 和更多位）
YYYY
年（4和更多位）
YYY
年的后三位
YY
年的后两位
Y
年的最后一位
MONTH
全长大写月份名（9字符）
Month
全长混合大小写月份名（9字符）
month
全长小写月份名（9字符）
MON
大写缩写月份名（3字符）
Mon
缩写混合大小写月份名（3字符）
mon
小写缩写月份名（3字符）
MM
月份 (01-12)
DAY
全长大写日期名（9字符）
Day
全长混合大小写日期名（9字符）
day
全长小写日期名（9字符）
DY
缩写大写日期名（3字符）
Dy
缩写混合大小写日期名（3字符）
dy
缩写小写日期名（3字符）
DDD
一年里的日子(001-366)
DD
一个月里的日子(01-31)
D
一周里的日子(1-7；SUN=1)
W
一个月里的周数
WW
一年里的周数
CC
世纪（2 位）
J
Julian 日期（自公元前4712年1月1日来的日期）
Q
季度
 
加月----add_months(时间,正数加负数减)   最后一天----last_day()    截取日期----trunc(时间,’截取格式’)
 
  转换函数：
to_char(numbet类型/date类型, ‘指定格式’)   to_date()  to_number
  空值处理函数：
nvl(检测的值,为null的值)   nvl2(检测的值,不为null的值,为null的值)    后两个参数类型要一致
  decode(字段名,值1,显示的值1,值2,显示的值2,….,缺省值)
报表查询：
case when 条件 then 结果else 其他 end
case 字段 when 条件then 结果 else 其他 end
行列转换、
case when 条件 then 结果else 其他 end
case 字段 when 条件then 结果 else 其他 end
差集
集合运算（并集、交集、差集）
并集运算 union all去重不带all
交集运算 intersect
差集运算 minus 可实现分页.另外该查询是两条语句同时查询,时间为最长的查询的时间
 
另:
查询语句执行顺序
1 from------------------多表先执行后面的表,以此为驱动表匹配前面的表,所以数据量少的表放在后面提高查询效率
2 where-----------------多条件先执行后面的条件,所以过滤较多的条件放在后面,先过滤多数数据,提高查询效率
3 group by
4 having
5 select
6 order by
 
 
 
视图
视图可以理解为封装了SQL查询语句的对象. 
 
使用视图的优点：
1.简化数据操作：视图可以简化用户处理数据的方式。
2.着重于特定数据：不必要的数据或敏感数据可以不出现在视图中。
3.视图提供了一个简单而有效的安全机制，可以定制不同用户对数据的访问权限。
4.提供向后兼容性：视图使用户能够在表的架构更改时为表创建向后兼容接口。
创建视图
语法：
 
 
CREATE [OR REPLACE] [FORCE] VIEW view_name    
AS subquery    
[WITH CHECK OPTION ]  --创建带检查约束的视图
[WITH READ ONLY] –创建只读视图
选项解释：
ORREPLACE ：若所创建的试图已经存在，ORACLE自动重建该视图；
FORCE       ：不管基表是否存在ORACLE都会自动创建该视图；
subquery      ：一条完整的SELECT语句，可以在该语句中定义别名；
WITH  CHECK OPTION ：插入或修改的数据行必须满足视图定义的约束；
WITH READONLY ：该视图上不能进行任何DML操作。
 
删除视图
drop view 视图名
 
没有约束的话,
在修改视图数据的时候,表中的数据也会改变,同样修改表中的数据视图的数据也会改变,因为视图其实就是封装的sql语句
 
而有检查约束的视图
在插入和修改时会检查视图中封装的sql语句子查询(例如where)条件
 
只读,只能查询
 
先创建视图,再创建表force
 
复杂视图的修改
只能修改键保留表的的列
是原表的主键也是视图的主键
如果有聚合函数,也无法修改
 
序列
在MySQL中有自增长主键
Oracle中没有自增长.
Oracle中的序列可以实现给一个number字段实现自增长
一般一张表绑定一个序列对象
多张表绑定一个序列对象会造成序列数值混乱
 
简单序列
创建序列
createsequence 序列名称  
nextval 返回序列下一个值
currval 返回序列当前值
获取下一个值
select序列名称.nextvalfrom dual
获取当前值
select序列名称.currvalfrom dual
 
复杂序列
CREATE SEQUENCE sequence  //创建序列名称
      [INCREMENT BY n] //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是1
      [START WITH n]   //开始的值,递增默认是minvalue 递减是maxvalue
      [{MAXVALUE n | NOMAXVALUE}] //最大值
      [{MINVALUE n | NOMINVALUE}] //最小值
      [{CYCLE | NOCYCLE}] //循环或不循环  默认NOCYCLE
                     [{CACHE n | NOCACHE}];//分配并存入到内存中 默认CACHE 20 （减少IO，提高效率）  
 
最小值要小于等于初始值
非循环的话,达到最大值以后会报错
 
缓存的值要小于一次循环的值
 
非循环的序列对于缓存的值没限制
 
修改序列,不能修改开始值
ALTERSEQUENCE 序列名称MAXVALUE 5000 CYCLE;
删除序列
DROPSEQUENCE 序列名称;
 
同义词
语法
createsynonym别名 for表名;
 
其他用户可使用公有权限同义词
create publicsynonym别名 for表名;
 
索引
索引可以增加数据查询效率,减少I/O次数
在ORACLE中，在建立索引树时，是通过字段(列)的值和值的长度来决定是存放树结构的哪个位图
索引的使用：
1、 表中的数据量比较大时，字段(列)经常被作为检索条件查询时
例：city经常用为查询条件。    Select列1,…. Fron 表名 wherecity=‘’
2、 在创建表时，不需要给字段添加索引（索引通常都是在项目后期添加[后期数据量庞大]）
3、 创建索引时，需要占用一定的空间（基于某列创建索引时，会把列中的数据提取出来，构建索引树）
4、 通常列上创建了索引后，列不建议经常修改(update操作)
5、当在查询时使用like时，索引无效
 
普通索引
语法
createindex索引名称 on表名(列名);
 
复合索引
语法
createindex索引名称 on表名(列名,列名.....);
在复合索引中会存在一个主索引和一些次索引
创建索引时第一个列名为主索引,后面为次索引
先查找主索引的位置再找次索引的位置在
索引条件如果使用and则会在查到主索引后接着查询次索引
如果使用or则会主索引查询生成临时表,次索引查询生成临时表,然后合并(union)
 
唯一索引
createunique index索引名称 on表名(列名);
建表时如果建立主键,则会默认生成一个唯一约束
想要实现唯一约束,只能依赖唯一索引
这里唯一约束也不能为null,
mysql中的唯一约束可以为null但是只能有一个
 
反向键索引
适用于大量数据长度,值相似时
如果使用普通索引则会在树的一侧查询次数增加,查询效率低
 
语法
createindex索引名称 on表名(列名) reverse;
 
 
位图索引
适用于低基数(数据少,例如性别(男,女),婚姻状况(已婚,未婚))列
 
语法
createbitmapindex索引名称 on表名(列名);
 
总结:
普通索引:建立在表中的一个字段上,数据可以重复
复合索引:建立在表中的多个字段上,数据可以重复
唯一索引:建立在表中的字段上,数据不可重复不可为null
反向键索引:当表中的字段的数据大小相近,长度相同时使用,避免歪脖子树降低查询效率
位图索引:建立在表中的低基数列
 
附:可以使索引失效
   1、没有 WHERE 子句
 
   2、使用 LIKE '%T' 进行模糊查询
       例： select * from t_owners where name like '%范'
       索引不失效：  select * from t_owners wherename like '张%'
 
   3、使用 IS NULL 和 IS NOT NULL    针对null值查询，索引无效
       例：select * from t_pricetable where max is null;
    
   4、 WHERE 子句中使用不等于操作       例：<>      !=       not in
       例：select * from t_account where money <>100;
       可以使用：select * from t_account where money>100 or money<100;
 
   5、WHERE 子句中使用函数
       例：select * from t_owners where round(id) > 5
       可以使用： create index idx_owners_id on t_owners(round(id));  --建立函数索引
                select * from t_owners where round(id) >5  
 
   6、隐式转换时索引失效   （比较时类型不匹配）
       例：select * from t_account  whereyear = 2012
             oracle在执行时，会把以上语句变为：select * from t_account where  to_number(year) = 2012
       正确写法：select * from t_account  whereyear = '2012';
 
   7、对索引列进行运算时导致索引失
       例：select * from t_owners where id-1=5;
       正确的写法：select * from t_owners where id=6;
 
PL/SQL
基本语法
declare 
   --声明变量
 begin
   --代码逻辑  
 exception
   --异常处理
 end;
 
变量
声明:             变量名类型(长度);
赋值:             变量名 := 值;
                     select 列名,列名2,… into  变量名,变量名2,… from 表名 where 条件
注意:第二种select into方式结果必须为一条数据,如果为多条或空会报错
 
属性类型(引用类型)--------理解为自适应类型
 
%TYPE引用型
表示某个字段类型
 
%ROWTYPE   记录型
表示一行数据类型,可使用   变量名.字段   来获取数据
 
异常
预定义异常
exception
  when异常类型  then
     异常处理逻辑
 
异常类型一般使用others---------类似最大E异常
 
条件判断
1 语法
if条件 then
     业务逻辑
endif;
2 语法
if条件 then
   业务逻辑
 else
   业务逻辑
 endif;
3 语法
if条件 then
     业务逻辑
elsif条件 then
     业务逻辑
else
     业务逻辑  
endif;
 
循环
无条件循环
loop
  --循环语句 
endloop;
 
条件循环
while条件
loop
--循环语句 
endloop;
 
for循环------常用
for变量  in起始值..终止值
loop
  --循环语句
endloop;
 
游标
原理理解为类似迭代器的next
实际储存的是一个结果集
声明游标
cursor游标名称 is SQL查询语句;
 
使用语法
open游标名称
loop
   fetch游标名称 into变量(行记录) 
   exit  when  游标名称%notfound
--逻辑代码
endloop;
close游标名称
 
带条件的语法
cursor游标名称(变量名 变量类型(不可加长度)) is SQL查询语句where筛选条件用变量名;
begin
open游标名称(传入参数)
loop
   fetch游标名称 into变量(行记录) 
   exit  when  游标名称%notfound
--逻辑代码
endloop;
close游标名称
 
 
游标属性
 
 
游标的属性
返回值类型
说明
%ROWCOUNT
整型
获得FETCH语句返回的数据行数
%FOUND
布尔型
最近的FETCH语句返回一行数据则为真，否则为假
%NOTFOUND
布尔型
与%FOUND属性返回值相反，代表游标结束
%ISOPEN
布尔型
游标已经打开时值为真，否则为假
 
使用for循环提取游标值
行变量名 行变量类型%rowtype
cursor游标名称(变量名 变量类型(不可加长度)) is SQL查询语句where筛选条件用变量名;
begin
for行变量名 into游标(参数)
loop
--逻辑代码--行变量名.字段--取值
endloop;
 
储存函数
又称自定义函数,
必须有一个或多个参数,
必须返回一个结果
语法
CREATE [ OR REPLACE ]  FUNCTION  函数名称
 （参数名称 参数类型, 参数名称 参数类型, ...）
RETURN 结果变量数据类型   --函数中的参数类型、返回值类型  不能声明长度
IS
  变量声明部分;
BEGIN
  逻辑部分;
  RETURN 结果变量;
[EXCEPTION 
  异常处理部分]
END;
 
使用方式
select 函数名(参数) from dual;
 
储存过程
可以理解为封装的一个PL/SQL块
储存过程与储存函数的区别:
       1储存函数必须有一个返回值,储存过程可以没有也可以有多个
       2储存函数可以在select语句中使用,储存过程不能这么用,多数是被程序调用
       3储存函数是封装一个查询结果,储存过程是封装一段事务代码
储存过程使用场景
       1封装SQL语句,增加安全性,程序直接执行相应的储存过程,不需直接书写SQL语句
       2复杂的SQL语句封装
       3敏感数据的操作封装
语法
CREATE [ OR REPLACE ]  PROCEDURE   存储过程名称
 （参数名 类型, 参数名 类型, 参数名 类型）
AS
  变量声明部分;
BEGIN
  逻辑部分
[EXCEPTION 
  异常处理部分]
END;
 
储存过程的三种类型参数
1，IN   传入参数（默认）
2，OUT  传出参数 ，主要用于返回程序运行结果
3，IN OUT  传入传出参数
置于参数名和类型中间
 
JDBC调用储存过程使用CallableStatement对象
CallableStatement stmt =null
stmt=conn.prepareCall("{call储存过程(参数1,参数2…)}");
 
                      //注册传出参数的类型
                      stmt.registerOutParameter(占位符, OracleTypes.Oracle属性类型);
                      stmt.execute();//执行SQL语句
                      id = stmt.get属性类型(占位符); //提取传出参数的值
 
触发器
触发器与表关联,执行某个表的增删改操作时触发的一段PL/SQL程序
 
触发器可用于：
1)     数据确认  
2)     实施复杂的安全性检查
3)     做审计，跟踪表上所做的数据操作等
4)     数据的备份和同步
 
触发器分为
前置触发器(before)commit之前执行
       后置触发器(after)commit之后执行
 
语法
CREATE  [orREPLACE] TRIGGER  触发器名
   BEFORE | AFTER
   [DELETE ][[or] INSERT] [[or]UPDATE [OF列名,列2]]
   ON 表名
   [FOREACHROW ] 是否行级触发器,操作一行触发一次,不指定的话只触发一次
declare
    ……
begin
   PLSQL 块
End；
 
触发器中语句被触发后会有两个伪记录变量
              :old                             :new
insert     null                             插入的值
update  修改前的值                  修改后的值
delete    删除前的值                  null
 
前置触发器中可以实现实时修改字段数据
后置触发器中
这两个伪记录变量可以用来记录数据库操作日志,向日志表插入修改数据记录