一、Oracle 操作集

• 一、Oracle 操作集
• 二、Oracle 数据类型
• 三、Oracle 函数
• 四、Oracle 视图
• 五、Oracle 序列
• 六、Oracle 同义词
• 七、Oracle 索引
• 八、Oracle PL/SQL
• 九、Oracle 存储函数
• 十、Oracle 存储过程
• 十一、Oracle 触发器
• 十二、Oracle 查询

---

一、数据库

• Oracle 简介

---

1. 连接数据库

# 账号`system`，密码`root`
sqlplus system/root

# `orcl`实例名 
sqlplus system/root@192.168.126.8:1521/orcl 

---

2. 常用命令

-- 启动监听     
lsnrctl start

-- 退出
quit    

---

3. 查询字符编码

-- 查询环境编码    
echo %NLS_LANG%;  

-- 查询数据库编码
SELECT USERENV('LANGUAGE') FROM DUAL;
-- AMERICAN_AMERICA.ZHS16GBK
-- `Windows`添加环境变量：set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
-- `Liunx`添加环境变量：export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

SELECT value FROM v$nls_parameters l WHERE l.PARAMETER='NLS_CHARACTERSET';
-- ZHS16GBK

---

4. 设置 Oracle 端口

• 由于 Tomcat 和 Oracle Express Edition(XE) 的默认 Http 端口都是 8080，会产生启动冲突。
• 于是重新设置 Oracle 端口。

begin  
  dbms_xdb.sethttpport('9090');  
  dbms_xdb.setftpport('0');  
end; 

---

5. 常用 SQL

-- 询当前数据库版本信息
SELECT * from v$version;

---

二、表空间

1. 查看表空间

• 查看当前用户表空间

SELECT USERNAME, DEFAULT_TABLESPACE
-- 当前用户
FROM USER_USERS;

---

• 查看表所属表空间

SELECT TABLE_NAME, TABLESPACE_NAME
-- 表名、表空间名
FROM USER_TABLES;

---

• 查看表空间大小（默认单位：byte）

-- 总大小
select tablespace_name,sum(bytes)/1024/1024 AS MB 
from dba_data_files 
group by tablespace_name;

-- 剩余大小
select tablespace_name,sum(bytes)/1024/1024 AS MB 
from dba_free_space 
group by tablespace_name;

---

SELECT a.TABLESPACE_NAME                      AS "表空间名",
       total                                  AS "总大小(B)",
       free                                   AS "剩余大小(B)",
       (total - free)                         AS "使用大小(B)",
       total / (1024 * 1024 * 1024)           AS "总大小(GB)",
       free / (1024 * 1024 * 1024)            AS "剩余大小(GB)",
       (total - free) / (1024 * 1024 * 1024)  AS "使用大小(GB)",
       ROUND((total - free) / total, 4) * 100 AS "使用率 %"
FROM (
         SELECT TABLESPACE_NAME, SUM(BYTES) free
         -- 空闲空间。
         FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME
     ) a,
     (
         SELECT TABLESPACE_NAME, sum(BYTES) total
         -- 数据文件。
         FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME
     ) b
WHERE a.TABLESPACE_NAME = b.TABLESPACE_NAME;

---

2. 创建表空间

• 创建 qshome 表空间，数据文件 qshome.dbf，初始大小 100m，递增 10m。

CREATE TABLESPACE qshome
	DATAFILE 'c:\qshome.dbf'
	SIZE 100m
	AUTOEXTEND ON
	NEXT 10m;

CREATE TABLESPACE qshome
	DATAFILE '${ORACLE_HOME}\oradata\qshome.dbf'
	SIZE 100m
	AUTOEXTEND ON
	NEXT 10m;

DATAFILE '/u01/app/oracle/product/11.2.0/db_1/oradata/qshome.dbf'

1. tablespace：表空间名称。
2. datafile：数据文件物理路径。

1. Windows 存放位置 datafile 'c:\qs.dbf'。
2. Linux 存放位置 DATAFILE '${ORACLE_HOME}\oradata\qs.dbf'。
   echo ${ORACLE_HOME}
   ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0。

3. size：表空间的初始大小（mb）。
4. autoextend on：设置自动扩容。

1. 如果存储量超过初始大小，则开始自动扩容。

5. next：设置每次扩容的空间大小（mb）。

---

3. 删除表空间

• 删除 qshome 表空间。

ALTER SYSTEM SET recyclebin = OFF DEFERRED;

DROP TABLESPACE `qshome` INCLUDING CONTENTS AND DATAFILES;

