Oracle PL/SQL/System Packages/dbms output
Содержание
- 1 A small buffer
- 2 Call dbms_output.disable
- 3 Call dbms_output.new_line to create a new line
- 4 Call dbms_output.put_line to display text message
- 5 Combine text and number in DBMS_OUTPUT.PUT_LINE
- 6 dbms_output.enable
- 7 DBMS_OUTPUT.GET_LINES
- 8 dbms_output.new_line
- 9 DBMS_OUTPUT.NEW_LINE: a new line sign
- 10 dbms_output.put: display text without new line sign
- 11 Displaying Hello World! to the Screen
- 12 EXECUTE DBMS_OUTPUT.ENABLE(10000)
- 13 Output Visual line break
- 14 This script demonstrates the DBMS_OUTPUT package
- 15 Use DBMS_OUTPUT.PUT_LINE to output clob data
- 16 Use DBMS_OUTPUT.PUT_LINE to output the table collection indexed by BINARY_INTEGER
A small buffer
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> SQL> BEGIN
2 DBMS_OUTPUT.ENABLE (2000);
3
4 DBMS_OUTPUT.PUT_LINE("Three names will be written.");
5 DBMS_OUTPUT.PUT("J");
6 DBMS_OUTPUT.NEW_LINE;
7 DBMS_OUTPUT.PUT("S");
8 DBMS_OUTPUT.NEW_LINE;
9 DBMS_OUTPUT.PUT("T");
10 DBMS_OUTPUT.NEW_LINE;
11 END;
12 /
Three names will be written. J S T PL/SQL procedure successfully completed. SQL> SQL> --
</source>
Call dbms_output.disable
<source lang="sql">
SQL> -- SQL> SQL> set serveroutput on SQL> begin
2 dbms_output.put_line( "One" ); 3 dbms_output.disable; 4 end; 5 /
PL/SQL procedure successfully completed. SQL> --
</source>
Call dbms_output.new_line to create a new line
<source lang="sql">
SQL> SQL> set serverout on SQL> begin
2 dbms_output.put( "One" ); 3 dbms_output.new_line; 4 dbms_output.put_line( "Three" ); 5 dbms_output.put( "Four" ); 6 end; 7 /
One Three PL/SQL procedure successfully completed. SQL> SQL> --
</source>
Call dbms_output.put_line to display text message
<source lang="sql">
SQL> SQL> set serveroutput on SQL> begin
2 dbms_output.put_line( "One" ); 3 dbms_output.put_line( "Two" ); 4 dbms_output.put_line( "Three" ); 5 end; 6 /
One Two Three PL/SQL procedure successfully completed. SQL> --
</source>
Combine text and number in DBMS_OUTPUT.PUT_LINE
<source lang="sql">
SQL> -- SQL> SQL> DECLARE
2 v_Calc NUMBER := 0;
3 BEGIN
4 WHILE v_Calc >= 10 LOOP
5 v_Calc := v_Calc + 1;
6 DBMS_OUTPUT.PUT_LINE("The value of v_Calc is " || v_Calc);
7 END LOOP;
8 END;
9 /
PL/SQL procedure successfully completed. SQL> SQL> --
</source>
dbms_output.enable
<source lang="sql">
SQL> SQL> set serveroutput on SQL> begin
2 dbms_output.put_line( "One" ); 3 dbms_output.put_line( "Two" ); 4 dbms_output.enable; 5 dbms_output.put_line( "Three" ); 6 end; 7 /
One Two Three PL/SQL procedure successfully completed. SQL> --
</source>
DBMS_OUTPUT.GET_LINES
<source lang="sql">
SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> SQL> SET SERVEROUTPUT ON SQL> DECLARE
2 v_Data DBMS_OUTPUT.CHARARR;
3 v_NumLines NUMBER;
4 BEGIN
5 DBMS_OUTPUT.ENABLE(1000000);
6
7 DBMS_OUTPUT.PUT_LINE("Line One");
8 DBMS_OUTPUT.PUT_LINE("Line Two");
9 DBMS_OUTPUT.PUT_LINE("Line Three");
10
11 v_NumLines := 3;
12
13 DBMS_OUTPUT.GET_LINES(v_Data, v_NumLines);
14
15 FOR v_Counter IN 1..v_NumLines LOOP
16 INSERT INTO MyTable (char_col)
17 VALUES (v_Data(v_Counter));
18 END LOOP;
19 END;
20 /
PL/SQL procedure successfully completed. SQL> SQL> select * from MyTable;
NUM_COL CHAR_COL
------------------------------------------------------------
Line One
Line Two
Line Three
SQL> SQL> drop table MyTable; Table dropped. SQL> SQL>
</source>
dbms_output.new_line
<source lang="sql">
SQL> SQL> SQL> ------------------------------- SQL> set serveroutput on SQL> set lines 200 SQL> ------------------------------- SQL> DECLARE
2 BEGIN 3 dbms_output.enable(200000) ; 4 dbms_output.new_line ; 5 dbms_output.new_line ; 6 END ; 7 /
PL/SQL procedure successfully completed. SQL>
</source>
DBMS_OUTPUT.NEW_LINE: a new line sign
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT OFF SQL> SQL> BEGIN
2 DBMS_OUTPUT.ENABLE (2000);
3
4 DBMS_OUTPUT.PUT_LINE("Three names will be written.");
5 DBMS_OUTPUT.PUT("J");
6 DBMS_OUTPUT.NEW_LINE;
7 DBMS_OUTPUT.PUT("S");
8 DBMS_OUTPUT.NEW_LINE;
9 DBMS_OUTPUT.PUT("T");
10 DBMS_OUTPUT.NEW_LINE;
11 END;
12 /
PL/SQL procedure successfully completed. SQL> --
</source>
dbms_output.put: display text without new line sign
<source lang="sql">
SQL> SQL> set serverout on SQL> begin
2 dbms_output.put( "One" ); 3 dbms_output.put( "Two" ); 4 dbms_output.new_line; 5 dbms_output.put_line( "Three" ); 6 dbms_output.put( "Four" ); 7 end; 8 /
OneTwo Three PL/SQL procedure successfully completed. SQL> SQL> --
</source>
Displaying Hello World! to the Screen
<source lang="sql">
SQL> -- SQL> SQL> set serveroutput on SQL> CREATE OR REPLACE PROCEDURE HELLO AS
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE("Hello World! " ||
4 TO_CHAR(SYSDATE,"MM-DD-YY HH:MI:SS AM"));
5 END;
6 /
Procedure created. SQL> SQL> SQL> --
</source>
EXECUTE DBMS_OUTPUT.ENABLE(10000)
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> EXECUTE DBMS_OUTPUT.ENABLE(10000) SQL> SQL>
</source>
Output Visual line break
<source lang="sql">
SQL> SQL> BEGIN
2 3 dbms_output.put (CHR(10)); -- Visual line break. 4 5 END; 6 /
PL/SQL procedure successfully completed. SQL>
</source>
This script demonstrates the DBMS_OUTPUT package
<source lang="sql">
SQL> SQL> set feedback on SQL> set pages 9999 serveroutput off SQL> SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE("Oh...");
3 END;
4 /
PL/SQL procedure successfully completed. SQL> SQL> SQL> set serveroutput on SQL> SQL> / Oh... PL/SQL procedure successfully completed. SQL> SQL>
</source>
Use DBMS_OUTPUT.PUT_LINE to output clob data
<source lang="sql">
SQL> CREATE TABLE nmyTable (
2 id INTEGER PRIMARY KEY, 3 nclobData NCLOB 4 );
Table created. SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY, 3 clobData CLOB 4 );
Table created. SQL> SQL> CREATE OR REPLACE PROCEDURE nclob_example
2 AS
3 clobVariable CLOB := "It is the east and Juliet is the sun";
4 nclobVariable NCLOB;
5 BEGIN
6 INSERT INTO nmyTable (id, nclobData) VALUES (1, clobVariable);
7
8 SELECT nclobData INTO clobVariable FROM nmyTable WHERE id = 1;
9
10 DBMS_OUTPUT.PUT_LINE("clobVariable = " || clobVariable);
11 END nclob_example;
12 /
Procedure created. SQL> SQL> drop table myTable; Table dropped. SQL> drop table nmyTable; Table dropped.
</source>
Use DBMS_OUTPUT.PUT_LINE to output the table collection indexed by BINARY_INTEGER
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table emp(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 fname VARCHAR2(10 BYTE), 4 lname VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into emp(ID, fname, lname, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from emp
2 /
ID FNAME LNAME START_DAT END_DATE SALARY CITY
---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
ID FNAME LNAME START_DAT END_DATE SALARY CITY
---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
ID FNAME LNAME START_DAT END_DATE SALARY CITY
---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected. SQL> SQL> SQL> SQL> SQL> DECLARE
2 TYPE emp_table_struct IS TABLE OF emp.fname%TYPE INDEX BY BINARY_INTEGER;
3 emp_table emp_table_struct;
4 CURSOR emp_cursor IS SELECT fname FROM emp ORDER BY id;
5 v_row NUMBER := 1;
6 BEGIN
7 OPEN emp_cursor;
8 LOOP
9 FETCH emp_cursor INTO emp_table(v_row);
10 EXIT WHEN emp_cursor%NOTFOUND;
11 DBMS_OUTPUT.PUT_LINE(emp_table(v_row));
12 v_row := v_row + 1;
13 END LOOP;
14 CLOSE emp_cursor;
15 DBMS_OUTPUT.PUT_LINE("Total rows: "||emp_table.COUNT);
16 END;
17 /
PL/SQL procedure successfully completed. SQL> SQL> -- clean the table SQL> drop table emp
2 /
Table dropped.
</source>
