来自 Java 的 Oracle。对象参数的 CallableStatement 和 Null？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25729225/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle from Java. CallableStatement and Null for object parameter?
提问by Beta033
I've got, what started as a fairly complex stored procedure boils down to a very simple use case that doesn't seem to be working as i'm expecting.
我知道，从一个相当复杂的存储过程开始归结为一个非常简单的用例，它似乎没有像我期望的那样工作。
The stored procedure now looks like this for my test:
对于我的测试，存储过程现在看起来像这样：
CREATE OR REPLACE PROCEDURE proc_test(
v_someString varchar2
) as
BEGIN
dbms_output.put_line('test');
END;
I can call this stored procedure from java using the callable statement API
我可以使用可调用语句 API 从 java 调用此存储过程
java.sql.CallableStatement stmt = conn.prepareCall("call proc_test(?)");
stmt.setObject(1,null);
stmt.execute();
The above works as expeted. The method executes and completes successfully. Nows where it gets tricky. We've created a set of oracle object types to allow us to pass more complicated structures around similar to the following
上述工作按预期进行。该方法执行并成功完成。现在它变得棘手了。我们创建了一组 oracle 对象类型，以允许我们传递类似于以下内容的更复杂的结构
CREATE OR REPLACE
type SOMETYPE_TYPE force UNDER BASE_TYPE (value varchar2(255),
CONSTRUCTOR FUNCTION SOMETYPE_TYPE RETURN SELF AS RESULT) NOT FINAL;
if i simply change the procedure to accept these types as parameters, like this:
如果我只是更改程序以接受这些类型作为参数，如下所示：
CREATE OR REPLACE PROCEDURE proc_test(
v_someString SOMETYPE_TYPE
) as
BEGIN
dbms_output.put_line('test');
END;
Oracle will break with PLS-00306: wrong number or types of arguments in call to 'CNV_CREATE_PERSON'
甲骨文将打破 PLS-00306: wrong number or types of arguments in call to 'CNV_CREATE_PERSON'
What gives? If i call this procedure from another procedure with null as the value, it works fine. Only calling it from the java API does it break.
是什么赋予了？如果我从另一个以 null 作为值的过程调用这个过程，它工作正常。只有从 java API 调用它才会中断。
I've been scouring the documentation for any solution and can't find one. Perhaps one of you wise and learned fellows could point me in the right direction.
我一直在寻找任何解决方案的文档，但找不到。也许你们中的一位聪明而博学的人可以为我指明正确的方向。
Thanks
谢谢
回答by Alex Poole
You need to use the setNull()methodto specify your user-defined type:
您需要使用该setNull()方法来指定您的用户定义类型：
void setNull(String parameterName, int sqlType, String typeName) throws SQLExceptionSets the designated parameter to SQL
NULL. This version of the methodsetNullshould be used for user-defined types and REF type parameters. Examples of user-defined types include: STRUCT, DISTINCT, JAVA_OBJECT, and named array types.Note: To be portable, applications must give the SQL type code and the fully-qualified SQL type name when specifying a NULL user-defined or REF parameter. In the case of a user-defined type the name is the type name of the parameter itself. For a REF parameter, the name is the type name of the referenced type.
void setNull(String parameterName, int sqlType, String typeName) throws SQLException将指定参数设置为 SQL
NULL。此版本的方法setNull应用于用户定义的类型和 REF 类型参数。用户定义类型的示例包括：STRUCT、DISTINCT、JAVA_OBJECT 和命名数组类型。注意：为了便于移植，应用程序在指定 NULL 用户定义或 REF 参数时必须提供 SQL 类型代码和完全限定的 SQL 类型名称。在用户定义类型的情况下，名称是参数本身的类型名称。对于 REF 参数，名称是引用类型的类型名称。
So in your case:
所以在你的情况下：
stmt.setNull(1, java.sql.Types.STRUCT, "SOMETYPE_TYPE");
If you just pass nullthen it doesn't know what type it represents and can't do any implicit conversion. That does mean that your code needs to branch depending on whether your Java object is null or not, to either call setNull()or setObject()as appropriate.
如果你只是通过，null那么它不知道它代表什么类型并且不能进行任何隐式转换。这确实意味着您的代码需要根据您的 Java 对象是否为 null 进行分支，以调用setNull()或setObject()根据情况进行。
You should really use setNull()for your varchar2example as well, but can use the simpler version, as:
您也应该真正setNull()用于您的varchar2示例，但可以使用更简单的版本，如：
stmt.setNull(1, java.sql.Types.VARCHAR);
... but you get away with just passing null.
......但你只是通过了null。
