Help required on assigning value to Tabletype variable! [message #672263] |
Tue, 09 October 2018 04:29 |
ramya29p
Messages: 146 Registered: November 2007 Location: Chennai
|
Senior Member |
|
|
Hi All,
could anyone tell me other possibility of assigning the value into the tabletype variable in pl/sql.
create table emp_test(id number,name varchar2(20),deptno number, sal number)
I have created the below package for inserting data into emp_test table.
Package Specification
create or replace
PACKAGE employee_details
AS
TYPE details
IS
RECORD
(
P_EMP_ID number,
P_NAME varchar2(40),
P_SAL number);
TYPE tab_employees
IS
table of DETAILS;
p_deptno number;
procedure write_employees(
P_DEPTNO in number,
p_tab_employees IN tab_employees );
END employee_details;
PACKAGE BODY
create or replace
PACKAGE BODY employee_details
AS
PROCEDURE write_employees(
P_DEPTNO in number,
p_tab_employees IN tab_employees)
IS
begin
DBMS_OUTPUT.PUT_LINE('test count' || P_TAB_EMPLOYEES.COUNT );
for I in 1..P_TAB_EMPLOYEES.COUNT LOOP
insert into EMP_TEST(id,name,DEPTNO,SAL) values(
P_TAB_EMPLOYEES(i).P_EMP_ID,P_TAB_EMPLOYEES(i).P_NAME,P_DEPTNO,P_TAB_EMPLOYEES(i).P_SAL
);
end loop;
end WRITE_EMPLOYEES;
END employee_details ;
using the below procedure for passing the value to write into the table.
DECLARE
P_DEPTNO number;
P_TAB_EMPLOYEES EMPLOYEE_DETAILS.TAB_EMPLOYEES:=EMPLOYEE_DETAILS.TAB_EMPLOYEES();
BEGIN
P_DEPTNO := 10;
P_TAB_EMPLOYEES.extend;
P_TAB_EMPLOYEES(1).P_EMP_ID:=125;
P_TAB_EMPLOYEES(1).P_NAME:='Scott';
P_TAB_EMPLOYEES(1).P_SAL:=1000;
P_TAB_EMPLOYEES.extend;
P_TAB_EMPLOYEES(2).P_EMP_ID:=126;
P_TAB_EMPLOYEES(2).P_NAME:='Henry';
P_TAB_EMPLOYEES(2).P_SAL:=2000;
EMPLOYEE_DETAILS.WRITE_EMPLOYEES(
P_DEPTNO => P_DEPTNO,
P_TAB_EMPLOYEES => P_TAB_EMPLOYEES
);
end;
My query is instead of passing the value as below, do we have any option to pass in a single line. I mean all P_EMP_ID,P_NAME,P_SAL in a single line.
P_TAB_EMPLOYEES(1).P_EMP_ID:=125;
P_TAB_EMPLOYEES(1).P_NAME:='Scott';
P_TAB_EMPLOYEES(1).P_SAL:=1000;
|
|
|
|
|
|
|
Re: Help required on assigning value to Tabletype variable! [message #672269 is a reply to message #672265] |
Tue, 09 October 2018 08:22 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
RECORD type is targeted to fetching data. So you could do something like:
DECLARE
P_TAB_EMPLOYEES EMPLOYEE_DETAILS.TAB_EMPLOYEES:=EMPLOYEE_DETAILS.TAB_EMPLOYEES();
BEGIN
P_TAB_EMPLOYEES.EXTEND;
SELECT 125,'Scott',1000
INTO P_TAB_EMPLOYEES(1)
FROM DUAL;
END;
/
However performance will be slightly worse than straight assignment.
SY.
|
|
|