oracle笔记

1.大二下课程



2.
Oracle 9internet
Oracle 10G
Oracle 11Grid 


www.oracle.com


3.安装与卸载
   Orcl 实例  完成一件事情的过程
   1.卸载软件
   2.更改注册表  打开注册表 regedit P11 
4.服务
   1.TNSListener  侦听服务  监视每次操作，确保安全。
   2.OracleServiceORCL 基础服务
   注意先后顺序


5.管理工具的认识
  net manager   iSQL/Plus  基于网络
  sql developer     SQL/PLUS (学习阶段使用)
  第三方  PL/SQL   developer 
    登录SQL/PLUS 用户名、密码  system/manager


6.登录SQL/PLUS


  system/Manager


7.sqlplus常用命令
   执行语句 ";"结尾 Enter
  remark ...
  show user  查看当前用户 
  exit  退出
  spool 'E:\Oracle\demo.txt'
  <需要输出的内容>
  spool off
  set LineSize 200  设置宽度
  set PageSize 200  设置页数
  edit   编辑  注意：编辑窗口打开的时候 不需要 “;”  保存之后退出。用 "/"执行
  desc   查看表结构 


8.用户
  安装之后有三个常用的用户
  管理员
  system /manager  
  sys /as sysdba
  普通用户
  scott/tiger
  所有的表都是基于用户的不是基于数据库，为了安全性考虑


9.切换用户的命令
  conn system/manager;


10.新安装的scott
   a.密码过期
   this password has expired;
   alter user scott identified by abc;
   b.scott 被锁定
   alter user scott account unlock;


11.创建表空间 
   tablespace (逻辑概念)  相当于SQL Server中的数据库 database
        create tablespace UserDB
  datafile 'E:\Oracle\UserDB.dbf'
        size 10m
        autoextend on
12.脱机与联机
alter tablespace <表空间名称> Offline temporary;
   ...
   联机  


  alter tablespace <表空间名称> online;


13.创建用户
  create user <名称>  identified by <密码>  deflaut tablespace <表空间的名称> temporary tablespace temp; 
   1.新创建用户没有任何权限


   create user demo identified by abc default tablespace UserTS temporary tablespace temp;
14.授权 身份 角色
   connect  resource dba; 
grant connnect to demo;

回顾
  1.服务
    linstener 监听会话的状态
    ServiceORCL
  2.实例 ORCL  完成操作Oracle数据库的过程
  3.SQLPLUS命令
    remark 
    show user;
    set linesize ;
    spool '<文件路径>'
    spool off;
    exit
    desc
    start ，@  用来执行SQL脚本
  4.表空间  tablespace 类似于 database
    create tablespace Demo
    datafile 'e:\Oracle\Demo.dbf'
    size 10m
    autoextend on;
  5.用户
    create user Demo identified by abc default tablespace demo temporary tablespace temp;
  6. 权限的管理
    
   系统权限：connect resource dba 
     grant connect to demo
    对象权限：insert delete update  select  all  
     
    收回权限 
     系统权限：
     revoke connect from demo
     对象权限
     revoke select on scott.emp from demo;


Lesson_2
1.数据类型
     a.数值型
     标准SQL 
     int  float double money byte
     number(p,s)
     p:精度 123456.7890000000000000000   38位 
     S:小数点后有效位数
     number (9,-4)
     123450.000
     兼容sql标准的数据类型
     b.字符型
     标准SQL 
      CHAR(10)  VARCHAR(10)  nvarchar(10)
     内存机制
      Oracle
       char(10)  varchar2(10)
     c.日期型
      SQL Server 
        datetime
      Oracle 
        date
     d.大对象
        SQL Server 
         Text
        Oracle
        CLOB BLOB
      long  2G  1024*1024*1024*2
     保留字 UID 
    TO_Date('1922-01-01','YYYY-MM-DD');
    To_char('1922-01-01','YYYY-MM-DD');
   
    理解会话： Session
    alter session set NLS_DATE_FORMAT='YYYY-MM-DD'
2.空类型 null
    ''的比较 
     
3.SQL 中的符号
    a. in,not in
    select * from students where id in(1,2,3,66,45);
    select * from students where id in(select pid from position)
    b. any some all 比较符号  <,>,=....
     any(条件)  只要满足其中的某一个条件 就可以查询出来结果
        select * from students where id <any(1,3,65,77);
     all(条件)  满足所有的条件，才能查询出来结果
         select * from students where id<all(1,3,65,77);
4.DDL数据定义语言
     create  drop alter 
     添加字段  ADD 不能用not null;
     alter table students ADD(sAddress varchar2(100));
     更新字段  
     alter table students modify(sAddress varchar2(10))；
     删除字段
     alter table students drop(address);
     扩展
     可以更改会话显示的宽度
     col <列名> format a10  字符类型的列
     col <列名> format 99999 数值类型的列
5.DML 数据操作语言
    insert update delete select 
    联合查询  
    内连接 
   <表名> inner jion <表名> on <条件>
     select * from <表名> a,<表名> a where a.<字段> = b.<字段>
    左外连接 
    <表名>  a  left outer join  <表名> b on  a.<字段> = b.<字段>
     select * from <表名>  a,<表名> b  where a.<字段> = b.<字段>(+);
    右外连接
    <表名>  a  right outer join  <表名> b on  a.<字段> = b.<字段>
     select * from <表名>  a,<表名> b  where  a.<字段>(+) = b.<字段>;
6.数据控制语言 
    系统权限  系统提供的权限 
      grant ... to  ...
      grant create any tale to demo;  
    对象权限  可以对Oracle的对象的操作权限
      select update delete insert all
     收回权限
      revoke ....from ...