-- 清空回收站中的对象
PURGE RECYCLEBIN;
PURGE RECYCLEBIN TABLESPACE qshome;
-- 恢复指定表在被删除之前的状态
FLASHBACK TABLE <table_name> TO BEFORE DROP;

-- 禁用回收站
ALTER SYSTEM SET recyclebin = OFF DEFERRED;

-- 查看表空间索引
SELECT index_name, tablespace_name FROM dba_indexes WHERE tablespace_name='<tablespace_name>';
-- 删除索引
ALTER TABLE table_name DROP CONSTRAINT constraint_name;

---

三、用户

---

-- 当前用户
SELECT USER FROM DUAL;

SELECT USERNAME,
       USER_ID,
       CREATED,
       CASE WHEN (USERNAME = USER) THEN 1 ELSE 0 END IS_CURRENT_USER
-- 所有用户
FROM SYS.ALL_USERS;

---

1. 创建用户

1. user：创建的用户名。
2. identified by：设置用户的密码。
3. default tablesapce：指定默认表空间名称。

---

• 创建 qs 用户，密码 123456，表空间 qshome

CREATE USER qs 
IDENTIFIED BY 123456
DEFAULT TABLESPACE qshome;

---

2. 用户赋权

• 给 qs 用户赋予 dba 权限

GRANT DBA TO qs;

---

• 给 qs 用户赋予 connect连接、resource资源、dba 权限。

GRANT CONNECT,RESOURCE,DBA TO qs;

---

3. 删除用户

• 删除 qs 用户。

DROP USER qs CASCADE;

---

4. 用户解锁

• 给 scott 用户解锁。

ALTER USER scott ACCOUNT UNLOCK;   

---

5. 修改密码

• 修改 scott 用户密码

ALTER USER scott IDENTIFIED BY tiger;

---

四、数据表

---

1. 查看表

-- 当前用户所有表
SELECT TABLE_NAME, TABLESPACE_NAME, NUM_ROWS AS "行数"
FROM TABS;

-- 当前用户所有表
SELECT TABLE_NAME, TABLESPACE_NAME, NUM_ROWS AS "行数"
FROM USER_TABLES;

---

-- 查询`列名`和`类型`
SELECT COLUMN_NAME AS "列名", DATA_TYPE AS "类型", DATA_LENGTH AS "大小", NULLABLE AS "不是 null", COLUMN_ID
-- 用户表列
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = #{tableName} ;

---

-- 计算表统计
ANALYZE TABLE T_ACCOUNT COMPUTE STATISTICS;
-- 查询`表行数`
ANALYZE TABLE T_ACCOUNT ESTIMATE STATISTICS;
ANALYZE TABLE T_ACCOUNT ESTIMATE STATISTICS SAMPLE 20 PERCENT;
-- 删除统计信息
ANALYZE TABLE tablename DELETE STATISTICS;

---

2. 创建表

CREATE TABLE '表名称' (
	-- 主键
	'字段名1' '类型'('长度') primary key, 
	'字段名2' '类型'('长度'),
	'字段名...'
);

-- 创建业主表
create table t_owners ( 
	id number primary key,
	name varchar2(30),
	addressid number,
	housenumber varchar2(30),
	watermeter varchar2(30),
	adddate date,
	ownertypeid number
);
comment on column qs.t_owners.id is '业主Id';
comment on column qs.t_owners.name is '业主姓名';

---

3. 删除表

DROP TABLE '表名称';

---

4. 增加列

• Oracle 列操作

---

ALTER TABLE '表名称' ADD (
	'列名1' '类型'[DEFAULT '默认值'],
	'列名2' '类型'[DEFAULT '默认值'],
	'列名...'
);

---

-- 增加一列
alter table qs_user add `name` varchar2(20);

-- 添加`列` 
alter table qs.qs_user add syn_item CHAR(1) default 1;
alter table qs.qs_user add syn_flag CHAR(1) default 1;
-- 添加`列注释`
comment on column qs.qs_user.syn_item is '1新增、2编辑';
comment on column qs.qs_user.syn_flag is '1未同步、2已同步';

---

-- 为业主表增加两个字段
ALTER TABLE T_OWNERS ADD ( 
	REMARK VARCHAR2(20),
	OUTDATE DATE
);

---

5. 修改列

ALTER TABLE '表名称' MODIFY (
	'列名1' '类型'[DEFAULT '默认值'],
	'列名2' '类型'[DEFAULT '默认值'],
	'列名...'
);

---

-- 修改一列
alter table qs_user modify name varchar2(30);

---

-- 修改两个字段的类型
ALTER TABLE T_OWNERS MODIFY ( 
	REMARK CHAR(20),
	OUTDATE TIMESTAMP
);

---

6. 修改列名

---

