<legend id='3p2eb'><style id='3p2eb'><dir id='3p2eb'><q id='3p2eb'></q></dir></style></legend>

      <i id='3p2eb'><tr id='3p2eb'><dt id='3p2eb'><q id='3p2eb'><span id='3p2eb'><b id='3p2eb'><form id='3p2eb'><ins id='3p2eb'></ins><ul id='3p2eb'></ul><sub id='3p2eb'></sub></form><legend id='3p2eb'></legend><bdo id='3p2eb'><pre id='3p2eb'><center id='3p2eb'></center></pre></bdo></b><th id='3p2eb'></th></span></q></dt></tr></i><div id='3p2eb'><tfoot id='3p2eb'></tfoot><dl id='3p2eb'><fieldset id='3p2eb'></fieldset></dl></div>

      <tfoot id='3p2eb'></tfoot>
    1. <small id='3p2eb'></small><noframes id='3p2eb'>

      • <bdo id='3p2eb'></bdo><ul id='3p2eb'></ul>

    2. 如何像在 T-SQL 中一样在 PL/SQL 中声明和使用变量?

      How do I declare and use variables in PL/SQL like I do in T-SQL?(如何像在 T-SQL 中一样在 PL/SQL 中声明和使用变量?)

      <i id='XpQW1'><tr id='XpQW1'><dt id='XpQW1'><q id='XpQW1'><span id='XpQW1'><b id='XpQW1'><form id='XpQW1'><ins id='XpQW1'></ins><ul id='XpQW1'></ul><sub id='XpQW1'></sub></form><legend id='XpQW1'></legend><bdo id='XpQW1'><pre id='XpQW1'><center id='XpQW1'></center></pre></bdo></b><th id='XpQW1'></th></span></q></dt></tr></i><div id='XpQW1'><tfoot id='XpQW1'></tfoot><dl id='XpQW1'><fieldset id='XpQW1'></fieldset></dl></div>

              • <bdo id='XpQW1'></bdo><ul id='XpQW1'></ul>
                <tfoot id='XpQW1'></tfoot>

              • <small id='XpQW1'></small><noframes id='XpQW1'>

                  <tbody id='XpQW1'></tbody>
              • <legend id='XpQW1'><style id='XpQW1'><dir id='XpQW1'><q id='XpQW1'></q></dir></style></legend>
                本文介绍了如何像在 T-SQL 中一样在 PL/SQL 中声明和使用变量?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                问题描述

                在 Sql Server 中,当我测试存储过程的主体时,我经常将主体复制到 SSMS,在页面顶部声明变量,将它们设置为一些示例值,然后将主体作为-是.

                In Sql Server, often times when I'm testing the body of a stored procedure, I copy the body into SSMS, DECLARE the variables at the top of the page, set them to some sample values, and execute the body as-is.

                例如,如果我的 proc 是

                For Example, if my proc is

                CREATE PROC MySampleProc
                    @Name   VARCHAR(20)
                AS
                    SELECT @Name
                

                那么我的测试 sql 将是

                Then my test sql would be

                DECLARE @Name VARCHAR(20)
                SET     @Name = 'Tom'
                
                    SELECT @Name
                

                与此等效的 Oracle PL/SQL 是什么?

                What is the Oracle PL/SQL equivalent to this?

                这是我想出的最接近的,但我得到PLS-00428:在这个 SELECT 语句中需要一个 INTO 子句"

                This is the closest that I've come up with, but I'm getting "PLS-00428: an INTO clause is expected in this SELECT statement"

                DECLARE
                   myname varchar2(20);
                BEGIN
                     myname := 'Tom';
                
                     select myname from DUAL;
                END;
                

                这是我真正想做的一个更好的例子:

                This is a better example of what I'm really trying to do:

                DECLARE
                   myname varchar2(20);
                BEGIN
                     myname := 'Tom';
                
                     SELECT *
                     FROM   Customers
                     WHERE  Name = myname;
                END;
                

                但同样,当我真的只想将记录打印在屏幕上,而不是存储在另一个表中时,它想要一个INTO"......

                But again, it wants an 'INTO' when really I just want the records printed on the screen, not stored in another table....

                已解决:

                感谢@Allan,我让它运行良好.Oracle SQL Developer 显然会记住您提供给它的参数值.但是,PL/SQL Developer 不想与此有任何关系....

                Thanks to @Allan, I've got it working well enough. Oracle SQL Developer apparently remembers the parameter values you supply it with. PL/SQL Developer, however, wants nothing to do with this....

                如果您以脚本方式运行",它将遵循您的默认设置,但只会以 ASCI 文本形式返回结果,而不是以网格/电子表格形式返回结果

                If you "Run As Script", it will abide by your defaults, but it will only return results as ASCI text, not in a grid/spreadsheet

                推荐答案

                修改后的答案

                如果您不是从另一个程序调用此代码,则可以选择跳过 PL/SQL 并使用绑定变量在 SQL 中严格执行:

                If you're not calling this code from another program, an option is to skip PL/SQL and do it strictly in SQL using bind variables:

                var myname varchar2(20);
                
                exec :myname := 'Tom';
                
                SELECT *
                FROM   Customers
                WHERE  Name = :myname;
                

                在许多工具(例如 Toad 和 SQL Developer)中,省略 varexec 语句会导致程序提示您输入值.

                In many tools (such as Toad and SQL Developer), omitting the var and exec statements will cause the program to prompt you for the value.

                原答案

                T-SQL 和 PL/SQL 之间的一个很大区别是 Oracle 不允许您隐式返回查询结果.结果总是必须以某种方式显式返回.最简单的方法是使用DBMS_OUTPUT(大致相当于print)输出变量:

                A big difference between T-SQL and PL/SQL is that Oracle doesn't let you implicitly return the result of a query. The result always has to be explicitly returned in some fashion. The simplest way is to use DBMS_OUTPUT (roughly equivalent to print) to output the variable:

                DECLARE
                   myname varchar2(20);
                BEGIN
                     myname := 'Tom';
                
                     dbms_output.print_line(myname);
                END;
                

                但是,如果您尝试返回结果集,这并不是很有帮助.在这种情况下,您要么想要返回一个集合,要么想要返回一个 refcursor.但是,使用这些解决方案中的任何一个都需要将您的代码包装在一个函数或过程中,并从能够使用结果的东西中运行该函数/过程.以这种方式工作的函数可能如下所示:

                This isn't terribly helpful if you're trying to return a result set, however. In that case, you'll either want to return a collection or a refcursor. However, using either of those solutions would require wrapping your code in a function or procedure and running the function/procedure from something that's capable of consuming the results. A function that worked in this way might look something like this:

                CREATE FUNCTION my_function (myname in varchar2)
                     my_refcursor out sys_refcursor
                BEGIN
                     open my_refcursor for
                     SELECT *
                     FROM   Customers
                     WHERE  Name = myname;
                
                     return my_refcursor;
                END my_function;
                

                这篇关于如何像在 T-SQL 中一样在 PL/SQL 中声明和使用变量?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                相关文档推荐

                How to redirect the output of DBMS_OUTPUT.PUT_LINE to a file?(如何将 DBMS_OUTPUT.PUT_LINE 的输出重定向到文件?)
                How do I get column datatype in Oracle with PL-SQL with low privileges?(如何使用低权限的 PL-SQL 在 Oracle 中获取列数据类型?)
                Get a list of all functions and procedures in an Oracle database(获取 Oracle 数据库中所有函数和过程的列表)
                Why cannot I create triggers on objects owned by SYS?(为什么我不能在 SYS 拥有的对象上创建触发器?)
                Returning result even for elements in IN list that don#39;t exist in table(即使对于表中不存在的 IN 列表中的元素也返回结果)
                Reset Sequence in oracle 11g(oracle 11g 中的重置序列)

                • <small id='a92NP'></small><noframes id='a92NP'>

                    <legend id='a92NP'><style id='a92NP'><dir id='a92NP'><q id='a92NP'></q></dir></style></legend>
                    <i id='a92NP'><tr id='a92NP'><dt id='a92NP'><q id='a92NP'><span id='a92NP'><b id='a92NP'><form id='a92NP'><ins id='a92NP'></ins><ul id='a92NP'></ul><sub id='a92NP'></sub></form><legend id='a92NP'></legend><bdo id='a92NP'><pre id='a92NP'><center id='a92NP'></center></pre></bdo></b><th id='a92NP'></th></span></q></dt></tr></i><div id='a92NP'><tfoot id='a92NP'></tfoot><dl id='a92NP'><fieldset id='a92NP'></fieldset></dl></div>
                        <bdo id='a92NP'></bdo><ul id='a92NP'></ul>
                            <tbody id='a92NP'></tbody>
                          <tfoot id='a92NP'></tfoot>