Oracle PL / SQL saglabātā procedūra & Funkcijas ar piemēriem

Satura rādītājs:

Anonim

Šajā apmācībā jūs redzēsiet detalizētu aprakstu par to, kā izveidot un izpildīt nosauktos blokus (procedūras un funkcijas).

Procedūras un funkcijas ir apakšprogrammas, kuras var izveidot un saglabāt datu bāzē kā datu bāzes objektus. Tos var izsaukt vai norādīt arī citu bloku iekšienē.

Bez tam mēs aplūkosim galvenās atšķirības starp šīm divām apakšprogrammām. Mēs arī apspriedīsim Oracle iebūvētās funkcijas.

Šajā Oracle Stored Procedūras apmācībā jūs uzzināsiet

  • Terminoloģijas PL / SQL apakšprogrammās
  • Kas ir procedūra PL / SQL?
  • Kas ir funkcija?
  • Procedūras un funkcijas līdzības
  • Procedūra Vs. Funkcija: Galvenās atšķirības
  • Iebūvētās funkcijas PL / SQL

Terminoloģijas PL / SQL apakšprogrammās

Pirms mēs uzzinām par PL / SQL apakšprogrammām, mēs apspriedīsim dažādas terminoloģijas, kas ir šo apakšprogrammu sastāvdaļa. Zemāk ir terminoloģijas, kuras mēs apspriedīsim.

Parametrs:

Parametrs ir mainīgs vai vietturis jebkuram derīgam PL / SQL datu tipam, caur kuru PL / SQL apakšprogramma apmainās ar vērtībām ar galveno kodu. Šis parametrs ļauj ievadīt apakšprogrammas un iegūt no šīm apakšprogrammām.

  • Šie parametri jāizveido kopā ar apakšprogrammām.
  • Šie parametri ir iekļauti šo apakšprogrammu izsaukuma paziņojumā, lai mijiedarbotos ar apakšprogrammām.
  • Parametra datu tipam apakšprogrammā un izsaukuma priekšrakstam jābūt vienādam.
  • Parametra deklarēšanas laikā datu veida lielumu nevajadzētu pieminēt, jo šī veida lielums ir dinamisks.

Pamatojoties uz to mērķi, parametri tiek klasificēti kā

  1. IN parametrs
  2. OUT parametrs
  3. IN OUT parametrs

IN parametrs:

  • Šis parametrs tiek izmantots, lai ievadītu apakšprogrammas.
  • Tas ir tikai lasāms mainīgais apakšprogrammu iekšpusē. To vērtības apakšprogrammā nevar mainīt.
  • Izsaucošā paziņojumā šie parametri var būt mainīgais, burtiskā vērtība vai izteiksme, piemēram, tā var būt aritmētiskā izteiksme, piemēram, '5 * 8' vai 'a / b', kur 'a' un 'b' ir mainīgie .
  • Pēc noklusējuma parametri ir IN tipa.

OUT parametrs:

  • Šis parametrs tiek izmantots, lai iegūtu izejas no apakšprogrammām.
  • Tas ir lasīšanas un rakstīšanas mainīgais apakšprogrammu iekšpusē. To vērtības var mainīt apakšprogrammās.
  • Izsaukuma paziņojumā šiem parametriem vienmēr jābūt mainīgajam, lai noturētu vērtību no pašreizējām apakšprogrammām.

IN OUT parametrs:

  • Šis parametrs tiek izmantots gan ievadei, gan izejas iegūšanai no apakšprogrammām.
  • Tas ir lasīšanas un rakstīšanas mainīgais apakšprogrammu iekšpusē. To vērtības var mainīt apakšprogrammās.
  • Izsaukuma paziņojumā šiem parametriem vienmēr jābūt mainīgajiem, lai noturētu vērtību no apakšprogrammām.

Šie parametru veidi jāpiemin apakšprogrammu izveidošanas laikā.

ATGRIEZTIES

