Oracle笔记本（1102）

select 2*3 from dual; 

--求年薪
select ename,sal, sal*12 from emp; 
--别名
select ename,sal, sal*12 as "年薪" from emp; 
select ename,sal, sal*12  "年薪" from emp; 

--员工一年总收入
select * from emp;


select ename,sal, sal*12+comm as "年总收入" from emp;
--注意：含有任何null值的数学表达式最后的结果都为null 

--字符串连接符 ||
select ename||'员工姓名' from emp;


--处理一个字符串中含有单引号的情况 :使用2个单引号表示一个单引号
select ename||'员工''姓名' from emp;


--含有任何null值的字符串表达式中，null被当作空字符串处理
select empno, ename||mgr from emp;

--求员工每个级别顶薪

select hisal*12 as "员工年顶薪" from salgrade;

/*  distinct  消除重复值   */
--求emp表中的部门列表
select distinct deptno  from emp;

select  deptno, job  from emp;
select distinct deptno, job  from emp;--去除deptno, job 组合起来的重复值

--null值
--求奖金为null的员工信息
select * from emp where comm is null;

--求奖金不为null的员工信息
select * from emp where comm is not null;


/*    in    */
--求员工编号在某一个集合内的值
select * from emp where empno in (7369,7499,7521)

select * from emp where empno  not in (7369,7499,7521)


/*  oracle 日期 */

select * from emp;  

--求入职日期在1982-1-23后入职的人

select * from emp where hiredate > '23-1月-82';


/*  like   */

--查询员工 名字第二个字面为A的人

select * from emp where ename like '_A%';

--查询名字中含有 % 等通配符的数据时，使用转义字符 \   (\%)
select * from emp where ename like '%\%%';


--查询1987年入职的人
select * from emp where hiredate>='1-1月-87' and hiredate<='31-12月-87';

select * from emp where hiredate like '%87%' 

/* order by*/

select * from emp order by deptno asc,sal desc;

/*常用函数*/
--lower()

select empno,  Lower(ename)  from emp;

--查询名字带有 “A“ 或 "a"的员工
select *  from emp where ename like '%A%' or ename like '%a%';

select *  from emp where Lower(ename) like '%a%';


--Upper();  小写转大写

select Upper('wepull') from dual;

--Substr
select Substr('wepull',2,3) from dual; --epu
select Substr('wepull',2) from dual; --epull

select Substr(ename,2) from emp;

--Chr()  某个ASCII码值对应的字符
select Chr(97) from dual; -- a

 -- Ascii()函数
--例子：求一个字符的ASCII码值
select Ascii('a') from dual;  --97



--常用数值函数

select round(23.652) from dual --24

select round(23.652,2) from dual  --23.65

select round(23.652,-1) from dual  --20
select round(493.652,-2) from dual  --500

--to_char()

select to_char(456123.45,'L999,999.99') from dual--  ￥456,123.00

--to_char()对日期的转换

select sysdate from dual;

select to_char(sysdate,'YYYY/MM/DD') from dual;


--to_date()函数

--例子:将1985年2月14日8:00以后入职的雇员信息取出

select * from emp where hiredate > to_date('1985-02-14 8:00','YYYY-MM-DD HH:MI')



--to_number()函数

--求薪水<于1200的人
select * from emp where sal<to_number('$1,200','$9,999')

--nvl 处理空值

select ename, sal*12+comm  as "一年总收入" from emp 

select ename, sal*12+nvl(comm,0)  as "一年总收入"  from emp 




---聚合函数/组函数
select * from emp;

--emp表中deptno数量
select count(deptno) from emp;

select count(distinct deptno) as "部门总数" from emp;


--group by

--求每个部门的最大工资
select  deptno ,max(sal) from emp group by deptno;

--求每个部门中职位的最大工资
select  deptno ,job,max(sal) from emp group by deptno , job;


---求每个部门工资最高人信息

