Oracle学习笔记

实例（Oracle Instance）

• 一个Oracle可以通过实例创建多个不同的数据库，一般不涉及多实例

• 一个 Oracle 实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。 一个数据库可以有 n 个实例。

用户

用户是Oracle管理表的基本单位
对比：MySQL管理表的基本单位是数据库
用户是在实例下建立的。不同实例可以建相同名字的用户。

表空间

表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件） 的逻辑映射。

数据文件（dbf、 ora）

数据文件是数据库的物理存储单位。 数据库的数据是存储在表空间中的， 真正是在某一个或者多个数据文件中。 而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。

注： 表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。

由于 oracle 的数据库不是普通的概念， oracle 是有用户和表空间对数据进行管理和存放的。但是表不是有表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了！

常见Oracle命令

C:\Users\Administrator>sqlplus system/orcl

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 10月 6 22:03:30 2020

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

远程连接Oracle数据库

C:\Users\Administrator>sqlplus system/itcast@192.168.80.10:1521/orcl
命令：sqlplus 用户名/密码@ip地址[:端口]/service_name [as sysdba]
1521是默认端口，/orcl是默认实例名，@192.168.80.10是服务器ip地址

-- 查询所有表
select * from tabs;

-- 查询数据库所安装的服务器的语言
select userenv('language') from dual;

--创建表空间
create tablespace waterboss

--路径必须在Oracle安装位置？？
datafile 'E:\app\Administrator\product\11.2.0\waterboss.dbf'
size 100m
autoextend on
next 10m;

--创建用户，用户必须归属一个表空间
create user wateruser
identified by orcl
default tablespace waterboss;

--给wateruser赋予dba权限，不然无法登陆
grant dba to wateruser;

黑马57期

选择某个文件，直接按T，可以找到T打头的文件

-- 查询所有表
select * from tabs;

-- 查询数据库所安装的服务器的语言
select userenv('language') from dual;

04_创建表空间创建用户以及用户授权

-- 创建表空间
create tablespace itheima
datafile 'D:\Oracle\heima\Database\itheima.dbf'--路径是服务器的位置
size 100m
autoextend on
next 10m;

-- 删除表空间
drop tablespace itheima;

-- 创建用户
create user itheima -- 用户名
identified by orcl -- 用户密码
default tablespace itheima; -- 用户所属表空间，必须指定

-- 给用户授权，才能登陆
-- Oracle常用角色
connect -- 连接用户，最基本的用户
resource -- 开发者角色
dba -- 超级管理员角色

-- 给itheima角色授予dba角色
grant dba to itheima;

05_数据类型介绍和表的创建

-- 使用itheima用户创建的表
-- 切换到itheima用户下
create table person(
       pid number(20), -- 整数20位
       pname varchar2(10) -- 可变长字符串，最大10位
);

select * from person;

drop table person;

06_修改表结构

-- #修改表结构

-- 添加一列
alter table person add gender number(1);

-- 添加多列
alter table person add (gender number(1), age number(3));

-- 修改列类型
alter table person modify gender char(1); -- 不可变长字符串

-- 修改列名称
alter table person rename column gender to sex;

-- 删除一列
alter table person drop column sex;

07_数据的增删改

-- #修改表结构

-- 添加一列
alter table person add gender number(1);

-- 添加多列
alter table person add (gender number(1), age number(3));

-- 修改列类型
alter table person modify gender char(1); -- 不可变长字符串

-- 修改列名称
alter table person rename column gender to sex;

-- 删除一列
alter table person drop column sex;

-- #数据的增删改

-- 添加一条记录
insert into person(pid, pname) values(1, '小明');
commit; -- 提交事务才会把数据写入物理文件，已提交的事务不能使用rollback

-- 修改一条记录
update person set pname = '小马' where pid = 1;
commit;

-- 3种删除表的方式
-- 删除表中所有记录
delete from person;
-- 删除表结构
drop table person;
-- 先删除表结构，在创建一张空表
truncate table person; -- 该方法在数据量大时效率高

08_序列的使用

-- 序列不真的属于一张表，但可以逻辑和表做绑定
-- 序列默认从1开始，一次递增，主要用来给主键赋值
-- dual虚表，只是为了补全select语法，没有任何意义
create sequence s_person;

-- 注意如果没有使用过 s_person.nextval，直接使用 s_person.currval 会报错：未定义
select s_person.nextval from dual;
select s_person.currval from dual;

-- 利用序列添加一条数据
insert into person(pid, pname) values(s_person.nextval, '小芳');
commit;

select * from person;

09_scott用户介绍

注意如何查询emp表的所有记录
方法：点击>>|（选择90度看看）

-- 解锁scott用户
alter user scott account unlock;

-- 解锁scott用户的密码[也可以来设置密码]
alter user scott identified by tiger;

select * from emp;

10_单行函数

-- 在scott用户下

-- 单行函数，作用于一行，返回一个值。
-- 多行函数，作用于多行，返回一个值。

-- # 字符函数
select upper('yes') from dual;-- YES，字符转成大写
select lower('YES') from dual;-- yes，字符转成小写

-- # 数值函数

-- 四舍五入
select round(26.4) from dual; -- 只有一个参数默认四舍五入为整数：26
select round(26.48, 1) from dual; -- 结果保留小数点后一位：26.5
select round(26.48, -1) from dual; -- 结果保留小数点前一位：30

-- 直接截取
select trunc(26.88) from dual; -- 只有一个参数默认截取为整数：26
select trunc(26.88, 1) from dual;-- 截取到小数点后一位：26.8
select trunc(26.88, -1) from dual;-- 截取到小数点前一位：20

-- 求余函数
select mod(10, 3) from dual; -- 10mod3 = 1
select mod(-10, 3) from dual; -- (-10)mod3 = -1

-- 日期函数
-- 系统时间：sysdate
-- 取别名：emp e
-- 查询emp表中所有员工入职距离现在几天
select sysdate-e.hiredate from emp e;-- 日期直接相减就是天数

-- 算出明天此时时间
select sysdate+1 from dual;

-- 查询emp表中所有员工入职距离现在几月
select months_between(sysdate, e.hiredate) from emp e;

-- 查询emp表中所有员工入职距离现在几年
select months_between(sysdate, e.hiredate)/12 from emp e;

-- 查询emp表中所有员工入职距离现在几周
select (sysdate-e.hiredate)/7 from emp e;

-- 结合取整函数查询emp表中所有员工入职距离现在几周
select round((sysdate-e.hiredate)/7) from emp e;

-- 转换函数
-- 日期转字符串
select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss') from dual;-- 2020-10-08 12:46:38
-- 24小时制，不固定位数
select to_char(sysdate, 'fm yyyy-mm-dd hh24:mi:ss') from dual;--  2020-10-8 12:47:59

-- 通用函数
-- 让null值可以参与运算
select e.sal*12+nvl(e.comm, 0) from emp e; -- 等同mysql的ifnull

11_条件表达式

-- 等值判断
-- 范围判断
-- 起别名可以不加引号，也可以加双引号，注意单引号不行
-- 尽可能使用通用写法

-- # 条件表达式

-- 给emp表中员工起中文名，等值判断
select e.ename,
  case e.ename
    when 'SMITH' then '史密斯'
      when 'ALLEN' then '艾伦'
        when 'JONES' then '琼斯'
          -- else '无名' -- 如果不写else就默认为null
            end 中文名 -- 也可以"中文名"
from emp e;

select e.ename
from emp e;

-- 判断emp表中员工工资，>3000为高收入，<1500为低收入，其他为中等收入
-- 给emp表中员工起中文名，范围判断
select e.sal,
  case
    when e.sal>3000 then '高收入'
      when e.sal<1500 then '低收入'
          else '中等收入' -- 如果不写else就默认为null
            end 收入情况 -- 也可以"中文名"
from emp e;

-- Oracle专用条件表达式
select e.ename,
  decode( e.ename,
     'SMITH',  '史密斯',
       'ALLEN',  '艾伦',
         'JONES',  '琼斯',
           '无名' )
             中文名
from emp e;

12_多行函数

-- count(1) = count(empno)和count(*)效果相同

-- #多行函数【聚合函数】，作用于多行，返回一个值
select count(1) from emp; --推荐使用count(1)
select sum(emp.sal) from emp; -- 查询总数量
select max(sal) from emp; -- 查询最大工资
select min(sal) from emp; -- 查询最低工资
select avg(sal) from emp; -- 查询平均工资

13_分组查询

-- where优先于select执行，所有select后起别名在having，where中都不能使用

-- 分组查询

-- 查询出每一个部门的平均工资
-- 分组查询中，出现在group by后面的原始列，才能出现在select后面
-- 没有出现在group by后面的列，必须加上聚合函数才能出现在select后面
-- 聚合函数有一个特性，可以把多行记录变成一个值
select e.deptno, avg(e.sal)
from emp e
group by e.deptno;

-- 查询出平均工资>2000的部门信息
-- 注意在select中avg(e.sal)的别名：部门平均工资，不能在where或having中使用
-- 因为where优先于select执行
select e.deptno, avg(e.sal) 部门平均工资
from emp e
group by e.deptno
having avg(e.sal)>2000; --只有having可以出现聚合函数，where不行

-- 查询出每个部门工资>800的员工的平均工资
-- 注意列名之间用逗号分隔
select e.deptno, avg(e.sal)
from emp e
where e.sal>800
group by e.deptno;

-- where：过滤分组前的数据筛选
-- having：过滤分组后的数据筛选

-- 查询出每个部门工资>800的员工的平均工资，在此基础上查询平均工资>2000的部门
select e.deptno, avg(e.sal)
from emp e
where e.sal>800
group by e.deptno
having avg(e.sal)>2000;

14_多表查询中的一些概念

-- 笛卡尔积大都是没有实际意义的
-- 等值连接，让主表的主键和从表的外键相等
-- 先有内连接，再有等值连接，推荐使用等值连接

-- 笛卡尔积
select *
from emp e, dept d;

