orcal入门，这一章足够

一:orcal 和mysql不同之处

1:dual表的使用

​ mysql:查看数据不是在于某一张表的时候 直接使用select语句
​ 举例:select 1+1;
​ orcal:查看数据不是在于某一张表的时候,需要根from dual表
​ 举例:select 1+1 from dual;
​ [-- ocarl查询数据的格式必须是select * from tab这种格式 --]

/*oracle要求select语句必须指定from table
如果仅仅是 简单的运算 不是查询某张表 此时可以用dual表来替代
*/
select 1+1 from dual;

--补充 查看表结构 TABLE_NAME必须大写
select * from user_tab_columns where TABLE_NAME='DEMO'

2:dml涉及到事务方面

​ orcal涉及事务方面**,默认是手动提交** 不提交数据库就不会更改 其他会话不能获取到

--orcal当只写一个表名的时候,默认本用户的表
select * from tab  --此时通过system用户访问wang用户的表 失败
--orcal想使用别人的表时候,必须加上用户名,并且有访问权限
select * from wang.demo --此时加上用户名.表名 成功

3:伪列和分页

​ rowid:是本行数据的物理地址 唯一值 可以通道此值快速找到行数据

​ 补充:

--[一定要用表别名]  [可以用表别名] [不能使用表别名]
select d.*,d.rowid,rownum from demo d;
-- rowid 是本行数据的物理地址 唯一值 可以通道此值快速找到行数据
select * from demo where dname='杨磊'
-- 通过rowid唯一地址查询速度比表的主键查询效率高
-- 因为通过主键需要扫描文件 而通过物理地址不需要扫描
select * from demo where rowid='paAAASNfAAGAAAAAMAAA';

补充:ROWID是物理存在的，它使用基于64 为编码的 18 个字符来唯一标

识一条记录物理位置的一个ID**，类似于Java** 中一个对象的哈希码

(内存地址)，都是为了唯一标识对应对象的物理位置，需要注意的

是ROWID虽然可以在表中进行查询，但是其值并未存储在表中，

所以不支持增删改操作

​ rownum:是获取结果集后 自动给结果集添加的行号[从1开始,为了实现orcal的分页]

/*ROWNUM 是获取结果集后 自动给结果集添加的行号[从1开始 \
为了实现orcal分页]   limk是mysql方言 !!!! */
/*这种格式可以使用 */
select d.*,rownum from demo d where rownum<=2
 /*语句有问题 rownum是查询完结果以后的内容*/
select d.*,rownum from demo d where rownum>=4 and rownum<=6
/*分页 需要使用子查询*/
select * from (select d.*,rownum r from demo d) d1 where d1.r>1
/*效率高一点*/
select * from 
(select d.*,rownum r from demo d where rownum<=7) d1 
where d1.r>=2

4:dml不同之处: insert update delete

​ mysql支持一次插入多行
​ orcal不支持.每个insert语句只能插入一行

/* 删除表的行记录 delete truncate*/
delete from demo where dname='yuan';
truncate table demo;
/*相同之处:
 1:delete 和truncate都可以用于删除表记录
 不同之处:
          1:delete 是dml(数据操作 对表记录的增删改) 需要提交事务
            truncate 是ddl(数据定义 对表结构的增删改) 不需要事务
          2:delete 可以使用where条件,删除指定行
            truncate只能删除整张表,是吧表删除,在创建一个
            一摸一样的表,但是表中没有内容*/

5:ddl:

​ check约束 --检查约束

create table demo02(
       did int,
       dage int,
       check(dage>0 and dage<100)
);
select * from tab
/*失败*/
insert into demo02  values(1,101);
-- 成功
insert into demo02  values(1,99);

​ 约束内容

/*1:check 检查约束
  2:unique 唯一约束
  3:default 默认约束
  4:not null非空
  5:primary key主键
  特点:唯一 不为空 可以被从表的外键引用
  6: foreign key外键  
  特点:可以重复  可以为Null  如果有值,值必须在主表的主键中存在 */

--使用约束
select * from tab
create table demo03(
       did int primary key--定义表时候指定主键约束
);
-- 创建表后添加约束
create table demo04(
       did int
);
alter table demo04 add primary key(did);--添加主键约束
alter table demo04 drop primary key; -- 删除主键约束

--定义表指定外键约束
drop table demo06
create table demo06(
       did int,
      constraint fk_1 foreign key(did) references demo05(tid)
);
-- 删除外键
alter table demo06 drop  CONSTRAINT  fk_1
--添加外键约束
alter table demo06 
add constraint fk_1 foreign key(did) references demo05(tid);

--吧一张表中的数据插入到另一张表的数据中
--注意,字段类型和个数必须一致
insert into 被插入表 select * from  要插入此表中的数据

6:ddl

create table student(
   sid int primary key,
   sname varchar(20),
   sage int,
   score float,
   stid  int  references teacher(tid)
);
create table teacher(
   tid int primary key,
   tname varchar(20),
   tage int,
   tsalary float
);
insert into teacher values(1,'王老师',32,10000);
insert into teacher values(2,'高老师',31,10010);


