oracle sql学习报错记录

报错一

代码：

INSERT INTO customers
VALUES (1, 'Babara', 'MacCaffrey', TO_DATE('1986-03-28', 'YYYY-MM-DD'), '781-932-9754', '0 Sage Terrace', 'Waltham', 'MA', 2273);

报错信息：

[42000][1950] ORA-01950: 对表空间 'USERS' 无权限 Position: 12

原因：

对于一个新建的用户，如果没有分配给unlimited table space系统权限的用户，必须先给他们指定限额，才能在表空间中创建对象。

解决方法：

SQL>ALTER USER 用户名 QUOTA UNLIMITED ON "USERS";

报错二

代码：

创建表时，其中一条语句编译报错

CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ON update CASCADE

报错信息：

DELETE expected, got 'update'

原因：

Oracle本身只支持外键的级联删除，并不支持外键的级联更新。

解决方法：

我们可以通过触发器来实现外键的级联更新。如下：

create or replace trigger trg_orders_customer_id
    after update
    on customers
    for each row
begin
    if :NEW.customer_id <> :old.customer_id then
        update orders set customer_id=:new.customer_id where customer_id = :old.customer_id;
    end if;
end;

报错三

代码：

创建表时，其中一条语句编译报错

CONSTRAINT fk_client_id FOREIGN KEY (client_id) REFERENCES clients (client_id) ON DELETE RESTRICT

报错信息：

CASCADE or SET expected, got 'RESTRICT'

原因：

Oracle不支持使用这种方法来指定外键约束的删除行为。

解决方法：

我们可以通过触发器来实现。如下：

create or replace trigger trg_clients_invoices_id
    before delete
    on clients
    for each row
declare
    --变量声明，用于在触发器中存储查询结果的计数值
    client_id_count NUMBER;
begin
    select count(*)
    INTO client_id_count
    FROM invoices
    WHERE client_id = :old.client_id;

    if client_id_count > 0 then
        raise_application_error(-20001, 'Cannot delete order. Related order items exist.');
    end if;
end;

报错四

代码：

insert into CUSTOMERS (FIRST_NAME, LAST_NAME,BIRTH_DATE, ADDRESS, CITY, STATE)
values (
        'John',
        'Smith',
        to_date('1990-01-01', 'YYYY-MM-DD'),
        'address',
        'city',
        'CA');

报错信息：

[2023-12-13 23:05:00] [23000][1] ORA-00001: 违反唯一约束条件 (ORCL.PK_CUSTOMERS)
[2023-12-13 23:05:00] Position: 0

原因：

主键自增触发器所使用的序列从1开始。如下：

create sequence SEQ_CUSTOMERS
    minvalue 1
    nomaxvalue
    start with 1
    nocycle
    cache 10;

创建表时，插入的初始数据没有通过触发器生成主键，因此序列还是从1开始。
插入的初始数据时使用代码如下：

INSERT INTO customers
VALUES (10, 'Levy', 'Mynett', TO_DATE('1969-10-13', 'YYYY-MM-DD'), '404-246-3370', '68 Lawn Avenue', 'Atlanta', 'GA', 796);

解决方法：

将序列删除，改成从11开始。如下：

drop sequence SEQ_CUSTOMERS;

create sequence SEQ_CUSTOMERS
    minvalue 1
    nomaxvalue
    start with 11
    nocycle
    cache 10;

报错五

代码：

insert all
      into SHIPPERS (NAME)
      values ('shipper1')
      into SHIPPERS (NAME)
      values ('shipper2')
      into SHIPPERS (NAME)
      values ('shipper2')
      select 1
      from DUAL

报错信息：

[2023-12-14 21:51:07] [23000][1] ORA-00001: 违反唯一约束条件 (ORCL.PK_SHIPPERS)
[2023-12-14 21:51:07] Position: 0

原因：

该代码只会触发一次主键自增触发器，因此当多条数据插入时，会使用同一个被生成的主键。

解决方法：

一、通过联合（union）的方式批量插入，如下：

INSERT INTO shippers (name)
SELECT 'SHIPPER_1'
FROM dual
UNION
SELECT 'SHIPPER_2'
FROM dual
UNION
SELECT 'SHIPPER_3'
FROM dual;

二、插入时，声明主键，如下：

