Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
я хочу добавить к ней распознавание и синтезирование речи (но это занятие долгое, а времени мало — сессия, студенты и им сочувствующие поймут)
select * from v$version
/
select value from v$nls_parameters
where parameter = 'NLS_CHARACTERSET'
/
DROP TABLE news
/
EXEC CTX_DDL.DROP_PREFERENCE ('my_wordlist')
EXEC CTX_DDL.DROP_PREFERENCE ('global_lexer')
EXEC CTX_DDL.DROP_PREFERENCE ('english_LEXER')
EXEC CTX_DDL.DROP_PREFERENCE ('german_LEXER')
EXEC CTX_DDL.DROP_PREFERENCE ('russian_LEXER')
CREATE TABLE news
(pkey NUMBER,
lang VARCHAR2 (2),
short_content CLOB)
/
INSERT ALL
INTO news (pkey, lang, short_content)
VALUES (1, 'en', 'I drive a bike.')
INTO news (pkey, lang, short_content)
VALUES (2, 'en', 'I drove a bike.')
INTO news (pkey, lang, short_content)
VALUES (3, 'en', 'I have driven a bike.')
INTO news (pkey, lang, short_content)
VALUES (4, 'en', 'I always drive a car')
INTO news (pkey, lang, short_content)
VALUES (5, 'en', 'This is nothing')
INTO news (pkey, lang, short_content)
VALUES (6, 'de', 'Ich fahre ein Fahrrad.')
INTO news (pkey, lang, short_content)
VALUES (7, 'de', 'Ich fuhr ein Fahrrad.')
INTO news (pkey, lang, short_content)
VALUES (8, 'de', 'Ich habe ein Fahrrad gefahren.')
INTO news (pkey, lang, short_content)
VALUES (9, 'de', 'Ich habe immer ein Auto fahren.')
INTO news (pkey, lang, short_content)
VALUES (10, 'de', 'Es ist nichts.')
INTO news (pkey, lang, short_content) VALUES (11, 'ru', 'читал')
INTO news (pkey, lang, short_content) VALUES (12, 'ru', 'читаю')
INTO news (pkey, lang, short_content) VALUES (13, 'ru', 'читали')
INTO news (pkey, lang, short_content) VALUES (14, 'ru', 'читать')
INTO news (pkey, lang, short_content) VALUES (15, 'ru', 'читаем')
INTO news (pkey, lang, short_content) VALUES (16, 'ru', 'читаешь')
SELECT * FROM DUAL
/
BEGIN
-- word list:
ctx_ddl.create_preference ('my_wordlist', 'basic_wordlist');
ctx_ddl.set_attribute ('my_wordlist', 'stemmer', 'auto');
-- english lexer:
ctx_ddl.create_preference ('english_lexer', 'basic_lexer');
-- russian lexer:
-- ctx_ddl.create_preference ('russian_lexer', 'basic_lexer');
--ctx_ddl.set_attribute('russian_lexer', 'INDEX_STEMS','YES');
-- german lexer:
ctx_ddl.create_preference ('german_lexer', 'basic_lexer');
ctx_ddl.set_attribute ('german_lexer', 'composite', 'german');
ctx_ddl.set_attribute ('german_lexer', 'alternate_spelling', 'german');
ctx_ddl.set_attribute ('german_lexer', 'mixed_case', 'no');
ctx_ddl.set_attribute ('german_lexer', 'base_letter', 'yes');
-- multi_lexer:
ctx_ddl.create_preference ('global_lexer', 'multi_lexer');
ctx_ddl.add_sub_lexer ('global_lexer', 'default', 'english_lexer');
ctx_ddl.add_sub_lexer ('global_lexer', 'german', 'german_lexer');
--ctx_ddl.add_sub_lexer ('global_lexer', 'russian', 'russian_lexer');
END;
/
--drop index search_news;
create index search_news
on news (short_content)
indextype is ctxsys.context
parameters
('lexer global_lexer
language column lang
wordlist my_wordlist')
/
EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'NEWS')
COLUMN short_content FORMAT A30
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN'
/
set timing on
select * from news
where contains (short_content, '$drive') > 0
/
select * from news
where contains (short_content, '$drove') > 0
/
ALTER SESSION SET NLS_LANGUAGE = 'GERMAN'
/
select * from news
where contains (short_content, '$fahr') > 0
/
select * from news
where contains (short_content, '$fuhr') > 0
/
ALTER SESSION SET NLS_LANGUAGE = 'RUSSIAN'
/
select * from news
where contains (short_content, '$читать') > 0
/
select * from news
where contains (short_content, '$читали') > 0
/
create table mybooks (title varchar2(20), author varchar2(20));
insert into mybooks values ('Consider the Lillies', 'Ian Crichton Smith');
insert into mybooks values ('Sphere', 'Michael Crichton');
insert into mybooks values ('Stupid White Men', 'Michael Moore');
insert into mybooks values ('Lonely Day', 'Michaela Criton');
insert into mybooks values ('How to Teach Poetry', 'Michaela Morgan');
create index auth_idx on mybooks (author) indextype is ctxsys.context;
SELECT score(1), title, author FROM mybooks WHERE CONTAINS (author, '
<query>
<textquery>
<progression>
<seq>michael crichton</seq>
<seq>?michael ?crichton</seq>
<seq>michael OR crichton</seq>
<seq>?michael OR ?crichton</seq>
</progression>
</textquery>
</query>', 1) > 0 ORDER BY score(1) DESC;
The output of this query is:
SCORE(1) TITLE AUTHOR
---------- -------------------- --------------------
76 Sphere Michael Crichton
51 Lonely Day Michaela Criton
26 Stupid White Men Michael Moore
26 Consider the Lillies Ian Crichton Smith
1 How to Teach Poetry Michaela Morgan
Создание простого интерактивного помощника