SQL语句（初识oracle）

文章目录

• • SQL语句（初识oracle）
• 数据库
• • 表空间
  • • 数据类型
  • 表
  • • 数据结构定义语句DDL
    • 数据操作语句 DML
    • 数据导入导出
    • 条件查询
    • 运算符
    • 虚拟表 dual
    • 聚合函数
    • 查询
    • 数据库对象1
    • 索引index
    • 语句块
    • 变量属性的类型
    • 循环
    • 分页练习
    • 函数function
    • 存储过程Procedure
    • 存储过程与游标
    • 触发器

数据库

-- 创建表空间***，注意表空间存储路径不能出现中文及空格
create tablespace *** datafiles 'E:\OraData\***.ora' size 100m;

--创建用户名***和密码******应用到***的表空间
create user （用户名***） identified by （密码******） default tablespace ***quota 50m on users;

--分配权限给用户***
grant all privileges to （用户***）;

表空间

数据类型

字符型：
char(2): 字符，没有数字默认为1，2表示初始化字符存储长度如果存储字符长度不足，自动以空格代替，最大为2000
varchar2(2):字符，可变长度字符存储，最大存放10个字符，不足会自动缩放.
long:字符（大文本）

数值型：
number(5):表示最大5位数的整数
number(5,2): 表示最大3位整数，两位小数

日期型：
date:精确到秒   yyyy-MM-dd yyyy-mm-dd hh24:mi:ss
timestamp:精确到毫秒的6位 

表

数据结构定义语句DDL

 -- create——创建  alter——修改  drop——移除

1、创建一个表

--1、创建一个表
create table T_test(
      id number(5) not null, --id
      name varchar2(30), --名称
      Gender char(1) default '1',
      age number(3),
      birthday date,
      create_time date,
      height number(3,2)
);
drop table t_test;--移除表

alter table t_test add (test_id number(6));--添加字段
alter table t_test rename column gender to sex;--修改字段名

alter table t_test drop column create_time;--移除某字段

2、创建数据表约束

--2、创建数据表约束
  --2.1）创建主键约束
  alter table t_test add constraint PK_test_id primary key(id);
  
  --2.2）检查约束
  alter table t_test add constraint ck_test_sex check(sex='1' or  sex='2' or sex='9');
  alter table t_test add constraint ck_test_age check(age>=1 and age<=100);

  --2.3）唯一约束
  alter table t_test add constraint uq_test_test_id unique(test_id);
  --2.4）默认约束
  alter table t_test Modify create_time default sysdate;
  --2.5）外键约束
  alter table t_test add constraint fk_test_info_id foreign key(test_id) references t_test_info(id) on delete cascade deferrable;
  

数据操作语句 DML

-- insert——插入  update——修改  delete——删除  select—查询

-- 3、新增数组 insert into...values
insert into t_test(id,name,sex,age,test_id) values(2,'hjk','1',23,3);
commit;

-- 4、修改语句 udpate....set...
update t_test set age = 140 where age>20;

-- 5、删除语句delete...from...

delete from t_test where id = 2;
commit;

 --6、查询语句 select * from...
 --查询所有列  所有行 (1=1是永恒条件)
 select * from t_test where 1 = 1
 
 --查询指定列，所有行
 select name,age,test_id from t_test where 1 = 1;
  
 --查询指定列，指定行
 select name,age,test_id from t_test where id = 1;
 
 --7、查询语句的升级
 --查询结果并创建表
 create table t_new_info as select * from t_test_info where id > 1;
  
 --8、查询并插入
 insert into t_new_info(id,mobile) select id,'110' from t_test;
 commit;
  
 --删除查询结果
  delete from t_new_info where id in( select id from t_test_info );
    

数据导入导出

在cmd中输入命令

-- 全导出
exp 登录名/密码@数据库名称  file = 路径\文件名.dmp full = y
 
-- 导出指定表
exp 登录名/密码@数据库名称  file = 路径\文件名.dmp tables=(table1,table2...)

-- 导出指定表的指定数据
exp 登录名/密码@数据库名称  file = 路径\文件名.dmp tables=(table1,table2...) query=\"where 条件\"

--压缩导出在命令后面加上 compress=y

