时间戳

首页 > 解决方案 > 使用游标/过程/函数连接 2 个表

java - 使用游标/过程/函数连接 2 个表

问题描述

我有以下表格：

CREATE TABLE branch (
    branch_code    NUMBER,
    branch_name   VARCHAR2(80)
);

CREATE TABLE client (
    client_nr       NUMBER,
    client_add   VARCHAR2(40),
    client_name     VARCHAR2(60)
);

CREATE TABLE insurance (
    account_no                      VARCHAR2(50),
    insurance_type                VARCHAR2(40),
    insurace_company_code       VARCHAR2(40),
    company_details   VARCHAR2(200)
);

CREATE TABLE credit (
    account_no             VARCHAR2(40),
    branch_code         NUMBER,
    loan_amount    FLOAT,
    amount _to _refund   FLOAT,
    account_type            VARCHAR2(30),
    credit_type         VARCHAR2(30),
    interest_rate       FLOAT,
    client_nr           NUMBER
);

--INTEGRITY CONSTRAINTS

ALTER TABLE branch ADD CONSTRAINT pk_branch PRIMARY KEY ( branch_code );

ALTER TABLE client ADD CONSTRAINT pk_client PRIMARY KEY ( client_nr );

ALTER TABLE credit
    ADD CONSTRAINT fk_client_nr FOREIGN KEY ( client_nr )
        REFERENCES client ( client_nr ) ON DELETE SET NULL;

ALTER TABLE credit
    ADD CONSTRAINT fk_branch_code FOREIGN KEY ( branch_code )
        REFERENCES branch ( branch_code ) ON DELETE SET NULL;

我必须使用游标/程序/函数来确定每个客户的利率。

我目前在解决这个问题时遇到问题，因为我收到错误“ora-01422：exact fetch 返回的行数超过请求的行数”。我使用 select 语句解决了这个问题，但我必须使用函数/过程或游标任务。

这是我使用的 sql select 语句：

SELECT
    client.client_nr,
    nvl(interest_rate, 0) dobanda
FROM
    credit
    FULL OUTER JOIN client ON credit.client_nr = client.client_nr
ORDER BY
    client.client_nr;

感谢您不厌其烦地帮助我。

标签： javasqlplsqloracle11g

解决方案

哪个使用光标/过程/功能？首先，您需要清楚地了解每一个是什么。下面是一个相当简化的版本。

  • 游标是查询的结果。因此，您提到的解决方案是游标的必要先决条件。给定的游标具有特定的查询。还有另一种称为引用游标的类型，它可以保存不同游标的结果（但最终必须解析为特定的结果集定义）。通常，在定义/生成的位置不处理引用游标，而在定义的时间/位置处理游标。
  • 过程或函数都是存储在数据库中的程序，可以根据需要使用。它们接近相同，但具有不同的预期用途。简而言之，一个过程对您的数据执行操作，一个函数检索信息。过程可以通过 OUT 参数返回信息，但这不是预期用途，尽管有时很有用。函数必须返回单个“数据库”对象。SQL 通常可以调用更多的函数，而不能从 SQL 调用过程。

示例过程处理光标

create or replace procedure client_credit_cursor
is 
    cursor c_client_credit is 
        select
            client.client_nr,
            nvl(interest_rate, 0) dobanda
        from
            client  
            left outer join credit  
                    on credit.client_nr = client.client_nr
        order by
            client.client_nr;
            
    client_cursor_rec c_client_credit%rowtype;
    
begin 
   open c_client_credit; 
   loop
      -- process each row from cursor
      fetch c_client_credit 
       into client_cursor_rec;
      exit when c_client_credit%notfound;
      
      -- process single rew returned by cursor
      dbms_output.put_line( 'Client: ' || client_cursor_rec.client_nr || 
                            ' Interest_rate: ' || client_cursor_rec.dobanda 
                          ) ; 
   end loop;
   close c_client_credit;
   
end client_credit_cursor;

以上使用了所谓的显式游标。还有另一种变体，称为隐式游标。我会把它留给你的研究。隐式光标通常是变体。

返回引用游标的函数示例。

create or replace function customer_credit_interest
  return sys_refcursor
is
  l_client_credit sys_refcursor;
begin 
   open l_client_credit for 
        select
            client.client_nr,
            nvl(interest_rate, 0) dobanda
        from
            client  
            left outer join credit  
                    on credit.client_nr = client.client_nr
        order by
            client.client_nr;
    return l_client_credit ;
end customer_credit_interest;

有关运行它们的 each 和匿名块的示例，请参见 fiddle here。

推荐阅读