主页 / dba / 问题 / 287474
Accepted
brudi4550
Asked: 2021-03-23 05:04:35 +0800 CST

Oracle 程序有时会无休止地运行，有时会立即完成

  • 772

该过程总是立即编译，我很确定我没有任何无限循环或其他一些错误。在 SQL Developer 中运行该过程时，它有时会立即完成（应该如此，每个表只有 5 行），而其他时候它永远不会停止运行。它与数据库或我的代码有关吗？对不起，如果代码很乱，这是我的第一个 PLSQL 程序。我正在我的大学提供的 Oracle DB 上运行该程序，因此我没有管理员权限。

CREATE OR REPLACE PROCEDURE integrate_data IS
    rowCount NUMBER;
    currId NUMBER;
    CURSOR ws1_customer_crsr IS SELECT * FROM ws1_customer;
    CURSOR ws1_product_crsr IS SELECT * FROM ws1_product;
    CURSOR ws1_order_crsr IS SELECT * FROM ws1_order o JOIN ws1_order_position p ON o.no = p.order_;
    CURSOR ws2_customer_crsr IS SELECT * FROM ws2_user u JOIN ws2_address a ON u.id = a.user_;
    CURSOR ws2_product_crsr IS SELECT * FROM ws2_product;
    CURSOR ws2_order_crsr IS SELECT * FROM ws2_purchase p JOIN ws2_entry e ON p.code = e.purchase; 
BEGIN
    SAVEPOINT beforeProcedure;
    DELETE FROM ts_transaction;
    DELETE FROM ts_customer;
    DELETE FROM ts_product;
    /* Insert customer data from ws1 */
    FOR ws1_customer_rec IN ws1_customer_crsr
    LOOP
        INSERT INTO ts_customer VALUES('ws1_' || to_char(ws1_customer_rec.id), ws1_customer_rec.email, ws1_customer_rec.address);
    END LOOP;
    /* Insert product data from ws1 */
    FOR ws1_product_rec IN ws1_product_crsr
    LOOP 
        INSERT INTO ts_product VALUES('ws1_' || to_char(ws1_product_rec.id), ws1_product_rec.name,
            ws1_product_rec.description, ws1_product_rec.price);
    END LOOP;
    /* Insert order data from ws1 */
    FOR ws1_order_rec IN ws1_order_crsr
    LOOP
        INSERT INTO ts_transaction VALUES('ws1_' || to_char(ws1_order_rec.no) || '_' || to_char(ws1_order_rec.pos_no), ws1_order_rec.date_, 
            ws1_order_rec.quantity, 'ws1_' || to_char(ws1_order_rec.customer), 'ws1_' || to_char(ws1_order_rec.product));
    END LOOP;
    /* Insert customer data from ws2, if a customer has multiple addresses only the first one gets added */
    FOR ws2_user_rec IN ws2_customer_crsr
    LOOP
        currId := ws2_user_rec.user_;
        SELECT CASE 
            WHEN EXISTS(SELECT * FROM ts_customer c WHERE c.id = 'ws2_' || currId) THEN 1
            ELSE 0
        END
        INTO rowCount
        FROM DUAL;
        IF rowCount = 0 THEN
            INSERT INTO ts_customer VALUES('ws2_' || to_char(ws2_user_rec.user_), ws2_user_rec.email, ws2_user_rec.street || ' ' || to_char(ws2_user_rec.street_no) ||
                ', ' || to_char(ws2_user_rec.postal_code) || ' ' || ws2_user_rec.city);
        END IF;
    END LOOP;
    /* Insert product data from ws2 */
    FOR ws2_product_rec IN ws2_product_crsr
    LOOP
        INSERT INTO ts_product VALUES('ws2_' || to_char(ws2_product_rec.ean), ws2_product_rec.title, ws2_product_rec.description, ws2_product_rec.sales_price);
    END LOOP;
    /* Insert order data from ws2 */
    FOR ws2_purchase_rec IN ws2_order_crsr
    LOOP
        INSERT INTO ts_transaction VALUES('ws2_' || to_char(ws2_purchase_rec.code) || '_' || to_char(ws2_purchase_rec.entry_no),
            ws2_purchase_rec.date_, ws2_purchase_rec.qty, 'ws2_' || to_char(ws2_purchase_rec.user_), 'ws2_' || to_char(ws2_purchase_rec.product));
    END LOOP;
    EXCEPTION
        WHEN OTHERS THEN
        dbms_output.put_line('An error has occurred, all work the procedure has done has been rolled backed');
        ROLLBACK TO beforeProcedure;