-- 导入 如果对已经存在的表导入不报错，在命令后面加上ignore = y
imp 登录名/密码@数据库名称  file = 路径\文件名.dmp

-- 不同用户名之间的导入
imp 用户名/密码@数据库名称 fromuser=导出用户名 touser=导入用户名 file = 路径\文件名.dmp

条件查询

--精确查询（=）
  --查询性别是女生的学生信息
  select * from t_student where sex = '2';
  
   --查询性别是女生且名称为‘测试1’的学生信息
  select * from t_student where sex = '2' and name='测试1';
  
  --查询性别是男的或者年龄大于20岁的学生
  select * from t_student where sex= '1' or age>20;
  
  --模糊查询  % 或者 _
  --%
   --查询名字最后一位是8的学生信息
   select * from t_student where name like '%8';
   --查询密码用123开头的学生信息
   select * from t_student where password like '123%';
   --查询名字含有8的学生信息
   select * from t_student where name like '%8%';
   
  --  _ ：一条下划线代表一个任意的字符
   --查询名字倒数第二个字是2的学生
   select * from t_student where name like '%2_'; 
   

运算符

1、算术运算符

--算术运算符（+-*/ mod[取模]）
    --查询主科的成绩
    select (s.chinese+s.math+s.english)/3 as 主科成绩 from t_score s 
    --查询年龄是偶数且大于12岁的学生
    select * from t_student t where mod(t.age,2)=0 and age>12;
   
   --查询根据生日计算出年龄
   select round((sysdate-t.birthday)/365,0) age from t_student t;
   
   --比较运算符（> <  >=  <= != = <>between...and...）
   --查询年龄大于13且小于等于18的学生
   select * from t_student s where s.age > 13 and s.age <= 18;
   
   --查询身高在1.60m（含）到1.80m（含）的学生
   select * from t_student s where s.height >= 160 and s.height <= 180;
   select * from t_student s where s.height between 160 and 180;
   --<>oracle
   --查询性别不是男的老师信息
   select * from t_teacher t where t.sex <> '1';
   select * from t_teacher t where t.sex != '1';
   
   --in
   --查询语文分数是80,85,86,88的学号及语文分数
   select o.student_no,o.chinese from t_score o where o.chinese in (80,85,86,88);
   
   --not like
   --查询非南宁的学生
   select * from t_student t where t.address not like '%南宁%';

2、集合运算符

 --集合运算符：把两个或以上的结果集合成一个结果的运算
    --union : 集合合并——去除重复后面的记录留下不存在重复且重复中的一条记录
    
    --查询性别为一的信息
   select id, name, birthday, age
     from t_student t
    where t.sex = '1'
   
   union
   --查询ID为1,4,5,6,8,10的记录
   select id, name, birthday, age
     from t_student t
    where t.id in (1, 4, 5, 6, 8, 10);
   
   --union all 集合合并  所有集合的记录数相加，不去除任何一条
    --查询性别为一的信息
   select id, name, birthday, age
     from t_student t
    where t.sex = '1'
   
   union all
   --查询ID为1,4,5,6,8,10的记录
   select id, name, birthday, age
     from t_student t
    where t.id in (1, 4, 5, 6, 8, 10);
    
  --intersect 集合的交集，去掉不重复的只留下重复的记录
   
   --查询性别为一的信息
   select id, name, birthday, age
     from t_student t
    where t.sex = '1'
   
   intersect
   --查询ID为1,4,5,6,8,10的记录
   select id, name, birthday, age
     from t_student t
    where t.id in (1, 4, 5, 6, 8, 10);
    
    --minus 集合相减(补集)，把重复的全部去掉，且只留下第一个集合中不重复的记录（本表中的重复记录不会去除,去除相对于第二个表的重复记录）
    --查询性别为一的信息
   select id, name, birthday, age
     from t_student t
    where t.sex = '1'
   
   minus
   --查询ID为1,4,5,6,8,10的记录
   select id, name, birthday, age
     from t_student t
    where t.id in (1, 4, 5, 6, 8, 10);
    
 --别名（可用于列转行）
 select birthday bir from t_student t;
 
 select 'name'as 名字 from t_student;

