Oracle

Oracle


同义词


DBA 同义词：意思相同, 基于安全考虑? 给它一个别名，ETL数据同步,Extract_Transform_load,用来描述将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至目的端的过程。ETL一词较常用在数据仓库，但其对象并不限于数据仓库。


    create synonym yuangong for scott.emp;
    select * from yuangong;


数据的导入导出(按用户) 做小抄


    --用system用户来导出scott用户的所表和其它对象, 导出的文件放在file里
    exp system/admin ower=scott file=c:\scottbk.dmp
    
    --导入fromuser:指的是从scottbk.dmp中的用户， touser：导入到这个用户底下
    imp system/admin file=c:\scottbk.dmp fromuser=scott touser=tbtest


1. PL/SQL


什么是PL/sql, procedure language过程语言, 由sql组成。oracle对sql的扩展。让我们的sql也有处理业务逻辑能力。可实现编程


语法


    declare
    --申明部分, 定义变量, number, varchar2
    begin
    --代码
    end;
    
    示例：
    --输出员工名称和员工编号
    declare
        vnum number(10) :=10;-- :=赋值
        vempno emp.empno%type;--引用emp表中empno字段的类型
        vemprow emp%rowtype;--引用emp表中的一行记录的类型, 数据库里的一条，对应java一个对象
    begin
         dbms_output.put_line('vnum=' || vnum);
         vnum:=20;
         dbms_output.put_line('改变后的vnum=' || vnum);
         --给vempno赋值,从查询结果给出
         select empno into vempno from emp where ename='KING';
         dbms_output.put_line('KING=' || vempno);
         
         --使用行的结果
         select * into vemprow from emp where ename='KING';
         dbms_output.put_line('KING sal=' || vemprow.sal);
    end;


1.1 条件语句


if 语法


    if 条件 then
    --代码
    elsif 条件 then
    --代码
    else
    --代码
    end if;
    示例：
    declare
        i number(10) :=&inputnum; --相当从控制台输入数据 赋给inputnum，再交给i
    begin
        if i=1 then
            dbms_output.put_line('我是supper man');
        elsif i=2 then
            dbms_output.put_line('我是spidder man');
        else
            dbms_output.put_line('我是iron man');
        end if;
    end;


case when, java switch


语法：


    case 表达式 
    when 值 then
    --代码
    when 值 then 
    --代码 
    else
    --代码
    end case;
    示例：
    declare
        i number(10) :=&inputnum; --相当从控制台输入数据 赋给inputnum，再交给i
    begin
        case i when 1 then
                dbms_output.put_line('我是supper man');
            when 2 then
                dbms_output.put_line('我是spidder man');
            else
                dbms_output.put_line('我是iron man');
        end case;
    end;


1.2循环


for, while, goto, loop


loop 循环 (多)


    loop
    --代码
    exit when 条件
    --代码
    end loop;
    示例，打印1到10
    declare
        i number(10) :=1;
    begin
        loop
            dbms_output.put_line(i);
            exit when i=10;
            i:=i+1;
        end loop;
    end;


while循环


    while 条件
    loop
    --代码
    end loop;
    示例
    declare
        i number(10) :=1;
    begin
        while i<=10
        loop
            dbms_output.put_line(i);
            i:=i+1;
        end loop;
    end;


for 循环 (用的多)


    --reverse 反转 i--;
    for i in[reverse] 范围 loop
    --代码
    end loop;
    示例
    declare
    
    begin
        for i in reverse 1..10 loop
            dbms_output.put_line(i);
        end loop;
    end;


goto标记 少用(快被遗忘)


    <<名称>>
    --代码
    goto 名称;
    
    示例
    declare
        i number(10) :=1;
    begin
        <<a>>
            dbms_output.put_line(i);
            i:=i+1;
            if i <= 10 then
                goto a;
            end if;
    end;


1.3异常(例外) exception


抛出:


raise 异常


RAISE_APPLICTION_ERROR(异常的编号，异常的描述), RAISE_APPLICATION_ERROR(-9527, '异常信息');


异常的编号: -20000 -> -20999


捕获:


    declare
    
    begin
    --代码
    exception 
    when 异常的类型 then
    --异常的处理
    when 异常的类型 then
    --异常的处理
    when others then
    --异常的处理
    end;


常见的异常：


