Oracle PL/SQL Tutorial/System Packages/DBMS OUTPUT
Содержание
- 1 Call substr in DBMS_OUTPUT.PUT_LINE
- 2 DBMS_OUTPUT.CHARARR
- 3 dbms_output.new_line
- 4 EXECUTE DBMS_OUTPUT.ENABLE(10000)
- 5 Output character or line
- 6 Output employee name with pl/sql, cursor and dbms_output.put_line
- 7 Output varchar variable
- 8 Output Visual line break
- 9 q for quotation with []
- 10 Use DBMS_OUTPUT.PUT_LINE to output clob data
- 11 Use DBMS_OUTPUT.PUT_LINE to output the table collection indexed by BINARY_INTEGER
Call substr in DBMS_OUTPUT.PUT_LINE
<source lang="sql">
SQL> SQL> SQL> DECLARE
2 v_string VARCHAR2(500 CHAR); 3 BEGIN 4 v_string := "recognize this, often ..."; 5 DBMS_OUTPUT.PUT_LINE(SUBSTR(v_string, 1, 2)); 6 DBMS_OUTPUT.PUT_LINE(SUBSTR(v_string, 6, 8)); 7 END; 8 /
re nize thi PL/SQL procedure successfully completed. SQL></source>
DBMS_OUTPUT.CHARARR
<source lang="sql">
SQL> SQL> CREATE OR REPLACE PACKAGE xbuff
2 IS 3 contents DBMS_OUTPUT.CHARARR; 4 PROCEDURE dumpit; 5 END; 6 /
Package created. SQL> CREATE OR REPLACE PACKAGE BODY xbuff
2 IS 3 PROCEDURE dumpit 4 IS 5 tempbuff DBMS_OUTPUT.CHARARR; 6 linenum PLS_INTEGER := 1000; 7 BEGIN 8 DBMS_OUTPUT.GET_LINES (tempbuff, linenum); 9 IF linenum > 0 10 THEN 11 linenum := tempbuff.FIRST; 12 LOOP 13 EXIT WHEN linenum IS NULL; 14 contents (NVL (contents.LAST, 0) + 1) := tempbuff(linenum); 15 linenum := tempbuff.NEXT (linenum); 16 END LOOP; 17 END IF; 18 END; 19 END; 20 /
Package body created. 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>
EXECUTE DBMS_OUTPUT.ENABLE(10000)
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> EXECUTE DBMS_OUTPUT.ENABLE(10000) SQL> SQL></source>
Output character or line
<source lang="sql">
SQL> SQL> SET SERVEROUTPUT ON SQL> BEGIN
2 DBMS_OUTPUT.PUT(CHR(10));
3 DBMS_OUTPUT.PUT_LINE("PRINT ME");
4 DBMS_OUTPUT.PUT(CHR(10));
5 END;
6 /
PRINT ME PL/SQL procedure successfully completed.</source>
Output employee name with pl/sql, cursor and dbms_output.put_line
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created. SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created.
SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> SQL> begin
2 for i in ( select ename from emp ) loop 3 dbms_output.put_line(i.ename); 4 end loop; 5 end; 6 /
Jack Wil Jane Mary Chris Smart Peter Take Fake PL/SQL procedure successfully completed. SQL> SQL> drop table emp; Table dropped.</source>
Output varchar variable
<source lang="sql">
SQL> SQL> DECLARE
2 v_string VARCHAR2(500 CHAR);
3 BEGIN
4 DBMS_OUTPUT.PUT(CHR(10));
5 DBMS_OUTPUT.PUT_LINE("releases");
6 DBMS_OUTPUT.PUT(CHR(10));
7 v_string := "y""ve "||"recognize this, often ...";
8 DBMS_OUTPUT.PUT_LINE(v_string);
9 END;
10 /
releases y"ve recognize this, often ... PL/SQL procedure successfully completed. SQL> 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>
q for quotation with []
<source lang="sql">
SQL> SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE (q"[What"s a quote among friends?]"); 3 END; 4 /
PL/SQL procedure successfully completed. 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>
