关键词：
【中文标题】存储过程与匿名块 - 相同的数据，不同的结果【英文标题】：stored procedure vs anonymous block - same data, different results 【发布时间】：2016-12-05 12:22:49 【问题描述】：当我使用匿名块和存储过程运行相同的代码时，我无法理解我得到的不同结果。他们都从数据字典中获取了一些细节，但看起来存储过程无法提取完整数据：
CREATE OR REPLACE PROCEDURE testing IS
lv_schema VARCHAR2(10) := 'some_schema';
total NUMBER;
CURSOR tab_cur IS
SELECT table_name
, column_name
FROM all_tab_columns
WHERE OWNER = lv_schema;
in_record tab_cur%ROWTYPE;
BEGIN
total := 0;
OPEN tab_cur;
LOOP
FETCH tab_cur INTO in_record;
EXIT WHEN tab_cur%NOTFOUND;
total := total + 1;
END LOOP;
CLOSE tab_cur;
dbms_output.put_line(total);
END testing;
对于我自己的架构，它给了我正确的结果（小架构，找到 13 个结果）。对于我们的一个较小的应用程序，它给出了不完整的结果（50，而不是预期的 83），而对于另一个（大）它列出了 0，而不是 5181）。
我已经驳回了这是因为特权的想法（当我直接使用 SELECT 查询数据字典时，我可以正确查看所有结果），并认为它可能与大小有关（当结果太多时光标失败，不知道)，但是当我在匿名块中运行相同的代码时，我得到了所有正确的结果：
DECLARE
lv_schema VARCHAR2(10) := 'some_schema';
total NUMBER;
CURSOR tab_cur IS
SELECT table_name
, column_name
FROM all_tab_columns
WHERE OWNER = lv_schema;
in_record tab_cur%ROWTYPE;
BEGIN
total := 0;
OPEN tab_cur;
LOOP
FETCH tab_cur INTO in_record;
EXIT WHEN tab_cur%NOTFOUND;
total := total + 1;
END LOOP;
CLOSE tab_cur;
dbms_output.put_line(total);
END;
这里唯一的变化是“DECLARE”而不是“CREATE OR REPLACE FUNCTION testing IS”以及从 END 行中删除函数名称...任何人都可以指出我的一些解释吗？
谢谢！
【问题讨论】：
光标失败？如何？你有错误吗？ 不，不，没有错误，很抱歉造成误解。这两段代码执行时都没有任何明显的错误，它们只是给出不同的结果。游标无法检索大量数据（我错误地描述为“失败”）的想法正在抓住稻草，我知道...... all_tab_columns 显示的列表取决于授予用户的权限。检查一下，如果它不起作用，请告诉我。 角色将在匿名块中处于活动状态，但在定义者权限过程中不处于活动状态，因此all_* 视图中的差异是可以预料的。
绅士们，我认为威廉·罗伯逊有一点 - 我读了一点（docs.oracle.com/cd/E25054_01/network.1111/e16543/…；搜索“角色如何在 PL/SQL 块中工作”）我尝试使用 DBA_TAB_COLUMNS，asn A Techtown 建议，但是对这两个数据字典表的访问权是从角色授予的，所以即使我似乎对这一切发生的原因有了答案，我仍在寻找一些补救策略。感谢所有参与者！
【参考方案1】：
William Robinson's comment 指出了一个解决方案。
这是来自 Oracle 文档中 Configuring Privilege and Role Authorization 的相关引用：
在具有定义者权限的命名块中使用的角色
在任何命名的 PL/SQL 块（存储过程、 以定义者的权限执行的函数或触发器。
我对使用的数据字典表 (all_tab_columns) 的访问权限是通过角色授予的。这就是为什么当我在存储过程中运行查询时没有考虑到它。这里的解决方案是创建一个过程，并在创建过程时使用AUTHID CURRENT_USER 子句强制它使用调用者权限而不是定义者权限（这是默认设置）。
【讨论】：
更多的是您对表本身的访问，而不是对 all_tab_columns 视图的访问。假设 HR 将EMPLOYEES 的选择权限授予角色 HR_READONLY，SCOTT 拥有该权限。 all_ 视图将角色考虑在内，因此 SCOTT 在命令行或匿名块中查询时将看到 EMPLOYEES 列在 all_tables 等中，但不是从定义者权限过程内部，其中仅直接授予权限数。【参考方案2】：
您是否尝试过使用 DBA_TAB_COLUMNS。我认为它与权限有关。
下面是 ALL_TAB_COLUMNS 中的附加子句，DBA_TAB_COLUMNS 中没有。
and (o.owner# = userenv('SCHEMAID')
or
o.obj# in ( select obj#
from sys.objauth$
where grantee# in ( select kzsrorol
from x$kzsro
)
)
or /* user has system privileges */
exists (select null from v$enabledprivs
where priv_number in (-45 /* LOCK ANY TABLE */,
-47 /* SELECT ANY TABLE */,
-397/* READ ANY TABLE */,
-48 /* INSERT ANY TABLE */,
-49 /* UPDATE ANY TABLE */,
-50 /* DELETE ANY TABLE */)
)
)**
【讨论】：
存储过程中的 MySQL select 语句返回的结果与 proc 外部不同
】存储过程中的MySQLselect语句返回的结果与proc外部不同【英文标题】：MySQLselectstatementinstoredprocreturnsdifferentresultsthanoutsideofproc【发布时间】：2016-02-2220:37:48【问题描述】：我无法弄清楚为什么在mysqlproc之外运行的完全相同的查询... 查看详情
数据库存储过程是啥
参考技术A问题一：sql数据库中的存储过程该怎么理解有什么用啊？存储过程是SQL语句和流程控制语句的预编译***，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序调用执行，而且允许用户声明变... 查看详情
T-SQL 和存储过程使用相同的查询给出不同的结果
】T-SQL和存储过程使用相同的查询给出不同的结果【英文标题】：T-SQLandStoredProceduregivesdifferentresultswithsamequery【发布时间】：2018-04-0618:20:55【问题描述】：我是组的新手。有一个奇怪的问题。我的T-SQL查询返回69行，但存储过程中... 查看详情
oracle中的创建过程，函数，包(代码片段)
一、创建存储过程存储过程是在oracle中存取完成特定业务逻辑的代码块。存储过程是命名块，匿名块不存在数据库中，命名块会存储到数据库中，匿名块每次运行都需要提前编译，命名块一次存储，只会编译一次。命名块可以多... 查看详情
详解一条sql语句的执行过程(代码片段)
...直接运行在数据库上。同时，SQL语句与数据在数据库上的存储方式无关，只是不同的数据库对于同一条SQL语句的底层实现不同罢了，但结果相同。这有点类似于java中接口的作用，一个接口可以有不同的实现类，不同的实现类对... 查看详情
达梦数据库的存储过程
达梦数据库的存储过程数据库的存储过程是非常好的一个功能，有用过oracle存储过程的同学再来写达梦的存储过程就非常容易。说说存储过程的优点：1.让业务执行效率更高，因为语句在存储过程都是编译好的，执行速度更快。2... 查看详情
oracle创建存储过程(代码片段)
存储过程： 存储过程是在oracle中存取完成特定业务逻辑的代码块。存储过程是命名块。命名块会存储到数据库中。匿名块每次运行都需要提前编译。命名块一次存储，只会编译一次。命名块可以多次使用。 创建存储过程... 查看详情
从匿名块返回游标结果
...（需要像在Java中那样使用匿名块，并且无权访问创建的存储功能），以便将文字的使用删除到绑定变量：SELECTIL.LAT_NUM,IL.LNGTD_NUMFROMITEM_LOCATIONILWHEREIL.ITEM_ID 查看详情
怎么用oracleplsql匿名块调用存储过程
现在有一个存储过程producrecore_acct_compare(p_branchinvarchar2,p_formatinvarchar2,p_collate_dateinvarchar2,p_statusinvarchar2)isv_prior_datevarchar(8char);v_start_datevarchar(8char);v_invalidexception;BEGIN...具体过程...ENDcore_acct_compare;怎么用匿名块调用一下这个存储过... 查看详情
在plc编程中怎样使用fb模块
...调用FB块时，需要提供一个DB块做为此FB块的数据块，用于存储此FB块的一些过程数据，第一次运算的结果，能给下次运算使用。 所以，一般比较大的子程序，有很多的中间过程数据，比如PID等，就需要使用FB；一般的程序块... 查看详情
如何通过 JDBC 从 Db2 匿名块返回动态结果集？
...式似乎与MySQL、SQLServer中的工作方式相似，方法是从任何过程逻辑运行简单的SEL 查看详情
mysql数据库（31）：存储过程procedure(代码片段)
存储过程procedurestoredprocedure完成特定功能的SQL语句集，存储在数据库中，经过第一次编译之后再次调用不需要编译（效率较高）1、存储过程与函数的区别1.1、相同点都是为了可重复地执行操作数据库的SQL语句集合都是一次编译... 查看详情
为啥调用 sp_who 的 sql 存储过程在通过 SqlCommand 调用时返回的结果与通过 SSMS 调用时返回的结果不同？
】为啥调用sp_who的sql存储过程在通过SqlCommand调用时返回的结果与通过SSMS调用时返回的结果不同？【英文标题】：Whydoesatsqlstoredproceedurethatcallssp_whoreturndifferentresultswhencalledthroughSqlCommnadthanitdoesthroughSSMS?为什么调用sp_who的sql存储过... 查看详情
存储过程非常慢，与具有相同结构的快速视图相比
】存储过程非常慢，与具有相同结构的快速视图相比【英文标题】：storedprocedureveryslow,versusfastviewwiththesameconstruction【发布时间】：2013-11-0615:48:28【问题描述】：我有一个针对特定参数运行非常缓慢的存储过程。存储过程返回一... 查看详情
视图和存储过程的不同结果
】视图和存储过程的不同结果【英文标题】：Differentresultinviewandstoredprocedure【发布时间】：2013-10-2506:46:45【问题描述】：我的表结构是这样的我正在为此表创建视图从dbo.tbPatientReport中选择输入表格中的数据是这样的如果我从sql... 查看详情
oracle存储过程连续执行结果不同
pl/sql里执行存储过程,f8执行后查看结果集,每次都累加.必须把窗口关闭重新测试.这是pl/sql本来就这样的吗,还是我存储过程写的不对??存储过程:http://hi.baidu.com/sordhmbreabivzq/item/a1626cca2873c3ce994aa05b存储过程是别人写的,我完全不懂,能... 查看详情
Pl/pgSQL 匿名代码块在 [42601] 错误上失败：查询没有结果数据的目的地
】Pl/pgSQL匿名代码块在[42601]错误上失败：查询没有结果数据的目的地【英文标题】：Pl/pgSQLanonymouscodeblockfailson[42601]ERROR:queryhasnodestinationforresultdata【发布时间】：2021-06-2011:38:39【问题描述】：假设我在应用程序启动时导入的sql脚... 查看详情
如何在存储过程中直接使用另一个存储过程返回的数据集
...环resultset封装你的数据 参考技术B首先你要知道“另一个存储过程”的结果集的所有列的类型。假设“另一个存储过程”的名字是sp1，没有参数，返回的结果集共3列，全部为int型，那么就在你说的“存储过程”里添加一个与结果... 查看详情