Oracle基础知识

最新推荐文章于 2025-02-18 15:35:28 发布

yzak_jule

最新推荐文章于 2025-02-18 15:35:28 发布

阅读量1.1k

点赞数 1

文章标签： oracle insert table date constraints delete

本文链接：https://blog.youkuaiyun.com/yzak_jule/article/details/6563173

版权

--------------------------------Oracle 基础知识 -----------------------------

1. 超级用户

用户名默认口令

sys change_on_install

system manager

scott tiger

2. select * from tab ; 查询当前的登录用户所拥有的表

3. 数据类型

(1) 数值类型

number(4) 表示 -9999 到 9999

number(7 ， 2 ）表示 -99999.99 到 99999.99

number 表示 10^-38 到 10^38(10 的负 38 次幂到正 38 次幂 )

(2) 字符类型

varchar2 变长 varchar2(N) N 的最大取值为 4000 字节

char 定长 char(N) N 的最大取值为 2000 字节

例如：

将 ” 张三 ” 字符分别存入 varchar2(20) 和 char(20) 中，那么

varchar2(20) 中实际的就是 4 个字节，

而 char(20) 中是 20 个字节， ” 张三 ” 占 4 个字节，剩下 16 个字节是空格

(3) 日期类型

Date 占 7 个字节年月日时分秒

(4) 大对象类型

字符类型 clob 可存放 4G 字符内容

二进制类型 blob 超过 4G ，可存放图片，音乐等

4. Oracle 与其他数据库的区别

(1) sqlserver: 分成若干的数据库，在不同的数据库中建立数据表

(2) oracle 是分用户操作，每一个用户就是一个数据库

5. Oracle 系统函数

系统函数分为：单行函数和分组函数

单行函数：

(1) sysdate 日期函数

查看当前系统日期

select sysdate from dual;

查看当前系统日期时间

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

dual 是哑元表

两个日期相减 = 两个日期间隔的天数

计算两个日期间隔的月份

select month_bewteen(sysdate,hiredate) from emp;

236 月以后是哪个日期

select add_months(sysdate,236) from dual;

236 月以前是哪个日期

select add_months(sysdate,-236) from dual;

当前月的最后一天

select last_day(sysdate) from dual;

当前月的倒数第二天

select last_day(sysdate)-1 from dual;

当前时间开始的下一个星期一的日期 ( 中国方式 )

select next_day(sysdate,' 星期一 ') from dual;

在 scott 的 emp 表中查哪个人的日期是注册时月份的倒数第三天

select * from emp where HIREDATE in(select last_day(HIREDATE)-2 from dual);

今天是一周中的第多少天

select to_char(sysdate,'D') from dual

今天是一年中的第多少天

select to_char(sysdate,'DDD') from dual;

向数据库中插入日期格式的数据，以 Scott 的 emp 表为例

insert into emp values

(7035,'Shiki','CLERK',1000,to_date('1988-12-27','yyyy-mm-dd'),

1500,0,10);

设置系统的日期插入格式为日月年

alter session set nls_date_format='dd-mm-yyyy';

(2) 数值函数

字符串转换数值

select to_number('56')+1 from dual;

字符串连接

select to_char(15)||'123' from dual;

数值转换日期

select to_date(to_char(19801215),'yyyy-mm-dd') from dual;

大于 n 的最小整数

select ceil(5.6) from dual; 返回值为 6

小于 n 的最大整数

select floor(5.6) from dual; 返回值为 5

求余数

select mod(3,2) from dual; 返回值为 1

四舍五入 ( 可以和日期连用 )

select round(4.5) from dual;

select round(4.567,2) from dual; 小数点后保留两位小数

截断 ( 可以和日期连用 )

select trunc(4.9) from dual; 返回值为 4

select to_char(trunc(sysdate),'hh24:mi:ss') from dual;

(3) 字符函数

连接 ||

select 'hello' || 'world' from dual;

select to_char(sysdate,'yyyy')||' 年 '||to_char(sysdate,'mm')||' 月 '||to_char(sysdate,'dd')||' 日 ' from dual;

返回值为 2010 年 05 月 14 日

首字母大写

select initcap(ename) from emp;

全部字母大写

select upper(ename) from emp;

全部字母小写

select lower(ename) from emp;

左填充 / 右填充 lpad/rpad

select lpad(ename,10,' ') from emp;

将 ename 这个字段设置为 10 个字节长度，多余的位置用空格在左侧填充

select rpad(ename,10,' ') from emp;

将 ename 这个字段设置为 10 个字节长度，多余的位置用空格在右侧填充

左 / 右 / 全去空格 ltrim/rtrim/alltrim

select ltrim(ename) from emp;

替换，将大写 S 替换为小写 s

select ename,replace(ename,'S','s') from emp;

转换 translate

将大写 S 转换为 $ 符号

select ename,translate(ename,'S','$') from emp;

求 ASCII 码 ASCII 码可以和 char 值之间相互转换

ASCII—ascii char---chr

select ascii('a'),chr(97) from dual;

截取 substr

截取 ename ，从第一位开始截，截取 3 位

select ename,substr(ename,1,3) from emp;

测试长度 length

查询字符次数

查询 ename 中出现过字符 S 的次数

select instr(ename,'S') from emp;

(4) 混合函数

求最大值 greatest

select greatest(100,15,24,56,90) from dual;

求最小值 least

select least(100,15,24,56,90) from dual;

空值转换 nvl

select nvl(sal,0)+nvl(comm,0) from emp;

如果有空值，用 0 代替，

如果这样写 select sal+comm from emp; 值为空的数据将会被屏蔽，不会参与加运算

结构分支 if—else ，可用 decode 函数代替

select sal,decode(sal,800,' 低工资 ',3000,' 正常 ',5000,' 高工资 ',' 不判断 ') from emp;

按照顺序书写

800= 低工资， 3000= 正常， 5000= 高工资，否则剩下的都 = 不判断

判断正负

select sign(1) from dual; 如果判断 0 ，结果还为 0

decode 和 sign 可以联合使用

select sal,decode(sign(sal-1000),1,' 低工资 ',2,' 高工 ',' 正常 ') from emp;

分组函数：

Group by

Sum

Avg

Count

第一列值的个数

select count(1) from emp;

emp 表中不同的工作的个数

select count(distinct upper(job)) from emp;

distinct 只能去除重复行，不能去除重复列

分组函数是对分组之后的内容进行操作

平均工资大于 1300 的

select job,avg(sal) from emp group by job having avg(sal)>1300;

每个部门里每种工作工资最高和最低的

select deptno,max(sal),min(sal) from emp group by deptno,job;

------------------------------ 约束的应用 ------------------------------

1. 约束是应用在数据表的创建过程中：

主键约束，非空约束，唯一性的约束，外键约束，检查约束

create table cla(

Id number(2) primary key,

Cname varchar2(20) not null

)

create table stu(

xh number(4) primary key,

xm varchar2(20) not null,

age number(2) check (age between 10 and 90),

–- 检查的约束，检查这个字段输入的内容

birthday date,

code number(18) unique, -- 唯一性约束

sex char(2) check(sex in(‘ 男 ’,’ 女 ’)), -- 检查约束

sal number(7,2) check(sal between 500 and 1000); -- 检查约束

classid number(2) references cla(id)

–- 外键约束，括号中要引用的一定是主表中的主键或者是唯一键

)

-- 问题：创建约束的时候，没有指定约束的名字，这时系统会自动创建，那么以后修改约束的时候就会很有问题，管理起来十分麻烦，不推荐使用这种直接创建约束的方式

解决方法：先创建表，然后在添加约束

从数据字典中查看默认约束

select * from user_constraints; 当前用户下创建的所有约束

查看 stu 表中的约束

select * from user_constraints where table_name='stu';

2. 添加约束

添加主键约束

alter table stu add constraint pk_stu primary key(xh);

添加外键

alter table stu add constraint fk_stu foreign key(classid) references cla(id);

添加非空约束

alter table stu modify( xm not null);

添加检查约束

alter table stu add constraint ck_sal check(sal between 500 and 1000);

alter table stu add check(sex in(‘ 男 ’,’ 女 ’)); -- 这个表示还是没有对约束起名字

添加唯一性约束

alter table stu add constraint uq_sfz unqiue(code);

3. 删除约束

Alter table 表名 drop constraint 约束名 ;

------------------------------ 关联查询 ------------------------------

当出现多个数据表时，彼此之间存在主外键的关系

关联查询， sql 功能的实现，性能要高，连接的条件的个数 = 表的个数 -1

只要参加连接的字段存放的数据代表的含义相同，就可以连接在一起，不一定是主外键的关系

1. 内连接 ( 内查询 ) ：多表查询，完全匹配的记录的查询操作

Select dname,ename from emp, dept where dept.deptn=emp.deptno where deptno=10;

将两张表匹配成一张大表，效率最低

2. 标准的 sql 查询连接的条件要使用 on 表示

Select dname,ename from emp inner join dept on dept.deptn=emp.deptno where deptno=10;

3. 外连接的查询 ( 使用的最频繁 ) ：多表中的部分匹配的记录的查询

左外连接，右外连接，全外连接

可以用 + 号和 outer join 来实现外连接

select dept.deptno,dname,ename from emp,dept where emp.deptno(+)=dept.deptno;

+ 号的作用是选择哪个部分来作为条件匹配，以 + 号对面的记录作为匹配条件来显示

+ 号在右边表示左外连接， + 号在左边表示右外连接

左外连接

Select dept.deptno,dname,ename from dept left outer join emp on dept.deptno=emp.deptno;

右外连接

Select dept.deptno,dname,ename from dept right outer join emp on dept.deptno=emp.deptno;

全外连接

Select dept.deptno,dname,ename from dept full outer join emp on dept.deptno=emp.deptno;

4. 自连接

查询每个员工的姓名以及上级的名称

Select a.ename,b.ename from emp a,emp b where a.ename=b.mgr;

5. 子查询

查询工资比 smith 高的

select ename,sal from emp where sal>(select sal from emp where ename=upper('smith'));

查询至少有一个员工的部门

方法 1 ： ( 分组 )

select deptno from emp group by deptno having count(ename)>=1;

方法 2 ： ( 自查询 )

select deptno from dept where(select count(*) from emp where emp.deptno=dept.deptno)>=1;

显示部门里边的详细信息，以及它的人数

select a.*, (select count(*) from emp where deptno=a.deptno) as 人数 from dept a;

显示每个部门工资最高的员工的姓名

方法 1 ： ( 分组 )

select deptno,ename,sal from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);

