|
0
|
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 /
|
