Oracle PL/SQL Tutorial/System Packages/DBMS OUTPUT — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Текущая версия на 10:05, 26 мая 2010
Содержание
- 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
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>
DBMS_OUTPUT.CHARARR
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>
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>
EXECUTE DBMS_OUTPUT.ENABLE(10000)
SQL>
SQL> SET ECHO ON
SQL> EXECUTE DBMS_OUTPUT.ENABLE(10000)
SQL>
SQL>
Output character or line
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.
Output employee name with pl/sql, cursor and dbms_output.put_line
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.
Output varchar variable
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>
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>
q for quotation with []
SQL>
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE (q"[What"s a quote among friends?]");
3 END;
4 /
PL/SQL procedure successfully completed.
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.