ORACLE开发误区探索【一】（空格、过程与权限、DDL）

梁敬彬梁敬弘兄弟出品

在Oracle数据库开发中，一些细节问题常被忽视，却可能造成难以排查的错误。本文探讨三个常见误区：字符串中的隐藏空格、过程执行权限问题以及DDL语句自动提交事务的特性，帮助开发者避免这些陷阱。

1 小心空格

字符串中的空格错误通常是由笔误引起的，但可能导致严重后果。请大家注意看下面两个语句，都出现了空格，一般而言，写这样的语句的人员并不是真正为了写进空格，而是笔误！

-- 注意 'abc  ' 结尾有两个空格
INSERT INTO employees SELECT * FROM emp_source WHERE name = 'abc  ';

-- 注意日期前后有空格
INSERT INTO transactions SELECT * FROM trans_source WHERE 
    substr(start_date, 1, 8) = ' 20250309 ';

插入数据后，他们一般是会按常理这么去查数据的

-- 没有考虑尾部空格
SELECT * FROM employees WHERE name = 'abc';

-- 没有考虑日期前后空格
SELECT * FROM transactions WHERE substr(start_date, 1, 8) = '20250309';

解决方案

1. 数据插入时注意检查字符串：避免在字符串两端添加不必要的空格
2. 使用TRIM函数处理已有数据：

SQL> select trim(' abc ') from dual;
TRIM('ABC')
----------------
abc

可如下操作完成修复

-- 验证空格问题
SELECT '[' || ' abc ' || ']', '[' || TRIM(' abc ') || ']' FROM dual;

-- 更新表中数据，清除空格
UPDATE employees SET name = TRIM(name) WHERE name LIKE '% %';

-- 查询时使用TRIM
SELECT * FROM employees WHERE TRIM(name) = 'abc';

总结：本小节的错误其实还是非常容易发生的，在我身上就发生过，我是查询语句中加了空格导致查询数据出问题，浪费不少时间查找原因，最终才发现是查询笔误导致!

2 过程与权限

很多开发的朋友在执行过程中常会遇到权限问题，在SQL中可以执行的语句，在过程中却无法运行，究竟是怎么回事？这里我对过程所需要的权限做个简单的介绍。具体如下：

2.1 过程中处理不同用户的表，必须要有显示授权！

SQL> create user yxl identified by yxl;
用户已创建。
SQL> grant dba to ljb;
授权成功。
登陆yxl用户，建表yxl_test
SQL> connect yxl/yxl
SQL> create table yxl_test as select * from dba_objects;
Table created

再次登陆ljb用户，发现dba权限具备更新yxl用户下yxl_test表的能力！

SQL> connect ljb/ljb;
SQL> delete from yxl.yxl_test;
62675 rows deleted

在ljb用户下建过程如下，发现提示错误，找不到yxl.yxl_test表？

SQL> create or replace procedure p_ljb_test
  2  as 
  3  begin 
  4  delete from yxl.yxl_test;
  5  commit;
  6  end;
  7  /
Warning: Procedure created with compilation errors
SQL> show error
Errors for PROCEDURE LJB.P_LJB_TEST:
LINE/COL ERROR
-------- ---------------------------------
4/17     PL/SQL: ORA-00942: 表或视图不存在
4/1      PL/SQL: SQL Statement ignored

原来，在过程中如果A用户要操作B用户表的时候，A用户仅有角色权限是不够的，要B用户显式的将该表权限授权给A用户。
登陆yxl用户，授权yxl_test表所有权限给 ljb用户

SQL> grant all on yxl_test to ljb;
Grant succeeded

然后现在回到ljb用户下继续建过程，终于成功了！

SQL> create or replace procedure p_ljb_test
  2  as
  3  begin
  4  delete from yxl.yxl_test;
  5  end;
  6  /
Procedure created

看来即便拥有dba role,还不能访问不同用户的表，一言以蔽之：role在过程中不可见！

2.2 利用AUTHID CURRENT_USER is传入执行者权限

回到yxl用户
大家还记得该用户是新建的，并且也是被授权dba的，让在这个用户下建立一下过程

SQL> create or replace procedure p_yxl_test
  2  as
  3  begin
  4  execute immediate 'create table yxl_test1 (id int)';
  5  end;
  6  /
