· 6 years ago · Jun 10, 2019, 07:04 PM
1-- MySQL dump 10.13 Distrib 8.0.16, for Win64 (x86_64)
2--
3-- Host: 127.0.0.1 Database: mydb
4-- ------------------------------------------------------
5-- Server version 8.0.16
6
7/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10 SET NAMES utf8 ;
11/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
12/*!40103 SET TIME_ZONE='+00:00' */;
13/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
17
18--
19-- Table structure for table `doc_types`
20--
21
22DROP TABLE IF EXISTS `doc_types`;
23/*!40101 SET @saved_cs_client = @@character_set_client */;
24 SET character_set_client = utf8mb4 ;
25CREATE TABLE `doc_types` (
26 `type_code` int(11) NOT NULL,
27 `type_d` varchar(45) NOT NULL,
28 PRIMARY KEY (`type_code`),
29 UNIQUE KEY `type_code_UNIQUE` (`type_code`)
30) ENGINE=InnoDB DEFAULT CHARSET=utf8;
31/*!40101 SET character_set_client = @saved_cs_client */;
32
33--
34-- Dumping data for table `doc_types`
35--
36
37LOCK TABLES `doc_types` WRITE;
38/*!40000 ALTER TABLE `doc_types` DISABLE KEYS */;
39/*!40000 ALTER TABLE `doc_types` ENABLE KEYS */;
40UNLOCK TABLES;
41
42--
43-- Table structure for table `document`
44--
45
46DROP TABLE IF EXISTS `document`;
47/*!40101 SET @saved_cs_client = @@character_set_client */;
48 SET character_set_client = utf8mb4 ;
49CREATE TABLE `document` (
50 `archive_num` int(11) NOT NULL,
51 `nazvanie` varchar(45) NOT NULL,
52 `fio` varchar(45) NOT NULL,
53 `stranic_col` int(11) NOT NULL,
54 `my_date` date NOT NULL,
55 `doc_types_type_code` int(11) NOT NULL,
56 `ogrs_org_code` int(11) NOT NULL,
57 PRIMARY KEY (`archive_num`),
58 UNIQUE KEY `archive_num_UNIQUE` (`archive_num`),
59 KEY `fk_document_doc_types_idx` (`doc_types_type_code`),
60 KEY `fk_document_ogrs1_idx` (`ogrs_org_code`),
61 CONSTRAINT `fk_document_doc_types` FOREIGN KEY (`doc_types_type_code`) REFERENCES `doc_types` (`type_code`),
62 CONSTRAINT `fk_document_ogrs1` FOREIGN KEY (`ogrs_org_code`) REFERENCES `ogrs` (`org_code`)
63) ENGINE=InnoDB DEFAULT CHARSET=utf8;
64/*!40101 SET character_set_client = @saved_cs_client */;
65
66--
67-- Dumping data for table `document`
68--
69
70LOCK TABLES `document` WRITE;
71/*!40000 ALTER TABLE `document` DISABLE KEYS */;
72/*!40000 ALTER TABLE `document` ENABLE KEYS */;
73UNLOCK TABLES;
74
75--
76-- Table structure for table `jurnal`
77--
78
79DROP TABLE IF EXISTS `jurnal`;
80/*!40101 SET @saved_cs_client = @@character_set_client */;
81 SET character_set_client = utf8mb4 ;
82CREATE TABLE `jurnal` (
83 `nomer_zap` int(11) NOT NULL,
84 `sotrudnik` varchar(45) NOT NULL,
85 `nom_example` int(11) NOT NULL,
86 `date_vidachi` date NOT NULL,
87 `date_vozvrata` date NOT NULL,
88 `otdeli_otd_code` int(11) NOT NULL,
89 `document_archive_num` int(11) NOT NULL,
90 `kolichestvo_example` varchar(45) NOT NULL,
91 PRIMARY KEY (`nomer_zap`),
92 UNIQUE KEY `nomer_zap_UNIQUE` (`nomer_zap`),
93 KEY `fk_jurnal_otdeli1_idx` (`otdeli_otd_code`),
94 KEY `fk_jurnal_document1_idx` (`document_archive_num`),
95 CONSTRAINT `fk_jurnal_document1` FOREIGN KEY (`document_archive_num`) REFERENCES `document` (`archive_num`),
96 CONSTRAINT `fk_jurnal_otdeli1` FOREIGN KEY (`otdeli_otd_code`) REFERENCES `otdeli` (`otd_code`)
97) ENGINE=InnoDB DEFAULT CHARSET=utf8;
98/*!40101 SET character_set_client = @saved_cs_client */;
99
100--
101-- Dumping data for table `jurnal`
102--
103
104LOCK TABLES `jurnal` WRITE;
105/*!40000 ALTER TABLE `jurnal` DISABLE KEYS */;
106/*!40000 ALTER TABLE `jurnal` ENABLE KEYS */;
107UNLOCK TABLES;
108
109--
110-- Table structure for table `ogrs`
111--
112
113DROP TABLE IF EXISTS `ogrs`;
114/*!40101 SET @saved_cs_client = @@character_set_client */;
115 SET character_set_client = utf8mb4 ;
116CREATE TABLE `ogrs` (
117 `org_code` int(11) NOT NULL,
118 `ogranizaciya` varchar(45) NOT NULL,
119 `adress` varchar(45) NOT NULL,
120 `indexx` int(11) NOT NULL,
121 `city` varchar(45) NOT NULL,
122 `phone_num` int(11) NOT NULL,
123 `faks` varchar(45) NOT NULL,
124 `mail` varchar(45) NOT NULL,
125 PRIMARY KEY (`org_code`),
126 UNIQUE KEY `org_code_UNIQUE` (`org_code`)
127) ENGINE=InnoDB DEFAULT CHARSET=utf8;
128/*!40101 SET character_set_client = @saved_cs_client */;
129
130--
131-- Dumping data for table `ogrs`
132--
133
134LOCK TABLES `ogrs` WRITE;
135/*!40000 ALTER TABLE `ogrs` DISABLE KEYS */;
136/*!40000 ALTER TABLE `ogrs` ENABLE KEYS */;
137UNLOCK TABLES;
138
139--
140-- Table structure for table `otdeli`
141--
142
143DROP TABLE IF EXISTS `otdeli`;
144/*!40101 SET @saved_cs_client = @@character_set_client */;
145 SET character_set_client = utf8mb4 ;
146CREATE TABLE `otdeli` (
147 `otd_code` int(11) NOT NULL,
148 `otdel` varchar(45) NOT NULL,
149 `nachalnik` varchar(45) NOT NULL,
150 `phone_number` int(11) NOT NULL,
151 PRIMARY KEY (`otd_code`),
152 UNIQUE KEY `otd_code_UNIQUE` (`otd_code`)
153) ENGINE=InnoDB DEFAULT CHARSET=utf8;
154/*!40101 SET character_set_client = @saved_cs_client */;
155
156--
157-- Dumping data for table `otdeli`
158--
159
160LOCK TABLES `otdeli` WRITE;
161/*!40000 ALTER TABLE `otdeli` DISABLE KEYS */;
162/*!40000 ALTER TABLE `otdeli` ENABLE KEYS */;
163UNLOCK TABLES;
164
165--
166-- Dumping routines for database 'mydb'
167--
168/*!50003 DROP PROCEDURE IF EXISTS `Add_Doc` */;
169/*!50003 SET @saved_cs_client = @@character_set_client */ ;
170/*!50003 SET @saved_cs_results = @@character_set_results */ ;
171/*!50003 SET @saved_col_connection = @@collation_connection */ ;
172/*!50003 SET character_set_client = utf8mb4 */ ;
173/*!50003 SET character_set_results = utf8mb4 */ ;
174/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
175/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
176/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
177DELIMITER ;;
178CREATE DEFINER=`root`@`localhost` PROCEDURE `Add_Doc`(Marchive_num int(11), nazvanie VARCHAR(45), fio VARCHAR(45), stranic_col int(11), my_date date, doc_types_type_code int(11), orgs_org_code int(11))
179BEGIN
180 DECLARE k INTEGER;
181 DECLARE err INTEGER DEFAULT FALSE;
182 DECLARE EXIT HANDLER FOR SQLSTATE '45002' SET err=TRUE;
183 SELECT COUNT(*) FROM document
184 WHERE archive_num=Marchive_num INTO k;
185 IF k=0 THEN
186 INSERT INTO document VALUES(Marchive_num, nazvanie, fio, stranic_col, my_date, doc_types_type_code, orgs_org_code);
187 ELSE
188 SIGNAL SQLSTATE '45000' SET message_text='Document already exists';
189 END IF;
190END ;;
191DELIMITER ;
192/*!50003 SET sql_mode = @saved_sql_mode */ ;
193/*!50003 SET character_set_client = @saved_cs_client */ ;
194/*!50003 SET character_set_results = @saved_cs_results */ ;
195/*!50003 SET collation_connection = @saved_col_connection */ ;
196/*!50003 DROP PROCEDURE IF EXISTS `Add_org` */;
197/*!50003 SET @saved_cs_client = @@character_set_client */ ;
198/*!50003 SET @saved_cs_results = @@character_set_results */ ;
199/*!50003 SET @saved_col_connection = @@collation_connection */ ;
200/*!50003 SET character_set_client = utf8mb4 */ ;
201/*!50003 SET character_set_results = utf8mb4 */ ;
202/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
203/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
204/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
205DELIMITER ;;
206CREATE DEFINER=`root`@`localhost` PROCEDURE `Add_org`(Morg_code int(11), ogranizaciya varchar(45), adress varchar(45), indexx int(11), city varchar(45), phone_num int(11), faks varchar(45), mail varchar(45))
207BEGIN
208 DECLARE k INTEGER;
209 DECLARE err INTEGER DEFAULT FALSE;
210 DECLARE EXIT HANDLER FOR SQLSTATE '45002' SET err=TRUE;
211 SELECT COUNT(*) FROM ogrs
212 WHERE org_code=Morg_code INTO k;
213 IF k=0 THEN
214 INSERT INTO orgs VALUES(Morg_code, ogranizaciya, adress, indexx, city, phone_num, faks, mail);
215 ELSE
216 SIGNAL SQLSTATE '45000' SET message_text='org already exists';
217 END IF;
218END ;;
219DELIMITER ;
220/*!50003 SET sql_mode = @saved_sql_mode */ ;
221/*!50003 SET character_set_client = @saved_cs_client */ ;
222/*!50003 SET character_set_results = @saved_cs_results */ ;
223/*!50003 SET collation_connection = @saved_col_connection */ ;
224/*!50003 DROP PROCEDURE IF EXISTS `Add_otdel` */;
225/*!50003 SET @saved_cs_client = @@character_set_client */ ;
226/*!50003 SET @saved_cs_results = @@character_set_results */ ;
227/*!50003 SET @saved_col_connection = @@collation_connection */ ;
228/*!50003 SET character_set_client = utf8mb4 */ ;
229/*!50003 SET character_set_results = utf8mb4 */ ;
230/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
231/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
232/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
233DELIMITER ;;
234CREATE DEFINER=`root`@`localhost` PROCEDURE `Add_otdel`(Motd_code int(11), otdel varchar(45), nachalnik varchar(45), phone_number int(11))
235BEGIN
236 DECLARE k INTEGER;
237 DECLARE err INTEGER DEFAULT FALSE;
238 DECLARE EXIT HANDLER FOR SQLSTATE '45002' SET err=TRUE;
239 SELECT COUNT(*) FROM otdeli
240 WHERE otd_code=Motd_code INTO k;
241 IF k=0 THEN
242 INSERT INTO otdeli VALUES(Motd_code, otdel, nachalnik, phone_number);
243 ELSE
244 SIGNAL SQLSTATE '45000' SET message_text='otdel already exists';
245 END IF;
246END ;;
247DELIMITER ;
248/*!50003 SET sql_mode = @saved_sql_mode */ ;
249/*!50003 SET character_set_client = @saved_cs_client */ ;
250/*!50003 SET character_set_results = @saved_cs_results */ ;
251/*!50003 SET collation_connection = @saved_col_connection */ ;
252/*!50003 DROP PROCEDURE IF EXISTS `Add_Type` */;
253/*!50003 SET @saved_cs_client = @@character_set_client */ ;
254/*!50003 SET @saved_cs_results = @@character_set_results */ ;
255/*!50003 SET @saved_col_connection = @@collation_connection */ ;
256/*!50003 SET character_set_client = utf8mb4 */ ;
257/*!50003 SET character_set_results = utf8mb4 */ ;
258/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
259/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
260/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
261DELIMITER ;;
262CREATE DEFINER=`root`@`localhost` PROCEDURE `Add_Type`(Mtype_code int(11), type_d varchar(45))
263BEGIN
264 DECLARE k INTEGER;
265 DECLARE err INTEGER DEFAULT FALSE;
266 DECLARE EXIT HANDLER FOR SQLSTATE '45002' SET err=TRUE;
267 SELECT COUNT(*) FROM type_code
268 WHERE type_code=Mtype_code INTO k;
269 IF k=0 THEN
270 INSERT INTO type_code VALUES(Mtype_code, type_d);
271 ELSE
272 SIGNAL SQLSTATE '45000' SET message_text='type already exists';
273 END IF;
274END ;;
275DELIMITER ;
276/*!50003 SET sql_mode = @saved_sql_mode */ ;
277/*!50003 SET character_set_client = @saved_cs_client */ ;
278/*!50003 SET character_set_results = @saved_cs_results */ ;
279/*!50003 SET collation_connection = @saved_col_connection */ ;
280/*!50003 DROP PROCEDURE IF EXISTS `Add_Zapis` */;
281/*!50003 SET @saved_cs_client = @@character_set_client */ ;
282/*!50003 SET @saved_cs_results = @@character_set_results */ ;
283/*!50003 SET @saved_col_connection = @@collation_connection */ ;
284/*!50003 SET character_set_client = utf8mb4 */ ;
285/*!50003 SET character_set_results = utf8mb4 */ ;
286/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
287/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
288/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
289DELIMITER ;;
290CREATE DEFINER=`root`@`localhost` PROCEDURE `Add_Zapis`(Mnomer_zap int(11), sotrudnik varchar(45), nom_example int(11), date_vidachi date, date_vozvrata date, otdeli_otd_code int(11), document_archive_num int(11), kolichestvo_example int(11))
291BEGIN
292 DECLARE k INTEGER;
293 DECLARE err INTEGER DEFAULT FALSE;
294 DECLARE EXIT HANDLER FOR SQLSTATE '45002' SET err=TRUE;
295 SELECT COUNT(*) FROM jurnal
296 WHERE nomer_zape=Mnomer_zape INTO k;
297 IF k=0 THEN
298 INSERT INTO jurnal VALUES(Mnomer_zap, sotrudnik, nom_example,date_vidachi, date_vozvrata,otdeli_otd_code, document_archive_num, kolichestvo_example);
299 ELSE
300 SIGNAL SQLSTATE '45000' SET message_text='zapis already exists';
301 END IF;
302END ;;
303DELIMITER ;
304/*!50003 SET sql_mode = @saved_sql_mode */ ;
305/*!50003 SET character_set_client = @saved_cs_client */ ;
306/*!50003 SET character_set_results = @saved_cs_results */ ;
307/*!50003 SET collation_connection = @saved_col_connection */ ;
308/*!50003 DROP PROCEDURE IF EXISTS `Delete_Doc` */;
309/*!50003 SET @saved_cs_client = @@character_set_client */ ;
310/*!50003 SET @saved_cs_results = @@character_set_results */ ;
311/*!50003 SET @saved_col_connection = @@collation_connection */ ;
312/*!50003 SET character_set_client = utf8mb4 */ ;
313/*!50003 SET character_set_results = utf8mb4 */ ;
314/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
315/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
316/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
317DELIMITER ;;
318CREATE DEFINER=`root`@`localhost` PROCEDURE `Delete_Doc`(Marchive_num int(11))
319BEGIN
320 DECLARE k INTEGER;
321 DECLARE err INTEGER DEFAULT FALSE;
322 DECLARE EXIT HANDLER FOR SQLSTATE '45007' SET err=TRUE;
323 SELECT COUNT(*) FROM document
324 WHERE archive_num=Marchive_num INTO k;
325 IF k=1 THEN
326 DELETE FROM document WHERE archive_num=Marchive_num;
327 ELSE
328 SIGNAL SQLSTATE '45007' SET message_text='doc Doesn`t Exist';
329 END IF;
330END ;;
331DELIMITER ;
332/*!50003 SET sql_mode = @saved_sql_mode */ ;
333/*!50003 SET character_set_client = @saved_cs_client */ ;
334/*!50003 SET character_set_results = @saved_cs_results */ ;
335/*!50003 SET collation_connection = @saved_col_connection */ ;
336/*!50003 DROP PROCEDURE IF EXISTS `Delete_org` */;
337/*!50003 SET @saved_cs_client = @@character_set_client */ ;
338/*!50003 SET @saved_cs_results = @@character_set_results */ ;
339/*!50003 SET @saved_col_connection = @@collation_connection */ ;
340/*!50003 SET character_set_client = utf8mb4 */ ;
341/*!50003 SET character_set_results = utf8mb4 */ ;
342/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
343/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
344/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
345DELIMITER ;;
346CREATE DEFINER=`root`@`localhost` PROCEDURE `Delete_org`(Morg_code int(11))
347BEGIN
348 DECLARE k INTEGER;
349 DECLARE err INTEGER DEFAULT FALSE;
350 DECLARE EXIT HANDLER FOR SQLSTATE '45007' SET err=TRUE;
351 SELECT COUNT(*) FROM orgs
352 WHERE org_code=Morg_code INTO k;
353 IF k=1 THEN
354 DELETE FROM orgs WHERE org_code=Morg_code;
355 ELSE
356 SIGNAL SQLSTATE '45007' SET message_text='org Doesn`t Exist';
357 END IF;
358END ;;
359DELIMITER ;
360/*!50003 SET sql_mode = @saved_sql_mode */ ;
361/*!50003 SET character_set_client = @saved_cs_client */ ;
362/*!50003 SET character_set_results = @saved_cs_results */ ;
363/*!50003 SET collation_connection = @saved_col_connection */ ;
364/*!50003 DROP PROCEDURE IF EXISTS `Delete_otdel` */;
365/*!50003 SET @saved_cs_client = @@character_set_client */ ;
366/*!50003 SET @saved_cs_results = @@character_set_results */ ;
367/*!50003 SET @saved_col_connection = @@collation_connection */ ;
368/*!50003 SET character_set_client = utf8mb4 */ ;
369/*!50003 SET character_set_results = utf8mb4 */ ;
370/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
371/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
372/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
373DELIMITER ;;
374CREATE DEFINER=`root`@`localhost` PROCEDURE `Delete_otdel`(Motd_code int(11))
375BEGIN
376 DECLARE k INTEGER;
377 DECLARE err INTEGER DEFAULT FALSE;
378 DECLARE EXIT HANDLER FOR SQLSTATE '45007' SET err=TRUE;
379 SELECT COUNT(*) FROM otdeli
380 WHERE otd_code=Motd_code INTO k;
381 IF k=1 THEN
382 DELETE FROM otdeli WHERE otd_code=Motd_code;
383 ELSE
384 SIGNAL SQLSTATE '45007' SET message_text='otdel Doesn`t Exist';
385 END IF;
386END ;;
387DELIMITER ;
388/*!50003 SET sql_mode = @saved_sql_mode */ ;
389/*!50003 SET character_set_client = @saved_cs_client */ ;
390/*!50003 SET character_set_results = @saved_cs_results */ ;
391/*!50003 SET collation_connection = @saved_col_connection */ ;
392/*!50003 DROP PROCEDURE IF EXISTS `Delete_type` */;
393/*!50003 SET @saved_cs_client = @@character_set_client */ ;
394/*!50003 SET @saved_cs_results = @@character_set_results */ ;
395/*!50003 SET @saved_col_connection = @@collation_connection */ ;
396/*!50003 SET character_set_client = utf8mb4 */ ;
397/*!50003 SET character_set_results = utf8mb4 */ ;
398/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
399/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
400/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
401DELIMITER ;;
402CREATE DEFINER=`root`@`localhost` PROCEDURE `Delete_type`(Mtype_code int(11))
403BEGIN
404 DECLARE k INTEGER;
405 DECLARE err INTEGER DEFAULT FALSE;
406 DECLARE EXIT HANDLER FOR SQLSTATE '45007' SET err=TRUE;
407 SELECT COUNT(*) FROM doc_types
408 WHERE type_code=Mtype_code INTO k;
409 IF k=1 THEN
410 DELETE FROM doc_types WHERE type_code=Mtype_code;
411 ELSE
412 SIGNAL SQLSTATE '45007' SET message_text='type Doesn`t Exist';
413 END IF;
414END ;;
415DELIMITER ;
416/*!50003 SET sql_mode = @saved_sql_mode */ ;
417/*!50003 SET character_set_client = @saved_cs_client */ ;
418/*!50003 SET character_set_results = @saved_cs_results */ ;
419/*!50003 SET collation_connection = @saved_col_connection */ ;
420/*!50003 DROP PROCEDURE IF EXISTS `Delete_zapis` */;
421/*!50003 SET @saved_cs_client = @@character_set_client */ ;
422/*!50003 SET @saved_cs_results = @@character_set_results */ ;
423/*!50003 SET @saved_col_connection = @@collation_connection */ ;
424/*!50003 SET character_set_client = utf8mb4 */ ;
425/*!50003 SET character_set_results = utf8mb4 */ ;
426/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
427/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
428/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
429DELIMITER ;;
430CREATE DEFINER=`root`@`localhost` PROCEDURE `Delete_zapis`(Mnomer_zap int(11))
431BEGIN
432 DECLARE k INTEGER;
433 DECLARE err INTEGER DEFAULT FALSE;
434 DECLARE EXIT HANDLER FOR SQLSTATE '45007' SET err=TRUE;
435 SELECT COUNT(*) FROM jurnal
436 WHERE nomer_zap=Mnomer_zap INTO k;
437 IF k=1 THEN
438 DELETE FROM jurnal WHERE nomer_zap=Mnomer_zap;
439 ELSE
440 SIGNAL SQLSTATE '45007' SET message_text='zapis Doesn`t Exist';
441 END IF;
442END ;;
443DELIMITER ;
444/*!50003 SET sql_mode = @saved_sql_mode */ ;
445/*!50003 SET character_set_client = @saved_cs_client */ ;
446/*!50003 SET character_set_results = @saved_cs_results */ ;
447/*!50003 SET collation_connection = @saved_col_connection */ ;
448/*!50003 DROP PROCEDURE IF EXISTS `Edit_Doc` */;
449/*!50003 SET @saved_cs_client = @@character_set_client */ ;
450/*!50003 SET @saved_cs_results = @@character_set_results */ ;
451/*!50003 SET @saved_col_connection = @@collation_connection */ ;
452/*!50003 SET character_set_client = utf8mb4 */ ;
453/*!50003 SET character_set_results = utf8mb4 */ ;
454/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
455/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
456/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
457DELIMITER ;;
458CREATE DEFINER=`root`@`localhost` PROCEDURE `Edit_Doc`(Marchive_num int(11), Mnazvanie VARCHAR(45), Mfio VARCHAR(45), Mstranic_col int(11), Mmy_date date, Mdoc_types_type_code int(11), Morgs_org_code int(11))
459BEGIN
460 DECLARE k INTEGER;
461 DECLARE err INTEGER DEFAULT FALSE;
462 DECLARE EXIT HANDLER FOR SQLSTATE '45005' SET err=TRUE;
463 SELECT COUNT(*) FROM document
464 WHERE archive_num=Marchive_num INTO k;
465 IF k=1 THEN
466 UPDATE Trip_Type #VALUES(Marchive_num, Mnazvanie, Mfio, Mstranic_col, Mmy_date, Mdoc_types_type_code, Morgs_org_code);
467 SET nazvanie=Mnazvanie,
468 fio=Mfio,
469 stranic_col=Mstranic_col,
470 my_date=Mmy_date,
471 doc_types_type_code=Mdoc_types_type_code,
472 orgs_org_code=Morgs_org_code
473 WHERE archive_num=Marchive_num;
474 ELSE
475 SIGNAL SQLSTATE '45005' SET message_text='doc Doesn`t Exist';
476 END IF;
477END ;;
478DELIMITER ;
479/*!50003 SET sql_mode = @saved_sql_mode */ ;
480/*!50003 SET character_set_client = @saved_cs_client */ ;
481/*!50003 SET character_set_results = @saved_cs_results */ ;
482/*!50003 SET collation_connection = @saved_col_connection */ ;
483/*!50003 DROP PROCEDURE IF EXISTS `Edit_org` */;
484/*!50003 SET @saved_cs_client = @@character_set_client */ ;
485/*!50003 SET @saved_cs_results = @@character_set_results */ ;
486/*!50003 SET @saved_col_connection = @@collation_connection */ ;
487/*!50003 SET character_set_client = utf8mb4 */ ;
488/*!50003 SET character_set_results = utf8mb4 */ ;
489/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
490/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
491/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
492DELIMITER ;;
493CREATE DEFINER=`root`@`localhost` PROCEDURE `Edit_org`(Morg_code int(11), Mogranizaciya varchar(45), Madress varchar(45), Mindexx int(11), Mcity varchar(45), Mphone_num int(11), Mfaks varchar(45), Mmail varchar(45))
494BEGIN
495 DECLARE k INTEGER;
496 DECLARE err INTEGER DEFAULT FALSE;
497 DECLARE EXIT HANDLER FOR SQLSTATE '45005' SET err=TRUE;
498 SELECT COUNT(*) FROM orgs
499 WHERE org_code=Morg_code INTO k;
500 IF k=1 THEN
501 UPDATE Trip_Type #VALUES(Morg_code, Mogranizaciya, Madress, Mindexx, Mcity, Mphone_num, Mfaks, Mmail);
502 SET
503 ogranizaciya=Mogranizaciya,
504 adress=Madress,
505 indexx=Mindexx,
506 city=Mcity,
507 phone_num=Mphone_num,
508 faks=Mfaks,
509 mail=Mmail
510 WHERE org_code=Morg_code;
511 ELSE
512 SIGNAL SQLSTATE '45005' SET message_text='org Doesn`t Exist';
513 END IF;
514END ;;
515DELIMITER ;
516/*!50003 SET sql_mode = @saved_sql_mode */ ;
517/*!50003 SET character_set_client = @saved_cs_client */ ;
518/*!50003 SET character_set_results = @saved_cs_results */ ;
519/*!50003 SET collation_connection = @saved_col_connection */ ;
520/*!50003 DROP PROCEDURE IF EXISTS `Edit_otdel` */;
521/*!50003 SET @saved_cs_client = @@character_set_client */ ;
522/*!50003 SET @saved_cs_results = @@character_set_results */ ;
523/*!50003 SET @saved_col_connection = @@collation_connection */ ;
524/*!50003 SET character_set_client = utf8mb4 */ ;
525/*!50003 SET character_set_results = utf8mb4 */ ;
526/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
527/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
528/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
529DELIMITER ;;
530CREATE DEFINER=`root`@`localhost` PROCEDURE `Edit_otdel`(Motd_code int(11), Motdel varchar(45), Mnachalnik varchar(45), Mphone_number int(11))
531BEGIN
532 DECLARE k INTEGER;
533 DECLARE err INTEGER DEFAULT FALSE;
534 DECLARE EXIT HANDLER FOR SQLSTATE '45005' SET err=TRUE;
535 SELECT COUNT(*) FROM otdeli
536 WHERE otd_code=Motd_code INTO k;
537 IF k=1 THEN
538 UPDATE Trip_Type #VALUES(Motd_code, Motdel, Mnachalnik, Mphone_number);
539 SET
540 otdel=Motdel,
541 nachalnik=Mnachalnik,
542 phone_number=Mphone_number
543 WHERE otd_code=Motd_code;
544 ELSE
545 SIGNAL SQLSTATE '45005' SET message_text='otdel Doesn`t Exist';
546 END IF;
547END ;;
548DELIMITER ;
549/*!50003 SET sql_mode = @saved_sql_mode */ ;
550/*!50003 SET character_set_client = @saved_cs_client */ ;
551/*!50003 SET character_set_results = @saved_cs_results */ ;
552/*!50003 SET collation_connection = @saved_col_connection */ ;
553/*!50003 DROP PROCEDURE IF EXISTS `Edit_type` */;
554/*!50003 SET @saved_cs_client = @@character_set_client */ ;
555/*!50003 SET @saved_cs_results = @@character_set_results */ ;
556/*!50003 SET @saved_col_connection = @@collation_connection */ ;
557/*!50003 SET character_set_client = utf8mb4 */ ;
558/*!50003 SET character_set_results = utf8mb4 */ ;
559/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
560/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
561/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
562DELIMITER ;;
563CREATE DEFINER=`root`@`localhost` PROCEDURE `Edit_type`(Mtype_code int(11), Mtype_d varchar(45))
564BEGIN
565 DECLARE k INTEGER;
566 DECLARE err INTEGER DEFAULT FALSE;
567 DECLARE EXIT HANDLER FOR SQLSTATE '45005' SET err=TRUE;
568 SELECT COUNT(*) FROM doc_types
569 WHERE type_code=Mtype_code INTO k;
570 IF k=1 THEN
571 UPDATE Trip_Type #VALUES(Mtype_code, Mtype_d);
572 SET
573 type_d=Mtype_d
574 WHERE type_code=Mtype_code;
575 ELSE
576 SIGNAL SQLSTATE '45005' SET message_text='type Doesn`t Exist';
577 END IF;
578END ;;
579DELIMITER ;
580/*!50003 SET sql_mode = @saved_sql_mode */ ;
581/*!50003 SET character_set_client = @saved_cs_client */ ;
582/*!50003 SET character_set_results = @saved_cs_results */ ;
583/*!50003 SET collation_connection = @saved_col_connection */ ;
584/*!50003 DROP PROCEDURE IF EXISTS `Edit_Zapis` */;
585/*!50003 SET @saved_cs_client = @@character_set_client */ ;
586/*!50003 SET @saved_cs_results = @@character_set_results */ ;
587/*!50003 SET @saved_col_connection = @@collation_connection */ ;
588/*!50003 SET character_set_client = utf8mb4 */ ;
589/*!50003 SET character_set_results = utf8mb4 */ ;
590/*!50003 SET collation_connection = utf8mb4_0900_ai_ci */ ;
591/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
592/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' */ ;
593DELIMITER ;;
594CREATE DEFINER=`root`@`localhost` PROCEDURE `Edit_Zapis`(Mnomer_zap int(11), Msotrudnik varchar(45), Mnom_example int(11), Mdate_vidachi date, Mdate_vozvrata date, Motdeli_otd_code int(11), Mdocument_archive_num int(11), Mkolichestvo_example int(11))
595BEGIN
596 DECLARE k INTEGER;
597 DECLARE err INTEGER DEFAULT FALSE;
598 DECLARE EXIT HANDLER FOR SQLSTATE '45005' SET err=TRUE;
599 SELECT COUNT(*) FROM jurnal
600 WHERE nomer_zap=Mnomer_zap INTO k;
601 IF k=1 THEN
602 UPDATE Trip_Type #VALUES(Mnomer_zap, Msotrudnik, Mnom_example, Mdate_vidachi, Mdate_vozvrata, Motdeli_otd_code, Mdocument_archive_num, Mkolichestvo_example);
603 SET
604 nomer_zap=Mnomer_zap,
605 sotrudnik=Msotrudnik,
606 nom_example=Mnom_example,
607 date_vidachi = Mdate_vidachi,
608 date_vozvrata = Mdate_vozvrata,
609 otdeli_otd_code = Motdeli_otd_code,
610 document_archive_num = Mdocument_archive_num,
611 kolichestvo_example = Mkolichestvo_example
612 WHERE nomer_zap=Mnomer_zap;
613 ELSE
614 SIGNAL SQLSTATE '45005' SET message_text='zapis Doesn`t Exist';
615 END IF;
616END ;;
617DELIMITER ;
618/*!50003 SET sql_mode = @saved_sql_mode */ ;
619/*!50003 SET character_set_client = @saved_cs_client */ ;
620/*!50003 SET character_set_results = @saved_cs_results */ ;
621/*!50003 SET collation_connection = @saved_col_connection */ ;
622/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
623
624/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
625/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
626/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
627/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
628/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
629/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
630/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
631
632-- Dump completed on 2019-06-11 2:59:09