Oracle18c 快速上手

最新推荐文章于 2023-05-18 11:28:59 发布

十二翼堕落天使

最新推荐文章于 2023-05-18 11:28:59 发布

阅读量693

点赞数

分类专栏：数据库文章标签： oracle

本文链接：https://blog.csdn.net/XY1790026787/article/details/119185777

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

Oracle

https://docs.oracle.com/en/database/oracle/oracle-database/18/index.html

一、前言

也是的确很久都没更新过东西了，连第一句都似乎难以启齿。那么稍微梳理一下，本文主要还是面向跟教主一样 “手里握着是锤子，看什么都是钉子” 的读者，通过收集资料和其它数据库进行类比，寻找一个了解 Oracle 合适的姿势，毕竟初学者发现的不友好大多都是姿势不对，虽然…似乎离目标还任重而道远。
目前教主文档的阅读进度还很满，因此大部分理解基本都是基于根据结果去猜测原因，所以如果有误的地方还望指出纠正。
当然本文也会引用很多文档、博客、视频来作为参考。

二、安装

构建镜像

https://github.com/oracle/docker-images/blob/main/OracleDatabase/SingleInstance/README.md

git clone https://github.com/oracle/docker-images.git

cd OracleDatabase/SingleInstance/dockerfiles/

./buildContainerImage.sh -v 18.4.0 -x

运行容器

version: "3"
services:
  oracle: 
    image: oracle/database:18.4.0-xe
    environment: 
      - ORACLE_PWD=0000
    ports: 
      - 1521:1521
      - 5500:5500

docker-compose up -d

docker exec -it <CONTAINER_ID> bash

常见问题

SQL *Plus 登录失败：ORA-01017: invalid username/password; logon denied

https://www.jianshu.com/p/7367b8882092

在这里插入图片描述

cd $ORACLE_HOME/bin

ls -lh | awk '{if($9 ~ /oracle/) print}'

chmod 6751 oracle

OEM 5500 端口无法访问

如果不是刚需，请务必跳过。

https://docs.oracle.com/en/database/oracle/oracle-database/18/xeinl/making-oracle-database-em-express-available-remote-clients.html

在这里插入图片描述

SQL> EXEC dbms_xdb.setlistenerlocalaccess(false);

Adobe Flash Player 已于2020-12-31 终止支持。360浏览器兼容模式尚且可用。

三、Quick Start

创建数据库（Pluggable Database）

# 以 sys 用户登录 CDB$ROOT 容器
sqlplus sys/0000@//localhost:1521/XE AS sysdba

-- 创建 PDB 以及 DBA 用户
CREATE PLUGGABLE DATABASE db1 ADMIN USER pdbadmin IDENTIFIED BY pdbadmin DEFAULT TABLESPACE users DATAFILE '/opt/oracle/oradata/XE/db1/users01.dbf' SIZE 100M FILE_NAME_CONVERT=('/opt/oracle/oradata/XE/pdbseed','/opt/oracle/oradata/XE/db1');

-- 启动 PDB
ALTER PLUGGABLE DATABASE db1 OPEN;

-- 自动启动 PDB
ALTER PLUGGABLE DATABASE db1 SAVE STATE;

-- 以 sys 用户切换到新建的 db1 容器
CONN sys/0000@//localhost:1521/db1 AS sysdba;

-- 授予 DBA 权限
GRANT dba TO pdbadmin;

-- 以 DBA 登录 PDB
CONN pdbadmin/pdbadmin@//localhost:1521/db1;

-- 执行脚本创建示例 HR
@/opt/oracle/product/18c/dbhomeXE/demo/schema/human_resources/hr_main_new.sql

# 配置服务名监听
vim $ORACLE_HOME/network/admin/tnsnames.ora

db1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = db1)
    )
  )

示例

在这里插入图片描述

四、基本概念

Schema（模式）

Oracle中很多概念难以理解可能就卡在它对于 Schema 的实现和大部分厂商的数据库都不太一样，具体可参考维基百科：

https://zh.wikipedia.org/wiki/%E7%B6%B1%E8%A6%81_(%E8%B3%87%E6%96%99%E5%BA%AB)

可以非常浅显的总结为以下几点：

Schema 是标准而 Database 是实现。
MySQL对 Schema 的实现为 Database。
Oracle 对 Schema 的实现为 User（比如HR）
MSSQL和PG中既有 Database 也有 Schema（比如dbo和public）。
MSSQL 2000中的 Schema 和 User 隐式同一，更像Oracle；MSSQL 2005 后Schema 独立于User而存在，更像 PG。
Oracle 12c 引入了Multitenant Architecture（多租户架构），Pluggable Database（可插拔数据库）更像Database。

表空间

-- 创建表空间
CREATE TABLESPACE t_data DATAFILE '/opt/oracle/oradata/XE/db1/t_data001.dbf' SIZE 100M;

-- 删除表空间
DROP TABLESPACE t_data INCLUDING CONTENTS AND DATAFILES CACADE CONSTRAINTS;

https://bbs.csdn.net/topics/390217815