3、列转行练习

 2、查询id=10的分数表记录并按照下面的格式显示
 id  课程  分数
 10  语文   75
 10  数学   80
 10  英语   80
 10  物理   72
 10  化学   85
 */
 select *
   from (select s.id as "id", '化学' as 课程，s.chemistry as 分数, '5' sx
           from t_score s
          where id = 10
         union
         select s.id as "id", '物理' as 课程，s.physics as 分数, '4' sx
           from t_score s
          where id = 10
         union
         select s.id as "id", '英语' as 课程，s.english as 分数, '3' sx
           from t_score s
          where id = 10
         union
         select s.id as "id", '数学' as 课程，s.math as 分数, '2' sx
           from t_score s
          where id = 10
         union
         select s.id as "id", '语文' as 课程，s.chinese as 分数, '1' sx
           from t_score s
          where id = 10) a
  order by sx;

虚拟表 dual

 -- 查询当前系统时间(永远只会返回一行多列)
  select sysdate from dual
 -- 获取年份
  select extract(year from sysdate) from dual;
 -- 获取月份(返回一个整数)
  select extract(month from sysdate) from dual;
  select extract(day from sysdate) from dual;
  
  select extract(hour from systimestamp) from dual;
  select extract(minute from systimestamp) from dual;
  select extract(second from systimestamp) from dual;
  select extract(timezone_abbr from systimestamp) from dual;
  
  --extract 可以计算两个时间的间隔(day,hour,minute,second)
  select extract(day from tt1 - tt2) days,
         extract(hour from tt1 - tt2) hours,
         extract(minute from tt1 - tt2) minutes,
         extract(second from tt1 - tt2) seconds
    from (select to_timestamp('2021-07-03 10:02:03',
                              'yyyy-mm-dd hh24:mi:ss') tt1,
                 to_timestamp('2021-07-01 09:05:03',
                              'yyyy-mm-dd hh24:mi:ss') tt2
            from dual) a
  -- 计算两个时间月份差 months_between
  select trunc(months_between(sysdate,date'2021-01-09'),2) mons from dual
  
  -- 向上取整，取大于这个值的最小整数
  select ceil(23.2) from dual;
  -- 向下取整，取小于这个值的最大整数
  select floor(23.2) from dual;
  --绝对值
  select abs(-25) from dual;
  --n次方
  select power(2,3) from dual;
  
  --字符串的操作
    -- 字母大写
    select upper('sdasff') from dual;
    --字母小写
    select lower('HKJKKL') from dual;
    --首字母大写
    select initcap('jkll') from dual;
    
    --替换
    select replace('ghja','hja','好的') from dual;
    -- 翻译(相同位置进行替换)
    select translate('dsagdghd','ds','yads') from dual;
    -- 替换(相当与switch()..case)
     select decode(1,1,'一',2,'二','数字') from dual;
     
    -- null 处理
    select nvl(null,'空') from dual;
    
    --字符串的拼接 ||
    select '年龄='|| t.age from t_student t;
    --虚拟列
   --rownum
    select t.*,rownum，rowid from t_student t

聚合函数

--只有一种查询结果的函数
 --统计记录数
 select count(t.id) from t_student t;
 
 --总和 列的值的总和
 select sum(t.age) from t_student t;
    
 -- 最大值 列的值的最大
 select max(t.height) from t_student t;
 
  -- 最小值 列的值的最小
 select min(t.weight) from t_student t;
 
 -- 平均值
 select round(avg(t.age),2) from t_student t;
  
 --查询学生中年龄最大,体重最轻，分别是多少
 select max(t.age) maxage,min(t.weight) minweight from t_student t;
  
-- 分组函数 group by  对查询的结果进行分类处理（先查询后分组）,且查询结果字段除了聚合函数字段外，只能有分组字段
 --查询学生中男女的人数
 select decode(t.sex, 1, '男', 2, '女', '不明性别') sex, count(t.id) con
   from t_student t
  group by t.sex;
  -- order by 排序函数  先查询再分组，最后排序  默认排序asc  倒序排序desc
   --查询所有学生信息，并按照年龄顺序排序
    select * from t_student t order by t.age asc, height desc

查询

1、等值连接查询

 -- 为更新 的查询
  select * from t_score for update;
