oracle使用游标进行循环数据插入

建表语句:

begin
execute immediate ' drop table Customer';
exception when others then
null;
end;

 

begin
execute immediate ' drop table OrderHistory';
exception when others then
null;
end;  

  -- Create Customer Table
CREATE TABLE Customers
(   
    Customer_Id NUMBER(16) NOT NULL,
    Name VARCHAR(25),
    CONSTRAINT cus_id_pk PRIMARY KEY(Customer_Id )
)


-- Create Order Table
CREATE TABLE Orders
(   
    Order_Id  NUMBER(16) NOT NULL,
    Name VARCHAR(25),
    Customer_Id NUMBER(16) NOT NULL,
    CONSTRAINT ord_id_pk PRIMARY KEY(Order_Id ),
    CONSTRAINT cus_id_fk FOREIGN KEY (Customer_Id) REFERENCES Customers(Customer_Id)
)

 

 

-- Create Order History Table

begin
execute immediate ' drop table OrderHistory';
exception when others then
null;
end;

CREATE TABLE OrderHistory
(
    OrderHistory_Id NUMBER(16),
    CustomerName VARCHAR(25),
    OrderName VARCHAR(25),
    CONSTRAINT OrderHistory_Id_pk PRIMARY KEY(OrderHistory_Id )
)

插入数据的存储过程:

CREATE   PROCEDURE  spAddOrderHistory
(
     @CustomerName   VARCHAR ( 25 ),
     @OrderName   VARCHAR ( 25 )
)
AS
BEGIN
     INSERT   INTO  OrderHistory(CustomerName,OrderName)
     VALUES ( @CustomerName ,  @OrderName )
END

使用游标进行数据插入:

--  use cursor to insert data into order history table
DECLARE   @customer_name   VARCHAR ( 25 )
DECLARE   @order_name   VARCHAR ( 25 )

DECLARE  curOrder  CURSOR  READ_ONLY
FOR
SELECT  c.Name  as   [ Customer Name ] , o.Name  as   [ Order Name ]
FROM  Customers c  INNER   JOIN  Orders o
     ON  c.CustomerId  =  o.Customer_Id
ORDER   BY   [ Customer Name ] ,  [ Order Name ]

OPEN  curOrder

FETCH   NEXT   FROM  curOrder
INTO   @customer_name ,  @order_name

WHILE   @@FETCH_STATUS   =   0     
BEGIN
     EXEC  spAddOrderHistory  @customer_name ,  @order_name  
           
     FETCH   NEXT   FROM  curOrder  INTO   @customer_name ,  @order_name
END

CLOSE  curOrder
DEALLOCATE  curOrder

运行结果:

C2 O2 by C2
C1 O3 by C1
C2 O4 by C2
C1 O1 by C1
C1 O5 by C1
C2 O6 by C2

 

转载于:https://www.cnblogs.com/Automation_software/archive/2011/04/04/2003332.html