7.事务控制语言 TCL
     事务？把处理事件的步骤在一个单元内执行，要么一起成功，要么一起失败。
     T-SQL SQL Server
     PL/SQL Oracle
     1.begin tansaction <名称>
       set @tran_error int；
       set @tran_error =  0 ；
      2.开始事务的操作
       update
      3.记录错误
       set @tran_error = @tran_error + @@error;
      4.开始下一个事务的操作
       update
      3.记录错误
       set @tran_error = @tran_error + @@error;
      if @tran_error <> 0
        begin
           rollbank transaction;
        end
     else 
        begin
          commit transction;
        end;
    Oracle
       savepiont 回滚点  rollback to 回滚点   commit;
        update account set acc = acc - 1000 where aid = 1;
        update account set acc = acc + 1000 where aid = 2;
8.伪表、伪列
    dual  为了保证数据查询的完整性
    select sysdate from dual; 
    ROWID  ROWNUM查看流水  
    level ???  
   
    

回顾
  1.服务
    linstener 监听会话的状态
    ServiceORCL
  2.实例 ORCL  完成操作Oracle数据库的过程
  3.SQLPLUS命令
    remark 
    show user;
    set linesize ;
    spool '<文件路径>'
    spool off;
    exit
    desc
    start ，@  用来执行SQL脚本
  4.表空间  tablespace 类似于 database
    create tablespace Demo
    datafile 'e:\Oracle\Demo.dbf'
    size 10m
    autoextend on;
  5.用户
    create user Demo identified by abc default tablespace demo temporary tablespace temp;
  6. 权限的管理
    
   系统权限：connect resource dba 
     grant connect to demo
    对象权限：insert delete update  select  all  
     
    收回权限 
     系统权限：
     revoke connect from demo
     对象权限
     revoke select on scott.emp from demo;


Lesson_2
1.数据类型
     a.数值型
     标准SQL 
     int  float double money byte
     number(p,s)
     p:精度 123456.7890000000000000000   38位 
     S:小数点后有效位数
     number (9,-4)
     123450.000
     兼容sql标准的数据类型
     b.字符型
     标准SQL 
      CHAR(10)  VARCHAR(10)  nvarchar(10)
     内存机制
      Oracle
       char(10)  varchar2(10)
     c.日期型
      SQL Server 
        datetime
      Oracle 
        date
     d.大对象
        SQL Server 
         Text
        Oracle
        CLOB BLOB
      long  2G  1024*1024*1024*2
     保留字 UID 
    TO_Date('1922-01-01','YYYY-MM-DD');
    To_char('1922-01-01','YYYY-MM-DD');
   
    理解会话： Session
    alter session set NLS_DATE_FORMAT='YYYY-MM-DD'
2.空类型 null
    ''的比较 
     
3.SQL 中的符号
    a. in,not in
    select * from students where id in(1,2,3,66,45);
    select * from students where id in(select pid from position)
    b. any some all 比较符号  <,>,=....
     any(条件)  只要满足其中的某一个条件 就可以查询出来结果
        select * from students where id <any(1,3,65,77);
     all(条件)  满足所有的条件，才能查询出来结果
         select * from students where id<all(1,3,65,77);
4.DDL数据定义语言
     create  drop alter 
     添加字段  ADD 不能用not null;
     alter table students ADD(sAddress varchar2(100));
     更新字段  
     alter table students modify(sAddress varchar2(10))；
     删除字段
     alter table students drop(address);
     扩展
     可以更改会话显示的宽度
     col <列名> format a10  字符类型的列
     col <列名> format 99999 数值类型的列
5.DML 数据操作语言
    insert update delete select 
    联合查询  
    内连接 
   <表名> inner jion <表名> on <条件>
     select * from <表名> a,<表名> a where a.<字段> = b.<字段>
    左外连接 
    <表名>  a  left outer join  <表名> b on  a.<字段> = b.<字段>
     select * from <表名>  a,<表名> b  where a.<字段> = b.<字段>(+);
    右外连接
    <表名>  a  right outer join  <表名> b on  a.<字段> = b.<字段>
     select * from <表名>  a,<表名> b  where  a.<字段>(+) = b.<字段>;
6.数据控制语言 
    系统权限  系统提供的权限 
      grant ... to  ...
      grant create any table to demo;  
    对象权限  可以对Oracle的对象的操作权限
      select update delete insert all
     收回权限
      revoke ....from ...
7.事务控制语言 TCL
     事务？把处理事件的步骤在一个单元内执行，要么一起成功，要么一起失败。
     T-SQL SQL Server
     PL/SQL Oracle
     1.begin tansaction <名称>
       set @tran_error int；
       set @tran_error =  0 ；
      2.开始事务的操作
       update
      3.记录错误
       set @tran_error = @tran_error + @@error;
      4.开始下一个事务的操作
       update
      3.记录错误
       set @tran_error = @tran_error + @@error;
      if @tran_error <> 0
        begin
           rollbank transaction;
        end
     else 
        begin
          commit transction;
        end;
    Oracle
       savepiont 回滚点  rollback to 回滚点   commit;
        update account set acc = acc - 1000 where aid = 1;
        update account set acc = acc + 1000 where aid = 2;
8.伪表、伪列
    dual  为了保证数据查询的完整性
    select sysdate from dual; 
    伪列：ROWID 记录每个数据行在硬盘上的位置的唯一标识；
          ROWNUM 标识查询结果集中记录序号；流水号
          LEVEL 查询数据所对应的层次；
      
        create table Position
(
         pid  number primary key,
         parentID number,
         posName varchar2(10)
        );
       insert into position values(1,null,'总经理');
       insert into position values(2,1,'项目经理');
       insert into position values(3,1,'财务总监');
       insert into position values(4,2,'程序员');
       insert into position values(5,3,'出纳');
      
  select level,pid,parentID,posName from position connect by prior pid = parentid start with pid = 1


9.集合运算
    union、union all  保持集合运算的两个表 要保持表结构一致。
    union 去除重复行
    union all 不去除重复行


    intersect  查询交集  
10.函数
   Lower ()   把字符变成小写
   upper ()   大写
   Trim()  去除空格  使用的场景  
   char(10)存储用户名或密码的时候，可能会存储空字符中，在读取的时候 要用到Trim()函数去除空格。
   round() 重点
   ceil()


   