-- 多表查询
select * from t_school l;--6
select * from t_class c;--10
-- 无关联查询
 select l.*,c.* from t_school l , t_class c;-- 笛卡尔积查询 
 
 --关联查询，表与表之间必须存在直接或间接的关系
  -- 等值连接查询
     select l.name sname,c.name cname from t_school l , t_class c where c.school_id = l.id
    
   --id = 1 这个学校有哪些老师
   select s.name sname, ec.teacher_id , t.name tname
     from t_school s, t_class c, t_teacher_class ec, t_teacher t
    where s.id = 1
      and s.id = c.id
      and ec.class_id = c.id
      and t.id = ec.teacher_id
    group by s.name , ec.teacher_id , t.name 
    
    -- distinct 去重复
    select distinct s.name sname, ec.teacher_id , t.name tname
     from t_school s, t_class c, t_teacher_class ec, t_teacher t
    where s.id = 1
      and s.id = c.id
      and ec.class_id = c.id
      and t.id = ec.teacher_id

2、join 连接查询

-- 内连接   inner...join...on...(on与where的作用相似)[与等值连接的查询结果是一样的]
 -- 使用等值连接查询
       select l.name sname, c.name cname from t_school l , t_class c where l.id = c.school_id
      --  使用内连接的方式查询
        select s.name, c.name cname
          from t_school s
         inner join t_class c
            on c.school_id = s.id
            inner join t_student_class sc on sc.class_id = c.id
            
    -- 外连接 outter...join...on...
     -- 左外连接查询  left outter join ... on -> left join ... on ...
     -- 与查询语句的left的左边的表为主表（显示完记录），右边的表（只显示对应的记录），用空值表示对应值
     
     --查询班级表的学生
       select * from t_class c
       select * from t_student t
       
     -- 按照内连接
       select c.name cname, count(t.id) 人数 from t_class c
       inner join t_student_class sc on sc.class_id = c.id
       inner join t_student t on t.id = sc.student_id
       group by c.id , c.name
       
     -- 左连接查询(尽量少写条件语句)
       select c.name, count(sc.student_id) con from t_class c
       left join t_student_class sc on sc.class_id = c.id
       left join t_student t on t.id = sc.student_id
       group by c.id, c.name
     
     -- 右连接查询
     select sc.class_id cid,
     (select id,name from t_class c where c.id = sc.class_id) cname,
      count(t.id) con
       from t_student_class sc
      right join t_student t
         on t.id = sc.student_id
      group by sc.class_id

数据库对象1

1、同以词 synonym

--1、同以词 synonym
    -- 创建同义词对象  
      --私有化——表示在当前的表空间使用
      --公有化——表示在当前数据库使用
      create or replace synonym syn_stu for khy0701.t_student; 
      
    --表的别名
    select * from t_student t;
    --同义词
    select * from syn_stu;
    
    --删除同义词
    drop synonym syn_stu;
    
    --创建共有化同义词
    create or replace public synonym psyn_cla for khy0701.t_class;
    
    --公有化同义词
    select * from psyn_cla;
    --删除同义词
    drop public synonym psyn_cla;
    
  --好处：保护表，做隐藏的查询，简写表名且可以在任何语句中使用。
  --坏处：易被误判表名

2、序列sequence

--创建序列表
 create sequence seq_class_id
   start with 9 --开始位置，默认值1
   increment by 1 --增量，每一次递增量，默认为1
   maxvalue 1000 --最大值
   minvalue 1 --最小值
   cache 2  --最小缓冲值
   cycle
    --序列的使用
     --查询
     select seq_class_id.nextval from dual;--查询下一个
     
     select seq_class_id.currval from dual; --查询当前
  
  --插入语句（为了主键不冲突）
  insert into t_class c
  (id, name, school_id) 
  values(seq_class_id.nextval,'2021年七年级1班',1);
  commit;

3、批量插入

--第三种方法（批量插入）
  insert into t_test
  (id,name,sex,birthday)
  --value
  select seq_student_id.nextval ,a.* from(
  select '测试1','1',date'2021-04-01' from dual
  union all
  select '测试1','1',date'2021-04-01' from dual
  union all
  select '测试1','1',date'2021-04-01' from dual 
  ) a

4、view视图

