Oracle 学习笔记

Oracle

一、注意事项

**登录：**使用SQL Plus登录时用户名应当这样输入——用户名 as sysdba，而不是直接使用用户名。

二、Oracle 的存储结构

Oracle数据库由一耳光数据库和至少一个数据库实例组成，它们分别用来存储数据和管理数据库文件的内存结构。

1. 物理存储结构

物理存储结构有数据文件、控制文件、联机重做日志文件组成。数据文件记录了逻辑数据库结构存储的表和索引等，事实上包含着存储的真实文件。控制文件则描述了元数据库的物理结构、数据库名称、数据文件位置等数据。联机重做日志文件由重做条目组成，记录了数据库数据的修改。

#####　２.逻辑存储结构

Oracle数据库使用逻辑存储结构对磁盘空间使用情况进行精细控制。以下是Oracle数据库中的逻辑存储结构：

• 数据块(Data blocks)：数据块对应于磁盘上的字节数。
• 范围(Extents)：范围是用于存储特定类型信息的逻辑连续数据块的具体数量。
• 段(Segments)：段是分配用于存储用户对象(例如表或索引)的一组范围。
• 表空间(Tablespaces)：数据库被分成称为表空间的逻辑存储单元。 表空间是段的逻辑容器。 每个表空间至少包含一个数据文件。

三、数据库表的操作

1.表操作

数据库中表的基本创建格式如下：

CREATE TABLE schema_name.table_name (
    column_1 data_type column_constraint,
    column_2 data_type column_constraint,
    ...
    table_constraint
 );

可以使用DESC table_name;查看表的结构

类型	定义方法	注释
NUMBER	NUMBER(p,s)	p表示精度，s是尺度,s不填或为0时表示整数，填入数字超过尺度会进行四舍五入，s运行为负（四舍五入）
FLOAT	FLOAT§	FLOAT只能指定精度，最大精度是126，且FLOAT的精度为二进制（大致等于十进制38位），公式P(d) = ⌈ 0.30103 * P(d) ⌉
CHAR	CHAR(length BYTE) CHAR(length CHAR)	默认是BYTE不指定长度默认值是1，不是可变长字符串，没有达到长度会使用空格填充，需要时要使用RTRIM()函数去除空格
NCHAR	NCHAR(length)	用于存储固定长度的Unicode字符数据，且不能使用字节长度(BYTE)
VARCHAR2	VARCHAR2(max_size BYTE) VARCHAR2(max_size CHAR)	默认字节长度，可变长字符串
NVARCHAR2	NVARCHAR2(length)	是可以存储Unicode字符的Unicode数据类型。 NVARCHAR2的字符集是在数据库创建时指定的国家字符集。
DATE	DATE	Oracle数据库有其自己的专用格式来存储日期数据。它使用7个字节的固定长度的字段，每个字段对应于世纪，年，月，日，时，分和秒来存储日期数据
TIMESTAMP	TIMESTAMP[(fractional_seconds_precision)]	fractional_seconds_precision指定SECOND字段小数部分的位数。它的范围从0到9，这意味着可以使用TIMESTAMP数据类型来存储到纳秒的精度。 如果省略fractional_seconds_precision，则默认为6。
INTERVAL	INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND	用于存储一段时间

DATE

输入和输出的标准日期格式是DD-MON-YY ,那么可以使用ALTER SESSION语句来更改NLS_DATE_FORMAT参数的值，如下所示：

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

要转换不是标准格式的日期值，可以使用带有格式字符串的TO_DATE()函数。

TO_DATE('2021-12-3', 'YYYY-MM-DD' )

除了使用TO_DATE()函数之外，还可以使用以下语法将日期值指定为字符串文字：

DATE 'YYYY-MM-DD'

TIMESTAMP

要指定TIMESTAMP文字，请使用以下格式：

TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'

