Oracle PL/SQL Tutorial/Character String Functions/CONTAINS — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 13:11, 26 мая 2010
CONTAINS(description, "10g near oracle") > 0
<source lang="sql">
SQL> CREATE TABLE book (
2 id NUMBER (10) PRIMARY KEY, 3 isbn VARCHAR2(10), 4 description VARCHAR2(500));
Table created. SQL> SQL> INSERT INTO book(id,isbn,description)VALUES (1,"3", "The essential PL/SQL."); 1 row created. SQL> SELECT isbn
2 FROM book 3 WHERE CONTAINS(description, "10g near oracle") > 0;
SELECT isbn
ERROR at line 1: ORA-20000: Oracle Text error: DRG-10599: column is not indexed
SQL> SQL> SQL> drop table book; Table dropped.</source>
CONTAINS(description, "website") > 3
<source lang="sql">
SQL> SQL> CREATE TABLE book (
2 id NUMBER (10) PRIMARY KEY, 3 isbn VARCHAR2(10), 4 description VARCHAR2(500));
Table created. SQL> SQL> INSERT INTO book(id,isbn,description)VALUES (1,"3", "The essential PL/SQL."); 1 row created. SQL> SQL> SELECT isbn
2 FROM book 3 WHERE CONTAINS(description, "website") > 3;
SELECT isbn
ERROR at line 1: ORA-20000: Oracle Text error: DRG-10599: column is not indexed
SQL> SQL> drop table book; Table dropped. SQL></source>
Proximity searches with contains
<source lang="sql">
SQL> SQL> CREATE TABLE book (
2 id NUMBER (10) PRIMARY KEY, 3 isbn VARCHAR2(10), 4 description VARCHAR2(500));
Table created. SQL> SQL> INSERT INTO book(id,isbn,description)VALUES (1,"3", "The essential PL/SQL."); 1 row created. SQL> SQL> DECLARE
2 v_isbn book.ISBN%TYPE; 3 v_score NUMBER(10); 4 BEGIN 5 6 SELECT score(1), isbn 7 INTO v_score, v_isbn 8 FROM book 9 WHERE CONTAINS (description, "10g near Oracle", 1) > 0; 10 11 DBMS_OUTPUT.PUT_LINE("Score: "||v_score||" and ISBN: "||v_isbn); 12 13 EXCEPTION 14 WHEN OTHERS 15 THEN 16 DBMS_OUTPUT.PUT_LINE(sqlerrm); 17 END; 18 /
ORA-20000: Oracle Text error: DRG-10599: column is not indexed PL/SQL procedure successfully completed. SQL> SQL> drop table book; Table dropped. SQL></source>