--视图 view
   -- 把统计每个班的学生人数写成一个视图
   create or replace view v_class_student_con as
   
     --查询各个班的学生人数
  select c.id, c.name, nvl(a.con, 0) con
    from t_class c
    left join (select sc.class_id, count(sc.student_id) con
                 from t_student_class sc
                group by sc.class_id) a
      on a.class_id = c.id
  
  --查询视图
   select * from v_class_student_con

索引index

1、唯一索引

 -- 4、索引index
   /*
   --优化查询速率，加速表与表的连接查询,对非查询功能的操作进行阻碍，加大维护成本
   */
   --唯一索引
   create unique index i_stu_id on t_student(id)
   
   --如何使用索引，在查询语句中where中使用到索引列作为添加时，自动使用索引
   select * from t_student t where t.student_no =  201609014501001 -- 不走索引
   
   select * from t_student t where t.id = 28 -- 符合条件会自动走索引  

2、组合索引

--组合索引
   create index i_no_term on t_score(year,school_term);
   
   select * from t_score o where o.year = '2016' and o.school_term = 1;  -- 走索引
   
   select * from t_score o where o.year = '2016' or o.school_term = 1;  ---不走索引
   
   -- 位图索引 建立在一些不经常用于更新的列
    create bitmap index i_student_sex on t_student(sex);
    
    --基于函数的索引（不能用于聚合函数，也不能用于大文本的数据类型）
    create index i_student_name on t_student(upper (name));

语句块

-- 语句块
    Declare
      A number(3); --声明整形变量A
      B number(3) := &b; -- 声明变量B且赋值3
      c float;
    begin
      --可执行的开始
      A := 10; --变量的赋值
      dbms_output.put_line('这里是输出语句'); --控制台输出
      c := A / B;
      dbms_output.put_line(A || '÷' || B || '=' || round(c, 2));
    Exception
      --异常捕捉
      when others then
        dbms_output.put_line('除数不能为0');
    end; -- 可执行部分的结束
     
     
     -- 查询输出男性学生的人数
     select t.sex, count(t.id) con
       from t_student t
      where t.sex = '1'
      group by t.sex
       --语句块
       Declare
        v_sex varchar2(20);
        v_con number;
        i_sex char(1):= &性别; 
       
       begin 
         select t.sex, count(t.id) con into v_sex,v_con --into用于查询结果为一行
       from t_student t
      where t.sex = i_sex
      group by t.sex;
      
      if v_sex ='1' then v_sex :='男';
      else v_sex := '女';  
      end if; -- 判断结束
      
      dbms_output.put_line('性别：'|| v_sex || ',人数:'|| v_con);--控制台输出
       end;

变量属性的类型

--变量属性的类型%type（一个字段），%rowtype（某个表所有的字段类型）
 --%type
   --sql语句
   select t.name,t.age,t.birthday from t_student t
   
   --语句块
   Declare
   
     v_name t_student.name%type;
     v_age  t_student.age%type;
     v_bir  t_student.birthday%type;
   begin
     select t.name, t.age, t.birthday
       into v_name, v_age, v_bir
       from t_student t
       where t.id = 1;
     dbms_output.put_line('名称：' || v_name || ',年龄：' || v_age || ',生日：' ||
                          to_char(v_bir,'yyyy-MM-dd'));
   end;
   
   
   --%rowtype(只能用于存放一个表的结果列，即单表查询)
    --SQL语句
     select * from t_teacher t where t.id = 1;
     
     --语句块
     Declare 
      v_tea t_teacher%rowtype;
     begin
         select * into v_tea from t_teacher t where t.id = 1;
         
         dbms_output.put_line('名称：'||v_tea.name);
       end;

循环

1、无条件循环

--循环
--无条件循环
 Declare 
  v_i number := 1;
 begin
   loop  --循环的开始
   exit when v_i > 10;
   dbms_output.put_line('第'||v_i||'的循环');
   v_i := v_i + 1;
   end loop;--循环结束 
 end;

2、for 循环

--for 循环
  --循环输出1-10 数字
  declare
   v_num number;
  begin
  for v_num in 1..10
    loop
        dbms_output.put_line(v_num);
      end loop;
  end;
  
