Oracle PL/SQL - Combining Ways of Assigning Variable Values

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

Introduction

You can combine methods of assigning variable values.

Demo

SQL>
SQL>--  w w w .java2s.c  o  m
SQL> drop table emp;

Table dropped.
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.
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 function f_generateNewEmp_rec
  2        (i_deptno number)
  3  return emp%ROWTYPE
  4   is
  5       v_emp1_rec emp%ROWTYPE;
  6  begin
  7        select max(empNo)+1
  8          into v_emp1_rec.empNo
  9          from emp;
 10       v_emp1_rec.deptNo:=i_deptNo;
 11       v_emp1_rec.eName:='Emp#'||v_emp1_rec.empNo;
 12        return v_emp1_rec;
 13  end;
 14  /

Function created.
SQL> declare
  2       v_emp_rec emp%ROWTYPE;
  3  begin
  4       v_emp_rec:=f_generateNewEmp_rec(10);
  5        DBMS_OUTPUT.put_line('Generated:'||v_emp_rec.empNo||' '||v_emp_rec.eName);
  6  end;
  7  /
Generated:7699 Emp#7699

PL/SQL procedure successfully completed.
SQL>

Related Topic