在 oracle 过程中设置 serveroutput on
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13114220/
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
set serveroutput on in oracle procedure
提问by bicycle
I've created a simple procedure. In this procedure i want to output some data. However where ever i put set serveroutput on
我创建了一个简单的程序。在这个过程中，我想输出一些数据。但是，我在哪里设置了服务器输出
it says
它说
Error(26,5): PLS-00103: Encountered the symbol "SERVEROUTPUT" when expecting one of the following: . ( ) , * @ % & = - + < / > at in is mod remainder not rem => <> or != or ~= >= <= <> and or like like2 like4 likec as between || multiset member submultiset
错误（26,5）：PLS-00103：在期望以下之一时遇到符号“SERVEROUTPUT”：。( ) , * @ % & = - + < /> at in 是 mod 余数不是 rem => <> 或 != 或 ~= >= <= <> 和或 like2 like4 likec as between || 多集成员子多集
It doesn't matter where i put it, it keeps saying it.
不管我把它放在哪里，它一直在说。
create or replace PROCEDURE discount
is --- signature
BEGIN --- executable part
update dvd set me_our_price = me_our_price*0.90 WHERE me_release_year = 2011;
update dvd set me_our_price = me_our_price*0.80 WHERE me_release_year = 2010;
update bluray set me_our_price = me_our_price*0.95 WHERE me_release_year = 2011;
update bluray set me_our_price = me_our_price*0.90 WHERE me_release_year = 2010;
DBMS_OUTPUT.PUT_LINE(' Blurays ');
for i in (
SELECT e.mo_title, e.mo_bluray.me_list_price as me_list_price, e.mo_bluray.me_our_price as me_our_price FROM movie e where e.mo_bluray is not null
)
loop
DBMS_OUTPUT.PUT_LINE(i.mo_title|| ' ' || i.me_list_price|| ' ' || i.me_list_price);
end loop;
DBMS_OUTPUT.PUT_LINE(' DVDs ');
for i in (
set serveroutput on
SELECT e.mo_title, e.mo_dvd.me_list_price as me_list_price, e.mo_dvd.me_our_price as me_our_price FROM movie e where e.mo_dvd is not null
)
loop
DBMS_OUTPUT.PUT_LINE(i.mo_title|| ' ' || i.me_list_price|| ' ' || i.me_list_price);
end loop;
END discount;
回答by Ashish Burnwal
To understand the use of "SET SERVEROUTPUT ON" I will take an example
为了理解“SET SERVEROUTPUT ON”的用法，我举个例子
DECLARE
a number(10) :=10;
BEGIN
dbms_output.put_line(a) ;
dbms_output.put_line('Hello World ! ') ;
END ;
With an output : PL/SQl procedure successfully completed i.e without the expected output
有输出：PL/SQl 程序成功完成，即没有预期的输出
And the main reason behind is that ,whatever we pass inside dbms_output.put_line(' ARGUMENT '/VALUES)i.e. ARGUMENT/VALUES , is internally stored inside a buffer in SGA(Shared Global Area ) memory area upto 2000 bytes .
背后的主要原因是，无论我们在dbms_output.put_line(' ARGUMENT '/VALUES)内传递什么，即 ARGUMENT/VALUES ，都在内部存储在 SGA（共享全局区域）内存区域的缓冲区中，最多 2000 字节。
*NOTE :***However one should note that this buffer is only created when we use **dbms_outputpackage. And we need to set the environment variable only once for a session !!
*注意：***但是应该注意，只有在我们使用 **dbms_output包时才会创建此缓冲区。并且我们只需要为一个会话设置一次环境变量！！
And in order to fetch it from that buffer we need to set the environment variable for the session . It makes a lot of confusion to the beginners that we are setting the server output on ( because of its nomenclature ) , but unfortunately its nothing like that . Using SET SERVER OUTPUT ONare just telling the PL/SQL engine that
为了从该缓冲区中获取它，我们需要为 session 设置环境变量。我们正在设置服务器输出（因为它的命名法）让初学者感到很困惑，但不幸的是它不是那样的。使用SET SERVER OUTPUT ON只是告诉 PL/SQL 引擎
*Hey please print the ARGUMENT/VALUES that I will be passing inside dbms_output.put_line
and in turn PL/SQl run time engine prints the argument on the main console .
*嘿，请打印我将在 dbms_output.put_line
中传递的 ARGUMENT/VALUES，然后 PL/SQl 运行时引擎在主控制台上打印参数。
I think I am clear to you all . Wish you all the best . To know more about it with the architectural structureof Oracle Server Engineyou can see my answer on Quora http://qr.ae/RojAn8
我想我对你们都很清楚。祝你一切顺利 。要通过Oracle Server Engine的架构结构进一步了解它， 您可以在 Quora http://qr.ae/RojAn8上查看我的回答
And to answer your question "One should use SET SERVER OUTPUT in the beginning of the session. "
并回答您的问题“一个人应该在会话开始时使用 SET SERVER OUTPUT。”
回答by DCookie
"SET serveroutput ON" is a SQL*Plus command and is not valid PL/SQL.
“SET serveroutput ON”是一个 SQL*Plus 命令，不是有效的 PL/SQL。
回答by Luis Armando
First add next code in your sp:
首先在您的 sp 中添加下一个代码：
BEGIN
dbms_output.enable();
dbms_output.put_line ('TEST LINE');
END;
Compile your code in your Oracle SQL developer. So go to Menu View--> dbms output. Click on Icon Green Plus and select your schema. Run your sp now.
在您的 Oracle SQL 开发人员中编译您的代码。所以转到菜单视图--> dbms 输出。单击 Icon Green Plus 并选择您的架构。现在运行你的 sp。
回答by Steve Bauman
Actually, you need to call SET SERVEROUTPUT ON;before the BEGINcall.
实际上，您需要在调用SET SERVEROUTPUT ON;之前先BEGIN调用。
Everyone suggested this but offers no advice where to actually place the line:
每个人都建议这样做，但没有提供实际放置线的建议：
SET SERVEROUTPUT ON;
BEGIN
FOR rec in (SELECT * FROM EMPLOYEES) LOOP
DBMS_OUTPUT.PUT_LINE(rec.EmployeeName);
ENDLOOP;
END;
Otherwise, you won't see any output.
否则，您将看不到任何输出。
回答by Bablu Gope
If you want to execute any procedure then firstly you have to set serveroutput on in the sqldeveloper work environment like.
-> SET SERVEROUTPUT ON;
-> BEGIN
dbms_output.put_line ('Hello World..');
dbms_output.put_line('Its displaying the values only for the Testing purpose');
END;
/
回答by LOULOU
Procedure successful but any outpout
程序成功但有任何输出
Error line1: Unexpected identifier
错误行 1：意外的标识符
Here is the code:
这是代码：
SET SERVEROUTPUT ON
DECLARE
-- Curseurs
CURSOR c1 IS
SELECT RWID FROM J_EVT
WHERE DT_SYST < TO_DATE(TO_CHAR(SYSDATE,'DD/MM') || '/' || TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY')) - 3));
-- Collections
TYPE tc1 IS TABLE OF c1%RWTYPE;
-- Variables de type record
rtc1 tc1;
vCpt NUMBER:=0;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO rtc1 LIMIT 5000;
FORALL i IN 1..rtc1.COUNT
DELETE FROM J_EVT
WHERE RWID = rtc1(i).RWID;
COMMIT;
-- Nombres lus : 5025651
FOR i IN 1..rtc1.COUNT LOOP
vCpt := vCpt + SQL%BULK_RWCOUNT(i);
END LOOP;
EXIT WHEN c1%NOTFOUND;
END LOOP;
CLOSE c1;
COMMIT;
DBMS_OUTPUT.PUT_LINE ('Nombres supprimes : ' || TO_CHAR(vCpt));
END;
/
exit