- zero_divide 除0异常
- no_data_found 没有找到记录
- value_error 类型转换失败
- too_many_rows  用一行的变更接收时返回了多条记录


    --异常
    declare
        i number(10) :=1;
        vrow emp%rowtype;
    begin
        --i:= i/0; 
        --select ename into i from emp where empno=9999;
        select * into vrow from emp;
    exception
        when zero_divide then
           dbms_output.put_line('除数为0'); 
        when value_error then
           dbms_output.put_line('类型不匹配'); 
        when no_data_found then
           dbms_output.put_line('没有找到数据'); 
        when too_many_rows then
           dbms_output.put_line('多条记录赋值给单行变量'); 
        when others then
            dbms_output.put_line('发生了其它异常'); 
    end;


1.4自定义异常


语法:


    declare
    异常名称 exception
    pragma exception_init(异常名称, 错误编号);--给绑定异常的编号， 错误编号：标识错误的类型
    begin
    
    end;
    declare
        myexe exception;
        myexe2 exception;
        pragma exception_init(myexe2, -9527);--给绑定异常的编号
    begin
        --raise myexe;
        raise myexe2;
    exception 
       when myexe then
            dbms_output.put_line('myexe'); 
       when myexe2 then
            dbms_output.put_line('myexe2 sqlcode=' || sqlcode); 
       when others then
            dbms_output.put_line('发生了其它异常');  
    end;


2. 游标(重点)


数据库用来保存结果集的一种数据结构, 类似java resultSet


语法


    declare 
      --普通的游标
      cursor 名称[(参数名称 参数类型)] is 查询语句 [ 条件]
      --系统引用游标
      名称 sys_refcursor; --打开时，才写查询语句, 关键词for
    begin
    --代码
    end;


游标取值的步骤


1. 打开游标 open cursor     
2. 开启循环         loop;
3. 取值                 fetch 游标名称 into 变量名
4. 关闭游标          close 游标名称


    --普通游标
    declare
        cursor mycursor1 is select * from emp;
        vrow emp%rowtype;
    begin
        open mycursor1;
            loop
            fetch mycursor1 into vrow;
            exit when mycursor1%notfound;--%notfound，游标中已经取到最后，没有数据了
                dbms_output.put_line('员工编号:' || vrow.empno || ' 员工姓名:' || vrow.ename);  
            end loop;
       close mycursor1;
    end;
    
    --带参游标
    --输出某个员工的工资
    declare
        cursor mycursor1(vempno emp.empno%type) is select * from emp where empno=vempno;
        vrow emp%rowtype;
    begin
        open mycursor1 (7788);
            loop
            fetch mycursor1 into vrow;
            exit when mycursor1%notfound;--%notfound，游标中已经取到最后，没有数据了
                dbms_output.put_line(' 员工姓名:' || vrow.ename || ' 工资:' || vrow.sal);  
            end loop;
       close mycursor1;
    end;
    
    --系统游标
    --输出所有员工的名称与工资
    declare
        mycursor1 sys_refcursor;
        vrow emp%rowtype;
    begin
        open mycursor1 for select * from emp;
            loop
            fetch mycursor1 into vrow;
            exit when mycursor1%notfound;--%notfound，游标中已经取到最后，没有数据了
                dbms_output.put_line(' 员工姓名:' || vrow.ename || ' 工资:' || vrow.sal);  
            end loop;
       close mycursor1;
    end;


使用for循环来读取游标, 自动打与自动关闭 (居多)


    --输出所有员工的名称与工资
    declare
        cursor mycursor1 is select * from emp;
        vrow emp%rowtype;
    begin
        for vrow in mycursor1 loop
            exit when mycursor1%notfound;--%notfound，游标中已经取到最后，没有数据了
                dbms_output.put_line('员工编号:' || vrow.empno || ' 员工姓名:' || vrow.ename);  
        end loop;
    end;


游标一般都是与存储过程一起使用


3 存储过程(Store Procedure)(重点)


什么存储过程? 封装了一段的过程语言，放到数据库，供以后调用。高效，编译好了


