DBMS_OUTPUT
| 函数名 |
参数类型 |
结果类型 |
描述 |
| disable |
null |
void |
关闭消息输出 |
| enable |
null |
void |
开启消息输出 |
| enable |
buffer_size integer |
void |
消息缓冲池大小(字节) |
| get_line |
OUT line text, OUT status integer |
record |
检索一行缓冲信息 |
| get_lines |
OUT lines text[], INOUT numlines integer |
record |
从缓冲区中检索行数组 |
| new_line |
null |
void |
放置一个行尾标记 |
| put |
a text |
void |
在缓冲区中放置行的一部分 |
| put_line |
a text |
void |
在缓冲区中放置一行 |
select dbms_output.disable();
select dbms_output.enable();
select dbms_output.enable(10240);
select dbms_output.put('asda');
select dbms_output.get_line();
get_line
----------
(asda,0)
select dbms_output.put('pp1');
put
-----
(1 row)
select dbms_output.new_line();
new_line
----------
(1 row)
select dbms_output.put('pp2');
put
-----
(1 row)
select dbms_output.get_lines(1);
get_lines
-----------
({pp1},1)
CREATE TABLE emp (
empno NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
comm NUMBER(7,2),
deptno NUMBER(2) CONSTRAINT emp_ref_dept_fk
REFERENCES dept(deptno)
);
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'23-MAY-87',1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'03-DEC-81',950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'03-DEC-81',3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
CREATE OR REPLACE PROCEDURE list_emp()
AS $$
declare
v_empno NUMBER(4);
v_ename VARCHAR2(10);
emp_cur CURSOR is SELECT empno, ename FROM emp ORDER BY empno;
BEGIN
OPEN emp_cur;
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
LOOP
FETCH emp_cur INTO v_empno, v_ename;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
END LOOP;
CLOSE emp_cur;
END;
$$ LANGUAGE plpgsql;
atlasdb=# call list_emp();
NOTICE: EMPNO ENAME
NOTICE: ----- -------
NOTICE: 7369 SMITH
NOTICE: 7499 ALLEN
NOTICE: 7521 WARD
NOTICE: 7566 JONES
NOTICE: 7654 MARTIN
NOTICE: 7698 BLAKE
NOTICE: 7782 CLARK
NOTICE: 7788 SCOTT
NOTICE: 7839 KING
NOTICE: 7844 TURNER
NOTICE: 7876 ADAMS
NOTICE: 7900 JAMES
NOTICE: 7902 FORD
NOTICE: 7934 MILLER
CALL
