Oracle PL/SQL - Inserts and updates using record variables

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

Description

Inserts and updates using record variables

Demo

SQL>
SQL> drop table emp;

Table dropped.-- www  .  j  a v a2 s.  c  om
SQL>
SQL> create table emp(
  2    empno    number(4,0),
  3    ename    varchar2(10),
  4    job      varchar2(9),
  5    mgr      number(4,0),
  6    hiredate date,
  7    sal      number(7,2),
  8    comm     number(7,2),
  9    deptno   number(2,0)
 10  );

Table created.

Elapsed: 00:00:00.00
SQL>
SQL>
SQL> insert into emp values(7369, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10);
SQL> insert into emp values(7698, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30);
SQL>
SQL> create or replace procedure p_insertNewEmp(i_deptno number)
  2  is
  3        v_emp_rec emp%ROWTYPE;
  4  begin
  5        select max(empNo)+1
  6           into v_emp_rec.empNo
  7           from emp;
  8        v_emp_rec.eName:='Emp#'||v_emp_rec.empNo;
  9        v_emp_rec.deptNo:=i_deptno;
 10        v_emp_rec.sal := 100;
 11
 12        insert into emp
 13        values v_emp_rec;
 14  end;
 15  /

Procedure created.
SQL>

Related Topic