如何从sql过程打印结果？ [英] how can I print results from sql procedure?

问题描述

我正在编写一个程序来对数据库中每个表中的行进行计数。到目前为止看起来像这样：

I'm writing a procedure to count rows in every table in my database. It so far looks like this:

create or replace procedure count_database_rows()
dynamic result sets 1
P1: begin atomic
DECLARE stmt CHAR(40);--
  FOR v1 AS
      c1 CURSOR FOR
   SELECT TABLE_SCHEMA, TABLE_NAME FROM sysibm.tables
    DO
       SET stmt = 'SELECT COUNT(*) FROM '||TABLE_SCHEMA||'.'||TABLE_NAME;--
       PREPARE s FROM stmt;--
       EXECUTE s;--
  END FOR;--
end P1
~

但是，当我运行它：

db2 -ntd~ -f script.sql > dump.csv

我得到的只是：

DB20000I  The SQL command completed successfully.

我该如何打印所有结果？

how can I print all results instead?

推荐答案

仅用于演示。我假设这是一项教育性的任务，它是LUW的Db2。

Just for demonstration. I assume, that it's some educational task, and it's Db2 for LUW.

--#SET TERMINATOR @
CREATE OR REPLACE FUNCTION COUNT_DATABASE_ROWS()
RETURNS TABLE (P_TABSCHEMA VARCHAR(128), P_TABNAME VARCHAR(128), P_ROWS BIGINT)
BEGIN
  DECLARE L_STMT VARCHAR(256);
  DECLARE L_ROWS BIGINT;

  FOR V1 AS 
    SELECT TABSCHEMA, TABNAME 
    FROM SYSCAT.TABLES 
    WHERE TYPE IN ('T', 'S')
    FETCH FIRST 10 ROWS ONLY
  DO
    SET L_STMT = 'SET ? = (SELECT COUNT(*) FROM "'||V1.TABSCHEMA||'"."'||V1.TABNAME||'")';
    PREPARE S FROM L_STMT;
    EXECUTE S INTO L_ROWS;
    PIPE(V1.TABSCHEMA, V1.TABNAME, L_ROWS);
  END FOR;
  RETURN;
END@

SELECT * FROM TABLE(COUNT_DATABASE_ROWS())@

对于LUW系统的任何Db2

对于DPF系统而言，有些棘手，但也可行。我们必须将内联化合物语句中不允许的代码包装到存储过程中。

For any Db2 for LUW systems

A little bit tricky for DPF systems, but doable as well. We have to wrap the code which is not allowed in the inlined compound statement into the stored procedure.

--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE COUNT_DATABASE_ROWS_DPF(OUT P_DOC XML)
READS SQL DATA
BEGIN 
  DECLARE L_STMT VARCHAR(256);
  DECLARE L_ROWS BIGINT;
  DECLARE L_NODE XML;

  SET P_DOC = XMLELEMENT(NAME "DOC");

  FOR V1 AS 
    SELECT TABSCHEMA, TABNAME 
    FROM SYSCAT.TABLES 
    WHERE TYPE IN ('T', 'S')
    FETCH FIRST 10 ROWS ONLY
  DO
    SET L_STMT = 'SET ? = (SELECT COUNT(*) FROM "'||V1.TABSCHEMA||'"."'||V1.TABNAME||'")';
    PREPARE S FROM L_STMT;
    EXECUTE S INTO L_ROWS;
    SET L_NODE = XMLELEMENT
    (
      NAME "NODE"
    , XMLELEMENT(NAME "TABSCHEMA", V1.TABSCHEMA)
    , XMLELEMENT(NAME "TABNAME", V1.TABNAME)
    , XMLELEMENT(NAME "ROWS", L_ROWS)
    );
    SET P_DOC = XMLQUERY
    (
      'transform copy $mydoc := $doc modify do insert $node as last into $mydoc return $mydoc'
      passing P_DOC as "doc", L_NODE as "node"
    );
  END FOR;
END@

CREATE OR REPLACE FUNCTION COUNT_DATABASE_ROWS_DPF()
RETURNS TABLE (P_TABSCHEMA VARCHAR(128), P_TABNAME VARCHAR(128), P_ROWS BIGINT)
BEGIN ATOMIC
  DECLARE L_DOC XML;

  CALL COUNT_DATABASE_ROWS_DPF(L_DOC);
  RETURN
  SELECT *
  FROM XMLTABLE ('$D/NODE' PASSING L_DOC AS "D" COLUMNS 
    TYPESCHEMA VARCHAR(128) PATH 'TABSCHEMA'
  , TABNAME    VARCHAR(128) PATH 'TABNAME'
  , LENGTH     BIGINT       PATH 'ROWS'
  );
END@

-- Usage. Either CALL or SELECT:
CALL COUNT_DATABASE_ROWS_DPF(?)@
SELECT * FROM TABLE(COUNT_DATABASE_ROWS_DPF())@

这篇关于如何从sql过程打印结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！