以字串格式顯示電影描述

我有一個任務需要用格式撰寫觸發陳述句

這是我的資料庫架構：

我寫我的代碼

CREATE OR REPLACE TRIGGER "BI_FILM_DESP"
BEFORE INSERT ON "FILM"
FOR EACH ROW
DECLARE
RatingFilm VARCHAR2(8);
Seq NUMBER(3);
OriginalL VARCHAR2(20);
Language VARCHAR2(20);
BEGIN
SELECT RATING INTO RatingFilm FROM FILM F;
SELECT COUNT(RATING) INTO Seq FROM FILM F GROUP BY F.RATING;
SELECT LANGUAGE.NAME INTO OriginalL FROM LANGUAGE L WHERE (L.LANGUAGE_ID =: FILM.LANGUAGE_ID);
SELECT LANGUAGE.NAME INTO Language FRoM LANGUAGE L WHERE (L.LANGUAGE_ID =: FILM.LANGUAGE_ID);
SELECT CONCAT(RatingFilm, "-", Seq, ": Originally in", OriginalL, ". Re-released in ", Language, ".");
END;
/

但是它顯示錯誤

我認為很難閱讀這些錯誤，我需要一些幫助來糾正它。提前致謝。

編輯：添加創建表的代碼

CREATE TABLE film (
  film_id NUMBER(5) NOT NULL,
  title varchar2(255),
  description varchar2(255),
  release_year NUMBER(4) DEFAULT NULL,
  language_id NUMBER(3) NOT NULL,
  original_language_id NUMBER(3) DEFAULT NULL,
  rental_duration NUMBER(3) DEFAULT 3 NOT NULL,
  rental_rate NUMBER(4,2) DEFAULT '4.99' NOT NULL,
  length NUMBER(5) DEFAULT NULL,
  replacement_cost NUMBER(5,2) DEFAULT '19.99' NOT NULL,
  rating varchar2(8) DEFAULT 'G' NOT NULL,
  special_features varchar2(255) DEFAULT NULL
);

CREATE TABLE language (
  language_id NUMBER(3) NOT NULL,
  name varchar2(20)
);

uj5u.com熱心網友回復：

我希望你閱讀 astentx 的評論。

這是你可以/應該怎么做。

涉及的表格（僅包含必要的列）：

SQL> CREATE TABLE language
  2  (
  3     language_id   NUMBER PRIMARY KEY,
  4     name          VARCHAR2 (20)
  5  );

Table created.

SQL> INSERT INTO language (language_id, name)
  2     SELECT 1, 'English' FROM DUAL
  3     UNION ALL
  4     SELECT 2, 'Croatian' FROM DUAL;

2 rows created.

SQL> CREATE TABLE film
  2  (
  3     film_id                NUMBER PRIMARY KEY,
  4     title                  VARCHAR2 (20),
  5     description            VARCHAR2 (100),
  6     language_id            NUMBER REFERENCES language,
  7     original_language_id   NUMBER REFERENCES language,
  8     rating                 NUMBER
  9  );

Table created.

觸發器：不要從該行級觸發器觸發的表中進行選擇，因為您會遇到變異表錯誤。對你有好處，你不必這樣做 - 改用:new偽記錄值：

SQL> CREATE OR REPLACE TRIGGER bi_film_desp
  2     BEFORE INSERT
  3     ON film
  4     FOR EACH ROW
  5  DECLARE
  6     l_language           language.name%TYPE;
  7     l_original_language  language.name%TYPE;
  8  BEGIN
  9     SELECT l.name
 10       INTO l_language
 11       FROM language l
 12      WHERE l.language_id = :new.language_id;
 13
 14     SELECT l.name
 15       INTO l_original_language
 16       FROM language l
 17      WHERE l.language_id = :new.original_language_id;
 18
 19     :new.description :=
 20           'Rating: '
 21        || :new.rating
 22        || ', original language: '
 23        || l_original_language
 24        || ', language: '
 25        || l_language;
 26  END;
 27  /

Trigger created.

SQL>

讓我們測驗一下：

SQL> INSERT INTO film (film_id,
  2                    title,
  3                    language_id,
  4                    original_language_id,
  5                    rating)
  6       VALUES (1,
  7               'Izbavitelj',
  8               1,
  9               2,
 10               7);

1 row created.

結果：

SQL> select title, description, rating from film;

TITLE      DESCRIPTION                                                      RATING
---------- ------------------------------------------------------------ ----------
Izbavitelj Rating: 7, original language: Croatian, language: English             7

SQL>

對我來說看起來不錯。

標籤：sql 甲骨文 触发器 字符串连接

上一篇：在子查詢中使用行內函式（在WHERE子句中）

下一篇：不是有效的月份，而是數字資料型別Java-Oracle