-- 等值连接等价内连接
select *
from emp e, dept d
where e.deptno = d.deptno;

-- 内连接
select *
from emp e inner join dept d
on e.deptno = d.deptno;

-- 查询出所有部门，以及部门下的员工信息【外连接】
-- 没有员工的部门也要显示
select *
from emp e right join dept d
on e.deptno = d.deptno;

-- 查询出所有员工信息，以及员工所属部门
-- 没有部门的员工也要显示
select *
from emp e left join dept d
on e.deptno = d.deptno;

-- Oracle中专用外连接
-- 可以这样记忆，(+)代表这个表时附属的
-- 查询出所有部门，以及部门下的员工信息【外连接】
-- 没有员工的部门也要显示
select *
from emp e, dept d
where e.deptno(+) = d.deptno;

-- 查询出所有员工信息，以及员工所属部门
-- 没有部门的员工也要显示
select *
from emp e, dept d
where e.deptno = d.deptno(+);

15_自连接概念和练习

-- 查询员工姓名和员工领导姓名
-- 自连接：站在不同角度，把一张表看出多张表
select e1.ename, e2.ename
from emp e1, emp e2
where e1.mgr = e2.empno;-- e1表中的领导是e2表的员工，以此区分哪个是员工表，哪个是部门表

-- 查询员工姓名，员工部门名称，和员工领导姓名，员工领导部门名称
select e1.ename 员工姓名, d1.dname 员工所属部门名称, e2.ename 员工领导姓名, d2.dname 员工领导所属部门名称
from emp e1, emp e2, dept d1, dept d2
where e1.mgr = e2.empno
and e1.deptno = d1.deptno
and e2.deptno = d2.deptno;

-- 分析询员工姓名，员工部门名称，和员工领导姓名，员工领导部门名称的错误写法
select e1.ename 员工姓名, d.dname 员工所属部门名称, e2.ename 员工领导姓名, d.dname 员工领导所属部门名称
from emp e1, emp e2, dept d
where e1.mgr = e2.empno
and e1.deptno = d.deptno
and e2.deptno = d.deptno;
-- 由于只使用了一张d表，e1.deptno = d.deptno and e2.deptno = d.deptno表明了员工和领导在同一个部门

-- 查询出每个员工编号，姓名，部门名称，工资等级和他的上级领导的姓名，工资等级
select e.empno,
       e.ename,
       decode(s.grade,
        1,'一级',
        2,'二级',
        3,'三级',
        4,'四级',
        5,'五级') grade,
        d.dname,
        e1.empno,
        e1.ename,
        decode(s1.grade,
        1,'一级',
        2,'二级',
        3,'三级',
        4,'四级',
        5,'五级') grade
from emp e, emp e1, dept d, salgrade s, salgrade s1
where e.mgr = e1.empno
and e.deptno = d.deptno
and e.sal between s.losal and s.hisal
and e1.sal between s1.losal and s1.hisal;

16_子查询

-- e1表中的领导是e2表的员工，以此区分哪个是员工表，哪个是部门表
-- ename='SCOTT'是区分大小写的

-- 子查询

-- 子查询返回一个值

-- 查询出工资和SCOTT一样的员工信息
select *
from emp
where emp.sal in(
     select emp.sal
     from emp
     where emp.ename = 'SCOTT'
);

-- 子查询返回一个集合
-- 查询出工资和10部门任意员工一样的员工信息
select *
from emp
where emp.sal in(
      select emp.sal
      from emp
      where emp.deptno = 10
);

-- 子查询返回一张表
-- 查询每个部门的最低工资，和最低工资员工姓名，和该员工所属部门名称

-- step1 先查出每个部门的最低工资
select emp.deptno, min(emp.sal) minSal
      from emp
      group by emp.deptno;

-- step2 三表联查
select t.minSal 最低工资, e.ename 最低工资员工姓名, d.dname 员工所属部门名称
from (select emp.deptno, min(emp.sal) minSal
      from emp
      group by emp.deptno) t,
      emp e,
      dept d
where t.deptno = e.deptno -- 最低工资表中的部门编号 = 员工表的部门编号
and t.minSal = e.sal -- 筛选员工表
and t.deptno = d.deptno -- 和部门表主键关联

17_分页查询

-- Oracle中的分页查询
-- rownum行号：每当我们做select操作的时候
-- 每查询出一行结果，就会在该行加上一个行号
-- 行号从1开始，依次递增，不能跳跃增长
-- emp表倒序排序后，每页显示5条记录，查询第2页（6~10）

-- 注意rownum不属于任意一张表
-- 由于select先于order by执行，所以rownum顺序被排序打乱了
select rownum, e.*
from emp e
order by e.sal desc;

-- 解决方法：对排序好的表再select一次（嵌套查询）
select rownum, t.*
from( select rownum, e.*
      from emp e
      order by e.sal desc) t;
      
-- emp表倒序排序后，每页显示5条记录，查询第2页（6~10）
-- 准则：where不能对当前的rownum加大于0的判断，不然就查不到东西，rownum加不上
-- 原因：先执行where，rownum=1，但是不满足rownum>0，所以行号加不上，所以select查不到东西
-- 解决方法：再套一层select，使用内层的rownum进行判断
select *
from( select rownum mid_rownum, t.*
      from( select *
            from emp
            order by emp.sal desc)t
      where rownum < 11)
where mid_rownum > 5;

01_视图

-- 视图
-- 概念：提供一个查询的窗口，所有数据来自原表
-- 这意味着改变视图数据，就是改变原表数据

-- 查询语句来创建表
-- 可以复制其他用户的表进行建表
create table emp as select * from scott.emp;

-- 创建视图必须有dba权限
create view v_emp as select ename, job from emp;

-- 查询视图
select * from v_emp;

-- 修改视图【不推荐】，原表也会被修改
-- 字符只有使用单引号
update v_emp set job = 'CLERK' where ename='ALLEN';
commit;--注意修改后要commit

-- 创建只读视图
create view v_emp2 as select ename, job from emp with read only;

-- 视图的作用
-- 1. 屏蔽一些敏感字段，如员工工资
-- 2. 保证总部和分部数据及时同一，分部使用虚表就会及时同步实表数据

02_索引

-- 索引
-- 概念：索引就是在表的列上构造一颗二叉树，加快查询速度，但也插入或删除时需要调整树，增加了时间
-- 作用：大幅提高查询效率
-- 缺点：影响增删改效率

-- 单列索引

-- 创建单列索引
create index idx_ename on emp(ename);

-- 单行索引触发规则：条件必须是索引列中的原始值
-- 单行函数，模糊查询都会影响索引的触发
select * from emp where emp.ename = 'SCOTT';

-- 复合索引
-- 复合索引中第一列为优先检索列
create index idx_enamejob on emp(ename, job);

-- 复合索引触发规则：条件必须是必须包含有优先索引的原始值
select * from emp where ename = 'SCOTT' and job = 'xx'; -- 触发复合索引
select * from emp where ename = 'SCOTT' or job = 'xx'; -- 不触发索引
select * from emp where ename = 'SCOTT'; -- 触发单列索引

03_plsql编程语言定义变量

-- pl/sql 编程语言
-- 概念：对sql语言的扩展，使sql语言具有过程化编程的特性
-- 作用：主要来编写存储过程和存储函数

-- 声明方法，类似于main方法
-- 赋值方法1：:=
-- 赋值方法2：select ename into ena from emp e where e.empno = 7788;
-- dbms_output.put_line连接多个变量使用||
declare
   i number(2) := 10; -- 定义变量
   s varchar2(10) := '小明';
   ena emp.ename%type; -- 引用emp的ename的数据类型，引用型变量
   emprow emp%rowtype; -- 可以存储emp表的一条记录，记录型变量
begin
  dbms_output.put_line(i);
  dbms_output.put_line(s);
  select ename into ena from emp e where e.empno = 7788;
  dbms_output.put_line(ena);
  select * into emprow from emp e where e.empno = 7788;
  dbms_output.put_line(emprow.ename || '的工作为：' || emprow.job);
end;

04_plsql中的if判断

-- pl/sql 的if判断
-- elsif可以省略，else也可以省略
-- 注意end if不能省略
-- 接收变量的方法i number(3) := ⅈ
declare
   i number(3) := ⅈ -- &ii也可以写成i，任意变量名
begin
  if i < 18 then
    dbms_output.put_line('未成年');
  elsif i < 40 then
    dbms_output.put_line('中年人');
  else
    dbms_output.put_line('老年人');
  end if; -- 不要漏了
end;

05_plsql中的循环

-- pl/sql的三种循环

-- while

declare
   i number(2) := 1;
begin
  while i < 11 loop
    dbms_output.put_line(i);
    i := i+1;
  end loop; -- 有loop就要end loop
end;


-- exit 最常使用
declare
   i number(2) := 1;
begin
  loop
     exit when i > 20;
     dbms_output.put_line(i);
     i := i+1;
  end loop;
end;

-- for
declare

begin
   for i in 10..20 loop
     dbms_output.put_line(i);
   end loop;
end;

06_plsql中的游标

-- 游标
-- 概念：可以存放多行记录

-- 需求：输出emp表中所有员工姓名
declare
   cursor c1 is select * from emp;
   emprow emp%rowtype;
begin
  open c1;
       loop
         fetch c1 into emprow;
         exit when c1%notfound;
         dbms_output.put_line(emprow.ename);
       end loop;
  close c1; --忘了写，但没有报错
end;

-- 需求：给指定部门的员工涨工资
declare
   cursor c2(eno emp.deptno%type)
   is select empno from emp where deptno = eno;
   en emp.empno%type;
begin
  open c2(10);
       loop
             fetch c2 into en;
             exit when c2%notfound;
             update emp set sal = sal+100 where empno = en;
             commit;-- 最好写上
       end loop;
  close c2;
end;

-- 对比
select ename, sal from emp where deptno = 10;

07_存储过程的使用

-- 存储过程
-- 概念：提前编译好并放在数据库的一段pl/sql语句
       -- 所有可以在Procedures可看到