方法 2 ： ( 自查询 )

select deptno,ename,sal from emp a where (select count(*) from emp b where a.sal>b.sal and deptno=a.deptno)=0;

找出每个部门工资最高的员工的姓名的前两个人

方法 1 ： ( 自查询 )

select deptno,ename,sal from emp a where (select count(*) from emp b where a.sal>b.sal and deptno=a.deptno)<2 order by deptno;

方法 2 ： ( 伪列 rownum)

select ename,sal from (select * from emp order by sal desc) where rownum<=2;

---------------------------3 种数据库的分页方式 -------------------------

1. Oracle 的分页操作

对于所有的 Oracle 的数据表，都包含伪列 rowid ， rownum

rowid:2 进制数据，反映的是当前的记录，在硬盘的物理位置 ( 最底层的索引列 ) ，物理位置

rownum: 逻辑的次序序列，逻辑位置

当修改表的记录时，不会修改记录的物理位置，只更改逻辑位置

Oracle 的分页操作通过伪列 rownum 来实现

select * from emp where rownum<=3 ;

select * from (select e.*,rownum as num from emp e) where num between 6 and 10;

(select e.*,rownum as num from emp e) 将这个看作是一个视图来操作

分页公式：

6 的位置：开始查询的起始记录次序 ( 当前的页数 -1)× 每页的显示的个数 +1

10 的位置：终止的次序编号，当前的页数 × 每页的个数

以上的写法只能应用于数量较小的表，

select * from (select e.*,rownum as num from emp e where rownum<16) where num between 10 and 16;

select * from

(select e.* from

(select e.*,rownum as num from emp e where rownum<16) e

where rownum>=10)

以上的写法适用于海量数据的分块查询方式的书写

2. MySQL 的分页

Select * from users limit n,m;

N: 起始的记录次序 ( 当前页数 -1)×m

M ：查询几个

3. SQLServer 的分页

Top 指令：

Select top 5 * from pubs;

Top 指令 +not in( 一定有主键或唯一键的参与 )