11.分组函数
   MAX、count avg min sum


12.分析函数
  row_Number行号  rank dense_rank




----4.排序函数 ROW_NUMBER、RANK、DENSE_RANK


 create table stuMarks 
(
ID NUMBER ,
SName varchar2(10),
sMarks number
);




insert into stuMarks values (1,'aa',50);
insert into stuMarks values (2,'aa',60);
insert into stuMarks values (3,'bb',50);
insert into stuMarks values (4,'bb',60);
insert into stuMarks values (5,'cc',78);
insert into stuMarks values (6,'aa',42);
insert into stuMarks values (7,'vv',67);
insert into stuMarks values (8,'dd',45);
insert into stuMarks values (9,'ff',60);
insert into stuMarks values (10,'gg',56);




select row_number() over (partition by sname order by sMarks) as sMarks,id,sname,smarks from stumarks


select rank() over (partition by sname order by sMarks) as sMarks,id,sname,smarks from stumarks


select dense_rank() over (partition by sname order by sMarks) as sMarks,id,sname,smarks from stumarks




partition  by  分组


order by 排序




SQL Server


select ROW_NUMBER() OVER (ORDER BY stuScore desc) as 排名, a.sName,b.ID,b.stuScore,b.stuSubject  from StuInfo a,StuScore b 
where a.ID = b.stuID and b.stuSubject = 'SQL'  ----没有并列编号


select RANK() OVER (ORDER BY stuScore desc) as 排名, a.sName,b.ID,b.stuScore,b.stuSubject  from StuInfo a,StuScore b 
where a.ID = b.stuID and b.stuSubject = 'SQL'   ---有并列编号，且有跳空行




select DENSE_RANK() OVER (ORDER BY stuScore desc) as 排名, a.sName,b.ID,b.stuScore,b.stuSubject  from StuInfo a,StuScore b 
where a.ID = b.stuID and b.stuSubject = 'SQL'   ---有并列编号，连续


---5.PARTITION BY  对所有进行先分组后排序 替代 多个where子句
select DENSE_RANK() OVER (PARTITION BY stuSubject  ORDER BY stuScore desc) as 排名, a.sName,b.ID,b.stuScore,b.stuSubject  from StuInfo a,StuScore b 
where a.ID = b.stuID   
  
     


Lesson_3  分区表、锁
 
   分区表：按照一定的规则把表的数据进行分段存储，用于处理数据量大的情形
      来源：毛泽东选集
    范围分区  纵向比较
     1.为分区表建立相对应的表空间
     2.把一个完整的表进行按照一定的范围进行划分  
create table StuTab
 (
  sID number primary key,
  sname varchar2(10),
  sAge number
 )
 partition by range(sID)
 (
   partition p1 values less than (1001)  tablespace a,
   partition p2 values less than (2000)  tablespace b,
   partition p3 values less than (maxvalue)  tablespace c
 )


 list列表 横向比较
  create table MPhone
 (
   MID number(11)
   Mtype varchar(10),
   location varchar2(10)
 )
  partition by list(location)
  ( 
   patition p1 values('武汉','北京'),
   patition p2 values('香港','深圳','珠海'),
   patition p3 values('上海','苏州'),
   patition p3 values(default)
)


 
 散列Hash分区 


方法一：只关心分区的数量  保证性能的优化
 
create table Orders
  (
  oID number(10),
  nName varchar2(10),
  Odate date
 )
 partition by hash(oid)
 partition 8;
 


方法二：给定分区的名称
 create table Orders
  (
  oID number(10),
  nName varchar2(10),
  Odate date
 )
 partition by hash(oid)
(
  partition p1,
  partition p2,
  partition p3,
  partition p4
)
 
方法三：给定分区存储的表空间
 create table Orders
  (
  oID number(10),
  nName varchar2(10),
  Odate date
 )
 partition by hash(oid)
 partitions 4
  store in(a,b,c,d)




创建复合分区???（下次课讲）
 
分区表的维护
   1.查询分区 在查询语句后面 增加分区
    select * from <表名>  partirion(p1);
   2.增加分区
    alter table <表名>  ADD partition p5 values less then (3001) tablespace a;
   3.删除分区
    alter table <表名> drop partition  p3;





    1. 创建表空间
    create tablespace a
    datafile 'e:\Oracle\a.dbf'
    size 10m
    autoextend on
    2.创建范围分区
    create table students
   (
    stuid number primary key,
    sName varchar2(10),
    sAddress varchar2(10)
  )
   partition by range (stuid)
      subpartition by list (sAddress)
   (
    partition p1 values less than(1001) tablespace a
    (
       subpartition p11 values ('武汉','南京'),     
       subpartition p12 values ('北京','上海'),
       subpartition p13 values (default)
     ),
    partition p2 values less than(2001) tablespace b
    (
subpartition p21 values ('武汉','南京'),     
        subpartition p22 values ('北京','上海'),
subpartition p23 values (default)
     ),
    partition p3 values less than(maxvalue) tablespace c
    (
        subpartition p31 values ('武汉','南京'),     
        subpartition p32 values ('北京','上海'),
subpartition p33 values (default)
   )
 )


  第三题   
  select subStr(ename,1,1)||lower(substr(ename,2,length(ename)-1)) from scott.emp


**************Lesson_4  数据库对象 ****************
  
 ###  锁  ###


    用途：解决并发


    挂起：Oracle 自动提供了锁对正在被访问的表锁定，当其他用户访问的时候，不能访问的状态叫做挂起。
  
 ### 数据库对象 ###
   同义词、序列、视图、索引
  
 1.同义词
   猴哥 = 悟空 
   同义词：解决查询的数据库表名过长的问题，方便查询 
   
    1.私有同义词 synonym 
 
   让demo 用户给 Scott.emp创建一个 同义词
    1).给demo用户 create any synonym  的权限；
   grant create any synonym to demo;
    2).为scott.emp创建同义词
   create synonym <同义词名称> for scott.emp;


    2.公有同义词 public synonym 
     1).给demo用户 create public synonym 的权限
      grant create public synonym to demo;
     2).创建公有同义词
       create public synonym pubEMP for scott.emp;
    3.删除同义词
      drop synonym myEMP;
      drop public synonym pubEMP;


