Table returning function (pipelined)

1. Оршил

Нэг өдөр манай төслүүдийн нэг дээр ийм шаардлага гарч ирлээ. Яг ижилхэн бүтэцтэй, n ширхэг table байгаа, тэр table-үүдээс тодорхой нэг баганаар нь хайлт хийж үр дүнг нь нэг хүснэгтэд харуулах байв. Тэр table-үүд нь бас өөр нэг table дээр бүртгэлтэй байх бөгөөд, замын дунд нэмэгдээд явахаар байв.

Ийм нөхцөлд union ашиглаад view энэ тэрээр хийхэд хүндрэлтэй болох гээд байсан учраас table төрлийн зүйл буцаадаг function бичих шаардлагатай боллоо.

2. Өгөгдөл
Энэ бол манай гол table-н бүтэц нь, жишээ болгоод 2 ширхэг copy-г нь бас оруулчихлаа.

Энэ бол table-үүдийнхээ бүртгэлийг хөтлөх table, жишээ болсон 2 copy-гоо өгөгдөл болгоод оруулчихъя:


3. Шийдэл
Function-даа зориулж өгөгдлийн төрөл үүсгэх хэрэгтэй болно. Буцаж буй мөр бүрийг илтгэх нэг object, мөн тэр object-уудыг list(table) болгож харуулах нэг table гэсэн 2 төрөл үүсгэе.

Эхнийх нь:
Дараах нь:
Ингээд энэхүү 2оо ашиглаад function-аа бичвэл:


4. Ашиглалт
Энэ функцыг ашиглахдаа:
5. Reference
http://stackoverflow.com/questions/2059299/table-valued-functions-in-oracle-11g-parameterized-views

table is mutating, trigger/function may not see it

1. Оршил
За энэ алдаа oracle бааз дээр, ямар нэг table-н row-level trigger дээрээс тухайн table рүүгээ хандаж байгаа үед гардаг алдаа юм. Нэлээн алдартай алдаа бөгөөд, mssql/mysql-н орчингоос oracle руу шилжилт хийж байгаа хүмүүст их тохиолдоно.

Хэсэг тодотгол хийе. Trigger дээрээс table рүү хандана гэдэг маань update/insert эсвэл зүгээр нэг select statement ч байж болох бөгөөд заавал trigger-н body дээрээ кодлогдсон байх албагүй. Өөр нэг функц/процедурт байж байгаад, тухайн trigger-г ажиллах үед дуудагдсан байж болно.

Мөн row-level trigger гэдгийг тайлбарлах гэж оролдоё. MSSql дээр ийм ойлголт байхгүй байгаа юм. Ямар ч update/insert/delete хийсэн, шинэ болон хуучин утгууд нь нэг set байдлаар ирдэг бөгөөд set байдлаар нь өөрчилж болдог, хэрэв нэг нэгээр нь өөрчлөх болбол cursor-р гүйлгэж хийнэ. (Би буруу санаагүй бол)

Харин oracle-д хоёр өөр төвшний trigger байдаг. Нэг нь statement-level, нөгөө нь row-level. Жишээ нь:
гэсэн update statement ажиллах үед тухайн table-н trigger-үүд

1. before update statement-level trigger
2. update хийгдэж буй бүх мөрийн хувьд
  2.1. before update row-level trigger
  2.2. after update row-level trigger
3. after update statement-level trigger

гэсэн дарааллаар ажиллана. Аа тэгээд 2.1, 2.2 дээр ажиллаж буй, row-level trigger-үүд дээр дээрх алдаа гарна гэсэн үг.

Row-level, statement level trigger-г яаж бичих вэ гэвэл, row-level trigger-г бичихдээ, "FOR EACH ROW" гэсэн түлхүүр үг ашиглана. Харин statement-level-н хувьд тийм зүйл бичихгүй.

Одоо тэгвэл, яаж row-level trigger-н үед тухайн table-с хамаарсан үйлдэл хийж update хийх вэ. Ерөнхий зарчим бол, дотроо array төрлийн хувьсагч, хэд хэдэн procedure агуулсан package бичих юм. Дараа нь 1. trigger дээр, өөрөөр хэлбэл, update хийгдэхээс өмнө тухайн array-аа цэвэрлэж эхлүүлэх procedure дуудна. 2.1 болон 2.2-н аль нэг дээр нь шинэ хуучин утгынхаа аль хэрэгтэйг нь авч array-даа хадгалах procedure дуудна. Харин 3. дээр дээрх цуглуулсан өгөгдлүүд дээр тухайн table-тай харьцаж ажиллах procedure дуудна.

