oracle简单语法

【oracle语法】
[oracle数据类型]
    char:固定长度 存储字符 最多存储2000字节
    varchar2:可变长度 存储字符 最多存储4000字节
    long ：大文本类型 最大存储2个G
    number：数值类型  number(5)
    date：精确到秒
    timestamp：精确到秒的小数点后9位
    clob ：存储字符  可以存储4个G
    blob: 存储图像声音视频  最多存储4个G
[创建表空间]
    create tablespace waterboss
    datafile 'c:\waterboss.dbf'
    size 100m
    autoextend on
    next 10m
[创建用户]
    create user wateruser
    identified by xiaoxiao
    default tablespace waterboss
[给用户赋予权限]
    grant dba to wateruser
[创建表]
    cteate table T_OWNERS(
     ID NUMBER PRIMARY key,
     name varchar2(30),
     addressid  number ,
     HOUSENUMBER VARCHAR2(30),
     WATERMETER VARCHAR2(30),
     ADDDATE DATE,
     OWNERTYPEID NUMBER
    )
[修改表]
    ALTER TABLE 表名称 ADD(列名 1 类型 [DEFAULT 默认值]，列名 1 类型[DEFAULT 默认值]...)
    ALTER TABLE 表名称 MODIFY(列名 1 类型 [DEFAULT 默认值]，列名 1 类型[DEFAULT 默认值]...)
    ALTER TABLE 表名称 RENAME COLUMN 原列名 TO 新列名
    ALTER TABLE 表名称 DROP COLUMN 列名   ALTER TABLE 表名称 DROP (列名 1,列名 2...)
[删除表]
    drop table 表名
[插入数据]
    INSERT INTO 表名(列名 1，列名 2，...)VALUES(值 1，值 2，...)
    插入后提交commit；
[修改数据]
    update 表名 set 列名=值; commit;
[删除语句]
    delelte from 表名 where 列名=值; commit;
    truncate table 表名  不需提交直接删除
    delete 删除后可以回滚 truncate不能回滚
【数据导出】
    整库导出     exp system/xiaoxiao full=y    exp system/xiaoxiao file=文件名.dmp full=y
    整库导入     imp system/xiaoxiao full=y    imp system/xiaoxiao full=y file=water.dmp
    按用户导出     exp system/xiaoxiao owner=wateruser file=wateruser.dmp
    按用户导入     imp system/xiaoxiao file=wateruser.dmp fromuser=wateruser
    按表导出      exp wateruser/xiaoxiao file=a.dmp tables=t_account,a_area
    按表导入     imp wateruser/xiaoxiao file=a.dmp tables=t_account,a_area