2.序列  自动增长列  sequence


 1.顺序增长   


   1).创建序列


   create sequence mySeq;
   2).显式使用序列
   insert into users values (mySeq.nextVAL,'demo');


 2.按照一定的间隔进行增长  
    create sequence mySeq1 increment by 2;


   ******** 掌握到此 即 OK *******


 3.按照开始的值进行序列往下
   create sequence mySeq2 increment by 1 start with 10;
 4.最大值的控制 
    create sequence mySeq2 increment by 1 start with 1 maxvalue 10  cycle chache 10;


5.删除序列
   drop sequence mySeq;
 


3.视图 简化查询
    1).简单视图：非链表查询
     create view vi_sal
     as
     查询语句
  
    复杂视图:链表查询
     create view vi_demo
as
select a.deptno,a.dname,b.empno,b.ename,b.sal,b.comm from dept a,emp b where a.deptno = b.deptno;
   
    2)键保留表（主键被保留）
     复杂视图有的字段可以修改，有的字段不能修改
     stuid的主键在视图中依然是主键，但classes的主键不再作为主键使用。因此students的主键叫做键保留表的主键,可以被修改。而classes 的主键是非键保留表的主键，不能被修改
    3)with check option 约束检查
      


4.索引 
   1.全表扫描
     select * from emp where sal<1500 
    2.通过索引扫描
      B树索引  位图索引 反向键索引
      在此基础上把数据排列一下，在内存中形成一种数据结构
          把第一个数字作为根节点，小的放左边，大的放右边。排列成树状结构
          只查询部分记录。 
  2.索引分类
    自动索引
       通过主键自动建立的索引，唯一索引
    手动索引
       
  3.建立索引原则                  
     
    a.在经常查询的字段上面建索引，不要在所有字段上建索引。
    b.只在上面update ,insert,delete 不用建索引，看数据结构
    c.用久了的索引，应该重新建索引。
    
 

        
     
   1.回顾
   数据库对象
1.同义词
   1).同义词 synonym 
    a.私有同义词 
    b.公有同义词 
   2).同义词权限
    grant create any synonym to demo
    grant create public synonym to demo
   3).删除
     drop synonym myEmp;
2.序列 
    1).sequence 自动增长列
    2).建序列
      create sequence mySeq;
    3).对象的属性
       mySeq.nextVAL;
    4).按照一定的间隔
      create sequence mySeq increment by 2;
3.视图
    view  
   1).create view vi_***
      as
      <查询语句>
   2).键保留表
      
4.索引
    index
   




1.office 


2.html  ---> b/s (网站) 


3.sql Server   +  asp.net 


  mySQL  + PHP  
                   + html + js(Jquery) + ps(网页设计)
  Oracle + jsp
   
4.   c/s winform + SQL Server  
         ??????   + Oracle


5.企业信息
   客户 + 原理 


      


Lesson_5  PL/SQL


1.什么是PL/SQL？
  数据库内部定义的语言
  SQL Server  Transaction-SQL
  Oracle      PL/SQL  


2.语言的数据类型,区别于字段的类型


    number（int,float）, binary_interger,pls_interger


3.定义变量输出HelloWord
   1).输出Hello PL/SQL


   set serverpoutput on;  在客户端输出
   
    2).声明变量，并在begin 里面进行运算（方法体）；
   declare 
     v_hello varchar2(10);
     v_plsql varchar2(10);
  begin 
    v_hello := 'hello';
    v_plsql := 'plsql';
    dbms_output.put_line(v_hello || v_plsql);
end;
    3).给默认值（初始值）
   declare 
   v_hello varchar(10) := 'Hello ';
   v_plsql varchar(10) default 'plsql';
   begin
     dbms_output.put_line(v_hello || '，今天学的是 ：' || v_plsql);
   end;


************************* 注意 ********************
 
    4).（Oracle）利用%type定义变量 与 表中的数据保持一致 
    ***** 查询记录不能为多条；有且只有一行记录，不能返回空行或多行。 *****
     exact fetch returns more than requested number of rows
    ***** SQL Server查询多条记录的时候，给变量赋最后一个值  ****
     
   
4.
   1).利用查询语句进行赋值  %type 
   declare
      v_ename scott.emp.ename %type;
      v_sal   scott.emp.sal%type;
begin
    select scott.emp.ename into v_ename from scott.emp where scott.emp.empno = 7369;
  dbms_output.put_line(v_ename || v_sal);
end;
   2).利用查询语句进行赋多个值  %type 
   declare
   v_ename scott.emp.ename%type;
   v_sal scott.emp.sal%type;
 begin
   select scott.emp.ename, scott.emp.sal into v_ename,v_sal from scott.emp where    scott.emp.empno=7369;
  dbms_output.put_line('姓名：'||v_ename ||'，薪水：'|| v_sal);
end;




5.%rowtype ：赋整行的类型
declare
   v_row scott.dept%rowtype;
   v_dept scott.dept.deptno % type;
 begin
     v_dept := 10;
     v_row.deptno := 50;
     v_row.dname := 'demos';
     v_row.loc  := 'demos';
    v_dept := v_dept +  v_row.deptno;
    dbms_output.put_line(v_dept  || v_row.dname || v_row.loc);
 end;


   注意：null + 数字   仍然是 null