insert into student values(1001,'张三1',19,23.5,1);
insert into student values(1002,'张三2',18,13.5,1);
insert into student values(1003,'张三3',17,33.5,1);
insert into student values(2001,'张三4',16,43.5,2);
insert into student values(2002,'张三5',15,53.5,2);


/*获取所有师生的名字  编号  年龄*/
select t.tname,t.tid,t.tage from teacher t
select sid 编号,sname 名字,sage 年龄 from student union 
select tid,tname,tage from teacher
/*获取所有老师及其学生的信息*/
select * from teacher t,student s where t.tid=s.stid
/*获取最低分学生的信息*/
select * from student s where s.score=(select min(score) from student)
/*获取最低分学生及其老师的信息*/
select * from student s,teacher t where s.score=(select min(score) from student) and s.stid=t.tid

二:序列 sequence

**概念:**SEQUENCE是序列号生成器.可以为表中的行自动生成序列号,产生一组等间隔的数值(类型为数字)
主要用途是生成表的主键值 实现自动增长

/*sequence 创建sequence */
create sequence s_1;
/*获取s_1下一个值*/
select s_1.nextval from dual; 
/*获取序列s_1当前值 */
select s_1.currval from dual; 

可以设置一次增长多少

-- 创建时候的代码
create sequence s_2
start with 10 --指定起始值 默认值1
increment by 2 --指定每次加的步长 默认值1
maxvalue 50  ---指定最大值
minvalue 10  --循环时的最小值
cycle  --cycle循环 nocycle 不循环
cache 4;  -- 缓存个数 
--缓存是为了减少对数据库的访问 每次取4个放在缓存中 默认存放20个

字符串拼接

select * from student
-- 主键自增 数字拼凑在名字后面
insert into student(sid,sname) values(s_1.nextval,concat('哈哈',s_1.currval));
--主键自增 数字拼凑,一条语句nextval只会自增一次 所以连两个nextval结果值一样
insert into student(sid,sname) values(s_1.nextval,concat('哈哈',s_1.nextval));
/*字符串拼接*/
insert into student(sid,sname) values(s_1.nextval,'哈哈'||s_1.nextval);

三:索引 index

概念:

--索引 :index
--索引是一种为了提高查询效率的数据库对象
--类似于书的目录
-- 索引是定义在表的某个列上
--所以和表是分开储存的
-- 索引时程序员创建 供数据库查询时使用(可以减少IO操作)
-- 数据库会自动使用和维护索引

select * from user_indexes --查看 索引对应的表
select * from user_indexes where TABLE_NAME='STUDENT' --通过表名查看索引

唯一索引

   --有uniquc 约束的列 自动创建唯一性索引
  create table demo05(
         tid int primary key,  --主键也会创建唯一索引
         tname varchar(20) unique, --此列会自动创建唯一索引
         tage int,
         tsex char(2)
  );
  select * from user_ind_columns where TABLE_NAME='DEMO05';

非唯一索引

非唯一索引是程序员自己创建的

建议对列创建索引的情况

1:	字段经常出现在where条件后面
2:	查询的结果集行数小于表总行数的2%或者4%
3:	字段的值不能频繁修改
4:	字段不能有大量的null值
5:	字段值分布广,就是字段中的类型多,[如性别,只有两个类型,不建议使用]

   -- 创建索引 对应内容 ind01 索引名字  demo05(tage)指定表明及字段  
   create index ind01 on demo05(tage);
   --删除索引
   drop index ind01

四:视图:view–结果集

4.1、视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，

​ 是一个select语句的查询结果(结果集)

4.2、视图中并不存放数据，数据库只存储视图的定义 不会专门存

​ 放视图的数据

​ 数据是存放在基表中

视图：view 视图存在的意义：简化sql语句的书写

作用：简化sql书写 (降低数据库的复杂度) 对敏感数据的保护

--创建视图
create view v01 as select * from student ;
select * from v01;
--可以通过视图对表增删改 但必须满足条件
-- 不能违反基表的约束
-- 视图对应的select语句不能包含 集合函数 分组 disstinct等操作
-- 视图中不能包含伪列和表达式等

五:事务

​ 概念:事务表示同一个业务的多个dml语句,同生共死

​ 术语:表示可能更新数据库中各种数据项的一个程序执行单元

–	-=-
tcl	事物控制语句
commit	事物提交
rollback	事物回滚
savepoint	标记事物的回滚的点

事物特点–	–
原子性 atomicity	同一个事物涉及多个和dml语句必须同生共死
一致性 Consistency	不管事物是否执行成功 事物前后的数据必须一致
隔离性 lsolation	两个事物之间没有影响
持久性 Durability	事物一旦提交 最终的数据必须持久化保存到数据库中

begin --plsql的开始
      update student set score=score+100 where sid=1001;
      update student set score=score-100 where sid=1002;
      commit;--事务提交
             dbms_output.put_line('平分分数成功');
      exception --捕获异常
             when others then --相当于捕获其他所有异常
             dbms_output.put_line('转账成功!'); --这相当于打印语句
             rollback;--事物回滚
end; ---结束

六.函数

1:数学相关函数

