Mercurial > pub > bltoolkit

comparison Data/Create Scripts/Oracle.sql @ 0:f990fcb411a9

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Копия текущей версии из github
author cin
date Thu, 27 Mar 2014 21:46:09 +0400
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:f990fcb411a9
1 -- Person Table
2
3 DROP SEQUENCE PersonSeq
4 /
5 DROP TABLE Doctor
6 /
7 DROP TABLE Patient
8 /
9 DROP TABLE Person
10 /
11 DROP SEQUENCE BinaryDataSeq
12 /
13 DROP TABLE BinaryData
14 /
15 DROP SEQUENCE DataTypeTestSeq
16 /
17 DROP TABLE DataTypeTest
18 /
19 DROP TABLE GrandChild
20 /
21 DROP TABLE Child
22 /
23 DROP TABLE Parent
24 /
25 DROP TABLE LinqDataTypes
26 /
27 DROP SEQUENCE SequenceTestSeq
28 /
29 DROP TABLE SequenceTest
30 /
31 DROP TABLE "STG_TRADE_INFORMATION"
32 /
33 DROP table t_test_user_contract
34 /
35 DROP table t_test_user
36 /
37 DROP sequence sq_test_user
38 /
39 DROP sequence sq_test_user_contract
40 /
41
42
43 CREATE SEQUENCE PersonSeq
44 /
45
46 CREATE TABLE Person
47 ( PersonID NUMBER NOT NULL PRIMARY KEY
48 , Firstname VARCHAR2(50) NOT NULL
49 , Lastname VARCHAR2(50) NOT NULL
50 , Middlename VARCHAR2(50)
51 , Gender CHAR(1) NOT NULL
52
53 , CONSTRAINT Ck_Person_Gender CHECK (Gender IN ('M', 'F', 'U', 'O'))
54 )
55 /
56
57 -- Insert Trigger for Person
58
59 CREATE OR REPLACE TRIGGER Person_Add
60 BEFORE INSERT
61 ON Person
62 FOR EACH ROW
63 BEGIN
64 SELECT
65 PersonSeq.NEXTVAL
66 INTO
67 :NEW.PersonID
68 FROM
69 dual;
70 END;
71 /
72
73 -- Doctor Table Extension
74
75 CREATE TABLE Doctor
76 ( PersonID NUMBER NOT NULL PRIMARY KEY
77 , Taxonomy NVARCHAR2(50) NOT NULL
78
79 , CONSTRAINT Fk_Doctor_Person FOREIGN KEY (PersonID)
80 REFERENCES Person (PersonID) ON DELETE CASCADE
81 )
82 /
83
84 -- Patient Table Extension
85
86 CREATE TABLE Patient
87 ( PersonID NUMBER NOT NULL PRIMARY KEY
88 , Diagnosis NVARCHAR2(256) NOT NULL
89
90 , CONSTRAINT Fk_Patient_Person FOREIGN KEY (PersonID)
91 REFERENCES Person (PersonID) ON DELETE CASCADE
92 )
93 /
94
95 -- Sample data for Person/Doctor/Patient
96
97 INSERT INTO Person (FirstName, LastName, Gender) VALUES ('John', 'Pupkin', 'M')
98 /
99 INSERT INTO Person (FirstName, LastName, Gender) VALUES ('Tester', 'Testerson', 'M')
100 /
101 INSERT INTO Doctor (PersonID, Taxonomy) VALUES (PersonSeq.CURRVAL, 'Psychiatry')
102 /
103 INSERT INTO Patient (PersonID, Diagnosis) VALUES (PersonSeq.CURRVAL, 'Hallucination with Paranoid Bugs'' Delirium of Persecution')
104 /
105
106 -- Person_Delete
107
108 CREATE OR REPLACE
109 PROCEDURE Person_Delete(pPersonID IN NUMBER) IS
110 BEGIN
111 DELETE FROM
112 Person
113 WHERE
114 PersonID = pPersonID;
115 END;
116 /
117
118 -- Person_Insert
119
120 CREATE OR REPLACE
121 PROCEDURE Person_Insert_OutputParameter
122 ( pFirstName IN NVARCHAR2
123 , pLastName IN NVARCHAR2
124 , pMiddleName IN NVARCHAR2
125 , pGender IN CHAR
126 , pPersonID OUT NUMBER
127 ) IS
128 BEGIN
129 INSERT INTO Person
130 ( LastName, FirstName, MiddleName, Gender)
131 VALUES
132 (pLastName, pFirstName, pMiddleName, pGender)
133 RETURNING
134 PersonID
135 INTO
136 pPersonID;
137 END;
138 /
139
140 CREATE OR REPLACE
141 FUNCTION Person_Insert
142 ( pFirstName IN NVARCHAR2
143 , pLastName IN NVARCHAR2
144 , pMiddleName IN NVARCHAR2
145 , pGender IN CHAR
146 )
147 RETURN SYS_REFCURSOR IS
148 retCursor SYS_REFCURSOR;
149 lPersonID NUMBER;
150 BEGIN
151 INSERT INTO Person
152 ( LastName, FirstName, MiddleName, Gender)
153 VALUES
154 (pLastName, pFirstName, pMiddleName, pGender)
155 RETURNING
156 PersonID
157 INTO
158 lPersonID;
159
160 OPEN retCursor FOR
161 SELECT
162 PersonID, Firstname, Lastname, Middlename, Gender
163 FROM
164 Person
165 WHERE
166 PersonID = lPersonID;
167 RETURN
168 retCursor;
169 END;
170 /
171
172 -- Person_SelectAll
173
174 CREATE OR REPLACE
175 FUNCTION Person_SelectAll
176 RETURN SYS_REFCURSOR IS
177 retCursor SYS_REFCURSOR;
178 BEGIN
179 OPEN retCursor FOR
180 SELECT
181 PersonID, Firstname, Lastname, Middlename, Gender
182 FROM
183 Person;
184 RETURN
185 retCursor;
186 END;
187 /
188
189 -- Person_SelectAllByGender
190
191 CREATE OR REPLACE
192 FUNCTION Person_SelectAllByGender(pGender IN CHAR)
193 RETURN SYS_REFCURSOR IS
194 retCursor SYS_REFCURSOR;
195 BEGIN
196 OPEN retCursor FOR
197 SELECT
198 PersonID, Firstname, Lastname, Middlename, Gender
199 FROM
200 Person
201 WHERE
202 Gender = pGender;
203 RETURN
204 retCursor;
205 END;
206 /
207
208 -- Person_SelectByKey
209
210 CREATE OR REPLACE
211 FUNCTION Person_SelectByKey(pID IN NUMBER)
212 RETURN SYS_REFCURSOR IS
213 retCursor SYS_REFCURSOR;
214 BEGIN
215 OPEN retCursor FOR
216 SELECT
217 PersonID, Firstname, Lastname, Middlename, Gender
218 FROM
219 Person
220 WHERE
221 PersonID = pID;
222 RETURN
223 retCursor;
224 END;
225 /
226
227 -- Person_SelectByName
228
229 CREATE OR REPLACE
230 FUNCTION Person_SelectByName
231 ( pFirstName IN NVARCHAR2
232 , pLastName IN NVARCHAR2
233 )
234 RETURN SYS_REFCURSOR IS
235 retCursor SYS_REFCURSOR;
236 BEGIN
237 OPEN retCursor FOR
238 SELECT
239 PersonID, Firstname, Lastname, Middlename, Gender
240 FROM
241 Person
242 WHERE
243 FirstName = pFirstName AND LastName = pLastName;
244 RETURN
245 retCursor;
246 END;
247 /
248
249 -- Person_SelectListByName
250
251 CREATE OR REPLACE
252 FUNCTION Person_SelectListByName
253 ( pFirstName IN NVARCHAR2
254 , pLastName IN NVARCHAR2
255 )
256 RETURN SYS_REFCURSOR IS
257 retCursor SYS_REFCURSOR;
258 BEGIN
259 OPEN retCursor FOR
260 SELECT
261 PersonID, Firstname, Lastname, Middlename, Gender
262 FROM
263 Person
264 WHERE
265 FirstName LIKE pFirstName AND LastName LIKE pLastName;
266 RETURN
267 retCursor;
268 END;
269 /
270
271 CREATE OR REPLACE
272 PROCEDURE Person_Update
273 ( pPersonID IN NUMBER
274 , pFirstName IN NVARCHAR2
275 , pLastName IN NVARCHAR2
276 , pMiddleName IN NVARCHAR2
277 , pGender IN CHAR
278 ) IS
279 BEGIN
280 UPDATE
281 Person
282 SET
283 LastName = pLastName,
284 FirstName = pFirstName,
285 MiddleName = pMiddleName,
286 Gender = pGender
287 WHERE
288 PersonID = pPersonID;
289 END;
290 /
291
292 -- Patient_SelectAll
293
294 CREATE OR REPLACE
295 FUNCTION Patient_SelectAll
296 RETURN SYS_REFCURSOR IS
297 retCursor SYS_REFCURSOR;
298 BEGIN
299 OPEN retCursor FOR
300 SELECT
301 Person.*, Patient.Diagnosis
302 FROM
303 Patient, Person
304 WHERE
305 Patient.PersonID = Person.PersonID;
306 RETURN
307 retCursor;
308 END;
309 /
310
311
312 -- Patient_SelectByName
313
314 CREATE OR REPLACE
315 FUNCTION Patient_SelectByName
316 ( pFirstName IN NVARCHAR2
317 , pLastName IN NVARCHAR2
318 )
319 RETURN SYS_REFCURSOR IS
320 retCursor SYS_REFCURSOR;
321 BEGIN
322 OPEN retCursor FOR
323 SELECT
324 Person.*, Patient.Diagnosis
325 FROM
326 Patient, Person
327 WHERE
328 Patient.PersonID = Person.PersonID
329 AND FirstName = pFirstName AND LastName = pLastName;
330 RETURN
331 retCursor;
332 END;
333 /
334
335 -- BinaryData Table
336
337 CREATE SEQUENCE BinaryDataSeq
338 /
339
340 CREATE TABLE BinaryData
341 ( BinaryDataID NUMBER NOT NULL PRIMARY KEY
342 , Stamp TIMESTAMP DEFAULT SYSDATE NOT NULL
343 , Data BLOB NOT NULL
344 )
345 /
346
347 -- Insert Trigger for Binarydata
348
349 CREATE OR REPLACE TRIGGER BinaryData_Add
350 BEFORE INSERT
351 ON BinaryData
352 FOR EACH ROW
353 BEGIN
354 SELECT
355 BinaryDataSeq.NEXTVAL
356 INTO
357 :NEW.BinaryDataID
358 FROM
359 dual;
360 END;
361 /
362
363 -- OutRefTest
364
365 CREATE OR REPLACE
366 PROCEDURE OutRefTest
367 ( pID IN NUMBER
368 , pOutputID OUT NUMBER
369 , pInputOutputID IN OUT NUMBER
370 , pStr IN NVARCHAR2
371 , pOutputStr OUT NVARCHAR2
372 , pInputOutputStr IN OUT NVARCHAR2
373 ) IS
374 BEGIN
375 pOutputID := pID;
376 pInputOutputID := pID + pInputOutputID;
377 pOutputStr := pStr;
378 pInputOutputStr := pStr || pInputOutputStr;
379 END;
380 /
381
382 CREATE OR REPLACE
383 PROCEDURE OutRefEnumTest
384 ( pStr IN NVARCHAR2
385 , pOutputStr OUT NVARCHAR2
386 , pInputOutputStr IN OUT NVARCHAR2
387 ) IS
388 BEGIN
389 pOutputStr := pStr;
390 pInputOutputStr := pStr || pInputOutputStr;
391 END;
392 /
393
394 -- ArrayTest
395
396 CREATE OR REPLACE
397 PROCEDURE ArrayTest
398 ( pIntArray IN DBMS_UTILITY.NUMBER_ARRAY
399 , pOutputIntArray OUT DBMS_UTILITY.NUMBER_ARRAY
400 , pInputOutputIntArray IN OUT DBMS_UTILITY.NUMBER_ARRAY
401 , pStrArray IN DBMS_UTILITY.NAME_ARRAY
402 , pOutputStrArray OUT DBMS_UTILITY.NAME_ARRAY
403 , pInputOutputStrArray IN OUT DBMS_UTILITY.NAME_ARRAY
404 ) IS
405 BEGIN
406 pOutputIntArray := pIntArray;
407
408 FOR i IN pIntArray.FIRST..pIntArray.LAST LOOP
409 pInputOutputIntArray(i) := pInputOutputIntArray(i) + pIntArray(i);
410 END LOOP;
411
412 pOutputStrArray := pStrArray;
413
414 FOR i IN pStrArray.FIRST..pStrArray.LAST LOOP
415 pInputOutputStrArray(i) := pInputOutputStrArray(i) || pStrArray(i);
416 END LOOP;
417 END;
418 /
419
420 CREATE OR REPLACE
421 PROCEDURE ScalarArray
422 ( pOutputIntArray OUT DBMS_UTILITY.NUMBER_ARRAY
423 ) IS
424 BEGIN
425 FOR i IN 1..5 LOOP
426 pOutputIntArray(i) := i;
427 END LOOP;
428 END;
429 /
430
431 -- ResultSetTest
432
433 CREATE OR REPLACE
434 PROCEDURE RESULTSETTEST
435 ( mr OUT SYS_REFCURSOR
436 , sr OUT SYS_REFCURSOR
437 ) IS
438 BEGIN
439 OPEN mr FOR
440 SELECT 1 as MasterID FROM dual
441 UNION SELECT 2 as MasterID FROM dual;
442 OPEN sr FOR
443 SELECT 4 SlaveID, 1 as MasterID FROM dual
444 UNION SELECT 5 SlaveID, 2 as MasterID FROM dual
445 UNION SELECT 6 SlaveID, 2 as MasterID FROM dual
446 UNION SELECT 7 SlaveID, 1 as MasterID FROM dual;
447 END;
448 /
449
450 -- ExecuteScalarTest
451
452 CREATE OR REPLACE
453 FUNCTION Scalar_DataReader
454 RETURN SYS_REFCURSOR
455 IS
456 retCursor SYS_REFCURSOR;
457 BEGIN
458 OPEN retCursor FOR
459 SELECT
460 12345 intField, '54321' stringField
461 FROM
462 DUAL;
463 RETURN
464 retCursor;
465 END;
466 /
467
468 CREATE OR REPLACE
469 PROCEDURE Scalar_OutputParameter
470 ( pOutputInt OUT BINARY_INTEGER
471 , pOutputString OUT NVARCHAR2
472 ) IS
473 BEGIN
474 pOutputInt := 12345;
475 pOutputString := '54321';
476 END;
477 /
478
479 CREATE OR REPLACE
480 FUNCTION Scalar_ReturnParameter
481 RETURN BINARY_INTEGER IS
482 BEGIN
483 RETURN
484 12345;
485 END;
486 /
487
488 -- Data Types test
489
490 CREATE SEQUENCE DataTypeTestSeq
491 /
492
493 CREATE TABLE DataTypeTest
494 (
495 DataTypeID INTEGER NOT NULL PRIMARY KEY,
496 Binary_ RAW(50) NULL,
497 Boolean_ NUMBER(1,0) NULL,
498 Byte_ NUMBER(3,0) NULL,
499 Bytes_ BLOB NULL,
500 Char_ NCHAR NULL,
501 DateTime_ DATE NULL,
502 Decimal_ NUMBER(19,5) NULL,
503 Double_ DOUBLE PRECISION NULL,
504 Guid_ RAW(16) NULL,
505 Int16_ NUMBER(5,0) NULL,
506 Int32_ NUMBER(10,0) NULL,
507 Int64_ NUMBER(20,0) NULL,
508 Money_ NUMBER NULL,
509 SByte_ NUMBER(3,0) NULL,
510 Single_ FLOAT NULL,
511 Stream_ BLOB NULL,
512 String_ NVARCHAR2(50) NULL,
513 UInt16_ NUMBER(5,0) NULL,
514 UInt32_ NUMBER(10,0) NULL,
515 UInt64_ NUMBER(20,0) NULL,
516 Xml_ XMLTYPE NULL
517 )
518 /
519
520 -- Insert Trigger for DataTypeTest
521
522 CREATE OR REPLACE TRIGGER DataTypeTest_Add
523 BEFORE INSERT
524 ON DataTypeTest
525 FOR EACH ROW
526 BEGIN
527 SELECT
528 DataTypeTestSeq.NEXTVAL
529 INTO
530 :NEW.DataTypeID
531 FROM
532 dual;
533 END;
534 /
535
536 INSERT INTO DataTypeTest
537 (Binary_, Boolean_, Byte_, Bytes_, Char_, DateTime_, Decimal_,
538 Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_,
539 Single_, Stream_, String_, UInt16_, UInt32_, UInt64_, Xml_)
540 VALUES
541 ( NULL, NULL, NULL, NULL, NULL, NULL, NULL,
542 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
543 NULL, NULL, NULL, NULL, NULL, NULL, NULL)
544 /
545
546 INSERT INTO DataTypeTest
547 (Binary_, Boolean_, Byte_, Bytes_, Char_, DateTime_, Decimal_,
548 Double_, Guid_, Int16_, Int32_, Int64_, Money_, SByte_,
549 Single_, Stream_, String_, UInt16_, UInt32_, UInt64_,
550 Xml_)
551 VALUES
552 (SYS_GUID(), 1, 255, SYS_GUID(), 'B', SYSDATE, 12345.67,
553 1234.567, SYS_GUID(), 32767, 32768, 1000000, 12.3456, 127,
554 1234.123, SYS_GUID(), 'string', 32767, 32768, 200000000,
555 XMLTYPE('<root><element strattr="strvalue" intattr="12345"/></root>'))
556 /
557
558
559
560 CREATE TABLE Parent (ParentID int, Value1 int)
561 /
562 CREATE TABLE Child (ParentID int, ChildID int)
563 /
564 CREATE TABLE GrandChild (ParentID int, ChildID int, GrandChildID int)
565 /
566
567
568 CREATE TABLE LinqDataTypes
569 (
570 ID int,
571 MoneyValue decimal(10,4),
572 DateTimeValue timestamp,
573 DateTimeValue2 timestamp,
574 BoolValue smallint,
575 GuidValue raw(16),
576 BinaryValue blob NULL,
577 SmallIntValue smallint,
578 IntValue int NULL,
579 BigIntValue number(20,0) NULL
580 )
581 /
582
583 CREATE SEQUENCE SequenceTestSeq
584 MINVALUE 1
585 START WITH 1
586 INCREMENT BY 1
587 CACHE 10
588 /
589
590 CREATE TABLE SequenceTest
591 (
592 ID int NOT NULL PRIMARY KEY,
593 Value VARCHAR2(50) NOT NULL
594 )
595 /
596
597 CREATE TABLE "STG_TRADE_INFORMATION"
598 (
599 "STG_TRADE_ID" NUMBER NOT NULL ENABLE,
600 "STG_TRADE_VERSION" NUMBER NOT NULL ENABLE,
601 "INFORMATION_TYPE_ID" NUMBER NOT NULL ENABLE,
602 "INFORMATION_TYPE_NAME" VARCHAR2(50 BYTE),
603 "VALUE" VARCHAR2(4000 BYTE),
604 "VALUE_AS_INTEGER" NUMBER,
605 "VALUE_AS_DATE" DATE
606 )
607 /
608
609
610 create table t_test_user
611 (
612 user_id number primary key,
613 name varchar2(255) not null unique
614 )
615 /
616
617 create table t_test_user_contract
618 (
619 user_contract_id number primary key,
620 user_id number not null references t_test_user on delete cascade,
621 contract_no number not null,
622 name varchar2(255) not null,
623 unique (user_id, contract_no)
624 )
625 /
626
627 create sequence sq_test_user
628 /
629 create sequence sq_test_user_contract
630 /
631
632
633 DROP SEQUENCE TestIdentitySeq
634 /
635 DROP TABLE TestIdentity
636 /
637
638 CREATE TABLE TestIdentity (
639 ID NUMBER NOT NULL PRIMARY KEY
640 )
641 /
642
643 CREATE SEQUENCE TestIdentitySeq
644 /
645
646 CREATE OR REPLACE TRIGGER TestIdentity_Add
647 BEFORE INSERT
648 ON TestIdentity
649 FOR EACH ROW
650 BEGIN
651 SELECT
652 TestIdentitySeq.NEXTVAL
653 INTO
654 :NEW.ID
655 FROM
656 dual;
657 END;
658 /