动态SQL代码可能很难调试。举个例子,让我们来看看如何应对这一挑战的6个技巧。
MariaDB的TX , 成熟的生产和由社区驱动,为任何和每一个企业一个完整的数据库解决方案-为现代应用的现代数据库。
通过我们的AskTOM PL / SQL办公时间 计划得到了这个请求 :
亲爱的专家,我在下面写了以下代码:
----------------------------------------------
宣布
v_Table all_tables .TABLE_NAME%type;
v_Mnt varchar2(2):= '08' ;
类型Cur_type 是 Ref Cursor;
C Cur_type;
开始
v_Table:= 'ddi_ticket_10_1018' ;
打开C表示“SELECT * from bill”。|| v_Table || v_Mnt || 'where called_nbr = 123' ;
结束;
-------------------------------------------------- -----------------
执行此代码时,我面临此错误消息。
ORA- 00933 -SQL命令未正确结束
ORA- 06512:第9行。
请检查上面的代码并修改语法更正
我一眼就能猜到问题是什么。
你可以吗?
我不是在吹嘘。我只是鼓励你 不要 进一步阅读,而是检查代码。什么可能导致他的问题?
动态SQL可能很棘手 - 在OPEN-FOR或EXECUTE IMMEDIATE是PL / SQL语言的复杂部分之前并非如此。但是因为它很容易搞乱你动态构建的SQL或PL / SQL。你可以:
遗漏“;” (来自PL / SQL代码)。
忘记在SQL的各个部分之间留下空格。
有无与伦比的括号。
一直打开。
在这种情况下,我回信说:“我很确定你会发现问题是你在”Where“关键字之前没有空格:
v_Mnt || 'where called_nbr = 123' ;
然后,这个交换提醒我,我应该写一篇文章,其中包含一些简单的技巧,可以让您更轻松地调试动态SQL代码并确保它按预期工作。开始。
使用AUTHID CURRENT_USER(调用者权限)定义子程序。
如果您正在执行动态 DDL,请使子程序成为 自治事务。
始终执行IMMEDIATE或OPEN FOR变量。
始终处理动态SQL执行可能引起的异常。
记录错误信息 以及 您尝试执行的变量。
在子程序中构建测试模式。
我将通过一个超级有用+危险程序的版本开始来证明这些点的价值,这些程序忽略了所有这些:drop_whatever程序。
PROCEDURE drop_whatever(nm IN VARCHAR2 DEFAULT '%',
典型的IN VARCHAR2 DEFAULT '%')
IS
CURSOR object_cur
IS
SELECT object_name,object_type
FROM user_objects
WHERE object_name LIKE UPPER(nm)
AND object_type LIKE UPPER(典型值)
AND object_name <> 'DROP_WHATEVER' ;
开始
FOR rec IN object_cur
循环
执行立即
'DROP'
|| rec .object_type
|| ''
|| rec .object_name
|| 案件
当rec .object_type IN('TABLE','OBJECT')时
然后
'CASCADE CONSTRAINTS'
其他
空值
结束;
结束循环;
结束;
在这个过程中,我使用静态游标来查找所有匹配的对象,然后对于找到的每个对象,我执行动态DDL DROP语句。
它很有用,因为我可以通过输入任何内容来删除模式中的所有数据库对象
执行drop_whatever()
而且出于同样的原因它也很危险 。
哦,等等。鉴于它有多么有用,也许我们应该让 每个人都 能够使用它。我知道,我会运行这个命令:
GRANT EXECUTE ON drop_whatever TO PUBLIC
嘿,怎么可能出错?:-)
非常非常。让我们逐步完善我的建议并突出潜在的问题。
1.使用AUTHID CURRENT_USER(调用者权限)定义子程序。
该过程没有AUTHID子句(我打赌你的大多数存储的程序单元都没有)。这意味着它默认为“定义权限”。这意味着它始终以过程的定义者/所有者的特权执行。
这意味着,如果,例如,HR拥有drop_whatever然后SCOTT执行它(谢谢你,GRANT到PUBLIC!),如:
EXEC HR .drop_whatever()
那么SCOTT将刚刚删除HR模式中的所有数据库对象 !
2.如果您正在执行动态 DDL,请使子程序成为 自治事务。
关于DDL语句的事情是Oracle在执行 DDL语句之前和之后都执行 隐式提交。因此,如果您有一个执行动态DDL的存储过程,您必须警告可能使用它的每个人,他们的会话中的任何未完成的更改(这只是粗鲁)或您将此语句添加到您的过程:
PRAGMA AUTONOMOUS_TRANSACTION;
现在,在程序执行的任何提交(或回滚)将仅影响所做的更改 中 的程序。
3.始终执行IMMEDIATE或OPEN FOR变量。
这是一件非常简单的事情,但是当你试图找出你的程序有什么问题时,它可以节省你很多时间。
事情就是这样:不难想出如何使用EXECUTE IMMEDIATE。但是 在运行时正确构造字符串可能 非常棘手。如此多的小错误都可能导致错误。如果直接在EXECUTE IMMEDIATE语句中构造字符串,那么如何查看执行的内容以及可能出错的位置?
例如,假设在drop_whatever过程中,我构造了我的DROP语句,如下所示:
执行立即
'DROP'
|| rec .object_type
|| rec .object_name ...
当我试图放下桌子时,我看到:
ORA - 00950:无效的 DROP 选项
这告诉我什么?不多。它认为我认为哪个选项无效?我刚刚尝试做什么?
另一方面,如果我将我希望执行的表达式分配给变量,然后调用EXECUTE IMMEDIATE,我可以捕获错误并记录/显示该变量(请参阅下面的drop_whatever的第二个实现)。然后我可能会看到类似的东西:
DROP SYNONYMV $ SQL - 失败
哦! 我现在明白了。我没有在对象类型和对象名称之间包含空格。傻我。因此,总是声明一个变量,将动态构造的SQL语句分配给该变量,然后执行它立即执行。
4.始终处理动态SQL执行可能引起的异常。
5.记录错误信息 以及 您尝试执行的变量。
如果未捕获异常,则无法记录或显示该变量。如果您不保留该变量值,则很难向您的支持团队提供有用的问题报告。
除了嘲笑代码的糟糕设计之外,你做不了什么。
6.在子程序中构建测试模式。
我一直在编写代码并且长时间搞砸了代码,我已经了解到它非常有用 - 尤其是当代码对表中的数据进行更改时 - 实现一个不“执行”任何操作的测试模式。只是告诉我如果我愿意的话会怎么做。
当我为just_checking参数传递TRUE(默认值)时,您可以在下面的代码中看到它。
更好(?)Drop_Whatever
“?” 该标题只是提醒我们,这个程序本质上是危险的。
这是drop_whatever的版本,遵循我的建议。需要注意的是真实的,生产代码,你应该 永远不会 “报告”或通过调用DBMS_OUTPUT.PUT_LINE“日志”的错误。谁会看到这个?相反,调用标准错误记录过程,如果没有,则获取并使用 Logger。
程序drop_whatever(
nm IN VARCHAR2 DEFAULT '%'
,典型 IN VARCHAR2 DEFAULT '%'
,just_checking IN BOOLEAN DEFAULT TRUE
)
AUTHID CURRENT_USER
IS
PRAGMA AUTONOMOUS_TRANSACTION;
dropstr VARCHAR2(32767);
CURSOR object_cur
IS
SELECT object_name,object_type
FROM user_objects
WHERE object_name LIKE UPPER(nm)
AND object_type LIKE UPPER(典型值)
AND object_name <> 'DROP_WHATEVER' ;
开始
FOR rec IN object_cur
循环
dropstr:=
'DROP'
|| rec .object_type
|| ''
|| rec .object_name
|| 案件
当rec .object_type IN('TABLE','OBJECT')时
然后''CASCADE CONSTRAINTS'
ELSE NULL
结束;
开始
如果just_checking
然后
DBMS_OUTPUT .put_line(dropstr || ' - 只是检查!');
其他
EXECUTE IMMEDIATE dropstr;
DBMS_OUTPUT .put_line(dropstr || ' - SUCCESSFUL!');
万一;
例外
其他时候
然后
DBMS_OUTPUT .put_line(dropstr || ' - FAILURE!');
DBMS_OUTPUT .put_line(DBMS_UTILITY .format_error_stack);
结束;
结束循环;
结束;