-- 数字相关的函数----------------------------------------
---------------------------------------------------
--1 round(n)    正宗的四舍五入
--  round(n,m)  四舍五入,取位小数
--2 floor(n) 小于等于最大整数
--3 ceil(n)  大于等于最大整数
select ceil(2.5),floor(2.5),
round(2.5),round(2.4),round(2.6) from dual; 
--幂相关的
--sqrt(n) n开方
--power(n,m) 求n的m次方
select sqrt(4),power(2,3) from dual;
--基本运算
-- mod(3,m) 求余数,3除以m的余数
-- abs(n) 绝对值
-- trunc(n) 取整数
-- trunc(n,m) 截取n,保留m位的小数
select mod(9,2),trunc(2.431),trunc(2.432,2) from dual;

2:转换函数

--转换函数------------------------------------------
-------------------------------------------------------
-- to_date(str,pattern)字符串转换日期
select to_date('1932-11-21 13:23:23','yyyy-MM-dd HH24:mi:ss')
from dual;
-- to_char(date,pattern)吧日期转换为指定格式字符串[也可以转换其他内容]
--sysdate表示现在时间
select to_char(sysdate,'yyyy') from dual;
select to_char('243.32') from dual;
--to_number  吧字符串转换为数字
select to_number('$1,234.22','$9,999.99') from dual;

3:字符串相关函数

-- 字符串相关的1函数--------------------------------------
---------------------------------------------------------
-- 1:length(str) 获取str的字符个数
select length('ez方式2@') from dual
-- 2:ascii(x) 获取字符x在编码表ascii中的整数
select ascii('a') from dual;
-- 3:trim(str) 去除两边空格 ltrim(str)去除左边空格 rtrim(str)去除右边的右边的空格
select '---'||trim(' v  ')||'----' from dual;
-- 4:concat(str1,str2)字符串拼接:只能有两个整数
-- ||等价于concat  而且可以拼多个
select concat('fadds','afa') from dual;
--5:instr(str1,str2)获取str1中str2第一次出现的位置 下表从1开始
--instr(str1,str2,num)获取str1中str2第一次出现的位置 下表从num开始
select instr('asda','1') from dual;
select instr('adfsaa','a',3) from dual;
--6:substr(str,startIndex)从startIndex位置处截取到末尾的子串
--  substr(str,startIndex,len) 从startIndex位置出截取len个字符串的字串
select substr('dasdad',4) from dual;
select substr('dasda343d',4,1) from dual;
--7:upper 字符串字符转换大写
--  lower 字符串转化小写
select upper('adaWEF231'),lower('adaWEF231') from dual;

4:日期相关函数

--日期相关的函数-----------------------------------
----------------------------------------------------
-- 1:sysdate获取当前时间
select sysdate from dual;
--2: add_months(date,n)在date基础上添加n月
select add_months(sysdate,1) from dual;
-- 3:round(date,'year'|'month'|'day') 获取当前年|月|日的第一天
select round(sysdate,'year'),round(sysdate,'month'),round(sysdate,'day') from dual;
-- 4:last_day(date) 获取本月最后一天
select last_day(sysdate) from dual;
-- 5:extract(YEAR/MONTH/DAY/DATE/HOUR/MINUTE/SELECT from date)
select extract(year from sysdate),extract(month from sysdate)
,extract(DAY from sysdate) from dual;
-- 时分秒
select extract(hour from systimestamp)+8 from dual;

5:随机函数

--随机数--------------------------------------------
----------------------------------------------------
--int范围 dbms_random.random
select dbms_random.random from dual; 
--[0-1) dbms_random.value
select dbms_random.value from dual; 
--随机指定字符类型 以及个数dbms_random.string(类型,个数)
select dbms_random.string('u',2) from dual; 
--随机一个32位的字符串  sys_guid() 
select  sys_guid() from dual;
--  nvl(x,value) 如果x为null 返回value 否则返回x
--   nvl(x,value,value2) 如果x为null 返回value2 否则返回va

七:orcal中双引号和单引号的区别

--orcal中双引号和单引号区别
-- 双引号只适用于 密码 关键字 表名 列名 别名中
-- 名字不加双引号 默认是大写 加了双引号保存原状
-- 单引号1:表示字符串
--       2:转义字符串 类似于java中的\

八:pl sql动态sql语句 declare

1:基本格式

declare

​ 定义变量

begin

​ 可执行sql语句

​ exception

​ 异常捕获要执行的代码

​ end;

主要所用：实现事务控制+sql的流程控制

2:使用客户端软件

​ 1、oracle自带的命令行客户端软件为sql_plus

​ 2、复制完plsql代码后 再新起一行 输入\ 表示plsql输入完毕 开始执行

​ 3、需要命令set serveroutput on来开启命令行的控制台输出流

3:选择结构语句

​ 单分支语句–水仙花数

declare 
     shui int:='&waterFlowerNumber';
     geNum int:=mod(shui,10);
     shiNum int:=trunc(mod(shui,100)/10);
     baiNum int:=trunc(shui/100);
     bYes varchar(9):='不是';
begin
     if power(geNum,3)+power(shiNum,3)+power(baiNum,3)=shui then
       bYes:='是';
     end if;
     dbms_output.put_line('是否水仙花数?'||bYes);
     exception 
     when others then
     dbms_output.put_line('错误');
end;

**if elsif 双分支**--闰年