-- 作用：可以直接调用，一般存储过程都是固定步骤的业务

-- 给员工编号为eno的员工涨100块
create or replace procedure p1(eno emp.empno%type)
is

begin
  update emp set emp.sal = emp.sal+100 where emp.empno = eno;
  commit;
end;

-- 测试p1

-- 查询前后结果
select * from emp where emp.empno = 7788;

-- 测试
declare

begin
  p1(7788);
end;

08_存储函数

-- 存储函数，也是预编译存储在数据库中
-- 概念：有2个return
-- 对比：存储过程和存储函数的参数都不能带长度
     -- 存储函数的返回值类型不能带长度
-- in和out类型参数的区别：
-- 凡是涉及into查询语句赋值或 := 赋值操作的参数，都必须是out
-- 原因：

-- 通过存储函数实现计算指定编号的员工的年薪

create or replace function f_yearsal(eno emp.empno%type) return number -- 注意不能加长度
is
  s number(10);
begin
  select emp.sal*12+nvl(emp.comm, 0) into s from emp where emp.empno = eno;
  return s;
end;

-- 测试f_yearsal
declare
   s number(10);
begin
  s := f_yearsal(7788); -- 有返回值一定要接收
  dbms_output.put_line(s);
end;

09_out类型参数的讲解

-- out类型参数如何使用
-- 使用存储过程来计算员工年薪
create or replace procedure p_yearsal(eno emp.empno%type, yearsal out number)
is
  s number(10);
  c emp.comm%type; 
begin
  select emp.sal*12, nvl(comm, 0) into s, c
  from emp
  where emp.empno = eno;
  yearsal := s+c; -- 没有返回值直接使用out类型参数接收
end;

-- 测试p_yearsal
declare
   yearsal number(10);
begin
  p_yearsal(7788, yearsal);
  dbms_output.put_line(yearsal);
end;

10_存储函数和存储过程的区别

-- 存储过程和存储函数的区别
-- 语法区别：关键字procedure, function
       -- ：function多了2个return
       --：函数可以返回值，过程必须依靠out参数实现

-- 可以使用存储函数来自定义函数

-- 案例：查询员工姓名和所在部门名称

-- 准备：把scott用户的dept表复制到但前用户下
create table dept as select * from scott.dept;

-- 普通方法
select e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno;

-- 调用自定义函数实现

-- 自定义函数：传入部门编号，返回部门名称
create or replace function fdna(dno dept.deptno%type) return dept.dname%type
is
  s dept.dname%type;
begin
  select dname
  into s
  from dept d
  where d.deptno = dno;
  return s;
end;

-- 调用函数实现需求
select e.ename, fdna(e.deptno)
from emp e;

课本

• Create - INSERT

• Read - SELECT

• Update - UPDATE

• Delete - DELETE

SELECT

General Form

SELECT [DISTINCT] <Column 1>, <Column 2>, …
FROM table(s)
[WHERE predicate]
[GROUP BY field(s) [HAVING predicate]]
[ORDER BY field(s)];

不等于Not equal to

SELECT *
FROM SUPPLIERS
WHERE SUPPLIERS.City<>'Paris'

Oracle不支持的日期使用

SELECT *
FROM Orders
WHERE OrderDate > #4/20/2003#

起别名column Alias

复杂的where条件

-- 复杂的where条件
SELECT OrderID, OrderDate, RequiredDate
FROM Orders
WHERE EmployeeID = 5
AND ShipCountry = 'USA'
AND (CustomerID = 'SAVEA' OR CustomerID = 'RATTC')
AND Freight > 50

IN()

通用形式

test_expression [NOT] IN (value1, value2,…valueN)

SELECT OrderID, OrderDate, RequiredDate
FROM Orders
WHERE EmployeeID = 5
AND ShipCountry = 'USA'
AND CustomerID IN ('SAVEA','RATTC')
AND Freight > 50

in里面是select语句

SELECT OrderID, OrderDate, RequiredDate
FROM Orders
WHERE EmployeeID = 5
AND ShipCountry = 'USA'
AND CustomerID IN (SELECT CustomerID
FROM Customers
WHERE Country = 'USA' )
AND Freight > 50

BETWEEN

通用形式

test_expression [NOT] BETWEEN begin_value AND end_value

范例1

SELECT EmployeeID, LastName, FirstName, Dept
FROM Employees
WHERE FirstName between 'L' and 'N'

like

通用形式

match_expression [NOT] LIKE pattern

范例1

-- % or * represents any character or string
SELECT EmployeeID, LastName, FirstName, Dept, Title
FROM Employees
WHERE Title Like '*Representative*'

特殊符号含义

Wildcard character	Description
% (or *)	Any string of zero or more characters.
_ (underscore) (or ?)	Any single character.
[ ]	Any single character within the specified range ([a-f]) or set ([abcdef]).
[^]	Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

NULL

通用形式

IS [NOT] NULL

范例1

SELECT *
FROM Employees
WHERE ReportsTo Is NULL

ORDER BY

通用形式

-- ORDER BY can sort on multiple columns. ASC -> ascending; DESC -> descending
ORDER BY { order_by_expression [ ASC | DESC ]

范例1

-- Two or more columns may also be used for sorting purposes
-- To sort the Country in descending order, and then to sort CompanyName under the same Country
Select Country, CompanyName, City
From Customers
Order By Country DESC, CompanyName ASC;

聚合函数 Built-in Functions

SQL includes five built-in functions: COUNT, SUM, AVG, MAX, MIN

Aggregate functions return a single value.

With the exception of COUNT, aggregate functions ignore NULLs.

范例1：Count

-- Display how many Employees are in the company.
SELECT COUNT(*)
FROM Employees; -- answer = 9
-- Display The number of orders placed by a specific CustomerID “HANAR” .
SELECT COUNT(*) AS NumberOfOrders
FROM Orders
WHERE CustomerID = ‘HANAR’

How to use “Distinct”

The SQL DISTINCT command used along with the SELECT keyword
retrieves only unique data record depending on the column list you
have specified after it.

范例1

-- Display all the Countries are in Customers table and don’t show redundancy
SELECT DISTINCT Country
FROM Customers;

范例2 count和distinct的配合查询，有多少条不重复的记录

-- Use “Count” and “Distinct” to calculate how many distinct records in the table
SELECT Count(DISTINCT Country)
FROM Customers;

How to use function “Sum”

范例1

-- What is the total sales for a specific product (ID=11)?
SELECT SUM(UnitPrice)
FROM [Order Details]
WHERE ProductID= 11 ;

为什么加[]的原因？ 表名有空格

Due to the table name “Order Details” has a space character between “Order” and “Detail”, we need to use a bracket symbol [] to enclose it

How to use “Average”

-- Display the average unit price of all Products.
SELECT AVG(UnitPrice) as AverageUnitPrice
FROM Products;
-- Display The average units in Stock of all Products.
SELECT AVG(UnitInStock) as AverageUnitInStock
FROM Products;

How to use “MAX” and “MIN”

范例

-- What is the highest and lowest UnitPrice in the Products table?
-- 起别名可以省略as
select max(UnitPrice) HighestPrice, min(UnitPrice) LowestPrice
from Products

-- What are the oldest employee’s birthday and youngest emploee’s birthday in table “LargeEmployees”? Please find these.
-- 可以作用在日期类型上
...

How to use “Group By”

范例

-- 这个是通过oder by发现有很多相同的GateGoryID 省略就是升序
select CategoryID, ProductID
from Products
order by GateGoryID, ProductID;
-- What is the number of employees in each City?
select City, Count(*) as number
from LargeEmployees
group by City;
-- What is the number of employees in each City in USA?
select City, Count(*) as number
from LargeEmployees
Where Country = 'USA'
group by City;
-- Count the number of products in each category in the Products table.
-- 先分组再排序
slect CategoryID, Count(*) as NumberOfProducts
from Products
Group By CategoryID
Order by NumberOfProducts DESC
-- Count the “average” unit price of products on each supplier.
...

How to use “Having”

The HAVING clause specifies a search condition for a group or an aggregate.

• HAVING is usually used with the GROUP BY clause.
• When GROUP BY is not used, HAVING behaves like a WHERE clause.

范例

-- What is the number of employees in each City whose must be larger than 20?
Select City, Count(*) as number
From LargeEmployees
Group by City
Having Count(*)>=20 -- 分组后在筛选
-- Count the “average” unit price of products on each supplier whose “average” unit price of products on each supplier must be larger than 40?
-- Group By前面的字段只能是Group By的字段或聚合函数
Select SupplierID, AVG(UnitPrice) as AverageUnitPrice
From Products
Group by SupplierID
Having AVG(UnitPrice)>=40

Multi-table queries

范例

-- 范例1
Select Employees.EmployeeID,
	Employees.FirstName, Employees.Dept,
	Department.DeptmentName
From Employees, Department
Where Employees.Dept=Department.Dept
-- 范例2 表名带空格
Select Orders.OrderID, Orders.OrderDate,
	[Order Details].ProductID, [Order Details].UnitPrice,
	[Order Details].Quantity
From Orders, [Order Details]
Where Orders.OrderID = [Order Details].OrderID -- Join condition
	and Orders.OrderDate #7/3/2003# -- match condition
-- 范例3 (JOIN) 3 tables
Select Orders.OrderID, Orders.OrderDate,
    [Order Details].ProductID, Products.ProductName,
    Products.Cost , [Order Details].UnitPrice,
    [Order Details].Quantity
From Orders, [Order Details],Products
Where Orders.OrderID=[Order Details].OrderID
    and [Order Details].ProductID=Products.ProductID
    Order By Orders.OrderID, [Order Details].ProductID
-- 范例4 joining table to itself 自关联
-- 通过给表起2个不同的别名实现
SELECT Employee.EmployeeID AS EmployeeID,
Employee.FirstName AS EmployeeName,
Employee.Title AS EmployeeTitle, Supervisor.EmployeeID AS SupervisorID,
Supervisor.FirstName AS SupervisorName, Supervisor.Title AS SupervisorTitle
FROM Employees AS Employee, Employees AS Supervisor
WHERE Employee.ReportsTo=Supervisor.EmployeeID

Sub-Queries

General structure of uncorrelated sub-query

SELECT Item
FROM table1 -- (Outer Query)
WHERE Item IN [NOT IN]
    (SELECT Item -- (Inner Query)
    FROM table2
    WHERE predicate);

范例

-- 范例1
-- How to retrieve the company’s information of suppliers, the product’s unit price of which is grater than $100.
-- Step 1: do the inner select first
-- Step 2: do the outer select, and search condition is according to the inner select’s results
SELECT CompanyName, ContactName, Phone
FROM Suppliers
Where SupplierID IN
    (SELECT SupplierID
    FROM Products
    WHERE UnitPrice > 100);
-- 范例2
-- How to retrieve the Employees’ information who took the orders within 2003/4/1~2003/4/2.
-- Step 1: do the inner select first
-- Step 2: do the outer select, and search condition is according to the inner select’s results
SELECT FirstName, LastName, Dept, Notes
FROM Employees
Where EmployeeID IN
    (Select Distinct EmployeeID
    From Orders
    Where OrderDate between
    #4/1/2003# and #4/2/2003#);

Data Definition Language

CREATE, DROP, ALTER

通用形式

CREATE TABLE TableName
(AttributeName Domain [Default][Constraint]
[, AttributeName Domain ...]
[other constraints])

• Domain :set of values an attribute can have
• SQL provides six “families” of elementary domains
  • – Character (strings)
  • – Bit (0,1)
  • – Exact Numeric (integer, smallint, decimal ($))
  • – Approximate numeric (floating point)
  • – Date and Time
  • – Temporal intervals

范例

-- 范例1
CREATE TABLE Customer
(CustomerID char(5) primary key,
CustomerName char(50) not null,
SocialSecurity char(9) not null unique,
Status char(10) Default ‘Active’);
-- 范例2
-- Compound primary key 联合主键
CREATE TABLE Customer
(FirstName char(20),
LastName char(20) not null,
SocialSecurity Char(9) not null unique,
Status char(10) Default ‘Active’
primary key (FirstName, LastName));
-- 删除表
DROP TABLE Customer;
-- Alter语句
ALTER TABLE TableName
alter column AttributeName
add constraint
drop constraint
add column
drop column;

Data Manipulation Language

UPDATE, INSERT, DELETE

Update

General structure of UPDATE syntax

UPDATE table
SET attribute = expression [,attribute = expression]
WHERE predicate;

OR

UPDATE table
SET attribute = expression [,attribute = expression]
subquery; -- 子查询
[Optional parameters]

insert

INSERT
INTO table [(attribute1, attribute2,...)]
VALUES(constant, constant,...);

delete

-- 模板1
DELETE
FROM table
[WHERE predicate]

-- 范例
delete from customer1
where Status is NULL;

OR

-- 模板2
DELETE
FROM table
[subquery];

-- 范例
delete from Customer1
where customerID in
(
    select customerID
    from CuSTOMERS
    where CITY = 'Madrid'
);

[Optional parameters]

使用 To_date 函数

想在新增一笔数据时，指定需要的日期，可以使
用 to_date 函数，例如 ， 我 们 想 指 定 日 期 为
92 年 2 月 5 日 ， 则 是 使 用 to_date
(‘05/02/92’,‘dd/mm/yy’)

ER-MODEL 转成关联表格(CONSTRAINTS)

check

-- CHECK 查核
-- 表内加约束，但是起约束名
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255),
    CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);

