INSERT INTO users (username, password, email, full_name, phone)
VALUES ('alice_wong', 'p@ssw0rd!', 'alice@example.com', 'Alice Wong', '13900139000');
UPDATE users
SET phone = '13912345678', last_login = SYSDATE
WHERE username = 'alice_wong';
DELETE FROM users
WHERE last_login < ADD_MONTHS(SYSDATE, -12)
AND user_id NOT IN (SELECT user_id FROM orders);
DECLARE
v_user_id CHAR(10);
BEGIN
SELECT 'U' || LPAD(seq_user_id.NEXTVAL, 9, '0') INTO v_user_id FROM DUAL;
INSERT INTO users (user_id, username, password, email, full_name, phone)
VALUES (v_user_id, 'alice_wong', 'secure_pass123', 'alice@example.com', 'Alice Wong', '13900139000');
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('用户名或邮箱已存在');
ROLLBACK;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('注册失败: ' || SQLERRM);
ROLLBACK;
RAISE;
END;
/
DECLARE
v_user_id CHAR(10);
v_product1_id CHAR(8);
v_product2_id CHAR(8);
v_cart_exists NUMBER;
BEGIN
SELECT user_id INTO v_user_id
FROM users
WHERE username = 'alice_wong';
SELECT COUNT(*) INTO v_cart_exists
FROM cart
WHERE user_id = v_user_id;
IF v_cart_exists = 0 THEN
INSERT INTO cart (cart_id, user_id)
VALUES ('C' || LPAD(seq_cart_id.NEXTVAL, 9, '0'), v_user_id);
END IF;
SELECT product_id INTO v_product1_id
FROM products
WHERE product_name = '平板电脑 Pro';
SELECT product_id INTO v_product2_id
FROM products
WHERE product_name = '无线耳机';
add_to_cart(v_user_id, v_product1_id, 1);
add_to_cart(v_user_id, v_product2_id, 2);
COMMIT;
DBMS_OUTPUT.PUT_LINE('商品已成功添加到购物车');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('错误: 用户或商品不存在');
ROLLBACK;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('添加失败: ' || SQLERRM);
ROLLBACK;
RAISE;
END;
/
DECLARE
v_user_id CHAR(10);
v_address_id CHAR(8);
v_order_id CHAR(12);
BEGIN
SELECT user_id INTO v_user_id
FROM users
WHERE username = 'alice_wong';
SELECT address_id INTO v_address_id
FROM addresses
WHERE user_id = v_user_id AND is_default = 'Y';
create_order_from_cart(
p_user_id => v_user_id,
p_address_id => v_address_id,
p_payment_method => '微信支付',
p_payment_id => 'WXPAY20231003123456',
p_order_id => v_order_id
);
DBMS_OUTPUT.PUT_LINE('订单创建成功: ' || v_order_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('错误: 用户不存在或未设置默认地址');
ROLLBACK;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('订单创建失败: ' || SQLERRM);
ROLLBACK;
RAISE;
END;
/
UPDATE products p
SET price = price *
CASE
WHEN (SELECT category_name FROM categories c WHERE c.category_id = p.category_id) = '智能手机' THEN 0.95
WHEN price > 3000 THEN 0.90
ELSE 0.98
END
WHERE create_date < ADD_MONTHS(SYSDATE, -6);
CREATE TABLE archived_products AS
SELECT * FROM products
WHERE stock_quantity = 0 AND create_date < ADD_MONTHS(SYSDATE, -12);
DELETE FROM products
WHERE product_id IN (SELECT product_id FROM archived_products);
DECLARE
CURSOR user_cursor IS
SELECT u.user_id, a.address_id
FROM users u
JOIN addresses a ON u.user_id = a.user_id
WHERE a.is_default = 'Y'
FETCH FIRST 20 ROWS ONLY;
v_order_id CHAR(12);
BEGIN
FOR user_rec IN user_cursor LOOP
BEGIN
FOR i IN 1..ROUND(DBMS_RANDOM.VALUE(1, 3)) LOOP
create_order_from_cart(
p_user_id => user_rec.user_id,
p_address_id => user_rec.address_id,
p_payment_method => CASE ROUND(DBMS_RANDOM.VALUE(1,3))
WHEN 1 THEN '支付宝'
WHEN 2 THEN '微信支付'
ELSE '银行卡' END,
p_payment_id => 'PAY' || TO_CHAR(SYSDATE, 'YYYYMMDD') || LPAD(DBMS_RANDOM.VALUE(1,99999), 5, '0'),
p_order_id => v_order_id
);
IF DBMS_RANDOM.VALUE > 0.3 THEN
update_order_status(v_order_id, '已付款');
IF DBMS_RANDOM.VALUE > 0.5 THEN
update_order_status(v_order_id, '已发货');
IF DBMS_RANDOM.VALUE > 0.7 THEN
update_order_status(v_order_id, '已完成');
END IF;
END IF;
ELSE
update_order_status(v_order_id, '已取消');
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('用户 ' || user_rec.user_id || ' 创建订单失败: ' || SQLERRM);
END;
END LOOP;
COMMIT;
END;
/
DECLARE
v_order_id orders.order_id%TYPE;
v_product_id products.product_id%TYPE;
v_quantity INT;
BEGIN
SELECT o.order_id, od.product_id, od.quantity
INTO v_order_id, v_product_id, v_quantity
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
WHERE o.status = '已完成'
ORDER BY DBMS_RANDOM.VALUE
FETCH FIRST 1 ROW ONLY;
v_quantity := ROUND(v_quantity * 0.5);
UPDATE orders SET status = '退货处理中' WHERE order_id = v_order_id;
UPDATE products
SET stock_quantity = stock_quantity + v_quantity
WHERE product_id = v_product_id;
INSERT INTO order_details (order_id, product_id, quantity, unit_price)
SELECT v_order_id, v_product_id, -v_quantity, unit_price
FROM order_details
WHERE order_id = v_order_id AND product_id = v_product_id;
UPDATE orders
SET total_amount = total_amount - (v_quantity * (
SELECT unit_price
FROM order_details
WHERE order_id = v_order_id AND product_id = v_product_id
))
WHERE order_id = v_order_id;
UPDATE orders SET status = '部分退货' WHERE order_id = v_order_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
/
SELECT
u.user_id,
u.username,
COUNT(DISTINCT o.order_id) AS total_orders,
SUM(o.total_amount) AS total_spent,
ROUND(AVG(o.total_amount), 2) AS avg_order_value,
MAX(o.order_date) AS last_order_date,
(SELECT COUNT(*) FROM cart_items ci JOIN cart c ON ci.cart_id = c.cart_id WHERE c.user_id = u.user_id) AS cart_items,
(SELECT COUNT(*) FROM addresses a WHERE a.user_id = u.user_id) AS address_count,
CASE
WHEN SUM(o.total_amount) > 10000 THEN 'VIP'
WHEN SUM(o.total_amount) > 5000 THEN '忠诚'
WHEN SUM(o.total_amount) > 1000 THEN '活跃'
ELSE '普通'
END AS user_level
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.username
ORDER BY total_spent DESC;
WITH cart_data AS (
SELECT
p.product_id,
COUNT(DISTINCT ci.cart_id) AS in_carts
FROM cart_items ci
JOIN products p ON ci.product_id = p.product_id
GROUP BY p.product_id
),
order_data AS (
SELECT
od.product_id,
COUNT(DISTINCT o.order_id) AS ordered,
SUM(od.quantity) AS total_ordered
FROM order_details od
JOIN orders o ON od.order_id = o.order_id
GROUP BY od.product_id
)
SELECT
p.product_id,
p.product_name,
NVL(c.in_carts, 0) AS in_carts,
NVL(o.ordered, 0) AS ordered,
NVL(o.total_ordered, 0) AS total_ordered,
ROUND(NVL(o.ordered, 0) / NULLIF(NVL(c.in_carts, 0), 0) * 100 AS conversion_rate
FROM products p
LEFT JOIN cart_data c ON p.product_id = c.product_id
LEFT JOIN order_data o ON p.product_id = o.product_id
ORDER BY conversion_rate DESC NULLS LAST;
SELECT
TO_CHAR(order_date, 'YYYY-MM') AS sales_month,
COUNT(*) AS order_count,
SUM(total_amount) AS total_revenue,
LAG(COUNT(*), 1) OVER (ORDER BY TO_CHAR(order_date, 'YYYY-MM')) AS prev_month_orders,
ROUND((COUNT(*) - LAG(COUNT(*), 1) OVER (ORDER BY TO_CHAR(order_date, 'YYYY-MM'))) /
NULLIF(LAG(COUNT(*), 1) OVER (ORDER BY TO_CHAR(order_date, 'YYYY-MM')), 0) * 100, 2) AS order_growth,
LAG(SUM(total_amount), 12) OVER (ORDER BY TO_CHAR(order_date, 'YYYY-MM')) AS prev_year_revenue,
ROUND((SUM(total_amount) - LAG(SUM(total_amount), 12) OVER (ORDER BY TO_CHAR(order_date, 'YYYY-MM'))) /
NULLIF(LAG(SUM(total_amount), 12) OVER (ORDER BY TO_CHAR(order_date, 'YYYY-MM')), 0) * 100, 2) AS yoy_growth
FROM orders
WHERE order_date > ADD_MONTHS(SYSDATE, -24)
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY sales_month DESC;
CREATE TABLE archived_orders AS
SELECT * FROM orders
WHERE order_date < ADD_MONTHS(SYSDATE, -24);
CREATE TABLE archived_order_details AS
SELECT od.*
FROM order_details od
JOIN archived_orders ao ON od.order_id = ao.order_id;
DELETE FROM order_details
WHERE order_id IN (SELECT order_id FROM archived_orders);
DELETE FROM orders
WHERE order_date < ADD_MONTHS(SYSDATE, -24);
ALTER INDEX idx_orders_date REBUILD;
ALTER INDEX idx_products_name REBUILD;
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => USER,
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
degree => DBMS_STATS.AUTO_DEGREE
);
END;
/
CREATE DATABASE LINK backup_link
CONNECT TO backup_user IDENTIFIED BY "backup_pass"
USING 'backup_db';
INSERT INTO orders@backup_link
SELECT * FROM orders
WHERE order_date > SYSDATE - 7;
INSERT INTO order_details@backup_link
SELECT * FROM order_details
WHERE order_id IN (SELECT order_id FROM orders WHERE order_date > SYSDATE - 7);
|
