关于如何调试动态SQL代码的6条技巧

MariaDB TX,在生产中得到验证，并由社区驱动，是一种适用于任何企业的完整数据库解决方案-现代应用程序的现代数据库。
通过我们的AskTOM PL/SQL Office时数节目：

---

   Declare
   v_Table all_tables.TABLE_NAME%type;
   v_Mnt varchar2(2):='08';
   Type Cur_type is Ref Cursor;
    C Cur_type;
   Begin
           v_Table:='ddi_ticket_10_1018';           
           Open C for 'SELECT * from bill.'||v_Table||v_Mnt||'Where called_nbr=123';
           End;

---

When executing this code, I face this Error message.
ORA-00933-SQL Command not properly ended
ORA-06512: At Line 9.
Please check the above code and modify for syntax correction
我一眼就能猜出问题出在哪里。

你能。

我不是想吹牛。我只是鼓励你不进一步阅读并检查代码。是什么导致了他的问题？

动态SQL可能很棘手-在打开之前就不那么复杂了-因为或立即执行是PL/SQL语言的复杂部分。但是，因为它非常容易搞乱您正在动态构建的SQL或PL/SQL。你可以：

省略“；”(来自PL/SQL代码)。
忘记在SQL各节之间留出空白。
有不匹配的括号。
时断时续。
在这种情况下，我回信说：“我很确定您会发现问题在于在”WHERE“关键字之前没有空格：

v_Mnt||‘Where called_nbr=123’;

然后，这个交换提醒我，我应该写一篇文章，给出一些简单的技巧，以使调试动态SQL代码更加容易，并确保它按预期工作。开始吧。

用AUTHID Current_USER定义您的子程序(请求人权利).
如果您正在执行动态DDL，使子程序成为自主交易.
始终对变量执行立即或打开。
始终处理动态SQL执行中可能出现的异常。
记录错误信息加试图执行的变量。
将测试模式构建到子程序中。
我将通过一个忽略所有这些点的超级欺骗有用+危险程序的版本开始演示这些点的价值：DROP_AY过程。

PROCEDURE drop_whatever (nm IN VARCHAR2 DEFAULT ‘%’,
typ 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 (typ)
AND object_name <> ‘DROP_WHATEVER’;
BEGIN
FOR rec IN object_cur
LOOP
EXECUTE IMMEDIATE
‘DROP ’
|| rec.object_type
|| ’ ’
|| rec.object_name
|| CASE
WHEN rec.object_type IN (‘TABLE’, ‘OBJECT’)
THEN
’ CASCADE CONSTRAINTS’
ELSE
NULL
END;
END LOOP;
END;
在这个过程中，我使用一个静态游标来查找所有匹配的对象，然后对找到的每个对象执行一个动态DDLDROP语句。

它很有用，因为我只需键入以下内容就可以删除架构中的所有数据库对象

EXEC drop_whatever()
而且很危险完全出于同样的原因.

但是等等。考虑到它有多有用，也许我们应该让大家能够使用它。我知道，我会运行这个命令：

GRANT EXECUTE ON drop_whatever TO PUBLIC
嘿，出什么事了？：-)

非常。让我们来看看我的建议，突出潜在的问题。

1.用AUTHID Current_USER定义您的子程序(请求人权利).

该过程没有AUTHID子句(我打赌大多数存储的程序单元没有)。这意味着它默认为“定义者权利”。这意味着它总是以过程的定义者/所有者的权限执行。

这意味着，如果HR拥有Drop_any，然后Scott执行它(谢谢，给public！)，如下所示：

EXEC HR.drop_whatever()
那么Scott就会丢弃所有的数据库对象。在HR的模式中!

2.如果您正在执行动态DDL，使子程序成为自主交易.

关于DDL语句，Oracle执行的是隐提交在执行DDL语句之前和之后。因此，如果您有一个执行动态DDL的存储过程，您必须警告可能使用它的每个人会话中的任何未完成的更改(这太不礼貌了)，或者将此语句添加到您的过程中：

PRAGMA AUTONOMOUS_TRANSACTION;
现在，在过程中执行的任何提交(或回滚)都将只影响所做的更改。内手术。

3.始终对变量执行立即或打开。

这是一个如此简单的事情，但它可以节省大量的时间，当你试图找出你的程序的问题。

问题是：不难理解如何使用立即执行。但它可以是非常很难在运行时正确构造字符串。如此多的小错误会导致错误。如果直接在执行立即语句中构造字符串，如何才能看到已执行的内容以及可能出错的地方？

例如，假设在DROP_AY过程中，我按照以下方式构造了DROP语句：

EXECUTE IMMEDIATE
'DROP ’
|| rec.object_type
|| rec.object_name …
当我试图放下我的桌子时，我看到：

ORA-00950: invalid DROP option
那能告诉我什么？不多。它认为我给它无效的选择是什么？我刚才想做什么？

另一方面，如果我将希望执行的表达式分配给一个变量，然后调用EXECUTE立即，我可以捕获错误并记录/显示该变量(参见下面DROP_AY的第二个实现)。然后我可能会看到这样的东西：

DROP SYNONYMV$SQL - FAILURE
噢我现在明白了。在对象类型和对象名称之间没有包含空格。我真傻因此，始终声明一个变量，将动态构造的SQL语句赋值给该变量，并立即执行它。

4.始终处理动态SQL执行中可能出现的异常。

5.记录错误信息加试图执行的变量。

如果不捕获异常，则无法记录或显示该变量。如果您不坚持这个变量值，那么很难向您的支持团队报告这个问题。

除了对代码的拙劣设计发牢骚外，什么也做不了。

6.将测试模式构建到子程序中。

我已经写了很长时间的代码，并把代码搞砸了这么长时间，我了解到，实现一种不“做”任何事情的测试模式是非常有帮助的-尤其是当该代码对表中的数据进行更改时。只要告诉我如果我愿意的话它会做什么。

当我为Just_CHECKING参数传递true(默认值)时，您可以在下面的代码中看到它。

一个更好的(？)放下任何东西

标题中的“？”只是为了提醒我们，这个程序本身就是危险的。

以下是Drop_All的版本，随我的建议而定。请注意，对于实际的生产代码，您应该绝不可能通过调用DBMS_OUTPUT.PUT_LINE，“报告”或“记录”错误。谁会看到这个？相反，请调用标准错误日志过程，如果没有，则获取并使用伐木机.

PROCEDURE drop_whatever (
nm IN VARCHAR2 DEFAULT ‘%’
, typ 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 (typ)
AND object_name <> ‘DROP_WHATEVER’;
BEGIN
FOR rec IN object_cur
LOOP
dropstr :=
‘DROP ’
|| rec.object_type
|| ’ ’
|| rec.object_name
|| CASE
WHEN rec.object_type IN (‘TABLE’, ‘OBJECT’)
THEN ’ CASCADE CONSTRAINTS’
ELSE NULL
END;
BEGIN
IF just_checking
THEN
DBMS_OUTPUT.put_line (dropstr || ’ - just checking!’);
ELSE
EXECUTE IMMEDIATE dropstr;
DBMS_OUTPUT.put_line (dropstr || ’ - SUCCESSFUL!’);
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (dropstr || ’ - FAILURE!’);
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
END;
END LOOP;
END;
我们来记一下
编写包含动态SQL的存储程序单元时：

用AUTHID Current_USER定义您的子程序(请求人权利).
如果您正在执行动态DDL，使子程序成为自主交易.
始终对变量执行立即或打开。
始终处理动态SQL执行中可能出现的异常。
记录错误信息加试图执行的变量。
将测试模式构建到子程序中。