-- 表建后再加约束
ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

-- 不起名的check约束
CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age>=18)
);

-- 删除check约束
ALTER TABLE Persons
DROP CHECK CHK_PersonAge;

Foreign Key 外键

范例

-- 范例1
CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
     -- 在创建表时，创建没有约束名的外键
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

-- 范例2
CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    -- 在创建表时，创建有约束名的外键
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID)
);

-- 删除外键约束方法1
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

-- 删除外键约束方法2
ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;

-- 启动约束Constraint
alter table depart
enable constraint depart_empid_fk;

-- 关闭约束Constraint
alter table depart
disable constraint depart_empid_fk; -- 暂时不用Constraint，可以不用删除，disable即可

父表和子表

当父表（被参考的表格）数据不存在时，我们无法在子表（参考表格）新增数据，因为没有可以参考到的值（在父表），我们以 Depart参考到EMP 为范例，直接在 Depart 表格新增一笔参考不到 EMP 的资料，会插入失败

-- 父表
CREATE TABLE supplier(
    supplier_id numeric(10) not null,
    supplier_name varchar2(50) not null,
    contact_name varchar2(50),
    CONSTRAINT supplier_pk PRIMARY KEY (supplier_id));

-- 子表
CREATE TABLE products( 
    product_id numeric(10) not null,
    supplier_id numeric(10) not null,
    CONSTRAINT fk_supplier FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id));
    
Delete from supplier where supplier_id =‘s01’
-- 若上述DML若删除失败，则代表products表中还有纪录有参考到’s01’，在参考限
-- 制下的数据保护，会不准删除，以避免在products表中有”资料孤儿”的资料。
Delete from supplier where supplier_id =‘s06’
-- 上述DML若删除成功，主要原因在于supplier中的supplier_id=s06这笔纪录尚未被
-- products参考到，因而没有违反参考性限制，不会不准删除。

解决方法

-- 解決方法1: 限制性做法(RESTRICTED —— restricted)
先刪除products中有關s01的紀錄
之後再刪除suppliers中， s01的紀錄
在刪除過程中，不會造成因參考完整性而刪除失敗

-- 解決方法2:連鎖反應的做法(CASCADES —— cascades)
使用Delete On Cascades的reference選項，將牽涉s01的記錄的兩個表，同時刪除
在刪除過程中，不會造成因參考完整性而刪除失敗

-- 解決方法3:虛值化做法(NULLIFIES)
在刪除suppliers的s01紀錄之前，會將products的有關s01的紀錄改為Null值(使用On Delete Set Null)
此方法的優點在於可以保留p01,p02紀錄，以待後續p01,p02可設定給別的供應商

參考性限制的解決方案(级联刪除、级联更新)

-- 此法在刪除supplier表的紀錄時，會一併將參考到此紀錄的products表，也一併刪除
CREATE TABLE supplier( 
    supplier_id numeric(10) not null,
    supplier_name varchar2(50) not null,
    contact_name varchar2(50),
    CONSTRAINT supplier_pk PRIMARY KEY (supplier_id));

CREATE TABLE products( 
    product_id numeric(10) not null,
    supplier_id numeric(10) not null,
    CONSTRAINT fk_supplier FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id) ON DELETE CASCADE);
    
-- 此法在刪除supplier表的紀錄時，會一併將參考到此紀錄的products表之參考欄位改成NULL。
-- 此法的好處在於保留products資料，以後還可將此products資料參考到別的supplier紀錄
CREATE TABLE supplier( 
    supplier_id numeric(10) not null,
    supplier_name varchar2(50) not null,
    contact_name varchar2(50),
    CONSTRAINT supplier_pk PRIMARY KEY (supplier_id));
    
CREATE TABLE products( 
    product_id numeric(10) not null,
    supplier_id numeric(10) not null,
    CONSTRAINT fk_supplier FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id) ON DELETE SET NULL);
    
-- 此法在更新supplier表中supplier的紀錄時，會將參考到此紀錄的products表一併修改。
CREATE TABLE supplier( 
    supplier_id numeric(10) not null,
    supplier_name varchar2(50) not null,
    contact_name varchar2(50),
    CONSTRAINT supplier_pk PRIMARY KEY (supplier_id));
    
CREATE TABLE products( 
    product_id numeric(10) not null,
    supplier_id numeric(10) not null,
    CONSTRAINT fk_supplier FOREIGN KEY (supplier_id)
    REFERENCES supplier(supplier_id) ON DELETE CASCADE ON UPDATE CASCADE);

第五部分 Oracle PL/SQL编程

选择语句

1．if…then语句
2．If…then…else语句
3．if…then…elsif语句
4．case语句

循环语句

1．loop语句
loop
plsql_sentence;
exit when end_condition_ exp
end loop;

2．while语句
while condition_expression loop
plsql_sentence;
end loop;

3．for语句
for variable_ counter_name in [reverse]
lower_limit..upper_limit loop
plsql_sentence;
end loop;

示例

declare
	var_name1 varchar2(50);
	vat_name2 varchar2(50);
begin
	var_name1:='East';
	var_name2:='xiaoke';
	if length(var_name1) < length(var_name2) then
		dbms_output.put_line('字符串"'||var_name1||'"的长度比字符串"'||var_name2||'"的长度小');
		
-- case判断
declare
	season int:=3;
	aboutInfo varchar2(50);
begin
	case season
		when 1 then
			aboutInfo:=season||'季度包含1,2,3月份';
		when 2 then
			aboutInfo:=season||'季度包含4,5,6月份';
		when 3 then
			aboutInfo:=season||'季度包含7,8,9月份';
		when 4 then
			aboutInfo:=season||'季度包含10,11,12月份';
		else
			aboutInfo:=season||'季度不合法';
	end case;
	dbms_output.put_line(aboutInfo);
end;

-- Write a simple code to sum two numbers Click the icon or F8 to execute the code.
Declare
v_Result Number;
-- Declare a variable of type Number with value of 50
v_a Number := 50;
-- Declare a variable of type Number with value 100
v_b Number := 100;
Begin
-- Print out Console Screen
Dbms_Output.Put_Line('v_a= ' || v_a);
-- Print out Console Screen
Dbms_Output.Put_Line('v_b= ' || v_b);
-- Sum
v_Result := v_a + v_b;
-- Print out Console Screen
Dbms_Output.Put_Line('v_Result= ' || v_Result);
End;

type data type

