Oracle

Oracle下载

官网下载地址
https://www.oracle.com/database/technologies/oracle-database-software-downloads.html
下载完成以后两个多G，解压后6个G。

安装

双击setup.exe

配置选项

因为是win10系统，系统类选择桌面类

使用虚拟账户

设置安装位置及口令

显示要安装的程序

等待安装完成即可

安装完成

安装完成后可以在开始菜单中查看

安装完成后可以打开链接，用system和刚才设置的密码进行登录。
https://localhost:5500/em

Oracle Instance Manager实例管理

Oracle数据库是数据的物理存储， 包括：数据文件ORA或者DBF、控制文件、联机日志、参数文件 。 一个操作系统只有一个Oracle数据库，可以看作是Oracle就只有一个大数据库。

一个Oracle实例（Oracle Instance）由一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。一个数据库可以有n个实例。

实例管理，在安装时会自动新建一个叫ORCL的实例，也可以新建多个实例。

常用命令

客户端

客户端工具：sqlplus(官方推荐命令行)、sqldeveloper(免费)、pl/sql、navicat for oracle
sqlplus命令

默认用户名密码

用户是在实例下建立的，不同实例可以建相同名字的用户，建立用户时会建立一个和用户名相同的schema（模式）。

系统用户
system 普通管理员。
sys 超级管理员，拥有字典表，系统package等。

登录身份：normal、sysdba、 sysoper
normal 是普通用户
sysdba 拥有最高的系统权限，登陆后是sys
sysoper 主要用来启动、关闭数据库，sysoper登陆后用户是public

默认用户名密码

用户名	密码	登录身份
sys	change_on_install	SYSDBA或SYSOPER
system	manager	SYSDBA或NORMAL

表空间

Oracle表空间（tablespaces）是一个逻辑的概念，真正存放数据的是数据文件（data files，ORA或者DBF文件）。一个Oracle数据库能够有一个或多个表空间，而一个表空间则对应着一个或多个物理的数据库文件。表空间是Oracle数据库恢复的最小单位，容纳着许多数据库实体，如表、视图、索引、聚簇、回退段和临时段等。

表空间分类

永久表空间：表、视图、存储过程。
临时表空间：存放数据库操作中间执行的过程，执行结束后临时表空间内容就会被释放掉。
UNDO表空间：保存事务所修改的旧值，以便回滚。

表空间查看

# 查看dba的表空间
select tablespace_name from dba_tablespaces;

# 查看普通user的表空间
select tablespace_name from user_tablespaces;

SYSTEM: 存储sys的表、视图、数据库对象。
SYSAUX: example的辅助表空间.
UNDOTBS1: 存储撤销信息,例如未提交的事务.
TEMP: 存储处理sql语句处理的表和索引信息的临时空间.
USERS: 永久性表空间，存储数据库用户。
EXAMPLE: 存储数据库实例表空间.

查看用户字典

# 查看登录用户
show user
# 查看dba用户数据字典
desc dba_users
# 查看普通user用户字典
desc user_users;

根据用户查看表空间

select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username='SYSTEM';

更改用户表空间

更改用户的默认表空间和临时表空间。
alter user username default|temporary tablespace tablespace_name;

ALTER USER system DEFAULT TABLESPACE system;

创建表空间

#永久表空间创建
create tablespace autumn_tablespace datafile 'autumnfile.dbf' size 10m;
#临时表空间创建
create temporary tablespace autumn_template_tablespace tempfile 'autumntempfile.dbf' size 10m;

#删除表空间,不带including contents时保留数据文件,仅删除表空间
drop tablespace AUTUMN_TABLESPACE including contents;

表空间字典查看

#表空间字典
desc dba_data_files
#查询永久表空间列表(表空间名、表空间位置)
select file_name,tablespace_name from dba_data_files;
#查询临时表空间列表(表空间名、表空间位置)
select file_name,tablespace_name from dba_temp_files;

设置表空间状态

设置在线/离线

#设置表空间脱机状态
alter tablespace AUTUMN_TABLESPACE offline;
#查询表空间状态
select status from dba_tablespaces where tablespace_name='AUTUMN_TABLESPACE';
#设置表空间联机状态
alter tablespace AUTUMN_TABLESPACE online;

