SQL语句总结（视图，序列，存储过程，函数，游标）

SQL语句总结：
     创建用户：   create user scott identified（确认） by root;
     删除用户；    drop user  scott cascade(串联);
     创建角色：    create role myrole;
     授予权限：  grant  connect, resource to scott;    grant connect,resource to myrole; grant myrole to scott;

     移除权限：  revoke  connect  from scott;   revoke  myrole from scott;

     修改用户密码：   alter user scott identified by root;

     切换用户        conn  ntt/root;
    
    
     创建表 create table emp
          (     empno number(4),
                ename varchar2(20),
                job varchar2(20),      
                sal number(7,2),
                mgr number(4),
                hiredate date,
                deptno number(2)
                  );

       创建主键：一.     
       create table emp
     (empno number(4) primary key,
       ename varchar2(20),
       job varchar2(20),      
       sal number(7,2),
       mgr number(4),
       hiredate date,
       deptno number(2)
     );


     二. 
     create table emp
     (empno number(4) constraint pk primary key,
       ename varchar2(20),
       job varchar2(20),      
       sal number(7,2),
       mgr number(4),
       hiredate date,
       deptno number(2)
     );
     三.
       create table emp
     (empno number(4),
       ename varchar2(20),
       job varchar2(20),      
       sal number(7,2),
       mgr number(4),
       hiredate date,
       deptno number(2),
       constraint pk primary key(empno)
     );
     四.
       create table emp5
     (empno number(4),
       ename varchar2(20),
       job varchar2(20),      
       sal number(7,2),
       mgr number(4),
       hiredate date,
       deptno number(2)
     );
          alter table emp5 add primary key (empno);


      创建外键：
        一.
           create table emp
     (empno number(4) constraint pk primary key,
       ename varchar2(20),
       job varchar2(20),      
       sal number(7,2),
       mgr number(4),
       hiredate date,
       deptno number(2)    references dept(deptno)
     );
     二.
      create table emp
     (empno number(4) constraint pk primary key,
       ename varchar2(20),
       job varchar2(20),      
       sal number(7,2),
       mgr number(4),
       hiredate date,
       deptno number(2),
       foreign key (deptno)
       references dept(deptno)

     );
     三.
     create table emp
     (empno number(4) constraint pk primary key,
       ename varchar2(20),
       job varchar2(20),      
       sal number(7,2),
       mgr number(4),
       hiredate date,
       deptno number(2)
     );
     alter table emp add foreign key(deptno)
     references dept(deptno);
        删除表结构：
     drop table emp;
     修改表中的字段类型：
         alter table emp modify  empno number (10);
     修改表名：
     rename table emp to emp1;
     查看表结构：
          desc emp;
     添加字段：
        alter table emp add comm number(6,2;
     删除字段：
        alter table emp  drop column comm;

        表数据操作：
           insert的使用

          insert into emp values(
       添加全部
       1000,'ntt','clerk',4000,null,'1-1月-2015',null   
      
       );
         添加部分：
       insert into emp(empno,ename)valeus(
               1000,'ntt'
       );
          拷贝一张表 （拷贝表结构和数据）

       create table empcopy
       as select * from emp;

       oracle 对ＤＭＬ 操作不是自动提交
          需要提交操作
          提交：commit;
          取消： rollback

          delete的使用：
       delete from 表名；
       delete from 表名 where 主键字段=值；
       delete from  emp where empno=1002;

       UPdate的使用：
       update 表名 set 字段名=值；
       update 表名 set 字段名=值 where 条件；
       update emp set ename='藏东'where empno =1001;
          update emp set sal=1000,hiredate ='1-1月-2015' where empno=1002;

         查询 SElect的使用：
     基本查询：
        select 字段1，字段2..... from 表名；
         条件查询：
         select from 表名 where ...
         使用的运算符：
         = !=(<>) < <= > >=
          需求：查询10号部门中的员工
             select * from emp where deptno=10;
          需求：查询不在10号部门中的员工
         select * from emp where deptno !=10;
         select *from emp where deptno <> 10;
         ** 使用 is null或 is not null
         
            需求：  查询奖金为null的员工
            select * from emp where comm is null;
             需求：  查询奖金不为null的员工
         select * from emp where comm is not null;
       ** 布尔连接：  and(逻辑与)    or（逻辑或）
       
需求： 显示职位是clerk 没有奖
金的员工
    select * from  emp where job='clerk' and comm is not null;

需求： 显示职位是clerk 或没有奖
金的员工
   select *from emp where job='cleck' or comm is not null;
         模糊查询：like
          通配符：
                    %：任意位数任意字符
              _: 一位任意字符
     需求：显示名字中有’A‘的员工    
    
     select * from emp where ename like '%A%';

          需求：显示名字中首字母’A‘的员工
        select * from emp where ename like 'A%';
       需求：显示名字中第二个字母为’A‘的员工
       select * from emp where ename like '_A%';
    
              区间：   between...and ..
                       not     between ...and..
     
     需求： 显示工资在800到1500 的员工
     select * from enp where sal between 800 and 1500;

     需求： 显示工资不在800到1500 的员工
    select * from emp where sal not between 800  and 1500;
   
             **  范围：in    not in
       需求：显示普通员工和销售人员的信息

       select * from emp where job in('CLERK','SALESMAN');
      
     **排序查询：   order by desc  (降序)  asc(升序)  默认为升序
    
     查询员工名 工资 入职日期 根据工资降序来排
     select ename ,sal,hiredate from emp order by sal desc;
     查询员工名 工资 入职日期 根据工资降序来排 入职日期升序来排
     select ename,sal,hiredate from emp order by sal desc , hiredate asc;
     升序：
                 * number类型  比较值
                 *date 类型 时间晚 值越大
                 *字符类型 根据字母 排序
          查询 20 30号部门的员工 按照员工的奖金降序排序
       select * from from emp where deptno in(20,30) order by comm desc;
       nvl() 函数： 可把null变为0 用于比较排序
       ** null在，默认在排序中 最大
       select * from from emp where deptno in(20,30) order by nvl(comm,0) desc;

       查询中计算：
            查询员工姓名  工资 工资涨500后大于2000的结果：
         select ename , sal, (sal+500) newsal from emp where newsal>2000;

            查询员工名 工资 奖金 及员工的年薪
         select ename, sal, comm, (sal+nvl(comm,0))*12  yearsal from emp ;
         **null值和其他任何数据运算都为null;

             单行函数：
          字符函数：
          lower（） 大写转小写
          upper ()  小写转大写
          initcap() 首字母大写
          查询 职位是clerk的员工名， 员工职位，工资
          select  ename , job,sal from emp where lower(job)='clerk';
          select ename,job,sal from emp where upper(job) ='CLERK';
          select ename ,job,sal from emp where initcap(job)='Clerk';
           字符连接： ||
              查询员工名， 工资 工资涨3倍后的结果
              按照如下格式：
                xiaoqiang sal is 300, hope 900;
            
              select ename ||'sal is'  ||sal||', hope'||sal*3 hopesal from emp;
        length()  长度      length('dsadsads')

---

        concat()  字符连接   concat('hhhh',';')
        substr()   截子串    substr('sahghd',2,3) 2位位置 3为个数
        instr()     是否在串中   instr('sadd','d')
        trim()  去空格            trim('   dashg  ')
        replace()   替换       replace('dsadsad','d','f')

        数学函数：
        mod()  求模

        round()  四舍五入

        trunc() 截取

        日期函数：
          stsdate :系统时间

         months_between(): 
         select round(months_between(sysdate,'2-1月-2016')) from dual;

---

         add_months()
         select add(sysdate,3) from dual;

         last_day()
         select last_day(sysdate) from dual;

         next_day() 下一个星期几是几号
         select next_day(sysdate,'星期日') from dual;

---

           计算员工工作的月数, 天数 周数
        select round(MOnths_between (sysdate
     ,hiredate)) months,
       round((sysdate-hiredate)/7) weeks,
       Round(sysdate-hiredate) days from dual;
        oracle中使用双引号 ---起别名

     转换函数：
     数据类型的转换（隐式）自动转换
     select 5 || '5' from dual;   55
     select 5+'5' from dual;   10

日期  to_date()<-------》to_char() 字符to_char  <—----—》to_number()  数字

  查询员工7369的员工名 入职日期  格式为（2015-1-1）
  select ename, to_char(hiredate,'yyyy-mm-dd') from emp where empno=7369;
  select ename,to_char(hiredate,'yyyy-mm--dd hh:mi:ss') from emp where empno =7369;
       数字转字符: 常用于数字转为货币表示
       9： 表示任意一位
       L:表示本地货币的符号
       $：表示美元符号

       select to_char(sal,'L999,999,999.99') from emp;

       分组查询：
       group by  组（聚合） 函数
       count() max() min() avg() sum()
       having: 筛选条件应用分组之后的运算结果
        查询每个部门员工的工资总和
     select sum(sal) from emp group by deptno;
     查询每个部门员工的最高工资
     select max(sal) from emp group by deptno;
     查询每个部门员工的最低工资
     select min(sal) from emp group by deptno;
     查询每个部门员工数

     select count(*) from  emp group by deptno;
   注意：分组查询中 ，只能查询分组字段和组函数的运算的结果
 
         需求： 查询10 20 号部门的平均工资

   select deptno,round(avg(sal))  from emp where deptno in(10,20) group by deptno;

    需求： 查询10 20 30号部门的平均工资， 降序排序
    select deptno ,round(avg(sal)) from emp where deptno in(10,20,30) group by deptno order by round(avg(sal)) desc;
      需求： 查询10 20 30号部门的平均工资， 降序排序 只保留平均工资大于2000的部门号和平均工资
    select deptno ,round(avg(sal)) from emp where deptno in (10,20,30) group by deptno having round(avg(sal)) >2000 order by ronud(avg(sal)) desc;
   查询emp表中有那些职位
   select job from emp group by job;
   或  select  distinct(别具一格) job from emp ;
    查询 emp表中 每种职位有都少员工
    select job, count(*) from emp group by job;
     查询 emp表中 每个部门的每种职位的工资总和
    select deptno,job ,sum(sal) from emp group by deptno ,job;
  数据的对应关系：
     一对多
     联合查询（多表查询）：
     需求： 查询所有部门的员工名  工资 部门名 部门地址
     select ename,sal,dname,loc from emp , dept;(笛卡尔积现象) ： 连接条件不明确
    
    (等值查询)select ename,sal,dname,loc from emp e,dept d where e.deptno=d.deptno;

    内连接查询 ：查询所有的记录都满足连接条件
    （内连接）: select ename,sal,dname,loc from emp e inner join dept d on e.deptno=d.deptno;

    外连接查询： 可以查询出不满足条件的数据
    左外连接： 查询出左表中满足和不满足连接条件的数据
   （左外连接）select ename,sal,dname,loc from emp e left join dept d on e.deptno=d.deptno;
    在oracle中左外连接还可以这样：
      select ename,sal,dname,loc from emp e,dept d where e.deptno=d.deptno(+);
     右外连接：查询出右表中满足和不满足连接条件的数据
     (右外连接) select ename,sal,dname, loc from  dept d right join  emp e on d.deptno=e.deptno;

    在oracle中右外连接还可以这样：
      select ename,sal,dname,loc from dept d,emp e where d.deptno(+)=e.deptno;

      全外连接：
      select ename,sal,dname,loc from emp e full join dept d on e.deptno=d.deptno;

    多对多对应关系：需要中间表
    查询所有用户名和用户的角色名
     select uname,rname from t_user,t_role,t_user_role where
         t_user.tid=t_user_role.tid and t_role.rid=t_user_role.rid;


    查询用户王航的角色
 
  select uname,rname from t_user,t_role,t_user_role where
       t_user.tid=1001  t_user.tid=t_user_role.tid and t_role.rid=t_user_role.rid;

    免去用户陆川的管理员角色
    delete from t_user_role where tid=1002 and rid=10;

    子查询：
     应用场景：
      1.在where子句中使用
      2.在from子句中使用
      select * from (select * from emp);
      3.在DML语句中可以使用
      例：
      修改员工smith的同部门SAl +200

    update emp set sal=sal+200 where deptno=(  select deptno from emp where lower(ename)='smith') and ename<>'SMITH';

      删除员工smith的部门员工信息

      delete from emp where deptno=(select deptno from emp where ename='SMITH');
    
     普通子查询：

     子查询可以独立执行  先执行子查询 再执行主查询
     查询员工smith的同部门同事信息
     select * from emp where deptno=(select deptno from emp where lower(ename)='smith') and ename<> 'SMITH';

     相关子查询：
     子查询的查询结果与主查询相关


     查询 工资比本部门平均工资高的员工名 工资，部门
       select ename,sal, deptno from emp e where sal>(select round(avg(sal)) from emp where deptno=e.deptno );
     查询工资低于同职位同事平均工资的员工信息

      select * from emp  e where sal<(select round(avg(sal)) from emp where job=e.job);

     **in   **not in   表示范围

select * from emp where deptno in(select deptno from emp where job='ANALYST' or job='PRESIDENT');
     **exists  ** not exists

需求： 查询有部下的员工
   select empno,ename,job,mgr from emp e where exists(select * from emp where mgr=e.empno);



需求： 查询没有部下的员工
        select empno,ename,job,mgr from emp e where not exists(select * from emp where mgr=e.empno);     
       ************分页查询*************
       rownum : 伪列 行号

       一.   page=2
            pageSize=3

            start: (page-1)*pageSize+1
            end: page*pageSize

     二.
         select * from (
          select rownum rn ,d.*
          from  (select * from emp) d

          where rownum<=end

          ) where rn>=start;

   

         三.  分页查询中需要计算最大页数

     totalRecord   ： 总记录数

     pageSize： 每页记录数（约定好）

     maxpage=totalRecord%pageSize=0?totalRecord/pageSize:(totalRecord/pageSize)+1;

    ***********************************************************************************
            查询比smith薪水都高的员工信息
          （smith可能有多个）
          注意：子查询中，使用关系运算符 子查询不可以
返回多个行
        select * from emp where sal> all(select sal from emp where ename ='SMITH');

          查询部门SALES包含哪些职位
         select  distinct job from  emp where deptno =(
     select deptno from dept where  dname='SALES'
     );


          查询比SALES部门所有员工薪水高的员工信息
           select *from emp where sal>  all(   select sal from emp where deptno=(select deptno from dept where dname='SALES'));

       查询比reseARch 部门任意员工薪水高的员工信息
         select * from emp where sal> any  (select sal from emp where deptno =(
        select deptno from dept where dname='RESEARCH'));

      
          查询BLAKE的下属信息
           select * from emp where mgr in(
           select empno from emp where ename='BLAKE'
        );

       查询每个部门拿最高薪水的员工信息
     select * from emp where (deptno,sal) in(  select deptno ,max(sal) from emp  group by deptno);


       查询比10号部门员工多的部门
        select count(*) from emp  group by deptno having count(*)>( select count(*) from emp where deptno=10);


       查询没有员工的部门
      
       select * from dept d where not exists (
         select 1 from emp where deptno=d.deptno
       );

          查询有员工的部门
        select * from dept d where  exists (
         select 1 from emp where deptno=d.deptno
       );

        集合的操作：

     union  （去重）
     union all（不去重）

        交集
     intersect 

     差集
     minus

     oracle 中其他的对象
     sequence  （序列）
     view （视图）
     index（索引）

     sequence :
        create sequence seq01_1507
        start with 1000   --初始值
        increment by 10  --自增
        maxvalue 10000  ---最大值
        cache 20  ;  --缓存中值的个数

      使用sequence：
         nextval  currval

     使用sequence值数据添加
     * 创建一个sequence
     * emp 中添加数据 员工编号使用序列的值
       create sequence seq_emp
       start with 8000
       increment by 10;
       insert into emp(empno,ename) values (
          seq_emp.nextval,'桑达'
       );

       view视图
       命名的查询，视图中并非存在数据
       可以把视图看做一个虚拟的表
       可以简化查询
       可以隐藏 表结构

       create or replace view emp_view
       as
       select * from emp;
       视图中关联的表称为视图的基表

       创建视图需要给用户授权
       grant create any view to scott;

       使用视图
       select * from emp_view;
       使用多基表 创建视图
       create or replace view emp_dept
       as
       select ename,sal,job,dname,loc from emp e left join dept d on e.deptno=d.deptno;

       通过视图执行DML的操作

       insert into emp_view(empno,ename)
          values(1001,'三灯') ;
      
     update emp_view set ename='僧淡'
     where empno='1001';

     delect from emp_view where empno=1001;

     通过视图实现对数据的增删改  只能是单基表视图
     多基表视图  不建议执行数据的更新操作
     
      独立实现    创建视图  查询emp表和dept表
        查询员工标号  姓名 薪水 职位 入职日期  部门号 部门名  部门地址  并按入职地址排序；
   
        create or replace view emp_dept
     as
     select * from(
     select rownum rn, ed.*   from
        (select empno,ename,sal,job,hiredate,e.deptno dname
                from emp e left join dept d on e.deptno=d.deptno order by hiredate asc) ed
             where rownum<=10 )
             where rn>=5;

       oracle 中的表分为数据表* 数据字典
       常见数据字典：
       user_tables
       user_views
       user_constraints
       user_sequences
       user_indexes
       all_tables 用户可以访问的所有的表
       all_views
       all_constraints
       all_sequences

       查询emp表中的约束
       select constraint_name from user_constraints
       where table_name='EMP';
       查询视图名
       select view_name from user_views;

       查询视图中相关联的sql语句
       select text from uaer_views
       where view_name='EMP_VIEW';

       查询用户中所有的序列
       select * from user_sequences;

       索引 ：index
       提高oracle 表中数据查询速度
       oracle中主字段自动加索引
       unique（唯一）字段自动加索引

       添加索引
            create index emp_ename_index
         on emp (ename);

        select index_name from user_indexes where table_name='EMP';

        独立实现：  索引性能测试
                 拷贝emp表  emp_copy_1507
              create table emp_copy_1507 as select * from emp;
                    
              反复复制emp_copy_1507的数据到本表
              insert into emp_copy_1507 select * from emp_copy_1507;
            
              修改表emp——copy-1507 中empnode 类型 number(7);
             alter table emp_copy_1507 modify empno number(7);
             
              修改表    中empno字段的值为rownum
              update emp_copy_1507 set empno=rownum;

              查询第500000条记录 记录操作时间
                    select * from emp_copy_1507 where empno=500000;

              创建索引  empno
              create index emp_copy_1507 on emp_copy_1507(empno);

              查询第500000条记录 记录操作时间
       
              
             PL/SQL  （Oracle）:
           procedure Language/
          Structured Query Language
                 编程语言+SQL
        
             PL/SQL 最基本的可以独立执行的程序单元为语句块

          PL/SQL 中分为不同种的程序
          分别为：  匿名语句块
                   **存储过程**
                函数
                触发器

              declare
           begin
           dbms_output.put_line('hello');
           end;

           变量的定义与赋值
           declare
               v_age number(2);
           begin
               v_age:=33;
            dbms_output.put_line(v_age);
           end;

           注:在赋值时使用 :=  
             
           分支语句：

           if语句
           declare
               v_num number(2);
            v_char varchar2(1);
           begin
           v_num :=2;
           if v_num=1 then
           v_char :='A';
           elsif  v_num=2 then
           v_char :='B';
           elsif v_num=3 then
           v_char :='c';
           else
           v_char :='o';
           end if;
           dbms_output.put_line(v_char);

           end;

       case结构语句

       declare
          v_num number(2);
          v_char varchar2(10);
          begin
          v_num :=2;
          case
          when v_num=1 then v_char:='aa';
          when v_num=2  then v_char :='aaa';
          when v_num=3 then v_char :='SSSS';
          when v_num=4 then v_char :='sdd';
          else
          v_char :='0';
          end case;
            dbms_output.put_line(v_char);
          end;
            循环语句：
         loop循环 if退出
         declare
         v_sum number(6);
         v_i   number(3);
         begin
         v_sum :=0;
         v_i := 1;
         loop
             if v_i=101 then
          exit;
          end if;
          v_sum := v_sum +v_i;
          v_i:=v_i+1;
          end loop;
          dbms_output.put_line(v_sum);
          end;
         
          LOOP 循环 when 退出

          declare
          v_num number(6);
          v_i number(3);
          begin
             v_sum:=0;
          v_i:=1;
              loop
          exit when v_i=101;
          v_sum :=v_sum+v_i;
          v_i:=v_i+1;
          end loop;
          dbms_output.put_line(v_sum);
          end;

          LOOP 循环 while 退出

          declare

          v_sum number(6);
          v_i number(3);
          begin
          v_sum := 0;
          v_i:=1;
         
          while v_i <=100
          LOOP
          v_sum := v_sum +v_i;
          v_i:=v_i+1;

          end loop;
          dbms_output.put_line(v_sum);
          end;

          fro循环：
          declare

          v_sum number(6);
          v_i number(3);
          begin
          v_sum := 0;
          for v_i in 1..100
          loop
          v_sum :=v_sum+v_i;
          end loop;
          dbms_output.put_line(v_sum);
          end;
             
    PL/SQL 与SQL的结合:

             insert delete update 直接写

             %type 引用表中字段的类型
            需求： 添加员工  员工号 2000  员工名 撒当

                        declare
                 v_empno  emp.empno%type;
                 v_ename  emp.ename%type;
                 begin
                    v_empno :=2000;
                 v_ename :='冻哈';
                 insert into emp (empno,ename) values(
                     v_empno,v_ename
                 );
                 end;
                
            根据员工编号 删除该员工记录 
              declare
     v_empno emp.empno%type;
     begin
     v_empno:=&no;
     delete from emp where empno=v_empno;
     end;

         根据员工号 打印员工姓名

     declare
            v_empno emp.empno%type;
         v_ename emp.ename%type;
         begin
         v_empno := &no;
         select ename into v_ename from emp
         where empno=v_empno;
         dbms_output.put_line(v_ename);
         end;


       根据员工号 打印员工信息
      
       declare
          v_empno emp.empno%type;
          v_empdata   emp%rowtype;
             begin
         select *  into v_empdata from emp
         where empno=&no;
         dbms_output.put_line(v_empdata.ename||v_empdata.job);
         end;
              PL/SQL:
           基本数据类型的变量只能存储一条记录
           命名程序：  可重用 安全

                  存储过程
                  触发器
                  函数

                  程序包

            create or replace procedure pro01

            is
            v_char varchar2(20);
            begin
            v_char :='chay';
            dbms_output.put_line(v_char);
            end;

           2.调用存储过程
                 java调用
                 命令调用  exec pro01;
                 过程调用
                   begin
                  pro01;
                  end;

                ***实用的存储过程
            查询员工号的员工名

            存储过程中的参数
            输入参数;   调用者传递的参数

            输出参数;  返回值

       create or replace procedure pro03(
         v_empno emp.empno%type,
         v_ename  out emp.ename%type
     )
     is
     begin
    
        select ename into v_ename from emp
        where empno=&no;
          
     end;
     注： 调用带有输入参数和输出参数的存储过程
     只能由Java程序和过程调用
     declare
     v_ename emp.ename%type;
     begin
     pro03(7788,v_ename);
     dbms_output.put_line(v_ename);
     end;
    

        独立实现：
             定义存储过程
             1根据员工编号 删除员工信息
                   create or replace procedure  pro03(v_empno emp.empno%type)
             is
             begin
               delete from emp where empno=v_empno;
             end;
             declare
             
              begin
                  pro03(7369);
               end;


             2根据特定条件 调整员工薪水
             根据员工编号
             工资低于1500 涨1000
             职位是普通员工 薪水涨300
             create or replace procedure pro04 (v_empno emp.empno%type)
             is
                         v_sal emp.sal%type;
                         v_job emp.job%type;
             begin
                 select sal into v_sal from emp where empno=v_empno ;
               if(v_sal<1500)  then
               update emp set sal=sal+1500 where empno=v_empno ;
               end if;
               select job into v_job from emp where empno=v_empno;
               if (v_job='CLEKER')  then
               update emp set sal=sal+300 where empno=v_empno;    
               end if;
               end;

           
             使用匿名块 打印dept表中的所有数据
            declare
                        v_dept_data dept%rowtype;
               v_count number(1);
             begin
                 select count(*) into v_count from dept;
                 for v_i in 1.. v_count
                 loop
                 select * into v_dapt_data
                 from (select * from dept
                 where rownum<=v_i
                 minus
                   select * from dept
               where rownum <=v_i-1);
               dbms_output.put_line(v_dept_data.dname||','
               ||v_dept_data.loc);
               end loop;
               end;
             end;
   使用游标来实现

       declare
          v_data dept%rowtype;

       cursor dept_cursor is select * from dept;
       begin
           open dept_cursor;

        fetch dept_cursor into v_data;

        while dept_cursor%found
        loop
         dbms_output.put_line(v_data.dname||','
               ||v_data.loc);
            fetch dept_cursor into v_data;
         end loop;
         close dept_cursor;   
       end;
      
       创建一个存储过程 查询某部门的员工数

       create or replace procedure findcount(
          v_deptno emp.deptno%type,
          v_num out number
       )
       is

       begin
       select count(*) into v_num from emp
       where deptno=v_deptno;


       end;
     参数既是输入参数，又是输出参数
     create or replace procedure findcount
     (
     v_num in out number
     )
     is

     begin
           select count(*) into v_num from emp
        where deptno=v_num;
     end;
    
     测试：
     declare
        v_num number(6);

     begin
     v_num :=10;
     findcount(v_num);
     dbms_output.put_line(v_num);
     end;

     异常处理：
     no_data_found
     too_many_rows
     zero_divede
     dup_val_on_index  唯一字段值重复

        输入不存在的用户编号 会抛出异常 no_data_found
     创建存储过程 根据员工号 查询员工的姓名
     create or replace procedure findname(
        v_empno emp.empno%type,
        v_ename out emp.ename%type
     )
     is
     begin
       select ename into v_ename from emp
       where empno=v_empno;

       exception
       when no_data_found then
       v_ename:='不存在';
       when others then
       null;
       end;
测试：
       declare
       v_ename emp.ename%type;
       begin
       findname(7788,v_ename);
       dbms_output.put_line(v_ename);
       end;
   程序包：

   可以定义存储过程 函数 类型
   定义程序包 并在程序包中定义存储过程

   create or replace package mypack
   is
       procedure hello(v_str varchar2);

   end;
   创建包体 对程序包中存储过程或函数 给出具体的实现
   craete or replece package body mypack
   is
   procedure hello(v_str varchar2)
   is
   begin
     dbms_output.put_line(v_str);
     end hello;
     end mypack;

     测试：
     exec mypack.hello('你好');
     函数：
       必须有返回值

       创建函数计算员工的年薪
       create or replace fundtion emp_income
       (
        v_empno emp.empno%type
       )
       return number
       is
       v_income number(8,2);
       begin
          select (sal+nvl(comm,0))*12 into v_income
       from emp
       where empno=v_empno;
       return v_income;
       exception
       when no_data_found then
       null;
       end;

       测试：
       select emp_income(7788) from dual;

       oracle中提供一种特殊的类型 记录类型
       record 需要用户通过record 自定义类型
       该存储过程需要一个自定义类型的参数 所以必须
       先创建一个程序包 程序包中定义一个记录类型

       create or replace package typepack
       is
         type mytype is record(
           v_ename emp.ename%type,
           v_ sal emp.sal%type,
           v_job emp,job%type
           );
           end;
         创建存储过程 查询员工的姓名 薪水 职位
     使用包中的 自定义类型

     create or replace procedure findemp
     (
        v_empno emp.empno%type,
        v_data out typepack.mytype
     )
     is
     begin
     select ename,sal ,job from emp
     where empno =v_empno;
     end;
     测试：
     declare
       v_data typepack.mytype;
       begin
       findemp(7788,v_data);
       dbms_output.put_line(
       v_data.v_ename||','||v_data.v_sal||','||v_data.v_job);
       end;
       游标：
       游标是一块内存区域 用于存放多条记录
       提供处理多条记录的方案
       创建游标的方式有两种
       方式一
            游标对象
        方式二
       游标变量

     ****** 游标对象
     %found 获取到数据返回true
     %notfount 没有获取数据返回false

     declare
     v_empdata emp%rowtype;
     cursor emp_cursor is select * from emp
        where deptno =10;
        begin
        open emp_cursor;

        fetch emp_cursor into v_empdata;

        while emp_cursor% found
        loop
        dbms_output.put_line(v_empadta.ename);
        fetch emp_cursor into v_empdata
        end loop;
        close emp_cursor;
        end;

        游标对象 使用游标的步骤：
        1. 创建一个游标的对象
        cursor cur is select ...
        2. 打开游标
        open cur...
        3.抓取数据
        fetch cur into v_data...
        4.提取数据
        while cur%found ...
        loop
        ......
        end loop;
        5.关闭游标
        close cur;

            方式二：游标变量 （参照游标变量）

         游标变量：一定明确 PL/SQL中 游标并非类型
         可以定义一个；类型参照游标
         定义一个变量 指定该变量类型为参照游标类型

         declare
          type cur_type is ref cursor;
          v_data cur_type;
          v_empdata emp%rowtype;
          begin
          open v_data for select * from emp
          where deptno=10;
          fetch v_data into v_empdata;
          while v_data%found
          loop
          dbms_output.put_line(v_empdata.ename);
          fetch v_data into v_empdata;
          end loop;
          close v_data;
          end;

          需求：创建存储过程 查询某部门的员工信息
            存储过程需要一个参照游标类型
            必须先创建一个程序包 在其中定义一个
            参照游标类型的变量

            craete or replace package curpack
            is
                 type  curtype is ref cursor;
            end;
         使用自定义的参照游标类型创建存储过程
     create or replace procedure findByDeptno(
           v_deptno emp.deptno%type,
           v_data out curpack.curtype
     )
     is
     begin
     open v_data for select * from emp
     where deptno=v_deptno;
     end;
     测试：
     declare
     v_empdata emp%rowtype;
     v_data curpack.curtype;
     begin
       findByDeptno(10,v_data);
       fetch v_data into v_empdata;
       dbms_output.put_line(v_empdata.ename);
       end;
        独立实现：
     创建存储过程 查询某表的所有数据
       create or replace package curpack
       is
         type curtype is ref cursor;
       end;

       create or replace procedure findBytablename
       (
          v_tablename varchar2,
       v_data out curpack.curtype
       )
       is
       v_sql varchar2(100);
       begin
       v_sql :='select * from ' || v_tablename;
       open v_data for v_sql;
       end;
       测试：
       declare
        v_data curpack.curtype;
     v_tabledata emp%type;
     begin
     findBytablename
     (
        'emp',v_data
     );
     fetch v_data into v_tabledata;
     dbms_output.put_line(v_tabledata.ename);
     end;
     创建一个存储过程，实现分页的功能

     思路：
       确定过程需要输入的参数
       表名 数据当前页数  每页显示数据的条目
       确定过程需要的输出参数
       分页的数据  最大的页数
           create or replace procedure findbypage
     (v_tablename varchar2,
     v_page number,
     v_pageSize number,
     v_data out curpack.curtype,
     v_maxpage out number
     )
     is
     v_datasql varchar2(100);
     v_countsql varchar2(50);
     v_start number(6);
     v_end number(6);
     v_count number(8);
     begin
          v_start:=(v_page-1)*v_pageSize+1;
          v_end:=v_page*v_pageSize;
          v_datasql:=
          'select * from('
          ||'select rownum rn,e.* from '
          ||'(select * from '||v_tablename||') e '
          ||'where rownum<='||v_end||') '
          ||'where rn>='||v_start;
          open v_data for v_datasql;
          v_countsql:='select count(*) from '||v_tablename;         
          --执行SQL语句execute immediate
          execute immediate v_countsql into v_count;         
          if mod(v_count,v_pageSize)=0 then
               v_maxpage:=v_count/v_pageSize;
          else
               v_maxpage:=trunc(v_count/v_pageSize)+1;
          end if;         
     end;
          测试：
          declare
            v_data curpack.curtype;
            v_maxpage number(5);
            begin
            findBypage('emp',2,3,v_data,v_maxPage);
            dbms_output.put_line(v_maxpage);
            end;
         事务：
     事务是一组DML语句的逻辑单元
     ACID
     A: 原子性 Atomicity
     事务的逻辑单元中所有的操作 要么都成功 要么都失败
     C:一致性 Consistency
     在事务执行前后 保证数据状态的一致性
     I:隔离性 Isolation
          多个事务之间具备隔离性
       D:持久性 durability
       事务提交 数据的状态将变成永久的

       事务的开启和结束：
        开启：
          DML语句执行 开启一个事务
          事务终止：
          执行commit、rollback结束事务
          连接突然中断（隐式提交）
          DDL操作（隐式提交）
          如果出现其他异常 （隐式提交）
       事务命令：
       commit 提交事务
       rollback 回滚事务
       设置回滚点
       saverpoint p1;
       rollback to p1;

       触发器：
       触发器在数据库里以独立的对象存储
       触发器是由一个事件来启动运行
       触发器不能接收参数
       触发器的组成：
           触发事件
        触发时间  before   after
           触发器本身
        触发频率

       create or replace trigger update_emp_trigger
       after
          update on emp
       for each row
       begin
         dbms_output.put_line('hello');
         end;

         编写一个触发器，在my_emp表中删除数据时  自动在my_emp_bak 表中备份数据

     create or replace trigger delete_trigger
       before
          delete on my_emp
          for each row
          begin
            insert into my_emp_bak
            values(:old.empno,:old.ename);
            end;