[查询]
    普通查询：
             select * from t_owners where (name like '%刘%' or housenumber like '%5%') and addressid=3
                    and 的优先级比or大 所以用（）来改变优先级
             select * from T_PRICETABLE t where maxnum is null
    伪列查询  rowId(返回改行的物理地址)  rowNum(返回行号)
    左外连接  ：在右边的表上加上(+)
             select a.id,a.name,b.year,b.month,b.money from t_owners a left join t_account b on a.id=b.ownerid
             select a.id,a.name,b.year,b.month,b.money from t_owners a , t_account b where a.id=b.ownerid(+)
    右外连接 ：在左边的表上加上(+)
            select a.id,a.name,b.year,b.month,b.money from t_owners a right join t_account b on a.id=b.ownerid
            select a.id,a.name,b.year,b.month,b.money from t_owners a , t_account b where a.id(+)=b.ownerid
    子查询   ：
            select a.id ,a.name,(select name from t_ownertype where id=a.ownertypeid)
            from t_owners  a where a.ownertypeid in (select id from t_ownertype where name like '%居民%')
            
            select a.id,a.name,(select name from t_address b where id=a.addressid) addressname,
            ( select (select name from t_area where id=addressid) from t_address where id=a.addressid) areaname from t_owners a
    分页   ：
            select rownum ,t.* from t_account t where rownum<=10 order by t.usernum desc

            select * from
            (select rownum r,t.* from
            (select * from t_account order by usernum desc) t where rownum<=20) a where r>10
    字符函数 ：
            select length('ABCD') from dual;
            select substr('qrefdgrt' ,3,4) from dual   结果efdg
            拼接：select 'av' || 'love' from dual    select concat('av','love') from dual
    数值函数：
            select round(192.55,1) from dual   结果192.6 四舍五入
            select trunc(192.79,1) from dual     结果192.7   直接截取
    日期函数：
            select add_months(sysdate,3)from dual
            select greatest('2017/6/14 10:35:23','2017/9/14 10:35:23') from dual    返回最晚的日期
            select least('2017/6/14 10:35:23','2017/9/14 10:35:23') from dual  返回最早的日期
            select last_day(sysdate) from dual   返回月末的最后一天
            select trunc(sysdate) from dual   
            select trunc(sysdate,'YYYY') from dual           
            select trunc(sysdate,'mm') from dual           
            select ROUND(sysdate) from dual       超过中午12点结果是下一天       
            select ROUND(sysdate,'YYYY') from dual
            select ROUND(sysdate,'mm') from du
            select next_day(sysdate,'monday') from dual;   返回下一个周一的日期
            select months_between(to_date('20090228', 'yyyymmdd'), to_date('20080501', 'yyyymmdd')) as months from dual;   返回两个月份的差值(有小数)
    转换函数：
            转字符串
            select TO_CHAR(1024) from dual
            select TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
            转日期
            select TO_DATE('2017-01-01','yyyy-mm-dd') from dual
            转数字
            select to_number('100') from dual
    空值处理函数：
            NVL（检测的值，如果为 null 的值）
            select nvl(null,'aaa') from dual
            NVL2（检测的值，如果不为 null 的值，如果为 null 的值）；
            select PRICE,MINNUM,NVL2(MAXNUM,to_char(MAXNUM) , '不限')from T_PRICETABLE where OWNERTYPEID=1
    条件取值函数：  
            decode(条件,值 1,翻译值 1,值 2,翻译值 2,...值 n,翻译值 n,缺省值)
            select name ,decode(ownertypeid,'1','居民','2','行政事业单位','3','商业')  from t_owners
            另一种写法
             select name ,case when ownertypeid=1 then '居民'
                               when ownertypeid=2 then '行政事业单位'
                               when ownertypeid=3 then '商业'    end  as ownertype
             from t_owners
             select name, case ownertypeid when 1 then '居民'
                                           when 2 then '行政'
                                           when 3 then '商业'
                                           else '其他' end
             from t_owners
    行列转换：
            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
            
            没转换的：
             select b.name  ,a.month , sum(money) from t_account a ,t_area b where a.areaid=b.id
             group by a.month,b.name  order by a.month ,b.name
    排名：
            RANK 相同的值排名相同，排名跳跃
                select rank() over(order by usenum desc ),usenum from  T_ACCOUNT
            DENSE_RANK 相同的值排名相同，排名连续
                select  DENSE_RANK() over(order by usernum desc) ,usernum from t_account
            ROW_NUMBER 返回连续的排名，无论值是否相等
                select  ROW_NUMBER() over(order by usernum desc) ,usernum from t_account
            用row_number()实现分页
                select * from
                (select row_number() over(order by usenum desc )
                rownumber,usenum from T_ACCOUNT)
                where rownumber>10 and rownumber<=20
    集合：
            union all   返回查询的所有记录包括重复
            union         返回查询的所有记录不包括重复
            intersect   交集  返回两个查询共有的记录
            minus       差集  返回第一个查询去掉第二个查询后的记录
            select * from table1 union  select * from table2  字段类型和个数要一致