-- %type data type
Declare
v_Emp_Id Employee.Emp_Id%Type := 1;
v_First_Name Employee.First_Name%Type; -- 参照Employee.First_Name的数据形态
Begin
Select Emp.First_Name
Into v_First_Name
From Employee Emp
Where Emp.Emp_Id = v_Emp_Id;
----
Dbms_Output.Put_Line('v_First_Name= ' || v_First_Name);
Exception
When No_Data_Found Then
-- When SELECT .. INTO returns nothing.
Dbms_Output.Put_Line('No data with emp_id= ' || v_Emp_Id);
End;

Rowtype data type

-- %Rowtype data type
Declare
v_Emp_Id Employee.Emp_Id%Type := 1;
-- Declare a variable
-- Is row type of Employee table.
v_Emp Employee%Rowtype;-- 參考原始Employee的原始欄位資料型態
Begin
Select * Into v_Emp From Employee Emp Where Emp.Emp_Id = v_Emp_Id;
----
Dbms_Output.Put_Line(' First_Name= ' || v_Emp.First_Name);
Dbms_Output.Put_Line(' Last_Name= ' || v_Emp.Last_Name);
Exception
When No_Data_Found Then
-- When SELECT .. INTO statement returns nothing.
Dbms_Output.Put_Line('No data with emp_id= ' || v_Emp_Id);
End;

record data type

-- record data type
v_Emp_Id Employee.Emp_Id%Type := 1;
-- Define Record data type has 3 column.
Type Emp_Name_Type Is Record(
Emp_First_Name Employee.First_Name%Type
,Emp_Last_Name Employee.Last_Name%Type
,Emp_Full_Name Varchar2(50));
----
-- Define a variable of type Emp_Name_Type
v_Emp Emp_Name_Type;
Begin
Select Emp.First_Name
,Emp.Last_Name
,Emp.First_Name || ' ' || Emp.Last_Name
Into v_Emp
From Employee Emp
Where Emp.Emp_Id = v_Emp_Id;
----
Dbms_Output.Put_Line(' First_Name= ' || v_Emp.Emp_First_Name);
Dbms_Output.Put_Line(' Last_Name= ' || v_Emp.Emp_Last_Name);
Dbms_Output.Put_Line(' Full_Name= ' || v_Emp.Emp_Full_Name);
Exception
When No_Data_Found Then
-- When SELECT .. INTO statement return nothing.
Dbms_Output.Put_Line('No data with emp_id= ' || v_Emp_Id);
End;

table data type

-- table data type(选学)
Declare
-- Define TABLE data type:
Type My_Tbl Is Table Of Varchar2(50) Index By Binary_Integer;
-- Define varable of type My_Tbl.
v_Emps My_Tbl;
Begin
v_Emps(1) := 'One';
v_Emps(2) := 'Two';
v_Emps(3) := 'Three';
----
Dbms_Output.Put_Line('Element Count = ' || v_Emps.Count);
---
For i In v_Emps.First .. v_Emps.Last Loop
Dbms_Output.Put_Line('Element at ' || i || ' = ' || v_Emps(i));
End Loop;
End;


Declare
-- Define TABLE data type.
Type t_City_Type Is Table Of Varchar2(30) Index By Binary_Integer;
-- Declare a variable of type T_City_Type.
t_City t_City_Type;
v_City_Index Binary_Integer;
v_e Boolean;
Begin
t_City(100) := 'Chicago'; t_City(101) := 'Chicago';
t_City(200) := 'Hanoi'; t_City(301) := 'Tokyo';
Begin
-- Check exists element at index 500.
v_e := t_City.Exists(500);
If v_e Then
Dbms_Output.Put_Line('Exists element At 500');
Else
Dbms_Output.Put_Line('Not Exists element At 500');
End If;
--
-- Delete element at index 101
t_City.Delete(101);
--
-- First index
v_City_Index := t_City.First;
Dbms_Output.Put_Line('First= ' || v_City_Index);
--
Loop
Dbms_Output.Put_Line('Element at ' || v_City_Index || ' = ' ||
t_City(v_City_Index));
Exit When v_City_Index = t_City.Last;
-- Returns the index of the next row in the table after the specified row.
v_City_Index := t_City.Next(v_City_Index);
End Loop;
-- Raise No_Data_Found exception
Raise No_Data_Found;
Exception
When No_Data_Found Then
Dbms_Output.Put_Line('the Last City Has Been Reached.');
End;
End;

array data type

Declare
-- Define Array data type.
-- containing data type of VARCHAR2 (50)
Type Emp_Array Is Varray(5) Of Varchar2(50);
-- Define Array data type, containing data type of Integer
Type Salary_Array Is Varray(5) Of Integer;
--- Array type須先宣告才能指定
v_Names Emp_Array;
v_Salaries Salary_Array;
v_Count Integer;
Begin
-- Initialize the value of array elements.
v_Names := Emp_Array('KING','JONES','FORD','SMITH','BLAKE');
-- Initialize the value of array elements.
v_Salaries := Salary_Array(5000,2975,3000,800,2850);
-- Element count.
v_Count := v_Names.Count;
Dbms_Output.Put_Line('Count = ' || v_Count);
---
For i In 1 .. v_Count Loop
Dbms_Output.Put_Line('Employee = ' || v_Names(i) || ', Salary = ' ||
v_Salaries(i));
End Loop;
End;

ORACLE常用SQL语句(参考)

1、创建数据库
CREATE DATABASE database-name

2、删除数据库
drop database dbname

3、创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

4、根据已有的表创建新表
A：select * into table_new from table_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only<仅适用于Oracle>

5、删除表
drop table tablename

6、说明：增加一个字段，删除一个字段
A：alter table tabname add column_name column_type
B：alter table tabname drop column_name

7、添加主键： Alter table tabname add primary key(col)
删除主键： Alter table tabname drop primary key(col)

8、创建索引：create [unique] index idxname on tabname(col….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。

9、创建视图：create view viewname as select statement
删除视图：drop view viewname

10、基本的sql语句
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’ %value1%’ ---like非精确查询
排序：select * from table1 order by field1,field2 [desc]
总数：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1

11、使用外连接
A、left （outer） join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B：right （outer） join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full/cross （outer） join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

12、分组:Group by:
一张表，一旦分组完成后，查询后只能得到组相关的信息。
组相关的信息：（统计信息） count,sum,max,min,avg 分组的标准)
在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据
在selecte统计函数中的字段，不能和普通的字段放在一起；

Comparison operators are used in the WHERE clause to determine which records to select. Here is a list of the comparison operators that you can use in Oracle/PLSQL:

Comparison Operator	Description
=	Equal
<>	Not Equal
!=	Not Equal
>	Greater Than
>=	Greater Than or Equal
<	Less Than
<=	Less Than or Equal
IN ( )	Matches a value in a list
NOT	Negates a condition
BETWEEN	Within a range (inclusive)
IS NULL	NULL value
IS NOT NULL	Non-NULL value
LIKE	Pattern matching with % and _
REGEXP_LIKE	Pattern matching with regular expressions
EXISTS	Condition is met if subquery returns at least one row

一些实例

CREATE TABLE customers
( customer_id number(10) NOT NULL,
customer_name varchar2(50) NOT NULL,
address varchar2(50),
city varchar2(50),
state varchar2(25),
zip_code varchar2(10),
CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

-- 创建表时，创建自定义约束名的主键和外键
CREATE TABLE employees
( employee_number number(10) NOT NULL,
employee_name varchar2(50) NOT NULL,
department_id number(10),
salary number(6),
CONSTRAINT employees_pk PRIMARY KEY (employee_number),
CONSTRAINT fk_departments FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);

-- 创建视图
CREATE VIEW sup_orders AS
SELECT suppliers.supplier_id, orders.quantity, orders.price
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
WHERE suppliers.supplier_name = 'Microsoft';

-- 删除视图
DROP VIEW sup_orders ;

-- 创建序列sequence
CREATE SEQUENCE supplier_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20; -- 缓冲大小

-- 使用序列
INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.NEXTVAL, 'Kraft Foods'); -- 通过.nextVal获取下一个值

-- 删除序列
DROP SEQUENCE sequence_name;

-- 去除重复列distinct
SELECT DISTINCT expressions
FROM tables
[WHERE conditions];

SELECT DISTINCT city, state
FROM customers
WHERE total_orders > 10
ORDER BY city;

-- 使用between and 简化SQL语句
SELECT *
FROM customers
WHERE customer_id BETWEEN 4000 AND 4999;

SELECT *
FROM customers
WHERE customer_id >= 4000
AND customer_id <= 4999;

-- between and 可以与日期类型
SELECT *
FROM order_details
WHERE order_date BETWEEN TO_DATE ('2014/02/01', 'yyyy/mm/dd')
AND TO_DATE ('2014/02/28', 'yyyy/mm/dd');

SELECT *
FROM order_details
WHERE order_date >= TO_DATE('2014/02/01', 'yyyy/mm/dd')
AND order_date <= TO_DATE('2014/02/28','yyyy/mm/dd');

-- 复杂的where条件
SELECT *
FROM suppliers
WHERE (state = 'California' AND supplier_name = 'IBM')
OR (supplier_id < 5000);

SELECT supplier_id
FROM suppliers
WHERE (supplier_name = 'IBM')
OR (supplier_name = 'Apple' AND state = 'Florida')
OR (supplier_name = 'Best Buy' AND status = 'Active' AND state = 'California');

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, customer_name
FROM customers
WHERE (customer_name = 'Apple' OR customer_name = 'Samsung')
AND customer_id > 20;

UPDATE suppliers
SET supplier_name = 'Samsung'
WHERE supplier_name = 'RIM'
AND (state = 'California' OR state = 'Florida');

DELETE FROM suppliers
WHERE state = 'Florida'
AND (product = 'PC computers' OR supplier_name = 'Dell');

