Oracle常用方法

一、Oracle原理

参考文章：https://blog.csdn.net/louisjh/article/details/78754116

1、数据库文件

SGA里：共享池、数据缓存区、日志缓冲区

共享池：记录语句解析的结果，搜索结果的位置，表结构，元数据。达到80M会覆盖。第一次sql查询会把查询结果存在共享区，第二次查询的结果来自共享池，区分大小写

数据库缓存区：缓存整个表

日志缓存区：保留日志

SGA进程：https://www.cnblogs.com/future2012lg/p/3701490.html

PGA：serverprocess，应用全局区，对外暴露的一个接口，通过这个进程进行数据库操作

开机读Parameterfile，创建实例文件

三种物理硬盘文件，数据库文件：

• 数据文件dbf
• 控制文件ctl
• 日志文件log

2、Oracle表空间、段、区和块

https://www.cnblogs.com/GooPolaris/p/7920282.html

各个平台下的文件系统：

Oracle：block，没有名字

Hadoop：hdfs

Linux：ext3

Windows：ntfs network，4G上限

Windows：fat 32位，2G上限

二、常用操作

1、建表建库

创建表空间，房子

create tablespace 表空间名
datafile '路径'
size 尺寸
autoextend on next nM maxsize mM;-- 不够的话每次扩大多少，上限多少

-- 创建表空间
create tablespace mydemo
datafile '/opt/oracledb/mydemo.dbf' size 500m
autoextend on next 50m maxsize 1000m;

-- 创建用户
create user xym identified by xym default tablespace mydemo;
-- 授权用户
-- 基础权限：链接和使用资源权限，一般够用
grant connect,resource to xym;

切换用户：conn xym/xym

2、使用伪列删除重复ID的方式

使用rowid，筛选比最小rowid更大的所有数据

delete
from userinfos u
where u.rowid>(
select min(x.rowid)
from userinfos x
where x.userid=u.userid
)

3、oracle插入数据的另一种方式

insert into userinfos
select '10','dd',to_date('2000-4-6','yyyy-mm-dd'),'199.8' from dual
union
select '11','aa',to_date('2001-4-6','yyyy-mm-dd'),'194.8' from dual

4、序列

create sequence seq_users_userid2
start with 15
increment by 1
order 
cache 20
nocycle

insert into userinfos
values(seq_users_userid2.nextval,'zll',sysdate,165.5)

5、同义词

-- 使用sys同义词授权,public公开授权不需要这一步，
grant create synonym to xym;
-- 使用xym授权查询权限给xym1
grant select on userinfos to xym1;
-- 使用sys创建公开同义词，不加public就是私有授权
create public synonym userinfos for xym.userinfos;
-- 然后就可以使用xym1调用xym的表了
select * from userinfos;

6、PLSQL

6.1、循环

loop、while() loop 、for … loop

打印直角三角形的测试案例：

set serveroutput on
-- 测试
declare
names varchar(20);
begin
  names:='zhangsanfeng';
  dbms_output.put_line(names);
end;

-- 测试循环输出
declare
nu number;
begin
  nu:=1;
  loop
    dbms_output.put_line('*');
    exit when nu>=10;
    nu:=nu+1;
  end loop;
end;

-- 打印三角形
declare
nu number;
sta varchar(20);
begin
  nu:=1;
  sta:='*';
  loop
    dbms_output.put_line(sta);
    exit when nu>=5;
    nu:=nu+1;
    sta:=rpad(sta,nu,sta);
  end loop;
end;

-- 打印三角形2
declare
nu number;
sta varchar(20);
begin
  nu:=1;
  sta:='*';
  loop
    dbms_output.put_line(sta);
    exit when nu>=5;
    nu:=nu+1;
    sta:=concat(sta,'*');
  end loop;
end;

-- 打印三角形3
declare
ro number;
co number;
stars varchar(20);
begin
  stars:='*';
  ro:=1;
  loop
    co:=1;
    loop
      dbms_output.put(stars);
      exit when co>=ro;
      co:=co+1;
    end loop;
    dbms_output.put_line('');
    exit when ro>=5;
    ro:=ro+1;
  end loop;
end;

-- while 循环
declare 
ro number;
co number;
begin
  ro:=1;
  while(ro<10) loop
    co:=1;
    while(co<=ro) loop
      dbms_output.put('*');
      co:=co+1;
    end loop;
    dbms_output.put_line('');
    ro:=ro+1;
  end loop;
end;
-- for循环
declare
ro number;
co number;
begin
  for ro in 1..10 loop
    for co in 1..ro loop
      dbms_output.put('*');
    end loop;
    dbms_output.put_line('');
  end loop;
end;

6.2、if…else、if…elsif…else语句

7、游标

游标是一种 PL/SQL 控制结构；可以对 SQL 语句的处理进行显示控制，便于对表的行数据逐条进行处理。

游标不是一个数据库对象，只是存在内存中

• 声明游标
• 打开游标
• 取出结果，此时的结果取出的是一行数据，next一条一条拿
• 关闭游标

参考文章：https://blog.csdn.net/qq_34745941/article/details/81294166

7.1、测试：静态游标

# 静态游标
-- 游标取单行的数
declare
-- 声明游标
-- 保存整个表数据
cursor cur_userinfos is select * from userinfos;
-- 定义一个保存单行数据的变量
userinfos_row cur_userinfos%rowtype;
begin
  open cur_userinfos; -- 打开游标
  fetch cur_userinfos into userinfos_row; -- 从表游标中拿数据放在行游标里
  dbms_output.put_line(userinfos_row.userid||','||userinfos_row.username); -- ||代表拼接
  close cur_userinfos; -- 关闭游标
end;