设置读写/只读状态

#设置表空间为只读状态
alter tablespace AUTUMN_TABLESPACE read only;
#查询表空间状态
select status from dba_tablespaces where tablespace_name='AUTUMN_TABLESPACE';
#设置表空间为读写状态(online即代表读写状态)
alter tablespace AUTUMN_TABLESPACE read write;

表空间增加/删除数据文件

# 给表空间再新增一个数据文件
alter tablespace AUTUMN_TABLESPACE add datafile 'AUTUMN_TABLESPACE02.dbf' size 10m;
#查询表空间文件
select file_name from dba_data_files where tablespace_name='AUTUMN_TABLESPACE';
# 删除表空间的数据文件(注意:表空间时的第一个数据文件不能删除,想删除第一个数据文件需要删除表空间)
alter tablespace AUTUMN_TABLESPACE drop datafile 'AUTUMN_TABLESPACE02.dbf';

sqlplus

免密登录

#免密登录
sqlplus /nolog   #免密登录

sqlplus / as sysdba  #直接登录为管理员
conn /as sysdba  #如果已经在sqlplus里面用conn命令重新作为管理员连接

用户名密码登录

#用户名密码登录
sqlplus  #输入 system 密码
sqlplus sys/ as sysdba  #用户名，然后输入密码
sqlplus sys/123456 as sysdba   #用户名密码登录，密码会显示出来
#其完整写法如下：ORCL为CDB
sqlplus system/orcl@127.0.0.1:1521/ORCL as sysdba

#[username/password][@IP/Server][as sysdba|sysoper]
sqlplus system/123456@orcl as sysoper  #用system以sysoper身份连接本机orcl实例。
#也可以用@IP/实例名
sqlplus system/orcl@127.0.0.1/orcl

sqlplus autumn/123456@10.10.10.21/orcl  #用普通账户连接其他oracle服务器

创建新用户

创建表空间

首先创建表空间,如果不事先创建表空间,新建用户时就会用默认表空间system和temp

#永久表空间创建,datafile可以指定表空间物理文件位置
#大小 500M，每次 5M 自动增大，最大不限制
create tablespace aeolian_tablespace datafile 'aeolianfile.dbf' size 100M autoextend on next 5M maxsize unlimited;;
#临时表空间创建
create temporary tablespace aeolian_template_tablespace tempfile 'aeoliantempfile.dbf' size 10m;

#查看用户表空间及其文件位置
select file_name,tablespace_name from dba_data_files;

创建用户

切换到指定PDB数据库ORCLPDB下面新建用户时，新建的用户为数据库PDB下面的local用户，而system作为common用户可以随意切换cdb和各个pdb。system要想在指定pdb下操作需要先切换session到指定的pdb下面。

#切换到PDB数据库，可以用dba用户执行show pdbs查看所有pdb数据库。不切换默认就是默认的CDB数据库。
alter session set container=ORCLPDB;

#创建账户密码,12c往后普通用户需要带前缀C##或c##
#创建用户,并指定默认表空间,如果不指定表空间则默认永久性表空间为system，默认临时表空间为temp
create user aeolian identified by orcl default tablespace aeolian_tablespace temporary tablespace aeolian_template_tablespace;

# 普通用户：授予connect, resource权限。
# DBA管理用户：授予connect，resource, dba权限。
grant connect,resource,dba to aeolian;

local用户登录PDB

#用户登录 username/pwd@IP:Port/PDB
sqlplus aeolian/orcl@127.0.0.1:1521/ORCLPDB

赋予用户权限

oracle内置了connect、resource、dba三种角色，可以直接把角色赋给用户，也可以不赋予角色直接赋予用户权限

#赋予某个用户其他模式下的增删改查权限
GRANT SELECT,INSERT,UPDATE,DELETE on schema.table to USERNAME;

#赋予用户创建会话权限
grant create session to USERNAME;
#赋予建表权限,但只能给当前登录用户的模式建表
grant resource to USERNAME;