--学校对应的班级
   select l.name lname,c.name cname from t_school l,t_class c where c.school_id = l.id
   declare
   --复合型数据类型 ——先创建数据类型
   type v_rec is record(
   sn t_school.name%type,
   cn t_class.name%type
   );
   --通过数据类型声明变量
   v_row v_rec;
   begin
     for v_row in 
       (select l.name lname,c.name cname from t_school l,t_class c where c.school_id = l.id)
     loop
         dbms_output.put_line('学校名称'||v_row.lname||'，班级名称'||v_row.cname);
       end loop;
   end;

3、while循环

 --while
     declare
     v_n number := 1; 
     begin 
       while v_n <= 10
         loop
           dbms_output.put_line(v_n);
           v_n := v_n +1;
           end loop;
       end;

分页练习

 /*
    利用语句块实现
    查询所有学生记录按照id 排序
    在输入框中输入要显示的页码，显示出该页的记录
    假设一页10条信息
    注意输入的页码不能小于1，也不能大于最大页码（需要计算）
    最后一页不够10条的只显示相应的记录数
    */ 
      
     select *
       from (select a.*, rownum nu
               from (select t.* from t_student t order by to_number(t.id)) a) b
      where b.nu >= 11
        and b.nu < = 20
        
         for v_row in (select *
       from (select a.*, rownum nu
               from (select t.* from t_student t order by to_number(t.id)) a) b
      where b.nu >= v_start
        and b.nu < = v_end) 
      
      declare
      page_size number :=10;
      page_sum number;
      v_con number; 
      i_page number := 1;
      v_start number := 1;
      v_end number :=10;
      v_name varchar2(20);
      begin
        select count(id) into v_con from t_student;
        if mod(v_con,page_size) = 0 then page_sum := v_con/page_size;
        else page_sum := round((v_con/page_size),0)+1;
        end if;
         dbms_output.put_line('总记录数'||v_con||'，可分页'||page_sum);
         i_page := &page;
         if i_page >=1 and i_page <= page_sum then
         v_start := (i_page-1)*10+1;
         v_end := v_start+page_size;
         
         if v_end>v_con then v_end :=v_con+1;
           end if;
         
         while v_start < v_end
           loop
              select b.name into v_name
       from (select a.*, rownum nu
               from (select t.* from t_student t order by to_number(t.id)) a) b
      where b.nu  = v_start;
       dbms_output.put_line('信息：'||v_name);
             v_start := v_start+1;
             end loop;
         
           else dbms_output.put_line('输入异常');
           end if;
        end;

函数function

 -- 数字补0
 --创建function
 create or replace function add_zero(i_num in number)
 return varchar2
 is
  --语句块
 --declare --创建函数时，不需要declare
   
  o_num varchar2(3);
  begin
    if i_num < 10 and i_num >= 1 then o_num := '00' || i_num;
    elsif i_num < 100 and i_num >=10 then o_num := '0' || i_num;
    else o_num := i_num;
    end if;
    dbms_output.put_line(o_num);
    return o_num;
    end;
    
    --使用函数
    select add_zero(12) from dual;

存储过程Procedure

1、没有与有返回值

 -- 是一段在数据库中执行某段特定某种特定功能的SQL语句
     /*
     优点：1、做复杂的sql操作  2、提前编译，不需要执行前编译 执行上的效率会高
          3、简化第三方调用的过程
          
     不足：1、维护难度高 2、不能重复在各个数据库中使用，兼容性差
     */
     
     --创建为一个存储过程
     create or replace procedure pro_test
     is
     -- 语句块
     --declare
     begin
       dbms_output.put_line('这里是test存储过程');
       end pro_test;
    --测试存储过程
     --没有返回值
     call pro_test();
     
     -- 2、有返回值
     declare 
     v_num number;
     begin 
       pro_test();
       end;
    
    -- 带参数的存储过程
    create or replace procedure pro_test_in (i_val in varchar2)
    is
    -- 语句块
     begin
       dbms_output.put_line('变量的值：'||i_val);
     end pro_test_in;
     
     call pro_test_in ('你好');

2、有返回值的存储过程

 --有返回值的存储过程
     
      create or replace procedure pro_test_out (o_val out varchar2)
    is
    -- 语句块
      
     begin
      o_val := '行情价';
     end pro_test_out;
     
     declare
     o_val varchar2(10);
     begin
       pro_test_out(o_val);
       dbms_output.put_line('返回值：'||o_val);
       end;

