oracle高级之-oracle 基础

---内容理解自《Oracle从入门到精通》，参考

1.oracle 是过程化语言，遵循标准的SQL，同时对SQL进行了扩展。PL/SQL是oracle的专用语言，是sql的扩展实现，可以在内部嵌套普通sql语句。继而把sql的数据操作（DM),数据查询(DQ)功能集成到oracle中，实现更复杂的业务逻辑。

2.PLSQL特色：通过if、loop控制sql流程，同时还可以定义变量，实现sql语句之间数据的传递。

3.PLSQL以语句块为单位。

模板如下：

[Declare]--声明部分,可有可无,变量，常量，游标

  begin --必须存在的

[exception]--异常，可有可无，可以使用when...then处理，也可以进行回滚操作

  end --必须存在的

4.sql可以接收集合输入，输出集合；或者sql结果作为另一个sql的输入(sql子查询）

5.sql分类：

数据定义语句(DDL)，--create，alter,drop

数据查询(DQL),-select

数据操作(DML),-insert,update,delete

数据控制(DCL),-grant,revoke

事务控制语言(TCL)--commit,rollback,savepoint

6.sql语句中，不区分大小写，但是在查询条件中字符，格式化后的日期区分大小写

7.在oracle中，创建用户，oracle就会创建一个对应于该用户名的模式，然后将改用户的数据库对象，放置在这个模式下，进行管理。由模式引发的就是，查询模式内的数据可以不适用模式名称，但是在模式之外的，需要指定模式名称。

8.在select语句中，可以针对数字，日期进行算术运算。

9.为列指定别名，既可以使用as指定，也可以不使用as ，在列后写明别名即可。

10.比较筛选中，可以使用ANY,all。like ,in ,between...and ,isnull ,都可以和not进行组合

11.往表中插入数据，即可以在表名后指定列名，也可以不指定列名，或者指定部分，但是必须保证部分中不可以出现null。还可以将select查询出的结果，作为输入，保存到新的表中。

12.关联查询相对来说比子查询快些。子查询分：单行子查询，多行子查询(查询结果多行）

13.删除数据，delete可以回滚的，truncate不能进行回滚，但是速度快于delete.

14.事务存储ACID性，一下情况自动执行：

   @1.显示的执行commit

   @2.显示rollback

   @3.执行数据定义语句，alter,create,drop

   @4.执行数据控制，grant,revoke

   @5.正常的断开数据库连接，退出sqlplus环境。

15.plsql注释：--和/**/

二：plsql控制语句

1.选择语句
  @1.if...then...end if
  @2.if...then...else..end if
  @3.if...then...elseif...then...else ...end if
  @4.case

declare
  seasion int:= 3;
  aboutInfo varchar2(50);
begin
   case seasion
     when 1 then
       aboutInfo := seasion||'季度包括1，2,3月份';
     when 2 then
       aboutInfo := seasion||'季度包括4,5,6月份';
      when 3 then
        aboutInfo := seasion||'季度包括7,8,9月份';
      when 4 then
        aboutInfo := seasion || '季度包括10,11,12月份';
    end case;
        dbms_output.put_line(aboutInfo);
    end;

2.循环语句
 @1.loop
loop
  sql_sentence;
  exit when condition
end loop;

declare
  var_sum int := 0;
  i int := 0;
begin
    loop
        var_sum := var_sum + i;
        i := i + 1;
      exit when var_sum > 20;
    end loop;
    dbms_output.put_line(i);
end;    

 @2.while
while condition_sql loop
  sql_sentence;
end loop;
 @3.for
for var_num in [reverse] lower_limit..upper_limit loop
  plsql_sentence;
end loop;
3.oracle 游标

  @1.显示游标

声明游标：

 cursor cur_name[([param1,param2...])] --param_name[in]dataType[{:=defualt}||:=para_value]

[return_type]

is

select_senctence;

打开游标：

open cur_name[([param1,param2...])];

利用游标，把结果集中的一行数据，读取到变量中

fetch cur_name into{variable}--默认结果集第一行，读取一行后，自动跳转到下一行。

关闭游标：

create or replace procedure crm_job_prod(baseDate in sinocrm.crm_customer_base.create_time%TYPE)
is
--这里的时间需要进行明确的获取某一天的
      CURSOR retcursor(cDate date) is
        select p.* from sinocrm.crm_elasticScreen_view p where (p.inputtime is not null and (trunc(p.inputtime) - trunc(sysdate) = 0) )or (p.modifytime is not null and  (trunc(p.modifytime) - trunc(sysdate) = 0));
      row_loc retcursor%rowtype;
      v_cnt number;
begin
     open retcursor(baseDate);
       fetch retcursor into row_loc;--其中为了标注数据的来源，使用了备用字段field150,根据代码意思，根据customer_code进行数据同步
         while retcursor%found loop
          select count(*) into v_cnt from dual where exists (select se.* from sinocrm.crm_customer_base se where se.customer_code = row_loc.cid and se.field48 = row_loc.identityno);
           if v_cnt = 0--依据身份信息，进行新增判断
               then insert into sinocrm.crm_customer_base(customer_id,create_id,tenant_id,create_organ_id,customer_type,field50,customer_code,customer_name,field48,field49,phone,valid_status,create_time,touch_time,field150)
               values(SYS_GUID(),'userAdmin','default','rootOrganId','2',row_loc.Vipcustkind,row_loc.cid,row_loc.name,row_loc.Identityno,row_loc.Company,row_loc.Address,'0',sysdate,sysdate,'sql');
           else--身份证作为唯一标示，身份证存在，进行更新
            update sinocrm.crm_customer_base se set
               se.customer_name = row_loc.name,se.field49 = row_loc.Company,se.phone = row_loc.Address,
               se.field50 = row_loc.Vipcustkind,se.edit_time = sysdate,se.field150 = 'sql',se.edit_organ_id='rootOrganId',
               se.eidt_id = 'userAdmin',se.tenant_id = 'default',se.valid_status = '0'
               where se.customer_code = row_loc.cid and se.field48 = row_loc.identityno;
          end if;
           fetch retcursor into row_loc;
         end loop;
       close retcursor;
--异常
exception
  when no_data_found then
     dbms_output.put_line('no value');
  when too_many_rows then
      dbms_output.put_line('too many rows');
end;

  @2.隐式游标

系统创建的，在处理update，delete，甚至select时出现的

5.游标属性：

cur_name%found--查询到数据

cur_name%notfound--没有查询到数据

cur_name%isopen--游标是否

cur_name%rowcount--数字类型

6.异常

 @1.oracle预定义异常--plsql违反oracle系统内部规定的设计规范，oracle自动引发预定义异常，20多个。

 @2.用户自定义异常

    .违反oracle系统的设计规范，发生的错误代码(针对错误编号异常)

      步骤：

    声明语句Declare中，定义Exception变量

    使用Pragma exception_init 将可能发生的错误编号与上述的变量关联

    在Exception中，针对这个excpition变量进行处理

declare 
  bhexcp exception;
  pragma exception_init(bhexcp,-00001);--关联
 begin
   insert into dept values(10,'软件开发部','北京');
 exception
   when bhexcp then 
     dbms_output.put_line('主键不可重复');
 end;

    .违反用户业务规则异常

   声明语句Declare中，定义Exception变量

   业务逻辑的违反在begin中，发生时使用raise抛出上述定义的异常变量

   然后在excption中，针对这个excption变量进行处理

declare 
  null_exp exception;
  dept_row dept%rowtype;
begin
  dept_row.deptno := 66;
  dept_row.dname := '软件开发';
  insert into dept values(dept_row.deptno,dept_row.dname,dept_row.loc);
  if dept_row.loc is null then
    raise null_exp;
   end if;
  exception
    when null_exp then 
      dbms_output.putline('loc字段不可为空');
      rollback;
    end;