【视图】    
    视图：对sql语句的封装  封装成一个虚拟表
         作用：简化数据操作
    视图创建 ：create (or replace) (force) view view_name
                as 查询语句
                (whth check option)
                (with read only)
                force:代表不管基表是否存在都创建视图
    视图删除：drop view view_name    
    注意：修改视图数据  对应表数据也会修改  但是视图一般不会用来更新数据  修改原表  视图查询也会改变
    复杂视图：多表关联查询  其中有一个表是键保留表(把主键保留下来的表)  键保留表可以修改数据  其他表不能修改
    物化视图：视图是一个虚拟表，每次访问都会查询一次
              物化视图相当于是真实的表 ，是建立的副本，需要占用存储空间
        作用：创建物化视图后查询相当于单表操作 提高效率
    创建物化视图：
                  create materialized view view_name
                  (build immediate(默认) | build deferred)//建立时数据是否生成
                  (refresh [fast|complete|force(默认)])//刷新方式
                  on [commit|demand(默认)] | start with (start_time) next (next_time) //基表改变时物化视图的刷新方式
                  as 查询语句
                  
            fast:增量刷新  作用：提高效率 只更新修改的基表数据  
                增量刷新前提：创建基表日志  create materialized  view log on t_address with rowid
                              创建视图时查询语句中要查询rowid
            complete：完全刷新
            force：自动选择  根据情况判断是选择fast还是选择complete
            
            on commit 在基表做提交操作的时候刷新物化视图
            on demand：手动刷新（默认选项）  
                        begin
                          DBMS_MVIEW.refresh('物化视图名字','c') //c 指的是complete完全刷新
                        end;
                        或者在命令窗口执行：exec DBMS_MVIEW.refresh('物化视图名字','c')
                        
            如果选择了build deferred   第一次需要手动刷新才能有数据
【序列】
    oracle提供的用于产生一系列唯一数字的数据库对象
    创建：
        create sequence 序列名
           创建：create sequence seq_test   使用：seq_test.nextval
    目的：生成主键值
            cache 提高性能
        create sequence seq_name //创建序列名称
        [increment BY n] //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是 1
        [start with n] //开始的值,递增默认是 minvalue 递减是 maxvalue
        [{maxvalue n | NOMAXVALUE}] //最大值
        [{minvalue n | NOMINVALUE}] //最小值
        [{cycle | NOCYCLE}] //循环/不循环
        [{cache n | NOCACHE}];//分配并存入到内存中 默认值是20
    修改序列：alter sequence 序列名称 maxvalue 500 cycle；
    删除序列：drop sequence  序列名称;
    使用：select 序列名.nextval from dual
          select 序列名.CurrVal FROM DUAL; 查询当前值
【同义词】
    create [public] synonym 名字 for object;  (object表示表 视图 序列)
【索引】
    加速数据存取的数据对象   需要占据存储空间  存储的是rowid
    创建基本索引：create index 索引名称 on 表名(列名);
    创建唯一索引：create unique index 索引名称 on t_owners（watermeter）watermeter必须是唯一的不重复
    创建复合索引：create  index 索引名称 on t_owners（name,addressid）生成一棵树      
                复合索引：查询条件的顺序要和建立索引的顺序一致
    反向键索引：create index 索引名 on 表名(列名) reverse    该列数值连续排序
    位图索引：低基数列适合建立位图索引  例如：性别  民族   查询的时候只能用“=” 不能用范围查询 select * from owner where sex='男'
                create bitmap index 索引名称 on 表名(列名)