select ename,deptno,sal from emp where (deptno,sal)in
(select  deptno ,max(sal) from emp group by deptno);

--方法二
select * from emp join
       (select  deptno ,max(sal) max_sal from emp group by deptno  ) t 
          on (emp.deptno=t.deptno and emp.sal=t.max_sal)



--having
select deptno, avg(sal)  from emp group by deptno having avg(sal)>2500


---子查询
--求工资最高的人的信息
select * from emp where sal=(
       select max(sal) from emp
);

--rownum（分页）

--查询emp表前5条记录

select ename,rownum from emp where rownum<=5;

--查询emp表第5条以后记录

select * from (select empno, ename,rownum as r from emp)
       where r>5;

--rownum 与排序

select empno,ename,sal ,rownum from emp order by sal;


select empno,ename,sal ,rownum from
       (select empno,ename,sal  from emp order by sal)
       
上司
--自连接
--求emp表中员工的姓名和对应的上司（mgr）
select * from emp;

select e1.ename, e2.ename 
from emp e1,emp e2
where e1.mgr=e2.empno;


--交叉连接
select ename from emp;
select dname from dept;

select ename,dname  from emp ,dept; --结果为笛卡尔乘积


--SQL1999 语法
select ename,dname  from emp cross join dept; 


--连接查询
create table a(
	id int primary key,
	name nvarchar2(20)
);
select  * from a;
insert into a values (1,'凤姐');
insert into a values (3,'犀利哥');
insert into a values (5,'春哥');
insert into a values (6,'曾哥');
insert into a values (7,'平平');

create table b(
	id int primary key,
	info nvarchar2(20)
)

insert into b values (1,'深圳');
insert into b values (2,'武汉');
insert into b values (6,'成都');

select  * from a;
select  * from b;

select  * from a  left outer join b on a.id=b.id;--左外连接
select * from a right outer join b on  (a.id=b.id);--右外连接
select * from a full  join b on  (a.id=b.id);--全连接 （左外连接+右外连接）
select * from a inner join b on  (a.id=b.id) --内连接

--非等值连接
--查询出雇员名字和薪水等级（2个表连接查询）
select * from emp;
select * from salgrade;

select e.ename ,e.sal ,s.grade from emp e
join salgrade s
on (e.sal between s.losal  and s.hisal)
 
--查询出雇员名字，及对应的部门名称，薪水等级（3个表连接查询） 
select e.ename, d.dname, s.grade from emp e
       join dept d on(e.deptno=d.deptno)
       join salgrade s on (e.sal between s.losal  and s.hisal)

--练习
--求部门中薪水最高的人名。

select * from emp e;
select e.deptno, max(e.sal) from emp e group by deptno ;


select e.ename ,t.deptno,e.sal from emp e  
join (select e.deptno, max(e.sal) max_sal from emp e group by deptno) t
on (e.deptno=t.deptno and e.sal =max_sal)


--求部门平均薪水等级？

select deptno, avg(sal) from emp e group by deptno;
select * from salgrade;
 
select t.deptno, t.avg_sal, s.grade from salgrade s    
join (select deptno, avg(sal) avg_sal from emp e group by deptno) t
on(t.avg_sal between s.losal and s.hisal );     
       
--求哪些人是经理    
select * from emp;

select * from emp where mgr in (select mgr from emp);
   
select * from emp where mgr in (select distinct mgr from emp);      
      
--平均薪水最高的部门编号与薪水  
  
--第一步：先求出部门平均薪水

select e.deptno ,avg(sal) from emp e group by e.deptno;

--第二步：求出平均薪水最高值
   
 select max(t.avg_sal) 
 from (select e.deptno ,avg(sal) avg_sal from emp e group by e.deptno) t      
  
 --综合     
 select deptno ,avg_sal from
 (select e.deptno ,avg(sal) avg_sal from emp e group by e.deptno) t
 where  t.avg_sal =
        ( select max(t.avg_sal) 
           from (select e.deptno ,avg(sal) avg_sal from emp e group by e.deptno) t      
         )          
