1. Шаардлага №1
Listagg
Өмнөх бичлэг дээр байсан table-үүдийг ашиглаад бас өөр нэг шаардлагыг хэрэгжүүлж үзье. Энэ нь сурагчийн жагсаалтыг харуулангаа, түүний авч байсан дүнгүүдийг огнооны дарааллаар нь цувуулан харуулах юм.
Энд listagg хэмээх oracle-н үндсэн функцийг ашигласан байгаа. Энэ функцийн зориулалт нь угаасаа энэ юм. тодорхой талбараар груплээд, тэндээсээ өөр нэг талбарыг жагсаан харуулах. to_char ашигладаг нь ямар учиртай вэ гэвэл listagg нь
nvarchar2 төрлийн хувьсагчийн харуулахдаа дөрвөлжин дүрсүүд болгочихоод байдаг учраас ингэсэн юм.
Ингээд жишээ үр дүнг харцгаая
2. Шаардлага №2
Эндээс үүдээд, сурагчдын жагсаалт дээр хамгийн авсан дүнг нь хичээлийн нэр, огноотой нь харуулъя. (Өөр нэг жишээ гэвэл тухайн барааны анх худалдаж авсан дүн байж болно).
Listagg-г жаахан string manipulation-той холиод доорх query-г бичиж болно
Үр дүн нь
Сул тал
listagg маань varchar төрөл дээр ажилладаг учир, 4000-с дээш урттай текст бүтээх явцдаа алдаа заадаг сул талтай. Мэдээж том жижиг бүхий л бааз дээр ажиллах зүйл хийх шаардлагатай учир өөр арга хайж олъё.
Row_number
Дээрх сул талыг шийдэхийн тулд row_number() ашиглая. энэ функц нь result set-г нэг талбараар бүлэглэхдээ, тэр бүлэг доторх мөрүүдийг нь дугаарлаж өгдөг юм. Жишээ нь
Энэ query-ний үр дүн нь :
Шаардлага №2-ынхоо query-г дахиад бичье, энэ удаа row_number() ашиглаж:
Хамгийн дотор талд, дээр ашигласан query-ээ хийж өгөөд, тэндээсээ зөвхөн 1 гэсэн grade_order-той мөрүүдийг нь аваад тэрийгээ join-уудаар баяжуулаад өгөхөд л үрдүнгээ авч чадаж байна.
Энэний давуу тал нь, 4000 тэмдэгтийн алдаа гарахгүй, нэг сурагч хичнээ мянга дүн авсан ч болно, мөн group by ашиглахгүй, арай цэвэрхэн болсон байгаа. Сул тал нь гэвэл group by ашиглаагүй учраас, aggregate функц-ээ хамтад нь бичиж
болохгүй болж байгаа юм (Жишээ нь өмнө нь Listagg хийхдээ хамтад нь count() хийж болох байсан).
Нэгэнт л 4000 тэмдэгтийн алдаа Шаардлага №1-н query дээр гарах болохоор, мөн хүн тийм урт юмыг (нэг сурагч хэдэн мянган дүн авах боломжтой бол) хараад байх боломжгүй (эсвэл хүсэхгүй) учраас тэр query-ээ дахиад бичье, ингэхдээ, тухайн
сурагчийн эхний 10 дүнг л харуулдаг болгоё. Listagg болон Row_number-г хамтад нь ашиглаж.
10-р мөр дээр заавал case ашиглаад байх шаардлага байхгүй, доор нь WHERE GRADE_ORDER <= 10 гэж бичихэд л хангалттай. Гэвч бид хамтад нь нийт хэдэн дүн авсаныг нь бас харуулъя гэсэн болохоор ингэж хийлээ. Ингээд үр дүн нь :
Thursday, October 24, 2013
Wednesday, October 16, 2013
SUM - CASE (Pivot like query)
1. Оршил
Ямар нэг систем хөгжүүлэх явцад (эсвэл жирийн нэг бааз дээр ажиллах явцад) гардаг шаардлагын нэг нь пивот маягийн үр дүнг харуулах query юм. Жишээ нь нийт сурагчдын ямар ямар дүнг хэдэн удаа авсан байна эсвэл барааны тухайн огноо хүртэлх үлдэгдэл болон эцсийн (нийт) үлдэгдэл гэх мэт. Яагаад пивот шиг гэж байна гэхээр, харахад пивот table шиг мөртлөө, багана нь static (өгөгдлөөс хамаарч өөрчлөгддөггүй) болохоор тэр.
Энэний шийдэл нь их энгийн бөгөөд, хувьдаа нэлээд их ашигладаг, мөн хүмүүс ч асуугаад байдаг болохоор нь нийтлэе гэж бодлоо
2. Өгөгдөл
Эхний жишээг шууд харуулах байдлаар шийдлээ үзүүлье. Тэгэхээр бидэнд student, journal, lesson гэсэн 3 table хэрэг болно. Эдгээр нь
3. Шийдэл
Энд хичээлийн нэр шаардлагагүй байсан болохоор, хичээлийн table-г ашигласангүй. Мөн заавал sub-query ашиглаад байх шаардлага ч байхгүй л дээ, гэхдээ яг бодогдсон үр дүн, нэмэлт мэдээлэл зэргийг тусад нь харахад амар байдаг болохоор ингэж ашиглаад сурчихсан юм.
Ингээд жишээ үр дүн нь :
Ямар нэг систем хөгжүүлэх явцад (эсвэл жирийн нэг бааз дээр ажиллах явцад) гардаг шаардлагын нэг нь пивот маягийн үр дүнг харуулах query юм. Жишээ нь нийт сурагчдын ямар ямар дүнг хэдэн удаа авсан байна эсвэл барааны тухайн огноо хүртэлх үлдэгдэл болон эцсийн (нийт) үлдэгдэл гэх мэт. Яагаад пивот шиг гэж байна гэхээр, харахад пивот table шиг мөртлөө, багана нь static (өгөгдлөөс хамаарч өөрчлөгддөггүй) болохоор тэр.
Энэний шийдэл нь их энгийн бөгөөд, хувьдаа нэлээд их ашигладаг, мөн хүмүүс ч асуугаад байдаг болохоор нь нийтлэе гэж бодлоо
2. Өгөгдөл
Эхний жишээг шууд харуулах байдлаар шийдлээ үзүүлье. Тэгэхээр бидэнд student, journal, lesson гэсэн 3 table хэрэг болно. Эдгээр нь
3. Шийдэл
Энд хичээлийн нэр шаардлагагүй байсан болохоор, хичээлийн table-г ашигласангүй. Мөн заавал sub-query ашиглаад байх шаардлага ч байхгүй л дээ, гэхдээ яг бодогдсон үр дүн, нэмэлт мэдээлэл зэргийг тусад нь харахад амар байдаг болохоор ингэж ашиглаад сурчихсан юм.
Ингээд жишээ үр дүн нь :
Monday, May 27, 2013
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
Ийм нөхцөлд 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
Thursday, May 9, 2013
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) байдаггүй.
За энэ алдаа 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) байдаггүй.
Wednesday, May 8, 2013
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-г оролцуулахгүй вэ гэдгийг.
Жич: Энэ аргаа энд хуваалцсан байгаа шүү.
Хэсэг хугацааны өмнө манай програмд ямар нэг бичлэгийг устгах үед тухайн бичлэгийг ашигласан газар байгаа эсэхийг нь шалгадаг юм хийх хэрэгтэй боллоо. Тэгэхийн тулд ямар нэг 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-г оролцуулахгүй вэ гэдгийг.
Жич: Энэ аргаа энд хуваалцсан байгаа шүү.
Subscribe to:
Posts (Atom)