是否可以在游标内调用记录类型? [英] Is it possible to call a record type inside a cursor?

问题描述

我试图在我的游标中调用一个记录类型，虽然它没有给我语法错误，但它仍然无法编译.我的任务是创建一个游标，允许检索数据并处理捐赠者类型和承诺金额的多种组合作为集合输入.输出结果应为与捐赠人类型匹配且大于指定的承诺金额的每个承诺的捐赠人姓名和承诺金额.以下是表格:

I am trying to call a record type inside my cursor and although it doesn't give me syntax errors, it still can't compile. My task is to create a cursor that allows retrieving data and handling multiple combinations of donor type and pledge amount as a collection input. The output result should be the donor name and the pledge amount for each pledge that matches the donor type and is greater than the pledge amount indicated. Here are the tables:

DD_DONOR

Name      Null?    Type         
--------- -------- ------------ 
IDDONOR   NOT NULL NUMBER(4)    
FIRSTNAME          VARCHAR2(15) 
LASTNAME           VARCHAR2(30) 
TYPECODE           CHAR(1)      
STREET             VARCHAR2(40) 
CITY               VARCHAR2(20) 
STATE              CHAR(2)      
ZIP                VARCHAR2(9)  
PHONE              VARCHAR2(10) 
FAX                VARCHAR2(10) 
EMAIL              VARCHAR2(25) 
NEWS               CHAR(1)      
DTENTERED          DATE     

DD_PLEDGE

Name        Null?    Type        
----------- -------- ----------- 
IDPLEDGE    NOT NULL NUMBER(5)   
IDDONOR              NUMBER(4)   
PLEDGEDATE           DATE        
PLEDGEAMT            NUMBER(8,2) 
IDPROJ               NUMBER(5)   
IDSTATUS             NUMBER(2)   
WRITEOFF             NUMBER(8,2) 
PAYMONTHS            NUMBER(3)   
CAMPAIGN             NUMBER(4)   
FIRSTPLEDGE          CHAR(1)     

我的代码:

DECLARE
   CURSOR donnor_pledge (dtype_amount RECORD) IS
     SELECT d.FIRSTNAME, d.LASTNAME, p.PLEDGEAMT
       FROM DD_DONOR d INNER JOIN DD_PLEDGE p
         USING (IDDONOR)
       WHERE d.TYPECODE = dtype_amount.type AND 
       p.PLEDGEAMT > dtype_amount.amount;
       
   TYPE dtype_amount IS RECORD (
     type DD_DONOR.TYPECODE%TYPE := 'I',
     amount DD_PLEDGE.PLEDGEAMT%TYPE) := 250);
     
BEGIN
   OPEN donnor_pledge;
   DBMS_OUTPUT.PUT_LINE('Donor name: ' || d.FIRSTNAME || ' ' || d.LASTNAME);
   DBMS_OUTPUT.PUT_LINE('Pledge amount: ' || p.PLEDGEAMT);
   CLOSE donnor_pledge;    
END;

错误信息:

Error report -
ORA-06550: line 2, column 39:
PLS-00201: identifier 'RECORD' must be declared
ORA-06550: line 2, column 4:
PL/SQL: Item ignored
ORA-06550: line 14, column 9:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 14, column 4:
PL/SQL: SQL Statement ignored
ORA-06550: line 15, column 45:
PLS-00201: identifier 'D.FIRSTNAME' must be declared
ORA-06550: line 15, column 4:
PL/SQL: Statement ignored
ORA-06550: line 16, column 50:
PLS-00225: subprogram or cursor 'P' reference is out of scope
ORA-06550: line 16, column 4:
PL/SQL: Statement ignored
ORA-06550: line 17, column 10:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 17, column 4:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

推荐答案

在游标中使用之前需要先声明记录类型，然后再声明一个变量让记录传入游标，然后在游标中循环.

You need to declare the record type before using it in the cursor and then declare a variable for the record to pass into the cursor and then loop through the cursor.

DECLARE
  TYPE dtype_amount IS RECORD (
    typecode DD_DONOR.TYPECODE%TYPE,
    amount   DD_PLEDGE.PLEDGEAMT%TYPE
  );

  CURSOR donnor_pledge (p_typeamount dtype_amount ) IS
    SELECT d.FIRSTNAME,
           d.LASTNAME,
           p.PLEDGEAMT
    FROM   DD_DONOR d
           INNER JOIN DD_PLEDGE p
           USING (IDDONOR)
    WHERE d.TYPECODE  = p_typeamount.typecode
    AND   p.PLEDGEAMT > p_typeamount.amount;

  p_typeamount dtype_amount;
  p_name_amount donnor_pledge%ROWTYPE;
BEGIN
  p_typeamount.typecode := 'I';
  p_typeamount.amount   := 250;
  OPEN donnor_pledge(p_typeamount);
  LOOP
    FETCH donnor_pledge INTO p_name_amount;
    EXIT WHEN donnor_pledge%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Donor name: ' || p_name_amount.FIRSTNAME || ' ' || p_name_amount.LASTNAME);
    DBMS_OUTPUT.PUT_LINE('Pledge amount: ' || p_name_amount.PLEDGEAMT);
  END LOOP;
  CLOSE donnor_pledge;    
END;
/

或者，您可以在没有记录的情况下使用游标的两个参数来完成:

or, you can do it without the record using two parameters for the cursor:

DECLARE
  CURSOR donnor_pledge (p_typecode CHAR, p_amount NUMBER ) IS
    SELECT d.FIRSTNAME,
           d.LASTNAME,
           p.PLEDGEAMT
    FROM   DD_DONOR d
           INNER JOIN DD_PLEDGE p
           USING (IDDONOR)
    WHERE d.TYPECODE  = p_typecode
    AND   p.PLEDGEAMT > p_amount;
  
  p_name_amount donnor_pledge%ROWTYPE;
BEGIN
  OPEN donnor_pledge('I', 250);
  LOOP
    FETCH donnor_pledge INTO p_name_amount;
    EXIT WHEN donnor_pledge%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Donor name: ' || p_name_amount.FIRSTNAME || ' ' || p_name_amount.LASTNAME);
    DBMS_OUTPUT.PUT_LINE('Pledge amount: ' || p_name_amount.PLEDGEAMT);
  END LOOP;
  CLOSE donnor_pledge;    
END;
/

db<>fiddle 这里

这篇关于是否可以在游标内调用记录类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！