1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182
| CREATE DATABASE '..\Data\Base.fdb' page_size 4096 DEFAULT CHARACTER SET WIN1252;
-- ***********************
-- * *
-- * Jeu d'Essai *
-- * *
-- ***********************
-- =============================
-- Création de la table 'TABONE'
-- =============================
create table tabone (
id integer generated by default as identity not null,
rang integer not null,
chaine varchar(20) not null,
lib varchar(20) not null,
primary key (id,rang)
);
-- =======================
-- Insertion dans 'TABONE'
-- =======================
insert into tabone (rang,chaine,lib) values (33,'one', 'un');
insert into tabone (rang,chaine,lib) values (25,'two', 'deux');
insert into tabone (rang,chaine,lib) values (12,'three','trois');
insert into tabone (rang,chaine,lib) values ( 7,'four', 'quatre');
insert into tabone (rang,chaine,lib) values ( 2,'five', 'trente-trois');
-- ==================
-- Vidage de 'TABONE'
-- ==================
select * from tabone;
ID RANG CHAINE LIB
============ ============ ==================== ====================
1 33 one un
2 25 two deux
3 12 three trois
4 7 four quatre
5 2 five trente-trois
commit;
-- =============================
-- Création de la table 'TABTWO'
-- =============================
create table tabtwo (
clef integer generated by default as identity not null primary key,
mess varchar(20) not null
);
-- =======================
-- Insertion dans 'TABTWO'
-- =======================
insert into tabtwo (mess) values ('trois-cent');
insert into tabtwo (mess) values ('quatre-cent');
insert into tabtwo (mess) values ('cinq-cent');
-- ==================
-- Vidage de 'TABTWO'
-- ==================
select * from tabtwo;
CLEF MESS
============ ====================
1 trois-cent
2 quatre-cent
3 cinq-cent
commit;
-- ***********************************************
-- * *
-- * Recherche des chaines de caractères *
-- * *
-- ***********************************************
-- =================
-- Procédure stockée
-- =================
set term #;
create procedure test (oldstring varchar(20),
newstring varchar(20))
returns (tab varchar(20),
clef varchar(20),
tri varchar(20),
col varchar(20),
id varchar(20))
as
declare stmt varchar(1024);
begin
for select f.rdb$relation_name,
cast(list(trim(s.rdb$field_name), ',') as varchar(20)),
cast(list(trim(s.rdb$field_name), '|| ''-'' ||') as varchar(20))
from rdb$relation_fields as f
inner join rdb$relation_constraints as r
on r.rdb$relation_name = f.rdb$relation_name
inner join rdb$index_segments as s
on s.rdb$index_name = r.rdb$index_name
where f.rdb$system_flag = 0
and f.rdb$field_id = 0
and r.rdb$constraint_type = 'PRIMARY KEY'
group by rdb$relation_name
into :tab, :clef, :tri
do
begin
for select r.rdb$field_name
from rdb$relation_fields as r
inner join rdb$fields as f
on f.rdb$field_name = r.rdb$field_source
where r.rdb$relation_name = :tab
and r.rdb$system_flag = 0
and f.rdb$field_type in (14,37)
into :col
do
begin
id = null;
stmt = 'select cast(list(' || tri || ','','') as varchar(255)) from ' || trim(tab) || ' where ' || trim(col) || ' like ''%' || trim(oldstring) || '%'';';
execute statement stmt into :id;
if (id is not null) then
begin
stmt = 'update ' || trim(tab) || ' set ' || trim(col) || ' = replace(' || trim(col) || ',''' || trim(oldstring) || ''',''' || trim(newstring) || ''');';
execute statement stmt;
suspend;
end
end
end
end#
set term ;#
commit;
-- =========
-- Exécution
-- =========
select tab, clef, col, id from test('trois','huit');
TAB CLEF COL ID
==================== ==================== ==================== ====================
TABONE ID,RANG LIB 3-12,5-2
TABTWO CLEF MESS 1
-- ========
-- Résultat
-- ========
select * from tabone;
ID RANG CHAINE LIB
============ ============ ==================== ====================
1 33 one un
2 25 two deux
3 12 three huit
4 7 four quatre
5 2 five trente-huit
select * from tabtwo;
CLEF MESS
============ ====================
1 huit-cent
2 quatre-cent
3 cinq-cent
exit;
Appuyez sur une touche pour continuer... |
Partager