Getting data from a database in Oracle stored procedures _ Oracle Applications _ script house

This section describes the Stored Procedure example for getting data from Oracle databases. Insert the data obtained from table A to another table B.

This section describes the Stored Procedure example for getting data from Oracle databases. Insert the data obtained from table A to another table B.

How can we insert the data obtained in Table A into another table B?

(1) For tables A and B with the same structure [number of fields, type of corresponding fields, etc.], you can use

Insert into B select * FROM;

Insert into B (field1, field2, field3) select A. field1, A. field2, A. field3 from;

(2) If the number of fields in two tables is different but the structure of several fields is the same (similar to parent-child relationship), you must use insert into B (field1, field2) select. field1,. field2 from;

1. Use a cursor with parameters to implement the insert function:

Create or replace procedure GET_DATA (

-- Parameter list:

N_task_id IN number, -- task id
V_task_name IN varchar2, -- Task Name
V_name IN varchar2 -- name
)

-----------------------------------------------

-- PROCEDURE name: GET_DATA --

-- Comment content: obtain qualified data from the data source table and insert it to the target data table :--

-- Reference: n_tas_id task ID ,--

-- V_task_namek Task Name ,--

-- V_bdw_name restrictions on the data source table: Intranet name --

-----------------------------------------------

IS

-- Insert row number control

I _count number (5 );

-- Data cursor: DATA_CUR (IN_NAME)

-- Parameter: Intranet name: IN_NAME

CURSOR DATA_CUR (IN_NAME VARchar2) IS/** note: the Parameter definition does not contain precision **/
Select *
FROM GET_DATA_SRC
Where A. NAME = IN_NAME;
BEGIN

-- Counter to control the number of inserted rows

I _count: = 0;

-- Insert data cyclically

For mycur in DATA_CUR (v_name) LOOP
Insert into abc (
ROW_ID,
TASK_ID,
TASK_NAME,
GET_DATA_DT,
CUST_ID,
ASSIGN_FLAG,
DEAL_DATE
) VALUES (
SEQ_KD.NEXTVAL,
N_TASK_ID,
V_TASK_NAME,
SYSDATE,
MYCUR. CUST_ID,
'N ',
NULL
);