#赋予建表权限,能给任何schema创建
grant create any table to USERNAME;
#赋予创建存储过程权限,能给任何schema创建
grant create any procedure to USERNAME; 

#生成语句-授予其他用户当前模式的所有表读取权限
select 'grant select on '|| tname ||' to  USERNAME;' from tab 
where tname not like 'BIN%';

#撤销指定schema下面指定table的权限
Revoke select on SCHEMA.tableName from USERNAME;
#删除用户式所有表的权限
Revoke select any table from USERNAME;

用户其他操作

-- 解锁用户
alter user autumn account unlock;

查看用户详细信息

-- 查看当前用户信息(包括用户的表空间)
select * from user_users;
-- 查看你能管理的所有用户！
select * from all_users;
-- 查看数据库里面所有用户，前提是你是有dba权限的帐号，如sys,system
select * from dba_users;


-- 查询你当前用户下,有哪些表
SELECT * FROM user_tables;
-- 查询你当前用户下, 可以访问哪些表 [也就是访问自己 和 其他用户的]
SELECT * FROM all_tables;
-- 查询当前数据库所有的表， 需要你有 DBA 的权限
SELECT * FROM dba_tables;

-- 命令:查看当前连接的cdb/pdb
show con_name;

查看权限视图

DBA_SYS_PRIVS/USER_SYS_PRIVS
DBA_ROLE_PRIVS/USER__ROLE_PRIVS
DBA_TAB_PRIVS/USER_TAB_PRIVS
DBA_COLPRIVS/USER_COL_PRIVS
DBA_ROLES
ROLE_SYS_PRIVS

Navicat需要下载instantclient更换oci.dll文件。

PLSQL

https://www.allroundautomations.com/registered-plsqldev/
下载PLSQL后配置下Oracle Home和OCI Library(没安装Oracle的需要单独下载Instant Client配置进去)。

用PLSQL连接。

数据类型

字符型

固定长度:
char(n): 最大长度2000
nchar(n): Unicode编码存储字符,一般用来存储汉字

变长:
varchar2(n): 最大长度4000
nvarchar2(n): 最大长度2000

数值型

number(p,s): p代表有效数字(包含小数),s表示为小数点后面的位置.number用的最多.
float(n): 精度是二进制位,用的较少.

日期型

date: 精确到秒,最大时间为9999年12月31日。date用的最多.
timestamp: 精确到毫秒

其他类型

blob: 4GB字节的数据，以二进制数据存放
clob: 4GB字节的数据，以字符串数据存放

DDL

oracle官网

创建表

