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,
oracle游标的使用
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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。