Example of a stored procedure for retrieving data from an Oracle database

> Insert the obtained data from one 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

);

-- In the program trial phase, satrt will be deleted later.

I _count: = I _count + 1;

IF I _count> 100 THEN

COMMIT;

RETURN;

End if;

-- The end will be deleted in the trial phase.

End loop;

-- Data commit, which avoids a large amount of data during program debugging and is temporarily disabled

-- Commit;

-------- Exception Handling Section ----------------------------

EXCEPTION

WHEN OTHERS THEN

Rollback;

END SRBZ_GET_SRBZ_KD_SPEED;

/

2. Use statement concatenation to implement the insert function:

Note: 2.1 string constants are enclosed in quotation marks. 2.2 variables are enclosed in quotation marks.

Create or replace procedure abc (

-- Parameter list:

Task_id IN number, -- task id

Task_name IN varchar2, -- Task Name

In_NAME IN varchar2 -- name

)

--------------------------- PROCEDURE name: ABC

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

-- Reference: table_src data source table

-- Table_to insert data into the target table

-- In_bdw_name restrictions on data source tables: domestic network name

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

IS

-- INSERT_STRING: insert statement storage location dynamically generated

INSERT_STRING VARCHAR2 (1000 );

-- Data source table Storage

TableSrc varchar2 (500 );

-- Insert data to the destination table storage location

TableTarget varchar2 (1000 );

-- Store the fields to be inserted into the target table

StrFields varchar2 (1000 );

BEGIN

-- Data source table: AAA

TableTarget: = BBB;

-- Data Source

TableSrc: = select seq_kd.nextval, sysdate, | task_id |, | task_name |,

| A. * from AAA

| Where a. value = | IP-VPDN

| And a. remark is null;

-- Fields to be inserted

StrFields: = (ROW_ID, GET_DATA_DT, TASK_ID, TASK_NAME, COST_CTR, SERVICE_ID,

| SI_ADDR, SI_SERVER_ID );

-- Generate an insert statement

INSERT_STRING: = insert into | tableTarget | StrFields | tableSrc;

-- Execute the insert statement

Execute immediate INSERT_STRING;

-- Submit a transaction

Commit;

-------- Exception Handling Section -------------------------------------------------------

EXCEPTION

-- Exception throw

WHEN OTHERS THEN

-- DBMS_OUTPUT.PUT_LINE );

Rollback;

END SRBZ_GET_SRBZ_KD_GSVPDN_FREE;

/