6.insert update delete 语句的使用
  step1:建表 
    create table test
    (
      tid number primary key,
      tname varchar2(10),
      tsal varchar2(10)
    );
    
  step2:建序列
    
     create sequence mySeq;
   
  step3:使用PL/SQL给表插入值


    declare 
      v_ename emp.ename%type := 'demo';
      v_sal   emp.sal%sal := 1200;
    begin 
      insert into test values(mySeq.nextval,v_ename,v_sal);
   end;


7.PL\SQL 控制语句
  1.  if 条件表达式 then
     执行语句
   end if;
   
   1）.判断部门30的预算是否合理
  declare 
      sumsal emp.sal % type;
   begin 
     select sum(sal) into sumsal from emp group by deptno having deptno  = 30;
  if sumsal > 5000 then
     dbms_output.put_line('部门为30的工资总额超出预算,共'||sumsal）；
  end if;
   end;
   2）.条件判断
    if 条件表达式 then
      执行语句
    else
      执行语句
    end if ;
    end;
     
  declare 
      sumsal emp.sal%type;
      disSal number := 0;
     begin 
    select sum(sal) into sumsal from emp group by deptno having deptno =30;
    if (sumsal < 5000) then
      dbms_output.put_line('上月预算合理，此次批准拨款,上次预算为：'|| sumsal);
    else  
      disSal := sumsal- 5000;
      dbms_output.put_line('上月预算超支，此次审核后拨款,上次预算超额为：'|| disSal);
     end if;
   end;




    3）.多从分支  
      if 条件 then
       执行语句
      elsif 条件 then
       执行语句
      else 
        执行语句
      end if;
      end;
       
     判断是否勤俭节约部门
     declare 
      sumsal emp.sal%type;
      disSal number := 0;
     begin 
      select sum(sal) into sumsal from emp group by deptno having deptno =30;
     if sumsal <= 3000 then
     dbms_output.put_line('最佳勤俭节约部门，花费预算为：'|| sumsal);
    elsif sumsal > 3000 and sumsal <= 5000 then   
     dbms_output.put_line('预算合理，花费预算为：'|| sumsal);
       else  
     disSal := sumsal- 5000;
      dbms_output.put_line('上月预算超支，此次审核后拨款,上次预算超额为：'|| disSal);
      end if;
      end;


  2.case  
       when 条件表达式1 then 
        执行语句；
       when 条件表达式2 then
        执行语句；
        else
        执行语句；
       end case;
       end;
   declare 
      sumsal emp.sal%type;
      disSal number := 0;
    begin
      select sum(sal) into sumsal from emp group by deptno having deptno = 30;
    case 
        when sumsal <= 3000 then 
         dbms_output.put_line('最佳勤俭节约部门');
        when sumsal > 3000 and sumsal <=5000
         dbms_output.put_line('预算合理');
        else  
        disSal := sumsal- 5000;
      dbms_output.put_line('上月预算超支，此次审核后拨款,上次预算超额为：'|| disSal);
    end;


8.循环控制 打印 1-10；
     语法： loop 
         exit when 条件表达式；
         执行语句
     end loop;
     
  declare 
     cou int := 1;
  begin 
     dbms_output.put_line('打印开始...');
  loop 
      exit when cou = 10;
       dbms_output.put_line('当前数字:'|| cou);
     cou:= cou +1;
   end loop;
 end;


9.  for 循环控制语句
     for 变量  in 起始值..结束值
     loop 
         执行语句
     end loop;
    
     declare 
         cou int := 1;
         result int := 0;
        begin 
        dbms_output.put_line('打印开始...');
      for cou in 1..10
        loop 
         dbms_output.put_line('当前数字:'|| cou);
          result := cou +1;
         end loop;
       end;


10.while 循环
     declare
     cou int := 0;
      begin
          dbms_output.put_line('打印开始...');
       while cou < 10
         loop
              cou := cou +1;
          dbms_output.put_line(cou);
         end loop;
         end;


11.跳转控制


  declare
      sumsal emp.sal % type;
  begin
      select sum(sal) into sumsal from emp where deptno = 30;
  if  sumsal > 2000 then
       goto first;
  elsif sumsal > 3000 then
       goto second;
  else goto third;
  end if;
 << first>>
   dbms_output.put_line('first:'||sumSal);
  return;
 <<second>>
    dbms_output.put_line('second:'||sumSal);
  return;
 <<third>>
    null;
end;


12.异常处理 
   1) 预定义异常
   declare 
     v_num number:= 0;
   begin 
     v_num := 2/v_num;
    dbms_output.put_line(v_num);
   exception 
      when others then
      dbms_output.put_line('error');
    end;




      


1.异常处理
   1) 预定义异常
   declare 
     v_num number:= 0;
   begin 
     v_num := 2/v_num;
    dbms_output.put_line(v_num);
   exception 
      when others then
      dbms_output.put_line('error');
    end;
   2).其他异常类型（略）
2.
  1).动态SQL
declare
   sqlstring varchar2(200);
   dept_id number(2) := 50;
   d_name varchar2(14) :='posal';
   location varchar2(10) :='develop';
  begin
  sqlstring :='insert into dept values (:1,:2,:3)';
  execute immediate sqlstring using dept_id,d_name,location;
end;


 
  2).动态SQL 把动态查询结果赋值给变量
declare
   sqlstring varchar2(200);
   emp_id number(4) := 7369;
   emp_rec emp%rowtype;
begin
 emp_id := &员工编号;
  sqlstring := 'select * from emp where empno = :id';
   execute immediate sqlstring into emp_rec using emp_id;
  dbms_output.put_line('员工编号：' || emp_rec.empno);
  dbms_output.put_line('员工姓名：' || emp_rec.ename);
end;


*******************  Lesson_5 游标 *****************