--方法二：       
   select deptno ,avg_sal from
   (select e.deptno ,avg(sal) avg_sal from emp e group by e.deptno) t 
   where  avg_sal =(select max(avg(sal)) from emp group by deptno )   
   --注意 :组函数嵌套只能嵌套2层 
       
   select * from emp;
--比普通员工的最高薪水还要高的经理人名称
-- 1.先求普通员工的最高薪水？  
   select max(sal) from emp e 
   where e.empno  not in (select mgr from emp where mgr is not null )
--2.求比上面的值要大，并且还是经理人

select e.ename,e.sal from emp e 
   where e.empno  in (select distinct mgr from emp where mgr is not null )
   and e.sal>
   ( select max(sal) from emp e 
     where e.empno  not in (select distinct mgr from emp where mgr is not null )
   );

  
--复制表

create table emp as select * from scott.emp where 1<>1;--只复制结构
create table emp as select * from scott.emp ;--复制数据
create table dept as select * from scott.dept ;
create table salgrade as select * from scott.salgrade ;
create table bonus as select * from scott.bonus ;
 select * from emp


---Orcle 高级部分

--视图

select * from scott.emp;

create view  emp_view 
as
select empno, ename,sal from scott.emp;


select * from emp_view;

drop view emp_view;

--查询每个部门的名称、总人数、平均工资、最低工资的员工名字。
select dept.dname from demp;

select deptno, count(empno) ,avg(sal),min(sal) from emp group by deptno ;  
 

--综合： 3张表连接查询
select d.dname, t.deptno, t.c, t.avg_sal,t.min_sal ,e.ename
from dept d
  join 
       (select deptno, count(empno) c ,avg(sal) avg_sal,min(sal) min_sal from emp group by deptno ) t
  on (d.deptno=t.deptno)
  join emp e
  on (e.sal=t.min_sal and e.deptno=t.deptno)
  
--把上面结果封装成视图
create or replace view deptInfo_view
as
select d.dname, t.deptno, t.c, t.avg_sal,t.min_sal ,e.ename
from dept d
  join  (select deptno, count(empno) c ,avg(sal) avg_sal,min(sal) min_sal from emp group by deptno ) t
  on (d.deptno=t.deptno)
  join emp e
  on (e.sal=t.min_sal and e.deptno=t.deptno)

--
select * from deptinfo_view;

---------

--外键约束
drop table t_type;
create table t_type(
       id int primary key,
       typeName varchar2(20)
  
)
drop table t_book;
create table t_book(
       id int primary key,
       name varchar2(20) ,
       tid int ,
      constraint FK_tid  foreign key (tid) references t_type(id)--字段级的外键约束，不用加foreign key
  
)

--
select * from user_tables;
select table_name from user_tables;

insert into t_type values (1,'言情类');
insert into t_type values (2,'计算机类');
insert into t_type values (3,'言武侠类');

select * from t_book t
insert into t_book values (1,'小磊子的情书',1);
insert into t_book values (2,'凤姐的韵事',1);
insert into t_book values (3,'干露露的视频',2);
insert into t_book values (4,'java自学成才',2);
insert into t_book values (5,'刘德华的情歌',3);
--------

--视图中使用DML的规定（不重要）

create or replace view book_view
as
select id,name from t_book;

--
select * from t_book
select * from book_view;

delete from book_view where id=4;

--只读视图

create or replace view book_view
as
select id,name from t_book
with read only;

--序列
create or replace sequence my_sec;

select  my_sec.nextval  from dual;
select  my_sec.currval  from dual;

create  sequence book_sec;

create table book(
       id number,
       name varchar2(50)
)
alter table book add constraint PK_id primary key (id)

select * from book;

insert into book values (book_sec.nextval,'111');
insert into book values (book_sec.nextval,'222');
insert into book values (book_sec.nextval,'333');
insert into book values (book_sec.nextval,'444');