-- EXISTS的使用
-- SQL语句的嵌套
-- 注意:使用EXISTS条件,main query(外部查询)和
-- subquery(子查询)一定要有连接,,如果没有连结
-- EXISTS条件使用是无效的
-- 注意:只要内层的subquery的数据较少， Exists
-- 查询速度比 IN 快很多， 因为IN不走索引
SELECT d.*
FROM DEPARTMENT d
WHERE EXISTS(
    SELECT *
    FROM EMPLOYEE e
    WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID);
    
SELECT *
FROM customers
WHERE EXISTS (
    SELECT *
    FROM order_details
    WHERE customers.customer_id = order_details.customer_id);
    
SELECT *
FROM customers
WHERE NOT EXISTS (
    SELECT *
    FROM order_details
    WHERE customers.customer_id = order_details.customer_id);

INSERT INTO contacts (contact_id, contact_name)
SELECT supplier_id, supplier_name
FROM suppliers
WHERE EXISTS (
    SELECT * FROM order_details
    WHERE suppliers.supplier_id = order_details.supplier_id);
    
UPDATE suppliers
SET supplier_name = (
    SELECT customers.name
    FROM customers
    WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (
    SELECT customers.name FROM customers
    WHERE customers.customer_id = suppliers.supplier_id)
    
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (
    SELECT ProductName -- 给什么欄位都一样，没有影响
    FROM Products
    WHERE Products.SupplierID = Suppliers.supplierID
    AND Price < 20);
    
-- is NULL and is not null
SELECT *
FROM suppliers
WHERE supplier_name IS NULL;

SELECT *
FROM customers
WHERE customer_name IS NOT NULL;

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city IS NULL;

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE account_no IS NOT NULL;

UPDATE suppliers
SET name = 'Apple'
WHERE name IS NULL;

DELETE FROM suppliers
WHERE supplier_name IS NULL;

-- union和union all 区别在于在于对排序与重复值的处理
-- Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
-- Union All：对两个结果集进行并集操作，包括重复行，不进行排序
-- 要点：
-- 重复的数据会过滤去除
-- 两个查询的column name最好一致，可用别名来处理
-- Intersection为交集，只显示重复的数据

SELECT supplier_id
FROM suppliers
UNION -- Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
SELECT supplier_id
FROM order_details;

SELECT supplier_id, supplier_name
FROM suppliers
WHERE supplier_id <= 500
UNION
SELECT company_id, company_name
FROM companies
WHERE company_name = 'Apple'
ORDER BY 2;

SELECT supplier_id, supplier_name
FROM suppliers
WHERE state = 'California'
UNION ALL -- Union All：对两个结果集进行并集操作，包括重复行，不进行排序
SELECT company_id, company_name
FROM companies
WHERE company_id > 1000
ORDER BY 2;

-- 多表查询
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
LEFT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
FULL OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

SQL指令复习

-- Query entire column of Product_Type table.
Select * From Product_Type;

-- The above statement and the following statement are equivalent.
-- Pty is alias (a alias of Product_Type table).
Select Pty.* From Product_Type Pty; -- 给表起别名，只是标准的SQL，MySQL也可以使用

-- Query some columns.
Select Pty.Product_Type_Cd, Pty.Name
From Product_Type Pty;

-- Query 4 columns on the Employee table
-- Using emp as alias for Employee
Select Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Dept_Id
From Employee Emp;

-- # create alias for column

-- Using the concat (string1, string2) to concatenate two strings together.
-- We have a new column, use 'as' to define column name for this column.
Select Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Dept_Id
-- concat 就是连接2个字串，eg: EMP1, EMP2
,Concat('EMP',Emp.Emp_Id) As Emp_No2 -- New column
From Employee Emp;

-- Maybe not need 'as' in the definition Alias column.
Select Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Dept_Id
,Concat('EMP'
,Emp.Emp_Id) Emp_No2 -- New column
From Employee Emp;

-- With Oracle you can use the || operator to concatenate two strings
-- This operator can concatenate multiple strings together.
-- (for Oracle only).
Select Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Dept_Id
,'EMP' || Emp.Emp_Id As Emp_No2 -- New column
From Employee Emp;

-- # SQL distinct

-- Query Product table.
Select Pro.Product_Cd
,Pro.Name
,Pro.Product_Type_Cd
From Product Pro;

-- Query Product_Type in Product table.
Select Pro.Product_Type_Cd from Product Pro;

-- Distinct need to use to remove the duplicates.
Select Distinct Pro.Product_Type_Cd from Product Pro;

-- # SQL where

-- Querying the Product table to find the type of products:
-- "Individual and Business Loans".
-- Corresponding column: Product_Type_Cd = 'LOAN'.
Select * From Product Pro Where Pro.Product_Type_Cd = 'LOAN';

-- # SQL and/or

-- Query the employee whose first name starts with S.
Select Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Dept_Id
From Employee Emp
Where Emp.First_Name Like 'S%';

-- Query the employee whose first name starts with S.
-- and work in Operation department. Dept_Id = 1.
Select Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Dept_Id
From Employee Emp
Where Emp.First_Name Like 'S%'
And Emp.Dept_Id = 1;

-- # SQL in

-- This command searches the employee named
-- Susan or Paula or Helen.
Select Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Dept_Id
From Employee Emp
Where Emp.First_Name In ('Susan'
,'Paula'
,'Helen');

-- SQL between

-- Find employees, with Emp_Id between 5 and 10.
Select Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Start_Date
From Employee Emp
Where (Emp.Emp_Id Between 5 And 10);

-- The statement above is equivalent to:
Select Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Start_Date
From Employee Emp
Where Emp.Emp_Id >= 5
And Emp.Emp_Id <= 10;

-- This statement helps find employees who have begun working for a period of time
-- specify it in where statement.
-- For example, 03-05-2002 ==> 09-08-2002 (Format: dd-MM-yyyy)
Select Emp.Emp_Id, Emp.First_Name, Emp.Last_Name, Emp.Start_Date
, -- To_Char is function of Oracle.
-- It may not exist in other DB.
-- To_Char convert Date to text
-- In form of some format. Here is dd-MM-yyyy.
To_Char(Emp.Start_Date, 'dd-MM-yyyy') Start_Date_Vn
From Employee Emp
Where
-- To_Date is a function of Oracle (It may not exist on other DB)
-- To_Date is a function that convert Text into Date
-- In form of some format. Here is dd-MM-yyyy.

Emp.Start_Date Between To_Date('03-05-2002‘,'dd-MM-yyyy') And
To_Date('09-08-2002‘,'dd-MM-yyyy');

-- # SQL wildcard

-- There are 2 special characters in SQL:
-- 1. % character (it describes 0, 1 or more characters)
-- 2. _ character (it describes exactly one character)
-- Two characters are usually used in the LIKE condition.
-- Find Customers whose FED_ID is formatted:
-- The initial part is random, following by -, then two any characters, then -, and the
last part is any.
-- Use two dashes (_) for illustrating two characters.
-- (Each dash (_) is a unique character).
Select Cus.Cust_Id, Cus.Fed_Id,Cus.Address
From Customer Cus
where cus.fed_id like '%-__-%';

-- # SQL order by
-- Arrange Product_Type_Cd in ascending order
-- Next, arrange Name in ascending order, too.
Select Pro.Product_Cd,Pro.Product_Type_Cd,Pro.Name
From Product Pro
Order By Pro.Product_Type_Cd Asc, Pro.Name Asc;

-- In Order BY, ASC is defaulted.
-- Thus, it is unnecessary to write ASC.
Select Pro.Product_Cd,Pro.Product_Type_Cd,Pro.Name
From Product Pro
Order By Pro.Product_Type_Cd, Pro.Name;

-- Arrange Product_Type_Cd in descending order
-- Next, arrange name in ascending order
Select Pro.Product_Cd,Pro.Product_Type_Cd,Pro.Name
From Product Pro
Order By Pro.Product_Type_Cd Desc, Pro.Name Asc;

-- Find the employees whose first name starts with S.
-- Sort descending by start date of work
Select Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Start_Date
From Employee Emp
Where Emp.First_Name Like 'S%'
Order By Emp.Start_Date Desc;

-- This is a syntax of Oracle (may not be supported by other Database).
-- Apply the order of column to Order by clause.
-- First_Name is the second column in Select clause.
-- Be able to use Order by 2 in lieu of Order by First_Name.
Select Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Start_Date
From Employee Emp
Order By 2 Desc;

-- # SQL group by
First, we need to understand what Aggregate Functions are:
•Sum: Function returns the sum of all the values
•Avg: Function return average of values
•Count: Function Returns the number of items.
•Min: Function finds the minimum value
•Max: Function finds the maximum value

-- Query Account table.
Select Acc.Account_Id
,Acc.Product_Cd
,Acc.Avail_Balance
,Acc.Pending_Balance
From Account Acc;

Select Acc.Product_Cd
,Count(Acc.Product_Cd) As Count_Acc
,Sum(Acc.Avail_Balance) As Sum_Avail_Balance
,Avg(Acc.Avail_Balance) As Avg_Avail_Balance
From Account Acc
Group By Acc.Product_Cd;

-- # SQL having
Select Acc.Product_Cd
,Count(Acc.Product_Cd) As Count_Acc
,Sum(Acc.Avail_Balance) As Sum_Avail_Balance -- Sum available balance
,Avg(Acc.Avail_Balance) As Avg_Avail_Balance -- The average available balance
From Account Acc
Group By Acc.Product_Cd
Having Count(Acc.Product_Cd) > 3;

-- Distinguishing Where & Having
Select Acc.Product_Cd
,Count(Acc.Product_Cd) As Count_Acc
,Sum(Acc.Avail_Balance) As Sum_Avail_Balance
,Avg(Acc.Avail_Balance) As Avg_Avail_Balance
From Account Acc
-- Using WHERE to filter data before group
Where Acc.Open_Branch_Id = 1
Group By Acc.Product_Cd
-- Using HAVING to filter data after group
Having Count(Acc.Product_Cd) > 1;

-- INSERT INTO statement syntax:
INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...);
-- Insert a record in Acc_Transaction table
-- the Txn_ID column contains the value created by Hibernate_Sequence sequence
-- Sysdate is a function of Oracle, which helps extract System Date
Insert Into Acc_Transaction
(Txn_Id
,Amount
,Funds_Avail_Date
,Txn_Date
,Txn_Type_Cd
,Account_Id
,Execution_Branch_Id
,Teller_Emp_Id)
Values
(Hibernate_Sequence.Nextval -- Txn_Id
,100 -- Amount
,Sysdate -- Funds_Avail_Date
,Sysdate -- Txn_Date
,'CDT' -- Txn_Type_Cd
,2 -- Account_Id
,Null -- Execution_Branch_Id
,Null -- Teller_Emp_Id
);

-- 使用select语句为inset into语句提供数据
-- Insert multi record in Acc_Transaction table
-- Use Select statement to provide data.
Insert Into Acc_Transaction
(Txn_Id
,Txn_Date
,Account_Id
,Txn_Type_Cd
,Amount
,Funds_Avail_Date)
Select Hibernate_Sequence.Nextval -- Txn_Id
,Acc.Open_Date -- Txn_Date
,Acc.Account_Id -- Account_Id
,'CDT' -- Txn_Type_Cd
,200 -- Amount
,Acc.Open_Date -- Funds_Avail_Date
From Account Acc
Where Acc.Product_Cd = 'CD';

-- Update, increase the amount of money in customers' account with CUST_ID = 1 by 2%.
Update Account Acc
Set Acc.Avail_Balance = Acc.Avail_Balance + 2 * Acc.Avail_Balance / 100
,Acc.Pending_Balance = Acc.Pending_Balance + 2 * Acc.Pending_Balance / 100
Where Acc.Cust_Id = 1;

-- Delete two records in the Acc_Transaction table
Delete From Acc_Transaction Txn
Where Txt.Txn_Id In (61, 62);

-- SQL functions
-- The COUNT() function returns the number of rows that matches a specified criteria.
-- Count the number of rows in the Account table
Select Count(Acc.Account_Id) From Account Acc;

-- Count the number of accounts having transaction with the bank
Select Count(distinct txn.Account_id) From Acc_Transaction txn;

-- Count the number of accounts opened for each customer.
Select Acc.Cust_Id, Count(Acc.Account_Id)
From Account Acc
Group By Acc.Cust_Id;

-- Find the sum of the money in customers' accounts with CUST_ID =
Select Sum(Acc.Avail_Balance) From Account Acc Where Acc.Cust_Id = 1;
-- Use Group by.
-- Find the sum of the money in accounts owned by each customer
Select Acc.Cust_Id, Sum(Acc.Avail_Balance)
From Account Acc
Group By Acc.Cust_Id;

-- Find the average of money equivalent to each type of deposit.
Select Avg(Acc.Avail_Balance)
From Account Acc
Where Acc.Product_Cd = 'SAV';
-- Use Group by.
-- A customer can own one or more account.
-- Find the average of money in each account owned by each customer
-- (In the bank with Branch_ID = 1)
Select Acc.Cust_Id
,Avg(Acc.Avail_Balance)
From Account Acc
Where Acc.Open_Branch_Id = 1
Group By Acc.Cust_Id;

-- Find the minimum amount of deposit.
Select Min(Acc.Avail_Balance)
From Account Acc
Where Acc.Product_Cd = 'SAV';
-- Use Group by.
-- A customer can own one or more account.
-- Accounts can be opened in different branches.
-- Find the amount in the account, minimum for each branch
Select Acc.Open_Branch_Id, Min(Acc.Avail_Balance)
From Account Acc
Group By Acc.Open_Branch_Id;

-- SQL join
-- There are 4 ways to join two tables:
-- •INNER JOIN (JOIN)
-- •LEFT OUTER JOIN (LEFT JOIN)
-- •RIGHT OUTER JOIN (RIGHT JOIN)
-- •FULL OUTER JOIN (OUTER JOIN)
-- •CROSS JOIN
-- Syntax:
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
-- INNER JOIN can replaced by JOIN
-- Same Meaning, and result.
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

-- General syntax
-- INNER JOIN 2 table: EMPLOYEE and
DEPARTMENT.
Select Emp.Emp_Id,Emp.First_Name
,Emp.Last_Name, Emp.Dept_Id,Dep.Name,
Dept_Name
From Employee Emp
Inner Join Department Dep
On Emp.Dept_Id = Dep.Dept_Id
Order By Emp.Emp_Id;

-- Oracle's own syntax （这个好像是通用语法）
-- Other Oracle syntax to join two tables:
Select Emp.Emp_Id,Emp.First_Name
,Emp.Last_Name, Emp.Dept_Id,Dep.Name,
Dept_Name
From Employee Emp
,Department Dep
Where Emp.Dept_Id = Dep.Dept_Id
Order By Emp.Emp_Id;

-- Customer LEFT OUTER JOIN Officer
-- LEFT OUTER JOIN Can be replaced by LEFT JOIN (meaning, and the result is the same)
Select Cus.Cust_Id, Cus.Fed_Id, Cus.State
,Ofc.Cust_Id As Ofc_Cust_Id
,Ofc.Officer_Id, Ofc.Start_Date, Ofc.Title
From Customer Cus -- Table1
Left Outer Join Officer Ofc -- Table2
On Cus.Cust_Id = Ofc.Cust_Id;

-- LEFT OUTER JOIN. See other syntax of Oracle:
-- Oracle LEFT OUTER JOIN condition push two tables down WHERE.
-- Use the (+) in Table2.
Select Cus.Cust_Id, Cus.Fed_Id, Cus.State
,Ofc.Cust_Id As Ofc_Cust_Id
,Ofc.Officer_Id, Ofc.Start_Date, Ofc.Title
From Customer Cus -- Table1
,Officer Ofc -- Table2
Where Cus.Cust_Id = Ofc.Cust_Id(+);

-- LEFT OUTER JOIN. See other syntax of Oracle:
-- Oracle LEFT OUTER JOIN condition push two tables down WHERE.
-- Use the (+) in Table2.
Select Cus.Cust_Id, Cus.Fed_Id, Cus.State
,Ofc.Cust_Id As Ofc_Cust_Id
,Ofc.Officer_Id, Ofc.Start_Date, Ofc.Title
From Customer Cus -- Table1
,Officer Ofc -- Table2
Where Cus.Cust_Id(+) = Ofc.Cust_Id;

-- SQL-Subquery 子查询
Select Acc.Account_Id
,Acc.Open_Date
,Acc.Product_Cd
,Acc.Avail_Balance
From Account Acc
Where Acc.Cust_Id In
(Select Cus.Cust_Id From Customer Cus Where Cus.Cust_Type_Cd = 'B')

-- A subquery can also be found in the from clause.
-- There are called inline views.
Select Cus.Cust_Id
,Cus.Address
,Cus.Fed_Id
,Acc2.Sum_Avail_Balance
From Customer Cus,
-- Define a inline view.
(Select Acc.Cust_Id
,Sum(Acc.Avail_Balance) As Sum_Avail_Balance
From Account Acc
Group By Acc.Cust_Id) Acc2
Where Cus.Cust_Id = Acc2.Cust_Id;

-- A subquery can also found in the select caluse.
Select Cus.Cust_Id
,Cus.Address
,Cus.Fed_Id
,(Select Sum(Acc.Avail_Balance)
From Account Acc
Where Acc.Cust_Id = Cus.Cust_Id) As Sum_Avail_Balance
From Customer Cus;

-- The trick to placing a subquery in the select clause
-- is that the subquery must return asingle value.
-- This is why an aggregate function such as SUM function,
-- COUNT function, MIN function, or MAX function is commonly used in the subquery.

Cursor

cursor 为 PL/SQL 中内建的一种 pointer，可用来撷取整个数据集合，并让用户可以逐笔资料的进行存取。
在 Oracle 中，cursor 分为两种，分别是：

• explicit(外显) cursor
  此种 cursor 就可以透过开发 PL/SQL 的方式来进行控制与管理，让管理者可以有很大的弹性来使用。
• implicit(内隐) cursor
  在 Oracle 所提供的功能中，可以发现许多 implicit cursor 的踪迹，例如：
  PL/SQL 中的 FOR…LOOP。
  而这一类的 cursor 是没有办法透过程序去控制的，而是由 Oracle 本身来进行控制与管理。

什么是 cursor variable 呢? 简单来说，就是「储存指向实际 cursor 的指针的变量」

使用 Explicit Cursor

每個 explicit cursor 都會有以下幾個屬性作為程式控制之用：

• %NOTFOUND
  根據從資料集合中擷取的最後一筆資料，來決定回傳 TRUE(有資料) or FALSE(沒
  資料)。
• %FOUND
  與 %NOTFOUND 相反。
• %ROWCOUNT
  回傳資料集合中所包含的資料筆數。當 cursor 從資料集合擷取過資料後，就可以
  使用此屬性。
• %ISOPEN
  若是 cursor 狀態為 open，則回傳 TRUE；反之則回傳 FALSE。

%isopen	Return true if cursor is openning
%notfound	Return true if there is no next line
%found	Return true if there is next line
%rowcount	return the row count was fetched.

兩種Cursor宣告的方式:

Declare
-- Cursor declaration has no parameters:
Cursor Emp_Cur Is
Select Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
From Employee Emp;

-- Cursor declaration has parameters:
Cursor Emp_Cur(p_Dept_Id Number
,p_Branch_Id Number)
Is
Select Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Assigned_Branch_Id
,Emp.Dept_Id
From Employee Emp
Where (Emp.Dept_Id = p_Dept_Id Or p_Dept_Id Is Null)
And (Emp.Assigned_Branch_Id = p_Branch_Id Or p_Branch_Id Is Null);

使用Explicit Cursor

Declare
    -- Declaring a Cursor with 2 parameters.
    Cursor Emp_Cur
    (
    p_Dept_Id Number
    ,p_Branch_Id Number
    ) Is
    Select Emp.Emp_Id
    ,Emp.First_Name
    ,Emp.Last_Name
    ,Emp.Assigned_Branch_Id
    ,Emp.Dept_Id
    From Employee Emp
    Where (Emp.Dept_Id = p_Dept_Id Or p_Dept_Id Is Null) -- Or p_Dept_Id Is Null是为了程序健壮性
    And (Emp.Assigned_Branch_Id = p_Branch_Id Or p_Branch_Id Is Null);
    ---
    -- Declare a variable type of ROWTYPE, based on the newly created Cursor.
    v_Emp Emp_Cur%Rowtype;
    ---
    v_Dept_Id Number := 1;
    v_Branch_Id Number;
    ---
    v_Row Integer := 0;
    v_Open Boolean;
Begin
    -- Check cursor is openning?
    If Emp_Cur%Isopen Then
    Dbms_Output.Put_Line('Cursor opened');
    Else
    Dbms_Output.Put_Line('Cursor not open');
    End If;
    --
    Dbms_Output.Put_Line('Opening cursor...');
    -- Open Cursor (Pass input parameters).
    Open Emp_Cur(v_Dept_Id, v_Branch_Id); -- 先打開Cursor之後才能Fetch
    -- Using loop
    Loop
        -- Get row of data from Cursor
        -- Each time Fetch cursor moves one line
        -- (Downward).
        Fetch Emp_Cur
        Into v_Emp; -- 提取一筆資料，游標會自動向下移一筆
        -- Conditions to exit the loop
        Exit When Emp_Cur%Notfound;
        -- Process the data
        v_Row := v_Row + 1;
        Dbms_Output.Put_Line(v_Row || ' - First_Name: ' || v_Emp.First_Name
        || ' - Last_Name: ' || v_Emp.Last_Name);
    End Loop;
    --
    Dbms_Output.Put_Line('Closing cursor...');
    -- Close Cursor.
    Close Emp_Cur;
End;

使用 Implicit Cursor（隐式cursor）

To implicit cursor, you don’t need to write open/close statement clearly.

-- 使用 Implicit Cursor
Declare
    -- Declaring a Cursor with 2 parameters.
    Cursor Emp_Cur(p_Dept_Id Number
    ,p_Branch_Id Number) Is
    Select Emp.Emp_Id
    ,Emp.First_Name
    ,Emp.Last_Name
    ,Emp.Assigned_Branch_Id
    ,Emp.Dept_Id
    From Employee Emp
    Where (Emp.Dept_Id = p_Dept_Id Or p_Dept_Id Is Null)
    And (Emp.Assigned_Branch_Id = p_Branch_Id Or p_Branch_Id Is Null);
    ---
    ---
    v_Dept_Id Number := 1;
    v_Branch_Id Number;
    ---
    v_Row Integer := 0;
Begin
    -- Check cursor is open
    If Emp_Cur%Isopen Then
    	Dbms_Output.Put_Line('Cursor opened');
    Else
    	Dbms_Output.Put_Line('Cursor not open');
    End If;
    --
    -- Using loops to fetch cursor
    -- No need: open/close/fetch.
    -- 其实就是不用打开和定义游标变量，直接在for里面定义局部游标变量
    For v_Emp In Emp_Cur(v_Dept_Id
    ,v_Branch_Id) Loop
        --
        v_Row := v_Row + 1;
        Dbms_Output.Put_Line(v_Row || ' - First_Name: ' || v_Emp.First_Name ||
        ' - Last_Name: ' || v_Emp.Last_Name);
    End Loop;
End;

參照游标

-- 创建两个强类型參照游标变量(有传回值)和一个弱类型參照游标变量(无传回值)
DECLARE
	-- 定义record
    TYPE deptrecord IS RECORD(
        Deptno departments.department_id%TYPE,
        Dname departments.department_name%TYPE,
        Loc departments.location_id%TYPE
    );
    -- 定义两个强类型參照游标(有传回值)，定义传回值通常是Record或Table的全部column
    TYPE deptcurtype IS REF CURSOR RETURN departments%ROWTYPE;
    TYPE deptcurtyp1 IS REF CURSOR RETURN deptrecord;
    -- 定义一个弱类型參照游标(无传回值)
    TYPE curtype IS REF CURSOR;
    
    -- 声明3个游标变量，Cursor所需要的查詢SQL可在Open時給予
    Dept_c1 deptcurtype;
    Dept_c2 deptcurtyp1;
    Cv curtype;

强类型参照游标

强类型参照游标变量类型(需定义回传值type)

DECLARE
    TYPE emp_job_rec IS RECORD(
        Employee_id employees.employee_id%TYPE,
        Employee_name employees.first_name%TYPE,
        Job_title employees.job_id%TYPE
    );
    TYPE emp_job_refcur_type IS REF CURSOR RETURN emp_job_rec;
    emp_refcur emp_job_refcur_type;
    emp_job emp_job_rec;
BEGIN
    OPEN emp_refcur FOR
    SELECT employees.employee_id, employees.first_name||employees.last_name, employees.job_id
    FROM employees
    ORDER BY employees.department_id;
    FETCH emp_refcur INTO emp_job;
    WHILE emp_refcur%FOUND LOOP
        DBMS_OUTPUT.PUT_LINE(emp_job.employee_id||': '||emp_job.employee_name||' is a '||emp_job
        .job_title);
        FETCH emp_refcur INTO emp_job;
    END LOOP;
END;

弱类型参照游标

弱类型参照游标变量类型(不需定义回传值type)

DECLARE
    Refcur refcur_t;
    TYPE sample_rec_type IS RECORD (
    Id number,
    Description VARCHAR2 (30)
    );
    Type refcur_t IS REF CURSOR;
    sample sample_rec_type;
    selection varchar2(1) := UPPER (SUBSTR (‘&tab’ , 1, 1));
BEGIN
    IF selection=‘D’ THEN
    OPEN refcur FOR
    SELECT departments.department_id, departments.department_name FROM departments;
    DBMS_OUTPUT.PUT_LINE(‘Department data’ );
    ELSIF selection=‘E’ THEN
    OPEN refcur FOR
    SELECT employees.employee_id, employees.first_name||' is a '||employees.job_id FROM employees;
    DBMS_OUTPUT.PUT_LINE('Employee data');
    ELSE
    DBMS_OUTPUT.PUT_LINE('Please enter ''D'' or ''E''');
    RETURN;
    END IF;
    DBMS_OUTPUT.PUT_LINE('----------------------');
    FETCH refcur INTO sample;
    WHILE refcur%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(sample.id||': '||sample.description);
    FETCH refcur INTO sample;
    END LOOP;
    CLOSE refcur;
END;

使用游标变量（有RETURN子句）

DECLARE
    --定义一个与employees表中的这几个列相同的记录数据类型
    TYPE emp_record_type IS RECORD(
    f_name employees.first_name%TYPE,
    h_date employees.hire_date%TYPE,
    j_id employees.job_id%TYPE);
    --声明一个该记录数据类型的记录变量 (传回值使用)
    v_emp_record emp_record_type;
    --定义一个游标数据类型
    TYPE emp_cursor_type IS REF CURSOR RETURN emp_record_type;
    --声明一个游标变量
    c1 emp_cursor_type;
    BEGIN
    OPEN c1 FOR SELECT first_name, hire_date, job_id
    FROM employees WHERE department_id = 20;
    LOOP
    FETCH c1 INTO v_emp_record;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('雇员名称：'||v_emp_record.f_name||' 雇佣日期：' ||
    v_emp_record.h_date||' 岗位：'||v_emp_record.j_id);
    END LOOP;
    CLOSE c1;
END;

使用游标变量（没有RETURN子句）

DECLARE
    --定义一个游标数据类型
    TYPE emp_cursor_type IS REF CURSOR;
    --声明一个游标变量
    c1 EMP_CURSOR_TYPE; -- 隨著SQL的不同，回傳值需要不同的rowtype，所以没有返回值
    --声明两个记录变量
    v_emp_record employees%ROWTYPE;
    v_reg_record regions%ROWTYPE;
BEGIN
    OPEN c1 FOR SELECT * FROM employees WHERE department_id = 20;
    LOOP
    FETCH c1 INTO v_emp_record;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp_record.first_name||'的雇佣日期是'
    ||v_emp_record.hire_date);
    END LOOP;
    --将同一个游标变量对应到另一个SELECT语句
    OPEN c1 FOR SELECT * FROM regions WHERE region_id IN（1，2）;
    LOOP
    FETCH c1 INTO v_reg_record;
    EXIT WHEN c1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_reg_record.region_id||'表示'||v_reg_record.region_name);
    END LOOP;
    CLOSE c1;
