Oracle基本修練: PL/SQL SELECT INTO

ChunJen Wang

Published in

jimmy-wang

5 min readSep 12, 2021

在執行PL/SQL我們會需要配合資料庫內資料，賦值給我們所建立的變量，此時，就必須使用到SELECT INTO。

Content

SELECT INTO introduction
PL/SQL SELECT INTO — selecting one column example
PL/SQL SELECT INTO — selecting a complete row example
PL/SQL SELECT INTO — selecting data into multiple variables example
Common Errors

Let’s Start!

SELECT INTO introduction

透過SELECT INTO，我們可以將從資料庫提出資料給變量(variable)，例如

SELECT
  select_list
INTO
  variable_list
FROM
  table_name
WHERE
  condition;

選出的資料內容，一樣可以如SQL操作，進行JOIN、order、group by等。

如果SELECT語句返回多於一row，Oracle 會報錯TOO_MANY_ROWS ，
而SELECT語句沒有返回任何row，Oracle 會報錯NO_DATA_FOUND。

接著讓我們用範例資料庫中資料進行練習。

1. PL/SQL SELECT INTO — selecting one column example

從customers中提取出_id=100的顧客姓名，並存放於l_customer_name 。

DECLARE
  l_customer_name customers.name%TYPE;
BEGIN
  SELECT name INTO l_customer_name
  FROM customers
  WHERE customer_id = 100;
  dbms_output.put_line( v_customer_name );
END;

2. PL/SQL SELECT INTO — selecting a complete row example

我們也可以透過select一整個row的資料來存放到變量上，注意type採用rowtype。

DECLARE
  r_customer customers%ROWTYPE;
BEGIN 
  SELECT * INTO r_customer
  FROM customers
  WHERE customer_id = 100;  
  dbms_output.put_line( r_customer.name || ', website: ' || r_customer.website );
END;

3. PL/SQL SELECT INTO — selecting data into multiple variables example

接著是多個變量，並且我們需要從多張表中，取得我們需要的資訊。
包含需要有顧客名稱(table:customers)，聯繫人姓、名(table: contacts)。

DECLARE
  l_customer_name customers.name%TYPE;
  l_contact_first_name contacts.first_name%TYPE;
  l_contact_last_name contacts.last_name%TYPE;
BEGIN
  SELECT
    name, 
    first_name, 
    last_name
  INTO
    l_customer_name, 
    l_contact_first_name, 
    l_contact_last_name
  FROM
    customers
  INNER JOIN contacts USING( customer_id )
  WHERE
    customer_id = 100;
  dbms_output.put_line( 
    l_customer_name || ', Contact Person: ' ||
    l_contact_first_name || ' ' || l_contact_last_name );
END;