oracle sql学习报错记录

zhengshuangyue

已于 2024-01-06 15:39:00 修改

阅读量1k

点赞数 24

文章标签： oracle sql 学习

于 2024-01-03 16:11:13 首次发布

本文链接：https://blog.csdn.net/zhengshuangyue/article/details/134866919

版权

报错一

代码：

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;

报错十四

代码：

create table customers
(
    customer_id number(11) generated by default as identity,
    first_name  varchar2(50)         not null,
    points      number(11) default 0 not null,
    email       varchar2(255)        not null unique,
    constraint pk_customers primary key (customer_id)
);

报错信息：

我已经给了用户创建表的权限，却依然报权限不足错误。

[2024-01-06 15:30:32] [42000][1031] ORA-01031: 权限不足
[2024-01-06 15:30:32] Position: 0

原因：

generated by default as identity：该代码的作用是，插入时自动递增。在Oracle中是通过序列和触发器实现的，因此不仅要给用户创建表的权限还要给创建序列和触发器的权限。

解决方法：

grant create trigger to username;
grant create sequence to username;

未完待续…

zhengshuangyue

关注

24
点赞
踩
21

收藏

觉得还不错? 一键收藏
0
评论
oracle sql学习报错记录

oracle sql学习报错记录
复制链接

扫一扫

oracle sql学习报错记录

报错一

代码：

报错信息：

原因：

解决方法：

报错二

代码：

报错信息：

原因：

解决方法：

报错三

代码：

报错信息：

原因：

解决方法：

报错四

代码：

报错信息：

原因：

解决方法：

报错五

代码：

报错信息：

原因：

解决方法：

报错六

代码：

报错信息：

原因：

解决方法：

报错七

代码：

报错信息：

原因：

解决方法：

报错八

代码：

报错信息：

原因：

解决方法：

报错九

代码：

报错信息：

原因：

解决方法：

报错十

代码：

报错信息：

原因：

解决方法：

报错十一

代码：

报错信息：

原因：

解决方法：

报错十二

代码：

报错信息：

原因：

解决方法：

报错十三

代码：

报错信息：

原因：

解决方法：

报错十四

代码：

报错信息：

原因：

解决方法：

“相关推荐”对你有帮助么？