RETURN ir atslēgvārds, kas liek kompilatoram pārslēgt vadību no apakšprogrammas uz izsaukuma paziņojumu. Apakšprogrammā RETURN vienkārši nozīmē, ka vadībai jāiziet no apakšprogrammas. Kad kontrolieris apakšprogrammā atradīs RETURN atslēgvārdu, kods pēc tā tiks izlaists.

Parasti vecāks vai galvenais bloks izsauks apakšprogrammas, un pēc tam vadība pāriet no šiem vecāku blokiem uz izsauktajām apakšprogrammām. RETURN apakšprogrammā atgriezīs vadību atpakaļ vecāku blokā. Funkciju RETURN gadījumā atgriež arī vērtību. Funkcijas deklarēšanas laikā šīs vērtības datu tips vienmēr tiek minēts. Datu tips var būt jebkura derīga PL / SQL datu tips.

Kas ir procedūra PL / SQL?

Procedūra in PL / SQL ir apakšprogramma vienība, kas sastāv no grupas, PL / SQL, ko var saukt pēc nosaukuma. Katrai procedūrai PL / SQL ir savs unikālais nosaukums, ar kuru to var atsaukties un izsaukt. Šī Oracle datu bāzes apakšprogrammas vienība tiek saglabāta kā datu bāzes objekts.

Piezīme: Apakšprogramma nav nekas cits kā procedūra, un tā ir jāizveido manuāli, kā noteikts prasībā. Pēc izveides tie tiks saglabāti kā datu bāzes objekti.

Tālāk ir aprakstīti procedūras apakšprogrammas vienības raksturlielumi PL / SQL:

  • Procedūras ir atsevišķi programmas bloki, kurus var saglabāt datu bāzē.
  • Šīs PLSQL procedūras var izsaukt, atsaucoties uz to vārdu, lai izpildītu PL / SQL priekšrakstus.
  • To galvenokārt izmanto, lai procesu izpildītu PL / SQL.
  • Tam var būt ligzdoti bloki, vai arī to var definēt un ievietot pārējos blokos vai paketēs.
  • Tajā ir deklarācijas daļa (pēc izvēles), izpildes daļa, izņēmumu apstrādes daļa (pēc izvēles).
  • Vērtības var pārsūtīt Oracle procedūrā vai iegūt no procedūras, izmantojot parametrus.
  • Šie parametri jāiekļauj izsaukuma paziņojumā.
  • Procedūrai SQL var būt RETURN priekšraksts, lai atgrieztu vadību zvanīšanas blokā, taču tā nevar atgriezt nevienu vērtību, izmantojot RETURN priekšrakstu.
  • Procedūras nevar izsaukt tieši no SELECT paziņojumiem. Tos var izsaukt no cita bloka vai ar EXEC atslēgvārda palīdzību.

Sintakse:

