oracle存储过程相关学习

/***************************内连接************************************/
/*内连接 inner join 的作用和 where 的写法，查询结果无区别 
      使用内连接进行多表关联查询时，返回的查询结果中只包含符合查询条件和连接条件的行，
      内连接消除了与另一个表中的任何不匹配的行。
*/
select *
  from tms_employee_info a
  join tms_teacher_information b on a.empno = b.teacher_no
                                and a.series_id = b.series_id
                                and a.series_id = '02';
select *
  from tms_employee_info a, tms_teacher_information b
 where a.empno = b.teacher_no
   and a.series_id = b.series_id
   and a.series_id = '02';

/*************************外连接******************************/
/*外连接扩展了内连接的结果集，处理返回所有匹配的行外，
        还会返回一部分或者全部不匹配的行，其主要取决于外连接的类型。
                    左外连接  left outer join/ left join
                    右外连接  right outer join/right join
                    全外连接  full outer join/full join
                    */
select *
  from tms_employee_info a
  left join tms_teacher_information b on a.series_id = b.series_id
                                     and a.empno = b.teacher_no
                                     and a.series_id = '02';

/*左（右）外连接的查询结果中不仅包含了满足连接条件的数据行，而且还包含左（右）表中不满足接条件的数据行
   全外连接返回满足连接条件的数据行，同时还会返回两个表中不满足的数据行
*/

/*************自然连接******natural join*********************************************/

/*自然连接，会将第一个表中的列与第二个表中具有相同名称的列进行自动连接。
   在自然连接中，用户不要需要明确指定进行连接的列，这个任务由Oracle系统自动完成*/
select concat('hello','world') information from dual;
select ini  full outerse

select lower(t.um_id) low,upper(t.um_id) up from tmsdata.tms_employee_info t 

/*replace(str，str1，str2)函数：str为原始字符串，str1为出现在str中的字符串，str2是把str中出现的所有str1替换为str2*/
select replace('Bad Luck Bad Girl','Bad','Good') from dual;


/*substr('str',i,j)函数截取字符串，i为截取开始位置，j为截取的长度*/
select substr('MessageBox',8,3) from dual;

/* ABS(NUM) 取数据的绝对值*/
select abs(-333) from dual;

select trunc(888.123,2) from dual;
select trunc(123.123,-2) from dual;

select round(3.1415926,1) from dual;

select power(2,3) from dual;

/************************loop循环***********************************/
declare 
  sum_i int := 0;
  i int := 0;
  begin
    loop
        i:=i+1;
        sum_i:=sum_i+i;
        exit when i=100;
    end loop;
    dbms_output.put_line('loop循环----前100个自然数的和是：'||sum_i);
 end;
 
/**********************while循环*********************************/ 
declare 
 sum_i int :=0;
 i int := 0;
 begin 
   while i<=99 
      loop
         i:=i+1;
         sum_i:=sum_i+i;
      end loop;
   dbms_output.put_line('while循环----前100个自然数的和是：'||sum_i);
 end;
 
/**********************for循环*********************************/
declare
  sum_i int := 0;
begin
  for i in reverse 1 .. 100 loop /*默认为递增，reverse标志为递减*/
    if mod(i, 2) = 0 then
      sum_i := sum_i + i;
    end if;
    dbms_output.put(' '||i);
  end loop;
  dbms_output.put_line('');
  dbms_output.put_line('for循环---前100个偶数的和是；'||sum_i);
end;

/**********************cursor：游标*********************************/
       /*************1、显式游标**************/
declare
  cursor cur_emp(var_branchId in varchar2:='4') --声明一个游标叫cur_emp
     is select t.empno,        --此处的查询出的结果值，要与record记录类型中的成员变量保持数量一致
               t.emp_name,
               t.branch_id 
           from tms_employee_info t 
               where t.branch_id=var_branchId; 
--以下是显示声明游标的方式               
  type record_emp is record --声明一个记录类型（record类型）
  (
      --定义当前记录的成员变量
       var_empno tms_employee_info.empno%type,
       var_empname tms_employee_info.emp_name%type,
       var_branchId tms_employee_info.branch_id%type
  );
  emp_row record_emp; --声明一个record_emp类型的变量
  begin
    open cur_emp; --打开游标
    fetch cur_emp into emp_row;  --先让指针指向结果集中的第一行，并将值保存到emp_row中
    while cur_emp%found loop
          dbms_output.put_line('--人员编号--'||emp_row.var_empno||'--人员姓名--'||emp_row.var_empname||'--机构id--'||emp_row.var_branchId);
      fetch cur_emp into emp_row; --让指针指向结果集中的下一行，并将值保存到emp_row中
    end loop;
    close cur_emp; --关闭游标
  end;
  
 /*************1、隐式游标**************/
 --隐式游标主要处理数据操纵语句（如update、delete语句）的执行结果，也可以处理select语句的查询结果。
 --由于隐式游标也有属性，当使用隐式游标时，需要在属性前面加----隐式游标的默认名称：sql
begin 
 update emp 
   set sal=sal*(1+0.2)
   where job='SALESMEN';
 if sql%notfound then  --判断update语句有没有影响到任何一条数据
    dbms_output.put_line('没有雇员需要上调工资');
 else
    dbms_output.put_line('有'||sql%rowcount||'雇员上调了20%');
 end if;
end;

--标识符"sql"就是updtae语句在更新数据过程中所使用的  “隐式游标”
--当需要使用隐式游标的属性时，标识符 “sql”就必须显示地添加到属性名称之前。

--通过for语句循环 “隐式游标”
begin
  for emp_record in (select *
                       from tms_employee_info t
                      where t.series_id = '02') loop
    dbms_output.put_line('人员编码：' || emp_record.empno || ';人员姓名：' ||
                         emp_record.emp_name || ';机构：' ||
                         emp_record.branch_id);
  end loop;
end;


--for语句循环 “显示游标”
declare
   cursor cur_emp is
     select * from tms_employee_info t where t.series_id='02';
  begin 
     for emp_record in cur_emp
     loop
         dbms_output.put_line('人员编码：' || emp_record.empno || ';人员姓名：' ||
                         emp_record.emp_name || ';机构：' ||
                         emp_record.branch_id);
     end loop;
  end;

 /*******************异常处理机制********************/
declare
  var_empno number;
  var_empname varchar2(50);
begin
  select t.empno, t.emp_name
    into var_empno, var_empname
    from tms_employee_info t
   where t.branch_id = '3';
   if sql%found then
      dbms_output.put_line('人员编码：' || var_empno || ';人员姓名：' ||var_empname);
   end if;
exception
    when too_many_rows then
         dbms_output.put_line('返回记录超过一行');
    when no_data_found then
         dbms_output.put_line('没有数据');
end;

--自定义异常：错误编号异常、业务逻辑异常

--1.错误编号异常
declare
  primary_iterant exception;
  pragma exception_init(primary_itera