Select top m * from pubs where id not in(select top n id from pubs)

M: 表示每页显示的个数

N:( 当前页数 -1)×m

Id: 表示主键或唯一键

---------------------------SQL 语句的分类 -------------------------

1. DDL— 数据定义语言

包括 create,drop,alter,truncate

Drop table 表名 ----- 数据和表结构同时删除，不可恢复

truncate table 表名 ----- 截断表 ( 删除表中的数据，保留结构，不写日志文件 )

delete from 表名 -----DML 语言，数据删除，结构保留，可以恢复

2. DML— 数据操作语言，操作实际是在内存中完成，必须执行 commit ，才能影响数据库

包括 select,insert,update,delete

3. TCL— 事务控制语言

包括 commit,rollback,savepoint

Oracle 中所有的事务都是隐性的 ----- 不会自动提交

set autocommit on/off 打开或关闭自动提交

DDL 指令全部都是自动提交的

4. DCL----- 数据控制语言

grant— 分配权限，

revoke— 回收权限

-----------------------------Oracle 用户管理 ----------------------------

1. 创建用户，分配权限

只有超级用户 system 才有权进行创建的操作

切换到 system 用户

connect sys / admin as sysdba

创建用户 , 但用户还未被激活

Create user Shiki identified by t123;

用户名： Shiki 密码： t123

分配权限

grant connect,resource to Shiki;

connect ：登录权限

resource ：可以使用资源空间的权限

给予 DBA 权限

grant dba to Shiki;

分配操作

grant select on emp to Shiki;

grant delete on emp to Shiki;

如果不分配操作权限，那么就是一个傀儡用户，不能执行任何操作，即使给予了 DBA

Grant all on emp to Shiki; 给予所有操作权限

删除用户

drop user Shiki;

级联分配

Grant all on Scott.emp to Shiki with grant option;

回收权限

Revoke all on Scott.emp from Shiki;

2. 查看用户

select * from all_users; 查看 Oracle 中的所有用户

3. 修改用户

修改用户的密码

Alter user Shiki identified by aaa;

4. 解锁

Alter user scott account unlock;

5. 切换用户， connect

用户名 / 密码 as 权限

connect sys / admin as sysdba

6. connect scott/tiger as normol

7. 显示当前用户 show user;

------------------------------------- 技巧型 SQL 语句 --------------------------------------

1. 创建一个表的备份

Create table myemp as select * from emp;

2. 备份一张表的结构

Create table myemp as select * from emp where 1=2;

只要 where 条件为假就可以

3. Insert 多条数据

Insert into myemp select * from emp where deptno=30;

定义变量

Insert into myemp(empno,ename) values(&eno,’&ename’)

一般出现在后台的测试过程中

4. 集合运算符的使用

A(1,2,3,4,5,6) B(1,2,3) 两张数据表

A 交集 B intersect 123

A 并集 B

union -123456

union all -123123456

A 差集 B minus

a-b 456

b-a 空集

集合用 select 语言来查询

Select num from A intersect select num from B

select distinct xm from grade a

where

not exists(

(select subject from grade where xm='tom')

minus

(select subject from grade where xm=a.xm)

)

and a.xm<>'tom'

----------------------------------------- 数据对象 -----------------------------------------

1. 序列 ( 自动的增长列 )

sequence: 开始是任意的，可以手动指定增长的长度，可以循环，可以包含负值和某一个数据表的列联合使用，属于 DDL 定义语言

创建一个表

create table employee(

bh number(4) primary key

)

开始创建序列

Create sequence seq1

Start with 100 – 从 100 开始，自增为 1 ，

使用序列

序列中包含两个属性，

当前值： currval

下一个值： nextval

Select seq1.nextval from dual

Insert into employee values(seq1.currval)

每调用一次序列的属性 nextval ，其值会自动 +1

复杂的序列：

Create sequence seq

Start with 1000 -- 起始 1000

Increment by 3 -- 步长为 3

Maxvalue 9999 -- 最大值 9999

Minvalue 900 -- 最小值

Cycle(nocycle) 表示循环 / 不循环

order-- 按次序产生序列值

如果进行循环，将按照步长取值，且不会取最大值和最小值之间的重复值，所有的值只取一次，直到所有的值被循环一次为止

2. 视图： sql 的查询的结果，简化 sql 语句的使用

视图和物理表的关系：视图不是表，只是存放一个 sql 语句 ( 虚表 )

创建视图：

Create or replace view v_emp

Select * from emp wwhere deptno=30;

如何查询当前的用户拥有的视图

Select * from tab where tabtype=’view’;

视图分类：简单的视图和复杂视图

视图中的数据和表中的数据是同步的，表或视图发生变化时，都会相应的发生改变

(1) 简单视图 -- 创建视图的 sql 语句中，如果没有使用分组函数，就称为简单视图，与表是一种一一映射的关系，视图中的一条记录对应于表中的一条记录，这样我们可以通过修改视图实现表的修改操作，简化操作的方式

一般情况下，不需要让视图具有修改表的权限，一般是只读的

设置视图的权限：

只读的权限： with read only

Create or replace view v_emp

Select * from emp wwhere deptno=30 with read only;

修改的权限，检查视图：通过视图进行修改 with check option ，

当进行 insert ， update 操作时，会对数据进行检查，

检查创建视图的 where 语句的限制

(2) 复杂视图 — 不能通过视图修改表的视图 ()

3. 索引 ( 加快 select 语句的执行，自动执行的 )

(1) 默认索引，添加约束的时候，索引自动添加

Primary key

Unique

索引都是建立在列上，牺牲空间换取效率，一个索引相当于书的目录

(2) 普通索引 ( 配合 where 的条件 )

Create index ind_emp_ename on emp(ename,job)— 属于逻辑的索引

Rowid 是查询速度最快的索引

缺点：，用空间换速度，索引会使表的增删改的操作速度降低 ( 索引是自动维护的 )

(3) 唯一性索引：引用索引的字段只能出现一次

Create unique index idx_sal on emp(sal)

(4) 位图索引：针对数据表某个列的取值在一个很小的范围之内

Create bitmap index ind_sex on emp(sex)

Select * from emp where sex=’ 男 ’; 查询时会触发这个索引

(5) 簇索引：用于关联的查询，检查物理位置靠近的存放

(6) 反向间的索引：正常的序列是 ABC ，建立索引后按照 CBA 反序查