2. Шийдэл
Бараа материалын категори гэдэг table байлаа гэж бодъё. Id, нэртэй. мөн бие биенээ агуулдаг учир parent_id-тай. Ингээд ашиглая гэж бодъё. Нэг категорийг сонгоод, тэрний доор агуулж буй, бүх категориудыг харуулахын тулд sortOrder гэдэг талбар ашиглая. (1., 1.1., 1.2., 1.2.1 гэх мэт) Ингэвэл table маань доорх байдалтай болно.
Ингээд тухайн table-н parent_id нь солигдох тоолонд sort_order нь автоматаар өөрчлөгддөг байг. Trigger дээр нь код бичиж болно. Бусад ийм бүтэцтэй table-үүдийг мөн ийм байдлаар шийдэхийн тулд, нэг generic package бичээд, тэрийгээ тал талаас нь trigger-ээр дуудъя. Даанч, package маань тухайн table рүү select хийх учраас "table is mutating.." алдаа заана. Иймээс л нөгөө нэг шийдэл маань хэрэг болно.

Package Header маань :
Package Body маань :
Before-Statement trigger :
After-Row trigger :
After-Statement trigger :



Мөн энэ талаар эндээс анх олж мэдсэн юм.

Жич : Бусад төрлийн бааз дээр өлхөөн хийчих юмыг ийм сүр, бөөн лай болгож хийж байгаа болохоор анхандаа дургүй хүрдэг л юм билээ. "Энэ нөгөө алдартай oracle уу?" гэж асуумаар санагдана. Мөн бас нэг ингэж асуумаар санагддаг зүйл нь auto-increment мөн л sequence, trigger гээд явж өгнө. Бас update-select (update join) байдаггүй.

Table-д хамаарах бүх foreign key constraint-г олох

1. Оршил
Хэсэг хугацааны өмнө манай програмд ямар нэг бичлэгийг устгах үед тухайн бичлэгийг ашигласан газар байгаа эсэхийг нь шалгадаг юм хийх хэрэгтэй боллоо. Тэгэхийн тулд ямар нэг table-д хамаатай бүх foreign key constraint-г олдог view бичээд constraint-нх нь зааж буй table-д тухайн бичлэгийн маань id байна уу гэдгийг шалгах function бичих хэрэгтэй болов. Тэр нь ямар ч хамаагүй table байж болох бөгөөд арр ажиллаж байх үед (runtime) шалгах хэрэгтэй байсан юм.

2. Өрнөл
Oracle-н өөрийнх нь ALL_CONS_COLUMNS, ALL_CONSTRAINTS гэсэн view-үүд байдаг бөгөөд эдгээрийг хооронд нь Join хийж ашиглаад хүссэн үр дүнгээ авч болох юм байна. Гэвч эдгээр нь хэт нүсэр view болоод тэр үү их удаад байлаа. Runtime үед ашиглах болохоор илүү хурдан арга хэрэгтэй болохоор нь эдгээр view-үүдийн доторх query-г нь задалж харж байгаад өөрөө нэг view бичив.

3. Хариу
Үүгээр view үүсгэвэл хамаагүй хурдан юм билээ. Гэхдээ анхаарах ёстой зүйл нь энэ view-г ажиллуулахын тулд, тодорхой эрхтэй байх хэрэгтэй юм. Доор тэдгээр эрхийг нь бичлээ. [USERNAME] гэсэн хэсэгт тухайн user-н name-г нь бичнэ.

4. Ашиглалт
Ингээд гол нь энэ view-г ашиглаж холбоотой бичлэг байгаа эсэхийг нь шалгадаг функц-ээ бичье. Энэ функц нь 3 параметр авна: Хайх гэж буй table-н нэр, Тэр table-нхээ primary key-н утга (Баганы нэр хэрэггүй), Мөн хайх явцад ямар ямар table-г оролцуулахгүй вэ гэдгийг.
Жич: Энэ аргаа энд хуваалцсан байгаа шүү.