Jocoboy's Blog
Oracle常用SQL、PL/SQL语句
Jocoboy
  2024-11-29 15:48:14   2025-06-19 16:56:28    40k Words  2:25 Mins

Oracle数据库的适用场景，以及一些常用SQL、PL/SQL语句。

前言

Oracle是由Oracle公司开发和维护的商业数据库管理系统，使用Oracle数据库通常需要购买许可证，价格因版本、功能、用户数量等因素而异。它主要面向对数据安全、性能和稳定性要求极高的大型企业、金融机构、电信公司等，这些企业愿意为高端的数据库解决方案支付较高的成本。Oracle在数据库技术和Java之间进行了大量的技术整合，Java是Oracle生态系统中企业级应用开发的核心语言，在企业资源规划（ERP）系统、客户关系管理（CRM）系统等大型企业应用中具有重要地位。

适用场景

Oracle数据库有以下性能特点和功能特性：

  • 处理复杂查询：Oracle在处理复杂的企业级查询和大规模数据操作方面表现卓越。
  • 并发处理：Oracle采用多版本并发控制（MVCC）机制来处理高并发事务，同时还具备强大的锁机制来确保数据的一致性和完整性。在高并发的 OLTP（联机事务处理）环境下，如银行的网上交易系统，大量用户同时进行账户查询、转账等操作时，Oracle 能够有效地协调并发事务，避免数据冲突。
  • 数据存储和读写性能：Oracle在存储结构上有多种存储选项，如自动存储管理（ASM）等，可以根据不同的应用场景和数据类型优化存储性能。
  • 数据类型和扩展性：Oracle支持丰富的内置数据类型，包括基本数据类型以及复杂的数据类型（如XMLTYPE用于处理XML数据）。在扩展性方面，Oracle 提供了诸如分区表、索引组织表等功能来提高数据管理的效率。
  • 备份和恢复功能：Oracle提供了一套完整的备份和恢复解决方案，包括物理备份和逻辑备份。它还支持闪回技术，可以在一定程度上快速恢复数据到某个时间点，这在应对数据误操作等情况时非常有用。
  • 安全机制：Oracle具有高度复杂的安全体系，包括用户认证、授权、角色管理、数据加密等多个层面。它可以通过细粒度的权限控制，对不同用户和角色访问不同的数据对象和操作进行严格的限制。

常用SQL语句

模式

在Oracle数据库中，模式是一个逻辑概念，它是一组数据库对象（如表、视图、存储过程、函数、序列等）的集合。可以将模式看作是一个用户所拥有的对象的容器，每个模式都与一个数据库用户相关联。

使用CREATE SCHEMA语句直接创建

1
2
3
4
5
6
7
8
-- 查询所有用户
SELECT USERNAME, USER_ID, ACCOUNT_STATUS FROM DBA_USERS;
-- 查询当前用户
SELECT USER FROM DUAL;
-- 授权创建模式的系统权限给用户
GRANT CREATE SCHEMA TO test_user;
-- 创建模式并授权所有者
CREATE SCHEMA new_schema AUTHORIZATION test_user;

通过创建用户隐式创建模式

当创建一个新用户时，如果这个用户开始创建数据库对象（如创建表、视图等），Oracle会自动为这个用户创建一个与用户同名的模式来存放这些对象。

在Oracle容器数据库（CDB）架构中，包含一个根容器（CDB$ROOT）和多个可插拔数据库（PDB）。公用用户是可以在CDB的公共部分或者多个PDB中访问的用户。这些用户用于管理跨越多个PDB的公共资源或执行通用的管理任务。创建公用用户的方式如下：

1
2
CREATE USER C##test_user IDENTIFIED BY test_password;
GRANT CREATE SESSION TO C##test_user;

创建本地用户则需要切换当前会话到指定的PDB中

1
2
3
4
5
6
7
-- 在容器数据库（CDB）中查看可插拔数据库（PDB）
SELECT name FROM v$pdbs;
-- 换到目标 PDB
ALTER SESSION SET CONTAINER = pdb1; 
-- PDB中创建本地用户
CREATE USER new_schema_user IDENTIFIED BY schema_password;
GRANT CREATE SESSION TO new_schema_user;

表空间

表空间是Oracle数据库中用于存储数据库对象（如表、索引等）的逻辑存储区域，后续创建的数据库对象可以指定存放在表空间里。

表空间操作

创建表空间

1
2
CREATE TABLESPACE tablespace_name
DATAFILE 'datafile_path' SIZE size_value;

例如创建名为WORKHUB的表空间，并设置大小为50M

1
2
CREATE TABLESPACE "WORKHUB"
DATAFILE 'WORKHUB.dbf' SIZE 50M