create sequence se_1
increment by 50 --次增长的幅度 50
start with 100  --从100开始
maxvalue 400  --最大400
minvalue 50
cycle  --到最大值后循环
cache 2;


select se_1.nextval from dual

--索引
create index idx_book on book(name);
drop index idx_book;

--同义词
t_book

create synonym t for t_book
select * from t;

drop synonym t;

--- PL/SQL
begin
     dbms_output.put_line('hello word');
end;


--变量赋值
declare
     v_name varchar2(20):='wepull';
begin
     dbms_output.put_line(v_name);
end;     


--复合类型变量

--记录类型
declare
     --定义一个记录类型
     type book_rec is record(
          id int,
          name varchar2(20)
     );

    b_rec book_rec;       --声明一个变量是记录类型
begin
    select id ,name into b_rec from wepull.t_book where id=1;
    dbms_output.put_line(b_rec.id||b_rec.name);
end;

--%type

declare 
    v_id wepull.t_book.id%type ;--定义的变量v_id类型与t_book中的id列的类型一致
    v_name wepull.t_book.name%type;
begin
   /*
     v_id:=1;
     v_name:='wepull';
    
    */
    
    select id ,name into v_id,v_name from wepull.t_book where id=1;
    dbms_output.put_line(v_id);
    dbms_output.put_line(v_name);
end;

--rowtype

declare 
       v_book wepull.t_book%rowtype ;--定义的变量v_book类型与t_book中列的类型一致
begin

    select * into v_book from wepull.t_book where id=1;
    dbms_output.put_line('id: '||v_book.id);
    dbms_output.put_line('name: '||v_book.name);
    dbms_output.put_line('tid: '||v_book.tid);
end;


--嵌套表
select * from t_book;

select * from wepull.t_book;


declare
       type book_table_type is table of wepull.t_book%rowtype;--定义类型
       v_book book_table_type:=book_table_type();
begin
       v_book.extend(5);
       select * into v_book(1)  from wepull.t_book where id=1;
       dbms_output.put_line(v_book(1).id||' '||v_book(1).name);
       
       select * into v_book(2)  from wepull.t_book where id=2;
       dbms_output.put_line(v_book(2).id||' '||v_book(2).name);
end;        
/
--流程控制语句

--if 

--单个if
--1.查询用户输入的id的图书，如果图书的tid=1 则输出图书类型为言情类
declare
   v_id wepull.t_book.id%type:=&v_id;
   v_tid wepull.t_book.tid%type;
begin
   select id,tid into v_id, v_tid from wepull.t_book where id=v_id;
   if (v_tid=1) then
      dbms_output.put_line('此id的图书为言情类');
   end if;
end;
/

--if else
--1.查询用户输入的id的图书，如果图书的tid=1 则输出图书类型为言情类，否则输出不是言情类
declare
   v_id wepull.t_book.id%type:=&v_id;
   v_tid wepull.t_book.tid%type;
begin
   select id,tid into v_id, v_tid from wepull.t_book where id=v_id;
   if (v_tid=1) then
      dbms_output.put_line(v_id|| ' :此id的图书为言情类');
   else
       dbms_output.put_line(v_id|| ' :此id的图书不是言情类');  
   end if;
end;
/

--
--if elsif

--1.查询用户输入的id的图书，
--如果图书的tid=1 则输出图书类型为言情类，
--如果图书的tid=2 则输出图书类型为计算机类
--如果图书的tid=3 则输出图书类型为言武侠类

select * from wepull.t_type;

declare
   v_id wepull.t_book.id%type:=&v_id;
   v_tid wepull.t_book.tid%type;
begin
   select id,tid into v_id, v_tid from wepull.t_book where id=v_id;
   
   if (v_tid=1) then
      dbms_output.put_line(v_id|| ' :此id的图书为言情类');
   elsif(v_tid=2)then
       dbms_output.put_line(v_id|| ' :此id的图书为计算机类');  
   elsif(v_tid=3)then
       dbms_output.put_line(v_id|| ' :此id的图书为武侠类');  
   end if;
