时间戳

首页 > 解决方案 > 如何将查询结果保存在 plsql 的变量中

oracle - 如何将查询结果保存在 plsql 的变量中

问题描述

我正在PLSQL中开发一个程序来读取XML文件，并且在其中我有一个查询，我想将这个查询的结果保存在一个变量中。

以下是我希望您的结果存储在变量中的查询：

SELECT TEXTO FROM gzvcatg
         UNPIVOT ((codigo, texto) FOR gzvcatg_external_code IN (
            (gzvcatg_external_code1, gzvcatg_desc1) AS '1')
         WHERE GZVCATG_GROUP = 'TIT_ELEC'
            AND CODIGO = 'PATH';

以下是我正在开发的读取XML文件的程序

create or replace PROCEDURE P_FILEUPLOAD_XML (P_CMTT_CODE IN NUMBER DEFAULT 15, P_TEXT IN VARCHAR2, P_TEXT_NAR IN VARCHAR2, P_PATH IN VARCHAR2, P_FILENAME IN VARCHAR2, P_RET_VAL OUT NUMBER) IS

l_dir       CONSTANT VARCHAR2(35) := P_PATH;
l_fil       CONSTANT VARCHAR2(30) := P_FILENAME; 
l_loc       BFILE; -- Pointer to the BFILE
l_ret       BOOLEAN := FALSE; -- Return value
l_pos       NUMBER := 1; -- Current position in the file (file begins at position 1)
l_sum       number default 8000; -- Amount of characters have been read
l_buf       VARCHAR2(32767); -- Read Buffer
l_sen       CONSTANT RAW(100) := UTL_RAW.CAST_TO_RAW(CHR(10)); -- Character at the end of the file is NEWLINE (ascii = 10)
l_end       NUMBER; -- End of the current word which will be read
l_counter NUMBER := 0; -- Counter for line sequence
l_line      VARCHAR2(32767); -- Contains the info line by line for insert

BEGIN

     -- Mapping the physical file with the pointer to the BFILE
        l_loc := BFILENAME('PATH','TIT_ELEC');

    -- Open the file in READ_ONLY mode
       DBMS_LOB.OPEN(l_loc,DBMS_LOB.LOB_READONLY);
       LOOP
          l_counter := l_counter + 1; -- Counter for sequence
          -- Calculate the end of the current word
          l_end := DBMS_LOB.INSTR(l_loc,l_sen,l_pos,1);

          -- Process end-of-file
          IF (l_end = 0) THEN
            l_end := DBMS_LOB.INSTR(l_loc,l_sen,l_pos,1);
            l_sum := l_end - l_pos - 1;
            EXIT;
          END IF;

          -- Read until end-of-file
          l_sum := l_end - l_pos;
          DBMS_LOB.READ(l_loc,l_sum,l_pos,l_buf);
          l_line := UTL_RAW.CAST_TO_VARCHAR2(l_buf);

        BEGIN 
            INSERT INTO SPRCMNT (
                         SPRCMNT_CMTT_CODE,
                         SPRCMNT_TEXT,
                         SPRCMNT_TEXT_NAR)
                                VALUES(P_CMTT_CODE,
                                       P_TEXT,
                                       P_TEXT_NAR);    
             EXCEPTION WHEN OTHERS THEN 
             ROLLBACK;           
        END;
    END LOOP;    
END;

我需要知道如何将查询结果存储在变量中，然后BFILENAME在我的过程中将其用作函数参数

更新：

我的查询结果是单行中的文件夹路径

在此处输入图像描述

标签： oracleplsql

解决方案

DBMS_XMLGEN 可能足以满足您的需求。它将进行查询并返回结果的 XML，而无需太多努力。

SQL> select dbms_xmlgen.getxml('select * from emp') xml from dual;

XML
-------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <EMPNO>7369</EMPNO>
  <ENAME>SMITH</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7902</MGR>
  <HIREDATE>17-DEC-80</HIREDATE>
  <SAL>800</SAL>
  <DEPTNO>20</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7499</EMPNO>
  <ENAME>ALLEN</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>20-FEB-81</HIREDATE>
  <SAL>1600</SAL>
  <COMM>300</COMM>
  <DEPTNO>30</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7521</EMPNO>
  <ENAME>WARD</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>22-FEB-81</HIREDATE>
  <SAL>1250</SAL>
  <COMM>500</COMM>
  <DEPTNO>30</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7566</EMPNO>
  <ENAME>JONES</ENAME>
  <JOB>MANAGER</JOB>
  <MGR>7839</MGR>
  <HIREDATE>02-APR-81</HIREDATE>
  <SAL>2975</SAL>
  <DEPTNO>20</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7654</EMPNO>
  <ENAME>MARTIN</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>28-SEP-81</HIREDATE>
  <SAL>1250</SAL>
  <COMM>1400</COMM>
  <DEPTNO>30</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7698</EMPNO>
  <ENAME>BLAKE</ENAME>
  <JOB>MANAGER</JOB>
  <MGR>7839</MGR>
  <HIREDATE>01-MAY-81</HIREDATE>
  <SAL>2850</SAL>
  <DEPTNO>30</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7782</EMPNO>
  <ENAME>CLARK</ENAME>
  <JOB>MANAGER</JOB>
  <MGR>7839</MGR>
  <HIREDATE>09-JUN-81</HIREDATE>
  <SAL>2450</SAL>
  <DEPTNO>10</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7788</EMPNO>
  <ENAME>SCOTT</ENAME>
  <JOB>ANALYST</JOB>
  <MGR>7566</MGR>
  <HIREDATE>09-DEC-82</HIREDATE>
  <SAL>3000</SAL>
  <DEPTNO>20</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7839</EMPNO>
  <ENAME>KING</ENAME>
  <JOB>PRESIDENT</JOB>
  <HIREDATE>17-NOV-81</HIREDATE>
  <SAL>5000</SAL>
  <DEPTNO>10</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7844</EMPNO>
  <ENAME>TURNER</ENAME>
  <JOB>SALESMAN</JOB>
  <MGR>7698</MGR>
  <HIREDATE>08-SEP-81</HIREDATE>
  <SAL>1500</SAL>
  <DEPTNO>30</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7876</EMPNO>
  <ENAME>ADAMS</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7788</MGR>
  <HIREDATE>12-JAN-83</HIREDATE>
  <SAL>1100</SAL>
  <DEPTNO>20</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7900</EMPNO>
  <ENAME>JAMES</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7698</MGR>
  <HIREDATE>03-DEC-81</HIREDATE>
  <SAL>950</SAL>
  <DEPTNO>30</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7902</EMPNO>
  <ENAME>FORD</ENAME>
  <JOB>ANALYST</JOB>
  <MGR>7566</MGR>
  <HIREDATE>03-DEC-81</HIREDATE>
  <SAL>3000</SAL>
  <DEPTNO>20</DEPTNO>
 </ROW>
 <ROW>
  <EMPNO>7934</EMPNO>
  <ENAME>MILLER</ENAME>
  <JOB>CLERK</JOB>
  <MGR>7782</MGR>
  <HIREDATE>23-JAN-82</HIREDATE>
  <SAL>1300</SAL>
  <DEPTNO>10</DEPTNO>
 </ROW>
</ROWSET>

推荐阅读