修改表所在空间

1
ALTER TABLE table_name MOVE TABLESPACE new_tablespace_name;

修改用户在特定表空间中的配额

1
ALTER USER user_name QUOTA new_quota_size ON tablespace_name;

表空间参数组合使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE schema_name.table_name (
    ...
)
TABLESPACE "WORKHUB" -- 定义了表空间的名称为 “WORKHUB”
LOGGING -- 启用日志记录功能
NOCOMPRESS -- 数据不进行压缩存储
PCTFREE 10 -- 每个数据块中，会预留 10% 的空间作为空闲区域
INITRANS 1 -- 每个数据块初始分配的事务入口数量为1
STORAGE ( -- 表空间存储相关参数的详细设置
  INITIAL 65536  -- 指定了表空间的初始大小为 65536 * 8KB = 512MB
  NEXT 1048576 -- 当表空间需要扩展时，每次扩展增加的大小为1048576 * 8KB = 8GB
  MINEXTENTS 1 -- 表示表空间最初创建时至少包含的扩展次数为1
  MAXEXTENTS 2147483645 -- 表空间最多可以扩展的次数为无限
  BUFFER_POOL DEFAULT -- 指定该表空间使用默认的缓冲池
)
PARALLEL 1 -- 表示对这个表空间中数据的并行处理程度，最多可以启用 1 个并行执行服务器来协助处理任务
NOCACHE -- 指定了该表空间中的数据块在被读取到内存（缓冲池）后，不会被缓存起来用于后续的重复访问
DISABLE ROW MOVEMENT -- 禁止行移动功能
;

窗口函数

场景：表中某个字段可能有重复值出现，对于重复出现的记录，根据创建时间只选取首次出现的记录

1
2
3
4
5
6
7
SELECT NAME, P_ID
FROM (
    SELECT t.*,
            ROW_NUMBER() OVER (PARTITION BY P_ID ORDER BY CRT_TIME) AS row_num
    FROM [schema_name].[table_name] t
)
WHERE row_num = 1

PL/SQL语句

概述

PL/SQL（Procedural Language/Structured Query Language）是Oracle数据库系统的过程化编程语言。它是一种块结构语言，将 SQL语句的强大数据处理能力与过程化编程语言的流程控制结构相结合。这使得开发人员可以在数据库内部编写复杂的业务逻辑，而不仅仅是执行简单的查询操作。由于PL/SQL程序是在数据库服务器内部执行，减少了数据在客户端和服务器之间的传输，从而提高了性能。特别是对于复杂的数据库操作和大量的数据处理，这种优势更加明显。

基本结构

块结构

PL/SQL程序由块（Block）组成，每个块都有一个声明部分、执行部分和可选的异常处理部分。声明部分用于定义变量、常量、游标等；执行部分包含了要执行的SQL语句和PL/SQL语句，用于实现具体的业务逻辑；异常处理部分用于处理程序执行过程中可能出现的错误。例如：

1
2
3
4
5
6
7
8
9
10
11
12
DECLARE
    -- 声明部分，定义变量
    v_count NUMBER;
BEGIN
    -- 执行部分，查询并赋值
    SELECT COUNT(*) INTO v_count FROM employees;
    DBMS_OUTPUT.PUT_LINE('员工总数为：' || v_count);
EXCEPTION
    -- 异常处理部分，处理可能的错误
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('未找到数据。');
END;

控制结构

条件语句包括IF - THEN - ELSE语句用于根据条件执行不同的代码块。例如：

1
2
3
4
5
6
7
IF condition THEN
    -- 条件为真时执行的语句
ELSIF another_condition THEN
    -- 另一个条件为真时执行的语句
ELSE
    -- 所有条件为假时执行的语句
END IF;

循环语句有LOOP、WHILE - LOOP和FOR - LOOP等多种循环结构。例如，使用FOR - LOOP来遍历一个查询结果集

1
2
3
FOR i IN (SELECT column_name FROM table_name) LOOP
    -- 对每一行数据进行操作
END LOOP;

存储过程

存储过程：是一组预编译的PL/SQL语句，存储在数据库中，可以被调用以执行特定的任务。存储过程可以接受参数，并且可以包含复杂的业务逻辑和数据库操作。例如，使用存储过程向employees表中插入一条新员工记录

1
2
3
4
5
6
7
8
CREATE OR REPLACE PROCEDURE insert_employee(
    p_name VARCHAR2,
    p_salary NUMBER
) AS
BEGIN
    INSERT INTO employees (name, salary) VALUES (p_name, p_salary);
    COMMIT;
END;