多租户架构

在这里插入图片描述

详细视频链接：https://www.bilibili.com/video/BV12i4y1t7iZ?p=1

五、语法整理

注意事项

Oracle 表别名不能加 AS ！

Oracle 列名不用 "" 限定时不区分大小写，但Navicat 建表时区分大小写！

Oracle 存在隐式数据类型转换！

Oracle 需要手动提交事务！

Oracle 没有实现NOW() 函数，用SYSDATE替代！

Oracle JDBC 字符串格式jdbc:oracle:thin:@//<HOST>:<PORT>/<SERVICE_NAME>

Oracle 日期默认参数会取当前日期值！

Oracle 标识符（表名、列名、索引名、序列名等）最大长度为30个字符！

容器管理

-- 查看当前容器
show con_name;

-- 查看是否是 CDB
SELECT name, cdb, con_id FROM v$database;

-- 切换容器
alter session set container=CDB$ROOT;
alter session set container=<PDB>;

-- 查看所有 PDB
SHOW pdbs;

-- 打开 | 关闭 PDB
ALTER PLUGGABLE DATABASE <PDB> <OPEN | CLOSE>;
ALTER PLUGGABLE DATABASE ALL <OPEN | CLOSE>;

-- 删除 PDB
ALTER PLUGGABLE DATABASE <PDB> close;
DROP PLUGGABLE DATABASE <PDB> INCLUDING DATAFILES;

-- 从 PDB#SEED 创建 PDB
CREATE ALTER PLUGGABLE DATABASE zero ADMIN USER pdbadmin IDENTIFIED BY 0000 DEFAULT TABLESPACE users DATAFILE '/opt/oracle/oradata/XE/zero/users01.dbf' SIZE 100M file_name_convert=('/opt/oracle/oradata/XE/pdbseed', '/opt/oracle/oradata/XE/zero');

ALTER ALTER PLUGGABLE DATABASE zero OPEN;
ALTER ALTER PLUGGABLE DATABASE zero SAVE STATE;

ORA-65010: maximum number of pluggable databases created

SHOW PARAMETER max_pd;

ALTER SYSTEM SET max_pdbs=10;

数据类型

https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Data-Type-Comparison-Rules.html#GUID-D0C5A47E-6F93-4C2D-9E49-4F2B86B359DD

-- Oracle implicitly converts it to the data type if it appears in a numeric expression
SELECT salary + '10' FROM employees;

-- Oracle implicitly converts '200' to 200
SELECT last_name FROM employees WHERE employee_id = '200';

-- Oracle implicitly converts '24-JUN-06 to a value using the default date format
SELECT last_name FROM employees WHERE hire_date = '24-JUN-06';

字符编码

可以放心使用 UTF-8

Note: Starting from Oracle Database 12c Release 2, if you use Oracle Universal Installer (OUI) or Oracle Database Configuration Assistant (DBCA) to create a database, then the default database character set used is the Unicode character set AL32UTF8.

SELECT * FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET'

常用单行函数

SELECT 
    -- 首字母大写 ==> Hello World
    INITCAP('hello world'),
    
    -- 查找（从 1 开始）==> 7
    INSTR('hello world', 'world'),
    
    -- 查找（从 1 开始）==> 06
    SUBSTR('2021-06-25', 6, 2),
    
    -- 字符串长度 ==> 11
    LENGTH('hello world'),
    
    -- 替换 ==> hi world
    REPLACE('hello world', 'hello', 'hi')
FROM dual;

SELECT 
    -- ==> 125
    ROUND(125.455, 0),
    
    -- 四舍五入保留小数点后 1 位 ==> 125.5
    ROUND(125.455, 1),
    
    -- 小数点前 1 位四舍五入 ==> 130
    ROUND(125.455, -1),
    
    -- ==> 125
    TRUNC(125.455, 0),
    
    -- 保留小数点后 1 位 ==> 125.4
    TRUNC(125.455, 1),
    
    -- 小数点前 1 位向下取整
    TRUNC(125.455, -1)
FROM dual;

SELECT
    -- 1个月之前 ==> 2021-05-25
    ADD_MONTHS(TO_DATE('2021-06-25','YYYY-MM-DD'), -1),
    
    -- 下周一 ==> 2021-06-28
    NEXT_DAY(TO_DATE('2021-06-25','YYYY-MM-DD'), 'MONDAY'),
    
    -- 月末日期 ==> 2021-06-30
    LAST_DAY(TO_DATE('2021-06-25','YYYY-MM-DD')),
    
    -- 以 YYYY-07-01 为标准四舍五入 ==> 2021-01-01
    ROUND(TO_DATE('2021-06-25','YYYY-MM-DD'), 'YEAR'),
    
    -- 以 YYYY-MM-16 为标准四舍五入 ==> 2021-07-01
    ROUND(TO_DATE('2021-06-25','YYYY-MM-DD'), 'MONTH'),
    
    -- 以 Thursday 为标准四舍五入（周日为第一天） ==> 2021-06-27
    ROUND(TO_DATE('2021-06-25','YYYY-MM-DD'), 'DAY')
