View vs Stored Procedure
Saib thiab khaws cov txheej txheem yog ob hom khoom siv database. Views yog hom lus nug, uas sau cov ntaub ntawv los ntawm ib lossis ntau lub rooj. Ntawm no, yog cov syntax los tsim kev pom
create or replace view viewname
as
select_statement;
Ib txheej txheem khaws cia yog qhov ua ntej tso ua ke SQL hais kom ua, uas yog khaws cia hauv database server. Txhua tus txheej txheem khaws cia muaj lub npe hu, uas yog siv los hu lawv hauv lwm cov pob khoom, cov txheej txheem thiab cov haujlwm. Nov yog cov syntax (hauv ORACLE) los tsim cov txheej txheem khaws cia, tsim lossis hloov cov txheej txheem npe (tsis suav)
yog
pib
nqe lus;
exception
exception_handling
end;
View
A Saib ua raws li lub rooj virtual. Nws zais cov lus xaiv hauv nws lub cev. Cov lus xaiv no tuaj yeem yog qhov nyuaj heev, uas siv cov ntaub ntawv los ntawm ntau lub rooj thiab cov kev pom. Yog li ntawd, nyob rau hauv lwm yam lus, ib tug saib yog ib tug npe xaiv nqe lus, uas yog khaws cia nyob rau hauv lub database. Kev pom tuaj yeem siv los nkaum qhov laj thawj tom qab lub rooj sib tham los ntawm cov neeg siv kawg. Txij li qhov pom yog qhov tshwm sim ntawm cov lus nug khaws cia, nws tsis khaws cov ntaub ntawv. Nws sau cov ntaub ntawv los ntawm cov rooj hauv paus thiab qhia. Views ua lub luag haujlwm tseem ceeb hauv kev ruaj ntseg cov ntaub ntawv, thiab. Thaum tus tswv lub rooj yuav tsum tau qhia tsuas yog ib txheej ntawm cov ntaub ntawv rau cov neeg siv kawg, tsim kev pom yog ib qho kev daws teeb meem zoo. Kev pom tuaj yeem muab faib ua ob pawg
- Updataable views (Saib cov uas tuaj yeem siv rau INSERT, Hloov Kho thiab DELETE)
- Non-Updataable views (Saib cov tsis tuaj yeem siv rau INSERT, Hloov Kho thiab DELETE)
Updataable views tsis tuaj yeem suav nrog cov hauv qab, Tsim cov neeg ua haujlwm (INTERSECT, MINUS, UNION, UNION ALL)
DISTINCT
Pab Pawg Sib Sau Ua Haujlwm (AVG, COUNT, MAX, MIN, SUM, thiab lwm yam)
GROUP BY Clause
ORDER BY Clause
Txuas los ntawm Clause
pib nrog Clause
Cia li nthuav qhia hauv ib Daim Ntawv Xaiv
Sub lus nug hauv Ib Daim Ntawv Xaiv
Qu lus nug
Txhim kho cov txheej txheem
Cov txheej txheem khaws cia yog hu ua programming blocks. Lawv yuav tsum muaj lub npe hu. Cov txheej txheem khaws cia txais cov kev txwv raws li cov neeg siv khoom nkag thiab cov txheej txheem raws li lub logic tom qab cov txheej txheem thiab muab cov txiaj ntsig (lossis ua ib qho kev txiav txim tshwj xeeb). Cov lus tshaj tawm sib txawv, cov haujlwm sib txawv, cov lus tswj hwm, cov lus nug, cov lus nug SQL thiab lwm yam haujlwm / txheej txheem / pob hu tuaj yeem nyob hauv lub cev ntawm cov txheej txheem.
Dab tsi yog qhov txawv ntawm Saib thiab Khaws Cov Txheej Txheem?
Cia peb saib qhov sib txawv ntawm ob qho no.
• Views ua raws li lub rooj virtual. Lawv tuaj yeem siv ncaj qha los ntawm kev kaw cov lus nug SQL (xaiv), tab sis cov txheej txheem tsis tuaj yeem siv los ntawm kev kaw cov lus nug.
• Saib tsuas muaj cov lus xaiv raws li lawv lub cev, tab sis cov txheej txheem tuaj yeem muaj cov lus tshaj tawm sib txawv, cov haujlwm sib txawv, tswj cov lus, loops, SQL queries thiab lwm yam haujlwm / txheej txheem / pob hu ua nws lub cev.
• Cov txheej txheem lees txais cov kev txwv kom ua, tab sis cov kev xav tsis xav kom ua tiav.
• Cov hom ntaub ntawv tuaj yeem tsim los ntawm kev pom siv % ROWTYPE, tab sis siv cov txheej txheem, cov ntaub ntawv hom tsis tuaj yeem tsim.
• SQL lus qhia tuaj yeem siv rau hauv cov lus xaiv xaiv, txhawm rau txhim kho txoj kev npaj ua tiav, tab sis SQL lus qhia tsis tuaj yeem siv rau hauv cov txheej txheem khaws cia.
• DELETE, INSERT, Hloov Kho, Xaiv, FLASHBACK, thiab DEBUG tuaj yeem tso cai rau kev pom, tab sis tsuas yog EXECUTE thiab DEBUG tuaj yeem tso cai rau cov txheej txheem.