(7) 函数索引

Sql 语句如何使用索引：

Select * from emp; 这种查询方式称为全表扫描，无法自动触发索引

要使用索引，则 select 语句中必须存在 where 字句

例如：

Emp 表中查询 1960.5-2000.5 这个期间中的员工

Create index ind_date on emp(hiredate)

查询语句如何写？这里要有技巧

也许大多数人都会这样

Select * from emp where to_char(hiredate,’yyyymm’)>=’196005’ and

to_char(hiredate,’yyyymm’)<=’200005’

虽然这句 sql 语句包含了 where 子句，但是没用到索引，此时索引是不产生效果的

Where 字句中必须出现索引字段 ( 字段一定直接连接比较运算符 ) ，此时索引才会执行

Select * from emp where hiredate>=to_date(‘196005’,’yyyymm’) and hiredate<=to_date(‘200005’,’yyyymm’)

索引字段不能参与运算

例如： hiredate+2>=to_date(‘196005’,’yyyymm’)

相反，可以这样写： hiredate>=to_date(‘196005’,’yyyymm’)-2

4. 同义词

数据库对象的别名

Create synnonym 名字 for scott.emp — 私有同义词 , 哪个用户创建的，这个同义词只属于该用户

Create public synonym 名字 for scott.emp – 公有同义词将 emp 表公开了，其他用户都可以访问

5. 数据对象的删除

Drop 数据对象类型名字

----------------------------------------- 数据字典 ----------------------------------------

数据字典是 Oracle 当中的系统信息库，配置信息和数据信息都在里边，所有者是 sys

数据字典的权限分为，这三个是数据字典的前缀

user_

all_

dba_

后缀分为：

Tables

Object

Procdurce

Function

Package 等

常用的数据字典的查询命令

一个用户有哪些表

Select object_name from user_objects where object_type=’TABLE’;

用户可以访问的数据表

Select table_name from all_talbes where owner=’’;

系统中有哪些用户

Select * from dba_users;

--------------------------------------PL/SQL 的编程 --------------------------------------

PL ：过程化的语言，包括 if ，循环等

SQL ：结构化查询，

解决的问题：一条 SQL 语句不能完成的操作，模块化功能

1. PL/SQL 块的格式 ( 匿名的 )

Declare

-- 定义变量 ( 先定义，后使用 )

Begin

-- 业务操作，执行语句 (DML) ，可以包含事务性控制 (TCL) 提交，回滚等，但不包括 select

--DDL 和 DCL 是不能直接写在 PL/SQL 块中

Exception

-- 异常处理

End

定义变量

-- 变量名变量的类型

aa number(2); --30 个字符之内，第一个字母不能是数字

设定初值

bb number(5) default 1000;

cc number(5,2) :=12.45( 变量类型与：之间要有空格 )

dd varchar2(20) :=’Hello’;

birthday date;

islogin Boolean :=false;

pi constant number :=3.14;

利用数据库的属性定义

eno emp.empno%type;

-- 行变量的定义

erow emp%rowtype;

赋值

Date:=’01-4 月 -05’;

-- 通过 select 语句

Select sysdate into birthday from dual; --PL/SQL 块中如果出现 select ，则一定是 select…into 的格式，不可能出现 select * 这种格式

Select to_char(sysdate,’yyyymmdd’) into dd from dual;

-- 通过键盘的输入

Bb:=&number;

Ename:=’&name’;

Select sal,ename into bb,ename from emp where empno=7369; -- 赋值时一定要保证唯一性

打印输出

Dbms_output.put_line(‘bb=’||bb);

要想使用 pl/sql ，一定要添加显示控制

Set serveroutput on;

输入一个员工号，得到该员工的工资的总和 sal+comm.

set serveroutput on;

declare

sc_sum number(5);

s emp.sal%type;

c emp.comm%type;

e emp.empno%type;

begin

e:=&number;

select nvl(sal,0),nvl(comm,0) into s,c from emp where empno = e;

sc_sum := s+c;

DBMS_output.put_line(' 工资总和 ='||sc_sum);

end;

输入一个部门号，得到部门的人数，最高工资，最低工资，平均工资

set serveroutput on;

declare

d emp.deptno%type;

c number(5);

s_max emp.sal%type;

s_min emp.sal%type;

s_avg emp.sal%type;

begin

d := &number;

select count(empno),max(sal),min(sal),avg(sal) into c,s_max,s_min,s_avg from emp where deptno=d;

DBMS_output.put_line(' 部门人数 ='||c||', 最高工资 '||s_max||', 最低工资 '||s_min||', 平均工资 '||s_avg);

end;

2. 循环

Declare

I number(2);

Begin

(1) Loop 循环 loop(do_while)

i := 0;

Loop

Exit when i=10;

DBMS_output.put_line(i);

i =i+1;

end loop;

(2) While 循环

i := 6;

while(i<10 and i>5) loop

DBMS_output.put_line(i);

i:=i+1;

End loop;

(3) for 循环

for I in 1..10 loop

DBMS_output.put_line(i);

End loop;

3. if…else 语句

if 条件 then

sleif then

elsif then

else

end if

根据员工编号涨工资，如果 deptno=10 涨 10% ， =20 涨 20% ， =10 涨 30%

涨工资后达到了 >5000 ，按照 5000 计算

declare

eno number ( 5 );

dno emp.deptno% type ;

v_sal emp.sal% type ;

begin

-- 输入员工号

eno:=& number ;

select deptno,sal into dno,v_sal from emmp where empno=eno;

if dno= 10 then

if v_sal* 1.1 > 5000 then

update emp set sal= 5000 where empno=eno;

else

update emp set salsal* 1.1 where empno=eno

end if ;

elsif dno= 20 then

if v_sal* 1.1 > 5000 then

else

end if ;

elsif dno= 30 then

if v_sal* 1.1 > 5000 then

else

end if ;

else

null ; -- 处理 dno=40 的情况

end if ;

end ;

使用函数代替 if-else 语句

Update emp set sal=least(5000,sal+sal*deptno/100) here empno=eno;

4. DDL 和 DCL

Create table test(i number)

Grant select on emp to mike;

例如这些语句不能直接写在 begin 和 end 中

只能采用间接的书写

S1 varchar2(100);

S1:=’ Create table test(i number)’;

