如何修复此SELECT语句中应包含的错误INTO子句 - How to fix error INTO clause is expected in this SELECT statement

How to fix error INTO clause is expected in this SELECT statement

我是pl/sql和xml的新手。我想通过调用用户定义函数：从表中以xml格式获取数据

此表sql:

CREATE TABLE "HIMADRI"."PAYROLLFILE" 
   (    "SALYR" NUMBER(4,0) NOT NULL ENABLE, 
    "SALMT" NUMBER(2,0) NOT NULL ENABLE, 
    "EMPID" NUMBER NOT NULL ENABLE, 
    "DPTID" NUMBER NOT NULL ENABLE, 
    "SALHD" VARCHAR2(2 BYTE) NOT NULL ENABLE, 
    "DESCR" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
    "ALAMT" FLOAT(126) NOT NULL ENABLE, 
    "OPID" NUMBER NOT NULL ENABLE, 
    "TRADT" DATE);

我想获得以下xml格式的数据：

  <transaction>
            <salary_year>SALYR</salary_year>
            <salary_month>SALMT</salary_month>
            <employee_id>EMPID</employee_id>
            <department_code>DPTID</department_code>
            <salary_head>SALHD</salary_head>
            <description>DESCR</description>
            <amount>ALAMT</amount>
            <operator_id>OPID</operator_id>
            <transaction_date>TRADT</transaction_date>
</transaction>

我应该如何编写pl/sql函数体：

FUNCTION get_all_payroll_transactions RETURN  VARCHAR2 IS  isSuccess VARCHAR2(50);
   BEGIN 
     SELECT XMLElement( "transaction",  
                    XMLElement("salary_year", SALYR),
                    XMLElement("salary_month", SALMT),
                    XMLElement("employee_id", EMPID),
                    XMLElement("department_code", DPTID),
                    XMLElement("salary_head", SALHD),
                    XMLElement("description", DESCR),
                    XMLElement("amount", ALAMT),
                    XMLElement("operator_id", OPID),
                    XMLElement("transaction_date", TRADT)
                 )AS transaction1
    FROM PAYROLLFILE ;
   END get_all_payroll_transactions;

我应该如何从php:调用函数

$stid = oci_parse($conn, " begin   
                               :result :=  PAYROLL.get_all_payroll_transactions();                      
                                end;" );    
    oci_bind_by_name($stid, ':result',$ru, 500);    
    oci_execute($stid);

请给我线索。感谢

Hi不确定PHP的调用，但当您在plsql块中进行选择时，您需要将其选择到变量中，因此在这种情况下，它看起来如下所示。您还需要显式返回该变量。在选择XML时，还需要一个比VARCHAR2（50）大得多的变量。下面是未经测试的，但给你一个方向的想法。

FUNCTION get_all_payroll_transactions RETURN  VARCHAR2 IS
  isSuccess VARCHAR2(4000);
   BEGIN 
     SELECT XMLElement( "transaction",  
                    XMLElement("salary_year", SALYR),
                    XMLElement("salary_month", SALMT),
                    XMLElement("employee_id", EMPID),
                    XMLElement("department_code", DPTID),
                    XMLElement("salary_head", SALHD),
                    XMLElement("description", DESCR),
                    XMLElement("amount", ALAMT),
                    XMLElement("operator_id", OPID),
                    XMLElement("transaction_date", TRADT)
                 )AS transaction1
    into isSuccess
    FROM PAYROLLFILE ;
  Return isSuccess;
   END get_all_payroll_transactions;