Procedure created
SQL> exec p_yxl_test;
begin p_yxl_test; end;
ORA-01031: 权限不足
ORA-06512: 在 "YXL.P_YXL_TEST", line 4
ORA-06512: 在 line 1

还是应验了那句话，一言以蔽之：role在过程中不可见！
那怎么办呢？大家可能马上想到了用SYSDBA用户给YXL用户显式的授予create any table权限！这个确实可行，但是如果涉及很多表，那就比较麻烦了，一个简便的方法是，AUTHID CURRENT_USER is传入执行者权限，这样，该用户在SQL下具备的权限，在过程中也具备了，就可以在过程中执行了。
下面让实验看看，果然成功！

SQL> create or replace procedure p_yxl_test
  2  authid current_user
  3  as
  4  begin
  5  execute immediate 'create table yxl_test1(id int)';
  6  end;
  7  /
Procedure created
SQL> exec p_yxl_test;
PL/SQL procedure successfully completed

查看表也已经建好了。

SQL> select count(*) from yxl_test1;
  COUNT(*)
---------------------
     0

细心的朋友可能会问，刚才第一个过程中处理不同用户的表，必须要有显示授权！这个案例是否可以改动一下，在脚本里加上AUTHID CURRENT_USER is传入执行者权限，不就可以不要显式授权了吗？事实并非如此，操作不同用户下的表，过程中是一定要有该表拥有者授权才可以操作的，授什么样的权就能做什么样的操作，用AUTHID CURRENT_USER is是无用的，大家可以继续往下看。

SQL> connect yxl/yxl
SQL> revoke all on yxl_test from ljb;
Revoke succeeded 
SQL> connect ljb/ljb

实验结果，ljb用户在过程中操作的yxl下的yxl_test表，由于没有授权，加上AUTHID CURRENT_USER is 也不行，操作不同用户的表，显式授权华山一条路！

SQL> create or replace procedure p_ljb_test
  2  authid current_user as
  3  begin
  4  delete from yxl.yxl_test;
  5  end;
  6  /
Warning: Procedure created with compilation errors
SQL> show err
Errors for PROCEDURE LJB.P_LJB_TEST:
LINE/COL ERROR
-------- ---------------------------------
4/13     PL/SQL: ORA-00942: 表或视图不存在
4/1      PL/SQL: SQL Statement ignored

role在过程中不可见，可以用单独得到某中权限或者是AUTHID CURRENT_USER is传入执行这权限方式处理，另外过程中如果要执行不同用户的表，用传入执行者权限是不够的，必须显式得到表的权限

3 小心DDL提交事务

DDL语句（如CREATE、ALTER、DROP等）执行时会自动提交当前事务，且这种提交是无条件的——即使DDL语句本身执行失败，之前的DML操作也会被提交。

问题展示

-- 创建测试表
CREATE TABLE ddl_test AS SELECT * FROM dba_objects;

-- 删除数据（未提交）
DELETE FROM ddl_test;
-- 此时数据在当前会话看不见，但在其他会话仍可见

-- 执行一个DDL操作
CREATE INDEX idx_ddl_test ON ddl_test(object_id);
-- DDL执行的同时，之前的DELETE操作被自动提交

-- 更惊人的是，即使DDL失败，事务也会提交
INSERT INTO ddl_test SELECT * FROM dba_objects;  -- 未提交

-- 故意执行一个错误的DDL
CREATE INDEX idx_bad ON ddl_test(nonexistent_column);
-- 虽然DDL失败，但INSERT操作仍被提交

解决方案

1. 避免在事务中混用DML和DDL：将DDL操作与DML操作分开执行
2. 在存储过程中特别注意：确保DDL前后的事务边界清晰
3. 使用自治事务处理DDL：在需要的情况下

-- 使用自治事务处理DDL
CREATE OR REPLACE PROCEDURE safe_create_index(
  p_table_name VARCHAR2,
  p_index_name VARCHAR2,
  p_column_name VARCHAR2
) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX ' || p_index_name || 
                    ' ON ' || p_table_name || '(' || p_column_name || ')';
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

本集总结

未完待续…

ORACLE开发误区探索【二】(INSERT INTO、OR）

系列回顾

“大白话人工智能” 系列
“数据库拍案惊奇” 系列
“世事洞明皆学问” 系列

三分钟讲述个人感悟——感恩，回馈