Unik=2— _ = 5aL ond el a SAL | Thtnduckion bo Suebu rad Quem Yanquosie (sq) s- © SQ shands_ or —__—— 1 @—S@L__ler ou cont ced omnia — -)SQu__ become _a_ Standard oP a a.meriicon National 4.Shandamd ins} tude (_ANST)_in_19 6, ovis Hie —.—intemattional _o7-4anisation\Rer shandardi gation £150) — 1Qeb fae Teun ischaemia gly 2b Cela label - a ot aa —_» || chavachemictes and Aduankaat to —__—___ .y 341 4 stanind cuir anne Pa erry on [a> SL is Wah impe toulconmeim 8) £91 allows —Phe_users bo _ delete and _ cebieve data fern the database: Ih Wwe. a | a and __funcHong in a dobabaie. +1: sy Nos Hoe Users be “Sok the perminsinns on the @ scanned with OKEN Scanner~ a] sau Data ta per and + 4) Numeric Oata_ty-per += int pinte num bers 4= cage qr : a bi urnt, INTEGER, SMALLINT , 81G i ______» Ploating poink number a= a —_ | Rem,“ Conere, FLOAT | ____m_Pixed-_poinr puembint___——_7 9 ____}_ _Dramar ai | NUM (Nn, a _—_/2)_Characten-_- Shring daa Values —____) __|#Pinted _leng#h 2- a Loca Cn), CHARACTER (145.<= Neneh 1 a ; ; 4 L * Naving. length a 2 OP eas gaat ! et arc ie_(n), OUP A Sot | VARYING (nj, Lon i ——}8)_Large_abjeot t data hatackeng + | cloa,. OBJECT [jy Bike ¢. BLOB, 4) @ scanned with OKEN Scanner— —) —}-S)_OATE data bupe 3 RTE ain’ the Porm oP Ya-YN dip aio ¥{ OL, ome) ne and Te sduebinelte — There are Pour kyper_oP 59.1. Cammetnel fa i ee Ler 105 sate) ee ie iz do | bel [ome | (ne | —% Pata De FinaHon ang ag e8- —.4) CREATE & This Command is: Wed ty create clabahate | Fobles, off x_n objecks. 4) SEUEL TATRA, cclviove cise, al ox desired Peconds Pron _one 08 rare _tobley __ 2) INSERT & For Insenking toe_becondt_in tte fader @ scanned with OKEN Scanner18) UPDATE t- Por updating one OF more. Piel the table j 4) DELETE § This command is Usted Pox _clelesj datired record *_Oaha Conta! Languge.t- @ scanned with OKEN ScannertL inl —— | Tob lee = [3 Creating —bobler t= 1® ‘A dokabase canbe. coneidened MPM cickacde boa tablet anda table _i9_a er and Columns yo held _dlaka. en Re ri a _@__ individual ctalemenk_io__S@._are coed a. Qui .@ In Khis_SecHon ude will discuss how to create a eq CARATE TABLE Penson —id_( Pdhar- No ink, —~PirstName VARCHAR (80), ary NARCHAR (a0), Address VaRcHAa 18} } 5 @ scanned with OKEN Scannern..debal @ scanned with OKEN ScannerOELETE FR Z ie eq DRLETE P Ror Pengon. details Wert | Adhar No = 839 « n093_ & ie" DELETE Fieom CT.” yi bent 4 sw ft Vite 14 3. siz @ scanned with OKEN Scannertm |4)stuect Query The Seth khaukemenb is ui Prom be database bable 4) The Petit retums in the fe | Sekt beable are cated —' resus . 8) we can ute bhe keyword DT. 4 epHienal Het qutard__indideting. Ene ner contain duplicate oe SELECT Coli, Colg .... Coln D @ scanned with OKEN Scanner9) WHERE 3- d @ The AWERE Command io ut dondiHon. Based on this condiHon the. inthe table can he displayed 6: | ar __deleled . ntax 8: ° A SPRLECT Coli, Coro ues Coln FRom bable name _____AHBRE _Condition 5 | ea. a s @ _SPLECT _Aodhar. No PRoM parson. details. \AHERE city = Pune; | @__serect ¥ PRoM —Peoson details | WanEne city 2° Pune’ @ scanned with OKEN Scanner5 ~spueet i Prom person_detail Uae ORDER ey —Aadhar—no Group BY §= The 01 |__+ha condition GRovp BY Column nae 6s); @ scanned with OKEN ScannerPRoy| student __ Gropp By city HANING Che TIN (Pune ‘Mumba)? ) i i @ scanned with OKEN Scannerxe! string “Opematione ¢: eidode ed ——_fper_shring Comparisons, We Con We the Comps _——-aperabers * =, <>, RE, PE 4k ith the Ordey} 5H Sen, deteamined alphabetical O8 tual . t @_sgt also _peamils a ariel OP Punchons an gy sheng _sush_as_ancatenation Suing a in _____|_ enero Aubs ng 5 Finding length ‘OP si} stonq “to Cos 4 Punch ——@_Paticen_matching can alga be p a | Lp) Porenk Ga 2 Tt matnauag, ‘el - charadlens . @ scanned with OKEN ScannerBuilt-In Panchions *- ny rn sQi_a_ built-in Punchan isa piece Por progaa~ || mming that _kohes een or mare inputs ond ———— _petums. a Yalue: ——— x Mothemahoa Punctions 2+ i oe i@_Abs_Cm)___@_ Mon Cm,9) @® Powencemn}— AovNo Cm, en}) © Senn} _@_ees (ny ean Cn) xen ind ® ces. —— ia re x Shing PuncHond_8= ~_|@ concat CS),52 @ ATAIM (> Lserd Fy @ ubshe {-s,o,Tny) 2 (Loops eee Ee ee beeen 83 @ scanned with OKEN ScannerDate. shidenk — sports — type. Is__NULI @ scanned with OKEN Scannera 3) “uni ive se mitt 3 oe __| nique. Conshraint is alkid to. ipregent=Gume —— _| Naliied in a column. rn bhe mm phoy.6e table, —— for example, you _miant usan be. prover too — {|_om__mart__empleyeed Pom having an \denHea) —_— ___destqnation - wees @_ “In _bhis cate we must we unique. Conatrsict.. ia lies a us - nila | eek QmPLovee —_— _slie moDiP} —_Dewti i NAReHAR (0-0) » Nol AULL » UNIQUE i a 4] Not _Nuw s= can_sek the Column ‘allel ot nak obi) bet ating _| wre E ~ ‘ Tre cheok Condink—ix_uted limit mihi — be. plated sini ai Calta. —_—$—$ ee *t ae a | a: @ scanned with OKEN Scannerae ——— bo ae Join Opesation [Othe sel Joins clawe is aed thee | pweavds” fram__tuoa_ ar mart _bablet_in ala J@ Torn. _is_a—__means for Combining Pio _ keo fables by using Oates commen ma) Naneous by pes of Join operations oe a sent ‘ eo Tone operations, a= a i Sees a) , Inner Join Outer Ton eT ay | left Ton — see @ scanned with OKEN ScannerThe basic —RYnkax eR inn hor i PON cei fs RD fram boble £ was Innew TIN 4ab)e2. | AR) WiTabied s\Comimnn 1Pisld Function_name: specifies the name of the function. [OR REPLACE] option allows modifying an existing function. The optional parameter list contains name, mode and types of the parameters. be passed from outside and OUT represents that this lue outside of the procedure. o INrepresents that value parameter will be used to return a val @ scanned with OKEN ScannerNOM awn a SHNAAkUY No The function must cont return statement. © RETURN clause specifies that data type you are going to return from the function. © Function_body contains the executable part. © The AS keyword is used instead of the IS keyword for creating a standalone function. PL/SQL Function Example Let's see a simple example to create a function. create or replace function adder(n1 in number, n2 in number) return number is 13 number(8); begin n3 :=n1+n2; return n end; / Now write another program to call the function. DECLARE n3 number(2); BEGIN n3 := adder(11,22); dbms_output.put_line(‘Addition END; / Output: | n3); Addition is: 33 Statement processed. 0.05 seconds DECLARE @ scanned with OKEN Scanner2. anumber; 3. b number; 4. number; 5. FUNCTION findMax(x IN number, y IN number) 6, RETURN number 7. Is 8 znumber; 9. BEGIN 10. IFx> y THEN 11 zy 12. ELSE 3, Zey; 14. ENDIF; 15. 16. RETURN z; 17. END; 18. BEGIN 19. a= 23; 20. b:= 45; 21. 22. ¢:= findMax(a, b); 23. dbms_output.put_line(’ Maximum of (23,45): 24, END; 25./ Output: Maximum of (23,45): 45 Statement processed. 0.02 seconds @ scanned with OKEN ScannerPL/SQL Cursor When an SQL statement is processed, Oracle creates a memory area known as context area. A cursor is a pointer to this context area, It contains all information needed for Processing the statement. In PL/SQL, the context area is controlled by Cursor. A cursor contains information on a select statement and the rows of data accessed by it. A cursor is used to referred to a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors: © Implicit Cursors © Explicit Cursors 1) PL/SQL Implicit Cursors The implicit cursors are automatically generated by Oracle while an SQL statement executed, if you don't use an explicit cursor for the statement. These are created by default to process the statements when DML statements like INSERT, UPDATE, DELETE etc. are executed. Orcale provides some attributes known as Implicit cursor’s attributes to check the status of DML operations. Some of them are: %FOUND, %NOTFOUND, %ROWCOUNT and %ISOPEN. ecute the SQL statements like INSERT, UPDATE, DELETE then the cursor attributes tell whether any rows are affected and how many have been affected. If you run a SELECT INTO statement in PL/SQL block, the implicit cursor attribute can be used to find out whether any row has been returned by the SELECT statement. It will return an error if there no data is selected. For example: When you ex The following table soecifies the status of the cursor with each of its attribute. @ scanned with OKEN Scanner %FOUND - a _ aaa Its return value is TRUE if DML statements like INSERT, DELETE and UPDATE affect at lea: row or more rows or a SELECT INTO statement returned one or more rows. Otherv: aa @ scanned with OKEN Scanner returns FALSE, S%6NOTFOUND ¥ return value is TRUE if DML statements like INSERT, DELETE and UPDATE affect no row, 0 ELECT INTO statement return no rows, Otherwise it returns FALSE. It is a just opposite | 96FOUND. ISOPEN It always returns FALSE for implicit cursors, because the SQL cursor is automatically clos after execu! its associated SQL statements. | %ROWCOUNT It returns the number of rows affected by OML statements like INSERT, DELETE, and UPDA ‘or returned by a SELECT INTO statement. PL/SQL Implicit Cursor Example Create customers table and have records: 1 Ramesh | 23 Allahabad 20000 | l2 Suresh 22 Kanpur 22000 3 Mahesh. 24 Ghaziabad 24000 4 ‘Chandan Noida 26000 a Se Sa = t = a Sa 5 Alex a Paris 28000 6 "Sunita 20 Delhi 30000 | m to update the table and increase salary of each Let's execute the following prograi the number customer by 5000. Here, SQL%ROWCOUNT attribute is used to determine of rows affected:Create procedure: 1, DECLARE 2 total_rows number(2); 3. BEGIN 4, UPDATE customers 5, _ SET salary = salary + 5000; 6. IF sql%notfound THEN f dbms_output put line('no customers updated’); ELSIF sql96found THEN 9, total_rows := sq[%rowcount; 10. _ dbms output put line( total_rows || ' customers updated ') 11. ENDIF; 12. END; 13./ Outp s updated cedure successfully completed. SQL pro gain more control over the context area 1s are defined by the programmers to mn of the PL/SQL block. It is created on a id be defined in the declaration section irs more than one row. The Explicit curso These cursors shou SELECT statement which retut Following is the syntax to create an explicit cursor: Syntax of explicit cursor Following is the syntax to create an explicit cursor: CURSOR cursor_name IS select_statementi; Steps: You must follow these steps while working with an explicit cursor. the memory. 4. Declare the cursor to initial @ scanned with OKEN Scanner2. Open the cursor to allocate memory, 3. Fetch the cursor to retrieve data, 4. Close the cursor to release allocated memory, 1) Declare the cursor: It defines the cursor with a name and the associated T statement. Syntax for explicit cursor decleration CURSOR name IS SELECT statement: 2) Open the cursor: It is used to allocate memory for the cursor and make it easy to fetch the rows returned by the SQL statements into it. Syntax for cursor open: OPEN cursor_name; 3) Fetch the cursor: It is used to access one row at a time. You can fetch rows from the above-opened cursor as follows: Syntax for cursor fetch: FETCH cursor_name INTO variable list; 4) Close the cursor: It is used to release the allocated memory. The following syntax is used to close the above-opened cursors. Syntax for cursor close: Close cursor_name; @ scanned with OKEN ScannerPL/SQL Explicit Cursor Example Explicit cursors are defined by programmers to gain more control over the context area. It is defined in the declaration section of the PL/SQL block. It is created on a SELECT statement which returns more than one row. Let's take an example to demonstrate the use of explicit cursor. In this example, we are using the already created CUSTOMERS table. Create procedure: Execute the following program to retrieve the customer name and address 1. DECLARE 2. cid customersid%type: 3, _cname customers.name%type; 4, caddr customers.address%typer 5, CURSOR ccustomers is 6. SELECT id, name, address FROM customers: 7. BEGIN 8. OPEN c_customers; 9, LOOP 40, FETCH c_customers into c_id, c_name, c_addr, 41. EXIT WHEN c_customers%notfound: 12, dbms_output-put_line(< i c.name |) [| addr): 43. END LOOP; 44, CLOSE c_customers; 15. END; 16./ @ scanned with OKEN Scanner