-- 将要执行的语句写在一个字符变量中

Execute immediate s1;-- 执行字符变量的内容

接收一个员工号如果该员工职位是 manager ，并且在 dallas 工作那么就给他薪金加 15% ，如果该员工职位是 clerk ，并且在 new York 工作那么就给他薪金扣除 5% ，其他情况不做处理

declare

eno number ( 4 );

v_job emp.job% type ;

v_loc dept.loc% type ;

begin

eno:=&empno;

select job,loc into v_job,v_loc from emp,dept where emp.deptno=dept.deptno and empno=eno;

dbms_output.put_line( ' 工作 ' ||v_job|| ' 检验员 ' ||v_loc);

if (v_job=upper( 'manager' ) and v_loc=upper( 'dallas' )) then

dbms_output.put_line( ' 涨工资 15%' );

elsif (v_job=upper( 'clerk' ) and v_loc=upper( 'new york' )) then

dbms_output.put_line( ' 降工资 5%' );

else

dbms_output.put_line( ' 无 ' );

end if ;

end ;

5. 异常处理

(1) 运行时的异常错误

Declare

Erow emp%rowtype;

Begin

Select * into erow from emp where empno=7369; -- 这里有可能出错

dbms_output.put_line( erow.ename||’ ’erow.sal );

当前的 emp 表中有 7369 这条数据，如果我输入 9999 ，没有这条数据的时候就会报告相应的错误，或者是 Select * into erow from emp 这样写，就会报 ” 没有匹配的数据，数据量大 ”

Exception then -- 异常处理

异常的种类：

Too_many_rows

No_data_found

End

End;

完整的程序：

Declare

Erow emp%rowtype;

Begin

Select * into erow from emp where empno=7369;

dbms_output.put_line( erow.ename||’ ’erow.sal );

Exception then

When No_data_found then

dbms_output.put_line( ‘ 没有匹配的数据 ’ );

When others then

dbms_output.put_line( ‘ 错误代码 ’||sqlcode||’, 错误描述 ’||sqlerrm );

-- 这里显示的是系统全局信息

End

(2) 自定义的异常 — 实现一种人为的程序的执行的中断

Declare

Eno number(2);

Myexp exception;

Begin

Eno:=&empno;

If eno>80 then

Raise myexp;-- 引发异常

End if;

Insert into dept values(eno,’&name’,’&loc’);

Commit;

Exception

When myexp then – 捕获编号 >80 的错误

dbms_output.put_line( ‘ 部门编号大于 80’ );

When dup_val_on_index then – 唯一约束异常

dbms_output.put_line( ‘ 部门编号已存在 ’ );

rollback;

End;

(3) 非预定义异常：将自定义的异常和系统中的异常进行绑定

Declare

Erow emp%rowtype;

Myex exception;-- 创建一个自定义异常

Pragma exception_init(myex,-1422);-- 将系统中的 1422 编号错误的异常与自定义 myex 绑定

When myex then

dbms_output.put_line( ‘ 查询的结果行太多 ’ );

(4) 异常编号的范围

系统产生的异常基本在 -20000 以内

Raise_application_error(-2001,’too rows’);

(5) 主要在异常中些什么内容

Rollback;

备份方式的执行命令

写恢复类型的代码

-------------------------------------------- 游标 -------------------------------------------

游标是内存中的一块区域，存放的是 sql 语句的执行的结果 ( 暂时的，多条记录 )

游标的关键字： cursor

1. 隐式游标：单条 sql 语句产生的结果

游标的属性：

%rowcount ：记录的行数 , 返回值是整数

%found ：找到的记录 (boolean)

%notfound: 没有找到的记录 (Boolean, 没有找到记录返回值为 true)

%isopen: 判断游标是否可用 (boolean)

2. 显式游标：应用在 select 语句，明确的访问结合集合

游标的是哟娜主要是配合循环命令， loop/for 语句

游标是单向的，只能向一个方向前进

For 循环的使用：

Declare

Cursor cc is select empno,ename,job,sal from emp where job=upper(‘manager’);

Ccrec cc%rowtype;

C emp%rowType;

Begin

For ccrec in cc loop

dbms_output.put_line( ‘ 部门编号已存在 ’ );

end loop;

End;

3. Fetch 游标指针方式

Declare

Cursor cc is select empno,ename,job,sal from emp where job=upper(‘manager’);

Ccrec cc%rowtype; 指针方式，必须创建游标变量

Begin

-- 遍历，打开游标

Open cc;

Loop;

-- 提取游标中的一行数据

Exit when cc%notcound;

Dbms_ouput.put_line(ccrec.empno||’-’ccrec.ename||’-’||ccrec.jpb||’-’|| sal)

End loop;

Close cc;

End;

输入一个部门号，给这个部门的人的每个工资上调 10% ，给他的直接上级部门工资上调 5% ，如果他没有上级，直接给他的工资上调 15%

4. 参数游标：带有参数并且不确定查询结果的游标

按照部门的编号输出部门经理的名字

Declare

Cursor c1 is select deptno from dept;-- 创建一个静态的的游标

Cursor c2(no number,pjob varchar2) is select * from emp where deptno=no and job=upper(pjob) -- 创建动态游标

-- 这种带参的游标在声明的时候，只能声明类型，不能指定类型的长度

-- 参数只能出现在 select 语句等号的右侧

C1rec c1%rowtype; -- 创建 c1 和 c2 游标对应的游标变量

C2rec c2%rowtype;

Begin

For clrec in c1 loop

For c2rec in c2(c1rec.deptno,’manager’) loop

Dbms_output.put_line(c1rec.deptno||’ ’||c2rec.ename);

End loop;

End;

5. 动态游标

Select 语句是一个动态的

对于游标而言，以 select 为执行的基础

Select * from emp;

游标类型是 select 查询语句产生结果的类型

Declare

-- 先定义一个类型

Type cur is ref cursor;-- 弱类型，它可以代表所有 select 的查询的结果

Type cur1 is ref cursor return emp%rowtype;-- 强类型

-- 创建游标变量

cura cur;-- 通过先前定义的 cur 类型定义该类型的变量 cura

-- 将来的结果记录，存放在 cura 中

Ccc emp%type;-- 创建配合游标使用的行变量

Begin

Open cura for select * from emp;-- 装载游标对象 , 执行 open 操作

