建表语句:
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 )
)
插入数据的存储过程:
(
@CustomerName VARCHAR(25),
@OrderName VARCHAR(25)
)
AS
BEGIN
INSERT INTO OrderHistory(CustomerName,OrderName)
VALUES(@CustomerName, @OrderName)
END
使用游标进行数据插入:
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