圈子推荐
查看所有吧>>
活跃用户
    atlasdb 兼容oracle 特性之dbms_output

    dbms_output通常用于显示消息和报告。


    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


    • 分享到:
    排序方式:回复时间 共有0条评论