end;
/

--case
declare
   v_id wepull.t_book.id%type:=&v_id;
   v_tid wepull.t_book.tid%type;
   v_typeName wepull.t_type.typeName%type;
begin
   select tid into v_tid from wepull.t_book where id = v_id;
   
   v_typeName :=
              case v_tid
                   when 1 then '言情类'
                   when 2 then '计算机类'
                   when 3 then '武侠类'
                   else '其他类'
              end;
   dbms_output.put_line('id: '||v_id);
   dbms_output.put_line('tid: '||v_tid);
   dbms_output.put_line('typeName: '||v_typeName);
end;
/

-- 
--case 搜索方式
declare
   v_id wepull.t_book.id%type:=&v_id;
   v_tid wepull.t_book.tid%type;
   v_typeName wepull.t_type.typeName%type;
begin
   select tid into v_tid from wepull.t_book where id = v_id;
   
   v_typeName :=
              case 
                   when v_tid=1 then '言情类'
                   when v_tid=2 then '计算机类'
                   when v_tid=3 then '武侠类'
                   else '其他类'
              end;
   dbms_output.put_line('id: '||v_id);
   dbms_output.put_line('tid: '||v_tid);
   dbms_output.put_line('typeName: '||v_typeName);
end;
/


--循环
--循环1---Loop
--求 1+2+....+100=？

declare
    v_i number:=0;
    v_sum number:=0;
begin
    loop
          v_i:=v_i+1;
          v_sum:=v_sum+v_i;
    exit when(v_i=100);
    end loop;   
    dbms_output.put_line('1+2+....+100='||v_sum);   
end;
/

--循环1---while
--求 1+2+....+100=？

declare
    v_i number:=0;
    v_sum number:=0;
begin
    while(v_i<100) loop
          v_i:=v_i+1;
          v_sum:=v_sum+v_i;
    end loop;   
    dbms_output.put_line('1+2+....+100='||v_sum);   
end;
/

--循环1---for
--求 1+2+....+100=？

declare
    v_i number:=0;
    v_sum number:=0;
begin
    for v_i in 1..100 
        loop
             v_sum:=v_sum+v_i;
    end loop;
  
    dbms_output.put_line('1+2+....+100='||v_sum);   
end;
/
--
declare
    v_i number:=0;
    v_sum number:=0;
begin
    for int in 1..100 
        loop
             v_i:=v_i+1;
             v_sum:=v_sum+v_i;
    end loop;
  
    dbms_output.put_line('1+2+....+100='||v_sum);   
end;
/

--goto

declare
    v_i number:=0;
    v_sum number:=0;
begin
    for v_i in 1..10 
        loop
           dbms_output.put_line('当前i为： '||v_i);  
           if(v_i=5) then
                     goto endloop;
            end if;         
    end loop;
    
    <<endloop>>
        dbms_output.put_line('此时v_i=5,退出了循环体');   
end;


--null语句
--goto

declare
    v_i number:=0;
    v_sum number:=0;
begin
    for v_i in 1..10 
        loop
           dbms_output.put_line('当前i为： '||v_i);  
           if(v_i=5) then
                     goto endloop;
            end if;         
    end loop;
    
    <<endloop>>
        null;   
end;


--游标
--使用游标取出emp表总前10人的信息。
select * from scott.emp;

declare
       --第一步：声明游标
       cursor emp_cursor is select * from scott.emp where rownum<=10; 
       emp_record scott.emp%rowtype;
begin
       --第二步：打开游标
       open emp_cursor;
       --第三步：从游标中取数据
       loop
            fetch emp_cursor into emp_record;--每取出一条数据，指针自动下移
            
       exit when(emp_cursor%notfound);--最后次取数据失败
            dbms_output.put_line(emp_record.empno|| '  ' ||emp_record.ename);
       end loop;
        --第四步：关闭游标
       close emp_cursor;