/*For ccc in cura loop

Dbms_output.put_line(c1rec.deptno||’ ’||c2rec.ename);

End loop;*/

-- 对于动态游标的使用，不能采用 for 语句作为遍历操作手段，所以无法执行

-- 可以使用 loop ， while 等方式

Loop

Fetch cura into ccc;

Exit when cura%notfound;

Dbms_output.put_line(ccc.deptno||’ ’||ccc.ename);

End;

6. 给游标加锁

当产生一个游标的时候，遍历和修改不希望其他的用户更改表中的数据，要求你给游标进行加锁

加锁的操作是通过列来锁定行

Declare

Cursor c is select * from emp for update of sal;-- 锁定整个结果集

Begin

For I in c loop

Update emp set sal=sal*1.1 where current of c;-- 通过游标修改 emp 表的记录

End loop;

Commit; -- 最后一定要记得解锁

Exception

When others then

Rollback;-- 解锁

End;

---------------------------------Oracle 的数组、记录的使用 --------------------------------

1. 数组

Declare

Type t_eno is table of number(4) index by binary_integer;

V_eno t_eno;--t_eno 是一种数组类型，而 v_eno 就是数组变量

Begin

V_eno(-6) :=7782;

V_eno(0) :=7702;

V_eno(16) :=7000;

Dbms_output.put_line(V_eno(-6));

Dbms_output.put_line(V_eno(0));

Dbms_output.put_line(V_eno(16));

End;

2. 记录

Declare

Type rec is record(

Eno number(4);

Enm varchar2(20);

);

R rec;-- 定义记录类型的变量

3. 将数组加载到数组中

Select empno bulk collect into v from emp;-- 将整个结果内容插入到数组中

还可以做批量更新

Forall i in 1..100

Insert into emp(empno,ename) values(r(i).eno,r(i).enm);

------------------------------Oracle 子程序 ( 命名的 pl/sql 块 )------------------------------

1. 存储过程

格式：

Create [or replace] procedure 存储过程名 ( 参数名 in/out 数据类型 … 可以写多个参数 )

As ( 相当于 declare)

声明变量

Begin

业务的操作

Exception

异常的处理

End;

例子：

Create or replace procedure p1(pnm varchar2,psal out number)

--pnm: 调用 p1 的时候需要传入一个 varchar2 类型的参数 -

--psal: 调用 p1 的时候，设置一个 number 类型的空值，用于接受

cursor ca is select * from emp where ename=pnm;

-- 生成一个查询的结果

V_ca ca%rowtype;

Begin

For v_ca in ca loop

Psal:=v_ca.sal;

End loop;

Exception

When others then

Dbms_output.put_line(sqlerrm);

End;

如何调用存储过程 :

(1) 直接在命令行调用

Var aaa number;

Execute p1(‘SMITH’,:aaa);

(2) 编写 pl/sql 块的调用

Declare

V_s number;

Begin

p1(‘SMITH’,V_s);

dbms_output.put_line(v_s);

end;

在 java 中，使用 Callablestatement 调用存储过程

// 输入一个部门的编号，求出该部门工资最高的员工，如果工资相同，按入场的时间的先后排序

显示的列：编号，姓名，入场时间，工资

2. 函数

函数必有返回值

格式：

Create [or replace] function 函数名 ( 参数名参数类型 )

Return 返回类型

-- 变量定义

-- 返回变量返回的类型

Begin

Exception

Return 变量

End;

例子：

Create or replace function f1(pname varchar2)

Return number

V_sal number;

Begin

Select sal into v_sal from emp where ename=pname;-- 要保证姓名是唯一的数据

Return v_sal;

End;

函数的调用：

(1) Sql 下的调用：使用 dual 进行测试

Select f1('SMITH') from dual;

(2) 使用 pl/sql 块

Declare

Vs number;

Begin

Vs:=f1(‘SMITH’);

Dbms_output.put_line(vs);

End;

3. 可能遇到的问题

如果需要在存储过程或函数的返回值中得到多个操作的数据 ( 多个值，多个记录 ) ，能否返回一个结果集 ( 表的结构 )

实现动态游标充当返回值：动态游标的类型如何确定

这样就要借助程序包技术来实现

4. 程序包的使用

是一种模块化的设计，将相同类型的函数，存储过程，类型的定义封装在一起的结构

程序包的构成：

(1) 说明部分 — 类似于 java 中的接口

Create [or replace] package 程序包的名字

存储过程和函数的定义

使用全局变量

自定义的类型

End;

(2) 实现部门 — 类似于接口的实现

Create [or replace] package body 名字 ( 必须与先前定义的程序包的名字相同 )

存储过程，函数的具体的实现

使用变量，类型

End;

例子：

Create or replace package mypack as

-- 返回多条记录 ( 使用动态游标 )

Type mycur is ref cursor;-- 定义一个表类型

-- 函数的定义

Function emp_dept(dno number) return mycur;

End;

Create or replace package body mypack

Function emp_dept(dno number) return mycur

Rc mycur;

Begin

Open rc for select * from emp where deptno=dno;

Return rc;-- 结果集，也就是一张表

End;

调用程序包的存储过程：

Execute 程序包名 . 调用的名字 ( 函数或存储过程名 ) ( 参数 )

调用程序包的函数：

select 程序包名 . 调用的名字 ( 函数或存储过程名 ) ( 参数 ) from dual

select mypack.emp_dept(10) from dual;

// 编写一个程序包，做一个分页的操作，提供的参数 2 个，

第一个 pagesize 指定每页显示的记录数，第二个 page 指定要查询的页数

最后返回分好页的所有的记录

// 根据员工在各自部门的工资的排序情况，进行输出名次 ( 允许并列的情况出现 )

select deptno,ename,sal,(select count(*)+1 from emp where deptno=a.deptno and sal>a.sal) as 名次

from emp a order by deptno,sal desc;

这个题需要返回一个表，所以需要使用程序包，但是可以不用游标来返回结果集

临时表 ( 表的结构是永久的，但其中的数据不能保存 )

Create global temporary table temp_emp(

Deptno number ( 2 ) -- 部门

Ename varchar2 ( 20 ) -- 姓名

Sal number ( 7 , 2 ) -- 工资

Ord number ( 2 ) -- 名次

) on commit delete rows

可以将程序包中返回的结果集插入到临时表中

创建程序包

