首页 文章

用于填充表的PL / SQL Block语句

提问于
浏览 次
1

我是PL / SQL的新手，这个让我很难过 . 在SQLPLUS中，我需要“使用PL / SQL块填充Description列，方法是从OLTP_VEHICLES表中选择组合，然后将组合插入到新的VEHICLES表中，最好通过循环中的游标执行 . ”这就是我所拥有的：

CREATE TABLE OLTP_Vehicles (
VIN VARCHAR(20) NOT NULL,
trade_ID VARCHAR(10) NOT NULL,
type VARCHAR(10),
make VARCHAR(15),
model VARCHAR(15),
where_from VARCHAR(30),
wholesale_cost NUMBER(8,2) NOT NULL,
PRIMARY KEY (VIN),
CONSTRAINT UC_Sale_Vehicles UNIQUE (VIN,trade_ID));

CREATE TABLE Vehicles (
vehicle_Code VARCHAR2(10),
description VARCHAR2(100),
PRIMARY KEY (vehicle_Code));

CREATE SEQUENCE veh_code_seq
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 20;

--PL/SQL Block
**SET SERVEROUTPUT ON
BEGIN
    FOR i in 1..10
    LOOP
        INSERT INTO Vehicles (vehicle_Code, description)
        VALUES veh_code_seq.NEXTVAL, (SELECT CONCAT(make, ', ',model) AS 
        description FROM OLTP_Vehicles); 
    END LOOP;
END;
/**

但是我收到了这个错误：

VALUES veh_code_seq.NEXTVAL，（SELECT CONCAT（make，'，'，model）AS description from OLTP_Vehicles）; *第5行的错误：ORA-06550：第4行，第50列：PL / SQL：ORA-00947：值不够ORA-06550：第4行，第1列：PL / SQL：忽略SQL语句

我假设这是导致问题的SELECT CONCAT语句 . 如何从另一个表中提取两列并将它们作为一列插入新表中？

MODIFIED Statement

SET SERVEROUTPUT ON
DECLARE
CURSOR c_VIN IS SELECT DISTINCT VIN FROM OLTP_Vehicles;
BEGIN
    FOR c1 in c_VIN
    LOOP
        INSERT INTO Vehicles (vehicle_Code, description)
        VALUES (veh_code_seq.NEXTVAL, (SELECT make || ', '||model AS 
        description
        FROM OLTP_Vehicles 
        WHERE VIN = c1.VIN));
    END LOOP;
END;
/

结果：

SQL> SELECT * FROM Vehicles ORDER BY vehicle_Code DESC;

VEHICLE_CODE    DESCRIPTION
--------------- ------------------------------
9               Chevrolet, Camaro
8               Chevrolet, Colorado
7               Dodge, Challenger
6               Ford, Fusion
50              Ford, Taurus
5               Chevrolet, Silverado
49              Chevrolet, Tahoe
48              Chevrolet, Colorado
47              Chevrolet, Colorado
46              Chevrolet, Silverado
45              Dodge, Ram

VEHICLE_CODE    DESCRIPTION
--------------- ------------------------------
44              Chevrolet, Impala
43              Chevrolet, Silverado
42              Chevrolet, Silverado
41              Chevrolet, Silverado
40              Chevrolet, Malibu
4               Ford, Focus
39              Dodge, Ram
38              Chevrolet, Camaro
37              Ford, F-350
36              Dodge, Ram
35              Ford, Fiesta

VEHICLE_CODE    DESCRIPTION
--------------- ------------------------------
34              Dodge, Dakota
33              Ford, F-150
32              Chevrolet, Silverado
31              Chevrolet, Suburban
30              Ford, Expedition
3               Dodge, Durango
29              Chevrolet, Colorado
28              Chevrolet, Tahoe
27              Dodge, Ram
26              Chevrolet, Silverado
25              Dodge, Charger

VEHICLE_CODE    DESCRIPTION
--------------- ------------------------------
24              Chevrolet, Silverado
23              Ford, F-150
22              Chevrolet, Tahoe
21              Chevrolet, Suburban
20              Ford, Expedition
2               Ford, Focus
19              Dodge, Charger
18              Chevrolet, Corvette
17              Chevrolet, Tahoe
16              Chevrolet, Suburban
15              Ford, F-250

VEHICLE_CODE    DESCRIPTION
--------------- ------------------------------
14              Ford, Edge
13              Chevrolet, Colorado
12              Chevrolet, Corvette
11              Dodge, Charger
10              Chevrolet, Camaro
1               Ford, Fusion

50 rows selected.

SQL>

现在我需要弄清楚如何获得不同的品牌和型号，因为它们没有与VIN连接，我需要弄清楚为什么vehicle_Code不是从1开始和DESC订单 .

oracle plsql concatenation sqlplus

3 回答

  • 1

    试试下面的区块代码，

    Declare
cursor c_vin is select distinct vin /*unique id*/ from OLTP_Vehicles;

begin

for c1 in c_vin loop

INSERT INTO Vehicles
(vehicle_Code, description)
SELECT veh_code_seq.NEXTVAL, make ||', '|| model
FROM OLTP_Vehicles where vin=c1.vin;


End loop;
commit;

End;
    回复于
  • 0

    concat函数只接受两个参数，而不是三个参数 . 只需用字符串连接运算符||替换即可例：

    BEGIN
    FOR i in 1..10
    LOOP
        INSERT INTO Vehicles (vehicle_Code, description)
        VALUES (veh_code_seq.NEXTVAL, (SELECT make||', '||model AS 
        description FROM OLTP_Vehicles) ); 
    END LOOP;
END;

    但是，这不是问题的结束 . 您的嵌入式SELECT语句没有意义 . 它正在读取每次循环迭代中OLTP_Vehicles的所有行 . 那是你的意图吗？

    回复于
  • 0

    我认为您需要做的就是使用以下查询将数据插入到Vehicle表中如果它是一次性工作 .

    insert into Vehicles 
select  rownum,  description from ( select  distinct initcap(trim(make)) ||', '||initcap(trim(model)) description from OLTP_Vehicles) ;
    回复于

相关问题