oracle 报错pls 00405,在PLSQL条件逻辑中使用子查询;(Using a subquery within PLSQL conditional logic; error PLS-0040...

在PLSQL条件逻辑中使用子查询;(Using a subquery within PLSQL conditional logic; error PLS-00405)

我正在构建一个使用PHP从Oracle10g数据库服务器获取数据的应用程序。 我的输入表单有一系列复选框，通过数组提交到处理页面(代码过滤到相关元素)：

处理页面调用Oracle过程，该过程使用自定义数据类型“STRING_TABLE”( CREATE OR REPLACE TYPE STRING_TABLE AS TABLE OF VARCHAR (1000); )将PHP处理为数组的复选框值转换为表。

我的过程将这些复选框作为传入参数“p_queue_type”(代码过滤到相关元素)：

PROCEDURE get_TLFQ_results (

p1 OUT SYS_REFCURSOR,

p_queue_type IN STRING_TABLE

)

IS

v_return_sql VARCHAR2(32767) := '';

BEGIN

IF ('HOLD' IN (SELECT COLUMN_VALUE AS queue_type FROM TABLE (p_queue_type))) THEN

--compile query string

END IF;

IF ('CR' IN (SELECT COLUMN_VALUE AS queue_type FROM TABLE (p_queue_type))) THEN

--compile query string

END IF;

-- Execute the query string and store the results in a cursor

OPEN p1 FOR v_return_sql;

END get_TLFQ_results;

当我尝试编译我的过程时，我收到此Oracle错误：

[错误] PLS-00405(4215：23)：PLS-00405：在此上下文中不允许使用子查询

我究竟做错了什么？ 如何在PLSQL条件逻辑中使用我的字符串表？

I am building an application that uses PHP to get data from an Oracle10g database server. My input form has a series of checkboxes that are submitted to the processing page via an array (code filtered down to the relevant elements):

The processing page calls an Oracle procedure that uses a custom data type "STRING_TABLE" (CREATE OR REPLACE TYPE STRING_TABLE AS TABLE OF VARCHAR (1000);) to translate the checkbox values, which PHP processes as an array, into a table.

My procedure takes those checkboxes as the incoming parameter "p_queue_type" (code filtered down to the relevant elements):

PROCEDURE get_TLFQ_results (

p1 OUT SYS_REFCURSOR,

p_queue_type IN STRING_TABLE

)

IS

v_return_sql VARCHAR2(32767) := '';

BEGIN

IF ('HOLD' IN (SELECT COLUMN_VALUE AS queue_type FROM TABLE (p_queue_type))) THEN

--compile query string

END IF;

IF ('CR' IN (SELECT COLUMN_VALUE AS queue_type FROM TABLE (p_queue_type))) THEN

--compile query string

END IF;

-- Execute the query string and store the results in a cursor

OPEN p1 FOR v_return_sql;

END get_TLFQ_results;

When I attempt to compile my procedure, I am getting this Oracle error:

[Error] PLS-00405 (4215: 23): PLS-00405: subquery not allowed in this context

What am I doing wrong? How can I use my string table within my PLSQL conditional logic?

原文：https://stackoverflow.com/questions/27927883

更新时间：2020-04-02 22:04

最满意答案

您不能以这种方式在PL / SQL IF条件中使用SELECT语句。 但是你可以这样做：

IF ('HOLD' member of p_queue_type) THEN

...

You cannot use a SELECT statement within a PL/SQL IF condition in that way. However you can do this:

IF ('HOLD' member of p_queue_type) THEN

...

2015-01-13

相关问答

只要单击单选按钮，就可以使用JavaScript onChange事件来运行函数。 然后，您可以根据单击的按钮显示

。 function displayQuestion(answer) {

document.getElementById(answer + 'Question').style.display = "block";

if (answer == "yes") { // hide the div that is not selected

docu

...

尝试这个： bool_value_string varchar2(5)

bool_value_string = case when bool_value then 'true' else 'false' end;

select * from mytable mt

where

(bool_value_string = 'true' and mt.criterion_1 = value)

or

(bool_value_string = 'false' and mt.criterion_2 = v

...

return语句中对this.message的引用不会触发computedMessage进行更新。 这是因为它在逻辑||位置 声明使其无法访问。 这是Vue.js计算属性文档中记录的问题 。 来自Docs： status: function () {

return this.validated

? this.okMsg

: this.errMsg // errMsg isn't accessible; won't trigger updates to sta

...

我“想”这就是你所追求的： function TK

{

param([int]$OTK)

$OTK * 2

}

function MAT

{

param([int]$OMAT, $TK)

$TK * $OMAT / 5

}

function MAF

{

param([int]$OMAF,$MAT)

$MAT * $OMAF / 5

}

$OMAT = 6

$OMAF = 7

for ($OTK = 1; $OTK -le 10; $OTK++

...

我在示例1阵营。 作为一个经验法则，需要的缩进越少越好。 // Exit early if there are errors.

if ($n < 0) {

die "bad n: $n";

}

// Handle trivial cases without fuss.

if ($n == 0) {

return 0;

}

/* Now the meat of the function. */

$widget->frob($n);

foreach ($widget->blaxes

...

test和[是同样的事情。 你需要摆脱if语句中的test命令，所以它看起来像这样： if $catme; then

some commands

fi

键入man test以获取更多信息。 例如： $ v=true

$ $v

$ if $v; then

> echo "PRINTED"

> fi

PRINTED

$ v=false

$ if $v; then

> echo "PRINTED"

> fi

$

test and [ are the same thing. Y

...

BigQuery标准SQL #standardSQL

WITH YourTable AS (

SELECT 'a1' AS item, TIMESTAMP('2016-03-03 19:52:23 UTC') AS click_time, 'm' AS class, 'u1' AS userid, 4 AS score UNION ALL

SELECT 'a1' AS item, TIMESTAMP('2016-03-03 19:53:23 UTC') AS click_time, '

...

如何在单个查询中完成整个操作？ 这有用吗？ select cnt + (case when count(*) > 0 then 1 else 0 end) into cnt

from sung

where hakbun = 1;

How about doing the whole thing in a single query? Does this work? select cnt + (case when count(*) > 0 then 1 else 0 end) into cnt

fro

...

您不能以这种方式在PL / SQL IF条件中使用SELECT语句。 但是你可以这样做： IF ('HOLD' member of p_queue_type) THEN

...

You cannot use a SELECT statement within a PL/SQL IF condition in that way. However you can do this: IF ('HOLD' member of p_queue_type) THEN

...

你直接引用val ，但那是一个游标行类型。 您需要指定列名称，即使只有一个： ... ||val.generation_qtr|| ...

...在两个地方你都在连接中使用它。 所以它变得： L_sql:=L_sql ||' MAX(DECODE(generation_qtr, '||''''

|| val.generation_qtr ||''''||' cum_actual_gen)) AS '

|| val.generation_qtr ||',

...