insert all
into SHIPPERS (shipper_id, NAME)
values (SHIPPERS_SEQ.nextval, 'shipper1')
into SHIPPERS (shipper_id,NAME)
values (SHIPPERS_SEQ.nextval+1,'shipper2')
into SHIPPERS (shipper_id,NAME)
values (SHIPPERS_SEQ.nextval+2,'shipper3')
select 1
from DUAL;

报错六

代码：

多表插入，插入ORDERS表后，获取自动生成的ORDER_ID值并插入ORDER_ITEMS表中。

begin
    insert into ORDERS (ORDER_ID, customer_id, order_date, status)
    values (SEQ_ORDERS.nextval, 1, to_date('1990-01-02', 'YYYY-MM-DD'), 1);

    insert into ORDER_ITEMS
    SELECT SEQ_ORDERS.currval, 1, 1, 2.95
    from DUAL
    union
    SELECT SEQ_ORDERS.currval, 2, 1, 3.95
    from DUAL;
end;

报错信息：

[2023-12-15 11:03:55] [65000][6550]
[2023-12-15 11:03:55] 	ORA-06550: 第 6 行, 第 23 列:
[2023-12-15 11:03:55] 	PL/SQL: ORA-02287: 此处不允许序号
[2023-12-15 11:03:55] 	ORA-06550: 第 5 行, 第 5 列:
[2023-12-15 11:03:55] 	PL/SQL: SQL Statement ignored
[2023-12-15 11:03:55] Position: 205

原因：

currval只能在SELECT语句中使用，并且必须在序列的NEXTVAL之后才能使用。在SELECT子句中直接使用SEQ_ORDERS.currval，这是不允许的。

解决方法：

将SEQ_ORDERS.currval的值存储在一个变量中，然后在SELECT子句中使用该变量。

DECLARE
    new_order_id NUMBER;
begin
    insert into ORDERS (ORDER_ID, customer_id, order_date, status)
    values (SEQ_ORDERS.nextval, 1, to_date('1990-01-02', 'YYYY-MM-DD'), 1);

    SELECT SEQ_ORDERS.currval INTO new_order_id FROM DUAL;

    insert into ORDER_ITEMS
    SELECT new_order_id, 1, 1, 2.95
    from DUAL
    union
    SELECT new_order_id, 2, 1, 3.95
    from DUAL;
end;

报错七

代码：

select c.CUSTOMER_ID, c.FIRST_NAME, c.LAST_NAME
      from CUSTOMERS c
      join ORDERS o using (customer_id)
      join ORDER_ITEMS oi using (order_id)
      where STATE = 'VA'

报错信息：

[2023-12-15 21:22:39] [99999][25154] ORA-25154: USING 子句的列部分不能有限定词
[2023-12-15 21:22:39] Position: 7

原因：

在Oracle中，USING子句用于指定两个表之间的列进行自动连接，但它不允许在列名中使用表限定词。

解决方法：

一、去掉表限定词，如下：

select CUSTOMER_ID, FIRST_NAME, LAST_NAME
from CUSTOMERS c
join ORDERS o using (customer_id)
join ORDER_ITEMS oi using (order_id)
where STATE = 'VA';

二、改为使用ON子句来指定连接条件，如下：

SELECT c.CUSTOMER_ID, c.FIRST_NAME, c.LAST_NAME
FROM CUSTOMERS c
JOIN ORDERS o ON c.customer_id= o.customer_id
JOIN ORDER_ITEMS oi ON o.order_id = oi.order_id
WHERE STATE = 'VA';

报错八

代码：

select INVOICE_ID,
       INVOICE_TOTAL,
       (select avg(INVOICE_TOTAL) from INVOICES) as invoice_average,
       INVOICE_TOTAL - (select invoice_average)
from INVOICES

报错信息：

[2023-12-20 11:24:22] [42000][923] ORA-00923: 未找到要求的 FROM 关键字
[2023-12-20 11:24:22] Position: 159

原因：

Oracle中，在子查询中引用外部查询结果时，不能直接使用子查询的别名。

解决方法：

一、子查询嵌套到外部查询

SELECT INVOICE_ID,
       INVOICE_TOTAL,
       (SELECT AVG(INVOICE_TOTAL) FROM INVOICES) AS invoice_average,
       INVOICE_TOTAL - (SELECT AVG(INVOICE_TOTAL) FROM INVOICES) AS difference
FROM INVOICES;