1.游标  显示游标


   为了暂存查询出来的多条结果。内存的引用


   1).提取emp中的所有数据
     declare
       --1.定义游标提取emp所有数据 
        cursor empcur 
         is 
         select * from scott.emp;
         emprow scott.emp%rowtype;
      begin
       --2.打开游标
       open empcur;
       --3.提取第一条记录数据；
      fetch empcur into emprow;
       --4.关闭游标;
         close empcur;
      end;


   2).循环提取所有的数据 ，有循环就需要有退出的条件   %notfound 游标检索数据表时，循环检测，直到不能被查询出来时。 %rowtype利用了面向对象的思想  一个%rowtype就是一个对象，
      读取与面向对象一样   students stu = new  students()   读取 stu.name  与  emprow.empno  一样  
     
       --1.定义游标提取emp所有数据 
        cursor empcur 
         is 
         select * from scott.emp;
         emprow scott.emp%rowtype;
      begin
       --2.打开游标
       open empcur;


       --3.提取所有记录数据；
      loop
      fetch empcur into emprow;
       exit when empcur%notfound;
      end loop;
       --4.关闭游标
         close empcur;
      end;
   3).定义多个变量去读取 
    declare
    cursor empcur
     is
    select empno,ename,sal from emp where deptno = 20;
    v_eno emp.empno%type;
    v_ename emp.ename%type;
    v_sal emp.sal%type;
   begin
     open empcur;
   loop
      fetch empcur into v_eno,v_ename,v_sal;
      exit when empcur%notfound;
      dbms_output.put_line('编号：'||v_eno ||',员工姓名：'|| v_ename || '，工资:' ||v_sal);
     end loop;
    close empcur;
  end;


2.游标的参数  empcur (形参)  ------> open empcur(实参) 


   declare
   t_dno emp.deptno%type;
   emprow emp%rowtype;
   --1.定义游标(参数)
   cursor empcur (dno emp.deptno%type) 
   is 
   select * from emp where deptno = dno;
  begin 
    --2.输入参数
    t_dno := &员工部门编号;
    --3.打开游标(带入参数)
    open empcur(t_dno);
    dbms_output.put_line('游标记录的条数:'|| empcur%rowcount);
     loop 
     --4.读取数据
          fetch empcur into emprow;
          exit when empcur%notfound;
          dbms_output.put_line('编号：'||emprow.empno ||',员工姓名：'|| emprow.ename || '，工资:' ||emprow.sal);
     end loop;
   dbms_output.put_line('游标记录的条数:'|| empcur%rowcount);
     --5.关闭游标
    close empcur;
 end;


3.循环游标  1.省略了open close  2.省略了行变量（对象）的定义
   declare 
     cursor empcur 
     is
     select * from emp where deptno = 10;
     begin 
       for emprow in curemp
       loop
         dbms_output.put_line('编号：'||emprow.empno ||',员工姓名：'|| emprow.ename || '，工资:' ||emprow.sal);
      end loop;
   end;


4.利用游标修改数据
   declare
     cursor empcur 
     is 
     select * from emp for update;
  begin 
     for emprow in empcur
      loop
         if emprow.deptno = 20 then
            dbms_output.put_line('进行修改的数据的编号为：' ||emprow.empno);
           update emp set ename = 'demo' where current of empcur;
          dbms_output.put_line('进行修改后的的数据为：' ||emprow.empno||',' || emprow.ename);
      end if;
     end loop;
 end; 


5.引用游标(动态游标)
   
 1).弱类型游标  （无规定的返回值） v_cur :可变的游标
   declare
--1.定义游标类型时，不控制游标的返回类型，。
   type empcur is ref cursor;
   v_cur empcur;
   command char(1);
   currow emp%rowtype;
   tname emp.ename%type;
 begin
      dbms_output.put_line('输入操作命令' || '1.查找dept = 30的员工姓名' || '查找job = manager的员工信息');
    command := &命令;
   if command = '1' then
            open v_cur for select empno from emp where deptno =30;
        loop
      fetch v_cur  into tname;
      exit when v_cur%notfound;
      dbms_output.put_line('部门为30的员工的姓名为:'||tname);
     end loop;
      close v_cur;
   else
     open v_cur  for select * from emp where job = 'manager';
      loop
        fetch v_cur into currow;
        exit when v_cur%notfound;
        dbms_output.put_line('编号:'||currow.empno||'姓名:'||currow.ename||'职位：'||currow.job);
      end loop;
     close v_cur;
   end if;
end;
   2).强类型游标 （规定返回的类型）
    declare 
  --1.定义游标类型时，控制游标的返回类型为对象类型%rowtype。
       type empcur is ref cursor 
       return emp%rowtype;
       v_cur empcur;
       command char(1);
       currow emp%rowtype;
 begin 
      dbms_output.put_line('输入操作命令' || '1.查找dept = 30的员工姓名' || '查找job = manager的员工信息');
       command := &命令;
     if command = '1' then
           open v_cur for select * from emp where deptno = 30;
        loop
        fetch v_cur into currow;
       exit when v_cur%notfound;
      dbms_output.put_line('编号...'|| currow.empno || '姓名...'||currow.ename ||'部门编号:'||currow.deptno);
      end loop;
    close v_cur;
   else 
      dbms_output.put_line('输入命令：2..');
      open v_cur for select * from emp where job = 'manager';
     loop
       fetch v_cur into currow;
        exit when v_cur%notfound;
   dbms_output.put_line('编号..'||currow.empno || '姓名:'||currow.ename ||'职位:' || currow.job);
end loop;
close v_cur;
end if;
end;




     
     *******************Lesson_7子程序和程序包*****************************


 PL/SQL程序单元
  
 1.是数据库中命名的PL/SQL块，作为数据对象保存在数据库中。
  
  什么是匿名函数？


  主要分类：
    1).存储过程（方法）：执行特定的操作，无返回值；
    2).函数：进行复杂计算，有返回值；
    3).包：逻辑上相关的过程和函数组织在一起；
    4).触发器：事件触发，执行相关操作。


 create table StuInfos
 (
   stuid number PRIMARY KEY,
   sName varchar2(10) not null,
   sSex char(2) check(sSex= '男' or sSex = '女'),
   ClassID int
 )


 create sequence mySeq;


 insert into StuInfos values (mySeq.nextVAL,'喜洋洋','男',1);
 insert into StuInfos values (mySeq.nextVAL,'懒洋洋','女',2);


 


  2.存储过程的创建与执行
   create or replace procedure pro_demo
   is
   begin
     null;
   end;
 
     
   执行一：
   execute pro_demo;


   执行三：
   begin 
    pro_demo;
   end;
 
  