declare
       runYear varchar(7):='不是';
       nowYear int:=2000;
begin 
       if (mod(nowYear,4)=0 and mod(nowYear,100)!=0) then
                 runYear:='是';
       elsif mod(nowYear,400)=0 then
                 runYear:='是';
       end if;
        dbms_output.put_line('是否闰年吗?'||runYear);
        exception
          when others then 
          dbms_output.put_line('出现错误');
end; 

多分支–称呼问题

declare 
      chenghu varchar(20);
      sex varchar(3);
      age int;
begin 
      select sage into age from student01 where sid=1001;
      if age<18 then
           select ssex into sex from student01 where sid=1001;
              if sex='男' then
                chenghu:='小男孩';
              else
                chenghu:='小女孩';
            end if;
       elsif age>=18 and age<35 then 
            select ssex into sex from student01 where sid=1001;
              if sex='男' then
                chenghu:='帅哥';
              else
                chenghu:='美女';
            end if; 
       elsif age>=35 and age<60 then 
            select ssex into sex from student01 where sid=1001;
              if sex='男' then
                chenghu:='叔叔';
              else
                chenghu:='阿姨';
            end if;
       else 
            select ssex into sex from student01 where sid=1001;
              if sex='男' then
                chenghu:='爷爷';
              else
                chenghu:='奶奶';
            end if;
       end if;
       dbms_output.put_line(chenghu);
       exception
         when others then 
           dbms_output.put_line('错误');
end;

case选择语句–一月有多少天

declare 
      byear int:='&year';
      bmonthday  int:='&month';
      countDay   int;
begin 
      case bmonthday
        when 1 then
          countDay:=31;
        when 3 then
          countDay:=31;
        when 5 then
          countDay:=31;
        when 7 then
          countDay:=31;
        when 8 then
          countDay:=31;
        when 10 then
          countDay:=31;
        when 12 then
          countDay:=31;
        when 4 then
          countDay:=30;
        when 6 then
          countDay:=30;
        when 9 then
          countDay:=30;
        when 11 then
          countDay:=30;
        else
          if (mod(byear,4)=0 and mod(byear,100)!=0) then
            countDay:=29;
          elsif mod(byear,400)=0 then
            countDay:=29;
          else
            countDay:=28;
          end if;
        end case;
          dbms_output.put_line('今年多少天'||countDay);
        exception
        when others then
          dbms_output.put_line('错误❌');          
end;   

4:循环结构语句

for循环 只能自增 每次自增是1 会自动创建循环变量—1-100之间指数的和

declare
     number01 int:=0;
     nowNum int;--当前判断的数字
     bZhi  int;--定义一个变量,记录一个书是否质数 0是 1不是质数
begin
     for n in 2..100 loop --循环一到一百
       bZhi:=0; --每次都初始化为0代表是质数
       nowNum:=1;--赋值
       loop 
         nowNum:=nowNum+1;--先加一 等价于初始值等于2
         if nowNum>=n then --跳出条件
           exit;
         end if;
         if mod(n,nowNum)=0 then --中间有一次结果取余等于0
           bZhi:=1;--不是质数
         end if;
       end loop;
       if bZhi=0 then --是质数
         --吧数字转换为字符串,判断这个字符串中是否包含1
         if instr(n||'','1')>=1 then
           number01:=number01+n;
         end if;
       end if;
     end loop;  
     dbms_output.put_line('1到100的之间的和'||number01); 
end;

loop 循环

​ 一直循环,到达某一个条件时候结束 ----结束语句是exit

 declare 
     pdNum number:=1;--定义判断+ -值
     ciShu int:=1;--定义次数
     pi number:=0; --结果值
  begin
     loop
       if mod(ciShu,2)=0 then --取余等于0 减法
          pi:=pi-(4/pdNum); 
       else
          pi:=pi+(4/pdNum);
       end if;
       ciShu:=ciShu+1;
       if pi>3.1415926  and pi<3.1415927 then
         dbms_output.put_line('次数一共'||ciShu||'--'||pi);
         exit;
       end if;
       pdNum:=pdNum+2;--次数加二   
     end loop;
  end;

while循环–求1到100的和

--while循环 有条件
 declare
     n int:=1;
     sumn int:=0;
begin
     while n<=100 loop
         sumn:=sumn+n;
         n:=n+1;
     end loop;
     dbms_output.put_line('1到100的和='||sumn);
 end;

5:补充 goto语句 和&使用

–到达某一个条件跳转到指定位置

declare  
     n int:=1;
begin
     loop  
         n:=n+1;
         if n>100 then
             goto aaa; --goto语句
         end if;
     end loop;
     <<aaa>> ---给此位置定义一个名字叫aaa
     null;--名字下不允许有空语句 只能用null表示什么也不执行
     dbms_output.put_line('循环结束');  
 end;

&使用 :类似于scanner获取控制台输入内热

九:子程序!!! procedure 和function

**储存过程:**没有返回值的子程序

函数: 有返回值的子程序

优点	–
模块化	将程序分解为逻辑代码块
复用性	可以被任意数目的程序调用
可维护性	简化维护操作
安全性	通过设置权限,使数据更加安全

1、存储过程 procedure

定义基本格式：

​ create [or replace] procedure