如果想避免重复执行同一个子查询可以通过内联视图。
二、内联视图

select t.INVOICE_ID,
       t.INVOICE_TOTAL,
       invoice_average,
       t.INVOICE_TOTAL - invoice_average as difference
from INVOICES t, (select avg(INVOICE_TOTAL) as invoice_average from INVOICES);

注：内联视图只能用无相关子查询。

报错九

代码：

select concat(FIRST_NAME, ' ', FIRST_NAME) as full_name
from CUSTOMERS

报错信息：

[2023-12-21 10:15:10] [42000][909] ORA-00909: 参数个数无效
[2023-12-21 10:15:10] Position: 7

原因：

concat()函数默认只接受两个参数。

解决方法：

嵌套使用concat()函数

select concat(concat(FIRST_NAME, ' '), LAST_NAME) as full_name
from CUSTOMERS;

报错十

代码：

create view sales_by_client as
select c.CLIENT_ID,
       c.NAME,
       sum(INVOICE_TOTAL) as total_sales
from CLIENTS c
         join INVOICES i on c.CLIENT_ID = i.CLIENT_ID
group by c.CLIENT_ID, c.NAME;      

报错信息：

[2023-12-24 15:07:35] [42000][1031] ORA-01031: 权限不足
[2023-12-24 15:07:35] Position: 12

原因：

没有创建视图权限。

解决方法：

使用管理员账户登录到 Oracle 数据库，然后执行以下语句：

SQL> GRANT CREATE VIEW TO your_user;

报错十一

代码：

create or replace procedure get_clients_by_state_default(p_state in varchar2)
    is
    client_record CLIENTS%rowtype;
begin
    if p_state is null then
        p_state := 'CA';
    end if;
    select * into client_record from CLIENTS c where c.STATE = p_state;
    DBMS_OUTPUT.PUT_LINE(client_record.CLIENT_ID || ' ' || client_record.NAME);
end;

报错信息：

[2023-12-26 13:30:20] [99999][17110] Warning: 执行完毕, 但带有警告
[2023-12-26 13:30:20] completed in 1 s 882 ms
[2023-12-26 13:30:20] 6:9:PLS-00363: 表达式 'P_STATE' 不能用作赋值目标
[2023-12-26 13:30:20] 6:9:PL/SQL: Statement ignored

原因：

在 Oracle 中，存储过程的参数是只读的，不能直接对其进行赋值操作。

解决方法：

可以使用一个新的变量来接收默认值，并在存储过程中使用该变量进行查询。

create or replace procedure get_clients_by_state_default(p_state in varchar2)
    is
    client_record CLIENTS%rowtype;
    v_state       VARCHAR2(2); -- 新增变量用于接收默认值
begin
    if p_state is null then
        v_state := 'CA';
    else
        v_state := p_state;
    end if;
    select * into client_record from CLIENTS c where c.STATE = v_state;
    DBMS_OUTPUT.PUT_LINE(client_record.CLIENT_ID || ' ' || client_record.NAME);
end;

报错十二

代码：

设置当前会话的事务隔离级别

alter session set isolation_level = read committed;

报错信息：

[2023-12-30 14:58:40] [72000][1453] ORA-01453: SET TRANSACTION 必须是事务处理的第一个语句
[2023-12-30 14:58:40] Position: 0

原因：

SET TRANSACTION 语句必须在事务开始之前执行。

解决方法：

将之前的事务提交完，就可以执行该语句了。

commit;
alter session set isolation_level = read committed;

报错十三

代码：

查看当前事务隔离级别：

--开启一个事务
select *
from DUAL for update;
--查看当前事务
SELECT s.sid,
       s.serial#,
       bitand(t.flag, POWER(2, 28)),
       case bitand(t.flag, POWER(2, 28))
           WHEN 0 THEN 'READ COMMITTED'
           ELSE 'SERIALIZABLE'
           END AS isolation_level
FROM v$transaction t
         JOIN v$session s ON t.addr = s.taddr AND s.sid = sys_context('USERENV', 'SID');

报错信息：

[2023-12-30 14:30:05] [42000][942] ORA-00942: 表或视图不存在
[2023-12-30 14:30:05] Position: 212

原因：

没有查询v$transaction和v$session的权限。

解决方法：

GRANT SELECT ON V_$TRANSACTION TO your_user;
GRANT SELECT ON V_$SESSION TO your_user;