• 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

    专注于自动化、性能研究,博客为原创,转载请注明文章来源于:http://www.cnblogs.com/Automation_software/ 只求在IT界有一个清闲的世界让我静心的去专研,不求功名利禄,只为心中的那份成就感及自我成长、自我实现的快感。
  • 相关阅读:
    hdu 1896 stones
    各种类型的取值范围
    RSS/PSS/VSZ
    kasan BUG log
    ARM机器码分析
    Linux进程状态
    谢宝友: 深入理解RCU之七:分级RCU实现
    rcu_preempt detected stalls on CPUs/tasks
    Linux 内核 hlist
    linux cmd
  • 原文地址:https://www.cnblogs.com/Automation_software/p/2003332.html
Copyright © 2020-2023  润新知