​ 存储过程名(参数列表)

​ is或者as

​ 定义变量的代码 （可选）

​ begin

​ 实现功能的代码

​ enception

​ 异常处理的代码 （可选）

​ end;

--子程序 内容
create or replace procedure
       pro_1(n number,m int) --需要有参数 参数不能定义精度
as --as必须存在 
       --设置变量可以不存在
begin 
       dbms_output.put_line('四舍五入的内容'||round(n,m));
exception 
       when others then
       dbms_output.put_line('异常');
end;  
--调用程序测试
begin 
      pro_1(33.33333,10);
end;

2:plsql中支持的数据类型

--plsql的数据类型
   --1 支持所有的sql类型： 
   --int  float  number(n,m)  char varchar(n) varchar2(n)  date  timestamp  
   --clob blob boolean 
   --   %type 指定此字段大小内容和表中字段一直
   --   %rowtype  指定这个数据可以接收表中一行的数据
   

%type 指定此字段大小内容和表中字段一直

   create or replace procedure 
        pro_1(sid_arg int)
   as
        --定义变量接受列数据时  变量的精度不能比列精度低
        sname1 student.sname%type;--变量sname2的类型与student表sname列的类型一致
        sage1 student.sage%type;
        sex1 student.sex%type;
   begin
        select sname,sage,sex into sname1,sage1,sex1
        from student where sid=sid_arg;  
        dbms_output.put_line(sname1||sage1||sex1);
    exception  when others then
        dbms_output.put_line('出错');
   end;

%rowtype 指定这个数据可以接收表中一行的数据

   create or replace procedure 
        pro_1(sid_arg int)
   as
        --定义一个%rowtype类型的变量 接受行数据 
        stu  student%rowtype;--stu可以接受student表一行的数据
   begin
        select * into stu  from student where sid=sid_arg; 
        dbms_output.put_line(stu.sname||stu.sage||stu.sex); 
   exception
        when others then
        dbms_output.put_line('出错了');
   end;

3:粗存过程中的参数模式

方法的参数列表：定义的变量来接受方法运行需要的原始数据

模式1：in 模式(默认) 只能用于接受原始数据
模式2：out 模式 只能用于给调用者返回结果
模式3：in out 模式 即能接受原始数据还能返回结果

create or replace procedure
       pro_2(str varchar) --参数是字符串
as 
       charAt char(20); --截器每一个字串
       strNum varchar(200):=''; --拼凑数字字符串
       number01 int:=0;       --转换后的数字
begin
       for n in 1..length(str) loop
         charAt:=Substr(str,n,1);
         if charAt>='0' and charAt<='9' then --比较编码
           strNum:=trim(concat(strNum,charAt));--拼凑字符串
         end if;
       end loop;
       number01:=to_number(strNum); --转换数字
       dbms_output.put_line(strNum);
end;  

--测试程序
declare  
    str varchar(300):='123f12sdaasd2';
begin
    pro_5(str);
    dbms_output.put_line(str);
end;

4:补充内容 设置权限

   --删除存储过程
      drop procedure pro_1;
   --撤销指定用户对当前用户存储过程的执行权限
      revoke execute on tian.pro_1 from miao;
   --授予指定用户对当前用户存储过程的执行权限
      grant execute on tian.pro_1 to miao;
   --创建和调用没有参数的存储过程
      create procedure  pro_1
      as
      begin
           dbms_output.put_line('tian的pro_1');
      end;

      begin
           pro_1;
      end;

五:函数

函数:有返回值的子程序

格式:
create or replace function
函数名称(参数列表)
return 返回值类型
as或者is
定义变量的语句
begin
正常执行的语句
exception
捕获异常的语句
end;

   --eg:求一个int和一个float的和
    create or replace function
        fun_1(a int,b float)
        return float
    as
        sumab float;
    begin
        sumab:=a+b;
        return sumab;
    end;
    
   --调用函数方式1：通过plsql块调用
   declare
       sum1 float;
   begin
       sum1:=fun_1(1,1.3);
       dbms_output.put_line('sum1='||sum1);
   end;

--参数  字符串
--定义变量  str字符串 存放每一次找到的下标
--          str02字符串 拼凑最大值
--         mynumber 数字/字符串字串  假想最大值
1:先遍历字符串 判断下标是否在str字符串中,不存在就赋值给mynumber
2: i遍历 字符串个数
3: j遍历 字符串个数
4: 在j遍历时候判断,这个j的下标是否在str字符串中
5:如果存在,就执行下一次循环
6:如果不存在  找到最大值 吧最大值拼凑给str02字符串 吧下标存放进字符串中,用逗号隔开 

十:异常

1:异常概念:

​ 程序出现错误时候会停止语句执行 ,控制权执行对应的PL/SQL快的异常处理部分

2:代码案例

异常分为 :预定义异常和自定义异常

常见的预定义异常

--测试预定义异常
declare 
   n float;--浮点数
   id01 int;
   sname varchar(1);