ALTER TABLE '表名' RENAME COLUMN '原列名' TO '新列名';

---

ALTER TABLE T_OWNERS RENAME COLUMN OUTDATE TO EXITDATE;

---

7. 删除列

-- 删除一个字段
ALTER TABLE '表名' DROP COLUMN '列名';     
    
-- 删除多个字段
ALTER TABLE '表名' DROP ('列名1', '列名2', '列名...');     

---

-- 删除一列
alter table qs_user drop column name;

---

ALTER TABLE T_OWNERS DROP COLUMN REMARK;

---

五、数据操作

---

1. 插入数据

-- 执行`INSERT`后，一定要执行`commit`提交事务
INSERT INTO '表名' [('列名1', '列名2', '列名...')] 
VALUES ('值1', '值2', '值...'); 

---

-- `sysdate`是系统变量，用于获取当前日期
insert into T_OWNERS VALUES (1, ' 张三丰', 1, '2-2', '5678', sysdate, 1); 
commit;

• PL/SQL 点击齿轮的图标后，再点击绿色图标为 commit，红色图标为 rollback。

---

2. 修改数据

-- 执行`UPDATE`后，一定要再执行`commit`提交事务
UPDATE '表名' 
SET '列名1'='值1', '列名2'='值2', '列名...'='值...' 
WHERE '修改条件'; 

---

 -- 将`ID`为`1`的业主，登记日期更改为三天前的日期
update T_OWNERS set adddate=adddate-3 where id = 1;
commit;

---

3. 删除数据

• delete 与 truncat 区别。

1. delete 删除的数据可以 rollback。
2. delete 删除可能产生碎片，并且不释放空间。
3. truncate 是先摧毁表结构，再重构表结构。

-- 执行`DELETE`后，一定要再执行`commit`提交事务
DELETE FROM '表名' WHERE '删除条件';
COMMIT;

-- 不用执行`commit`
TRUNCATE TABLE '表名';

---

-- 删除业主`ID`为`2`的业主信息
delete from T_OWNERS where id = 2; 
commit;

---

六、数据导出导入

• 当我们使用一个数据库时，总希望数据库的内容是可靠的、正确的。
• 但由于计算机系统的故障（如：硬件故障、软件故障、网络故障、进程故障和系统故障）。
• 影响数据库系统的操作，影响数据库中数据的正确性，甚至破坏数据库，使数据库中全部或部分数据丢失。
• 因此当发生上述故障后，希望能重构这个完整的数据库。
  该处理称为数据库恢复，而要进行数据库的恢复必须要有数据库的备份工作。

---

1. 整库导出导入

• 执行命令后会在当前目录下生成一个叫 EXPDAT.DMP，此文件为备份文件。
• 如果想指定备份文件的名称，则添加 file 参数即可。

# 参数`full=y`指定整库导出
exp system/root full=y 

# 参数`file=full.dmp`指定导出文件名（默认导出文件`expdat.dmp`）
exp system/root full=y file=full.dmp

---

• 如果不指定 file 参数，则默认用备份文件 EXPDAT.DMP 进行导入。
• 如果指定 file 参数，则按照 file 指定的备份文件进行恢复。

imp system/root full=y

imp system/root full=y file=full.dmp

---

2. 按用户导出导入

exp $user/$password@$orcl 
	owner=$owner 
	file=/$backup_data 
	log=$backup_log 
	grants=y

1. $user 用户名。
2. $password 密码。
3. $orcl 实例名（192.168.31.200:1521/orcl）。
4. $owner 导出指定的用户。
5. $backup_data 文件目录。
6. $backup_log 日志目录。

---

exp system/root owner=qs file=/root/backup_data/qs.dmp log=/root/backup_log/qs.log grants=y

imp system/root fromuser=qs touser=qs_wy file=/root/backup_data/qs.dmp ignore=y 

exp system/root owner=qs file=qs.dmp 

imp system/root fromuser=qs file=qs.dmp  

exp qs/qs owner=qs file=qs_20230628.dmp log=qs_20230628.log grants=y

scp -r db:/root/qs_20230628.dmp ./

imp qshome/qshome fromuser=qs touser=qshome file=/home/oracle/qs_20230628.dmp ignore=y 

---

3. 按表导出导入

# 参数`tables`指定需要导出的表（多个表用逗号分割即可）
exp system/root tables=t_account,a_area file=backup_tables.dmp 

imp system/root tables=t_account,a_area file=backup_tables.dmp 

---

七、锁

• Oracle 锁

---

1. 悲观锁

SELECT T.* FROM `user` T FOR UPDATE; 

---

八、其他

---

• Oracle 11g安装

---