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

 

建表語句:

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