FROM dual;

SELECT
    -- col == null ? v : col
    NVL(col, v),
    
    -- col == null ? v2 : v1
    NVL2(col, v1, v2)
FROM dual;

SELECT 
    1
FROM dual
WHERE 
    REGEXP_LIKE('一个鲁班跪在街头，陈述的是全峡谷的罪恶！', '(鲁班|卤蛋|小短腿)')

六、SQL收集

行转列

WITH temp AS (
    SELECT 'Tim'    rowkey, 'chinese' course, 80 score FROM dual UNION ALL 
    SELECT 'Tim'    rowkey, 'math'    course, 72 score FROM dual UNION ALL 
    SELECT 'Tim'    rowkey, 'english' course, 72 score FROM dual UNION ALL 
    SELECT 'Lucy'   rowkey, 'chinese' course, 80 score FROM dual UNION ALL 
    SELECT 'Lucy'   rowkey, 'math'    course, 82 score FROM dual UNION ALL 
    SELECT 'Lucy'   rowkey, 'english' course, 68 score FROM dual UNION ALL 
    SELECT 'Vivian' rowkey, 'chinese' course, 90 score FROM dual UNION ALL 
    SELECT 'Vivian' rowkey, 'math'    course, 70 score FROM dual UNION ALL 
    SELECT 'Vivian' rowkey, 'english' course, 82 score FROM dual UNION ALL 
    SELECT 'Golden' rowkey, 'chinese' course, 67 score FROM dual UNION ALL 
    SELECT 'Golden' rowkey, 'math'    course, 77 score FROM dual UNION ALL 
    SELECT 'Golden' rowkey, 'english' course, 80 score FROM dual
)
SELECT
     rowkey,
     MAX(CASE WHEN course = 'chinese' THEN score END) "score:chinese",
     MAX(CASE WHEN course = 'math'    THEN score END) "score:math",
     MAX(CASE WHEN course = 'english' THEN score END) "score:english"
FROM temp 
GROUP BY rowkey;

列转行

WITH temp AS (
    SELECT 'Vivian' rowkey, 90 "score:chinese", 70 "score:math", 82 "score:english" FROM dual UNION ALL 
    SELECT 'Golden' rowkey, 67 "score:chinese", 77 "score:math", 80 "score:english" FROM dual UNION ALL
    SELECT 'Tim'    rowkey, 80 "score:chinese", 82 "score:math", 68 "score:english" FROM dual UNION ALL 
    SELECT 'Lucy'   rowkey, 80 "score:chinese", 72 "score:math", 72 "score:english" FROM dual 
)
SELECT rowkey,'chinese' course, "score:chinese" score FROM temp UNION ALL
SELECT rowkey,'math'    course, "score:math"    score FROM temp UNION ALL
SELECT rowkey,'english' course, "score:english" score FROM temp

树递归（邻接表）

https://www.cnblogs.com/boboxing/p/7055251.html

WITH temp AS (
    SELECT '1'     id, '0'   parent_id, '系统管理' title, '菜单' type FROM dual UNION ALL
    SELECT '1/1'   id, '1'   parent_id, '用户管理' title, '菜单' type FROM dual UNION ALL
    SELECT '1/1/1' id, '1/1' parent_id, '新增'    title, '按钮' type FROM dual UNION ALL
    SELECT '1/1/2' id, '1/1' parent_id, '修改'    title, '按钮' type FROM dual UNION ALL
    SELECT '1/1/3' id, '1/1' parent_id, '删除'    title, '按钮' type FROM dual UNION ALL
    SELECT '1/2'   id, '1'   parent_id, '菜单管理' title, '菜单' type FROM dual UNION ALL
    SELECT '1/3'   id, '1'   parent_id, '角色管理' title, '菜单' type FROM dual UNION ALL
    SELECT '2'     id, '0'   parent_id, '系统监控' title, '菜单' type FROM dual UNION ALL
    SELECT '3'     id, '0'   parent_id, '系统工具' title, '菜单' type FROM dual
)
SELECT LEVEL, temp.* FROM temp START WITH id = '1' CONNECT BY parent_id = PRIOR id

分页（`[11~20]`）

SELECT * FROM (
    SELECT t.*, ROWNUM rn FROM t WHERE ROWNUM <= 20
) WHERE rn > 10

十二翼堕落天使

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Oracle18c 快速上手

一、前言也是的确很久都没更新过东西了，连第一句都似乎难以启齿。那么稍微梳理一下，本文主要还是面向跟教主一样 “手里握着是锤子，看什么都是钉子” 的读者，寻找一个了解 Oracle 合适的姿势，毕竟初学者发现的不友好大多都是姿势不对，虽然…目前离目标还任重而道远。目前教主文档的阅读进度还很满，因此大部分理解基本都是基于根据结果去猜测原因，所以如果有误的地方还望指出纠正。当然本文也会引用很多文档、博客、视频来作为参考。二、安装构建镜像
复制链接

扫一扫