oracle语法总结

忘了密码后
 1.若名登录到oralce编缉环境:sqlplus /nolog 
 2.集成登录到oracle  数据库:conn /as sysdba 
 3.修改  oracle  用户的密码:alter user 用户名 identified by 密码;

建表空间
 create tablespace 表空间名
 datafile 'E:\a.dbf'
 size 1M(大小) autoextend on;

新建用户
 create user 用户名
 identified by 33
 default tablespace 默认表空间名
 temporary tablespace 临时表空间名(temp)

用户授权
 给用户一个连接数据库授权
 grant connect to 用户名
 给用户一个使用数据库中的空间
 grant resource to 表户名
 给用户一个建序列的权限
 grant create requence to 用户名
 给用户一个访问scott用户下的一个emp表的权限
  1.要先登录到scott用户模式下
  2.授权:grant select on emp to 用户 || grant all on emp to 用户
修改用户的口令
 alter user 用户名 identified by 密码
删除用户
 drop user 用户名 cascade (其中的cascade表式用户模式对象)
登录到远程服务器
 用户名/密码@网络服务名(其中的网络服务名自己配置所要连接的主机)
得到表中数据行的行地址Rowid,行号rownum
 select rowid,rownum from emp where empno='7902';
创建数据表
      1.create table 表名
 (
  列名 类型 约束
  .
 )
      2.利用现有的表创建新表(新表名中的结构和表名的结构一样,且记录也一样,查询建表)
 例1.create table 新表名
  as
     select * from 表名;
 例2.create table 新表名
  as
     select 列名1,列名2 from 表名;
 例3.create table 新表名
  as
     select * from 表名1
  集合操作符(如union,union all)
     select * from 表名2;
修改表结构
 添加列:alter table 表名 add (列名 类型);
 删除列:alter table 表名 drop column 列名;
 修改列:alter table 表名 modify (列名 要改的类型)
    !!! 注意,在修改的列必须要根据该列中的数据修改(如:该中有一条记录为10个宽度,但你要修改为６个宽度，会报错)
  
删除数据表
 drop table 表名 cascade constraints;(删除表,cascade表对象,constraints约束);
插入来自其他表中的数据
 insert into 表名(列名1,列名2...) select 列名1,列名2... from 表名 where 条件
查看表结构
 desc 表名;
查看表中某一列的数据
 select 列名 from 表名
 如果这一个列的数据有重复，又想要无重复的行,用distinct 
 select distinct　列名 form 表名
查询排序
 select * from 表名 where 条件 order by 列名1,列名2 desc(降序)||asc(升,默认)
分组查询
 select * from 表名 where 条件 group by 列名
 having 条件(可以使用聚合函数)
 注:where ,group by, having, order by顺序不能倒
事务控制
 sql>savepoint mark1;
 sql>delete ,insert ,update数据操纵语言(DML);
 sql>savepoint mark2;
 sql>rollback to savepoint mark1(回滚到mark1)
 sql>commit;
授予权限
 grant 权限 on 表名 to 用户名;
 grant 权限 on 表名 to 用户名 with grant option;
 注:with grant option表示该用户可以把这种权限授予其他用户
收回权限 
 revoke 权限 on 表名 from 用户名;
算术操作符
 select 列名１*100,列名２－列名３　from 表名
比较操作符
 in的使用(用来列表中任何一个相匹配的，where 条件都成立)
  select * from 表名 where 列名1 in ('aaa','bbb');
 like的使用(%表示后接任意长度任何字符,_表示后接一个长度任何字符)
  select * from 表名 where 列名1　like 'S%';
 between．．． and．．．使用
  select * From 表名 where 列名1　between 5 and 100;
 not in ,not like ,not between ... and... 同上反之即为
集合操作符
 union(联合):两个查询所有而不重复的行(类型匹配)
  select 列名1 from 表1 union select 列名1 from 表2;
  select 列名1,列名2 from 表1
   union
  select 列名2,列名1 from 表名2 order by 2;
 两个列的列名不同,但要用第2列来排序,可以使用列的索引号来代替列名(是第2列,为2)
 union all(联合所有):两个查询所有的行,两个列名不必相同,类型必须匹配
  select 列名1 from 表1 union all select 列名2 from 表名2;
 intersect(两个查询的交集)
  select 列名1 from 表1 intersect select 列1 from 表2
 minus (减集)第一个查询有,但第二个查询的结果没有的数据
  select 列1 from 表1 minus select 列1 from 表2
连接操作符'||'
 select （'a'||'是一个字符'）字符 from dual;
 输出:
  字符
  -----------------------------
  a是一个字符
操作符的优先级
 算术>连接>比较>not逻辑操作符>and逻辑>or逻辑