create or replace package emp_pack

type myrctype is ref cursor ; -- 创建一个表类型

-- 建立一个统计排序的操作

procedure p_emp;

-- 返回结果级的操作

function emp_order return myrctype ;

end ;

实现程序包

create or replace package body emp_pack

-- 编写函数的实现

function emp_order return myrctype

rc myrctype;

begin

-- 排序的操作

p_emp; -- 函数中调用存储过程

open rc for select * from temp_emp;

return rc;

end ;

-- 编写存储过程

procedure p_emp

-- 按照部门的排序

-- 创建一个部门的静态的游标

cursor cc is select * from dept;

ccrec cc% rowtype ; -- 创建部门的游标变量

-- 参数游标 , 专门负责部门的排序操作

cursor ck(no number ) is select * from emp where deptno=no order by sal desc ;

ckrec ck% rowtype ;

i number ;

j number ;

v_sal number :=- 1 ;

begin

for ccrec in cc loop -- 按照部门循环 4

i:= 0 ;

for ckrec in ck(ccrec.deptno) loop

i:=i+ 1 ; -- 记录当前的名次

-- 写入临时表

if ckrec.sal = v_sal then

null ;

else

-- 名次的取值 ( 比较工资的操作 )

j:=i; --j 是最终的名次

end if ;

insert into temp_emp values (ccrec.deptno,ckrec.ename,ckrec.sal,j);

v_sal:=ckrec.sal; -- 记录当前的工资

end loop ;

commit ;

end ;

// 创建一个程序包，生成一个游标变量，可以装载整个 emp 表，

写一个向 emp 表中插入记录的方法

删除 emp 表记录的方法，如果该员工是该部门的最后一个人，删除这个员工后还要删除这个部门

接受一个部门号，可以把该部门的员工结果集返回出来

Create or replace package emp_all as

Type cur_query_emp is ref cursor ;

Function query_emp return cur_query_emp;

Function emp_dept(dno number ) return cur_query_emp;

procedure insert_emp(eno number ,ename varchar2 ,ejob varchar2 ,emgr number ,ehiredate date ,esal number ,ecomm number ,edeptno number );

procedure delete_emp(dno number );

End ;

Create or replace package body emp_all

Function query_emp return cur_query_emp

cqe cur_query_emp;

Begin

Open cqe for select * from emp;

Return cqe;

End ;

Function emp_dept(dno number ) return cur_query_emp

Rc cur_query_emp;

Begin

Open rc for select * from emp where deptno=dno;

Return rc;

End ;

procedure insert_emp(eno number ,ename varchar2 ,ejob varchar2 ,emgr number ,ehiredate date ,esal number ,ecomm number ,edeptno number )

begin

insert into emp values (eno,ename ,ejob,emgr,ehiredate,esal,ecomm,edeptno);

end ;

End ;

一个员工号，返回他的工资和所在部门的平均工资

---------------------------------Oracle 的定时作业 --------------------------------

在 Oracle 的环境中，注册 pl/sql 的执行代码块，叫 Oracle 自动进行执行操作

应用：数据的汇总，备份 … 等操作按照设置自动执行

实现的步骤

1. 修改 Oracle 的配置文件 init.ora ，一般路径为

C:/Oracle/admin/ORA9i/pfile/init.ora.26201019350 ， Oracle 的作业是通过 SNP 进程处理的

寻找 # Job Queues ， # 为注释

如果 job_queue_processes=0 ，表示当前系统是不允许作业的，末尾设置为 10

job_queue_INTERVAL=60 表示操作间隔时间

2. 创建一张数据表

Create table test_table(

Num_col number,

Date_col date

)

3. 创建一个序列

Create sequence test_seq

Start with 1

Increment by 1

4. 编写 Oracle 要自动执行的代码，最好是写成存储过程

Create or replace procedure test_proc

Begin

Insert into test_table

values (test_seq.nextval,to_date( sysdate , 'yyyy-mm-dd' ));

commit ;

End ;

5. 对于每一个作业，都要求存在一个作业编号

Variable jobnumber number

6. 使用 Oracle 系统的作业包，注册作业的使用

Declare

jobnumber number ;

Begin

Dbms_job.submit(jobnumber, 'test_proc();' , sysdate , 'sysdate+90/(24*60*60)' );

end ;

--submit 表示注册一个作业

-- 参数为：系统分配给我一个作业编号，执行的 pl/sql 块的名字，从哪个时间开始运行，开始后间隔多少时间再次执行

Commit;

End;

7. 删除作业

Execute dbms_job.remove(21,ture,null);

------------------------------------------- 触发器 -----------------------------------------

触发器：加强约束条件额实现

实现级联操作

Create or replace trigger < 触发器的名字 >

触发器的类型 after|before

触发器的触发条件 insert|update|delete on 表名

For each now -- 是否是行级触发

When -- 表示附加的条件 , 用于改善触发器的执行的效率

Declare

Begin

Exception

End;

触发器中不能使用 commit 和 rollback

在 emp 表中添加数据，如果 deptno 不存在，要求自动添加部门编号，而且要求部门的编号 >77

create or replace trigger t_emp;

before insert on emp

for each now

when (new.deptno>77 and new.ename='JONE')

declare

n_count number;-- 是否存在的部门

begin

select count(*) into n_count from dept where deptno=:new.deptno;

if n_count=0 then

insert into dept values(:new.deptno,' 人事 ',' 北京 ');

end;

日志表， log_table, 有三列 id number,ndate date ， operation varchar2

给 emp 表添加一个触发器，

如果 insert ，在 lob_table 体现出来 new.empno,sysdate,insert

Update , 在 lob_table 体现出来 new.empno,sysdate,update

Delete, 在 lob_table 体现出来 old.empno,sysdate,delete

create or replace trigger t_log

after insert or update or delete

on emp

for each row

declare

new_empno emp.empno%type;

old_empno emp.empno%type;

begin

if inserting then

new_empno:=:new.empno;

DBMS_output.put_line(' 插入成功 ');

insert into log_table values(new_empno,sysdate,'insert');

end if;

if updating then

new_empno:=:new.empno;

DBMS_output.put_line(' 更新成功 ');

insert into log_table values(new_empno,sysdate,'update');

end if;

if deleting then

old_empno:=:old.empno;

DBMS_output.put_line(' 删除成功 ');