END;
/
oracle plsql

2 个回答

  1. Best Answer

    您的代码中没有commit。如果您从不同的并发会话运行两次，您可能会阻止自己。

    在最后添加一个提交：

        /* Insert order data from ws2 */
    FOR ws2_purchase_rec IN ws2_order_crsr
    LOOP
        INSERT INTO ts_transaction VALUES('ws2_' || to_char(ws2_purchase_rec.code) || '_' || to_char(ws2_purchase_rec.entry_no),
            ws2_purchase_rec.date_, ws2_purchase_rec.qty, 'ws2_' || to_char(ws2_purchase_rec.user_), 'ws2_' || to_char(ws2_purchase_rec.product));
    END LOOP;

    COMMIT;

    EXCEPTION
        WHEN OTHERS THEN
        dbms_output.put_line('An error has occurred, all work the procedure has done has been rolled backed');
        ROLLBACK TO beforeProcedure;
END;
/
    • 1

  2. 你真的需要SAVEPOINT吗？如果没有，请尝试TRUNCATE TABLE ...- 应该更快。然后你不需要循环，像这样尝试（未测试）：

    CREATE OR REPLACE PROCEDURE integrate_data IS
    
BEGIN
    
    EXECUTE IMMEDIATE 'TRUNCATE TABLE ts_transaction';
    EXECUTE IMMEDIATE 'TRUNCATE TABLE ts_customer';
    EXECUTE IMMEDIATE 'TRUNCATE TABLE ts_product';

    /* Insert customer data from ws1 */
    INSERT INTO ts_customer 
    SELECT 'ws1_' || TO_CHAR(id), 
        email, 
        address
    FROM ws1_customer;

    /* Insert product data from ws1 */       
    INSERT INTO ts_product 
    SELECT 'ws1_' || TO_CHAR(id), 
        name,
        description, 
        price
    FROM ws1_product;

    /* Insert order data from ws1 */
    INSERT INTO ts_transaction 
    SELECT 'ws1_' || TO_CHAR(no) || '_' || TO_CHAR(pos_no), 
        date_, 
        quantity, 
        'ws1_' || TO_CHAR(customer), 
        'ws1_' || TO_CHAR(product)
    FROM ws1_order o JOIN ws1_order_position p ON o.no = p.order_ ;

   /* Insert customer data from ws2, if a customer has multiple addresses only the first one gets added */
    INSERT INTO ts_customer
    SELECT 'ws2_' || TO_CHAR(user_), 
        email, 
        street || ' ' || TO_CHAR(street_no) ||', ' || TO_CHAR(postal_code) || ' ' || city
    FROM ws2_user u JOIN ws2_address a ON u.id = a.user_
    WHERE NOT EXISTS (
       SELECT 'x'
       FROM ts_customer 
       WHERE c.id = 'ws2_' || a.user_ );

    /* Insert product data from ws2 */
    INSERT INTO ts_product 
    SELECT 'ws2_' || TO_CHAR(ean), 
        title, 
        description, 
        sales_price
    FROM ws2_product;

    /* Insert order data from ws2 */
   INSERT INTO ts_transaction 
   SELECT 'ws2_' || TO_CHAR(code) || '_' || TO_CHAR(entry_no),
        date_, 
        qty, 
        'ws2_' || TO_CHAR(user_), 
        'ws2_' || TO_CHAR(product)
    FROM ws2_purchase p JOIN ws2_entry e ON p.code = e.purchase;

    EXCEPTION
        WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error has occurred, all work the procedure has done has been rolled backed');
        ROLLBACK;
END;
/
    • 0

相关问题