单行函数据
 1日期函数(d,表示日期)
  add_months: 在指定的格式上加上指定的月数后的日期值
   select add_months(sysdate,2) from dual;
  months_between(d1,d2):返两个日期之间的月数,d1晚d2为正,否为负,相等为0,若d1,d2为某月中的同一于或最后一天,为正
   months_between('10-11月-06','1-11月-06')为正
  last_day(d) 返回指定日期的当月的最后一天
   last_Day('10-11月-06')为'30-11月-06'
  round(d,'格式'):返回日期的四舍五入为格式模型指定的单位
   如:round('06-5月-06','year'),返回为:01-1月-06,因为5月小于一年的一半,四舍五入
  next_day(d,day):day表示星期几, 返回下一个星期的这个星期几的日期
   如:next_day('10-11月-06','星期三'),得出下一星期三的日期,由于'10-11月-06'这一天是星期五,所以返回下一星    期三的是日期:'15-11月-06'
  trunc(d,'格式'):以指定的格式模型截断日期
   如:trunc('10-11月-06','year')以年的格式截断后变成:'1-1月-06' 
  extract(格式(fmt) from 日期(d)):提取日期中特定的部分
   如:extract(year from to_date('10-11月-06')) 反回2006(字符串需要格式化成日期);
 2字符函数
  initcap():首字母大写
  lower():转换成小写
  upper():转换成大写
  Ltrim('abzhb','ab'):左剪裁,在左边把ab 剪裁
  rtrim('zhbab','ab'):右剪裁,在右边把ab 剪裁
  translate('zhblq','ahdhzb','123450'):按字母翻译,输出为:520lq (其中z,表示5,以此类推)
  replace('zhbzhb','h','520'):字符串替换,输出为:z520bz520b
  instr('zhb','h'):查找字符串的位置,输出为:2(注意,不是从0开始)
  substr('zhblq',4,2):取字符串,输出:lq(注意:第4个字符为L,了取两个字符)
  concat('zhb','lq') :连接字符串:输出为:zhblq
  
 3数字函数 
  abs(-15):取绝对值,                       输出:15
  ceil(44.778):向上取数,                   输出:45
  sin(1.571):正弦,                         输出:.9999999
  cos(0):余弦,                             输出:1
  sign(-32):取符号,                        输出:-1
  floor(100.2):向下取整,                   输出:100
  power(4,2):4的2次幂,                     输出:16
  mod(10,3):取余数,                        输出:1
  round(100.269,2(表示精度)):四舍五入,     输出:100.27
  trunc(100.269,2(表示截断后小数点位数)):截断,输出:100.26
  sqrt(4):平方根,输出2
 4转换函数
  to_char(d||n,[fmt(日期或数字的格式)]):可以d(日期)或n(数字)转化成指定的格式
     如:select to_char(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS'):输出2006年11月10日 16:23:56
   其中:YYYY表年,fmMM:月,fmDD:日,HH24表小时(24表示日２４小时格式),mi:分,SS:秒
     select to_char(56,'C99999||$999999'):输出CNY56||$56,后面的９９９９９９一定要大于前面的５６
  to_date(char,[fmt('格式')]):将char/varchar类型转换为日期类型
     如:to_date('2005-12-06','YYYY-mm-dd'),输出:06-12月-05
     注：to_char转换函数中格式模型fmt的定义同样适用于to_date转换函数
  to_number('字符数据'):将包含数字的字符串转换为number数据类型(oracle可以对数字字符串进行隐式转换)
     如:to_number('100'):,输出:100;

 5其他函数
  转换null值:(注:null和0不同,null表示为空（未知）,0表示为0(已知))
     1.NVL(expression1,expression2):将空值替换为指定的值
   如expression1为null,nvl返回expression2,如expression1不为空,nvl返回expression1
   select nvl(aa,0) from uu;在表uu中aa列为空就返回0，否则就返回aa列的值
     2.NVL2(expression1,expression2,expression3)
   如expression1为null,nvl返回expression3,如expression1不为空,nvl返回expression2
     3.NULLif(expr1,expr2):两个表达式比较，如果相等,返回为nul;l空值,不相等:返回expr1
   

分组函数(聚合函数) 
 avg(列名):返回指定列的平均值
 min(列名):返回指定列所有值中最小的
 max(列名):返回指定列所有值中最大的
 sum(列名):返回指定列所有值的总和
 count(*)：返回所有的行，包括重复的和空值的行
 count(列名):返回指定列的非空值的个数（不含空值）
 group by子句:将信息划分为组，按组进行聚全运算
 having子句:用来指定group by子句的检索条件,位于group by的后面
  如:
 select a max(b) from ab
  group by a
 having a not in('abccsfs');
临时修改(本会话)的日期语言
 1.修改日期语言为识别英文格式的月份
  alter session set nls_date_language = 'AMERICAN';
 2.修改日期语言为识别简体中文的月份
  alter session set nls_date_language = 'SIMPLIFIED CHINESE';
行级锁
 用户自定义锁:select ...... for update语句(共享行级锁)
 自动 应 用锁:insert,delete,update语句在运行时自动应用锁(行级排他锁)
 当其他用户已以对此加锁,该用户加锁确认等待时间
  select .... for update wait 5;(要更新的行被锁定,等5秒后自动返回,否则一直等待)
 可以用不等待来查看想要锁定的行是否被其他用户锁定,若被锁定,立即返回
  select .... fro update nowait
 commit或rollback释放锁定
表级锁
 共享锁:多个用户加锁定表时,可以查询,但不能改,可以多个户加共享锁
 排他锁:限制最大,别的用户可查(但查的不是真实的数据,只有到用户释放后),不可改,其他用户不再可锁
 
 注:共享锁可以升级为排他锁(在没有其他用户加共享锁的情况下),排他锁不可以降级为共享锁
    如果要想在一个表中加排他锁,须要先验其他用户已是否对此加锁(nowait),先加共享锁,再加排他锁(以防无限等待)
 lock table 表名 in share/exclusive mode [nowait]
 commit或rollback释放锁定
死锁   :两个事务相互等待对方完成任务时,就会出现死锁
 如  :两位用户都对一个单独的对象进行了锁定(如a对对象1,b对对象2,对象1和对象2都是一个单独的对象),但是两个用户同时又要获              取对方的对象的锁(a想取对象2,b想取对象1),在这种情况下,两个用都在等待对对方放锁,两个用户都在等待中,形成死锁
 注:oracle会自动检测死锁,并自动终止其中的一个来解决问题(且先等待的那个用户先终止事务)
表分区(表要在建时才能分区,建后不能再分)
 范围分区:partition by range (列名)
   (
   partition 分区名1 value less than(边界值) [tablespace 分区所在的表空间名(可选)],
   partition 分区名2 value less than(边界值) [tablespace 分区所在的表空间名(可选)],
   ...
   partition 分区名n value less than(MAXVALUE)
   );每个分区的边界值必须小于下一个边界值
 散列分区:partition by hash(列名)
   (
   partition 分区名1 [tablespace 分区所在的表空间名(可选)],
   partition 分区名2 [tablespace 分区所在的表空间名(可选)],
   .....
   );
   或
   partition by hash(列名) partitions 分区的数目 [store in (分区使用的表空间,可选)];
 复合分区:(范围和散列,或列表的合成)
   partition by range(列名1)
   subpartition by hash(列名2)
   subpartitions 散列的个数 [stort in (分区使用的表空间,可选)]
   (
   partition 分区名1 value less than (边界值),
   partition 分区名2 value less than (边界值),
   ......
    partition 分区名n value less than (maxvalue)
   );
   先根据列1创建范围分区,再通过散列分区的方法建N个子分区
 列表分区:partition by list(列名)
   (
   partition 分区名1 values (值列表1(如:'a','b':表列在值'a'和'b'中的记录)),
   partition 分区名2 values (值列表2),
   ......
   partition 分区名N values (default(表示上面列表中没有包括的记录))
   );
表分区的DML:
 1.表分区中插入记录:写正常的SQL语句,它会通过你分区用的列来自动分到该到的区
 2.查看表分区中的数据:select * from 表名 partition (分区名);
表分区的维护:
 添加分区:alter table 表名 partition 分区名 values less than(边界值);
 删除分区:alter table 表名 drop partition 分区名
 截断分区:alter table 表名 truncate partition 分区名 :(截断:删除分区中所有的记录)
 合并分区:alter table 表名 merge partitions 分区1,分区2 into 分区名
 拆分分区:alter table 表名 split partition 分区名 at(边界值) into (partition 名1,partition 名2);
 分区重命名:alter talbe 表名 rename partition 分区名 to 新分区名
同义词
 私有同义词:
     定义:只能被当前模式的用户访问(同义词名 不能和 想要建同义词的对象 相同)
  ###在自身的模式下建同义词须有create synonym权限,在其他用户中建须有create any synonym权限
     语法:create [or replace(表示该同义词存在替换该同义词)] synonym 同义词名 for 想要建同义词的对象   
     步骤:首先要有访问对象的权限，然后才成建同义词
 共有同义词:
     可以被所有数据访问,隐藏基表身份,必拥有create public sysnonym权限
     语法:create [or replace] public synonym 同义名 for 想要建同义词的对象
 注:当公有对象和本地对象具有相同名称时,本地优先同义词
删除同义词
 drop [public] synonym 同义词名,删除同义词,不会删除对应的表
序列
 用来生成唯一,连续的整数的数据库对象,通常用于生成主键或唯一健值(可以排列)
 create sequence 序列名
    start with 数(生成的第一个序列号)
    increment by 数(间隔,可为正／负,决定升或降)
    maxvalue 最大值
    minvalue 最小值
    cycle/nocyle（表示是否到了最大值或最小值后，将继续从头(最小值)开始）
    cache/nocache 数(表预先分配一组序列号,保在内存中,加快访问序列号,nocache:不预先分配),如果没有指定这个项,默认20个
访问序列
 序列名.nextval:第一次使用时,返回初始值,以后以序列的间隔数增或降
 序列名.currval:返回当前序列的值，即最后一次引用nextval时返回的值
更改序列
 设置maxvalue 或minvalue值, 修改增量值, 修改(cache 数)缓存中列号的数目,不能修改起始数;
 如:alter sequence 序列名
  maxvalue 5000
  cycle;
删除序列:drop sequence 序列名

视图
 又名:已存储的查询，虚拟表
 create [or replace] [force|noforce]　view 视图名 [视图列名1,视图列名2...]
 as
 select语句
 [with check option[constraint(约束的名称)]]------指定只能更新视图可以访问的行
` [with read only]---------------确保不能在视图上做任何修改操作
 注:force表示无论是否存在基表,都要建该视图,noforce,表只有存在基表才建(默认noforce)
视图中order by 子句:可以按某列排序

建带有错误的视图
 视图定义的查询引用了一个不存在的表,
 视图定义的查询引用了一个表中无效的列,
 视图的所有者没有所需的权限
 语法正确,将会创建视图,但是不能用,这种视图成为'带错误的视图',
  如果以后用命令修复(如:建视图时不存在表,以后又建了这个表,oracle自动重新编译该视图,并使其可用)
联接视图:
 create view 视图名
 as
 select a1,a2,b1,b2 from a 表a别名,b 表b别名 where 表a别名.a1=表b别名.b1(+)
 或
 select a1,a2,b1,b2 from a 表a别名 left outer join b 表b别名 on 表a别名.a1=表b别名.b1
 注:'+'是oralce特用的外联接号
键保留表
 如果一个表的主键是一个视图的主键,称这个主键表为键保留表,包含外联接的视图通常不包含键保留表,除非外联接生成非空值
 键保留表才适合使用DML语句
视图中的函数:可以使用单行(由数,字符,日期组成),分组函数和表达式

删除视图:drop view 名;

索引
 快速访问数据,影响执行的速度,与视图不同的是:索引是独立的结构,因此不需存储空间
 普通索引:create index 名 on 表名(列名) [tablespace 表空间名];
 唯一索引:create unique index 名 on 表名(列名);   可以为数据唯一的据创建,(oracle自动为表的主键建唯一索引)
 组合索引:create index 名 on 表名(列1,列2);       同时使用两个列检索,若只使用其中一列不会用检索数据
 反向键索引:create index 名 表名(列) reverse;     通常建在一些值连续增长的,数据过于密集的列上
  alter index 反向键索引名 rebulid noreverse;noreverse可以将反向键索引重建为标准索引,反之标准不能重建反键
 位图索引:create bitmap index 名 on 表名(列);     建在有重复的列上,blob列上,因为它存的是rowid的地址(rowid存数据的地   址)(终端交互),直接的cpu交互,速度会快一些,不应用在DML(insert...)语句操作频繁的表上,会引起加锁反应
 基于函数的索引:create index 名 表名(upper(列名));upper代表函数
  注:要创建函数或表达式的索引,必须有query rewrite权限
索引组织表:
 在建表最后一行加:organization index;索引包含列(通过键值记录数据),和唯一索引一样,没有重复的键值,降低储存要求
 索引组织表根据主键访问数据,没有隐式的rowid,不能使用unique约束条件,必须有主键,不允许使用分区

索引中的分区:
 局部分区索引:在局部索引分区中oracle 为每个分区建立一个独立的索引,索引对为分区来说是'局部'
 create index 名 on 表名(列) local;
 全局分区索引:在分区或非分区的表上建的索引,全局索引的键可以引用存储在多个分区中的行
   create index 名 on 表名(列名) global
   partition by range(列)
   (
    partition 名1  values less than (边界值),
    partition 名2  values less than (边界值)
   );
   有3个分区的表上创建两个分区的索引
 全局非分区索引:全局非分区索引是在分区表上创建全局索引,类似非分区表上建的索引,索引的结构不会被分割
 
重建现有的索引
 alter index 名 rebuild :优于drop index 和create index语句重新建索引
删除索引
 drop index 名;
获得索引的信息:
 user_indexes:可获取有关用户创建的索引的详细信息
 user_ind_partitions:有关用户建分区索引的信息
 user_ind_columns:有关列(用户的索引是基于这些列创建的)的信息
获得用户所创建的视图信息:user_views
获得用户所创建的序列信息:user_sequences
获得用户所创建的同义词信息:user_synonyms

使用pl/sql

pl/sql语法
 declare
  声明变量
 begin
  执行语句
 exception
  if 异常 then
   异常处理
 end;
 
声明变量
 declare
  名 类型(可为sql或pl/sql类型) [:=变量初始化]
给变量赋值
 1.变量名 :=值;
 2.select 列名1,列名2... into 变量1,变量2... from 表名 where 条件...
   (须要有where 条件语句,因为列可能有多个值,不可能把多个值赋给一个变量)
声明常量
 变量名 constant 类型 :=值;
     注:初始化变量和常量时，可以用default 替换赋值操作符(:=) 如 aa varchar2(10) default 值;

pl/sql数据类型的用法
 标量类型
     1.数字数据类型
  1.binary_integer(-2^31-1到2^31-1)
   子类型
      1.natural:存储非负整数(自然数)
      2.naturaln:存储自然数,且非空
      3.positive:正整数
      4.positiven:正整数,且非空
             5.signtype:只存1,0,-1三个值
  2.number(1E-130到10E125)
   number(p,s)参数 p表示位数(38位,最高38,默认38),s表示小数点后有s位小数(-84到127)
   number 不带参数时,小数点可移动
   number(p):只有p参数时,表示不带小数点的整数(即:number(p,0))
   注:number 支持四舍五入,如s 为2,则2.466数会转化为4.45;
   子类型
      1.declmal:38位十进制的定点数
      2.float :126位二进制数(相当于38位十进制数)的浮点数
      3.integer:38位十进制的整数
      4.real: 63位二进制数(相当于18位进制数)的浮点数
  3.pls_integer(-2^31-1到2^31-1):存带符号的整数  
   注:建议在执行处于pls_integer数值范围内的所有计算时使用可以提高效率 
      2.字符数据类型
  1.char(size [char|type]):表固定多少字节,要想有字符有char(n char)带上char参数表可容n个字符,即使某些字符含多      个字符.char在oracle中的列最多可容2000个字节,char 最大不超过32767个字节
  2.raw(size) :存固定的二进制数据或字节串,raw变量的最大长度为32767 个.数据库存raw 列长度为2000个
  3.long 和long raw :oracle列中,long列和long raw列可存2GB
    注:lob 变量可以与long 和long raw 变量互换使用,可以将long数据和long raw数据 移值到clob类型
  4.varchar2(size[char|byte]):存可变长度的字符串,oracle 列中varchar2 类型的列可存4000个字节
    子类型
    string
    varchar,是varchar2的另一个名称(单独数据类型)
      3.日期时间数据类型
  date:固定长度的日期和时间数据(公元前(b.c.e)4712-1-1到公元(c.e)9999-12-31))
  timestamp(p):P表示秒的精度
      4.布尔型
  boolean :可存true,false,null值,不带任何参数,boolean数据能插入到数据库中,不能提取列值赋予boolean变量,只允许     逻辑操作

  大对象lob类型:lob数据库列用存储定位器,指向大对象的存储位置,可以存在数据库中,也可存在外存部文件中
  bfile:存大型二进制对象在操作系统中,数据是只读的,bfile类型有bfile定位器,其指向服务器上的一个文件,其含一个目录              的别名,用于指目录的完整路径, 
  blob:大型的二进制数据对象存在数据库中,blob数据类型可用于事务处理
   授权:要在表中插图片,1要create dirctory images as 'C:\images'
         2要grant read on directory images to 用户名
  clob:存大型字符数据在数据库中,clob变量中的定位器指向大型数据的地址 
  nclob:将大型nchar数据存在数据库中,支持固定宽度和可变可unicode字符数据,使用类似clob
  属性类型
  用于引用变量和数据库列的数据类型,可表示表中一行的记录类型
  %type:引用某个变量或数据库列的数据类型来声明变量,如:decl zhb.lq%type:表示变量decl,是zhb表中lq列的类型相同;
  %rowtype:引用表中的一行记录类型,记录类型可以从表中选择或游标提取的整行数据
    decl zhb%rowtype;表变量decl可以从表zhb中提取一个记录,(可用,decl.列名来调用所提取记录中的列的数据);
  引用数据类型
  组合数据类型
逻辑比较
 =(等于),!=|<>(不等于),<,>,>=,<=
 比较表达式称布尔表达式,有三种:数字布尔型(数字可以比较),字符布尔(默认情况,比较字符串中每个字节的二进制值),日期布尔.
控制结构
     条件控制
 if 条件 then  |    if 条件 then  |  if 条件 then
     执行块    |        执行块    |     执行块
 end  if       |    else   |  elsif 条件 then
        |        执行块    |     执行块
        |    end if  |  else
      | 执行块
      |  end if
      case语句:值的匹配,执行case语句前,先计算选择器(case后的条件)的值
  case 条件
       when 匹配值1 then 执行语句1;
       when 匹配值2 then 执行语句2;
   ...
       else 没有以上的匹配的值执行语句;
  end case;
     循环控制(可以用exit或(exit when 条件)结束本个循环)
 loop循环:          loop
    循环块;
      end loop;
 while循环:    while 条件 loop
    循环块;
      end loop;
 for 循环:          for n in [reverse] value1..value2 --reverse表示是循环从大到小,计量数n变量不用声明,可作常量用
      loop
    循环块;
      end loop;
     顺序语句
 goto语句:无条件地将控制权转到标签指定的语句.标签:用<<>>括起来的,必须有唯一的名称,后必紧跟执行语句
   goto语句不能跳转到if,case,loop语句中,或子块中
  如: if a>b then
   goto zhb;
      else
   goto lq;
      end if;
      <<zhb>>
   update ...
      <<lq>>
   insert '''
动态sql
 实现pl/sql使用ddl语句各会话,编译部分对动态sql不处理,可以通过sql 命令或dbms_sql程序包来执行
 语法
  execute immediate sql语句
  [into 变量]--接受sql语句选择的记录值可以插入到一个变量中
  [using 变量(即参数)]
  如:(变量)sql_stmt :='select * from zhblq where zhb= :lq'--':lq'代表接受一个参数,可以随便于工作,':zhb'也可 
   execute immediate sq_stmt into zhbrow(一个rowtype类型的变量) using emp_id(一个值);
     emp_id这个值传到上一句的:lq中,sql_stmt查询的记录集存在zhbrow中
错误处理
 预定义异常:隐式引发内部异常,要通过名子来捕获异常
 用户定义异常:declare
   自定义异常名 exception;---定义异常
       begin
   ....
    raise 自定义异常名;---显式引发异常
       exception
    when 自定义异常名 then--处理异常
     dbms_output.put_line('引发自定义异常');
       end;
 引发程序异常:用户自定义错误消息,可比指定的消息更详细
  raise_application_error(error_number(错误号),error_message(错误文本));
  注:错误号在-20000到-20999之间的负整数,错误文本可在2048个字节,raise_application_error可以放在可执行程序中,      也可放在异常部分中处理;
dbms_output.put_line() 和dbms_output.put()
 dbms_output.put():不换行执行,抵制执行.放在缓存中,遇到dbms_output.put_line()才执行,且不换行
 dbms_output.put_line():换行执行
 如:
  begin
  dbms_output.put_line('aaaaaaaaaaaaaaaaaaa');
  dbms_output.put('b');
  dbms_output.put_line('cccccccccccc');
  end;
  输出为:aaaaaaaaaaaaaaaaaa
         bcccccccccc
游标管理
 静态游标
    隐式游标:所有的DML语句隐式声明游标,当用户  使用操纵语言时,oracle 自动定义一个名为sql的隐式游标
  隐式游标的属性:(可以通过:游标名%属性名来获取相应sql语句信息,如:sql%found,sql是一个游标名(隐式自动创建的));
    %found:DML语句影响一行或多行时,%FOUND返回true;
    %notfound:如果DML语句没有影响行,%NOTFOUND返回true;
    %rowcount:返回DML语句影响的行数,没有影响行返回0;
    %isopen:返回游标是否已打开,在SQL语句执行已后,oracle自动关闭(隐式)游标,所以隐式游标的%isopen始终为false;
  注:在引发异常时,将不使用属性sql%notfound来查明DML语句是否已经影响了行
  
    显式游标:用户显式的声明游标,游标指向活动集(游标中定义的查询返回的行集)的当前行;
  显式游标的使用步骤:
   声明: cursor 标名 [(参数1,参数2...)] [return 定义游标提取行的类型] is select语句
   打开游标:open 游标名[(参数1...)]
   从游标中提取行:fetch 游标名 into 变量名
   关闭游标:close 游标名; 
  declare
   my_row zhb.a%type;
   cursor zhb is
   select a from zhb where 条件;--返回一个列a的记录集,有列a的类型,如果为:select * from zhb,返回行类型
  begin
   open zhb;
   loop
      fetch zhb into my_row;---游标zhb的类型为zhb表中的a列类型
      dbms_output.put_line(sql%rowcount);--当fetch影响一行,该数加1
      exit when zhb%notfound;如果没有被影响的行(即返回的行),退出loop,否则打印出来
    dbms_out...
   end loop;
   close zhb;
  end;
  注:exit when zhb&notfound 需在循环语句中
     显式游标的属性:(可以通过:游标名%属性名来获取相应sql语句信息,如:sql%found,sql是一个游标名(隐式自动创建的));
    %found:DML语句影响一行或多行时,%FOUND返回true;
    %notfound:如果DML语句没有影响行,%NOTFOUND返回true;
    %rowcount:返回DML语句影响的行数,没有影响行返回0;
    %isopen:如果游标已经被打开,返回true ,否则false;
                使用显式游标删除或更新
   在定义游标必须使用select... for update语句,在执行更新语句时使用where current of子句指定游标的当前行
     声明更新游标的语法:
   cursor 名 is select句 for update [of columns];有of columns 锁定行的哪个列上
     在执行更新语句时
   update 表名 set 列=value where current of 游标名
     例:
   cursor cursor_zhb is select * from zhb where id<100 for update [of 列名];
   ...
   update zhb set name='lq' where current of cursor_zhb;--cursor_zhb代表声明时的select 语句
   delete from zhb where current of cursor_zhb;--删除了所有的游标指定的当前的行
       where current of是指定游标当前的行
  循环游标:简化显式游标的处理代码,隐式打开游标,自动从活动集中获取记录,处理后自动关闭
    自动建%rowtype类型的变量并将此变量用作记录索引.
   for t(变量为%rowtype类型,作用域在for循环内s) in 标名
   loop
    执行句(可用t,t表游标结果集的类型); 
   end;
   如果在提取记录后%notfound属性返回true,终止loop,如果没有返回行,不进入循环
 ref游标
  动态决定执行何种查询 
  ref创建变量使用
   1.先声明ref_cursor类型      :type 标名 is ref cursor [return 指定提取结果集的返回类型]
   2.声明ref_cursor类型的变量, :标变量名 标名;
   注:有return语句表示强类型的ref游标,否则为弱类型
   例:
    type aa_cursor is ref sursor;
    ab aa_cursor;
   动态的特点:select语句不在声明中固定,可以begin..end 指定,可以用:
        open aa for select语句   
       打开游标来指定语句 ;
  动态sql的用法
  .......
  游标变量的优点:
   1.用于从不同的结果集中提取记录
   2.可作为过程的参数进行传递
   3.引用游标的所有属性
   4.可用于赋值运算
  游标变量的一些限制
   1.for...update子句不能与游标变量一起使用
   2.不允许在程序中声明游标变量
   3.不能将null值赋给游标变量
   4.不能使用比较运算符
   5.数据库的列不能存储游标变量
   6.另一台服务器上的远程子程序不能接受游标变量参数的值
子程序
 过程:
  create [or replace] procedure 过程名
  [(参数1 参数模式 参数类型,参数2 参数模式 参数类型...)]
  is|as
  声明
  begin
   执行语句
  exception
  end;
 参数模式:in,out,in out
  in:可以输入,默认型
  out:输出值(可以用一个变量来接受其值)
  in out:可输入,输出值,
  out ,int out不可以是个常量来给定参数值
 将过程授权
  grant execute on 过程名 to 用户名
  grant execute on 过程名 to public(给所用户)
 执行过程:
  execute 过程名(参数);
 删除过程:
  drop procedure 过程名
 函数:执行特定的pl/sql块,并返回值
  create [or replace] function 函数名
  [(参数1 参数类型,参数2 参数类型...)]--函数只有输入格式的,形式参数只能用数据库类型,不能有pl/sql类型
  return 返回类型  ----返回值必为数据库类型
  is|as
  声明
  begin
  exception
  end
 函数执行:函数不能单独执行
  select 函数名 from dual;
  用sql 语句或pl/sql语句块来调用
 函数授权:和过程相同
  grant execute on 函数名 to 用户
 删除函数:
  drop function 函数名;
 函数的使用限期限制:
  1.select 语句调用的函数不能修改数据库表;
  2.如果函数调用执行update 的存储过程,此函数不能在sql语句内使用
  3.select,values,set子句可以调用函数写入程序包中变量,其他都不能
  4.远程执行或并行执行函数时,函数不能读取或写入程序包中变量的值
 自主事务处理
  是由一个事务(主事务)来调用另一个事务(另一个事务独立处理)
  过程S1启动S2过程
  定义自主事务处理;
   要对S1的更改不会影响到过程S2;
   需要在s2 声明部分标明:pragm autonomous_transaction;
  自主事务处理的特征:
   1.处理结果不依赖于主事务处理的状态或配置
   2.处理结果提交或回退,不影响主事务处理的结果
   3.处理结果一旦提交,可以独立提交,无需等到主事务处理完成,且提交后对其他事务是可见的,包括主事务
   4.处理结果可以启动其他自主事务处理
程序包 
 程序包规范:相当于应用程序的接口,声明一些程序都可见的化公共对象和类型的声明(声明中有子程序和游标才需包主体)
 程序包语法:
  create or replace backage 包名
  is|as
  [public(公共的) 声明(类型,常量,变量,异常,游标)]
  [声明子程序(过程,函数)]
  end [包名];
 程序包主体:游标和子程序的具体实现,私有声明可以包括在程序主体中
  create or replace backage body 包名(与规范相同)
  is|as
  [public(公共的) 声明(类型,常量,变量,异常,游标)]
  [声明公有或私有子程序(过程,函数)]
  [begin
   初始化声明]--此过程是可选的,初始化包中的变量,其不能调用程序包,不能传参给程序包,仅运行一次
  end [包名];
 引用包规范中声明的类型,对象,子程序:包名.对象名;(触发器,匿名pl/sql,存储子程序等可以用)
 执行包中的过程:execute 包名.过程名(参数);
程序包中的游标:
 定义游标规范:
  create or replace backage 包名
  cursor 标名(参数)
  return 类型;------可以返回两个数据类型,(1):%rowtype,(2):根据程序员定义的记录类型的记录
  ....子程序....
  end;
 定义包中游标的主体:
  create or replace backage body 包名
  cursor 标名(参数)
  return 返回类型 is
  select语句;
  ...
  end;
 引用:包名.游标名;(也可以open,fetch,close);
查看数据库储存对象:select * from user_objects
查看数据库储存子程序,程序包,函数,包主体:
     select object_name,object_type from user_objects where object_type in ('procedure','function','package','package body');
查看程序包中的子程序的源代码:
 --select line,text from user_source where name='子程序名';
 column 列名 format 999;--表示'列名'这个列的数据要按9999(以内的数)数字这个格式来显示   
 column 列名 format a50;--表示'列名'这个列的数据要按每行这个列数据有50个字母这种格式来显示(a,表示字母,50表示个数)
 
触发器:当特定事件出现时自动执行的代码块
       与过程的区别:过程由用户或应用程序显式调用，触发器:不能真接调用,oracle会在事件请求触发器时，执行适当的触发器
       创建:
       create [or replace] trigger 名
       before|after|instead of --after|before:表在事件发时之前或后执行,instead of:表示触发器代替事件(引起触发器),用于视图;
       [insert|update of 列1,列2...|delete] --引起触发器调用的操纵类型,有多个可用or来隔开,如:insert or update ename or ..
       on 表或视图名
       [referenction new as 新行的别名 old as 旧行的别名]
       [for each row]--是否为行级触发器
       [when (条件)]--条件一般可有新列和旧列来操作,如,new.列1>20,表示在'新列1值(在更新或插入时)>20'引发触发器
       [beclare
 声明部分    
       ] 
       begin
  执行块
       excepiton
  异常处理会块;--当有异常处理,则执行有错,信息会回滚
       end;
触发器:触发器语句(where以上的),触发器限制:where条件,触发器操作:begin ... end;

触发器类型:
 表级触发器:1.行级触发器.2.语句级触发器
   行级触发器:对于DML语句影响的每个行执行一次,update aa set bb='a';此update会影响aa表中所有的行,行级触发器对update每对              表aa执行update一次,就会执行一次触发器,创建:在创建触发器时，指定for each row，即创建行级触发器     语句级触发器:对每一个DML语句只执行一次，如上的update 语句，只会触发语句级触发器一次,不常用于与数据相关的活动,通常用                 于强制实行在表上执行操作的额外安全性措施;是创建触发器的默认类型,与行级区别:只是没有for each row指定
 instead of触发器:只能用于视图,只能在行级(for each row)使用,允许用户修改不能直接使用DML语句修改的视图.
    如果在两个表中插入值的情况上，可能使用instead of 触发器.(不推荐使用此触发器,因它对于视图)
 模式触发器:对DLL(create,alter,drop),DCL(grant,revoke),truncate语句建触发器，防止他人非法DLL操作
  create or replace trigger 名
  before|alter create|alter|drop....
  on schema
  [when (条件)]
  begin
   执行块
  end;
 数据库级触发器:对启动，关闭，服务器错误，登录，注销等(实例范围)建触发器,不与表或视图关联
  create or replace trigger 名
  after|before startup|...
  on database
  begin
   执行块;
  end;
禁用和启用触发器
 禁用触发器:alter trigger 名 disable;
 启用触发器;alter trigger 名 enable;
 在特定表启用各禁用触发器:alter table 表名 disable|enable all triggers;
删除触发器:
 drop trigger 名;
查看有关触发器的信息:desc user_triggers;select trigger_name from user_triggers where table_name='EMP';

内置程序包:sys有oracle提供的所有程序包，被定义为公有同义词，并将执行权限授于所有(public)用户,任何用户可可以访问
   用于扩展数据库的功能 ;
      dbms_output:显示pl/sql和子程序输出结果,put和put_line过程将信息输出到SGA的缓存区,通过get_line或set serveroutput on显示.
           默认缓冲区是:2000字节,最小2000字节,最大:1000000字节;
 常用过程:
 enable:启动其他过程的调用,有一个参数：缓存区大小,调用enable将清除任何已废弃会话中缓存的数据
 disable:禁用过程,没有输入和输出,清除任何缓存剩余的数据
 put:输入参数,为varchar2,number,date(to_char会自动格式化这些项),可以在缓存区中存一条信息(直到遇到输出(如:put_line));
 put_line:输入一个参数,接收varchar2,date,number, 如果这些值要混合使用，必显式用TO_char函数,此过程接一个结束标记.
  如:put_line('aaa'),put('bbb'),putline('ccc');输出:aaa换行bbbccc,换行在后面不在前,bbb是接在aaa换行后.
 new_line:没有参数,添加一个换行符
      dbms_lob:包含用于处理大型号对象的过程和函数:blob(二进大),clob(字符大),bfile(外部二进文件大-只读)
 常用过程:
 过程append;
 过程copy:
 过程erase:
 函数getlength:
 函数instr:
 过程read:
 函数substr:
 过程wrire:
 专门操作BFILE类型的:
  过程fileopen:
  过程filegetname:
  过程fileclose:
  函数fileisopen:
      dbms_xmlquery:用于将查询结果转换为XML格式;
 
      dbms_random:用来生成随机数,random函数返回8位的随机数,正负也是随机的
 例:100个随机整数
 begin
  for i in 1..100
  loop
   dbms_output.put_line(abs(dbms_random.random mod 100));
  end loop;
 end;
 
     utl_file:用于读取操作系统文本文件,如:XML文件
       流程：打开，读写，关闭
 utl_file包要求的文件所在位置是基于oracle的directroy 对象指定的目录，不能直接访问文件夹的绝对路径
 实现步骤：
 1.utl_file包必须先将创建目录对象与操作系统的实际目录关联起来,创建目录对象需要有关权限： 
  create directory 目录对象名 as 'C:\aaa';--创建与C下的文件夹aaa关联的对象,一般用户没有创建的权限
                             注:系统中c盘下一定要有aaa文件夹一定要存在
  grant read,write on directory 目录对象名 to 用户;--授于用户的权限
 
   

  ddl 数据定义语言 dml 数据操作语言 dcl 数据操作语言
create table test(…) ; --创建表test，类似sql语句
create table test as select * from dept; --从已知表复制数据和结构
create table test as select * from dept where 1=2; --从已知表复制结构但不包括数据
desc test    --显示表结构
drop table tablename;  --删除表
delete from tablename;  --删除表里的所有记录
truncate table tablename –删除表
注:在删除一个表的数据时 ，建议使用truncate,因为delete，tablespace未释放。
默认约束、非空约束、唯一性约束、主键约束、外键约束和Check约束。

 
      1. CONNECT Role(连接角色)
      2. RESOURCE Role(资源角色)
      3. DBA Role(数据库管理员角色)
SQL> alter user scott account unlock; --解锁
SQL> alter user scott identified by 123; --改密码123
SQL> conn scott/123;--登录
SQL> alter user scott account lock; --锁定

   //加载JDBC驱动? ?? ???Class.forName("oracle.jdbc.driver.OracleDriver");? ?//创建数据库连接? ?? ???Connection con =    DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "test", "test");

    声明部分（用declare开头）
    执行部分（以begin开头）(必须的)
    异常处理部分（以exception开头）

声明一个number类型的变量v_id
v_id number;
给上面的v_id变量赋值，不能用”=”应该用”:=”
v_id :=5;

a. loop … end loop;
b. while condition loop … end loop;
c. for variable in low_bound . . upper_bound loop … end loop; 其中的“…”代表循环体

　　过程：Create or replace procedure procname(参数列表) as PL/SQL语句块
　　函数：Create or replace function funcname(参数列表) return 返回值 as PL/SQL语句块

 

 

 

游标（CURSOR）也叫光标，在关系数据库中经常使用，在PL/SQL程序中可以用CURSOR与SELECT一起对表或者视图中的数据进行查询并逐行读取。 Oracle游标分为显示游标和隐式游标。
显示游标（Explicit Cursor）:在PL/SQL程序中定义的、用于查询的游标称作显示游标。
隐式游标（Implicit Cursor）:是指非PL/SQL程序中定义的、而且是在PL/SQL中使用UPDATE/DELETE语句时，Oracle系统自动分配的游标。
一.显示游标
1.使用步骤
(1)定义 (2)打开 (3)使用 (4)关闭
2.使用演示
首先创建测试用表STUDENT，脚本如下：
复制代码代码如下:

CREATE TABLE "STUDENT" (
"STUNAME" VARCHAR2(10 BYTE),
"STUNO" VARCHAR2(4 BYTE),
"AGE" NUMBER,
"GENDER" VARCHAR2(2 CHAR)
)

(1).使用WHILE循环处理游标
create or replace PROCEDURE PROC_STU1 AS
BEGIN
--显示游标使用，使用while循环
declare
--1.定义游标，名称为cur_stu
cursor cur_stu is
select stuno,stuname from student order by stuno;
--定义变量，存放游标取出的数据
v_stuno varchar(4);
v_stuname varchar(20);
begin
--2.打开游标cur_stu
open cur_stu;
--3.将游标的当前行取出存放到变量中
fetch cur_stu into v_stuno,v_stuname;
while cur_stu%found --游标所指还有数据行，则继续循环
loop
--打印结果
dbms_output.PUT_LINE(v_stuno||'->'||v_stuname);
--继续将游标所指的当前行取出放到变量中
fetch cur_stu into v_stuno,v_stuname;
end loop;
close cur_stu; --4.关闭游标
end;
END PROC_STU1;
(2).使用IF..ELSE代替WHILE循环处理游标
create or replace PROCEDURE PROC_STU2 AS
BEGIN
--显示游标使用，使用if判断
declare
--1.定义游标，名称为cur_stu
cursor cur_stu is
select stuno,stuname from student order by stuno;
--定义变量，存放游标取出的数据
v_stuno varchar(4);
v_stuname varchar(20);
begin
--2.打开游标cur_stu
open cur_stu;
--3.将游标的当前行取出存放到变量中
fetch cur_stu into v_stuno,v_stuname;
loop
if cur_stu%found then --如果游标cur_stu所指还有数据行
--打印结果
dbms_output.PUT_LINE(v_stuno||'->'||v_stuname);
--继续将游标所指的当前行取出放到变量中
fetch cur_stu into v_stuno,v_stuname;
else
exit;
end if;
end loop;
close cur_stu; --4.关闭游标
end;
END PROC_STU2;
(3).使用FOR循环处理游标
create or replace PROCEDURE PROC_STU3 AS
BEGIN
--显示游标使用，使用for循环
declare
--定义游标，名称为cur_stu
cursor cur_stu is
select stuno,stuname from student order by stuno;
begin
for stu in cur_stu
loop
dbms_output.PUT_LINE(stu.stuno||'->'||stu.stuname);
--循环做隐含检查 %notfound
end loop;
--自动关闭游标
end;
END PROC_STU3;
(4).常用的使用EXIT WHEN处理游标
create or replace
PROCEDURE PROC_STU1_1 AS
BEGIN
--显示游标使用，使用exit when循环
declare
--1.定义游标，名称为cur_stu
cursor cur_stu is
select stuno,stuname from student order by stuno;
--定义变量，存放游标取出的数据
v_stuno varchar(4);
v_stuname varchar(20);
begin
--2.打开游标cur_stu
open cur_stu;
loop
--3.将游标的当前行取出存放到变量中
fetch cur_stu into v_stuno,v_stuname;
exit when cur_stu%notfound; --游标所指还有数据行，则继续循环
--打印结果
dbms_output.PUT_LINE(v_stuno||'->'||v_stuname);
end loop;
close cur_stu; --4.关闭游标
end;
END PROC_STU1_1;
二.隐式游标
1.使用演示
create or replace PROCEDURE PROC_STU4 AS
BEGIN
--隐式游标使用
update student set stuname='张燕广' where stuno='1104';
--如果更新没有匹配则插入一条新记录
if SQL%NOTFOUND then
insert into student(STUNO,STUNAME,AGE,GENDER)
values('1104','张燕广',18,'男');
end if;
END PROC_STU4;
2.说明
所有的SQL语句在上下文区内部都是可执行的，因为都有一个游标指向上下文区，此游标就是
SQL游标，与现实游标不同的是，SQL游标在PL/SQL中不需要打开和关闭，而是在执行UPDATE、
DELETE是自动打开和关闭。