begin 
--     insert into teacher values(1,'高老师',33,11000); --违反唯一约束
--     select to_number('dsa') into n from dual;  --数字转换异常 [ 此格式可以测试转换异常!!]
--     select tname into sname from teacher where tid=1; --变量精度太低
--     select tid into id01 from teacher; --一行数据字段接收多行数据字段
--     select tid into id01 from teacher where tid=303; --老师ID没有303 出现数据找不到错误
--       select 1/0 into n from dual; --0不能作为被除数
/*       case id01 
         when 2 then
           dbms_output.put_line('穷举法');
       end  case;
*/
       sname:='wqq24fsdhwejk';
exception 
  when DUP_VAL_ON_INDEX then
    dbms_output.put_line('违反唯一约束'); 
  when INVALID_NUMBER then
    dbms_output.put_line('数字转换异常');  
  when VALUE_ERROR then
    dbms_output.put_line('变量精度太低');
  when TOO_MANY_ROWS then
    dbms_output.put_line('需要一行数据,但是出现多行数据');
  when NO_DATA_FOUND then
    dbms_output.put_line('没有找到数据');
  when ZERO_DIVIDE then
    dbms_output.put_line('0不能被作为被除数'); 
  when CASE_NOT_FOUND then
    dbms_output.put_line('穷举法的case中没有找到对应的选择语句'); 
  when others then --捕获其他所有的异常
    dbms_output.put_line('其他所有的异常');
end;

自定义异常

关键字	作用
raise	抛出异常,指定对应异常代码块

declare 
    age int;--随意一个变量
    ageToSmallException exception;--自定义一个异常 异常类型是exception
begin
    age:='&输入一个年龄';
    --判断条件 如果满足条件就抛异常
    if age <= 0 then 
      --通过关键字raise抛出一个自定义异常 类似于java的throw
      raise ageToSmallException;
    end if;
    dbms_output.put_line('age是正常范围');
exception 
    when ageToSmallException then
      dbms_output.put_line('出现ageToSmallException异常,年纪不正常');
      --使用RAISE_APPLICATTON_ERROR方法错误原因通过弹出框提示用户
      --                错误指定码     错误原因
      RAISE_APPLICATION_ERROR(-20000,'出错了,年纪不合法');
end;

十一:游标 cursor

[for循环可以省略 stu定义 open close fetch]

1:关键字