如果想通过复制表的形式创建表可以使用`create ··· as (select ··· ) 的方式，示例如下：

CREATE TABLE newcustomers3  
  AS (SELECT regularcustomers.rcustomer_id, regularcustomers.rc_city, irregularcustomers.ircustomer_name  
      FROM regularcustomers, irregularcustomers  
      WHERE regularcustomers.rcustomer_id = irregularcustomers.ircustomer_id  
      AND regularcustomers.rcustomer_id < 5000);

要将表移动到回收站或将其从数据库中完全删除，使用DROP TABLE语句：

DROP TABLE schema_name.table_name
[CASCADE CONSTRAINTS | PURGE];

CASCADE CONSTRAINTS子句删除引用表中主键和唯一键的所有参照完整性约束。 如果存在这种引用完整性约束，并且不使用此子句，Oracle将返回错误并停止删除表。

如果想删除表格并且一次释放与之关联的空间，指定PURGE子句。 通过使用PURGE子句，Oracle不会将表及其依赖对象放入回收站。且PURGE不支持回滚。

2.列操作

列的添加基本语法如下：

ALTER TABLE table_name
ADD (
    column_name type constraint,
    column_name type constraint,
    ...
);

添加表中已有列时会出错，这时候就需要先进行检测，以下展示一个在添加之前检查members表是否有effective_date列 的示例：

SET SERVEROUTPUT ON SIZE 1000000 //打开输出，最大输出为1000000
DECLARE
v_col_exists NUMBER //声明一个Number类型的变量
BEGIN
SELECT count(*) INTO v_col_exists //把查询的次数赋值给变量
 FROM user_tab_cols
 WHERE column_name = 'EFFECTIVE_DATE'
   AND table_name = 'MEMBERS';

IF (v_col_exists = 0) THEN
   EXECUTE IMMEDIATE 'ALTER TABLE members ADD effective_date DATE';
ELSE
 DBMS_OUTPUT.PUT_LINE('The column effective_date already exists');
END IF;
END;

对已有的列进行修改的基本语法如下：

ALTER TABLE table_name
MODIFY ( 
    column_1 type constraint,
    column_1 type constraint,
    ...
);

删除已有列的基本语法

ALTER TABLE table_name 
DROP (column_1,column_2,...);

从大表中删除数据往往需要耗费大量资源，所以通常使用

ALTER TABLE SET UNUSED COLUMN语句来逻辑删除列，如下所示：

ALTER TABLE table_name 
SET UNUSED COLUMN column_name;

待资源空闲时可以使用以下语句删除：

ALTER TABLE table_name
DROP UNUSED COLUMNS;

也可以从DBA_UNUSED_COL_TABS视图查看每个表中未使用的列的数量：

SELECT
 *
FROM
 DBA_UNUSED_COL_TABS;

重命名列的基本语法

ALTER TABLE table_name
RENAME COLUMN column_name TO new_name;

如果不填写列名就是重命名该表名

3.截断表

删除表中所有数据即为截断表，可以使用DELETE语句删除，但数据量大时效率并不高，以下说明了Oracle TRUNCATE TABLE语句的语法：

TRUNCATE TABLE schema_name.table_name
[CASCADE]
[[ PRESERVE | PURGE] MATERIALIZED VIEW LOG ]]
[[ DROP | REUSE]] STORAGE ]

如果表通过外键约束与其他表有关系，则需要使用CASCADE子句截断关联表

通过MATERIALIZED VIEW LOG子句，可以指定在表上定义的物化视图日志是否在截断表时被保留或清除。 默认情况下，物化视图日志被保留。

STORAGE子句允许选择删除或重新使用由截断行和关联索引(如果有的话)释放的存储。 默认情况下，存储被删除。

四、Oracle CRUD

1.有条件的Oracle INSERT ALL语句

条件多项插入语句允许根据指定的条件将行插入到表中。

以下显示了条件多项插入语句的语法：

INSERT [ ALL | FIRST ]
    WHEN condition1 THEN
        INTO table_1 (column_list ) VALUES (value_list)
    WHEN condition2 THEN 
        INTO table_2(column_list ) VALUES (value_list)
    ELSE
        INTO table_3(column_list ) VALUES (value_list)
Subquery
SQL

如果指定了ALL关键字，则Oracle将在WHEN子句中评估每个条件。如果条件评估/计算为true，则Oracle执行相应的INTO子句。

但是，当指定FIRST关键字时，只会执行相应的第一个为true的INTO子句。

所有INSERT INTO子句中的列总和不得超过999。

单条件多项式插入语句最多可以有127个WHEN子句。

2.Oracle的级联删除

在现实应用中，经常从与其他表中的行具有外键关系的表中来删除行记录。

例如，想要从orders表中删除id为1的销售订单，并从order_items表中删除与订单id是1关联的所有行项目。 通常可以发出两个DELETE语句，如下所示：

-- 第一条
DELETE FROM
    orders
WHERE
    order_id = 1;

-- 第二条
DELETE FROM
    order_items
WHERE
    order_id = 1;

COMMIT WORK;
SQL

请注意，COMMIT WORK语句确保两个DELETE语句以全部或全部方式执行，以防第一条语句成功，但第二个DELETE语句失败时在order_items表中的孤行。

但是，如果知道如何正确设置表的约束，那么这是不必要的。

在这种情况下，当创建order_items表时，可以使用DELETE CASCADE选项定义一个外键约束，如下所示：

CREATE TABLE order_items 
(
    order_id   NUMBER( 12, 0 )                                , 
    -- other columns
    -- ...
    CONSTRAINT fk_order_items_orders 
    FOREIGN KEY( order_id ) 
    REFERENCES orders( order_id ) 
    ON DELETE CASCADE
);

3.Oracle MERGE

以下说明了Oracle MERGE语句的语法：

MERGE INTO target_table 
USING source_table 
ON search_condition
    WHEN MATCHED THEN
        UPDATE SET col1 = value1, col2 = value2,...
        WHERE <update_condition>
        [DELETE WHERE <delete_condition>]
    WHEN NOT MATCHED THEN
        INSERT (col1,col2,...)
        values(value1,value2,...)
        WHERE <insert_condition>;

五、Oracle 查询数据

1. ORACLE ORDER BY

可以对数据进行排序，使用方法如下：

SELECT
	...
FROM
    table_name
ORDER BY
    [column1_name | column_id] [ASC | DESC] [NULLS FIRST | NULLS LAST],
    [column2_name | column_id] [ASC | DESC] [NULLS FIRST | NULLS LAST]

ASC表示按升序排序，为默认选项，DESC表示按降序排序

NULLS FIRST在非NULL值之前放置NULL值，为默认选项，NULLS LAST在非NULL值之后放置NULL值。

2. ORACLE DISTINCT

DISTINCT子句可以用来过滤结果集中的重复行。以下说明了SELECT DISTINCT语句的语法：

SELECT DISTINCT
  column_1
FROM
  table_name;

DISTINCT 将NULL视为重复值

3. Oracle FETCH

用于进行行限制，以下说明了语法：

[ OFFSET offset ROWS]
FETCH  NEXT [ row_count | percent PERCENT ] ROWS [ ONLY | WITH TIES ]

OFFSET用于跳过前几行的数据再进行接下来的行限制，即为偏移量。

可以使用FIRST代替NEXT，ROW代替ROWS

WITH TIES返回与最后一行相同的排序键。请注意，如果使用WITH TIES，则必须在查询中指定一个ORDER BY子句。如果不这样做，查询将不会返回额外的行。

4.ORACLE GROUP BY

GROUP BY子句在SELECT语句中用于按行或表达式的值将行组合到分组汇总的行中。 GROUP BY子句为每个分组返回一行。

以下说明了Oracle GROUP BY子句的语法：

SELECT
    column_list
FROM
    T
GROUP BY
    c1,
    c2, 
    c3;

GROUP BY子句按分组列中的值(如c1，c2和c3)对行进行分组。GROUP BY子句只能包含聚合或分组的列。

如果要指定应该一次计算的多个分组级别，请使用以下ROLLUP语法：

SELECT
 column_list
FROM
 T
GROUP BY
 ROLLUP(c1,c2,c3);

5.ORACLE HAVING

以下说明了OracleHAVING子句的语法：

SELECT
    column_list
FROM
    T
GROUP BY
    c1
HAVING
    group_condition;
SQL

在这个语句中，HAVING子句紧跟在GROUP BY子句之后。如果使用不带GROUP BY子句的HAVING子句，则HAVING子句将像WHERE子句那样工作。

HAVING子句过滤分组的行，而WHERE子句过滤行。这是HAVING和WHERE子句之间的主要区别。

六、Oracle 连接表

#####　１.内连接、左连接、右连接、全连接

基本语法：

SELECT
    column_list
FROM
    T1
[ INNER | LEFT | RIGHT | OUTER ] JOIN T2 ON
    join_predicate;

三者的区别：内连接会将T1表中每一项和T2进行比较，比较匹配的想并合并在这一行，在左/右连接中T1叫左表，T2叫右表，左连接中除了能生成和内连接一样的结果集，还会把NULL值与T1中未进行匹配的值进行组合输出，右连接则相反，外连接就是左/右连接的集合。

USING语句的使用展示即同义表达：

SELECT
    column_list
FROM
    T1
LEFT JOIN T2 USING(c1,c2,c3, ...);

SELECT
    column_list
FROM
    t1
LEFT JOIN t2 ON
    t1.c1 = t2.c1
    AND t1.c2 = t2.c2
    AND t1.c3 = t2.c3
    AND ... ;

2.笛卡尔连接

给定两个集合A和B，A x B的笛卡尔乘积是所有有序对(a，b)的集合，属于A，b属于B。要在Oracle中创建表的笛卡尔乘积，可以使用CROSS JOIN子句。 以下说明了CROSS JOIN子句的语法：

SELECT
    column_list
FROM
    T1 
CROSS JOIN T2;

七、Orcale 操作符

1. UNION 、UNION ALL

下图展示两者区别：

是对列的合并

2. INTERSECT

Oracle INTERSECT运算符比较两个查询的结果，并返回两个查询相同的行。

3. MINUS

MINUS运算符比较两个查询，并返回第一个查询中但不是第二个查询输出的行。 换句话说，MINUS运算符从一个结果集中减去另一个结果集。

八、Oracle 约束

1. 主键

创建主键示例：

CREATE TABLE purchase_orders (
    po_nr NUMBER PRIMARY KEY,
    vendor_id NUMBER NOT NULL,
    po_status NUMBER(1,0) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL 
);

-- 明确地给PRIMARY KEY主键约束分配了一个名称：pk_purchase_orders。
CREATE TABLE purchase_orders (
    po_nr NUMBER,
    vendor_id NUMBER NOT NULL,
    po_status NUMBER(1,0) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    CONSTRAINT pk_purchase_orders PRIMARY KEY(po_nr)
);

-- 创建多个列主键
CREATE TABLE purchase_order_items (
    po_nr NUMBER NOT NULL,
    item_nr NUMBER NOT NULL,
    product_id NUMBER NOT NULL,  
    quantity NUMBER NOT NULL,
    purchase_unit NUMBER NOT NULL,
    buy_price NUMBER (9,2) NOT NULL,
    delivery_date DATE,
    PRIMARY KEY (po_nr, item_nr)
);

将主键约束添加到一个存在的表。要做到这一点，只需要使用ALTER TABLE语句，如下所示：

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
PRIMARY KEY (column1, column2, ...);

删除主键约束

ALTER TABLE table_name
DROP CONSTRAINT primary_key_constraint_name;

启用/禁用主键约束

要在将大量数据加载到表中或更新海量数据时需要提高性能，可以暂时禁用PRIMARY KEY约束。

要禁用或启用表的主键约束，可以使用ALTER TABLE语句：

ALTER TABLE table_name
[ DISABLE | ENABLE ] CONSTRAINT primary_key_constraint_name;

或者，

ALTER TABLE table_name
[ DISABLE | ENABLE ] PRIMARY KEY;

2.外键

创建外键示例：

CREATE TABLE child_table (
    ...
    CONSTRAINT fk_name
    FOREIGN KEY(col1, col2,...) REFERENCES parent_table(col1,col2) 
    ON DELETE [ CASCADE | SET NULL ]
);

当删除父表中的行时，使用ON DELETE子句来指定结果。

• ON DELETE CASCADE：如果父项中的一行被删除，那么子表中所有引用该行的行都将被删除。
• ON DELETE SET NULL：如果父项中的一行被删除，那么对该外键列的引用该行的子表中的所有行将被设置为NULL。

将外键约束添加到表中

如果要将外键约束添加到现有表中，请按如下所示使用ALTER TABLE语句：

ALTER TABLE child_table 
ADD CONSTRAINT fk_name
FOREIGN KEY (col1,col2) REFERENCES child_table (col1,col2);

3. NOT NULL 、UNQUE、CHECK

可以通过使用ALTER TABLE语句将NOT NULL/UNIQUE/CHECK约束添加到现有表。

ALTER TABLE table_name MODIFY ( column_name NOT NULL) 
/ UNIQUE(column_name)
/ CHECK(expression);

使用ALTER TABLE语句从列中删除NOT NULL约束，如下所示：

ALTER TABLE table_name MODIFY ( column_name NULL)

使用ALTER TABLE语句从列中删除唯一、检查约束，如下所示：

ALTER TABLE table_name
DROP CONSTRAINT unique_name / check_name;

九、PL/SQL

1.定义变量

declare
    --- 声明变量
    --- 第一种 := 赋值
    i number(2) :=10;
    --- 第二种引用型变量使用into查询语句赋值
    stu student.name%type;
    info student%rowtype;
begin
    --- 操作 
    select name into stu from student where id = 201800301323;
    select * into info from student where id = 201800301323;
    dbms_output.put_line(i);
    dbms_output.put_line(stu);
    dbms_output.put_line(info.name || '的性别为' || info.gender);
end;

2.条件判断

declare
    i number (3) := ⅈ
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;

3.循环

--- while循环
declare
	i number(2) := 1;
begin
	while i<11 loop
		dbms_output.put_line(i);
		i := i + l;
	end loop;
end;

--- exit循环
declare
	i number(2) := 1;
begin
	loop
		exit when i>10;
		dbms_output.put_line(i);
		i := i + 1;
end loop;
end;

--- for循环
declare

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

4.游标

游标可以存放多个对象、多行记录

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;

5.存储过程

存储过程就是把已经编译的PL/SQL放置在数据库中以便随时调用，一般是固定步骤的业务。

CREATE OR REPLACE PROCEDURE procedure_name  
    [ (parameter [,parameter]) ]  
IS  
    [declaration_section]  
BEGIN  
    executable_section  
[EXCEPTION  
    exception_section]  
END

IN：这是一个默认参数，它将值传递给子程序。

OUT：必须指定，它向调用者返回一个值。

IN OUT：必须指定，它将初始值传递给子程序并将更新的值返回给调用者。

6.存储函数

过程和函数的区别在于函数可以有一个返回值，而过程没有，但过程和函数都可以通过out指定一个或多个参数的打印

存储过程和存储函数的参数都不能带长度

7.触发器

触发器，就是制定一个规则，做增删改操作的时候，只要满足该规则，自动触发，无需调用。
**语句级触发器:**不包含有for each row的触发器。

**行级触发器:**包含有for each row的就是行级触发器。

for each row是为了使用:old或者:new对象或者一行记录。

create or replace trigger t1 
after
insert
on person 
declare
begin
dbms_output.put_line ( '一个新员工入职');
end;

插入新数据后打印“一个新员工入职”。

create or replace trigger t2
before
update
on emp
for each row
declare
begin
	if :old.sal> :new.sal then
		raise_application_error(-20001, '不能给员工降薪' );
	end if;
end;

修改工资前发现比之前更低发出警告。

raise_application_error（-20001~20999,‘错误提示信息’）