用于矩形棱柱体积计算的 PL/SQL 绑定变量 [英] PL/SQL BIND VARIABLES for Rectangular Prism Volume Calculation

问题描述

我已经解决了这个问题，并使其在声明的替换变量时正常工作，但我无法正确计算 BIND 变量.有人告诉我它与 SQL*PLUS 和 Oracle 的开发人员混淆.这是我最初回答正确的问题，但 BIND 变量部分不起作用.学习绑定变量问题

I've worked on this question and have it working properly for substitution variables declared, but I'm having trouble getting it to calculate properly for BIND variables. I've been told it's confusing with SQL*PLUS and Oracle's Developer. Here is my initial question that was answered correctly, but the BIND variable part is not working. Learning Bind Variables Question

所以我有使用替换变量计算矩形棱镜体积的代码，但想使用这样声明的 BIND 变量.我的教科书上说我必须使用PRINT并在结束后打印；/命令并且不显示 dbms_output 所以这可能是一个问题.这不是一种非常实用的做事方式，我理解这一点.

So I have that code which calculates the volume of a Rectangular Prism using substitution variables, but wanted to use BIND variables declared like this. My textbook says that I have to use PRINT and print after the end; / command and doesn't show the dbms_output so that might be a problem. It's not a very practical way of doing things, I understand that.

SET SERVEROUTPUT ON
VARIABLE d_length NUMBER;
VARIABLE d_height NUMBER;
VARIABLE d_width NUMBER;

DECLARE 
    d_volume    NUMBER;
BEGIN
    :d_length := &q_length;
    :d_height := &q_height;
    :d_width := &q_width;

    DBMS_OUTPUT.PUT_LINE('The length dimension is: ' || :d_length);
    DBMS_OUTPUT.PUT_LINE('The height dimension is: ' || :d_height);
    DBMS_OUTPUT.PUT_LINE('The width dimension is: ' || :d_width);  

    d_volume := :d_length * :d_height * :d_width;

    DBMS_OUTPUT.PUT_LINE(
        'The rectangular prism volume for the swimming pool is: ' 
           || d_volume);

END;
/

所以我的问题是，如何让它与 BIND 变量一起工作，我将变量放在块外作为 VARIABLES，然后声明 d_volume，执行该计算，并使用这些绑定变量打印出游泳池的体积.我就在这附近，但有些不对劲.它打印出 dbms_output 语句，但不显示任何变量.

So my question is, how to get it to work with BIND variables, where I put the variables outside the block as VARIABLES, then declare d_volume, perform that calculation, and PRINT out the volume of the swimming pool using those bind variables. I'm close here, but something is off. It prints out the dbms_output statement, but doesn't show anything for the variables.

推荐答案

如上一个问题的答案所述，并且在 APC 的评论中，绑定变量在这里并没有给你太多，但这似乎是一个练习，所以......你的代码用 dbms_output 显示值OK.使用 PRINT相反，您不能在 PL/SQL 块中声明 d_volume 因为当您退出块时它会超出范围，因此您需要将其设为 variable还有:

As noted on the answer to your previous question, and in APC's comment, bind variables aren't giving you much here, but it seems to be an exercise, so... The code you have displays the values OK with dbms_output. To use PRINT instead, you can't declare d_volume in the PL/SQL block as it'll be out of scope when you exit the block, so you need to make that a variable as well:

VARIABLE d_length NUMBER;
VARIABLE d_height NUMBER;
VARIABLE d_width NUMBER;
VARIABLE d_volume NUMBER;

BEGIN
    :d_length := &q_length;
    :d_height := &q_height;
    :d_width := &q_width;

    :d_volume := :d_length * :d_height * :d_width;
END;
/

print d_length
print d_height
print d_width
print d_volume

在 SQL*Plus 中，使用 set verify off 删除一些垃圾，给出:

Which in SQL*Plus, with set verify off to remove some cruft, gives:

Enter value for q_length: 3
Enter value for q_height: 4
Enter value for q_width: 5

PL/SQL procedure successfully completed.


  D_LENGTH
----------
         3


  D_HEIGHT
----------
         4


   D_WIDTH
----------
         5


  D_VOLUME
----------
        60

SQL>

奇怪的是，这在 SQL Developer(3.1.07 或 3.2.20)中不太适用；:d_volume := :d_length * :d_height * :d_width; 行没有按预期分配值，因此报告为空.您可以将 select :d_length * :d_height * :d_width into :d_volume from dual; 代替，这是有道理的，因为它们是SQL 语句中的占位符".看起来你仍然不能在块中引用 :d_volume(即如果你 dbms_output 它被报告为 null)，但它由 print<显示/代码>.

Curiously that doesn't quite work in SQL Developer (3.1.07 or 3.2.20); the line :d_volume := :d_length * :d_height * :d_width; doesn't assign a value as expected, so it's reported as null. You can do select :d_length * :d_height * :d_width into :d_volume from dual; instead, which makes some sense as they are 'placeholders in SQL statements'. It appears you still can't then reference :d_volume within the block (i.e. it's reported as null if you dbms_output it), but it is shown by print.

BEGIN
    :d_length := &q_length;
    :d_height := &q_height;
    :d_width := &q_width;

    select :d_length * :d_height * :d_width into :d_volume from dual;
    dbms_output.put_line('d_volume inside the block: ' || :d_volume);
END;
/

anonymous block completed
d_volume inside the block: 

D_LENGTH
-
3

D_HEIGHT
-
4

D_WIDTH
-
5

D_VOLUME
--
60

<小时>

有趣的是，dbms_output.put_line(':d_volume'); 在 SQL Developer 中显示了类似 :ZSqlDevUnIq8 的内容；在 SQL*Plus 中，它显示 :d_volume.

---

Interestingly, dbms_output.put_line(':d_volume'); shows something like :ZSqlDevUnIq8 in SQL Developer; in SQL*Plus it shows :d_volume.

这篇关于用于矩形棱柱体积计算的 PL/SQL 绑定变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！