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 a Table Row to a RECORD Variable?

If you have a table, and want to assign a data row of that table to a RECORD variable, you need to define this RECORD variable to match the table column structure, then use the SELECT ... INTO statement to assign a data row that RECORD variable. The script below shows you how to do this:

CREATE OR REPLACE PROCEDURE FYI_CENTER AS
  manager employees%ROWTYPE;
BEGIN
  SELECT * INTO manager FROM employees 
    WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE('My manager = ' || 
    manager.first_name || ' ' || manager.last_name);
END;
/
My manager = Steven King

How To Insert a Record into a Table?

If you have a RECORD variable with data fields matching a table structure, you can insert a row to this table with this RECORD variable using the INSERT statement as shown in the example below:

CREATE TABLE emp_temp AS SELECT * FROM employees;

CREATE OR REPLACE PROCEDURE FYI_CENTER AS
  manager employees%ROWTYPE;
BEGIN
  SELECT * INTO manager FROM employees
    WHERE employee_id = 100;
  manager.employee_id := 299;
  INSERT INTO emp_temp VALUES manager;
  DBMS_OUTPUT.PUT_LINE('# rows inserted = '
    || SQL%ROWCOUNT);
END;
/
# rows inserted = 1

How To Update a Table Row with a Record?

If you have a RECORD variable with data fields matching a table structure, you can update a row in this table with this RECORD variable using the UPDATE ... SET ROW statement as shown in the sample script below:

CREATE TABLE emp_temp AS SELECT * FROM employees;

CREATE OR REPLACE PROCEDURE FYI_CENTER AS
  manager employees%ROWTYPE;
BEGIN
  SELECT * INTO manager FROM employees
    WHERE employee_id = 100;
  manager.employee_id := 299;
  INSERT INTO emp_temp VALUES manager;
  manager.first_name := 'FYI';
  manager.last_name := 'Center';
  UPDATE emp_temp SET ROW = manager WHERE employee_id = 299;
  DBMS_OUTPUT.PUT_LINE('# rows updated = ' || SQL%ROWCOUNT); 
END;
/
# rows updated = 1

How To Define a Variable to Match a Table Column Data Type?

If you have a table, and want to define some variables to have exactly the same data types as some columns in that table, you can use table_name.column_name%TYPE as data types to define those variables. The tutorial sample below shows you how to do this:

CREATE OR REPLACE PROCEDURE FYI_CENTER AS
  my_email employees.email%TYPE;
  my_salary employees.salary%TYPE;
BEGIN
  SELECT email, salary INTO my_email, my_salary
    FROM employees WHERE employee_id = 101;
  DBMS_OUTPUT.PUT_LINE('My email = ' || my_email);
  DBMS_OUTPUT.PUT_LINE('My salary = ' || my_salary);
END;
/
My email = NKOCHHAR
My salary = 17000

Part: 1 2 3 4 5 6

More...