3、即返回又传参存储过程

 -- 即返回又传参存储过程
    create or replace procedure 
    pro_test_in_out (io_val in out varchar2,i_val in varchar2,o_val out varchar2)
    is
    -- 语句块
      
     begin
      o_val := '霍奇金';
      io_val := i_val|| to_char(sysdate,'yyyy-MM-dd');
     end pro_test_in_out;

存储过程与游标

1、隐式游标

/*
  %found ，从结果集中查询记录
  %notfound
  %rowcount：当前执行的sql语句中获取的记录数量
  %isopen: 游标是否已经打开
  */
    --插入一个学校的信息
     declare
     begin
        insert into t_school(id,name,address)
     values(8,'南宁市三十六中学','江南区');
     dbms_output.put_line('insert'||SQL%rowcount||'记录');
       end;    
    
    
     declare
     v_name t_student.name%type;
     v_id number := &id; -- 找不到结果会报错
     begin
       select name into v_name from t_student t where id = v_id;
       if SQL%FOUND then
       dbms_output.put_line('学生名字'||v_name);
       else
         dbms_output.put_line('无记录');
         end if;
     end;

2、显示游标 Cursor

     -- 显示游标 Cursor 
     /*
     1、把查询结果集存入到游标
     2、打开游标
     3、循环游标
     4、做判断
     5、退出游标并关闭
     */
     
     declare
        cursor stu_name is select t.name from t_student t where  t.id = &id; -- 没有结果不会报错
        v_name t_student.name%type;
     begin
        open stu_name;-- 打开游标
        fetch stu_name into v_name;-- 游标下移
        loop
          if stu_name%found then
            dbms_output.put_line('名称'||v_name);
              fetch stu_name into v_name; -- 游标下移
            elsif stu_name%notfound then
            exit; -- 退出循环
            end if;
          end loop;
          if stu_name%isopen then 
            close stu_name ; -- 关闭游标
            end if;
     end;

3、ref 游标

--  ref 游标：类似于显示游标（存储查询得到的结果）
      -- 包 Package(用于存储全局变量,可以放n个变量)
     create or replace package pck_all
     as
     type ref_cur is ref cursor;-- 定义一个ref 游标
     end pck_all;
      -- 存储过程
     create or replace procedure pro_test02(v_cur out pck_all.ref_cur)
     is
     --declare
    -- v_cur pck_all.ref_cur;
     begin 
       open v_cur for
       select t.name,t.student_no,o.chinese from t_student t,t_score o where t.student_no = o.student_no;
       end pro_test02;
     
     declare
     b_cur pck_all.ref_cur;
      v_name varchar2(30);
      sno t_student.student_no%type;
      chn number;
     begin
       pro_test02(b_cur);
      
       loop
       fetch b_cur into v_name,sno,chn;
       exit when b_cur%notfound;
       dbms_output.put_line('名称:'||v_name||'，学号:'||sno||',语文成绩：'||chn);
       end loop;
       end;
     

触发器

--触发器（trigger）:当对某一个表A的数据进行修改（insert、update、delete）操作时，
  --触发到另一些表（不含表A）的 操作过程。
  /*
  NEW：插入只有new 没有old
  OLD：删除只有old 没有new，更新new，old 都有
  */
  --新增一位老师记录,触发对所有学生的查询
  insert into t_teacher(id,name,sex,birthday)
  values(seq_teacher_id.nextval,'黄家驹123','1',date'199-06-02');
  
  select * from t_teacher
  
  delete from t_teacher where name = '黄家驹123' ;
  
  update t_teacher set name = 'khy' where id = 16;
  
  
  --触发器
  create or replace trigger tri_test
  after insert or delete or update on t_teacher
  for each row
  --语句块
  declare 
  v_stu t_student%rowtype;
  
  begin
    
    case  --相当与switch()...case
      when inserting then 
     dbms_output.put_line(:new.name);
     when deleting then
    dbms_output.put_line(:old.id);
    when updating then
      dbms_output.put_line(:old.name||','||:new.name);
      end case;
      
    -- select * from t_teacher; -- 不可对触发源进行操作
    for v_stu in (select * from t_student)
      loop
        dbms_output.put_line('名称：'||v_stu.name);
        end loop;
    end;