-- rowcount就是看当前游标读到什么位置
declare
cursor cur_userinfos is select * from userinfos;
userinfos_row cur_userinfos%rowtype;
begin
  open cur_userinfos;
  fetch cur_userinfos into userinfos_row;
  --dbms_output.put_line(userinfos_row.userid||','||userinfos_row.username);
  dbms_output.put_line(cur_userinfos%rowcount);
  close cur_userinfos;
end;

7.2、测试：动态游标，弱类型，单元格type

-- 动态游标，弱类型
declare 
userid xym.userinfos.userid%type;--定义一个单元格，也就相当于那一列
username xym.userinfos.username%type;-- 同上
type abc is ref cursor; -- 定义一个abc的游标类型
a abc; -- 定义一个abc类型的实例a
-- 此时游标里啥也没有
begin
  open a for 'select userid,username from userinfos';-- a是活的，查的临时数据放在a里
  fetch a into userid,username;-- 把a里的数据拿出来
  dbms_output.put_line(userid||','||username);-- 输出游标里的内容
  close a;
end;

7.3、测试：动态游标，强类型，取一行rowtype

-- 动态游标，强类型
-- rowtype一行对象，一次接一行(单引号不能用)
declare 
myrows xym.userinfos%rowtype; --定义一行数据，不在乎里面有多少列
type abc is ref cursor return xym.userinfos%rowtype;
a abc;
-- 此时游标里啥也没有
begin
  open a for select * from userinfos;
  fetch a into myrows;
  dbms_output.put_line(myrows.userid||','||myrows.username);
  close a;
end;

8、函数

8.1、简单的自定义函数

对下表中某一年出生的小孩求平均身高

create or replace function func_calhigh(y varchar) return number
as
avg_high number;
begin
  select avg(userhigh) into avg_high from userinfos where to_char(birthday,'yyyy')=y;
  return avg_high;
end;
select func_calhigh('1999') from dual;

8.2、返回游标（系统级的）的自定义函数

自定义一个返回游标的函数

-- 函数：返回一个游标（系统级的）
create or replace function func_calhigh(y varchar) return sys_refcursor
as
hc sys_refcursor;
begin
  open hc for
  select avg(userhigh) from userinfos where to_char(birthday,'yyyy')=y;
  return hc;
end;

PLSQL遍历输出返回的游标

declare
lc sys_refcursor;
ah number;
begin
  lc:=func_calhigh('2021');
  fetch lc into ah;
  dbms_output.put_line(ah);
  close lc;
end;

9、存储过程

创建一个存储过程

create or replace procedure proc_calhigh(y varchar,hg out number)
as
begin
  select avg(userhigh) into hg from userinfos where to_char(birthday,'yyyy')=y;
end;

使用PLSQL输出

declare
hg number;
begin
  proc_calhigh('1999',hg);
  dbms_output.put_line(hg);
end;

10、触发器

前触发器，也就是操作前要做的事，用old，而后触发器，一般用new

设计一个场景，银行开户系统，每当用户开户时，自动往银行卡里充值100元

create table bank_user(
       userid int primary key not null,
       username varchar2(20) not null
);

create table bank_trans(
       transid int primary key not null,
       userid int not null,
       cq int not null,
       money number(10,2) not null
);
-- 外键约束
alter table bank_trans add constraint FK_user_trans foreign key(userid)
references bank_user(userid);
-- 创建自增序列
create sequence seq_user_userid;
create sequence seq_trans_transid;

10.1、后触

银行开户用户第一笔资金存入必须是100元，后触

create or replace trigger trig_openaccount
after insert on bank_user
for each row
-- for each row行级触发器，不写的话是表级的触发器，表级别的不管更新多少行都触发一次
begin
  insert into bank_trans values(seq_trans_transid.nextval,:new.userid,1,100);
end;

操作bank_user表

insert into bank_user values(seq_user_userid.nextval,'zs');

查询即可看到，bank_trans也自动触发(后)插入了数据

如果要批量插入数据怎么做？

我们可能第一反应是使用insert into bank_user values select seq_user_userid.nextval,'ls' from dual union select seq_user_userid.nextval,'ww' from dual方法插入，但是实际使用时会报错，是因为序列自增只能依次取值，union这种方法是同时进行的因此不行，所以采用先取值再依次生成序列的方法，如下：

insert into bank_user select seq_user_userid.nextval,names from
(
       select 'ls' names from dual
       union
       select 'ww' names from dual 
);

10.2、前触

设计一个场景，银行销户功能，在销户之前，要删掉对应的银行卡信息

create or replace trigger trig_desctoryaccount
before delete on bank_user
for each row 
begin
  delete from bank_trans where userid=:old.userid;
end;

删掉用户

delete from bank_user where username='zs';

11、表空间的导入导出

连接对象，把对象下的所有表都导出，所有的表和数据，把文件拷贝走，换个计算机，建个表空间和用户，导入，就可以用了，命令如下

mkdir /opt/exportoracle
# chown oracle:oinstall exportoracle/ # 这一步不需要
chmod 777 exportoracle/
exp xym/xym@orcl file=/opt/exportoracle/m.dmp

删除表空间和用户

# 删除用户
drop user xym1 cascade;
# 删除表空间以及文件
drop tablespace mydemo1 including contents and datafiles cascade constraint;
# 删除后原来的账号就不能登陆了，报如下错误
SQL> conn xym1/xym1;
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

转移到另一台机器或新机器

create tablespace mydemo1 datafile '/opt/oracledb1/mydemo1.dbf' size 500m autoextend on next 50m maxsize 1000m;
# 创建一个用户
create user xym1 identified by xym1 default tablespace mydemo1;
# 进行相应的授权
grant connect,resource to xym1;
grant create synonym to xym1;

导入备份数据

imp xym1/xym1@orcl file=/opt/exportoracle/m.dmp full=y