【pl/sql】    
    基本语法结构：
        declare
         声明变量   //变量名  类型(长度)     变量名:=变量值
        begin
        代码逻辑
        exception
        异常处理
        end;
            declare
            v_price number(10,2);
            v_usernum number;
            v_usernum2 number(10,2);
            v_money number(10,2);
            begin
              v_price:=2.5;
              select usernum  into v_usernum from t_account where id=93;
              v_usernum2:=v_usernum/1000;
              v_money:=v_price*v_usernum2;
              dbms_output.put_line('金额：' || v_money || '水表数：' || v_usernum);
            exception
             when no_date_found then
             dbms_output.put_line('没有找到对应数据');
            end;
    属性类型：
        引用型：v_usernum t_account.usernum%type
        记录型：v_account T_account%rowtype
                select * into v_account from t_account where id=93;
                v_usernum2:=v_account.usernum/1000;
    条件判断：
        if  条件  then  业务逻辑
        elsif  条件  then   业务逻辑
        else  业务逻辑
        end if;
    循环：
        无条件循环：
             loop    
            业务逻辑
            exit  when 条件;
            end loop;
        有条件循环：
            while 条件
            loop
            业务逻辑
            end loop;
        for循环：
            for v_num  in  1 .. 100  //v_num不需要声明
            loop
            业务逻辑
            end loop;
    游标：
        声明游标：
        cursor 游标名称(参数) is sql语句(where条件用参数来限制)
        使用游标：
        open 游标名称(参数)
          loop
            fetch 游标名称 into 变量值
            exit when 游标名称%notfound
          end loop;
        close 游标名称
            declare
              cursor cur_pricetable(v_ownertypeid number) is select * from t_pricetable where ownertypeid=v_ownertypeid;
              v_pricetable t_pricetable%rowtype;
            begin
              open cur_pricetable(1);
              loop
                  fetch cur_pricetable into v_pricetable;
                  exit when cur_pricetable%notfound;
                  dbms_output.put_line('价格' ||v_pricetable.price );
             end loop;
              close cur_pricetable;
            end;
        游标的简单写法：
            declare
              cursor cur_pricetable(v_ownertypeid number) is select * from t_pricetable where ownertypeid=v_ownertypeid;
            begin
                for v_pricetable in cur_pricetable(2)   
                loop
                    dbms_output.put_line('价格' ||v_pricetable.price );
                end loop;
            end;
    存储函数：        
        CREATE [ OR REPLACE ] FUNCTION 函数名称
        （参数名称 参数类型, 参数名称 参数类型, ...）
        RETURN 结果变量数据类型
        IS
        变量声明部分;
        BEGIN
        逻辑部分;
        RETURN 结果变量;
        [EXCEPTION
        异常处理部分]
        END;
            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;
    存储过程：
        create [or replace] procedure 存储过程名
        （参数名，类型,参数名，类型……）//in 或者out，或者 in out
        is/as
        变量声明部分
        begin
        逻辑部分
        [exception
        异常处理部分]
        end;
            create or replace procedure pro_owners2
            (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_oners values
              (v_id,v_name,v_address,v_housenumber,v_watermeter,v_ownertypeid);
              commit;
            end;
        调用存储过程(无输出参数)：call pro_owners('潇潇','1','1-1','58689','1');
        调用存储过程(带参数调用：)
                                    declare
                                      v_id number;
                                    begin
                                      pro_owners2('潇潇3','1','1-1','67890','1',v_id);
                                      dbms_output.put_line(v_id);
                                    end;
        java中JDBC调用存储过程(无参数) CallableStatement stmt=conn.prepareCall("{call pro_owners(?,?,?,?,?)}");
        java中JDBC调用存储过程(有参数)
                                     CallableStatement stmt=conn.prepareCall("{call pro_owners(?,?,?,?,?)}");
                                     //注册传出参数的类型
                                      stmt.registerOutParameter(6, OracleTypes.NUMBER);
                                       stmt.execute();
                                        //拿出传出参数值
                                       id=stmt.getLong(6);
【触发器】    
    数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的
    数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发
    器中定义的语句序列。
        create [or replace] trigger 触发器名
        before | after   //前置触发器   后置触发器
        delete or insert or update of 列名  //没有列名默认所有列都触发
        on 表名
        for each row when(条件)  //for each row 代表行级触发器：执行的sql所影响的行数，触发器会执行[行数]次    语句级触发器：一个sql触发器执行一次
        declare
        begin
         语句
        end;
    触发器中的触发语句与伪记录变量的值
                 :old          :new  
        insert    空          空
        update   更新前值    更新后值
        delete   删除前值     空
    前置触发器：
        create or replace trigger tri_account_num1
        before
        update of num1
        on t_account
        for each row
         declare
         begin
           :new.usernum:=:new.num1-:new.num0;
         end;
    后置触发器：
        
        create or replace trigger t_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
            );
         end;