orcale总结

oracle总结
常见的关系型数据：mysql,oracle,sqlserver,db2等


oracle中使用的编程语言：PL/SQL（过程语言/结构化查询语言）
SQL:分为DDL,DML,TCL,DQL
DDL:create ，drop， alter
DML/DQL: insert , update, delete , select
TCL:grant to（授予）， revoke from（给用户权限，撤销权限）


PL:过程语言（PL引擎，内置在oracle服务器中）
数据库编程：变量，逻辑语言，顺序语言，过程，函数，视图，包，触发器等。


oracle：oracle服务器和oracle客户端。通过TCP协议实现通讯，需要配置服务器端的listener.ora配置文件，客户端的tnsname.ora配置文件。其中配置通讯协议，服务器地址，服务器通讯端口，对应配置。


oracle服务器中：可以创建多个oracle数据库（一般一个服务器只创建一个数据库）例如：orcl
在数据库中以表空间来区分不同的内容（表空间相当于使用mysql时，先创建的一个数据库）。


oracle本身即是关系型数据库，也是面向对象的数据库。


DML/DQL:增删改查语句；
insert into 表名 values(值列表);
-- 创建表同时使用现有表的结构和数据插入
create table 表名
as
selectsql;


update 表名 set 列名1 = 新列值1 ,列名2 = 新列值2  ... 
where 条件;


delete from 表名 where 条件；


select 列名列表   (列筛选)
from 表名           (表名及关联表列表)
where 条件         (行筛选)
group by 分组列  (顺序分组)
having 分组后的筛选条件   （二次行筛选）
order by 排序列   （顺序排序desc降序，asc升序默认）


select * from 表名；


常用函数：------------------------------------
to_char(),to_date(),to_number();
to_date('2012-12-25 17:28:25','yyyy-mm-dd hh24:mi:ss')
to_char(sysdate,'yyyy"年"mm"月"dd"日" hh"时"mi"分"ss"秒"');
to_char(123456.25,'$999,999.99')  结果：$123,456.25


select 1+2 from dual;  结果：3
select '1'+2 from dual;  结果：3
等价于
select to_number('1')+2 from dual;  结果：3
因为oracle中连接字符为“||”，所以，在使用“+”时，表示数学运算。


oracle中的高级查询：
子查询，连接查询，联合查询


不相关子查询：相对简单（父查询语句和子查询语句之间没有联系）
相关子查询：父查询需要传递列值给子查询使用。


连接查询：
内连接：
    显示连接：使用inner  join .. on
例如：
    select a.id,a.name,b.id bid, b.name bname
    from A a 
    inner join B b
    on  a.id = b.a_id;
    隐式连接：
例如：
    select a.id,a.name,b.id bid, b.name bname
    from A a , B b
    where  a.id = b.a_id;+


外连接：
    左外： left outer join  .. on
注意：以左边的表为主要数据表（所有数据都会显示的表）,右表为匹配数据的表，若匹配到则显示数据，否则显示null
例如： 
    select *
    from A a 
    left outer join B b
    on  a.id = b.a_id;
    在结果集中将会显示所有A表数据。    


    右外： right outer join  .. on
注意：以右边的表为主要数据表（所有数据都会显示的表）,左表为匹配数据的表，若匹配到则显示数据，否则显示null
    select *
    from A a 
    right outer join B b
    on  a.id = b.a_id;
    在结果集中将会显示所有B表数据。    


联合查询：
union , union all （并集）,intersect(交集),minus;
联合查询指的是将具有相同列数及对应列类型的多条查询结果集拼接成一个查询结果集。
select * from A
union    -- A表和B中所有的非重复数据
select * from B;


select * from A
union all    -- A表和B中所有的数据
select * from B;


select * from A
intersect  -- A表中和B表中公共的数据
select * from B;


select * from A
minus  -- 在A表中存在，且B表中不存在的数据
select * from B;


分页查询：
第一步：查询满足条件的所有记录数（总条数）
selet count(*) from 表名 where 条件;  -- counts
第二步：计算总页数
int totalPages = counts % pageSize == 0 ? counts / pageSize : counts / pageSize + 1;
第三步：查询指定页数的结果
select * from (  
  select rownum rn , t.* from (
    selet * from 表名 where 条件 order by 列
  ) t
) where  rn > (pageIndex - 1) * pageSize 
      and rn <= pageIndex * pageSize;


PL/SQL块：
PL/sql编程的语法：
declare 
    -- 声明在执行过程中可能会使用的变量或对象
begin
   -- 业务过程(PL / SQL)
exception
   -- 处理在执行业务过程中发生的指定类型的异常