存储过程可以结合块结构和控制结构，实现批量插入随机数据，例如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
-- 批量插入随机数据(存储过程)
CREATE OR REPLACE PROCEDURE batch_insert_users(
    total IN NUMBER DEFAULT 2
) AS
    -- 定义变量
    v_id ADMIN_USER.ID%TYPE;
    v_name ADMIN_USER.NAME%TYPE;
    v_gender ADMIN_USER.GENDER%TYPE;
    v_pid ADMIN_USER.P_ID%TYPE;
    v_orgcode ADMIN_USER.ORG_CODE%TYPE;

    gender NUMBER := 0;
    v_counter NUMBER := 1; -- 循环计数器
    v_max NUMBER := total; -- 最大插入条数
BEGIN
	-- 循环插入数据
    WHILE v_counter <= v_max LOOP
        v_id := random_alphanum(6); -- 随机六位字符(包含大小写字母、数字)
        v_name := random_chinese_name(); -- 随机两位或三位姓名
        gender := ROUND(DBMS_RANDOM.VALUE(0, 1));
        v_pid := simple_random_id_card(gender); -- 随机身份证号
        SELECT 
	    CASE 
	        WHEN gender = 0 THEN '2' 
	        ELSE '1' 
	    END AS res INTO v_gender; -- 随机性别(1男性2女性)
        SELECT ID INTO v_orgcode  -- 从现有组织代码中随机挑选一个
		FROM (
		    SELECT *
		    FROM ADMIN_ORG
		    ORDER BY DBMS_RANDOM.VALUE -- 对查询结果进行随机排序
		)
		WHERE ROWNUM = 1;

        -- 插入数据到表中
        INSERT INTO ADMIN_USER 
		(ID, NAME, P_ID, ORG_CODE, GENDER) 
		VALUES(v_id, v_name, v_pid, v_orgcode, v_gender);

        v_counter := v_counter + 1; -- 更新计数器
    END LOOP;
	
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('成功插入' || v_max || '条数据');
END;

存储函数

存储函数与存储过程类似，但函数必须返回一个值。函数可以用于计算并返回一个结果，这个结果可以在SQL语句中使用。例如，使用存储函数计算员工的年薪

1
2
3
4
5
6
CREATE OR REPLACE FUNCTION calculate_annual_salary(
    p_salary NUMBER
) RETURN NUMBER AS
BEGIN
    RETURN p_salary * 12;
END;

存储函数中可以使用条件语句。例如，生成随机两位或三位姓名、随机身份证号

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
-- 常见姓氏数组
CREATE OR REPLACE FUNCTION get_random_surname RETURN VARCHAR2 IS
    TYPE surname_array IS TABLE OF VARCHAR2(10);
    v_surnames surname_array := surname_array(
        '王', '李', '张', '刘', '陈', '杨', '赵', '黄', '周', '吴',
        '徐', '孙', '胡', '朱', '高', '林', '何', '郭', '马', '罗'
    );
BEGIN
    RETURN v_surnames(TRUNC(DBMS_RANDOM.VALUE(1, v_surnames.COUNT + 1)));
END;

-- 随机名字生成
CREATE OR REPLACE FUNCTION random_chinese_name RETURN VARCHAR2 IS
    v_name VARCHAR2(30);
