在PL-SQL错误中选择：选择后INTO

3 回答

• 0

  假设您真正想要的是使用一些参数查询数据库，那么您有一些选择：
  1-使用sqlplus或plsql developer "command window"或"sql window"与这样的查询：

  select d.r                          "R",
           e.amount                     "Amount",
           e.inv_da                     "InvoiceData",
           e.product                    "ProductId",
           d.system_time                "Date",
           d.action_code                "Status",
           e.term_rrn                   "IRRN",
           d.commiount                  "Commission",
           0                            "CardStatus"
      from docs d
     inner join ext_inv e on d.id = e.or_document
     inner join term t on t.id = d.term_id
     where d.system_time >= &p_StartDate
       and d.system_time <= &p_EndDate
       and e.need_r = 1
       and t.term_gr_id = &p_ClientID;
  

  系统将提示您为参数赋值 .

  2-你可以使用plsql（虽然我不明白为什么）但是你需要一个显式游标
  例如，如果您使用"test window"：

  declare
    -- Local variables here
    p_StartDate date := to_date('10/15/2012');
    p_EndDate   date := to_date('10/16/2012');
    p_ClientID  integer := 000192;
  begin
    -- Test statements here
    OPEN :src FOR select d.r                          "R",
           e.amount                     "Amount",
           e.inv_da                     "InvoiceData",
           e.product                    "ProductId",
           d.system_time                "Date",
           d.action_code                "Status",
           e.term_rrn                   "IRRN",
           d.commiount                  "Commission",
           0                            "CardStatus"
      from docs d
     inner join ext_inv e on d.id = e.or_document
     inner join term t on t.id = d.term_id
     where d.system_time >= p_StartDate
       and d.system_time <= p_EndDate
       and e.need_r = 1
       and t.term_gr_id = p_ClientID;
  end
  

  请注意，您需要在下表中添加一个名为“src”的变量并键入“cursor”，运行plsql块后它将保存结果集

  回复于 2024-05-14T05:35:01+08:00
• 0

  这些列需要存储在某种类型的结构中 . 像这个例子中给出的那样

  DECLARE
    deptid        employees.department_id%TYPE;
    jobid         employees.job_id%TYPE;
    emp_rec       employees%ROWTYPE;
  **Create type structure**
    TYPE emp_tab IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
    all_emps      emp_tab;
  BEGIN
    SELECT department_id, job_id INTO deptid, jobid 
       FROM employees WHERE employee_id = 140;
    IF SQL%FOUND THEN 
      DBMS_OUTPUT.PUT_LINE('Dept Id: ' || deptid || ', Job Id: ' || jobid);
    END IF;
    SELECT * INTO emp_rec FROM employees WHERE employee_id = 105;
    SELECT * INTO all_emps FROM employees;  **//storing into all_emp type structure**
    DBMS_OUTPUT.PUT_LINE('Number of rows: ' || SQL%ROWCOUNT);
  END;
  /
  

  回复于 2024-05-14T05:35:01+08:00
• 0

  你可以尝试这个解决方案：

  set serveroutput on
  declare
    -- Local variables here
    p_StartDate date := to_date('10/15/2012');
    p_EndDate   date := to_date('10/16/2012');
    p_ClientID  integer := 000192;
  begin
    for cur in ( select d.r                          "R",
                        e.amount                     "Amount",
                        e.inv_da                     "InvoiceData",
                        e.product                    "ProductId",
                        d.system_time                "Date",
                        d.action_code                "Status",
                        e.term_rrn                   "IRRN",
                        d.commiount                  "Commission",
                        0                            "CardStatus"
                   from docs d
                   inner join ext_inv e on d.id = e.or_document
                   inner join term t on t.id = d.term_id
                   where d.system_time >= p_StartDate
                     and d.system_time <= p_EndDate
                     and e.need_r = 1
                     and t.term_gr_id = p_ClientID)
     LOOP
         dbms_output.put_line('R: '||cur.R||'Amount:  '||cur.Amount/*...*/);
     END LOOP;
  
  end;
  /
  

  回复于 2024-05-14T05:35:01+08:00