Oracle 学习笔记
前言
本贴内容纪录Oracle课程中的学习笔记，和Oracle的课后作业，以及数据库相关课程的学习笔记，笔记部分使用实例代码记录，不记详细语法。
用户管理
1.如果新安装数据库后，忘了oracle用户的密码，可以用哪些方法重新设置密码？（从系统用户sys和普通用户scoot两个方面去考虑）,针对两种不同类型的用户，给出解决问题的具体思路和代码。
分两种情况考虑:
一种是忘记普通用户（如scoot）
用SYS (或SYSTEM)用户登录: CONN SYS/PASS_WORD AS SYSDBA;
使用修改语句：ALTER USER user_name IDENTIFIED BY newpass;
注意：密码不能纯数字或以数字开头，否则会出现ORA-00988: 口令缺失或无效
另一种是管理员用户（SYS或System）
可以使用ORAPWD.EXE 工具修改密码。
开始菜单->运行->输入CMD,打开命令提示符窗口，输入如下命令：
orapwd file=D:\oracle\product\10.2.0\db_1\database\pwdctcsys.ora password=Wut1234或者使用系统模式登陆
cmd中输入 sqlplus /nolog
使用系统登入
conn / as sysdba;
输入
alter user sys identified by Wut1234;
用户以及权限管理
创建用户
CREATE USER user1 IDENTIFIED BY user1;授权 WITH ADMIN OPTION 传递
GRANT CREATE SESSION,CREATE TABLE TO user1 WITH ADMIN OPTION;收回授权
REVOKE CREATE SESSION,CREATE TABLE FROM user1;将scott模式下的emp表的SELECT，UPDATE，INSERT权限授予user1用户（带 WITH GRANT OPTION 参数）。
CONN scott/tiger
GRANT SELECT,INSERT,DELETE ON emp TO user1 WITH GRANT OPTION;回收用户user1授予的scott模式下的emp表的SELECT，UPDATE，INSERT权限。
CONN scott/tiger@orcl
REVOKE SELECT,UPDATE,INSERT ON emp FROM user1;表空间
建表空间
CREATE TABLESPACE datastudent
DATAFILE '%ORACLE_HOME%\database\datastudent.dbf'
SIZE 100M REUSE
UNIFORM SIZE 128K;建临时表空间
CREATE TEMPORARY TABLESPACE tempstudent
TEMPFILE '%ORACLE_HOME%\database\tempstudent.dbf'
SIZE 120M REUSE
UNIFORM SIZE 128K;建用户并指定表空间
CREATE USER class_mgr IDENTIFIED BY manager DEFAULT TABLESPACE datastudent TEMPORARY TABLESPACE tempstudent;授权用户指定权限
GRANT SELECT ON scott.emp TO class_mgr;
GRANT UPDATE(deptno,dname) ON scott.dept to class_mgr;手动修改表空间大小
ALTER TABLESPACE data_ts1
ADD DATAFILE
'%ORACLE_HOME%\database\data_ts2.dbf' SIZE|RESIZE 10M; //增加/减少开启/关闭自动扩容
ALTER DATABASE DATAFILE
'%ORACLE_HOME%\database\data_ts2.dbf'
AUTOEXTEND ON|OFF NEXT 5M MAXSIZE 100M; //开关删除表空间-删除临时
ALTER TABLESPACE temp_ts1
DROP TEMPFILE
'%ORACLE_HOME%\database\temp_ts2.dbf';删除表空间-删除数据文件及约束
DROP TABLESPACE temp_ts1
INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;表空间离线/上线
ALTER TABLESPACE tablespace_name ONLINE|OFFLINE离线状态的表空间是不能进行数据访问的,所对应的所有数据文件也都处于脱机状态。
表空间读写状态
ALTER TABLESPACE tbs_name
READ ONLY|READ WRITE //可读 读写Oracle数据类型
常用数据类型：
- VARCHAR2：可变长字符串类型，≤4KB
- DATE ：固定长度（7B）的日期型。注意存储与使用格式
- TIMESTAMP：时间戳类型，可更精确的存储与表示时间
- NUMBER：数字型，可存放实型和整型
- FLOAT:浮点型，可存放实型
不常用数据类型：
- BLOB 大二进制对象类型，≤4GB
- CLOB 大字符串对象类型，≤4GB
- NCLOB：多字节字符集的CLOB，≤4GB
- BFILE：外部二进制文件类型，只读，大小与OS有关
- ROWID：存储记录行的物理地址（2进制表示），固定为10个字节
特殊类型Date（注意）
用来存放日期时间类型数据，用7个字节分别描述年、月、日、时、分、秒。 语法格式：
date_field DATE;
缺省格式为DD-MON-YY，分别对应日、月、年。
例17-JUN-02。（月份的表达要用英文单词的缩写格式）。
日期的格式可以设置为中文格式，例如17-六月-2002。
to_date('2017-03-02','yyyy-mm-dd')
SQL> select to_date('2017-03-02','yyyy-mm-dd') from dual;Oracle衍生Date类型-TIMESTAMP 不展开赘述
表管理
表是真正存储各种各样数据的对象，由行和列组成。行有时也称为记录，列有时也称为字段或域。设计数据库时，要决定它包括哪些表，每个表中包含哪些列，每列的数据类型等。
在表中创建列时，必须为其指定数据类型，列的数据类型决定了数据的取值、范围和存储格式。
建表
Create Table XS
(XH VARCHAR2 (10) NOT NULL,
XM VARCHAR2 (20) NOT NULL,
ZYM VARCHAR2 (20),
XB VARCHAR2(2) NOT NULL,
CSSJ Date NOT NULL,
ZXF Number(2) DEFAULT 01,
BZ Varchar(200)
);使用查询建表
为了保存原始数据以便于恢复或是得到一个与源表一样结构的表，可通过子查询创建表。
CREATE TABLE XS_JSJ
AS SELECT * FROM XS WHERE ZYM='计算机' ;例为计算机专业的学生创建一个备份表XS_JSJ。
表管理
主要为对列及其属性的修改
增加列
ALTER TABLE XS ADD
( JXJ NUMBER(1),
DJSM VARCHAR2(40) DEFAULT '奖金1000元'
);修改列
ALTER TABLE XS
MODIFY ( DJSM DEFAULT '奖金800元' );删除列
ALTER TABLE XS
DROP COLUMN DJSM;修改表名
Alter Table XS Rename To XSCopy;或
Alter Table XS
RENAME XS TO XSCopy;修改字段名
Alter Table xscopy
Rename Column BZ To BZSM;删除表
DROP TABLE XS;表的删除闪回
FLASHBACK TABLE XS TO BEFORE DROP;注意：管理员sys是没有回收站，请尽量不要在sys用户下创建表
语法：FLASHBACK TABLE 表名TO BEFORE DROP;
此命令可用于恢复误删除的表、视图和索引。
彻底删除表
无法闪回
DROP TABLE XS PURGE;临时表
临时表的特点：
- 多用户操作的独立性：对于使用同一张临时表的不同用户，ORACLE都会分配一个独立的临时表，这样就避免了多个用户在对同一张临时表操作时发生交叉，从而保证了多个用户操作的并发性和独立性；
- 数据的临时性：既然是临时表，顾名思义，存放在该表中的数据是临时性的。ORACLE根据你创建临时表时指定的参数（On Commit Delete Rows / On Commit Preserve Rows），自动将数据TRUNCATE掉。
会话级临时表
会话级的临时表因为这这个临时表中的数据和你的当前会话有关系，当你当前SESSION不退出的情况下，临时表中的数据就还存在，而当你退出当前SESSION的时候，临时表中的数据就全部没有了，当然这个时候你如果以另外一个SESSION登陆的时候是看不到另外一个SESSION中插入到临时表中的数据的。即两个不同的SESSION所插入的数据是互不相干的。当某一个SESSION退出之后临时表中的数据就被截断（truncate table，即数据清空）了。会话级的临时表创建方法：
Create Global Temporary Table Table_Name
(Col1 Type1,Col2 Type2...) On Commit Preserve Rows；事务级临时表
事务级临时表是指该临时表与事务相关，当进行事务提交或者事务回滚的时候，临时表中的数据将自行被截断，其他的内容和会话级的临时表的一致（包括退出SESSION的时候，事务级的临时表也会被自动截断）。事务级临时表的创建方法：
Create Global Temporary Table Table_Name
(Col1 Type1,Col2 Type2...) On Commit Delete Rows；两中类型临时表的区别
会话级临时表采用on commit preserve rows；而事务级则采用on commit delete rows；用法上，会话级别只有当会话结束临时表中的数据才会被截断，而且事务级临时表则不管是commit、rollback或者是会话结束，临时表中的数据都将被截断
约束
主键约束，外键约束，特殊约束等
手机号约束
ALTER TABLE EMPLOYEES
ADD ( CONSTRAINT "CH_PHONE_CHECK" CHECK (phonenumber between '000000000000' and '999999999999') ) ;索引
在Oracle 中，索引是为了加速对表中元组的检索而创建的一种分散存储结构； 索引是对表而建立的，由除存放表的数据页面以外的索引页面组成，独立于被索引的表。
注意：索引增加了查询的速度，但是会降低插入，修改，删除的速度，故县插入数据，后创建索引。
- 索引中存储了记录的关键字和ROWID
- 索引在逻辑上和物理上都与相关表的数据无关
- 索引需要单独的存储空间。否则使用表所在表空间
- 创建或删除一个索引不会影响基于数据表的数据库应用或其他索引
- 创建索引后一般会增加数据更新操作的执行时间
B-树索引(B-tree index)
B*树索引存储结构类似书的索引结构，有分支和叶两种类型的存储数据块（叶子块数据是排序的，从左向右递增； 在分支块和根块中放的是索引的范围 ），分支块相当于书的大目录，叶块相当于索引到的具体的书页。
对于取值范围很大的列应当创建B树索引。
位图索引
位图索引储存主要用来节省空间，减少ORACLE对数据块的访问，它采用位图偏移方式来与表的行ID号对应。( 每一个BIT（值是1或0）对应着一个ROWID。是1表示着BIT对应的ROWID有值；)
对于取值范围很小的列应当创建位图索引。
条件
- 索引的表或簇必须在自己的模式中；
- 必须在要索引的表上具有INDEX权限；
- 必须具有CREATE ANY INDEX权限。
创建索引
CONN scott/tiger@orcl
CREATE INDEX kc_kcm_index
ON kc(kcm DESC)
TABLESPACE my_index;默认情况下，索引中数据按升序(ASC)排列
视图
视图(view)，也称虚表, 不占用物理空间，这个也是相对概念，因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候, 只是重新执行SQL.
视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。
视图的定义存在数据库中，与此定义相关的数据并没有再存一份于数据库中。通过视图看到的数据存放在基表中。
视图看上去非常象数据库的物理表，对它的操作同任何其它的表一样。当通过视图修改数据时，实际上是在改变基表中的数据；相反地，基表数据的改变也会自动反映在由基表产生的视图中。由于逻辑上的原因，有些Oracle视图可以修改对应的基表，有些则不能（仅仅能查询）。
需要给予视图权限
-- 给予创建视图权限
conn system
GRANT create view TO McswAdmin;创建视图
create or replace view vw_Users_Buy
as
select "Users_Id","Goods_Id","Tr_Num"
from Transaction_Log
order by "Tr_Id";PL/SQL程序块
PL/SQL程序块是PL/SQL程序的基本单元，按照指定的方式，进行定义的一段程序。
概念等内容参考 PL/Sql语句块
示例：
--序列
create sequence Users_sequence
increment by 1
start with 10000020
nomaxvalue
nocycle
nocache
--触发器
CREATE TRIGGER Users_idadd
BEFORE INSERT ON Users
FOR EACH ROW
WHEN(NEW."Users_Id" is null) --当输入ID为空时启动触发器
BEGIN
select Users_sequence.nextval into :new."Users_Id" from sys.dual;
END;--创建good_check函数，检查商品的对应游戏物品ID
CREATE OR REPLACE FUNCTION good_check
(f_goodsid Goods."Goods_Id"%type
)
RETURN Goods."Goods_Game_id"%TYPE
AS
v_g_rname Goods."Goods_Game_id"%TYPE;
BEGIN
select "Goods_Game_id" into v_g_rname FROM Goods
where "Goods_Id"=f_goodsid;
RETURN v_g_rname ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有找到对应游戏物品!');
END good_check;
--调用good_check输入Goods_Id和Goods_Game_id检查商品的对应游戏物品ID
select good_check('ces01') from dual;