END;

StoredProcedure&Function

实例

CREATE OR REPLACE
PROCEDURE GET_EMPLOYEE_INFOS (p_Emp_Id Number,v_First_Name Out Varchar2,
v_Last_Name Out Varchar2, v_Dept_Id Out Number) Is
BEGIN
    -- Print out console.
    -- Log - use for programmers
    Dbms_Output.Put_Line('Parameter p_Emp_Id = ' || p_Emp_Id);
    -- If SELECT .. INTO statement return nothing
    -- it will throw an Exception - NO_DATA_FOUND:
    Select EmpT.First_Name
    ,EmpT.Last_Name
    ,EmpT.Dept_Id
    Into v_First_Name
    ,v_Last_Name
    ,v_Dept_Id
    From Employee EmpT
    Where EmpT.Emp_Id = p_Emp_Id;
    --
    -- Print out Console.
    --
    Dbms_Output.Put_Line('Found Record!');
    Dbms_Output.Put_Line(' v_First_Name= ' || v_First_Name);
    Dbms_Output.Put_Line(' v_Last_Name= ' || v_Last_Name);
    Dbms_Output.Put_Line(' v_Dept_Id= ' || v_Dept_Id);
    Exception
    When No_Data_Found Then
    -- Print out Console.
    Dbms_Output.Put_Line('No Record found with p_Emp_Id = ' || p_Emp_Id);
END GET_EMPLOYEE_INFOS; -- 可以直接写end