目录
  1. 一、 oracle介绍[了解]
  2. 二、 Oracle安装[了解](待补充)
  3. 三、 Oracle体系结构[理解]
    1. 3.1 数据库
    2. 3.2 实例
    3. 3.3 用户
    4. 3.4 表空间
    5. 3.5 数据文件(dbf、ora)、
  4. 四、 创建表空间[理解]
  5. 五、 用户[理解]
    1. 5.1 创建用户
    2. 5.2 用户赋权限
  6. 六、 Oracle数据类型[应用]
  7. 七、 表的管理[应用]
    1. 7.1 建表
    2. 7.2 表删除
    3. 7.3 表的修改
    4. 7.4 数据库表数据的更新
      1. 7.4.1 INSERT(增加)
      2. 7.4.2 UPDATE(修改)
      3. 7.4.3 DELETE(删除)
      4. 7.4.4 序列
  8. 八、 Scott用户下的表结构[了解]
  9. 九、 单行函数[应用]
    1. 9.1 字符函数
    2. 9.2 数值函数
    3. 9.3 日期函数
    4. 9.4 转换函数
    5. 9.5 通用函数
  10. 十、 多行函数(聚合函数)
    1. 10.1 统计记录数 count()
    2. 10.2 最小值查询 min()
    3. 10.3 最大值查询 max()
    4. 10.4 查询平均值 avg()
    5. 10.5 求和函数 sum()
  11. 十一、 分组统计
  12. 十二.视图[应用]
    1. 语法 1.:CREATE VIEW 视图名称 AS 子查询
    2. 语法 2:CREATE OR REPLACE VIEW 视图名称 AS 子查询
    3. 语法 3:CREATE OR REPLACE VIEW 视图名称 AS 子查询 WITH READ ONLY(设置视图为只读)
  13. 十三.索引[应用]
    1. 1. 单列索引
    2. 2. 复合索引
  • 十四.pl/sql 基本语法[了解]
    1. 3.1 pl/sql 程序语法
    2. 3.2 常量和变量定义
    3. 3.3 if 分支
    4. 3.4 LOOP 循环语句
    5. 3.5游标 Cursor
  • 十五.存储过程[理解]
  • 十六.存储函数[理解]
  • 十七.触发器[理解]
  • Oracle入门

    一、 oracle介绍[了解]

    ORACLE 数据库系统是美国 ORACLE 公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或 BS 体系结构的数据库之一。比如SilverStream 就是基于数据库的一种中间件。ORACLE 数据库是目前世界上使用最为广泛的数据库管理系统,作为一个通数据库系统,它具有完整的数据管理功能;作为一个关系数据库,它是一个完备关系的产品;作为分布式数据库它实现了分布式处理功能。但它的所有知识,只要在一种机型上学习了 ORACLE 知识,便能在各种类型的机器上使用它。

    二、 Oracle安装[了解](待补充)

    三、 Oracle体系结构[理解]

    3.1 数据库

    Oracle 数据库是数据的物理存储。这就包括(数据文件 ORA 或者 DBF、控制文件、联机日志、参数文件)。其实 Oracle 数据库的概念和其它数据库一样,这里的数据库是一个操作系统只有一个库。可以看作是 Oracle 就只有一个大数据库。

    3.2 实例

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

    3.3 用户

    用户是在实例下建立的。不同实例可以建相同名字的用户。

    3.4 表空间

    表空间是 Oracle 对物理数据库上相关数据文件(ORA 或者 DBF 文件)的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间,每个表空间包含在逻辑上相关联的一组结构。每个数据库至少有一个表空间(称之为 system 表空间)。每个表空间由同一磁盘上的一个或多个文件组成,这些文件叫数据文(datafile)。一个数据文件只能属于一个表空间。

    3.5 数据文件(dbf、ora)、

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

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

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


    四、 创建表空间[理解]

    表空间? ORACLE 数据库的逻辑单元。 数据库—表空间 一个表空间可以与多个数据文件(物理结构)关联一个数据库下可以建立多个表空间,一个表空间可以建立多个用户、一个用户下可以建立多个表。

    1
    2
    3
    4
    5
    create tablespace byliuyu
    datafile 'c:\byliuyu.dbf'
    size 100m
    autoextend on
    next 10m

    byliuyu 为表空间名称
    datafile 指定表空间对应的数据文件
    size 后定义的是表空间的初始大小
    autoextend on 自动增长 ,当表空间存储都占满时,自动增长
    next 后指定的是一次自动增长的大小。

    五、 用户[理解]

    5.1 创建用户

    1
    2
    3
    create user byliuyuuser
    identified by byliuyu
    default tablespace byliuyu

    identified by 后边是用户的密码
    default tablespace 后边是表空间名称
    oracle 数据库与其它数据库产品的区别在于,表和其它的数据库对象都是存储在用户下的。

    5.2 用户赋权限

    新创建的用户没有任何权限,登陆后会提示:

    Oracle 中已存在三个重要的角色:connect 角色,resource 角色,dba 角色。
    CONNECT 角色: –是授予最终用户的典型权利,最基本的

    1
    2
    3
    4
    5
    6
    7
    ALTER SESSION --修改会话
    CREATE CLUSTER --建立聚簇
    CREATE DATABASE LINK --建立数据库链接
    CREATE SEQUENCE --建立序列
    CREATE SESSION --建立会话
    CREATE SYNONYM --建立同义词
    CREATE VIEW --建立视图

    RESOURCE 角色: –是授予开发人员的

    1
    2
    3
    4
    5
    6
    CREATE CLUSTER --建立聚簇
    CREATE PROCEDURE --建立过程
    CREATE SEQUENCE --建立序列
    CREATE TABLE --建表
    CREATE TRIGGER --建立触发器
    CREATE TYPE --建立类型

    DBA 角色:拥有全部特权,是系统最高权限,只有 DBA 才可以创建数据库结构,并且系统
    权限也需要 DBA 授出,且 DBA 用户可以操作全体用户的任意基表,包括删除。

    1
    grant dba to byliuyuuser

    进入 system 用户下给用户赋予 dba 权限,否则无法正常登陆

    六、 Oracle数据类型[应用]

    No 数据类型 描述
    1 Varchar, varchar2 表示一个字符串
    2 NUMBER NUMBER(n)表示一个整数,长度是 n
    3 DATA 表示日期类型
    4 CLOB 大对象,表示大文本数据类型,可存 4G
    5 BLOB 大对象,表示二进制数据,可存 4G

    补充第二条描述:NUMBER(m,n):表示一个小数,总长度是 m,小数是 n,整数是 m-n
    因markdown语法不支持合并单元格,所以在此补充,如果你知道,请告诉我。谢谢~

    七、 表的管理[应用]

    7.1 建表

    语法:
    Create table 表名(
    字段 1 数据类型 [default 默认值],
    字段 2 数据类型 [default 默认值],

    字段 n 数据类型 [default 默认值]
    );

    范例:创建 person 表

    1
    2
    3
    4
    5
    6
    7
    8
    create table person(
    pid number(10),
    name varchar2(10),
    gender number(1) default 1,
    birthday date
    );
    insert into person(pid, name, gender, birthday)
    values(1, '张三', 1, to_date('1999-12-22', 'yyyy-MM-dd'));

    7.2 表删除

    语法:DROP TABLE 表名

    7.3 表的修改

    在 sql 中使用 alter 可以修改表
    添加语法:ALTER TABLE 表名称 ADD(列名 1 类型 [DEFAULT 默认值],列名 1 类型 [DEFAULT 默认值]…)
    修改语法:ALTER TABLE 表名称 MODIFY(列名 1 类型 [DEFAULT 默认值],列名 1 类型 [DEFAULT 默认值]…)
    修改列名: ALTER TABLE 表名称 RENAME 列名 1 TO 列名 2

    范例:在 person 表中增加列 address

    1
    alter table person add(address varchar2(10));

    范例:把 person 表的 address 列的长度修改成 20 长度

    1
    alter table person modify(address varchar2(20))

    7.4 数据库表数据的更新

    7.4.1 INSERT(增加)

    标准写法:
    INSERT INTO 表名[(列名 1,列名 2,…)]VALUES(值 1,值 2,…)
    简单写法(不建议):
    INSERT INTO 表名 VALUES(值 1,值 2,…)
    注意:使用简单的写法必须按照表中的字段的顺序来插入值,而且如果有为空的字段使用 null
    insert into person values(2,’李四’,1,null,’北京’);

    7.4.2 UPDATE(修改)

    全部修改:UPDATE 表名 SET 列名 1=值 1,列名 2=值 2,…. (慎用)
    局部修改:UPDATE 表名 SET 列名 1=值 1,列名 2=值 2,….WHERE 修改条件;

    7.4.3 DELETE(删除)

    语法 : DELETE FROM 表名 WHERE 删除条件;
    在删除语句中如果不指定删除条件的话就会删除所有的数据
    因为 oracle 的事务对数据库的变更的处理,我们必须做提交事务才能让数据真正的插入到数据库中,在同样在执行完数据库变更的操作后还可以把事务进行回滚,这样就不会插入到数据库。如果事务提交后则不可以再回滚。
    提交:commit
    回滚:rollback

    7.4.4 序列

    在很多数据库中都存在一个自动增长的列,如果现在要想在 oracle 中完成自动增长的功能,则只能依靠序列完成,所有的自动增长操作,需要用户手工完成处理。
    语法:CREATE SEQUENCE 序列名
    [INCREMENT BY n]
    [START WITH n]
    [{MAXVALUE/ MINVALUE n|NOMAXVALUE}]
    [{CYCLE|NOCYCLE}]
    [{CACHE n|NOCACHE}];

    范例:创建一个 seqpersonid 的序列,验证自动增长的操作

    1
    CREATE SEQUENCE seqpersonid;

    序列创建完成之后,所有的自动增长应该由用户自己处理,所以在序列中提供了以下的两种操作:
    nextval :取得序列的下一个内容
    currval :取得序列的当前内容

    1
    2
    select seqpersonid.nextval from dual;
    select seqpersonid.currval from dual;

    在插入数据时需要自增的主键中可以这样使用

    1
    insert into person values(seqpersonid.nextval,'宋江',1,null,'北京燕龙');

    在实际项目中每一张表会配一个序列,但是表和序列是没有必然的联系的,一个序列被哪一张表使用都可以,但是我们一般都是一张表用一个序列。
    序列的管理一般使用工具来管理。

    八、 Scott用户下的表结构[了解]



    九、 单行函数[应用]

    9.1 字符函数

    接收字符输入返回字符或者数值,dual 是伪表

    1. 把小写的字符转换成大小的字符
      例:把liuyu.pw转换成大写字符
      1
      select upper('liuyu.pw') from dual;

    2.把大写字符变成小写字符
    例:把LIUYU.PW转换成小写字符

    1
    select LOWER('LIUYU.PW') from dual;

    可以根据 API 学习:首字母大写函数、字符串链接函数、字符串截取函数、字符串替换函数、获取字符
    串长度函数等。

    9.2 数值函数

    1. 四舍五入函数:ROUND()默认情况下 ROUND 四舍五入取整,可以自己指定保留的位数。
      例:把1234.56四舍五入取整
      1
      select round('1234.56') from dual;

    可以根据 API 学习:数值截取函数、取余函数等

    9.3 日期函数

    Oracle 中提供了很多和日期相关的函数,包括日期的加减,在日期加减时有一些规律
    日期 – 数字 = 日期
    日期 + 数字 = 日期
    日期 – 日期 = 数字

    1. 范例:查询雇员的进入公司的周数。
      分析:查询雇员进入公司的天数(sysdate – 入职日期)/7就是周数

      1
      select ename,round((sysdate-hiredate)/7) from EMP;

    2. 获得两个时间段中的月数:MONTHS_BETWEEN(),参数是两个时间,开始时间和当前时间
      范例:查询所有雇员进入公司的月数

      1
      select ename,round(months_between(sysdate,hiredate)) from EMP;

    9.4 转换函数

    1. TO_CHAR:字符串转换函数
      范例:查询所有的雇员将将年月日分开,此时可以使用 TO_CHAR 函数来拆分
      拆分时需要使用通配符
      年:y, 年是四位使用 yyyy
      月:m, 月是两位使用 mm
      日:d, 日是两位使用 dd
      1
      select empno,ename,to_char(hiredate,'yyyy') 年,to_char(hiredate,'mm')月,to_char(hiredate,'dd') 日 from emp;

    1
    select empno,ename,to_char(hiredate,'yyyy-mm-dd') from emp;

    在结果中 10 以下的月前面被被补了前导零,可以使用 fm 去掉前导零

    1
    select empno,ename,to_char(hiredate,'fmyyyy-mm-dd') from emp;

    1. TO_DATE:日期转换函数
      TO_DATE 可以把字符串的数据转换成日期类型

    9.5 通用函数

    1.空值处理 nvl
    范例:查询所有的雇员的年薪
    sal代表月工资,comm代表奖金或者佣金

    1
    select ename,sal*12+comm from emp;


    我们发现很多员工的年薪是空的,原因是很多员工的奖金是 null,null 和任何数值计算都是
    null,这时我们可以使用 nvl 来处理。

    1
    select ename,nvl(comm,0),sal*12+nvl(comm,0) from emp;

    2.Decode 函数
    该函数类似 if….else if…esle
    语法:DECODE(col/expression, [search1,result1],[search2, result2]….[default])
    Col/expression:列名或表达式
    Search1,search2…:用于比较的条件
    Result1, result2…:返回值
    如果 col/expression 和 Searchi 匹配就返回 resulti,否则返回 default 的默认值

    1
    select DECODE(2,1,'我是1',2,'我是2','我是无名') from dual;

    范例:查询出所有雇员的职位的中文名

    1
    select ename,job,decode(job,'CLERK','业务员','SALESMAN','销售','PRESIDENT','总裁','MANAGER','分析员','MANAGER','经理','无业') from emp;

    3.case when
    语法:
    CASE expr WHEN comparison_expr1 THEN return_expr1
    [WHEN comparison_expr2 THEN return_expr2
    WHEN comparison_exprn THEN return_exprn
    ELSE else_expr]
    END

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    select t.empno,t.ename,t.job,
    case
    when t.job = 'CLERK' then
    '业务员'
    when t.job = 'MANAGER' then
    '经理'
    when t.job = 'ANALYST' then
    '分析员'
    when t.job = 'PRESIDENT' then
    '总裁'
    when t.job = 'SALESMAN' then
    '销售'
    else
    '无业'
    end from emp t

    十、 多行函数(聚合函数)

    10.1 统计记录数 count()

    范例:查询出所有员工的记录数

    1
    select count(*) from emp;

    不建议使用 count(*),可以使用一个具体的列以免影响性能。

    10.2 最小值查询 min()

    范例:查询出来员工最低工资

    1
    select min(sal) from emp;

    10.3 最大值查询 max()

    范例:查询出员工的最高工资

    1
    select max(sal) from emp;

    10.4 查询平均值 avg()

    范例:查询出员工的平均工资

    1
    select avg(sal) from emp;

    10.5 求和函数 sum()

    范例:查询出 20 号部门的员工的工资总和

    1
    select sum(sal) from emp  e where e.deptno=20;

    十一、 分组统计

    分组统计需要使用 GROUP BY 来分组
    语法:语法:SELECT * |列名 FROM 表名 {WEHRE 查询条件} {GROUP BY 分组字段} ORDER BY 列
    名 1 ASC|DESC,列名 2…ASC|DESC

    范例:查询每个部门的人数

    1
    select deptno,count(ename) from emp group by deptno;

    范例:查询出每个部门的平均工资

    注意

    1. 如果使用分组函数,SQL 只可以把 GOURP BY 分组条件字段和分组函数查询出来,不能有其他字段。
    2. 如果使用分组函数,不使用 GROUP BY 只可以查询出来分组函数的值

    十二.视图[应用]

    视图就是封装了一条复杂查询的语句。

    语法 1.:CREATE VIEW 视图名称 AS 子查询

    范例:建立一个视图,此视图包括了 20 部门的全部员工信息

    1
    create view empviewdept20 as select * from emp t where t.deptno = 20;

    视图创建完毕就可以使用视图来查询,查询出来的都是 20 部门的员工

    1
    select * from empviewdept20;

    注:创建视图可能提示无权限,请用sys或者system账户登录,执行以下sql即可

    1
    2
    3
    alter user scott account unlock;
    alter user scott identified by scott;
    grant dba to scott;

    语法 2:CREATE OR REPLACE VIEW 视图名称 AS 子查询

    如果视图已经存在我们可以使用语法 2 来创建视图,这样已有的视图会被覆盖。

    1
    create or replace view empviewdept20 as select * from emp t where t.deptno = 20;

    那么视图可以修改吗?

    1
    update empviewdept20 t set t.ename ='史密斯' where t.empno=7369;


    我们尝试着修改视图但是发现是视图所查询的表的字段值被修改了。所以我们一般不会去修改视图。我们可以设置视图为只读。

    语法 3:CREATE OR REPLACE VIEW 视图名称 AS 子查询 WITH READ ONLY(设置视图为只读)

    1
    create or replace view  empviewdept20 as select * from emp t where t.deptno = 20 with read only ;

    再次尝试更新视图

    1
    update empviewdept20 t set t.ename ='史密斯' where t.empno=7369;

    会提示:ORA-42399: 无法对只读视图执行 DML 操作

    十三.索引[应用]

    索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而
    提高数据访问性能。索引有很多种我们主要介绍常用的几种:
    为什么添加了索引之后,会加快查询速度呢?
    图书馆::如果杂乱地放书的话检索起来就非常困难,所以将书分类,然后再建一个箱子,箱子里面放卡片,卡片里面可以按类查询,按书名查或者类别查,这样的话速度会快很多很多,这个就有点像索引。索引的好处就是提高你找到书的速度,但是正是因为你建了索引,就应该有人专门来维护索引,维护索引是要有时间精力的开销的,也就是说索引是不能乱建的,所以建索引有个原则:如果有一个字段如果不经常查询,就不要去建索引。现在把书变成我们的表,把卡片变成我们的索引,就知道为什么索引会快,为什么会有开销。

    创建索引的语法:
    创建索引:

    1. 单列索引

    单列索引是基于单个列所建立的索引,比如:
    CREATE index 索引名 on 表名(列名)

    2. 复合索引

    复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引,但是
    要求列的组合必须不同,比如:

    1
    2
    Create index emp_idx1 on emp(ename,job);
    Create index emp_idx1 on emp(job,ename);

    范例:给 person 表的 name 建立索引

    1
    create index pname_index on person(name);

    范例:给 person 表创建一个 name 和 gender 的索引

    1
    create index pname_gender_index on person(name, gender);

    索引的使用原则:

    在大表上建立索引才有意义
    在 where 子句后面或者是连接条件上的字段建立索引
    表中数据修改频率高时不建议建立索引

    十四.pl/sql 基本语法[了解]

    什么是 PL/SQL?
    PL/SQL(Procedure Language/SQL)
    PLSQL 是 Oracle 对 sql 语言的过程化扩展,指在 SQL 命令语言中增加了过程处理语句(如分支、循环等),使 SQL 语言具有过程处理能力。把 SQL 语言的数据操纵能力与过程语言的数据处理能力结合起来,使得 PLSQL 面向过程但比过程语言简单、高效、灵活和实用。
    范例 1:为职工涨工资,每人涨 10%的工资。

    1
    update emp set sal=sal*1.1

    范例 2:按职工的职称长工资,总裁涨 1000 元,经理涨 800 元,其他人员涨 400 元。
    这样的需求我们就无法使用一条 SQL 来实现,需要借助其他程序来帮助完成,也可以使用 pl/sql。

    3.1 pl/sql 程序语法

    程序语法:
    declare
    说明部分 (变量说明,游标申明,例外说明 〕
    begin
    语句序列 (DML 语句〕…
    exception
    例外处理语句
    End;

    3.2 常量和变量定义

    在程序的声明阶段可以来定义常量和变量。
    (Ⅰ)变量的基本类型就是 oracle 中的建表时字段的变量如 char, varchar2, date, number,boolean, long
    定义语法:varl char(15);
    Psal number(9,2);
    说明变量名、数据类型和长度后用分号结束说明语句。
    常量定义:married constant boolean:=true

    (Ⅱ)引用变量
    Myname emp.ename%type;
    引用型变量,即 my_name 的类型与 emp 表中 ename 列的类型一样
    在 sql中使用 into 来赋值

    1
    2
    3
    4
    5
    6
    declare
    emprec emp.ename%type;
    begin
    select t.ename into emprec from emp t where t.empno = 7369;
    dbms_output.put_line(emprec);
    end;

    (Ⅲ)记录型变量
    Emprec emp%rowtype
    记录变量分量的引用
    emp_rec.ename:=’ADAMS’;

    1
    2
    3
    4
    5
    6
    declare
    p emp%rowtype;
    begin
    select * into p from emp t where t.empno = 7369;
    dbms_output.put_line(p.ename || ' ' || p.sal);
    end;

    3.3 if 分支

    语法 1:

    1
    2
    3
    IF 条件 THEN 语句 1;
    语句 2;
    END IF;

    语法 2:

    1
    2
    3
    IF 条件 THEN 语句序列 1;
    ELSE 语句序列 2;
    END IF

    语法 3:

    1
    2
    3
    4
    IF 条件 THEN 语句;
    ELSIF 语句 THEN 语句;
    ELSE 语句;
    END IF;

    范例 1:如果从控制台输入 1 则输出我是 1

    1
    2
    3
    4
    5
    6
    7
    declare
    pnum number := #
    begin
    if pnum = 1 then
    dbms_output.put_line('我是1');
    end if;
    end;

    范例 2:如果从控制台输入 1 则输出我是 1 否则输出我不是 1

    1
    2
    3
    4
    5
    6
    7
    8
    9
    declare
    mynum number := #
    begin
    if mynum = 1 then
    dbms_output.put_line('我是1');
    else
    dbms_output.put_line('我不是1');
    end if;
    end;

    范例 3:判断人的不同年龄段 18 岁以下是未成年人,18 岁以上 40 以下是成年人,40 以上是老年人

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    declare
    mynum number := #
    begin
    if mynum < 18 then
    dbms_output.put_line('未成年人');
    elsif mynum >= 18 and mynum < 40 then
    dbms_output.put_line('中年人');
    elsif mynum >= 40 then
    dbms_output.put_line('老年人');
    end if;
    end;

    3.4 LOOP 循环语句

    语法 1:

    1
    2
    3
    4
    WHILE total <= 25000 LOOP
    total : = total + salary;
    .......
    END LOOP;

    范例:使用语法 1 输出 1 到 10 的数字

    1
    2
    3
    4
    5
    6
    7
    8
    declare
    step number := 1;
    begin
    while step <= 10 loop
    dbms_output.put_line(step);
    step := step + 1;
    end loop;
    end;

    语法 2:

    1
    2
    3
    4
    Loop
    EXIT [when 条件];
    ……
    End loop

    范例:使用语法 2 输出 1 到 10 的数字

    1
    2
    3
    4
    5
    6
    7
    8
    9
    declare
    step number := 1;
    begin
    loop
    exit when step > 10;
    dbms_output.put_line(step);
    step := step + 1;
    end loop;
    end;

    语法 3:

    1
    2
    3
    FOR I IN 1 . . 3 LOOP
    语句序列 ;
    END LOOP ;

    范例:使用语法 3 输出 1 到 10 的数字

    1
    2
    3
    4
    5
    6
    7
    declare
    step number := 1;
    begin
    for step in 1 .. 10 loop
    dbms_output.put_line(step);
    end loop;
    end;

    3.5游标 Cursor

    在写 java 程序中有集合的概念,那么在 pl/sql 中也会用到多条记录,这时候我们就要用到游标,游标可以存储查询返回的多条数据。
    语法:
    CURSOR 游标名 [ (参数名 数据类型,参数名 数据类型,…)] IS SELECT 语句;
    例如:

    1
    cursor c1 is select ename from emp;

    游标的使用步骤:

    • 打开游标: open c1; (打开游标执行查询)
    • 取一行游标的值:fetch c1 into pjob; (取一行到变量中)
    • 关闭游标: close c1;(关闭游标释放资源)
    • 游标的结束方式 exit when c1%notfound
    • 注意: 上面的 pjob 必须与 emp 表中的 job 列类型一致
      定义:pjob emp.empjob%type;

    范例 1:使用游标方式输出 emp 表中的员工编号和姓名

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    declare
    cursor pc is
    select * from emp;
    pemp emp%rowtype;
    begin
    open pc;
    loop
    fetch pc
    into pemp;
    exit when pc%notfound;
    dbms_output.put_line(pemp.empno || ' ' || pemp.ename);
    end loop;
    close pc;
    end;

    范例 2:按员工的工种涨工资,总裁 1000 元,经理涨 800 元其,他人员涨 400 元。
    备份出一张新表为myemp:

    1
    myemp;create table myemp as select * from emp;

    开始操作myemp

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    declare
    cursor pc is
    select * from myemp;
    addsal myemp.sal%type;
    pemp myemp%rowtype;
    begin
    open pc;
    loop
    fetch pc
    into pemp;
    exit when pc%notfound;
    if pemp.job = 'PRESIDENT' then
    addsal := 1000;
    elsif pemp.job = 'MANAGER' then
    addsal := 800;
    else
    addsal := 400;
    end if;
    update myemp t set t.sal = t.sal + addsal where t.empno =
    pemp.empno;
    end loop;
    close pc;
    end;

    范例 3:写一段 PL/SQL 程序,为部门号为 10 的员工涨工资。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    declare
    cursor pc(dno myemp.deptno%type) is
    select empno from myemp where deptno = dno;
    pno myemp.empno%type;
    begin
    open pc(20);
    loop
    fetch pc
    into pno;
    exit when pc%notfound;
    update myemp t set t.sal = t.sal + 1000 where t.empno = pno;
    end loop;
    close pc;
    end;

    十五.存储过程[理解]

    存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的 SQL 语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。
    创建存储过程语法:

    1
    2
    3
    4
    5
    create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
    AS
    begin
    PLSQL 子程序体;
    End;

    或者

    1
    2
    3
    4
    5
    create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
    is
    begin
    PLSQL 子程序体;
    End 过程名;

    范例:创建一个输出 helloword 的存储过程

    1
    2
    3
    4
    create or replace procedure helloworld is
    begin
    dbms_output.put_line('helloworld');
    end helloworld;

    调用存储过程
    在 plsql 中调用存储过程

    1
    2
    3
    4
    begin
    -- Call the procedure
    helloworld;
    end;

    范例 2:给指定的员工涨 100 工资,并打印出涨前和涨后的工资
    分析:我们需要使用带有参数的存储过程

    1
    2
    3
    4
    5
    6
    7
    8
    create or replace procedure addSal1(eno in number) is
    pemp myemp%rowtype;
    begin
    select * into pemp from myemp where empno = eno;
    update myemp set sal = sal + 100 where empno = eno;
    dbms_output.put_line('涨工资前' || pemp.sal || '涨工资后' ||
    (pemp.sal + 100));
    end addSal1;

    调用存储过程

    1
    2
    3
    4
    5
    begin
    -- Call the procedure
    addsal1(eno => 7902);
    commit;
    end;

    十六.存储函数[理解]

    create or replace function 函数名(Name in type, Name in type, …) return 数据类型 is
    结果变量 数据类型;
    begin

    return(结果变量);
    end 函数名;

    存储过程和存储函数的区别
    一般来讲,过程和函数的区别在于函数可以有一个返回值;而过程没有返回值。但过程和函数都可以通过 out 指定一个或多个输出参数。我们可以利用 out 参数,在过程和函数中实现返回多个值。

    范例:使用存储函数来查询指定员工的年薪

    1
    2
    3
    4
    5
    6
    7
    8
    create or replace function empincome(eno in emp.empno%type) return
    number is
    psal emp.sal%type;
    pcomm emp.comm%type;
    begin
    select t.sal into psal from emp t where t.empno = eno;
    return psal * 12 + nvl(pcomm, 0);
    end;

    使用存储过程来替换上面的例子

    1
    2
    3
    4
    5
    6
    7
    8
    income out number) is
    psal emp.sal%type;
    pcomm emp.comm%type;
    begin
    select t.sal, t.comm into psal, pcomm from emp t where t.empno
    = eno;
    income := psal*12+nvl(pcomm,0);
    end empincomep;

    调用:

    1
    2
    3
    4
    5
    6
    declare
    income number;
    begin
    empincomep(7369, income);
    dbms_output.put_line(income);
    end;

    十七.触发器[理解]

    数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时,Oracle 自动地执行触发器中定义的语句序列。

    触发器可用于

    • 数据确认
    • 实施复杂的安全性检查
    • 做审计,跟踪表上所做的数据操作等
    • 数据的备份和同步

    触发器的类型
    语句级触发器 :在指定的操作语句操作之前或之后执行一次,不管这条语句影响了多少行 。
    行级触发器(FOR EACH ROW) :触发语句作用的每一条记录都被触发。在行级触发器中使用 old 和 new 伪记录变量, 识别值的状态。
    语法:

    1
    2
    3
    4
    5
    6
    7
    8
    CREATE [or REPLACE] TRIGGER 触发器名
    {BEFORE | AFTER}
    {DELETE | INSERT | UPDATE [OF 列名]}
    ON 表名
    [FOR EACH ROW [WHEN(条件) ] ]
    begin
    PLSQL 块
    End 触发器名

    范例:插入员工后打印一句话“一个新员工插入成功”

    1
    2
    3
    4
    5
    6
    7
    create or replace trigger testTrigger
    after insert on person
    declare
    -- local variables here
    begin
    dbms_output.put_line('一个员工被插入');
    end testTrigger;

    范例:不能在休息时间插入员工

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    create or replace trigger validInsertPerson
    before insert on person
    declare
    weekend varchar2(10);
    begin
    select to_char(sysdate, 'day') into weekend from dual;
    if weekend in ('星期一') then
    raise_application_error(-20001, '不能在非法时间插入员工');
    end if;
    end validInsertPerson;

    当执行插入时会报错

    在触发器中触发语句与伪记录变量的值

    触发语句 :old :new
    Insert 所有字段都是空(null) 将要插入的数据
    Update 更新以前该行的值 更新后的值
    delete 删除以前该行的值 所有字段都是空(null)
    文章作者: LiuYu
    文章链接: https://www.liuyu.pw/posts/9f698189.html
    版权声明: 本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 LiuYuBLOG

    评论