-- 创建表
create table userinfo
(
    id number(6,0),
    username varchar2(20),
    userpwd varchar2(20),
    email varchar2(30),
    regdate date default sysdate
);
![image](https://img2022.cnblogs.com/blog/1208477/202207/1208477-20220704222628871-92040773.png)

修改表

-- 添加字段
alter table userinfo add remark varchar2(500);
-- 修改字段数据类型/长度
alter table userinfo modify userpwd number(6,0);
-- 删除字段
alter table userinfo drop column remark;
-- 修改字段名
alter table userinfo rename column email to new_email;

-- 修改表名
rename userinfo to new_userinfo;
desc new_userinfo;

删除表

-- 清除表数据
truncate table new_userinfo;
-- 删除表结构
drop table new_userinfo;

DML

CRUD

-- 新增
insert into table_name(column1,column2,...) values(value1,value2,...);

-- 修改
update userinfo set userpwd = '111111' where username = 'autumn';

-- 删除
delete from table_name where id = '3';

复制表数据

-- 建表时复制全部数据
cerate table userinfo_new as select * from userinfo;
-- 添加时复制数据,字段名可以不一样,但是对应个数和数据类型要一样
insert into table_new(column1,column2) select column1,column2 from table_old;

查询表数据

-- sqlplus用
-- 列起别名命令
COLUMN column_name HEADING new_name
-- 列格式化：列宽、数值小数位数、数值带￥符etc.
COLUMN column_name FORMAT dateformat
-- 清除列样式
COLUMN column_name CLEAR

算数运算符: +-*/
比较运算符: >,>=,<,<=,=,<>
逻辑运算符: not,and,or

decode函数相当于case when语句。
decode(columnName,val1,result1,val2,result2,...,defaultValue)

约束

非空约束

字段后面加上not null,建表时只能列级设置不能表级设置

-- 列级设置:建表时添加非空约束
create table userinfo
(
    id number(6,0),
    username varchar2(20) not null,
    userpwd varchar2(20) not null
)

-- 修改表添加非空约束,注意：数据有null值时设置不上去
alter table userinfo modify username varchar2(20) not null;

-- 删除非空约束
alter table userinfo modify username varchar2(20) null;

主键约束

非空且唯一，在列后面添加primary key

-- 列级设置:建表时添加主键约束
create table userinfo
(
    id number(6,0) primary key,
    username varchar2(20) not null,
    userpwd varchar2(20) not null
)
-- 表级设置:添加组合主键时在最后添加constraint PKNAME primary key(col1,col2);
create table userinfo_p1
(
    id number(6,0),
    username varchar2(20),
    userpwd varchar2(20),
    constraint pk_id_username primary key(id,username)
);

-- 修改表添加主键约束
alter table userinfo add constraint pk_id primary key(id);

-- 删除主键约束drop primary key [cascade级联删除其他表的外键约束]
alter table userinfo drop primary key;

外键约束

建表

-- 列级设置:建表时在从表外键字段后面加references 主表(columnid)
colname references tbl1(columnid)
-- 表级设置:建表语句最后加[on delete cascade]加了后删除tblName1的记录时会级联删除从表的数据
constraint constraintName foreign key(colName2) references tblName1(colName1) [on delete cascade]

-- 修改表时添加外键约束
alter table tblName2 add constraint fk_tblName2_fkcolumn foreign key(colName2) references tableName1(idColumn);

唯一约束

唯一约束允许有一个空值，且一个表中可以有多个唯一约束。

-- 列级设置唯一约束：建表时在字段后面加
columnName unique

-- 表级设置唯一约束：建表语句最后
constraint UN_constraintName unique(colName)

-- 修改表设置唯一约束
alter table userinfo add constraint un_constraintName unique(columnName);

检查约束

字段后面添加check(表达式)

-- 列级设置检查约束：列后面添加check（表达式）
create table userinfo_c
(
    id varchar2(10) primary key,
    username varchar2(20),
    salary number(5,0) check(salary>0)
);

-- 表级设置检查约束：建表是最后加上
create table userinfo_c
(
    id varchar2(10) primary key,
    username varchar2(20),
    salary number(5,0),
    constraint ck_salary check(salary>0)
);

-- 修改表是修改检查约束
alter table tblName add constraint constraint_name check(expressions);

查询/rename/删除约束

-- 字典表

desc user_constraints
-- 根据表名查询约束
select CONSTRAINT_NAME from  user_constraints where table_name = 'USERINFO_P1';

-- 更改约束名
alter table userinfo rename constraint pk_id to new_pk_id;

-- 启用/禁用约束 enable|disable
alter table userinfo disable constraint new_pk_id;

-- 删除约束
alter table userinfo drop constraint new_pk_id;

函数

创建函数

create [or replace] function 函数名 
([p1,p2...pn])
return datatype
is|as
    --声明部分
begin
    --PL/SQL程序块
end

删除函数

drop function 函数名

调用函数

sql调用

select 函数名 from dual;

pl/sql调用

declare
    ff varchar(20);
begin
    ff:=函数名;
    dbms_output.put_line(ff);
end;

函数demo

--先创建一个空表
create table employ(
        id number(10),
        name varchar2(20),
        sal number(10)
);

-- 再创建一个序列，自动生成id字段
create sequence idseq start with 1 maxvalue 100 minvalue 1 CYCLE nocache increment by 1 ;

-- 查看当前序列的值
select idseq.nextval from dual;
select idseq.Currval from dual;

-- 向表里插入随机数据
declare
    vname varchar2(20);
    vsal number(10);
begin
   for i in 1..100 loop
      select dbms_random.string('U',5) into vname from dual;
      select round(dbms_random.value(1000,10000)) into vsal from dual;
      insert into employ values(idseq.nextval,vname,vsal);
  end loop;
end;

函数

--创建涨薪函数：使用游标
create function  f_employ
return number
as
i number:=0;
cursor c is select * from employ where sal<5000;
begin
   for v in c loop
        update employ set sal=sal*1.05;
        i:=i+1;
   end loop;
   if i>0 then
   return 1;
   else
   return 0;
   end if;
end;

-- 创建涨薪函数：不使用游标
create function  f_employ
return number
as
begin
    update employ set sal=sal*1.05 where SAL<5000 ;
    if SQL%ROWCOUNT >0 then
        return 1;
    else
        return 0;
    end if;
end;

--调用函数：如果程序中有DML语句，则不能用sql语句调用，只能用pl/sql语句来调用
declare
f number(10);
begin
   f := f_employ;
   dbms_output.put_line(f);
end;

函数和存储过程区别

参数区别

函数有1个返回值，而存储过程是通过参数返回的可以有多个或者没有。

返回区别

函数return返回值没有返回参数模式，存储过程通过out参数返回值，如果需要返回多个参数则建议使用存储过程。

调用方式区别

sql数据操纵语句(DML/select)中只能调用函数而不能调用存储过程。
函数：一般情况下是用来计算并返回一个计算结果；
存储过程： 一般是用来完成特定的数据操作（比如修改、插入数据库表或执行某些DDL语句等等）

性能

存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。且存储过程可以减少网络交互的成本。

光标

光标cursor相当于java语言中的Result Set结果集。

光标使用顺序

-- 定义一个光标
cursor c1 is select t.name from employ t;
-- 打开光标,打开光标相当于获取result set
open c1;

-- 在loop循环中从光标中取数据放入指定变量中,每次fetch后指针指向下一条记录
loop
  fetch c1 into vname;
  exit when c1%notfound;   --当光标取不到数据时跳出loop
end loop;

-- 关闭光标
close c1;

光标属性

%found %notfound
%isopen 判断光标是否打开
%rowcount 影响的行数

光标的数量

open cursor后用完一定要close cursor,不然当光标超过默认三百个时会不能使用光标。

# 查看光标数量
show parameter cursor
# 修改光标数量,scope: memory（当前会话有效）、spfile（文件，需要重启）、both（两者同时修改）
alter system set open_cursors=400 scope=both;

带参数光标

declare 
  -- 定义带参数的光标
  cursor cemp(v_name varchar2) is select t.* from employ t where t.name like concat(concat('%',v_name),'%');
  -- 定义记录型变量
  v_emp employ%rowtype;
begin
  -- 打开光标
  open cemp('A');
  
  loop
    -- 取出员工记录
    fetch cemp into v_emp;
    exit when cemp%notfound;  -- 直到光标取不到数据退出循环
    
    -- 逐行打印员工薪资
    dbms_output.put_line(v_emp.name || '薪资是' || v_emp.sal);  
  
  end loop;
  
end;

异常

预定义异常

INVALID_CURSOR	ORA-01001	试图进行非法游标操作。
INVALID_NUMBER	ORA-01722	试图将字符串转换为数字
NO_DATA_FOUND	ORA-01403	SELECT INTO 语句中没有返回任何记录。
TOO_MANY_ROWS	ORA-01422	SELECT INTO 语句中返回多于 1 条记录。
ZERO_DIVIDE	    ORA-01476	试图用 0 作为除数。

自定义异常

declare
   v_size number(5) := -1;
   Exp_Nodata exception; -- 异常定义
begin
   if v_size < 0 then
      raise Exp_Nodata; -- 异常抛出
   end if;
exception 
   when Exp_Nodata then
      dbms_output.put_line('没有发现数据！');
   when others then
      dbms_output.put_line('其他异常！');
end;

PLSQL

plsql程序结构

plsql develop中推荐使用test window可以debug

plsql程序设计

构成： sql语句、变量
sql: select集合 -》 光标 -》 loop循环 -》 退出条件
变量：初始值是多少、最终值如何得到

基础型&引用型&记录型变量

declare
    --定义基本变量类型
    --字符串变量
    pname varchar2(20);
    
    --引用型变量: 引用列的数据类型
    ename employ.name%type;
    esal employ.sal%type;
    
    --记录型变量: 应用表的记录作为变量
    emp_rec employ%rowtype;
    
begin
    --基本数据类型赋值并打印
    pname := 'ORCL';
    dbms_output.put_line(pname);   
    
    -- 引用型变量赋值并打印
    select t.name,t.sal into ename,esal from employ t where t.id = 1;
    -- 打印姓名和薪水
    dbms_output.put_line(ename||'的薪水是'||esal);  
      
    -- 记录型变量赋值并打印
    select * into emp_rec from employ t where t.id = 2;
    -- 打印姓名和薪水
    dbms_output.put_line(emp_rec.name||'的薪水是'||emp_rec.sal);  
end;

IF判断

declare 
  -- 定义变量并赋初值
  pnum number := 1;
begin
  -- 执行if语句进行条件判断
  if pnum = 0 then dbms_output.put_line('输入的数字是0');
    elsif pnum =1 then  dbms_output.put_line('输入的数字是1');
    elsif pnum =2 then  dbms_output.put_line('输入的数字是2');
    else dbms_output.put_line('其他数字');
  end if;
end;
/

循环

while循环

declare 
  pnum number := 1;
begin
  while pnum <= 10 loop
    
    dbms_output.put_line(pnum);
    pnum:=pnum+1;
    
  end loop;  
end;

loop循环

declare 
  pnum number := 1;
begin
  loop
    -- 退出条件: 循环变量大于10
    exit when pnum > 10;  
  
    -- 打印变量
    dbms_output.put_line(pnum);
    -- 循环变量+1
    pnum:=pnum+1;
    
  end loop;  
end;

for循环

declare 
  pnum number := 1;
begin
  -- 循环连续区间可以用for循环
  for pnum in 1..10 loop
    -- 打印变量
    dbms_output.put_line(pnum);
  end loop;  
end;

备份数据

库内备份

-- 同时备份表结构和表数据
create table 新表名 as select * from 旧表名

-- 只备份表结构
create table 新表名 as select * from 旧表名 where 1=2

-- 备份数据
insert into tbl_bak select * from tbl;

-- 恢复
truncate table tbl;
insert into tbl select * from tbl_bak;

dmp备份

expdp导出

# 以dba身份登录sqlplus
sqlplus / as sysdba
# 查询DATA_PUMP_DIR文件夹
select * from dba_directories where directory_name='DATA_PUMP_DIR';
# 导出dmp文件,会自动把文件导出到DATA_PUMP_DIR下面
expdp 用户名/密码 @orcl dumpfile=DBName.dmp

impdp导出

impdp 用户名/密码 @orcl dumpfile=DBName.dmp

OJDBC

ojdbc下载

把ojdbc8.jar放入程序中，此包需要jdk1.8版本，JDBC代码如下。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DbUtil {
    public static final String DRIVERCLASS = "oracle.jdbc.driver.OracleDriver";  //加载驱动类
    //端口为1521,实例名为ORCL(在OracleInstance Manager中可查看)
    public static final String URL = "jdbc:oracle:thin:@localhost:1521:ORCL";  
    public static final String USER = "c##autumn";  //用户名
    public static final String PASSWORD = "123456";  //密码

    public static void main(String[] args) throws Exception {
        //1.加载驱动程序
        Class.forName(DRIVERCLASS);
        //2. 获得数据库连接
        Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
        //3.操作数据库，实现增删改查
        Statement stmt = conn.createStatement();

        //4.执行sql语句
        ResultSet rs = stmt.executeQuery("SELECT stuname, age FROM stu");
        //5.如果有数据，rs.next()返回true
        while(rs.next()){
            System.out.println(rs.getString("stuname")+" 年龄："+rs.getInt("age"));
        }
    }
}

常用sql

查看表、列注释

-- 查看数据库中所有表注释
select * from user_tab_comments where table_name = 'tabName' order by table_name;
-- 查看表中所有列注释
select * from user_col_comments where table_name = 'tabName' order by column_name;