语法


    CREATE OR REPLACE PROCEDURE 名称(参数 IN/OUT 参数的类型,....) -- in 指输入参数， out输出参数
    is|as
    --申明部分
    begin
    
    end;
    
    --输出指定员工编号的工资
    create or replace procedure pro_printsal(inempno in number)
    is
       vsal emp.sal%type;
    begin
        select sal into vsal from emp where empno=inempno;
        dbms_output.put_line(vsal);
    end;
    
    call pro_printsal(7788);--3000
    
    --带输出参数
    create or replace procedure pro_outsal(inempno in number, outsal out emp.sal%type)
    is
       
    begin
        select sal into outsal from emp where empno=inempno;
    end;
    --带输出参数的调用 
    declare
        vsal emp.sal%type;
    begin
        pro_outsal(7369,vsal);
        dbms_output.put_line(vsal);
    end;


过程的调用


    call 名称(参数的值);
    --pl/SQL
    declare
        ii number(10) :=10;
    begin
        pro_test1(ii);
    end;


4. 存储函数


输入参数进行处理后返回结果, 必须有返回值


过程和函数的区别


1. 函数可以放sql中执行
2. 函数必须有返回值
3. 函数能实现的功能，过程也能实现
4. 过程能实现的功能，函数也能实现
5. 本质上没有多少区别
   函数的参数默认是输入类型
   函数多数是被过程调用的
   函数多数被过程替换
   函数与过程可以相互嵌套调用


语法:


    CREATE OR REPLACE FUNCTION 函数名称(参数 in|out 类型,...) return 类型
    is|as
    --申明部分
    begin
    --代码部分
    return 具体的值
    end;
    
    示例：根据员工编号返回员工工资
    create or replace function fun_printsal(inempno number) return emp.sal%type
    is
       vsal emp.sal%type;
    begin
    --赋值
        select sal into vsal from emp where empno=inempno;
        dbms_output.put_line(vsal);
        --返回值
        return vsal;
    end
    
    --调用方式,sql
    select fun_printsal(empno) from emp;--每条记录都调用了函数，输入的是员工编号，返回工资
    --pl/sql调用
    declare
        vsal emp.sal%type; --定义变量接收返回值
    begin
        vsal := fun_printsal(7788); -- 把函数的返回值赋给了vsal
        dbms_output.put_line(vsal);
    end;
    
    --带输出参数,输入的是员工编号，输出参数员工名称, 还返回了员工的工资
    create or replace function fun_outsal(inempno number, outename out emp.ename%type) return emp.sal%type
    is
       vsal emp.sal%type;
    begin
        --查询语句赋值如果出现多个，用逗号分割, 顺序必须一致
        select sal,ename into vsal,outename from emp where empno=inempno;
        dbms_output.put_line(vsal);
        --返回值 的类型必须跟申明的部分一样 return 类型是什么，返回的类型就是什么
        return vsal;
    end;
    
    --调用
    declare
        vsal emp.sal%type;
        vname emp.ename%type;
    begin
        vsal := fun_outsal(7788,vname);
        dbms_output.put_line(vname || ':' || vsal);
    end;
    
    --过程调用函数
    create or replace procedure pro_callfun(vempno in emp.empno%type)
    is
        vsal emp.sal%type; -- 接收fun_outsal返回的值
        vname emp.ename%type; -- 做为输出参数传给fun_outsal
    begin
        vsal := fun_outsal(vempno,vname);
        dbms_output.put_line(vname || ':' || vsal);
    end;
    
    call pro_callfun(7369);


5. 触发器


触发器什么？


数据库中，对表执行操作时发生的事件时执行一段过程语句. 不能用过程语言去调用，只能通过触发。


说白：对表进行insert, update, delete的时触发一段过程的语句的执行


语法


    create [or replace] trigger 名称
    before|after --之前或之后触发
    insert|update|delete 
    on 表名
    [for each row] -- 表示， 
    declare
    --申明部分
    begin
    --代码
    end;


类型：


- 行级触发器: 影响了多少条记录就触发多少次, 关键词for each row;
  ：new 代表 新的记录
   ：old 代表 旧的记录
- 语句级触发器：语句执行了一次，只触发一次，没有关键词for each row


    --语句级触发器
    create or replace trigger tri_test1
    before
    update 
    on emp
    declare 
        --申明部分
    begin
        dbms_output.put_line('触发了');
    end;
    --调用
    update emp set sal=sal+1000;


    --行级触发器
    create or replace trigger tri_test2
    before
    update
    on emp
    for each row
    declare
    begin
        --打印出新的工资和原来的工资
        dbms_output.put_line(:old.ename || ' 原来的工资=' || :old.sal || '新的工资=' || :new.sal);
    end;
    --调用
    update emp set sal=sal+100;