end;


变量： v_name  数据类型;
常量： c_name  constant 数据类型 := 初始值；


数据类型：
    标量类型：integer, varchar2...
    LOB类型：bfile, blob, clob, nclob
    属性类型：%type  ,  %rowtype


逻辑语句：
if:  
   if  条件  then 
       条件为true时，执行的代码部分
   end if;


   if  条件  then 
       条件为true时，执行的代码部分
   else 
       条件为false时，执行的代码部分
   end if;


   if  条件1  then 
       条件为true时，执行的代码部分
   elsif  条件2  then 
       条件为true时，执行的代码部分
   else 
       所有条件为false时，执行的代码部分
   end if;


case:
   case 表达式
        when 值1 then
              表达式=值1时，执行的代码块
        when 值2 then
              表达式=值2时，执行的代码块
        when 值3 then
              表达式=值3时，执行的代码块
        else 
              表达式不为值1,2,3时，执行的代码块
   end case;


   case
        when 条件1 then
              条件1为true时，执行的代码块
        when 条件2 then
              条件2为true时，执行的代码块
        when 条件3 then
              条件3为true时，执行的代码块
        else 
              条件1,2,3都不为true时，执行的代码块
   end case;


循环语句：
while:
     while 循环条件 loop
           循环操作； 
           迭代部分；
     end loop;
loop:
     loop
         循环操作；
         exit when 退出条件;
     end loop;
for:
     for 迭代变量 in 1 .. 10 loop
          循环操作
     end loop;
顺序语句：
goto: 将执行权直接转移到带有<<标记>>的部分，继续执行.
null: 当需要某个为空的程序块时，补充语法中的空语句。


异常：
exception作为声明中的一种类型


系统异常：
    oracle中已经定义好的一些全局类型的异常名称
自定义异常：
    在一个PL/SQL块中使用的局部自定义异常名称
declare
    e_myex exception;
begin
    if 1=1 then
        raise  e_myex;
    end if;
exception
    when e_myex then 
        处理自定义异常的代码部分
    when others then
        处理其他未知异常(sqlcode,sqlerrm)
end;






游标：
关键字：cursor
作用：当需要处理结果集中多行数据的部分时，使用游标提取数据.


显示游标：
declare
    cursor cur_mycursor is selectsql;   -- 声明游标 cursor mycur is select * from emp;
begin
    -- 打开游标
    if not cur_mycursor%isopne then
       open cur_mycursor;  -- 打开游标(执行selectsql)
    end if;
    -- 提示游标指定的行数据
    fetch(取来) cur_mycursor into 满足结果行的变量列表/行类型变量；
    -- 使用游标.列名获取数据
    dbms_output.put_line(cur_mycursor.name);
    -- 关闭游标
    close cur_mycursor;
end;
隐式游标：
declare
    cursor cur_mycursor is selectsql;   -- 声明游标
begin
    for r in cur_mycursor loop
        dbms_output.put_line(r.name);
    end loop;
end;
另一种隐式游标的写法：
begin
    for r in selectsql loop
        dbms_output.put_line(r.name);
    end loop;
end;


oracle中的过程（存储过程）
过程可以看成是一个带有名称和参数的PL/SQL程序。
create or replace procedure  proc_name
(参数列表)
is|as
-- 声明部分
bengin
-- 程序部分
exception
-- 异常处理部分
end [proc_name];


过程中的参数和声明变量的区别：
1.参数的类型可以是标量，属性类型等，但在使用标量类型时，不能指定长度。但是声明变量必须指定标量类型的长度。
2.参数是在调用过程时，由调用方传递初始值。而声明变量对调用方不可见。


参数类型：
in：表示输入参数类型
out：表示输出参数类型 
in out ：表示输入且输出参数类型


create or replace procedure  proc_name
(
    id in integer,
    name in out varchar2,
    result out varchar2,
)
is|as
...
end;


declare
    v_id number(3) := 1;
    v_name varchar2(20) := '张三';
    v_result varchar2(50);
bengin
    proc_name(v_id,name => v_name,result => v_result);
    dbms_output.put_line('修改'||v_id||'的姓名，结果：'||v_result||'修改后的姓名为：'||v_name);
end;




调用过程：
在一个PL/SQL块中，通过使用过程名称(参数值)来调用过程。
调用时的参数传递方式：
1.顺序传参:  proc_name(1,'tom','男');
2.命名传参:  proc_name(name => 'tom', sex => '男', id => 1);
3.混合传参：proc_name(1,sex = >'男', name => 'tom');


删除过程:
drop procedure  proc_name;