insert into log_table values(old_empno,sysdate,'delete');

end if;

end;

日志表， log_table, 有三列 id 序列， empno number ， ndate date ， emp 表所有字段

给 emp 表添加一个触发器，

当部门编号 =10 时，或者 dept loc= ‘ New York ’时，才允许向日志插入记录

在 emp 表中，创建一个触发器，在插入或者删除记录 ( 要求 deptno=10, 记录操作的时间，执行语句，设计员工号 )

日志的操作

创建日志表

Create table logtable(

Serial number primary key,

Dmltime date,

Dmltype varchar2(20),

Empno number

)

创建序列

Create sequence sql

创建触发器

Create or replace trigger tg

After insert or delete on emp

For each row -- 表示行触发，并表示可以使用 new 和 old ，记载了触发时每个行的状态

When (new.deptno= 10 or old.deptno= 10 )

Begin

If inserting then

Insert into logtable values(sql.nextval,sysdate,'insert',:new.deptno);

Elsif deleting then

Insert into logtable values (sql.nextval, sysdate , 'delete' ,:old.deptno);

End if ;

End ;

触发器还包括

视图触发器 ( 替代触发器 ) ：

简单视图 ( 和表是一一对应的 )

复杂视图

创建视图触发器

Create or replace trigger myvtrig instead of 表示是一个视图级触发器

Update on Myview( 视图名 )

For each row

Is cursor cc is select * from emp were deptno=:new.deptno;

Begin

End;

数据库触发器

Create or replace trigger t1

--After startup on datebase 启动

--before shutdown datebase 关闭

----------------------------------------- 联动操作 -----------------------------------------

通过触发器，实现表之间的级联操作

库存表 kc

Pid 商品编号

Pname 商品名称

Pcount 库存数量

记录：

1 ibm 10

2 sony 15

3 hp 20

销售表 ( 流水表 ) sale

Pid 商品编号

Ptype 进货还是出货

Pcount 进出货的数量

操作销售表，添加记录，自动对库存进行修改

1 in 10 1 号库存增加 10 个数量

2 out 5 2 号商品出库 5 个

创建触发器

Create or replace trigger ttt

Before insert on sale

For each row

Begin

If :new.ptype=’in’ then

Update kc set Pcount=pcount+:new.pcount where pid=:New.pid;

Else

Update kc set Pcount=pcount-:new.pcount where pid=:New.pid;

Raise_application_error(-20001,’’)

End if;

End;

---------------------------------- 锁 ---------------------------------

锁的机制，也称为事务的处理

Update emp set sal=100 where empno=8000;

用户 1 执行了这句话后，会自动上锁，用户 2 在执行这句话就会处于静止的锁定状态

增删改操作执行后都是自动上锁，这种锁称为行锁

如何使用 select 来进行上锁

Select * from emp deptno=10 for update

-- 这种锁叫做共享更新锁，对查询到的记录进行上锁

例题：订票的操作

北京 ----- 上海的火车票 3 张，但现在有 4 个订票人， 4 个人可能都在订票，有一个人肯定是订不到的，如何编写程序通过锁的机制让该程序运行正常

创建票务表

Create table ticket(

Id number ( 3 ) primary key,

route varchar2 ( 20 ) not null , -- 航线

num number ( 3 ) -- 剩余票数

)

添加记录

insert into ticket values(1,' 北京 -- 上海 ',5);

编写一个存储过程，实现买票

create or replace procedure p_db(pid number ) is --pid 航线编号

-- 订票之前要先查询一下余票

v_num number ; -- 余票变量

begin

select num into v_num from ticket where id=pid for update ; -- 对查询结果上锁

if v_num> 0 then

update ticket set num=num- 1 where id=pid;

commit ; -- 如过有票的时候，解锁

else

dbms_output.put_line( ' 卖完了 ' );

rollback ; -- 如果票卖完了，解锁

end if ;

end ;

使用 PL/SQL 的测试模式

进入 scott 用户的 sysdba 模式

connect scott/tiger as sysdba

给予 scott 用于调试程序的权限，不给权限否则会报错

grant debug any procedure,debug connect session to scott;

然后在返回 normal 模式，即可进行调试，

select num into v_num from ticket where id=pid for update wait 1 ;

wait 1 表示过 1 秒后查看程序是否能加上锁，不能加锁的话就停止加锁操作

---------------------------------Oracle 大表的处理 ---------------------------------

一张表记录很多，假设几百万条记录，如何来提高 select 的性能

即使是索引，也不能根本的解决问题

所以要使用分区的技术

将表视为一个硬盘，划出几个逻辑的区域来

在建表的时候，加上分区的技术，分区也是一种建表语句的使用

1. 范围分区

Create table emp1(

Empno number ( 5 ) primary key,

Ename varchar2 ( 20 )

)

Partition by range (empno) ( -- 创建分区

Partition p1 values less than ( 3000 ),

Partition p2 values less than ( 5000 ),

partition p3 values less than (MAXVALUE) -- 指定超过 5000 范围的

--partition p3 values less than (to_date('20100604','yyyymmdd'))

-- 可以对日期或时间进行分区

)

如果 insert into emp1 values ( 1001 , '' ) ，将会插入 p1 区内

如果 insert into emp1 values ( 6001 , '' ) ，将会插入 p3 区内

查询的时候按照分区来查询，而不使用 where

select * from emp1 partition (p1);

范围的主要应用：时间

论坛的顶帖量可以达到几 W 的，如果要查帖子，就要按照时间来分区

医院会有很多人挂号，如果要查询某个挂号单，就要按照日期来查询

-- 添加分区

alter table 表名 add partition p4 values less than ( 3000 )

2. 散列分区

Create table ttt(

deptno number ,

dname varchar2 ( 10 ),

loc varchar2 ( 10 )

)

Partition by hash (deptno) ( -- 创建分区

Partition p1, Partition p2

)

3. 复合分区

先做范围分区，再在里边做子分区

Partition by range (empno) (

subpartition by hash ()

)

4. 列表分区

Partition by list(area) (

Partition north values ( ' 上海 ' , ' 广州 ' , ' 厦门 ' ),

Partition west values ( ' 北京 ' , ' 天津 ' )

)

一般使用的最多的分区方法是范围，其次是散列

---------------------------------PL_SQLDeveloper 命令 ---------------------------------

1. ed 打开文本编辑器