end;
/

--错误例子
declare
       --第一步：声明游标
       cursor emp_cursor is select * from scott.emp where rownum<=10; 
       emp_record scott.emp%rowtype;
begin
       --第二步：打开游标
       open emp_cursor;
       --第三步：从游标中取数据
       loop
            fetch emp_cursor into emp_record;--每取出一条数据，指针自动下移
            
             dbms_output.put_line(emp_record.empno|| '  ' ||emp_record.ename);
       
       exit when(emp_cursor%notfound);--最后次取数据失败
           
       end loop;
        --第四步：关闭游标
       close emp_cursor;
end;
/


--使用游标取出emp表总前10人的信息。  while


declare
       --第一步：声明游标
       cursor emp_cursor is select * from scott.emp where rownum<=10; 
       emp_record scott.emp%rowtype;
begin
       --第二步：打开游标
       open emp_cursor;
       --第三步：从游标中取数据
       
       fetch emp_cursor into emp_record;
       
       while(emp_cursor%found)loop
              dbms_output.put_line(emp_record.empno|| '  ' ||emp_record.ename);
              fetch emp_cursor into emp_record; 
       end loop;
        --第四步：关闭游标
       close emp_cursor;
end;
/


--使用游标取出emp表总前10人的信息。 for  必须掌握


declare
       --第一步：声明游标
       cursor emp_cursor is select * from scott.emp where rownum<=10; 
       emp_record scott.emp%rowtype;
begin
       for emp_rec in emp_cursor loop
         dbms_output.put_line(emp_rec.empno|| '  ' ||emp_rec.ename);  
       end loop;
end;
/

--使用游标取出emp表总指定job 和 deptno的员工信息。

select * from scott.emp 


declare
       --第一步：声明游标
       cursor emp_cursor(v_job scott.emp.job%type,v_deptno scott.emp.deptno%type) 
       is select * from scott.emp where job=v_job and deptno=v_deptno; 
       emp_record scott.emp%rowtype;
begin
       for emp_rec in emp_cursor('CLERK',30) loop
         dbms_output.put_line(emp_rec.empno|| '  ' ||emp_rec.ename);  
       end loop;
end;
/


--模糊查询
　select PATINDEX('%abb%','abcaabbeeabb') from dual;

select * from scott.emp where regexp_like(ename, '[AS]MITH');--SMITH






select * from scott.emp where ename like 'SMITH';


--游标的update操作
--更新指定图书的名字
declare
       cursor book_cursor is select * from  t_book for update; 
       --v_id t_book.id%type:=v_id;
       v_name t_book.name%type:=&v_name;
begin
       for book_rec in book_cursor loop
           if(book_rec.tid=1)  then 
                    update t_book set name=v_name where current of book_cursor;
           end if;
       end loop;
             
end;
/   

--隐式游标（了解）
--更新指定图书的名字，如果该图书没有找到，则打印”查无此图书”。
declare
       v_name t_book.name%type:=&v_name;
       v_id t_book.id%type:=&v_id;
       
begin
       update t_book set  name=v_name where id=v_id;
       if(sql%found) then
                     dbms_output.put_line('修改成功： '||v_id||' '||v_name);
       elsif(sql%notfound) then
                           dbms_output.put_line('没有指定id的图书 ');
       end if;                   
end;
/   




-------------存储过程
create or replace procedure p1 
as
       v_name t_book.name%type:='言情';
       v_id t_book.id%type:=1;
begin
       update t_book set name=v_name where id =v_id;
       if(sql%found) then
                 dbms_output.put_line('修改成功');
       end if;
end;
/

--调用存储过程
exec p1;

select * from t_book;

begin
     p1;
end;
/


--带参数的存储过程
--求1...N的和 （N为入参）
create or replace procedure p2(v_number in number)
as
       v_sum number:=0;
       v_i number:=0;
begin
       for v_i in 0..v_number loop
           
           v_sum:=v_sum+v_i;
       end loop; 
       dbms_output.put_line(v_sum);  
