Have the following code:
--CREATE TABLE
CREATE TABLE account_temp (
sub_account_no VARCHAR2(30),
dep_no NUMBER
);
--IN THE package declare
TYPE sub_account IS RECORD OF(
sub_account_no VARCHAR2(30),
dep_no NUMBER
);
TYPE tab_sub_accounts IS TABLE OF sub_account;
Now We defined a Record Type in the PL/SQL and a collection of this record type.
--In the package body's function or procedure
var_l_tab_accounts tab_sub_accounts;
SELECT cod_account_no, td_dep_no
BULK COLLECT INTO var_l_tab_accounts
FROM TD_NOBOOK;
IF(var_l_tab_accounts.FIRST IS NOT NULL) THEN
FOR i IN var_l_tab_accounts.FIRST .. var_l_tab_accounts.LAST LOOP
DBMS_OUTPUT.put_line('sub_account_no is '|| var_l_tab_accounts(i).sub_account_no || ', dep_no is '|| var_l_tab_accounts(i).dep_no);
INSERT INTO account_temp VALUES var_l_tab_accounts(i);
END LOOP;
END IF;
We can even don't define the RECORD, but use the RECORD table as below:
TYPE tab_sub_accounts IS TABLE OF ACCOUNT_TEMP%ROWTYPE;
--ACCOUNT_TEMP is the name of the table.
Although it's very convenient to use the table directly, but I think it was not a good practice, since when the table changes, the package code also need changes to add new column; Also I think the insert into clause should also list the columns and values, not using the way above.
As a Java developer, after about one year's PL/SQL coding in the past year, I think PL/SQL should and already is playing a very important role in enterprise applications. EBS, Flexcube as well as other application products in Oracle all depend on PL/SQL heavily. Know some guy who is doing in Asset management software in Reuters also using PL/SQL a lot. Maybe I should dig a little more in PL/SQL. PL/SQL really has some advantages over Java(JDBC, ORM) or other Process-oriented/Object-Oriented Languages when interact with database. Maybe I should call it data-oriented programming language or record-oriented language.
--CREATE TABLE
CREATE TABLE account_temp (
sub_account_no VARCHAR2(30),
dep_no NUMBER
);
--IN THE package declare
TYPE sub_account IS RECORD OF(
sub_account_no VARCHAR2(30),
dep_no NUMBER
);
TYPE tab_sub_accounts IS TABLE OF sub_account;
Now We defined a Record Type in the PL/SQL and a collection of this record type.
--In the package body's function or procedure
var_l_tab_accounts tab_sub_accounts;
SELECT cod_account_no, td_dep_no
BULK COLLECT INTO var_l_tab_accounts
FROM TD_NOBOOK;
IF(var_l_tab_accounts.FIRST IS NOT NULL) THEN
FOR i IN var_l_tab_accounts.FIRST .. var_l_tab_accounts.LAST LOOP
DBMS_OUTPUT.put_line('sub_account_no is '|| var_l_tab_accounts(i).sub_account_no || ', dep_no is '|| var_l_tab_accounts(i).dep_no);
INSERT INTO account_temp VALUES var_l_tab_accounts(i);
END LOOP;
END IF;
We can even don't define the RECORD, but use the RECORD table as below:
TYPE tab_sub_accounts IS TABLE OF ACCOUNT_TEMP%ROWTYPE;
--ACCOUNT_TEMP is the name of the table.
Although it's very convenient to use the table directly, but I think it was not a good practice, since when the table changes, the package code also need changes to add new column; Also I think the insert into clause should also list the columns and values, not using the way above.
As a Java developer, after about one year's PL/SQL coding in the past year, I think PL/SQL should and already is playing a very important role in enterprise applications. EBS, Flexcube as well as other application products in Oracle all depend on PL/SQL heavily. Know some guy who is doing in Asset management software in Reuters also using PL/SQL a lot. Maybe I should dig a little more in PL/SQL. PL/SQL really has some advantages over Java(JDBC, ORM) or other Process-oriented/Object-Oriented Languages when interact with database. Maybe I should call it data-oriented programming language or record-oriented language.