Oracle DBA FAQ - Working with Database Objects in PL/SQL

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6

(Continued from previous part...)

How To Assign Data of the Deleted Row to Variables?

If a DELETE statement is deleting a single row, you can assign column values of the deleted row to variables by using the RETURNING clause, which an extension of DELETE statements for PL/SQL. The tutorial script shows you how to do this:

CREATE TABLE emp_temp AS SELECT * FROM employees;

DECLARE
  fname VARCHAR2(10);
  lname VARCHAR2(10);
BEGIN
  DELETE FROM emp_temp WHERE employee_id = 100;
    RETURNING first_name, last_name INTO fname, lname;
  DBMS_OUTPUT.PUT_LINE('Name deleted = ' || fname || ' '
    || lname);

  -- This will not work because multiple rows deleted
  -- DELETE FROM emp_temp WHERE employee_id > 100;
    -- RETURNING first_name, last_name INTO fname, lname;
END;
/
Name deleted = Steven King

Similar to SELECT ... INTO, RETURNING ... INTO will not work if multiple rows are deleted.

What Is a RECORD in PL/SQL?

RECORD is a composite data type in PL/SQL. It can have many fields representing data elements with different data types. Variables of RECORD type can be designed to hold data from database table rows. To use RECORD data type, you need to define a specific RECORD type with each field defined with a name and a data type. Then you can use your specific RECORD type to define your RECORD type variables.

How To Define a Specific RECORD Type?

If you want to define a specific RECORD type, you need to use the TYPE ... IS RECORD statement in the declaration part of any procedure or function. The following example script defines a RECORD type called STUDENT:

CREATE OR REPLACE PROCEDURE HELLO AS
  TYPE student IS RECORD (
    id NUMBER(5),
    first_name VARCHAR(80),
    last_name VARCHAR(80)
  );
BEGIN
  NULL;
END;
/

How To Define a Variable of a Specific RECORD Type?

Once you have your specific RECORD type defined, you can define new variables with this specific RECORD type like any other data type. In the sample script below, several variables are defined with a regular data type and a specific RECORD type:

CREATE OR REPLACE PROCEDURE HELLO AS
  TYPE student IS RECORD (
    id NUMBER(5),
    first_name VARCHAR(80),
    last_name VARCHAR(80)
  );
  best_student student;
  another_student student;
  class_name VARCHAR2(80);
BEGIN
  NULL;
END;
/

How To Assign Values to Data Fields in RECORD Variables?

If a variable is a RECORD variable, you can assign values to its data fields by using fields names prefixed with variable name as "variable.field_name". Here is a sample script assigning values to data fields of RECORD variables:

CREATE OR REPLACE PROCEDURE HELLO AS
  TYPE student IS RECORD (
    id NUMBER(5),
    first_name VARCHAR(80),
    last_name VARCHAR(80)
  );
  best_student student;
  another_student student;
  class_name VARCHAR2(80);
BEGIN
  class_name := 'FYI Center 2006';
  best_student.first_name := 'The';
  best_student.last_name := 'Best';
END;
/

(Continued on next part...)

Part: 1 2 3 4 5 6

More...