BEGIN
    v_name := get_random_surname;
    
    -- 60%概率单字名，40%双字名
    IF DBMS_RANDOM.VALUE(0, 1) < 0.6 THEN
        v_name := v_name || UNISTR('\' || LPAD(TO_CHAR(19968 + TRUNC(DBMS_RANDOM.VALUE(0, 500)), 'FMXXXX'), 4, '0'));
    ELSE
        v_name := v_name || 
                 UNISTR('\' || LPAD(TO_CHAR(19968 + TRUNC(DBMS_RANDOM.VALUE(0, 500)), 'FMXXXX'), 4, '0')) ||
                 UNISTR('\' || LPAD(TO_CHAR(19968 + TRUNC(DBMS_RANDOM.VALUE(0, 500)), 'FMXXXX'), 4, '0'));
    END IF;
    
    RETURN v_name;
END;

-- 生成随机身份证号
CREATE OR REPLACE FUNCTION simple_random_id_card(gender NUMBER DEFAULT 0) RETURN VARCHAR2 IS
    v_id_card VARCHAR2(18);
BEGIN
    -- 1. 前6位行政区划代码（随机）
    v_id_card := LPAD(TRUNC(DBMS_RANDOM.VALUE(0, 999999)), 6, '0');
    
    -- 2. 中间8位出生日期（1980-2000年间）
    v_id_card := v_id_card || 
                TO_CHAR(TO_DATE('1980-01-01', 'YYYY-MM-DD') + 
                TRUNC(DBMS_RANDOM.VALUE(0, 365*20)), 'YYYYMMDD');
    
    -- 3. 顺序号3位（最后一位奇数男，偶数女）
    v_id_card := v_id_card || LPAD(TRUNC(DBMS_RANDOM.VALUE(0, 99)), 2, '0') || TRUNC(gender);
    
    -- 4. 随机校验码（1位数字或X）
    IF DBMS_RANDOM.VALUE(0, 1) < 0.9 THEN
        v_id_card := v_id_card || TRUNC(DBMS_RANDOM.VALUE(0, 10));
    ELSE
        v_id_card := v_id_card || 'X';
    END IF;
    
    RETURN v_id_card;
END;

存储函数中也可以使用循环语句。例如，获取随机6位包含大小写字母、数字的字符串

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE OR REPLACE FUNCTION random_alphanum(p_length NUMBER DEFAULT 6) 
RETURN VARCHAR2 IS
    v_chars VARCHAR2(62) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
    v_result VARCHAR2(4000);
BEGIN
    v_result := '';
    FOR i IN 1..p_length LOOP
        v_result := v_result || SUBSTR(v_chars, 
                             MOD(ABS(DBMS_RANDOM.RANDOM), 62) + 1, 
                             1);
    END LOOP;
    RETURN v_result;
END random_alphanum;

命令行工具

SQL*Plus

SQL*Plus是Oracle数据库提供的一个命令行界面的工具，用于与Oracle数据库进行交互。它允许用户输入和执行SQL语句、PL/SQL块以及执行各种数据库管理和操作任务。比如查询数据、创建表、修改数据库对象结构等。

连接数据库

方式一：使用Easy Connection Identifier连接

1
sqlplus system/root1234@"localhost:1521/FREEPDB1"

方式二：使用Full Connection Identifier连接。首先需要编辑tnsnames.ora文件(以23 ai个人免费版为例，对应目录为C:\app\your-username\product\23ai\dbhomeFree\network\admin)，添加以下配置

1
2
3
4
5
6
7
FREEPDB1 = 
 (DESCRIPTION=
   (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
   (CONNECT_DATA=
      (SERVICE_NAME=FREEPDB1)
    )
 )
1
sqlplus system/root1234@FREEPDB1

.NET项目配置文件中数据库连接字符串如下，

1
2
3
4
5
{
    "ConnectionStrings": {
        "OracleConnection": "User Id=system;Password=your-password;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=FREEPDB1)));"
    }
}

方式三：使用Oracle提供的外部认证方式，以操作系统管理员身份登录

1
sqlplus / as sysdba

SQLcl

SQLcl是Oracle推出的一款现代化的命令行工具，它是基于Java开发的，在功能上可以看作是SQL*Plus的增强版，提供了更加简洁易用、功能丰富的交互界面，并且融入了很多新的特性来提升开发和管理数据库的效率。

配置

SQLcl需要Java 11及以上版本的JDK，Oracle在使用过JDK后就会将JDK的配置写到配置文件中，若是Oracle的环境变量配置在JDK的变量前时将会被Oracle的配置信息加载覆盖掉。若遇到Java版本切换不生效的问题，可将PATH路径中的C:\Program Files\Common Files\Oracle\Java\javapath置于JDK变量之后。

SQL*Plus和SQLcl可执行文件一般位于%ORACLE_HOME%/bin目录下，以23 ai个人免费版为例，对应目录为C:\app\your-username\product\23ai\dbhomeFree\bin。

参考文档

  1. 前言
  2. 适用场景
  3. 常用SQL语句
    1. 模式
      1. 使用CREATE SCHEMA语句直接创建
      2. 通过创建用户隐式创建模式
    2. 表空间
      1. 表空间操作
    3. 窗口函数
  4. PL/SQL语句
    1. 概述
    2. 基本结构
      1. 块结构
      2. 控制结构
    3. 存储过程
    4. 存储函数
  5. 命令行工具
    1. SQL*Plus
      1. 连接数据库
    2. SQLcl
    3. 配置
  6. 参考文档
© 2019 - 2025    Jocoboy
Powered by Hexo & Theme Keep
This site is deployed on
  1. 前言
  2. 适用场景
  3. 常用SQL语句
    1. 模式
      1. 使用CREATE SCHEMA语句直接创建
      2. 通过创建用户隐式创建模式
    2. 表空间
      1. 表空间操作
    3. 窗口函数
  4. PL/SQL语句
    1. 概述
    2. 基本结构
      1. 块结构
      2. 控制结构
    3. 存储过程
    4. 存储函数
  5. 命令行工具
    1. SQL*Plus
      1. 连接数据库
    2. SQLcl
    3. 配置
  6. 参考文档