1.不带参数的存储过程
 1).
  create or replace procedure pro_InsertStu
  is
  begin
   insert into StuInfos values (mySeq.nextVAL,'美洋洋','女',2);
  end;
  
  begin
    pro_InsertStu;
  end;


 2).定义变量
   create or replace procedure pro_InStuInfos
   is
    v_name varchar2(10) := 'lauren';
    v_sex char(2) := '男';
    v_class int := 1;
  begin
       insert into StuInfos values (mySeq.nextVAL,v_name,v_sex,v_class);
  end;


   begin
     pro_InStuInfos;
   end;
  
2.带参数的存储过程 形参的变量 定义类型的时候只给类型不给长度
 
  用户从客户端输入数据，使用存储过程的方式为数据库添加数据，比用ado.net或JDBC的效率要高。


  create or replace procedure pro_InStuInfos(v_sname varchar2,v_sex char,v_class number)
   is
  begin 
     insert into stuinfos values (mySeq.nextval,v_sname,v_sex,v_class);
   end;


   带参的调用
  begin
    pro_InStuInfos('joana','女',1);
  end;




3.创建带输出参数的存储过程
create table StuMarks 
(
  stuMarkID number  primary key,
  sSubject varchar2(10) not null,
  sScore int not null,
  StuID number references StuInfos(stuid)
)


insert into StuMarks values (1,'JAVA',87,1);
insert into StuMarks values (2,'JAVA',72,2);


  
--查询喜洋洋、懒洋洋的java成绩  


create or replace procedure proc_GetStu(v_sName stuinfos.sname%type,v_score in out StuMarks.sScore%type)
as


begin


  select b.sScore into v_score from StuInfos a,StuMarks b where a.stuID  = b.StuID  and a.sName  = v_sName and b.sSubject = 'JAVA';


end;






 ---调用
declare
   theScore StuMarks.sScore%type;
begin 
   proc_GetStu('喜洋洋',theScore);
   dbms_output.put_line('喜洋洋的分数：'||theScore);
end;






4.结合游标的使用
 create or replace procedure upsal(theno emp.empno%type, per number)
   is
      cursor empcur (theEmpno emp.empno%type) 
       is 
       select * from emp where empno = theEmpno for update;
       emprow emp%rowtype;
  begin 
     open empcur(theno);
     fetch empcur into emprow;
     update emp set sal =  sal * (1+per) where current of empcur;
     dbms_output.put_line('数据修改完成');
    close empcur;
end;




begin
   UPSAL(7369,0.2);
end;




5.定义out 变量
  
  create or replace procedure upDateSal (theempno number,thesal in out number)
   is
     thejob emp.job%type;
     tempsal emp.sal%type;
 begin
     select job,sal into thejob,tempsal from emp where empno = theempno;
    if thejob  = 'manager' then
       thesal := thesal *1.2;
    end if;
     update emp set sal = thesal where empno = theempno;
end;


declare 
   thesal emp.sal%type;
 begin 
   thesal := 3000;
   upDateSal(7689,thesal);
   dbms_output.put_line(thesal);
end;




6.函数的定义


create or replace function myAdd(a int,b int) return int
as
 begin 
  return a + b;
end;




---1).在SQL 语句中调用；
select empno,ename,myAdd(sal,300),sal from emp where deptno = 30;


--2).在PL/SQL中调用


declare 
  result int := 100;
 begin
    result := myAdd(12,13);
 dbms_output.put_line('结果是:'|| result);
 end;




7.包的使用（相当于java中的类）


  包头
create or replace package pak_demo
is
  procedure mySub(n int);
  function myFun(n int) return int;
end pak_demo;


 包体
 
create or replace package body pak_demo
as


 procedure mySub(n int)
  is
 begin
    dbms_output.put_line(n*n); 
 end mySub;
 
 function myFUN(n int) return int
 is
 begin 
  return n+1;
 end myFun;
end pak_demo;


调用


begin
  pak_demo.mySub(5);
end;






1.回顾




2.包和作用域  




3.1).用一个方法a去调用b的方法；
 a()
{
   b();
}
 2).如果b()里面有一个操作是用来于a()里面修改的值一样，那么a()方法里面的操作失效


 3).自主事务处理就是用来解决方法之间的冲突问题。


  方法一
 create or replace procedure subProc
 as
   mysal number;
 begin
   select sal into mysal from emp where empno = 7369;
   dbms_output.put_line('儿子过程的mysal:' || mysal);
   rollback;
 end;
 
  方法二  方法体中调用 方法一 被方法一中的rollback 影响


 create or replace procedure parentProc
 as
   mysal number;
 begin
   select sal into mysal from emp where empno = 7369;
   dbms_output.put_line('调用前的mysal:' || mysal);
    update emp set sal = 5555 where empno = 7369;
    subProc();
    select sal into mySal from emp where empno = 7369;
    dbms_output.put_line('调用后的mysal:' || mysal);
 end;


  execute parentProc;




 解决方式：自主事务处理


 create or replace procedure subProc
 as
   mysal number;


   --自主事务处理关键字
   pragma autonomous_transaction;  
 begin
   select sal into mysal from emp where empno = 7369;
   dbms_output.put_line('儿子过程的mysal:' || mysal);
   rollback;
 end;