关键字	作用
SQL	隐式游标的名字
%FOUND	判断是否有元素
%NOTFOUND	判断是否没有元素
%ISOPEN	判断是否开启[这里用!
%ROWCOUNT	影响行数[int类型]
cursor	创建显示游标地关键字
open	开启显示游标
close	关闭显示游标
fetch	从游标中获取一个元素

2:先判断在获取 多一条记录的原因

主要认知功能	作用
fetch	判断当前内容是否存在,存在就获取当前内容,并推动下标前进一个,到最后推不动就不改变下标 **[下标从0]**开始
%NOTFOUND	判断下标是否被改变,如果没有改变,就是true,[0例外``]

所以在使用时候注意顺序:先fetch获取在%NOTFOUND进行判断

3;loop显示游标

--loop案例
 --eg:1
declare 
     name01 varchar(200); --定义变量记录名字
     cursor c_1 is select sname from student;--自定义一个游标的名字 
begin
    open c_1;--开启游标
    loop --开始遍历游标
      if c_1%NOTFOUND then --判断是否有游标可以遍历
        exit;  --没有就退出
      end if;
      --从游标中取一个元素
      fetch c_1 into name01;
      --打印内容
      dbms_output.put_line('学生名字'||name01);  
    end loop;   
    close c_1;--关闭游标
end; 

--loop获取一个游标对象
 --eg:2
declare 
    stu student%rowtype;--这个变量接受表中一整行的数据
    --自定义一个游标
    cursor c_2 is select * from student;
begin
    --开启游标
    open c_2;
    --开始遍历游标
    loop
      --判断游标中是否有元素可以遍历
      if c_2%NOTFOUND then
        --没有元素就退出
        exit;
      end if;
    -- 如果有元素 就提取元素
      fetch c_2 into stu;
      dbms_output.put_line('学号'||stu.sid||',名字'||stu.sname||',年纪'||stu.sage);
    end loop;
    close c_2;--关闭游标
end;

4:for循环显示游标

--for循环 可以省略
--for循环可以省略 stu定义  open close fetch
--eg:4 
  declare 
  --自定义游标 
     cursor c_1 is select sname from student where sex='男';
  begin
     --开始遍历
     for stu in c_1 loop  --创建的stu是student对象  但c_1中内容只有sname
       dbms_output.put_line('姓名'||stu.sname);
     end loop; 
  end;
  

两个for案例

--eg:5
declare 
     --定义游标                             游标可以做的权限 [update 删除和修改]
   cursor c_1 is select * from student for update;
   stu student%rowtype;--定义变量对象
begin
   --开启游标
   open c_1;
   --遍历游标
   loop
        --先赋值2内容
        fetch c_1 into stu;
        --判断游标是否被修改过
        if c_1%NOTFOUND then
          exit;
        end if;
        --修改内容
        if stu.sex='女' then
          --修改时候指定游标 对当前内容进行修改
          update student set sage=16 where current of c_1;
        end if;
   end loop;
   --关闭游标
   close c_1;
   --提交事务
   commit;
end;
---------------------------------------------------------
--eg:6 修改性别 sid偶数是妖精 是奇数的删除
declare
       --自定义一个游标                   游标可以修改和删除表内容
       cursor c_1 is select * from student for update;
begin
       --遍历游标
       for stu in c_1 loop
         --判断条件
           if mod(stu.sid,2)=0 then 
             --修改语句 指定修改的游标
             update student set sex='妖' where current of c_1;
           else
             --删除
             delete from student where current of c_1;
           end if;
       end loop; 
       --提交事

十二:package包

1:概念

​ package:对所有的数据库对象(视图view ,索引index ,序列`sequence ,储存过程procedure ,函数function ,异常exception,游标cursor) 进行分类管理

1:包的规范和包的主题

--定义包的规范
--格式   create or replace package 包名
--       is/as
--       数据库的声明1 [就是存储过程或者方法]
--       数据库的声明2...
--       包名

--定义包的主题
--格式   create or replace package body 包名
--       is/as
--       数据库的对象
--       数据库的对象2...
--       end 包名

2:案例

--定义包规范
create or replace package 
       pack_2
as
       -- 存储过程：打印所有学生的自我介绍
       procedure studentMyShow;
       --存储过程：获取所有年龄小于20的学生  然后把女的删除  男的名字更改为无名
       procedure updateStudentName(message out varchar);
       --函数：  获取所有指定性别的学生的年龄
       function studentAgeAll(sex01 varchar) return varchar;
       -- 函数：  删除deleteChar(str c) 删除str中所有的c
       function deleteChar(str varchar,c varchar) return varchar;
end pack_2;
----------------------------------------------------------------------------------------------------------------
--定义包的主题
create or replace package body 
       pack_2
as         
       -- 存储过程：打印所有学生的自我介绍
       procedure studentMyShow
       as
              --定义一个游标
              cursor c_1 is select * from student;
       begin
              for stu in c_1 loop
                dbms_output.put_line('我叫'||stu.sname||',今年'||stu.sage||'岁');
              end loop; 
       end;
------------存储过程：获取所有年龄小于20的学生  然后把女的删除  男的名字更改为无名
       procedure updateStudentName(message out varchar)
       as
             --定义一个游标,具有删除和修改功能
             cursor c_2 is select * from student for update;
       begin 
             for stu in c_2 loop
               --判断年纪
               if stu.sage<=20 then
                  --判断性别
                  if stu.sex='女' then
                    --删除 指定游标
                     delete from student where current of c_2;
                  else
                     --修改 指定游标
                     update student set sname='' where current of c_2;
                  end if;
               end if;
             end loop;
             message:='修改成功';
       end;  
----------- --函数：  获取所有指定性别的学生的年龄
       function studentAgeAll(sex01 varchar) return varchar
       as 
                --定义游标
                cursor c_3 is select sage from student where sex=sex01;
                --返回值内容
                strMessage varchar(2000);
       begin
                --遍历游标
                for stu in c_3 loop
                  --拼凑年龄字符串
                    strMessage:=strMessage||stu.sage||',';
                end loop;
                --返回值
                return strMessage;
       end;
--------- -- 函数：  删除deleteChar(str c) 删除str中所有的c
       function deleteChar(str varchar,c varchar) return varchar
       as
                --定义变量,每次截取的整合 模仿删除    
                myStr varchar(2000):=str;
       begin
                --遍历字符串
          for i in 1..length(myStr) loop
            --判断
            if substr(myStr,i,1)=c then
               myStr:=substr(myStr,0,i-1)||substr(myStr,i+1,length(myStr));
            end if;
          end loop;
          return myStr;
       end;                                          
end pack_2;

十三:触发器 trigger

1:dml触发器 语 语句级触发器

一个dml的sql语句只触发一次 [语句级触发器有三个boolean变量 :update ,inserting,deleteingf]

/*格式:
       create or replace trigger 触发器名称
       before|afer update or insert or delete of 某列
       on 表名
       begin
            代码
       end;*/

create or replace trigger t_1
   before  update or insert or delete 
   on student
begin 
   if updating then
      dbms_output.put_line('student数据被修改');
   end if;
   if  deleting then
      dbms_output.put_line('student数据被删除');
   end if;
   if inserting then
      dbms_output.put_line('student有添加数据');
   end if;
end;

select * from student;
--触发事件的语句
update  student set sex='男' where sid=8;
insert into student values(1,'杨磊',20,99.99,1,'女');
delete  student where sid=10;

2:dml触发器–行级触发器

/*格式:create or replace trigger 触发器名称
       before|after update or delete or insert [of 某列]
       on 表名 for each row
       begin
             代码块
       end;
*/ 

-------:old 获取以前数据  :mew获取新的数据-------------------
--一行代码触发一次触发器
--update操作有 :old :new 数据
--delete 操作有 :old 数据
--insert 操作有 :new 数据
/*第一步:创建一个`student_log表 记录对student标进行ddl操作的内容*/
create table student_log(
       slog int primary key,--本表自己的ID
       sid  int,--dml影响行数的ID
       sname_old varchar(200),--修改前数据
       sname_new varchar(200),--修改后数据
       sage_old  int,
       sage_new  int,
       score_old number,
       score_new number,
       stid_old  int,
       stid_new  int,
       sex_old   varchar(3),
       sex_new   varchar(3)
);
--序列号
create sequence s_6 start with 1000;
--创建触发器
create or replace trigger t_2
       after update or delete or insert --选择被触发的条件
       on student for each row --表名是行级
begin
   if updating then
      dbms_output.put_line('student数据被修改');
      insert into student_log(slog,sid,sname_old,sname_new,sage_old,sage_new,score_old,score_new,stid_old,
      stid_new,sex_old,sex_new)  
      values(s_6.nextval,:old.sid,:old.sname,:new.sname,:old.sage,:new.sage,:old.score,:new.score,
      :old.stid,:new.stid,:old.sex,:new.sex);
   end if;
    if  deleting then
      dbms_output.put_line('student数据被删除');
      insert into student_log(slog,          sid,   sname_old,  sage_old, score_old, stid_old, sex_old)
      values                  (s_6.nextval,:old.sid,:old.sname,:old.sage,:old.score,:old.stid,:old.sex);
   end if;
   if inserting then
      dbms_output.put_line('student有添加数据');
      insert into student_log(slog,          sid,   sname_new,  sage_new, score_new, stid_new, sex_new)
      values                  (s_6.nextval,:new.sid,:new.sname,:new.sage,:new.score,:new.stid,:new.sex);
   end if;
end;

select s_6.nextval  from dual;
select * from student;
select * from student_log;
--触发事件的语句
update  student set sname='磊' where sid=8;
insert into student values(3,'杨磊',20,99.99,1,'女');
delete  student where sid=2;

2:ddl触发器

​ --专注于 update insert delete表结构的增删改

--ddl触发器     对表结构进行增删改添加的触发器
/*格式:create or replace trigger 触发器名称
       before|after create on schema --create,在当前用户下创建对象才会被触发
       on 表名 for each row
       begin
             代码块
       end;
*/ 
-- drop table ddl_message
create table ddl_message(
       obj_owner varchar(30), --所有者
       obj_name  varchar(30),--对象名称
       obj_type  varchar(20), --对象类型
       obj_user  varchar(30), --创建用户
       create_time date--创建日期
);
-- drop trigger t_3
create or replace trigger t_3
       after create on schema --在当前用户创建对象时候触发
begin 
       --插入日志记录
       insert into ddl_message(obj_owner,obj_name,obj_type,obj_user,create_time) 
       values(SYS.DICTIONARY_OBJ_OWNER(),
            SYS.DICTIONARY_OBJ_NAME(),
            SYS.DICTIONARY_OBJ_TYPE(),
            SYS.LOGIN_USER(),
            SYSDATE);
end;

十四：oracle的导入和导出

1. List item

1.1 使用plsql_developer工具实现

第一步：把需要的表到处 文件后缀名是.dmp

第二步： 创建一个新的用户

---删除用户
drop user miao cascade;
---显示所有用
select * from all_users;
-- 创建tablespace
create tablespace baospace datafile 'D:\soft_other\oraclesoft\oradata\orcl\baospace.DBF' size 100M;
---创建用户 并指定密码和默认tablespace
create user bao identified by "123" default tablespace baospace;
-- 授予权限
grant dba to bao;

第三步：用新的用户登陆 把数据导入

1.2 通过exp和imp 命令实现

​ 导出：在命令行输入： exp 用户名/密码@服务名 file=导出文件的路径

--专注于 update insert delete表结构的增删改

--ddl触发器     对表结构进行增删改添加的触发器
/*格式:create or replace trigger 触发器名称
       before|after create on schema --create,在当前用户下创建对象才会被触发
       on 表名 for each row
       begin
             代码块
       end;
*/ 
-- drop table ddl_message
create table ddl_message(
       obj_owner varchar(30), --所有者
       obj_name  varchar(30),--对象名称
       obj_type  varchar(20), --对象类型
       obj_user  varchar(30), --创建用户
       create_time date--创建日期
);
-- drop trigger t_3
create or replace trigger t_3
       after create on schema --在当前用户创建对象时候触发
begin 
       --插入日志记录
       insert into ddl_message(obj_owner,obj_name,obj_type,obj_user,create_time) 
       values(SYS.DICTIONARY_OBJ_OWNER(),
            SYS.DICTIONARY_OBJ_NAME(),
            SYS.DICTIONARY_OBJ_TYPE(),
            SYS.LOGIN_USER(),
            SYSDATE);
end;

十四：oracle的导入和导出

1.1 使用plsql_developer工具实现

第一步：把需要的表到处 文件后缀名是.dmp

第二步： 创建一个新的用户

---删除用户
drop user miao cascade;
---显示所有用
select * from all_users;
-- 创建tablespace
create tablespace baospace datafile 'D:\soft_other\oraclesoft\oradata\orcl\baospace.DBF' size 100M;
---创建用户 并指定密码和默认tablespace
create user bao identified by "123" default tablespace baospace;
-- 授予权限
grant dba to bao;

第三步：用新的用户登陆 把数据导入

1.2 通过exp和imp 命令实现

​ 导出：在命令行输入： exp 用户名/密码@服务名 file=导出文件的路径

​ 导入：在命令行输入： imp 用户名/密码@服务名 file=导出文件的路径 full=y