end;
       
--调用
begin
   p2(100);
end;
/

--带参数的存储过程
--求1...N的和 （v_number为入参,v_sum为出参）
create or replace procedure p3(v_number in number,v_sum out number)
as     
      
begin
       v_sum :=0;
       for v_i in 0..v_number loop
           
           v_sum:=v_sum+v_i;
       end loop; 
       dbms_output.put_line('存储过程中打印：'||v_sum);  
end;
       
--调用
declare
       v_sum number;
begin
   p3(100,v_sum);
   dbms_output.put_line('调用打印：'||v_sum);  
end;
/

--函数 无参
create or replace function my_fun
       return date
as
       v_date date;
begin
       select sysdate into v_date from dual;
       return v_date;
end;
/

--调用
select my_fun() from dual;
---
declare
        v_date date;
begin
        v_date:=my_fun();
        dbms_output.put_line(v_date);
end;
/


--函数 带参数
--根据id查询出图书名字
create or replace function getName(v_id t_book.id%type)
       return t_book.name%type
as
       v_name t_book.name%type;

begin
       select name into v_name from t_book where id = v_id;
       return v_name;
end;
/

--调用

declare 
       v_name t_book.name%type;
begin
       v_name:=getName(2);
       dbms_output.put_line(v_name);
end;
/

--触发器
create table book_log(
       username varchar2(10),
       action varchar2(10),
       adate  date
) 

select * from a;


create or replace trigger trigger_book
       after insert or update or delete on scott.a
begin
       if inserting then
          insert into book_log values (user,'insert',sysdate); 
       elsif updating then
          insert into book_log values (user,'update',sysdate); 
       elsif deleting then
          insert into book_log values (user,'delete',sysdate); 
       end if;
end;
/       
--测试
select * from a;
select * from book_log;

 insert into a values (8,'龙磊的铃声'); 
 update a set name='龙磊' where id = 8;
 
delete from a set name='龙磊' where id = 8;

--行触发器

create or replace trigger trigger_book
       after insert or update or delete on scott.a for each row
begin
       if inserting then
          insert into book_log values (user,'insert',sysdate); 
       elsif updating then
          insert into book_log values (user,'update',sysdate); 
       elsif deleting then
          insert into book_log values (user,'delete',sysdate); 
       end if;
end;
/ 
--
 update a set name=name||'2';
 delete from book_log;
 select * from book_log;
 select * from a;
     
  select * from emp;     
--异常处理（了解）

declare
        v_empno scott.emp.empno%type:=&v_empno;
        v_emp_rec emp%rowtype;
begin
        select * into v_emp_rec from scott.emp  where scott.emp.empno=v_empno;
        dbms_output.put_line(v_emp_rec.empno||' '||v_emp_rec.ename);
        
        exception
          when no_data_found then 
          dbms_output.put_line('没有此编号的员工 ');                                       
end;
/


declare
        v_deptno scott.emp.deptno%type:=&v_deptno;
        v_emp_rec emp%rowtype;
begin
        select * into v_emp_rec from scott.emp  where scott.emp.deptno=v_deptno;
        dbms_output.put_line(v_emp_rec.empno||' '||v_emp_rec.ename);
        
        exception
          when no_data_found then 
                dbms_output.put_line('没有此编号的员工 ');
          when too_many_rows then 
                dbms_output.put_line('select 语句返回了多行数据 ');                                       
end;

 

with e as 用法： 

 

其实就是把一大堆重复用到的sql语句放在with as里面，取一个别名，后面的查询就可以用它，这样对于大批量的sql语句起到一个优化的作用，而且清楚明了。


with e as (select * from smc_sql )
select * from e 
where e.id=2;



with as优点
增加了sql的易读性，如果构造了多个子查询，结构会更清晰；
更重要的是：“一次分析，多次使用”，这也是为什么会提供性能的地方，达到了“少读”的目标