CREATE OR REPLACE PROCEDURE
(
… )[ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE PROCEDURE liek kompilatoram izveidot jaunu procedūru Oracle. Atslēgvārds “VAI NOMAINI” liek kompilētam aizstāt esošo procedūru (ja tāda ir) ar pašreizējo.
  • Procedūras nosaukumam jābūt unikālam.
  • Atslēgvārds “IS” tiks izmantots, kad Oracle saglabātā procedūra tiks ievietota citos blokos. Ja procedūra ir atsevišķa, tiks izmantota “AS”. Izņemot šo kodēšanas standartu, abiem ir vienāda nozīme.

1. piemērs: Procedūras izveide un izsaukšana, izmantojot EXEC

Šajā piemērā mēs izveidosim Oracle procedūru, kurā nosaukums tiek izmantots kā ievade un sagaidīšanas ziņa tiek izdrukāta kā izeja. Lai izsauktu procedūru, mēs izmantosim komandu EXEC.

CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)ISBEGINdbms_output.put_line (‘Welcome '|| p_name);END;/EXEC welcome_msg (‘Guru99’);

Koda skaidrojums:

  • 1. koda rindiņa : procedūras izveide ar nosaukumu 'welcome_msg' un vienu parametru 'p_name' ar 'IN' tipu.
  • 4. koda rindiņa : sveiciena ziņojuma izdrukāšana, sasienot ievades nosaukumu.
  • Procedūra ir veiksmīgi sastādīta.
  • Kodu rinda 7 : procedūras izsaukšana, izmantojot komandu EXEC ar parametru 'Guru99'. Procedūra tiek izpildīta, un ziņojums tiek izdrukāts kā "Welcome Guru99".

Kas ir funkcija?

Funkcijas ir atsevišķa PL / SQL apakšprogramma. Tāpat kā PL / SQL procedūra, arī funkcijām ir unikāls nosaukums, ar kuru tās var atsaukties. Tie tiek glabāti kā PL / SQL datu bāzes objekti. Zemāk ir sniegti daži funkciju raksturlielumi.

  • Funkcijas ir atsevišķs bloks, ko galvenokārt izmanto aprēķinu vajadzībām.
  • Funkcija izmanto RETURN atslēgvārdu, lai atgrieztu vērtību, un tā datu tips ir noteikts izveides laikā.
  • Funkcijai vajadzētu vai nu atgriezt vērtību, vai arī paaugstināt izņēmumu, ti, funkcijai atgriešana ir obligāta.
  • Funkciju bez DML priekšrakstiem var tieši izsaukt SELECT vaicājumā, savukārt funkciju ar DML darbību var izsaukt tikai no citiem PL / SQL blokiem.
  • Tam var būt ligzdoti bloki, vai arī to var definēt un ievietot pārējos blokos vai paketēs.
  • Tajā ir deklarācijas daļa (pēc izvēles), izpildes daļa, izņēmumu apstrādes daļa (pēc izvēles).
  • Vērtības var pārsūtīt funkcijā vai iegūt caur procedūru, izmantojot parametrus.
  • Šie parametri jāiekļauj izsaukuma paziņojumā.
  • PLSQL funkcija var arī atgriezt vērtību, izmantojot OUT parametrus, izņemot RETURN izmantošanu.
  • Tā kā tā vienmēr atgriezīs vērtību, izsaukuma paziņojumā tā vienmēr tiek pievienota piešķiršanas operatoram, lai aizpildītu mainīgos.

Sintakse

CREATE OR REPLACE FUNCTION
(
)RETURN [ IS | AS ]BEGINEXCEPTIONEND;
  • CREATE FUNCTION uzdod kompilatoram izveidot jaunu funkciju. Atslēgvārds “OR REPLACE” liek kompilatoram aizstāt esošo funkciju (ja tāda ir) ar pašreizējo.
  • Funkcijas nosaukumam jābūt unikālam.
  • Jāpiemin RETURN datu tips.
  • Atslēgvārds “IS” tiks izmantots, kad procedūra ir ievietota citos blokos. Ja procedūra ir atsevišķa, tiks izmantota “AS”. Izņemot šo kodēšanas standartu, abiem ir vienāda nozīme.

1. piemērs: Funkcijas izveide un izsaukšana, izmantojot anonīmo bloku

Šajā programmā mēs izveidosim funkciju, kas vārdu pieņem kā ievadi un atgriež sveiciena ziņojumu kā izvadi. Lai izsauktu funkciju, mēs izmantosim anonīmu bloku un atlasīsim paziņojumu.

CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2ISBEGINRETURN (‘Welcome ‘|| p_name);END;/DECLARElv_msg VARCHAR2(250);BEGINlv_msg := welcome_msg_func (‘Guru99’);dbms_output.put_line(lv_msg);END;SELECT welcome_msg_func(‘Guru99:) FROM DUAL;

Koda skaidrojums:

  • 1. koda rindiņa : Oracle funkcijas izveide ar nosaukumu 'welcome_msg_func' un ar vienu parametru 'p_name', kura tips ir 'IN'.
  • 2. koda rindiņa : atgriešanās veida deklarēšana par VARCHAR2
  • 5. koda rindiņa : atgriež sasieto vērtību “Welcome” un parametra vērtību.
  • Kodu rinda 8 : Anonīms bloķēt, lai izsauktu iepriekš minēto funkciju.
  • 9. koda rindiņa : Mainīgā deklarēšana ar datu tipu tāds pats kā funkcijas atgriešanas datu tips.
  • 11. koda rindiņa : Funkcijas izsaukšana un mainīgā “lv_msg” atgriešanās vērtības aizpildīšana.
  • 12. koda rindiņa : mainīgā lieluma drukāšana. Šeit iegūtais rezultāts ir "Welcome Guru99"
  • 14. koda rindiņa : tās pašas funkcijas izsaukšana, izmantojot SELECT paziņojumu. Atgriešanās vērtība tiek novirzīta tieši uz standarta izvadi.

Procedūras un funkcijas līdzības

  • Abus var izsaukt no citiem PL / SQL blokiem.
  • Ja apakšprogrammā izvirzītais izņēmums netiek apstrādāts apakšprogrammas izņēmumu apstrādes sadaļā, tas tiks izplatīts izsaukuma blokā.
  • Abiem parametriem var būt tik daudz parametru, cik nepieciešams.
  • Abi PL / SQL tiek uzskatīti par datu bāzes objektiem.

Procedūra Vs. Funkcija: Galvenās atšķirības

Procedūra Funkcija
  • Izmanto galvenokārt noteikta procesa izpildei
  • Izmanto galvenokārt dažu aprēķinu veikšanai
  • SELECT paziņojumā nevar piezvanīt
  • Funkciju, kurā nav DML priekšrakstu, var izsaukt SELECT priekšrakstā
  • Izmantojiet parametru OUT, lai atgrieztu vērtību
  • Izmantojiet RETURN, lai atgrieztu vērtību
  • Atgriezt vērtību nav obligāti
  • Vērtība ir jāatdod obligāti
  • RETURN vienkārši izies no apakšprogrammas vadības.
  • RETURN izies no apakšprogrammas vadības un atgriež arī vērtību
  • Atgriešanas datatips izveides laikā netiks norādīts
  • Atgriešanas datu tips ir obligāts izveides laikā

Iebūvētās funkcijas PL / SQL

PL / SQL satur dažādas iebūvētas funkcijas darbam ar virknēm un datuma datu tipu. Šeit mēs redzēsim parasti lietotās funkcijas un to lietošanu.

Konversijas funkcijas

Šīs iebūvētās funkcijas tiek izmantotas, lai pārveidotu vienu datu tipu citam datu tipam.

Funkcijas nosaukums Lietošana Piemērs
TO_CHAR Pārvērš citu datu tipu par rakstura datu tipu TO_CHAR (123);
TO_DATE (virkne, formāts) Pārvērš norādīto virkni par datumu. Virknei jāatbilst formātam. TO_DATE ('2015-JAN-15', 'GGGG-MON-DD'); Rezultāts: 15.01.2015
TO_NUMBER (teksts, formāts) Pārvērš tekstu par norādītā formāta numuru tipu. Informāts “9” apzīmē ciparu skaitu Atlasiet TO_NUMBER ('1234', '9999') no dual; Izeja: 1234 Izvēlieties TO_NUMBER ('1,234.45', '9,999.99') no dual; Rezultāts: 1234

Stīgu funkcijas

Šīs ir funkcijas, kuras tiek izmantotas rakstzīmju datu tipā.

Funkcijas nosaukums Lietošana Piemērs
INSTR (teksts, virkne, sākums, izpildījums) Piešķir konkrēta teksta pozīciju dotajā virknē.
  • teksts - galvenā virkne
  • virkne - teksts, kas jāmeklē
  • sākuma - meklēšanas sākuma pozīcija (pēc izvēles)
  • atbilstība - meklētās virknes parādīšanās (pēc izvēles)
 Izvēlieties INSTR ( "LIDMAŠĪNU", "E", 2,1) no duālās izejas : 2 Izvēlieties INSTR ( "LIDMAŠĪNU", "E", 2,2) no dubulto Output: 9 (2 nd notikums E)
SUBSTR (teksts, sākums, garums) Piešķir galvenās virknes apakšvirknes vērtību.
  • teksts - galvenā virkne
  • sākums - sākuma stāvoklis
  • garums - garums, kas jānosaka apakšā
 atlasiet substrātu ('lidmašīna', 1,7) no dual Output : aeropla
UPPER (teksts) Atgriež norādītā teksta lielo burtu Izvēlieties augšējo ('guru99') no dual; Rezultāts : GURU99
LOWER (teksts) Atgriež norādītā teksta mazo burtu Izvēlieties zemāko ('lidmašīna') no divkāršās; Rezultāts : lidmašīna
INITCAP (teksts) Atgriež norādīto tekstu ar sākuma burtu lielajiem burtiem. Atlasiet ('guru99') no divkāršās izejas : Guru99 Atlasiet ('mans stāsts') no divkāršās izejas : Mans stāsts
LENGTH (teksts) Atgriež norādītās virknes garumu Izvēlieties LENGTH ('guru99') no dual; Rezultāts : 6
LPAD (teksts, garums, pad_char) Atbalsta virkni kreisajā pusē norādītajam garumam (kopējā virkne) ar doto rakstzīmi Atlasiet LPAD ('guru99', 10, '$') no dual; Izeja : $$$$ guru99
RPAD (teksts, garums, pad_char) Atbalsta virkni labajā pusē norādītajam garumam (kopējā virkne) ar doto rakstzīmi Atlasiet RPAD ('guru99', 10, '-') no divkāršās izvades : guru99 ----
LTRIM (teksts) Apgriež no teksta vadošo atstarpi Izvēlieties LTRIM ('Guru99') no dual; Rezultāts : Guru99
RTRIM (teksts) Apgriež teksta beigu atstarpi Izvēlieties RTRIM ('Guru99') no dual; Izeja ; Guru99

Datuma funkcijas

Tās ir funkcijas, kuras tiek izmantotas manipulācijām ar datumiem.

Funkcijas nosaukums Lietošana Piemērs
ADD_MONTHS (datums, mēnešu skaits) Datumam pievieno norādītos mēnešus ADD_MONTH ('2015-01-01', 5); Rezultāts : 2015. gada 5. janvāris
SYSDATE Atgriež servera pašreizējo datumu un laiku Atlasiet SYSDATE no dual; Izeja : 2015. gada 4. oktobris 14:11:43
TRUNC Datuma mainīgā noapaļošana uz zemāko iespējamo vērtību no dual atlasiet sysdate, TRUNC (sysdate); Izeja : 2015. gada 4. oktobris 14:12:39 4.10.2015
RAUNDS Noapaļo datumu līdz tuvākajai robežai vai nu augstāk, vai zemāk Izvēlieties sysdate, ROUND (sysdate) no divkāršās izejas : 2015.10.04 14:14:34 5.10.2015.
MONTHS_BETWEEN Atgriež mēnešu skaitu starp diviem datumiem Atlasiet MONTHS_BETWEEN (sysdate + 60, sysdate) no divkāršās izejas : 2

Kopsavilkums

Šajā nodaļā mēs esam iemācījušies sekojošo.

  • Kā izveidot Procedūru un dažādus veidus, kā to saukt
  • Kā izveidot funkciju un dažādus veidus, kā to izsaukt
  • Procedūras un funkcijas līdzības un atšķirības
  • Parametri un RETURN kopīgās terminoloģijas PL / SQL apakšprogrammās
  • Oracle PL / SQL kopējās iebūvētās funkcijas