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
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> --
Call dbms_output.disable
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> --
Call dbms_output.new_line to create a new line
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> --
Call dbms_output.put_line to display text message
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> --
Combine text and number in DBMS_OUTPUT.PUT_LINE
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> --
dbms_output.enable
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> --
DBMS_OUTPUT.GET_LINES
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>
dbms_output.new_line
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>
DBMS_OUTPUT.NEW_LINE: a new line sign
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> --
dbms_output.put: display text without new line sign
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> --
Displaying Hello World! to the Screen
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> --
EXECUTE DBMS_OUTPUT.ENABLE(10000)
SQL>
SQL> SET ECHO ON
SQL> EXECUTE DBMS_OUTPUT.ENABLE(10000)
SQL>
SQL>
Output Visual line break
SQL>
SQL> BEGIN
2
3 dbms_output.put (CHR(10)); -- Visual line break.
4
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
This script demonstrates the DBMS_OUTPUT package
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>
Use DBMS_OUTPUT.PUT_LINE to output clob data
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.
Use DBMS_OUTPUT.PUT_LINE to output the table collection indexed by BINARY_INTEGER
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.