**************************   Lesson_8 触发器   ***************************


  1.触发器是一个特殊的存储过程
     
  2.与存储过程的区别
     1).存储过程需要调用，触发器无需调用,触发器会自动调用；
     2).触发器不带参数，存储过程可带参数可不带参数。
  
  3.触发器的类型
    1).DDL 触发器 (了解)  create drop 
    2).DML 触发器 (行级触发器(掌握)，语句级触发器(理解))  insert update view 
    3).替代触发器 instead of 操作视图 (了解)


  4.创建简单触发器


   注意：主表的主键与从表外键的名称不能一样
    create table classes 
    (
      id number primary key,
      cname char(10)  
    );


     insert into classes values(1,'EIM1101');
     insert into classes values(2,'EIM1102');
     insert into classes values(3,'EIM1103');


    create table students
    (
     stuid number primary key,
     stuname char(10),
     sex char(2),
     cid number references classes(id)
    );


   insert into students values (1,'喜洋洋','男',1);
   insert into students values (2,'美洋洋','女',1);
   insert into students values (3,'小灰灰','男',1);
   insert into students values (4,'灰太狼','男',2);
   insert into students values (5,'红太狼','女',2);




1.触发机制   何时触发？  
  
DML触发器
 
  insert触发器的创建：语句级触发器 DDL
 
  create or replace trigger tg_insert
 
  before insert on student
 
  begin
 
     dbms_output.put_line('insert trigger is chufa le .....');
 
   end;


     insert into students values(201,'joana','女',1);




  update表级触发器的例子：
 
  create or replace trigger tg_updatestudent
 
  after update on students
 
  begin
 
    dbms_output.put_line('update trigger is chufale .....');
 
  end;




  库存表 


  出库表   




   删除班级表，班级的学生也删除     :old :new必须与行级触发器一起使用
   create or replace trigger tri_delStu
   before delete on classes
   for each row  --行级触发器：对每一行的每一条记录进行检查     
    begin 
       delete from students where cid = :old.id;
    end;


    delete from classes where id = 2;


    
     说明：:old.id  = 2;


  执行删除操作前，建立一个old内存表，Old内存表和classes 表的结构完全一致。


  前置触发器(before)与后置触发器(after)  ---操作业务逻辑级别而言与主外键约束没有关系




 :new :old是内存的表保存数据的时机   insert 是先在内存中开辟空间然后 保存所以会使用到:new内存表 
                                     delete 是先删除表中的数据，然后在内存中缓存一段时间 会使用到 :old内存表


 For each row的意义是：在一次操作表的语句中，每操作成功一行就会触发一次；不写的话，表示是表级（语句）触发器，则无论操作多少行，都只触发一次；






:new 与: old：必须是针对行级触发器的，也就是说要使用这两个变量的触发器一定有for each row
 
这两个变量是系统自动提供的数组变量，:new用来记录新插入的值，old用来记录被删除的值；
 
使用insert的时候只有:new里有值；
 
使用delete的时候只有:old里有值；
 
使用update的时候:new和:old里都有值；




 4.创建行级插入触发器
       
    create or replace trigger tri_inStudent
    after insert on students
    for each row 
    begin 
      insert into classes values(:new.cid,'GIS1102');
    end;




    insert into students values (6,'村长','男',4)
     
  :new 当插入数据之前，会在内存中建立一个 new内存表，然后再插入到数据库中，与classes保持一致。 :new.cid = 4;






 5.更新班级ID  学生表中 cid 级联更新
   create or replace trigger tri_update
    after update on classes 
   for each row
  begin 
      update students set cid =:new.id,stuname =:new.stuname,sex = :new.sex,cid =:new.cid  where cid =:old.id;
  end;


  update classes  set id = 8 where id = 1;




 6.行级触发器带条件
   
   create table tritest
  (
    stuid int primary key,
    stuname char(8),
    sindex int
  );
  
  create sequence tSeq;


  create or replace trigger over_tri
  before insert on tritest
  for each row
  begin
    if :new.stuid >= 30 then 
      raise_application_error(-20001,'超出了一个班的范围');
    end if; 
  end;
  
  有条件的触发
  create or replace trigger tri_over
  before insert on tritest
  for each row
  when (new.stuid< 2000)  --注意作为条件的时候  :new前面不需要 “:”
  begin 
    if:new.stuid >= 30 then
       raise_application_error(-20001,'超出一个班的范围');
   end if;
 end;


7.创建混合触发器
  create table Demos
  (
  id number primary key,
  dname varchar2(10),
  dSex char(2)
  )


  create or replace trigger tir_Demos 
  before insert or update or delete
  on Demos
   for each row 
  begin 
    if inserting then 
     dbms_output.put_line('insert 被触发...');
   elsif updating then
     dbms_output.put_line('update 被触发...'); 
   elsif deleting then
     dbms_output.put_line('delete 被触发...');
   end if;
 end; 






8.语句级触发器 常用在 DDL上
  
  create or replace trigger tri_updateEMP
  before insert or update or delete
   on emp
   begin
      if user not in ('SCOTT') then
        raise_application_error(-20001,'该用户不能修改EMP表');
      end if;
   end;




9.替代触发器  instead of 用来操作视图  解决键保留表的问题
    
create view vi_student 
as
select c.id,c.cname,s.stuid,s.stuname,s.sex,s.cid from classes c,students s where c.id = s.cid;
  
更新视图


非键保留表不能被修改
update vi_student set cname = 'GIS1102' where id=1;


创建instead of 触发器可以解决此问题


create or replace trigger tri_vi_stu
instead of insert or update or delete
on vi_student
for each row
begin 
  if  inserting then 
      insert into students values (:new.stuid,:new.stuname,:new.sex,:new.cid);
      insert into classes values (:new.id,:new.cname);
 elsif updating then
      update students set stuname = :new.stuname,sex = :new.sex,cid = :new.cid where stuid = :old.stuid;
      update classes set cname =:new.cname where id = :old.id;
 elsif deleting then
      delete from classes where id = :old.id;
      delete from students where stuid =:old.stuid;
 end if;
end;








总结：1).掌握行级触发器；
      2).理解语句级触发器；
      4).前置before与后置对象after 
      3).理解内置对象的使用方法  :new , :old