Oracle相关知识(3)
程序员文章站
2022-07-13 08:05:42
...
1.关于DBlink
概念：顾名思义就是数据库的链接，相当于一个通道，当我们要跨本地数据库，访问另外一个数据库表中的数据时，本地数据库中就必须创建远程数据库的dblink,通过dblink可以使本地数据库可以像访问本地数据库一样访问远程数据库表中的数据。
注意：DBlink是作用在同一局域网内的方式
语法：
create database link 数据库链接名 connect to 用户名 identified by 密码 using '服务名';
例如：现在需要创建一个地址为 192.168.1.56 的远程链接，访问scott用户
create database link mylink connect to scott identified by tiger
using '192.168.1.56/orcl';
注意：
创建DBlink之前需要对scott进行授权：
grant create database link to scott;
如果要创建公共的link，授权时也要加上public：
grant create public database link to scott;
使用：
DBlink创建好了之后就可以直接使用了：
select * from [email protected]; -- 注意：本地的是对象.表 远程的是表@link名
同样的，我们可以将远程数据库的表定义为同义词：
create synonym emp3 from [email protected];
select * from emp3;
查询：
对于已经创建好的DBlink，我们可以进行查询：
查询创建的公共的DBlink：
select * from dba_db_links; -- 注意：这是在管理员用户下操作
查询系统中所有的DBlink
select * from all_db_links;
删除：
删除公共的：
drop public database link 远程链接名;
删除dblink:
drop database link 远程链接名;
2.关于分区表
思考：当我们表中的数据特别大的时候，如何提高使用效率、减少额外消耗（如：维护等），能否将数据分开进行存放？
概念：
为了使用户的大量的数据在读写操作和查询中速度更快，Oracle提供了对表和索引进行分区的技术，以改善大型应 用系统的性能。
优点：
1.增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可以使用
2.维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可
3.均衡I/O: 可以把不同的分区映射到磁盘以平衡I/O,改善整个系统的性能
4.改善查询性能：对分区对象的查询可以仅搜索自己需要数据所在的分区，提高检索速度
分区表常用的方法：
1.范围分区
概念：对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。
可根据序号分区，根据业务记录的创建日期进行分区等。
例如：
物料交易表，表名：material_transactions,该表可能有千万条数据记录，要求使用分区表。
1.首先准备三个表空间
2.范围分区 --- 按序号范围：
Create table material_test
(
transaction_id number primary key,
Item_id number(8) not null,
Item_description varchar2(300),
Transaction_date date not null
)Partition by range(transaction_id)
(
partition part_01 values less than(10) tablespace test1,
partition part_02 values less than(60) tablespace test2,
partition part_03 values less than(maxvalue) tablespace test3
)
3.范围分区 --- 按日期范围：
Create table material_test
(
transaction_id number primary key,
Item_id number(8) not null,
Item_description varchar2(300),
Transaction_date date not null
)Partition by range(transaction_date)
(
partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd'))
tablespace test1,
partition part_02 values less than(to_date('2010-01-01','yyyy-mm-dd'))
tablespace test2,
partition part_03 values less than(maxvalue) tablespace test3
)
2.HASA分区（散列分区）
概念：在列的取值不明确的时候可以采用此方法，HASH实际上是一种算法，当向表插入数据时，系统会
自动根据当前分区列的值计算出HASH值之后确定应该将行存放在哪个表空间中。
示例：创建一个material_test2表，结构同material_test
Partition by hash(transaction_id) 1 ----随机
(
partition part_01 tablespace test_01 ,
partition part_02 tablespace test_02 ,
partition part_03 tablespace test_03
)
3.列表分区
概念：是Oracle10G增加的功能，对表的某个列可列举的值进行分区，可以只用枚举的方式
列出分区字段的所有选项，从而达到分区的目的。
示例：
create table material_test3
(
transaction_id number primary key,
item_id number(8) not null,
item_description varchar2(300),
transaction_date date not null,
city varchar2(100)
)partition by list(city)
(
partition part_01 values('北京','天津') tablespace test1,
partition part_02 values('上海') tablespace test2,
partition part_03 values(default) tablespace test3
)
4.复合分区 -- 范围 + HASH:
概念：复合分区是一种组合。
示例：
Partition by range(transaction_date) subpartition by hash(transaction_id)
subpartitions 3 store in (test1,test2,test3)
(
partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd')),
partition part_02 values less than(to_date('2010-01-01','yyyy-mm-dd')),
partition part_03 values less than(maxvalue)
);
5.分区表操作
1.插入：
insert into material_test1 values(1,12, 'BOOKS',sysdate);
insert into material_test1 values(2,12, 'BOOKS',sysdate+30);
insert into material_test1 values(3,12, 'BOOKS',to_date('2006-05-30', 'yyyy-mm-dd'));
insert into material_test1 values(4,12, 'BOOKS',to_date('2007-06-23', 'yyyy-mm-dd'));
insert into material_test1 values(5,12, 'BOOKS',to_date('2011-02-26', 'yyyy-mm-dd'));
insert into material_test1 values(6,12, 'BOOKS',to_date('2011-04-30', 'yyyy-mm-dd'));
commit;
2.查询
select * from material_test1 partition(part_01);
select * from material_test1 partition(part_02);
select * from material_test1 partition(part_03);
select * from material_test1;
3.更新数据
update material_test1 partition(part_01) t set t.item_description='DESK'
where t.transaction_id=1;
commit;
解释：将第一个分区中的交易ID=1的记录中的item_description字段更新为"DESK"
注意：如果指定了分区，但是根据查询的记录不在该分区中时，将不会更新数据。
4.删除数据
delete from material_test1 partition(part_02) t where t.transaction_id=4;
commit;
解释：上面案例是删除第二个分区part_02中的交易记录ID为4的一条记录
注意：和更新数据一样，如果指定了分区，但是条件中的数据又不在该分区中，将不会删除任何数据。
总结：
分区表的方法有以下几种：
1.范围分区
2.HASH分区（散列分区）
3.列表分区
4.复合分区（范围 + HASH）(范围 + 列表)
3.PL/SQL语法结构
思考：如何插入指定范围内的数据？什么是PL/SQL?为什么会产生PL/SQL?
概念：首先我们要知道，SQL是结构化查询语言。是用来访问关系型数据库的一种通用语言，其执行特点是非过程化，
也就是说，我们不需要关注具体的执行方法和途径，只需要通过简单的调用相应的语句来直接取得结果即可。
其次，有些复杂的业务流程又要求相应的程序来描述：
PL/SQL通过增加了用在其它过程性语言中的结构来对SQL进行了扩展，使得它不仅是一个数据库查询语言，
也是一个编程语言。
简单的介绍：
1.PL/SQL是Oracle对SQL扩展的过程化语言。
2.PL/SQL联合了SQL的易用性、灵活性和程序化的结构化编程语言的功能
3.有些复杂的业务流程要求相应的程序来描述SQL不能实现
4.可控性更好，有分支和循环结构 如IF ... THEN,WHILE 和 LOOP.
5.效率提高，而且实现了程序共享。
语法结构：
DECLARE
变量、常量、游标、自定义异常
BEGIN
SQL语句
PL/SQL控制语句
EXCEPTION
错误发生时异常时执行的动作
END;
解释：整个PL/SQL块分三个部分
声明部分(declare开头)
执行部分(以begin开头)
异常处理部分(以exception开头)
其中执行部分是必须的，其他两个部分为可选项。
语句块可以嵌套：
declare --- 变量声明
x int;
begin --- 代码主体
declare --- 嵌套语句块
x int;
begin
x:=100;
exception --- 异常处理
end
exception --- 异常处理
end;
例如：
打印系统当前日期：
1.打开SQLplus并登陆
2.执行：set serveroutput on --- 设置打印内容可显示
3.执行如下PL/SQL
declare
current_date date:=sysdate;
begin
dbms_output.put_line(current_date)
end;
注意：要执行需要使用 /
变量的声明：
语法：变量名 类型 [constant][not null][:=value];
例如：declare
v_firstname varchar2(20);
v_last_name varchar2(20);
注意：在声明部分，每一行只能有一个变量声明，如以下的声明式错误的：
declare
v_firstname,v_lastname varchar2(20);(错误)
4.%TYPE和%ROWTYPE
概念：
使用%TYPE属性：
定义的变量的数据类型和长度与数据库表中的列保持一致
例如：declare
v_empName EMP.ENAME %type;
使用%ROWTYPE属性
定义变量类型为记录型变量，它表示和数据表一样类型的记录行。 --- 这个记录行不是单指某一个表
例如：declare
v_empRecord EMP%rowtype; --- 整个表的结构
注意：v_empRecord将与表EMP有相同的结构。如果表的定义改变了，则记录的类型也随之改变。
变量初始化：
declare
v_num number:=45;
v_counter int:=0;
注意：
1.":=" 向声明语句中的变量来指定初始值
2.使用default替代:=符号
declare
vnumberseat number default 45;
3.输出语句是：
dbms_output.put_line(变量);
5.NOT NULL和CONSTANT
1.CONSTANT:
如果变量在声明时，使用了constant，则该变量应被初始化，且以后不能改变它的值。 --- 类似于常量
declare
c_minimunstudentid constant number(5):=10000; --- 这个值 定了就不能改变
2.NOT NULL
如果在声明时，指明not null，那么应该给该变量赋初值。
例如：下面的声明则是错的
declare
v_tempvar number not null:=1;
注意：CONSTANT关键字是在变量类型之前列出的，而 NOT NULL是在数据类型之后列出的。
例如：
constant number
number not null
变量赋值案例：
declare
emp_num number(4):=1;
emp_name varchar(100);
begin
dbms_output.put_line('初始值：'||emp_num); --- 在SQL中 字符连接使用的是 ||
emp_num:=2;
dbms_output.put_line('第2次赋值：'||emp_num);
select empno,ename into emp_num,emp_name --- 注意：使用into可以在查询时赋值
from emp where empno=7788;
dbms_output.put_line(emp_num||'='||emp_name);
end;
/
记录型变量赋值：
declare
v_empRecord EMP%rowtype;
begin
select * into v_empRecord from emp where empno=7788;
dbms_output.put_line('empno='||v_empRecord.empno);
dbms_output.put_line('ename='||v_empRecord.ename);
end;
/
变量的作用范围：
declare
v_var1 number:=123;
begin
dbms_out.put_line('OUT:v_ar1:'||v_var1);
declare
v_var2 number:=456;
begin
dbms_output.put_line('IN: v_var1 :'||v_var1)
dbms_output.put_line('IN: v_var2 :'||v_var2);
END;
--1. dbms_output.put_line('IN: v_var2 :'||v_var2);
end;
注意：
内部块中的变量不能被外部块使用，去掉注释则报错
上一篇： Oracle的知识点(杂记)