Oracle PL/SQL - Updating Rows with Records

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. RECORD Types

Introduction

The PL/SQL can update one or more table rows with a record.

The record must represent a row of the table.

The following code updates the first three weeks of the table schedule by putting the new values in a record and updating the first three rows of the table with that record.

Demo

SQL>
SQL> DROP TABLE schedule;

Table dropped.--   w ww .j  a  va 2 s  .  c  om

SQL> CREATE TABLE schedule (
  2    week  NUMBER,
  3    Mon   VARCHAR2(10),
  4    Tue   VARCHAR2(10),
  5    Wed   VARCHAR2(10),
  6    Thu   VARCHAR2(10),
  7    Fri   VARCHAR2(10),
  8    Sat   VARCHAR2(10),
  9    Sun   VARCHAR2(10)
 10   );
SQL> DECLARE
  2    default_week  schedule%ROWTYPE;
  3  BEGIN
  4    default_week.Mon := 'Day Off';
  5    default_week.Tue := '0900-1800';
  6    default_week.Wed := '0900-1800';
  7    default_week.Thu := '0900-1800';
  8    default_week.Fri := '0900-1800';
  9    default_week.Sat := '0900-1800';
 10    default_week.Sun := 'Day Off';
 11
 12    FOR i IN 1..3 LOOP
 13      default_week.week    := i;
 14
 15      UPDATE schedule
 16      SET ROW = default_week
 17      WHERE week = i;
 18    END LOOP;
 19  END;
 20  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT * FROM schedule;

no rows selected

SQL>

Related Topic