в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:08:29Z

1 версия

Новая страница

== Call user defined function in DBMS_OUTPUT.PUT_LINE==



<source lang="sql">
SQL> set serveroutput on
SQL> set echo on
SQL>
SQL> CREATE OR REPLACE FUNCTION squareme(thenum number)
2 RETURN NUMBER IS
3 BEGIN
4 RETURN thenum * thenum;
5 END squareme;
6 /
Function created.
SQL>
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE("9 squared is " || squareme(9) );
3 END;
4 /
9 squared is 81
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL></source>

== DBMS_OUTPUT.CHARARR==



<source lang="sql">
SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 v_Data DBMS_OUTPUT.CHARARR;
3 v_NumLines NUMBER;
4 BEGIN
5 -- Enable the buffer first.
6 DBMS_OUTPUT.ENABLE(1000000);
7
8 DBMS_OUTPUT.PUT_LINE("Line One");
9 DBMS_OUTPUT.PUT_LINE("Line Two");
10 DBMS_OUTPUT.PUT_LINE("Line Three");
11
12 -- Set the maximum number of lines which we want to retrieve.
13 v_NumLines := 3;
14
15 DBMS_OUTPUT.GET_LINES(v_Data, v_NumLines);
16
17 FOR v_Counter IN 1..v_NumLines LOOP
18 DBMS_OUTPUT.put_line(v_Data(v_Counter));
19 END LOOP;
20 END;
21 /
Line One
Line Two
Line Three
PL/SQL procedure successfully completed.
SQL></source>

== DBMS_OUTPUT.GET_LINES==



<source lang="sql">
SQL>
SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 v_Data DBMS_OUTPUT.CHARARR;
3 v_NumLines NUMBER;
4 BEGIN
5 -- Enable the buffer first.
6 DBMS_OUTPUT.ENABLE(1000000);
7
8 DBMS_OUTPUT.PUT_LINE("Line One");
9 DBMS_OUTPUT.PUT_LINE("Line Two");
10 DBMS_OUTPUT.PUT_LINE("Line Three");
11
12 v_NumLines := 3;
13
14 DBMS_OUTPUT.GET_LINES(v_Data, v_NumLines);
15
16 FOR v_Counter IN 1..v_NumLines LOOP
17 DBMS_OUTPUT.put_line(v_Data(v_Counter));
18 END LOOP;
19 END;
20 /
Line One
Line Two
Line Three
PL/SQL procedure successfully completed.
SQL></source>

== New quoting syntax is q"[ ... ]" where the [] is the user-defined delimiter.==



<source lang="sql">
SQL> --The [] can be any character not present in the string literal.
SQL>
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE("Oracle""s are BEAUTIFUL");
3 DBMS_OUTPUT.PUT_LINE(q"!Oracle"s are BEAUTIFUL!");
4 DBMS_OUTPUT.PUT_LINE(q"[Oracle"s are BEAUTIFUL]");
5 DBMS_OUTPUT.PUT_LINE(q"<Oracle"s are BEAUTIFUL>");
6 DBMS_OUTPUT.PUT_LINE(q"%Oracle"s are BEAUTIFUL%");
7 END;
8 /
Oracle"s are BEAUTIFUL
Oracle"s are BEAUTIFUL
Oracle"s are BEAUTIFUL
Oracle"s are BEAUTIFUL
Oracle"s are BEAUTIFUL
PL/SQL procedure successfully completed.
SQL></source>

== Output NUMBER with dbms_output.put_line Procedure==



<source lang="sql">
SQL>
SQL> SET SERVEROUTPUT ON
SQL> SET ECHO ON
SQL>
SQL>
SQL> DECLARE
2 x NUMBER;
3 BEGIN
4 x := 72600;
5 dbms_output.put_line("The variable X = ");
6 dbms_output.put_line(x);
7 END;
8 /
The variable X =
72600
PL/SQL procedure successfully completed.
SQL></source>

== Output string with dbms_output.put_line Procedure==



<source lang="sql">
SQL>
SQL> SET SERVEROUTPUT ON
SQL> SET ECHO ON
SQL>
SQL>
SQL> DECLARE
2 x NUMBER;
3 BEGIN
4 x := 72600;
5 dbms_output.put_line("The variable X = ");
6 dbms_output.put_line(x);
7 END;
8 /
The variable X =
72600
PL/SQL procedure successfully completed.
SQL></source>

== PL/SQL Block Showing the Use of the dbms_output.put_line Procedure==



<source lang="sql">
SQL>
SQL> SET SERVEROUTPUT ON
SQL> SET ECHO ON
SQL>
SQL>
SQL> DECLARE
2 x NUMBER;
3 BEGIN
4 x := 72600;
5 dbms_output.put_line("The variable X = ");
6 dbms_output.put_line(x);
7 END;
8 /
The variable X =
72600
PL/SQL procedure successfully completed.
SQL></source>

== Use the LOWER function to in DBMS_OUTPUT.PUT_LINE==



<source lang="sql">
SQL>
SQL>
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(LOWER("CaSe Is nOt"));
3 END;
4 /
case is not
PL/SQL procedure successfully completed.
SQL>
SQL></source>

== Use the ROUND function in PL/SQL==



<source lang="sql">
SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 v_round NUMBER (10,4) := 12345.6789;
3 BEGIN
4
5 DBMS_OUTPUT.PUT_LINE("Default: "||ROUND(v_round));
6 DBMS_OUTPUT.PUT_LINE("+2: "||ROUND(v_round, 2));
7 DBMS_OUTPUT.PUT_LINE("-2: "||ROUND(v_round, -2));
8
9 END;
10 /
Default: 12346
+2: 12345.68
-2: 12300
PL/SQL procedure successfully completed.
SQL>
SQL></source>

== Using two single-quotes print the string correctly==



<source lang="sql">
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE("Oracle""s are BEAUTIFUL");
3 END;
4 /
Oracle"s are BEAUTIFUL
PL/SQL procedure successfully completed.
SQL></source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/PL SQL Programming/DBMS OUTPUT - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия