plsql full notes1
TRANSCRIPT
-
8/12/2019 Plsql Full Notes1
1/136
Oracle/PLSQL: Data Types
The following is a list of datatypes available in Oracle.
Character Datatypes
Data Type
Syntax
Oracle 9i Oracle 10g Oracle 11g Explanation
(i applica!le"
char(size) Maximum size of 2
bytes.
Maximum size of 2
bytes.
Maximum size of 2
bytes.
!here sizeis the number of characters to store. "ixed#length strings.
$pace padded.
nchar(size) Maximum size of 2
bytes.
Maximum size of 2
bytes.
Maximum size of 2
bytes.
!here sizeis the number of characters to store. "ixed#length %&$ string
$pace padded.
nvarchar2(size) Maximum size of '
bytes.
Maximum size of '
bytes.
Maximum size of '
bytes.
!here sizeis the number of characters to store. ariable#length %&$
string.
varchar2(size) Maximum size of '
bytes.
Maximum size of '
bytes.
Maximum size of '
bytes.
!here sizeis the number of characters to store. ariable#length string.
long Maximum size of 2*. Maximum size of 2*. Maximum size of 2*. ariable# length str ings. (bac+ward compat ible)
raw Maximum size of 2
bytes.
Maximum size of 2
bytes.
Maximum size of 2
bytes.
ariable#length binary strings
long raw Maximum size of 2*. Maximum size of 2*. Maximum size of 2*. ariable#length binary strings. (bac+ward compatible)
#$%eric Datatypes
Data Type
Syntax
Oracle 9i Oracle 10g Oracle 11g Explanation
(i applica!le"
-
8/12/2019 Plsql Full Notes1
2/136
number(p,s) -recision can range from
to /0.
$cale can range from #0'
to 21.
-recision can range from
to /0.
$cale can range from #0'
to 21.
-recision can range from
to /0.
$cale can range from #0'
to 21.
!herepis the precision and sis the scale.
"or example, number(1,2) is a number that has digits before the decimal
and 2 digits after the decimal.
numeric(p,s) -recision can range from
to /0.
-recision can range from
to /0.
-recision can range from
to /0.
!herepis the precision and sis the scale.
"or example, numeric(1,2) is a number that has digits before the decimal
and 2 digits after the decimal.
float
dec(p,s) -recision can range from
to /0.
-recision can range from
to /0.
-recision can range from
to /0.
!herepis the precision and sis the scale.
"or example, dec(/,) is a number that has 2 digits before the decimal and
digit after the decimal.
decimal(p,s) -recision can range from
to /0.
-recision can range from
to /0.
-recision can range from
to /0.
!herepis the precision and sis the scale.
"or example, decimal(/,) is a number that has 2 digits before the decimal
and digit after the decimal.
integer
int
smallint
real
double
precision
-
8/12/2019 Plsql Full Notes1
3/136
Date/Ti%e Datatypes
Data Type
Syntax
Oracle 9i Oracle 10g Oracle 11g Explanation
(i applica!le"
date 3 date between 4an , '12 *5
and 6ec /, 7777 36.
3 date between 4an , '12 *5
and 6ec /, 7777 36.
3 date between 4an , '12 *5
and 6ec /, 7777 36.
timestamp (fractional
seconds precision)
fractional seconds precision
must be a number between
and 7. (default is 8)
fractional seconds precision
must be a number between
and 7. (default is 8)
fractional seconds precision
must be a number between
and 7. (default is 8)
9ncludes year, month, day, hour, minute,
and seconds.
"or example:
timestamp(8)
timestamp (fractional
seconds precision) with
time zone
fractional seconds precision
must be a number between
and 7. (default is 8)
fractional seconds precision
must be a number between
and 7. (default is 8)
fractional seconds precision
must be a number between
and 7. (default is 8)
9ncludes year, month, day, hour, minute,
and seconds; with a time zone
displacement value.
"or example:
timestamp() with time zone
timestamp (fractional
seconds precision) with
local time zone
fractional seconds precision
must be a number between
and 7. (default is 8)
fractional seconds precision
must be a number between
and 7. (default is 8)
fractional seconds precision
must be a number between
and 7. (default is 8)
9ncludes year, month, day, hour, minute,
and seconds; with a time zone expressed
as the session time zone.
"or example:
timestamp(') with local time zone
interval year
(year precision)
to month
year precisionis the number of
digits in the year. (default is 2)
year precisionis the number of
digits in the year. (default is 2)
year precisionis the number of
digits in the year. (default is 2)
Time period stored in years and months.
"or example:
interval year(') to month
interval day day precisionmust be a day precisionmust be a day precisionmust be a Time period stored in days, hours, minutes,
-
8/12/2019 Plsql Full Notes1
4/136
(day precision)
to second (fractional
seconds precision)
number between and 7.
(default is 2)
fractional seconds precision
must be a number between
and 7. (default is 8)
number between and 7.
(default is 2)
fractional seconds precision
must be a number between
and 7. (default is 8)
number between and 7.
(default is 2)
fractional seconds precision
must be a number between
and 7. (default is 8)
and seconds.
"or example:
interval day(2) to second(8)
Large O!&ect (LO'" Datatypes
Data
Type
Syntax
Oracle 9i Oracle 10g Oracle 11g Explanation
(i applica!le"
bfile Maximum file size of'*.
Maximum file size of 2/2
# bytes. Maximum file size of 28'
# bytes. "ile locators that point to a binary file onthe server file system (outside the
database).
blob $tore up to '* of
binary data.
$tore up to (' gigabytes #) < (the value of the
5=>%? parameter of &O* storage).
$tore up to (' gigabytes #) < (the value of the
5=>%? parameter of &O* storage).
$tores unstructured binary large ob@ects.
clob $tore up to '* of
character data.
$tore up to (' gigabytes #) < (the value of the
5=>%? parameter of &O* storage) of
character data.
$tore up to (' gigabytes #) < (the value of the
5=>%? parameter of &O* storage) of
character data.
$tores single#byte and multi#byte
character data.
nclob $tore up to '* of
character text data.
$tore up to (' gigabytes #) < (the value of the
5=>%? parameter of &O* storage) of
character text data.
$tore up to (' gigabytes #) < (the value of the
5=>%? parameter of &O* storage) of
character text data.
$tores unicode data.
-
8/12/2019 Plsql Full Notes1
5/136
o)i* Datatypes
Data Type
Syntax
Oracle 9i Oracle 10g Oracle 11g Explanation
(i applica!le"
rowid The format of the rowid is:
*******.AAAA."""""
!here ******* is the bloc+ in the
database file;
AAAA is the row in the bloc+;
""""" is the database file.
The format of the rowid is:
*******.AAAA."""""
!here ******* is the bloc+ in the
database file;
AAAA is the row in the bloc+;
""""" is the database file.
The format of the rowid is:
*******.AAAA."""""
!here ******* is the bloc+ in the
database file;
AAAA is the row in the bloc+;
""""" is the database file.
"ixed#length binary data. Bvery record in the
database has a physical address or ro)i*.
urowid(size) >niversal rowid.
!here sizeis optional.5opyright C 2/#27 Tech on the %et. 3ll rights reserved.
Oracle/PLSQL: Declaring +aria!les
The syntax for declaring variables is:
,aria!le-na%e .CO#ST#T *atatype .#OT #LL .:2 3 DE4LT initial-,al$e
4or exa%ple:
6eclaring a variable:
-
8/12/2019 Plsql Full Notes1
6/136
LDescription ,archar5(60"7
6eclaring a constant:
LTotal CO#ST#T n$%eric(81" :2 8;964 L,al$e >S #OT #LL then
-
8/12/2019 Plsql Full Notes1
7/136
select ? ro% s$ppliers)here s$pplier-na%e >S #OT #LL7
This will return all records from the supplierstable where the supplier_namedoes notcontain a null value.
Oracle/PLSQL: >S #LL
9n -&$D& to chec+ if a value is null, you must use the E9$ %>&&E syntax.
"or example,
>4 L,al$e >S #LL then
S #LL7
This will return all records from the supplierstable where the supplier_namecontains a null value.
Oracle/PLSQL Topics: Loops an* Con*itional State%ents
>4@TAE#@ELSE State%ent
http://www.techonthenet.com/oracle/loops/if_then.phphttp://www.techonthenet.com/oracle/loops/if_then.php -
8/12/2019 Plsql Full Notes1
8/136
Case State%ent
BOTO State%ent
Loop State%ent
4O Loop
CSO 4O Loop
hile Loop
epeat ntil Loop
Exit State%ent
Oracle/PLSQL: >4@TAE#@ELSE State%ent
There are three different syntaxes for these types of statements.
Syntax 1: >4@TAE#
>4 con*ition TAE#
-
8/12/2019 Plsql Full Notes1
9/136
>4 con*ition TAE#
-
8/12/2019 Plsql Full Notes1
10/136
open c17etch c1 into %onthly-,al$e7close c17
>4 %onthly-,al$e G2 6000 TAE#
>Le,el :2 =Lo) >nco%e=7
ELS>4 %onthly-,al$e H 6000 an* %onthly-,al$e G2 I000 TAE# >Le,el :2 =,g >nco%e=7
ELS>4 %onthly-,al$e H I000 an* %onthly-,al$e G2 1J000 TAE# >Le,el :2 =Ko*erate >nco%e=7
ELSE >Le,el :2 =Aigh >nco%e=7
E#D >47
ET# >Le,el7
E#D7
9n this example, weGve created a function called 9ncome&evel. 9t has one parameter called name_inand it returns a varchar2. The function will return the income level based onthe employeeGs name.
Oracle/PLSQL: Case State%ent
$tarting in Oracle 7i, you can use the casestatement within an $D& statement. 9t has the functionality of an 9"#T=B%#B&$B statement.
The syntax for the casestatement is:
CSE . expression AE# con*ition-1 TAE# res$lt-1 AE# con*ition-5 TAE# res$lt-5
-
8/12/2019 Plsql Full Notes1
11/136
-
8/12/2019 Plsql Full Notes1
12/136
select ta!le-na%eCSE o)ner AE# =SS= TAE# =The o)ner is SS= AE# =SSTEK= TAE# =The o)ner is SSTEK= ELSE =The o)ner is another ,al$e=
E#Dro% all-ta!les7
Or you could write the $D& statement using the casestatement li+e this: (omits the expressionclause)
select ta!le-na%eCSE AE# o)ner2=SS= TAE# =The o)ner is SS= AE# o)ner2=SSTEK= TAE# =The o)ner is SSTEK= ELSE =The o)ner is another ,al$e=E#Dro% all-ta!les7
The above two casestatements are eIuivalent to the following 9"#T=B%#B&$B statement:
>4 o)ner 2 =SS= TAE# res$lt :2 =The o)ner is SS=7
ELS>4 o)ner 2 =SSTEK= TAE# res$lt :2 =The o)ner is SSTEK==7
ELSE res$lt :2 =The o)ner is another ,al$e=7
E#D >47
The casestatement will compare each owner value, one by one.
-
8/12/2019 Plsql Full Notes1
13/136
One thing to note is that the B&$B clause within the casestatement is optional. Fou could have omitted it. &etGs ta+e a loo+ at the $D& statement above with the B&$B clauseomitted.
Four $D& statement would loo+ as follows:
select ta!le-na%eCSE o)ner AE# =SS= TAE# =The o)ner is SS= AE# =SSTEK= TAE# =The o)ner is SSTEK=E#Dro% all-ta!les7
!ith the B&$B clause omitted, if no condition was found to be true, the casestatement would return %>&&.
4or Exa%ple:
=ere is an example that demonstrates how to use the casestatement to compare different conditions:
selectCSE AE# a G ! TAE# =hello= AE# * G e TAE# =goo*!ye=E#Dro% s$ppliers7
4reM$ently sNe* Q$estions
Q$estion: 5an you create a casestatement that evaluates two different fieldsJ 9 want to return a value based on the combinations in two different fields.
ns)er: Fes, below is an example of a case statement that evaluates two different fields.
-
8/12/2019 Plsql Full Notes1
14/136
select s$pplier-i*CSE AE# s$pplier-na%e 2 =>'K= an* s$pplier-type 2 =Aar*)are= TAE# =#orth oice= AE# s$pplier-na%e 2 =>'K= an* s$pplier-type 2 =Sot)are= TAE# =So$th oice=E#D
ro% s$ppliers7
$o if supplierHname field is IBMand the supplierHtype field is Hardware, then the casestatement will return North office. 9f the supplierHname field is IBMand the supplierHtypeis Software, the casestatement will return South office.
Oracle/PLSQL: BOTO State%ent
The OTO statement causes the code to branch to the label after the OTO statement.
"or example:
BOTO la!el-na%e7
Then later in the code, you would place your label and code associated with that label.
La!el-na%e: statementsF
Oracle/PLSQL: Loop State%ent
The syntax for the &OO- statement is:
LOOP
-
8/12/2019 Plsql Full Notes1
15/136
Fou would use a &OO- statement when you are not sure how many times you want the loop body to execute and you want the loop body to execute at least once.
The &OO- statement is terminated when it encounters either an BK9T statement or when it encounters an BK9T !=B% statement that evaluated to TA>B.
&etGs ta+e a loo+ at an example:
LOOP %onthly-,al$e :2 *aily-,al$e ? 17 E>T AE# %onthly-,al$e H 60007E#D LOOP7
9n this example, the &OO- would terminate when the monthlyHvalue exceeded '.
Oracle/PLSQL: 4O Loop
The syntax for the "OA &oop is:
4O loop-co$nter ># .E+ESE lo)est-n$%!er
-
8/12/2019 Plsql Full Notes1
16/136
The "OA &oop can also loop in reverse. "or example:
4O Lcntr ># E+ESE 1
-
8/12/2019 Plsql Full Notes1
17/136
'EB>#
total-,al :2 07
4O e%ployee-rec in c1LOOP total-,al :2 total-,al e%ployee-recLE con*itionLOOP
-
8/12/2019 Plsql Full Notes1
18/136
9n this example, the !=9&B &oop would terminate once the monthlyHvalue exceeded '.
Oracle/PLSQL: epeat ntil Loop
Oracle doesnGt have a Aepeat >ntil loop, but you can emulate one. The syntax for emulating a AB-B3T >%T9& &oop is:
LOOP %T9& &oop when you do not +now how many times you want the loop body to execute. The AB-B3T >%T9& &oop would terminatewhen a certain condition was met.
&etGs ta+e a loo+ at an example:
LOOP %onthly-,al$e :2 *aily-,al$e ? 17 E>T AE# %onthly-,al$e H 60007E#D LOOP7
9n this example, the &OO- would repeat until the monthlyHvalue exceeded '.
Oracle/PLSQL: Exit State%ent
The syntax for the BK9T statement is:
E>T .AE# !oolean-con*ition7
The BK9T statement is most commonly used to terminate &OO- statements.
&etGs ta+e a loo+ at an example:
-
8/12/2019 Plsql Full Notes1
19/136
LOOP %onthly-,al$e :2 *aily-,al$e ? 17 E>T AE# %onthly-,al$e H 60007E#D LOOP7
9n this example, the &OO- would terminate when the monthlyHvalue exceeded '.
Oracle/PLSQL: SeM$ences ($ton$%!er"
9n Oracle, you can create an autonumber field by using seIuences. 3 seIuence is an ob@ect in Oracle that is used to generate a number seIuence. This can be useful whenyou need to create a uniIue number to act as a primary +ey.
The syntax for a seIuence is:
CETE SEQE#CE seM$ence-na%e K>#+LE ,al$e
K+LE ,al$e STT >TA ,al$e >#CEKE#T ' ,al$e CCAE ,al$e7
"or example:
CETE SEQE#CE s$pplier-seM K>#+LE 1 K+LE 999999999999999999999999999 STT >TA 1 >#CEKE#T ' 1 CCAE 507
This would create a seIuence ob@ect called supplier_se$. The first seIuence number that it would use is and each subseIuent number would increment by (ie: 2,/,',...L. 9twill cache up to 2 values for performance.
9f you omit the K+LE option, your seIuence will automatically default to:
-
8/12/2019 Plsql Full Notes1
20/136
-
8/12/2019 Plsql Full Notes1
21/136
CETE SEQE#CE s$pplier-seM K>#+LE 1 STT >TA 1 >#CEKE#T ' 1 CCAE 507
Or you could create the same seIuence with the nocache option:
CETE SEQE#CE s$pplier-seM K>#+LE 1 STT >TA 1 >#CEKE#T ' 1 #OCCAE7
ns)er: !ith respect to a seIuence, the cacheoption specifies how many seIuence values will be stored in memory for faster access.
The downside of creating a seIuence with a cache is that if a system failure occurs, all cached seIuence values that have notbe used, will be ElostE. This results in a EgapE inthe assigned seIuence values. !hen the system comes bac+ up, Oracle will cache new numbers from where it left off in the seIuence, ignoring the so called ElostE seIuencevalues.
%ote: To recover the lost seIuence values, you can always execute an 3&TBA $BD>B%5B command to reset the counter to the correct value.
Nocachemeans that none of the seIuence values are stored in memory. This option may sacrifice some performance, however, you should not encounter a gap in theassigned seIuence values.
Q$estion: =ow do we set the &3$T3&>B value in an Oracle $eIuenceJ
ns)er: Fou can change the &3$T3&>B for an Oracle seIuence, by executing an 3&TBA $BD>B%5B command.
-
8/12/2019 Plsql Full Notes1
22/136
"or example, if the last value used by the Oracle seIuence was and you would li+e to reset the seIuence to serve 22 as the next value. Fou would execute the followingcommands.
alter seM$ence seM-na%eincre%ent !y 1567
select seM-na%e
-
8/12/2019 Plsql Full Notes1
23/136
Oracle/PLSQL: Co%%it
The syntax for the 5OMM9T statement is:
COKK>T .O .COKKE#T text7
The 5ommit statement commits all changes for the current session. Once a commit is issued, other users will be able to see your changes.
Oracle/PLSQL: oll!acN
The syntax for the AO&&*35? statement is:
OLL'C .O .TO .S+EPO>#T sa,epoint-na%e7
The Aollbac+ statement undoes all changes for the current session up to the savepoint specified. 9f no savepoint is specified, then all changes are undone.
Oracle/PLSQL: Set Transaction
There are three transaction control functions. These are:
1< SET T#SCT>O# ED O#L7
5< SET T#SCT>O# >SOLT>O# LE+EL SE>L>R'LE7
< SET T#SCT>O# SE OLL'C SEBKE#T na%e7
Oracle/PLSQL: LocN Ta!le
The syntax for a &oc+ table is:
-
8/12/2019 Plsql Full Notes1
24/136
LOC T'LE ta!les ># locN-%o*e KODE .#O>T7
Ta!lesis a comma#delimited list of tables.
LocN-%o*eis one of:
!"% SH&!'!"% '(CLSI)'SH&!' *+&,'SH&!'SH&!' !"% '(CLSI)''(CLSI)'-
#oaitspecifies that the database should not wait for a loc+ to be released.
Oracle/PLSQL Topics: C$rsors
3 c$rsoris a mechanism by which you can assign a name to a Eselect statementE and manipulate the information within that $D& statement.
!eGve categorized cursors into the following topics:
Declare a C$rsor
OPE# State%ent
4ETCA State%ent
CLOSE State%ent
C$rsor ttri!$tes (4O#D #OT4O#D etc"
http://www.techonthenet.com/oracle/cursors/declare.phphttp://www.techonthenet.com/oracle/cursors/open.phphttp://www.techonthenet.com/oracle/cursors/fetch.phphttp://www.techonthenet.com/oracle/cursors/close.phphttp://www.techonthenet.com/oracle/cursors/attributes.phphttp://www.techonthenet.com/oracle/cursors/declare.phphttp://www.techonthenet.com/oracle/cursors/open.phphttp://www.techonthenet.com/oracle/cursors/fetch.phphttp://www.techonthenet.com/oracle/cursors/close.phphttp://www.techonthenet.com/oracle/cursors/attributes.php -
8/12/2019 Plsql Full Notes1
25/136
SELECT 4O PDTE State%ent
AEE CE#T O4 State%ent
5ursor Bxamples:
Proce*$re that o$tp$ts a *yna%ic PLSQL c$rsor
C$rsor )ithin a c$rsor
C$rsor )ith ,aria!le in an ># CLSE
Oracle/PLSQL: Declare a C$rsor
3 cursor is a $B&B5T statement that is defined within the declarationsection of your -&$D& code. !eGll ta+e a loo+ at three different syntaxes for cursors.
C$rsor )itho$t para%eters (si%plest"
The basic syntax for a cursor without parameters is:
CSO c$rsor-na%e>S SELECT-state%ent7
"or example, you could define a cursor called c as below.
http://www.techonthenet.com/oracle/cursors/for_update.phphttp://www.techonthenet.com/oracle/cursors/current_of.phphttp://www.techonthenet.com/oracle/questions/cursor1.phphttp://www.techonthenet.com/oracle/questions/cursor2.phphttp://www.techonthenet.com/oracle/questions/cursor3.phphttp://www.techonthenet.com/oracle/cursors/for_update.phphttp://www.techonthenet.com/oracle/cursors/current_of.phphttp://www.techonthenet.com/oracle/questions/cursor1.phphttp://www.techonthenet.com/oracle/questions/cursor2.phphttp://www.techonthenet.com/oracle/questions/cursor3.php -
8/12/2019 Plsql Full Notes1
26/136
CSO c1>S SELECT co$rse-n$%!er ro% co$rses-t!l )here co$rse-na%e 2 na%e-in7
The result set of this cursor is all courseHnumbers whose courseHname matches the variable called nameHin.
*elow is a function that uses this cursor.
CETE O EPLCE 4$nction 4in*Co$rse ( na%e-in ># ,archar5 " ET# n$%!er>S cn$%!er n$%!er7
CSO c1 >S SELECT co$rse-n$%!er ro% co$rses-t!l )here co$rse-na%e 2 na%e-in7
'EB>#
open c17etch c1 into cn$%!er7
i c1noto$n* then cn$%!er :2 99997en* i7
close c17
ET# cn$%!er7
-
8/12/2019 Plsql Full Notes1
27/136
E#D7
C$rsor )ith para%eters
The basic syntax for a cursor with parameters is:
CSO c$rsor-na%e (para%eter-list">S SELECT-state%ent7
"or example, you could define a cursor called c2 as below.
CSO c5 (s$!&ect-i*-in ># ,archar5"
>S SELECT co$rse-n$%!er ro% co$rses-t!l )here s$!&ect-i* 2 s$!&ect-i*-in7
The result set of this cursor is all courseHnumbers whose sub@ectHid matches the sub@ectHid passed to the cursor via the parameter.
C$rsor )ith ret$rn cla$se
The basic syntax for a cursor with a return clause is:
CSO c$rsor-na%eET# iel*OTPE>S SELECT-state%ent7
-
8/12/2019 Plsql Full Notes1
28/136
"or example, you could define a cursor called c/ as below.
CSO cET# co$rses-t!lOTPE>S
SELECT ? ro% co$rses-t!l )here s$!&ect 2 =Kathe%atics=7
The result set of this cursor is all columns from the courseHtbl where the sub@ect is Mathematics.
Oracle/PLSQL: OPE# State%ent
Once youGve declared your cursor, the next step is to open the cursor.
The basic syntax to O-B% the cursor is:
OPE# c$rsor-na%e7
"or example, you could open a cursor called c with the following command:
OPE# c17
*elow is a function that demonstrates how to use the O-B% statement:
CETE O EPLCE 4$nction 4in*Co$rse ( na%e-in ># ,archar5 " ET# n$%!er>S cn$%!er n$%!er7
-
8/12/2019 Plsql Full Notes1
29/136
CSO c1 >S SELECT co$rse-n$%!er ro% co$rses-t!l )here co$rse-na%e 2 na%e-in7
'EB>#
open c17etch c1 into cn$%!er7
i c1noto$n* then cn$%!er :2 99997en* i7
close c17
ET# cn$%!er7
E#D7
Oracle/PLSQL: 4ETCA State%ent
The purpose of using a cursor, in most cases, is to retrieve the rows from your cursor so that some type of operation can be performed on the data. 3fter declaring and openingyour cursor, the next step is to "BT5= the rows from your cursor.
The basic syntax for a "BT5= statement is:
4ETCA c$rsor-na%e >#TO Glist o ,aria!lesH7
"or example, you could have a cursor defined as:
-
8/12/2019 Plsql Full Notes1
30/136
CSO c1>S SELECT co$rse-n$%!er ro% co$rses-t!l )here co$rse-na%e 2 na%e-in7
The command that would be used to fetch the data from this cursor is:
4ETCA c1 into cn$%!er7
This would fetch the first courseHnumber into the variable called cnumber;
*elow is a function that demonstrates how to use the "BT5= statement.
CETE O EPLCE 4$nction 4in*Co$rse ( na%e-in ># ,archar5 "
ET# n$%!er>S cn$%!er n$%!er7
CSO c1 >S SELECT co$rse-n$%!er ro% co$rses-t!l )here co$rse-na%e 2 na%e-in7
'EB>#
open c17
etch c1 into cn$%!er7
i c1noto$n* then cn$%!er :2 99997en* i7
-
8/12/2019 Plsql Full Notes1
31/136
close c17
ET# cn$%!er7
E#D7
Oracle/PLSQL: CLOSE State%ent
The final step of wor+ing with cursors is to close the cursor once you have finished using it.
The basic syntax to 5&O$B the cursor is:
CLOSE c$rsor-na%e7
"or example, you could close a cursor called c with the following command:
CLOSE c17
*elow is a function that demonstrates how to use the 5&O$B statement:
CETE O EPLCE 4$nction 4in*Co$rse ( na%e-in ># ,archar5 " ET# n$%!er
>S cn$%!er n$%!er7
CSO c1 >S
-
8/12/2019 Plsql Full Notes1
32/136
SELECT co$rse-n$%!er ro% co$rses-t!l )here co$rse-na%e 2 na%e-in7
'EB>#
open c17etch c1 into cn$%!er7
i c1noto$n* then cn$%!er :2 99997en* i7
close c17
ET# cn$%!er7
E#D7
Oracle/PLSQL: C$rsor ttri!$tes
!hile dealing with cursors, you may need to determine the status of your cursor. The following is a list of the cursor attributes that you can use.
ttri!$te Explanation
9$O-B% # Aeturns TA>B if the cursor is open, "3&$B if the cursor is closed.
"O>%6 # Aeturns 9%3&96H5>A$OA if cursor is declared, but not open; or if cursor has been closed.
# Aeturns %>&& if cursor is open, but fetch has not been executed.
# Aeturns TA>B if a successful fetch has been executed.
-
8/12/2019 Plsql Full Notes1
33/136
# Aeturns "3&$B if no row was returned.
%OT"O>%6 # Aeturns 9%3&96H5>A$OA if cursor is declared, but not open; or if cursor has been closed.
# Aeturn %>&& if cursor is open, but fetch has not been executed.
# Aeturns "3&$B if a successful fetch has been executed.
# Aeturns TA>B if no row was returned.
AO!5O>%T # Aeturns 9%3&96H5>A$OA if cursor is declared, but not open; or if cursor has been closed.
# Aeturns the number of rows fetched.
# The AO!5O>%T attribute doesnGt give the real row count until you have iterated through
the entire cursor. 9n other words, you shouldnGt rely on this attribute to tell you how many rowsare in a cursor after it is opened.
*elow is an example of how you might use the %OT"O>%6 attribute.
CETE O EPLCE 4$nction 4in*Co$rse ( na%e-in ># ,archar5 " ET# n$%!er>S cn$%!er n$%!er7
CSO c1 >S SELECT co$rse-n$%!er ro% co$rses-t!l )here co$rse-na%e 2 na%e-in7
-
8/12/2019 Plsql Full Notes1
34/136
'EB>#
open c17etch c1 into cn$%!er7
i c1noto$n* then cn$%!er :2 99997en* i7
close c17
ET# cn$%!er7
E#D7
Oracle/PLSQL: SELECT 4O PDTE State%ent
The Select 4or p*atestatement allows you to loc+ the records in the cursor result set. Fou are not reIuired to ma+e changes to the records in order to use this statement.The record loc+s are released when the next commit or rollbac+ statement is issued.
The syntax for the Select 4or p*ateis:
CSO c$rsor-na%e>S select-state%ent 4O PDTE .o col$%n-list .#O>T7
"or example, you could use the Select 4or p*atestatement as follows:
CSO c1>S
-
8/12/2019 Plsql Full Notes1
35/136
SELECT co$rse-n$%!er instr$ctor ro% co$rses-t!l 4O PDTE o instr$ctor7
9f you plan on updating or deleting records that have been referenced by a Select 4or p*atestatement, you can use thehere C$rrent Ostatement.
Oracle/PLSQL: AEE CE#T O4 State%ent
9f you plan on updating or deleting records that have been referenced by a Select 4or p*atestatement, you can use the here C$rrent Ostatement.
The syntax for the here C$rrent Ostatement is either:
PDTE ta!le-na%e SET set-cla$se
AEE CE#T O4 c$rsor-na%e7
OA
DELETE 4OK ta!le-na%eAEE CE#T O4 c$rsor-na%e7
The !here 5urrent Of statement allows you to update or delete the record that was last fetched by the cursor.
p*ating $sing the AEE CE#T O4 State%ent
=ere is an example where we are updating records using the here C$rrent O$tatement:
http://www.techonthenet.com/oracle/cursors/current_of.phphttp://www.techonthenet.com/oracle/cursors/current_of.phphttp://www.techonthenet.com/oracle/cursors/current_of.phphttp://www.techonthenet.com/oracle/cursors/for_update.phphttp://www.techonthenet.com/oracle/cursors/for_update.phphttp://www.techonthenet.com/oracle/cursors/current_of.phphttp://www.techonthenet.com/oracle/cursors/for_update.php -
8/12/2019 Plsql Full Notes1
36/136
CETE O EPLCE 4$nction 4in*Co$rse ( na%e-in ># ,archar5 " ET# n$%!er>S cn$%!er n$%!er7
CSO c1 >S SELECT co$rse-n$%!er ro% co$rses-t!l )here co$rse-na%e 2 na%e-in 4O PDTE o instr$ctor7
'EB>#
open c17etch c1 into cn$%!er7
i c1noto$n* then
cn$%!er :2 99997
else PDTE co$rses-t!l SET instr$ctor 2 =SK>TA= AEE CE#T O4 c17
COKK>T7
en* i7
close c17
ET# cn$%!er7
E#D7
-
8/12/2019 Plsql Full Notes1
37/136
Deleting $sing the AEE CE#T O4 State%ent
=ere is an example where we are deleting records using the here C$rrent O$tatement:
CETE O EPLCE 4$nction 4in*Co$rse
( na%e-in ># ,archar5 " ET# n$%!er>S cn$%!er n$%!er7
CSO c1 >S SELECT co$rse-n$%!er ro% co$rses-t!l )here co$rse-na%e 2 na%e-in 4O PDTE o instr$ctor7
'EB>#
open c17etch c1 into cn$%!er7
i c1noto$n* then cn$%!er :2 99997
else DELETE 4OK co$rses-t!l AEE CE#T O4 c17
COKK>T7
en* i7
close c17
ET# cn$%!er7
-
8/12/2019 Plsql Full Notes1
38/136
E#D7
Oracle/PLSQL: Proce*$re that o$tp$ts a *yna%ic PLSQL c$rsor
Q$estion: 9n Oracle, 9 have a table called EwineE and a stored procedure that outputs a cursor based on the EwineE table.
9Gve created an =TM& "orm where the user can enter any combination of three values to retrieve results from the EwineE table. My problem is that 9 need a general EselectEstatement that will wor+ no matter what value(s), the user enters.
Exa%ple:
parameterHN E5hiantiEparameterH2N EEparameterH/N wasnGt entered by the user but 9 have to use in the select statement. 3nd this is my problem. =ow to initialize this parameter to get all rows for column/J
$B&B5T < "AOM wine
!=BAB column N parameterH3%6 column2 N parameterH23%6 column/ N parameterH/;.
The output of my stored procedure must be a cursor.
ns)er: To solve your problem, you will need to output a dynamic -&$D& cursor in Oracle.
&etGs ta+e a loo+ at how we can do this. !eGve divided this process into / steps.
Step 1 @ Ta!le Deinition
"irst, we need a table created in Oracle called EwineE. *elow is the create statement for the wine table.
-
8/12/2019 Plsql Full Notes1
39/136
create ta!le )ine( col1 ,archar5(60" col5 ,archar5(60" col ,archar5(60""7
!eGve made this table definition very simple, for demonstration purposes.
Step 5 @ Create pacNage
%ext, weGve created a pac+age called Ewinep+gE that contains our cursor definition. This needs to be done so that we can use a cursor as an o$tp$tparameter in our storedprocedure.
create or replace PCBE )inepNg
>S /? Deine the E4 CSO type< ?/ TPE )ine-type >S E4 CSO ET# )ineOTPE7E#D )inepNg7
This cursor will accept all fields from the EwineE table.
Step @ Create store* proce*$re
Our final step is to create a stored procedure to return the cursor. 9t accepts three parameters (entered by the user on the =TM& "orm) and returns a cursor (c) of type
EwineHtypeE which was declared in $tep 2.
The procedure will determine the appropriate cursor to return, based on the value(s) that have been entered by the user (input parameters).
-
8/12/2019 Plsql Full Notes1
40/136
create or replace proce*$re in*-)ine5 (col1-in in ,archar5 col5-in in ,archar5 col-in in ,archar5 c1 o$t )inepNg
-
8/12/2019 Plsql Full Notes1
41/136
/? col5 an* col )here entere* ?/ ELS>4 (length(col1-in" 2 0" an* (length(col5-in" H 0" an* (length(col-in" H 0" TAE# OPE# c1 4O select ? ro% )ine
)here )ine
-
8/12/2019 Plsql Full Notes1
42/136
Q$estion: 9n -$D&, 9 want to declare a cursor within cursor. The second cursor should use a value from the first cursor in the Ewhere clauseE. =ow can 9 do thisJ
ns)er: *elow is an example of how to declare a cursor within a cursor.
9n this example, we have a cursor called .et_ta/lesthat retrieves the owner and tableHname values. These values are then used in a second cursor called .et_columns.
create or replace proce*$re KLT>PLE-CSOS-POC is ,-o)ner ,archar5(60"7 ,-ta!le-na%e ,archar5(60"7 ,-col$%n-na%e ,archar5(100"7
/? 4irst c$rsor ?/ c$rsor get-ta!les is select *istinct t!l
-
8/12/2019 Plsql Full Notes1
43/136
open get-col$%ns7 loop etch get-col$%ns into ,-col$%n-na%e7
en* loop7
close get-col$%ns7
en* loop7 close get-ta!les7
ECEPT>O#AE# OTAES TAE# raise-application-error(@50001=n error )as enco$ntere* @ =33SQLCODE33= @EO@ =33SQLEK"7en* KLT>PLE-CSOS-POC7
The tric+ to declaring a cursor within a cursor is that you need to continue to openand closethe second cursor each time a new record is retrieved from the first cursor. That
way, the second cursor will use the new variable values from the first cursor.
Oracle/PLSQL: C$rsor )ith ,aria!le in an ># CLSE
Q$estion: 9Gm trying to use a variable in an 9% 5&3>$B.
ss$%ptions U *eclarations:
. AefHcursor is of type AB" 5>A$OA declared in -ac+age
2. 9 will to pass a comma separated %umbers as a string
/. This should be used in the Iuery in the 9% 5lause'. Bxecute the Duery and Aeturn the Output as AB" 5ursor
$omething similar to the following:
-
8/12/2019 Plsql Full Notes1
44/136
-
8/12/2019 Plsql Full Notes1
45/136
,1 :2 =1=7 ,5 :2 =5=7 , :2 ==7
Open o$t-c$rsor
4or Select ? ro% Ta!le-na%e )here col$%n-na%e in (,1 ,5 ,"7
et$rn o$t-c$rsor7
En*7
Option 5
Fou could try storing your values in a table. Then use a sub#select to retrieve the values."or example:
Create or eplace 4$nction $nc-na%e et$rn PacNage#a%e
-
8/12/2019 Plsql Full Notes1
46/136
Oracle/PLSQL Topics: '$ilt@>n 4$nctions ('y Category"
+ie) an lpha!etical Listing o 4$nctions
5haracter $tring "unctions:scii Con,ert Lo)er So$n*exsciiStr Deco%pose Lpa* S$!strChr D$%p Ltri% TranslateCo%pose >nitcap eplace Tri%Concat >nstr pa* pperConcat )ith 33 Length tri% +SiVe
5onversion "unctions:'in-To-#$% #$%ToDS>nter,al To-Date To-#$%!erCast #$%ToK>nter,al To-DS>nter,al To-Single-'yteCharToo)i* a)ToAex To-Lo! To-Ti%esta%p4ro%-TV To-Char To-K$lti-'yte To-Ti%esta%p-TVAexToa) To-Clo! To-#Clo! To-K>nter,al
3dvanced "unctions:'4ilena%e Bro$p->D #LL>4 ser
Car*inality Lag #+L serEn,Case State%ent Lea* #+L5 Coalesce L##+L Sys-Context Deco*e ##+L i*
Mathematical "unctions:!s Co,ar-pop Kax SMrtcos Co,ar-sa%p Ke*ian St*De,sin Co$nt Kin S$%tan C$%e-Dist Ko* Tantan5 Dense-anN Po)er Tanh,g Exp anN Tr$nc (n$%!ers"'in-To-#$% Extract e%ain*er Tr$nc (*ates"'itn* 4loor o$n* (n$%!ers" +ar-popCeil Breatest o$n* (*ates" +ar-sa%pCorr Least Sign +ariance
http://www.techonthenet.com/oracle/functions/index_alpha.phphttp://www.techonthenet.com/oracle/functions/ascii.phphttp://www.techonthenet.com/oracle/functions/convert.phphttp://www.techonthenet.com/oracle/functions/lower.phphttp://www.techonthenet.com/oracle/functions/soundex.phphttp://www.techonthenet.com/oracle/functions/asciistr.phphttp://www.techonthenet.com/oracle/functions/decompose.phphttp://www.techonthenet.com/oracle/functions/lpad.phphttp://www.techonthenet.com/oracle/functions/substr.phphttp://www.techonthenet.com/oracle/functions/chr.phphttp://www.techonthenet.com/oracle/functions/dump.phphttp://www.techonthenet.com/oracle/functions/ltrim.phphttp://www.techonthenet.com/oracle/functions/translate.phphttp://www.techonthenet.com/oracle/functions/compose.phphttp://www.techonthenet.com/oracle/functions/initcap.phphttp://www.techonthenet.com/oracle/functions/replace.phphttp://www.techonthenet.com/oracle/functions/trim.phphttp://www.techonthenet.com/oracle/functions/concat.phphttp://www.techonthenet.com/oracle/functions/instr.phphttp://www.techonthenet.com/oracle/functions/rpad.phphttp://www.techonthenet.com/oracle/functions/upper.phphttp://www.techonthenet.com/oracle/functions/concat2.phphttp://www.techonthenet.com/oracle/functions/length.phphttp://www.techonthenet.com/oracle/functions/rtrim.phphttp://www.techonthenet.com/oracle/functions/vsize.phphttp://www.techonthenet.com/oracle/functions/bin_to_num.phphttp://www.techonthenet.com/oracle/functions/numtodsinterval.phphttp://www.techonthenet.com/oracle/functions/to_date.phphttp://www.techonthenet.com/oracle/functions/to_number.phphttp://www.techonthenet.com/oracle/functions/cast.phphttp://www.techonthenet.com/oracle/functions/numtoyminterval.phphttp://www.techonthenet.com/oracle/functions/to_dsinterval.phphttp://www.techonthenet.com/oracle/functions/to_single_byte.phphttp://www.techonthenet.com/oracle/functions/chartorowid.phphttp://www.techonthenet.com/oracle/functions/rawtohex.phphttp://www.techonthenet.com/oracle/functions/to_lob.phphttp://www.techonthenet.com/oracle/functions/to_timestamp.phphttp://www.techonthenet.com/oracle/functions/from_tz.phphttp://www.techonthenet.com/oracle/functions/to_char.phphttp://www.techonthenet.com/oracle/functions/to_multi_byte.phphttp://www.techonthenet.com/oracle/functions/to_timestamp_tz.phphttp://www.techonthenet.com/oracle/functions/hextoraw.phphttp://www.techonthenet.com/oracle/functions/to_clob.phphttp://www.techonthenet.com/oracle/functions/to_nclob.phphttp://www.techonthenet.com/oracle/functions/to_yminterval.phphttp://www.techonthenet.com/oracle/functions/bfilename.phphttp://www.techonthenet.com/oracle/functions/group_id.phphttp://www.techonthenet.com/oracle/functions/nullif.phphttp://www.techonthenet.com/oracle/functions/user.phphttp://www.techonthenet.com/oracle/functions/cardinality.phphttp://www.techonthenet.com/oracle/functions/lag.phphttp://www.techonthenet.com/oracle/functions/nvl.phphttp://www.techonthenet.com/oracle/functions/userenv.phphttp://www.techonthenet.com/oracle/functions/case.phphttp://www.techonthenet.com/oracle/functions/lead.phphttp://www.techonthenet.com/oracle/functions/nvl2.phphttp://www.techonthenet.com/oracle/functions/coalesce.phphttp://www.techonthenet.com/oracle/functions/lnnvl.phphttp://www.techonthenet.com/oracle/functions/sys_context.phphttp://www.techonthenet.com/oracle/functions/decode.phphttp://www.techonthenet.com/oracle/functions/nanvl.phphttp://www.techonthenet.com/oracle/functions/uid.phphttp://www.techonthenet.com/oracle/functions/abs.phphttp://www.techonthenet.com/oracle/functions/covar_pop.phphttp://www.techonthenet.com/sql/max.phphttp://www.techonthenet.com/oracle/functions/sqrt.phphttp://www.techonthenet.com/oracle/functions/acos.phphttp://www.techonthenet.com/oracle/functions/covar_samp.phphttp://www.techonthenet.com/oracle/functions/median.phphttp://www.techonthenet.com/oracle/functions/stddev.phphttp://www.techonthenet.com/oracle/functions/asin.phphttp://www.techonthenet.com/sql/count.phphttp://www.techonthenet.com/sql/min.phphttp://www.techonthenet.com/sql/sum.phphttp://www.techonthenet.com/oracle/functions/atan.phphttp://www.techonthenet.com/oracle/functions/cume_dist.phphttp://www.techonthenet.com/oracle/functions/mod.phphttp://www.techonthenet.com/oracle/functions/tan.phphttp://www.techonthenet.com/oracle/functions/atan2.phphttp://www.techonthenet.com/oracle/functions/dense_rank.phphttp://www.techonthenet.com/oracle/functions/power.phphttp://www.techonthenet.com/oracle/functions/tanh.phphttp://www.techonthenet.com/oracle/functions/avg.phphttp://www.techonthenet.com/oracle/functions/exp.phphttp://www.techonthenet.com/oracle/functions/rank.phphttp://www.techonthenet.com/oracle/functions/trunc_nbr.phphttp://www.techonthenet.com/oracle/functions/bin_to_num.phphttp://www.techonthenet.com/oracle/functions/extract.phphttp://www.techonthenet.com/oracle/functions/remainder.phphttp://www.techonthenet.com/oracle/functions/trunc_date.phphttp://www.techonthenet.com/oracle/functions/bitand.phphttp://www.techonthenet.com/oracle/functions/floor.phphttp://www.techonthenet.com/oracle/functions/round_nbr.phphttp://www.techonthenet.com/oracle/functions/var_pop.phphttp://www.techonthenet.com/oracle/functions/ceil.phphttp://www.techonthenet.com/oracle/functions/greatest.phphttp://www.techonthenet.com/oracle/functions/round_date.phphttp://www.techonthenet.com/oracle/functions/var_samp.phphttp://www.techonthenet.com/oracle/functions/corr.phphttp://www.techonthenet.com/oracle/functions/least.phphttp://www.techonthenet.com/oracle/functions/sign.phphttp://www.techonthenet.com/oracle/functions/variance.phphttp://www.techonthenet.com/oracle/functions/index_alpha.phphttp://www.techonthenet.com/oracle/functions/ascii.phphttp://www.techonthenet.com/oracle/functions/convert.phphttp://www.techonthenet.com/oracle/functions/lower.phphttp://www.techonthenet.com/oracle/functions/soundex.phphttp://www.techonthenet.com/oracle/functions/asciistr.phphttp://www.techonthenet.com/oracle/functions/decompose.phphttp://www.techonthenet.com/oracle/functions/lpad.phphttp://www.techonthenet.com/oracle/functions/substr.phphttp://www.techonthenet.com/oracle/functions/chr.phphttp://www.techonthenet.com/oracle/functions/dump.phphttp://www.techonthenet.com/oracle/functions/ltrim.phphttp://www.techonthenet.com/oracle/functions/translate.phphttp://www.techonthenet.com/oracle/functions/compose.phphttp://www.techonthenet.com/oracle/functions/initcap.phphttp://www.techonthenet.com/oracle/functions/replace.phphttp://www.techonthenet.com/oracle/functions/trim.phphttp://www.techonthenet.com/oracle/functions/concat.phphttp://www.techonthenet.com/oracle/functions/instr.phphttp://www.techonthenet.com/oracle/functions/rpad.phphttp://www.techonthenet.com/oracle/functions/upper.phphttp://www.techonthenet.com/oracle/functions/concat2.phphttp://www.techonthenet.com/oracle/functions/length.phphttp://www.techonthenet.com/oracle/functions/rtrim.phphttp://www.techonthenet.com/oracle/functions/vsize.phphttp://www.techonthenet.com/oracle/functions/bin_to_num.phphttp://www.techonthenet.com/oracle/functions/numtodsinterval.phphttp://www.techonthenet.com/oracle/functions/to_date.phphttp://www.techonthenet.com/oracle/functions/to_number.phphttp://www.techonthenet.com/oracle/functions/cast.phphttp://www.techonthenet.com/oracle/functions/cast.phphttp://www.techonthenet.com/oracle/functions/numtoyminterval.phphttp://www.techonthenet.com/oracle/functions/to_dsinterval.phphttp://www.techonthenet.com/oracle/functions/to_single_byte.phphttp://www.techonthenet.com/oracle/functions/chartorowid.phphttp://www.techonthenet.com/oracle/functions/rawtohex.phphttp://www.techonthenet.com/oracle/functions/to_lob.phphttp://www.techonthenet.com/oracle/functions/to_timestamp.phphttp://www.techonthenet.com/oracle/functions/from_tz.phphttp://www.techonthenet.com/oracle/functions/to_char.phphttp://www.techonthenet.com/oracle/functions/to_multi_byte.phphttp://www.techonthenet.com/oracle/functions/to_timestamp_tz.phphttp://www.techonthenet.com/oracle/functions/hextoraw.phphttp://www.techonthenet.com/oracle/functions/to_clob.phphttp://www.techonthenet.com/oracle/functions/to_nclob.phphttp://www.techonthenet.com/oracle/functions/to_yminterval.phphttp://www.techonthenet.com/oracle/functions/bfilename.phphttp://www.techonthenet.com/oracle/functions/group_id.phphttp://www.techonthenet.com/oracle/functions/nullif.phphttp://www.techonthenet.com/oracle/functions/user.phphttp://www.techonthenet.com/oracle/functions/cardinality.phphttp://www.techonthenet.com/oracle/functions/lag.phphttp://www.techonthenet.com/oracle/functions/nvl.phphttp://www.techonthenet.com/oracle/functions/userenv.phphttp://www.techonthenet.com/oracle/functions/case.phphttp://www.techonthenet.com/oracle/functions/lead.phphttp://www.techonthenet.com/oracle/functions/nvl2.phphttp://www.techonthenet.com/oracle/functions/coalesce.phphttp://www.techonthenet.com/oracle/functions/lnnvl.phphttp://www.techonthenet.com/oracle/functions/sys_context.phphttp://www.techonthenet.com/oracle/functions/decode.phphttp://www.techonthenet.com/oracle/functions/nanvl.phphttp://www.techonthenet.com/oracle/functions/uid.phphttp://www.techonthenet.com/oracle/functions/abs.phphttp://www.techonthenet.com/oracle/functions/covar_pop.phphttp://www.techonthenet.com/sql/max.phphttp://www.techonthenet.com/oracle/functions/sqrt.phphttp://www.techonthenet.com/oracle/functions/acos.phphttp://www.techonthenet.com/oracle/functions/covar_samp.phphttp://www.techonthenet.com/oracle/functions/median.phphttp://www.techonthenet.com/oracle/functions/stddev.phphttp://www.techonthenet.com/oracle/functions/asin.phphttp://www.techonthenet.com/sql/count.phphttp://www.techonthenet.com/sql/min.phphttp://www.techonthenet.com/sql/sum.phphttp://www.techonthenet.com/oracle/functions/atan.phphttp://www.techonthenet.com/oracle/functions/cume_dist.phphttp://www.techonthenet.com/oracle/functions/mod.phphttp://www.techonthenet.com/oracle/functions/tan.phphttp://www.techonthenet.com/oracle/functions/atan2.phphttp://www.techonthenet.com/oracle/functions/dense_rank.phphttp://www.techonthenet.com/oracle/functions/power.phphttp://www.techonthenet.com/oracle/functions/tanh.phphttp://www.techonthenet.com/oracle/functions/avg.phphttp://www.techonthenet.com/oracle/functions/exp.phphttp://www.techonthenet.com/oracle/functions/rank.phphttp://www.techonthenet.com/oracle/functions/trunc_nbr.phphttp://www.techonthenet.com/oracle/functions/bin_to_num.phphttp://www.techonthenet.com/oracle/functions/extract.phphttp://www.techonthenet.com/oracle/functions/remainder.phphttp://www.techonthenet.com/oracle/functions/trunc_date.phphttp://www.techonthenet.com/oracle/functions/bitand.phphttp://www.techonthenet.com/oracle/functions/floor.phphttp://www.techonthenet.com/oracle/functions/round_nbr.phphttp://www.techonthenet.com/oracle/functions/var_pop.phphttp://www.techonthenet.com/oracle/functions/ceil.phphttp://www.techonthenet.com/oracle/functions/greatest.phphttp://www.techonthenet.com/oracle/functions/round_date.phphttp://www.techonthenet.com/oracle/functions/var_samp.phphttp://www.techonthenet.com/oracle/functions/corr.phphttp://www.techonthenet.com/oracle/functions/least.phphttp://www.techonthenet.com/oracle/functions/sign.phphttp://www.techonthenet.com/oracle/functions/variance.php -
8/12/2019 Plsql Full Notes1
47/136
Cos Ln Sin Cosh Log Sinh
6ate "unctions:**-Konths Last-Day o$n* To-DateC$rrent-Date LocalTi%esta%p SessionTi%eRone Tr$nc
C$rrent-Ti%esta%p Konths-'et)een Sys*ate TV-OsetD!Ti%eRone #e)-Ti%e SysTi%esta%p4ro%-TV #ext-Day To-Char
Brror "unctions:SQLCODESQLEK
Miscellaneous "unctions:etrie,e $ser >D ro% the c$rrent Oracle sessionetrie,e the session >D or the $ser logge* in
Oracle/PLSQL: Oracle Syste% Ta!les
*elow is an alphabetical listing of the Oracle system tables that are commonly used.
Syste% Ta!le Description
3&&H3A>MB%T$ 3rguments in ob@ect accessible to the user
3&&H53T3&O 3ll tables, views, synonyms, seIuences accessible to the user
3&&H5O&H5OMMB%T$ 5omments on columns of accessible tables and views
3&&H5O%$TA39%T$ 5onstraint definitions on accessible tables
3&&H5O%$H5O&>M%$ 9nformation about accessible columns in constraint definitions
http://www.techonthenet.com/oracle/functions/cos.phphttp://www.techonthenet.com/oracle/functions/ln.phphttp://www.techonthenet.com/oracle/functions/sin.phphttp://www.techonthenet.com/oracle/functions/cosh.phphttp://www.techonthenet.com/oracle/functions/log.phphttp://www.techonthenet.com/oracle/functions/sinh.phphttp://www.techonthenet.com/oracle/functions/add_months.phphttp://www.techonthenet.com/oracle/functions/last_day.phphttp://www.techonthenet.com/oracle/functions/round_date.phphttp://www.techonthenet.com/oracle/functions/to_date.phphttp://www.techonthenet.com/oracle/functions/current_date.phphttp://www.techonthenet.com/oracle/functions/localtimestamp.phphttp://www.techonthenet.com/oracle/functions/sessiontimezone.phphttp://www.techonthenet.com/oracle/functions/trunc_date.phphttp://www.techonthenet.com/oracle/functions/current_timestamp.phphttp://www.techonthenet.com/oracle/functions/months_between.phphttp://www.techonthenet.com/oracle/functions/sysdate.phphttp://www.techonthenet.com/oracle/functions/tz_offset.phphttp://www.techonthenet.com/oracle/functions/dbtimezone.phphttp://www.techonthenet.com/oracle/functions/new_time.phphttp://www.techonthenet.com/oracle/functions/systimestamp.phphttp://www.techonthenet.com/oracle/functions/from_tz.phphttp://www.techonthenet.com/oracle/functions/next_day.phphttp://www.techonthenet.com/oracle/functions/to_char.phphttp://www.techonthenet.com/oracle/exceptions/sqlcode.phphttp://www.techonthenet.com/oracle/exceptions/sqlerrm.phphttp://www.techonthenet.com/oracle/functions/username.phphttp://www.techonthenet.com/oracle/functions/uid.phphttp://www.techonthenet.com/oracle/functions/cos.phphttp://www.techonthenet.com/oracle/functions/ln.phphttp://www.techonthenet.com/oracle/functions/sin.phphttp://www.techonthenet.com/oracle/functions/cosh.phphttp://www.techonthenet.com/oracle/functions/log.phphttp://www.techonthenet.com/oracle/functions/sinh.phphttp://www.techonthenet.com/oracle/functions/add_months.phphttp://www.techonthenet.com/oracle/functions/last_day.phphttp://www.techonthenet.com/oracle/functions/round_date.phphttp://www.techonthenet.com/oracle/functions/to_date.phphttp://www.techonthenet.com/oracle/functions/current_date.phphttp://www.techonthenet.com/oracle/functions/localtimestamp.phphttp://www.techonthenet.com/oracle/functions/sessiontimezone.phphttp://www.techonthenet.com/oracle/functions/trunc_date.phphttp://www.techonthenet.com/oracle/functions/current_timestamp.phphttp://www.techonthenet.com/oracle/functions/months_between.phphttp://www.techonthenet.com/oracle/functions/sysdate.phphttp://www.techonthenet.com/oracle/functions/tz_offset.phphttp://www.techonthenet.com/oracle/functions/dbtimezone.phphttp://www.techonthenet.com/oracle/functions/new_time.phphttp://www.techonthenet.com/oracle/functions/systimestamp.phphttp://www.techonthenet.com/oracle/functions/from_tz.phphttp://www.techonthenet.com/oracle/functions/next_day.phphttp://www.techonthenet.com/oracle/functions/to_char.phphttp://www.techonthenet.com/oracle/exceptions/sqlcode.phphttp://www.techonthenet.com/oracle/exceptions/sqlerrm.phphttp://www.techonthenet.com/oracle/functions/username.phphttp://www.techonthenet.com/oracle/functions/uid.php -
8/12/2019 Plsql Full Notes1
48/136
3&&H6*H&9%?$ 6atabase lin+s accessible to the user
3&&HBAAOA$ 5urrent errors on stored ob@ects that user is allowed to create
3&&H9%6BKB$ 6escriptions of indexes on tables accessible to the user
3&&H9%6H5O&>M%$ 5O&>M%s comprising 9%6BKes on accessible T3*&B$
3&&H&O*$ 6escription of &O*s contained in tables accessible to the user
3&&HO*4B5T$ Ob@ects accessible to the user
3&&HO*4B5THT3*&B$ 6escription of all ob@ect tables accessible to the user
3&&H$BD>B%5B$ 6escription of $BD>B%5Bs accessible to the user
3&&H$%3-$=OT$ $napshots the user can access
3&&H$O>A5B 5urrent source on stored ob@ects that user is allowed to create
3&&H$F%O%FM$ 3ll synonyms accessible to the user
3&&HT3*&B$ 6escription of relational tables accessible to the user
3&&HT3*H5O&>M%$ 5olumns of userGs tables, views and clusters
3&&HT3*H5O&H$T3T9$T95$ 5olumns of userGs tables, views and clusters
3&&HT3*H5OMMB%T$ 5omments on tables and views accessible to the user
3&&HTA9BA$ Triggers accessible to the current user
3&&HTA9BAH5O&$ 5olumn usage in userGs triggers or in triggers on userGs tables
3&&HTF-B$ 6escription of types accessible to the user
3&&H>-63T3*&BH5O&>M%$ 6escription of all updatable columns
3&&H>$BA$ 9nformation about all users of the database
3&&H9B!$ 6escription of views accessible to the user
-
8/12/2019 Plsql Full Notes1
49/136
63T3*3$BH5OM-3T9*&BH&BB& 6atabase compatible parameter set via init.ora
6*3H6*H&9%?$ 3ll database lin+s in the database
6*3HBAAOA$ 5urrent errors on all stored ob@ects in the database
6*3HO*4B5T$ 3ll ob@ects in the database
6*3HAO&B$ 3ll Aoles which exist in the database
6*3HAO&BH-A9$ Aoles granted to users and roles
6*3H$O>A5B $ource of all stored ob@ects in the database
6*3HT3*&B$-35B$ 6escription of all tablespaces
6*3HT3*H-A9$ 3ll grants on ob@ects in the database
6*3HTA9BA$ 3ll triggers in the database
6*3HT$HD>OT3$ Tablespace Iuotas for all users
6*3H>$BA$ 9nformation about all users of the database
6*3H9B!$ 6escription of all views in the database
695T9O%3AF 6escription of data dictionary tables and views
695TH5O&>M%$ 6escription of columns in data dictionary tables and views
&O*3&H%3MB global database name
%&$H63T3*3$BH-3A3MBTBA$ -ermanent %&$ parameters of the database
%&$H9%$T3%5BH-3A3MBTBA$ %&$ parameters of the instance
%&$H$B$$9O%H-3A3MBTBA$ %&$ parameters of the user session
-AO6>5TH5OM-O%B%THBA$9O% version and status information for component products
AO&BHT3*H-A9$ Table privileges granted to roles
-
8/12/2019 Plsql Full Notes1
50/136
$B$$9O%H-A9$ -rivileges which the user currently has set
$B$$9O%HAO&B$ Aoles which the user currently has enabled.
$F$TBMH-A99&BBHM3- 6escription table for privilege type codes. Maps privilege type numbers to type
names
T3*&BH-A99&BB$ rants on ob@ects for which the user is the grantor, grantee, owner, or an
enabled role or ->*&95 is the grantee
T3*&BH-A99&BBHM3- 6escription table for privilege (auditing option) type codes. Maps privilege
(auditing option) type numbers to type names
Oracle/PLSQL: Pri%ary eys
hat is a pri%ary NeyW
3 pri%ary Neyis a single field or combination of fields that uniIuely defines a record. %one of the fields that are part of the primary +ey can contain a null value. 3 table canhave only one primary +ey.
#ote:
9n Oracle, a primary +ey can not contain more than /2 columns.
3 primary +ey can be defined in either a 5AB3TB T3*&B statement or an 3&TBA T3*&B statement.
sing a CETE T'LE state%ent
The syntax for creating a primary +ey using a 5AB3TB T3*&B statement is:
-
8/12/2019 Plsql Full Notes1
51/136
CETE T'LE ta!le-na%e(col$%n1 *atatype n$ll/not n$llcol$%n5 *atatype n$ll/not n$llK E (col$%n1 col$%n5 < col$%n-n""7
4or exa%ple:
CETE T'LE s$pplier
( s$pplier-i* n$%eric(10" not n$ll
s$pplier-na%e ,archar5(J0" not n$ll
contact-na%e ,archar5(J0"
CO#ST>#T s$pplier-pN P>K E (s$pplier-i*"
"7
9n this example, weGve created a primary +ey on the supplier table called supplierHp+. 9t consists of only one field # the supplierHid field.
!e could also create a primary +ey with more than one field as in the example below:
CETE T'LE s$pplier
( s$pplier-i* n$%eric(10" not n$ll
s$pplier-na%e ,archar5(J0" not n$ll
contact-na%e ,archar5(J0"
CO#ST>#T s$pplier-pN P>K E (s$pplier-i* s$pplier-na%e"
"7
sing an LTE T'LE state%ent
The syntax for creating a primary +ey in an 3&TBA T3*&B statement is:
-
8/12/2019 Plsql Full Notes1
52/136
LTE T'LE ta!le-na%ea** CO#ST>#T constraint-na%e P>K E (col$%n1 col$%n5 #T constraint-na%e7
4or exa%ple:
LTE T'LE s$pplier*rop CO#ST>#T s$pplier-pN7
-
8/12/2019 Plsql Full Notes1
53/136
9n this example, weGre dropping a primary +ey on the supplier table called supplierHp+.
Disa!le a Pri%ary ey
The syntax for disabling a primary +ey is:
LTE T'LE ta!le-na%e*isa!le CO#ST>#T constraint-na%e7
4or exa%ple:
LTE T'LE s$pplier*isa!le CO#ST>#T s$pplier-pN7
9n this example, weGre disabling a primary +ey on the supplier table called supplierHp+.
Ena!le a Pri%ary ey
The syntax for enabling a primary +ey is:
LTE T'LE ta!le-na%eena!le CO#ST>#T constraint-na%e7
4or exa%ple:
-
8/12/2019 Plsql Full Notes1
54/136
LTE T'LE s$pplierena!le CO#ST>#T s$pplier-pN7
9n this example, weGre enabling a primary +ey on the supplier table called supplierHp+.
Oracle/PLSQL Topics: 4oreign eys
5reating "oreign ?eys:
4oreign eys
4oreign eys )ith casca*e *elete
4oreign eys )ith set n$ll on *elete
6ropping "oreign ?eys:
Drop a oreign Ney
6isableBnable "oreign ?eys:
Disa!le a oreign Ney
Ena!le a oreign Ney
Oracle/PLSQL: 4oreign eys
hat is a oreign NeyW
3 oreign Neymeans that values in one table must also appear in another table.
http://www.techonthenet.com/oracle/foreign_keys/foreign_keys.phphttp://www.techonthenet.com/oracle/foreign_keys/foreign_delete.phphttp://www.techonthenet.com/oracle/foreign_keys/foreign_null.phphttp://www.techonthenet.com/oracle/foreign_keys/drop.phphttp://www.techonthenet.com/oracle/foreign_keys/disable.phphttp://www.techonthenet.com/oracle/foreign_keys/enable.phphttp://www.techonthenet.com/oracle/foreign_keys/foreign_keys.phphttp://www.techonthenet.com/oracle/foreign_keys/foreign_delete.phphttp://www.techonthenet.com/oracle/foreign_keys/foreign_null.phphttp://www.techonthenet.com/oracle/foreign_keys/drop.phphttp://www.techonthenet.com/oracle/foreign_keys/disable.phphttp://www.techonthenet.com/oracle/foreign_keys/enable.php -
8/12/2019 Plsql Full Notes1
55/136
The referenced table is called the parent ta!lewhile the table with the foreign +ey is called the chil* ta!le. The foreign +ey in the child table will generally reference a pri%aryNeyin the parent table.
3 foreign +ey can be defined in either a 5AB3TB T3*&B statement or an 3&TBA T3*&B statement.
sing a CETE T'LE state%ent
The syntax for creating a foreign +ey using a 5AB3TB T3*&B statement is:
CETE T'LE ta!le-na%e(col$%n1 *atatype n$ll/not n$llcol$%n5 *atatype n$ll/not n$llB# E (col$%n1 col$%n5 #T s$pplier-pN P>K E (s$pplier-i*"
"7
CETE T'LE pro*$cts
( pro*$ct-i* n$%eric(10" not n$ll
s$pplier-i* n$%eric(10" not n $ll
CO#ST>#T N-s$pplier
http://www.techonthenet.com/oracle/primary_keys.phphttp://www.techonthenet.com/oracle/primary_keys.phphttp://www.techonthenet.com/oracle/primary_keys.phphttp://www.techonthenet.com/oracle/primary_keys.php -
8/12/2019 Plsql Full Notes1
56/136
4OE>B# E (s$pplier-i*"
E4EE#CES s$pplier(s$pplier-i*"
"7
9n this example, weGve created a primary +ey on the supplier table called supplier_p0. 9t consists of only one field # the supplierHid field. Then weGve created a foreign +ey called
f0_supplieron the products table that references the supplier table based on the supplierHid field.
!e could also create a foreign +ey with more than one field as in the example below:
CETE T'LE s$pplier
( s$pplier-i* n$%eric(10" not n$ll
s$pplier-na%e ,archar5(J0" not n$ll
contact-na%e ,archar5(J0"
CO#ST>#T s$pplier-pN P>K E (s$pplier-i* s$pplier-na%e"
"7
CETE T'LE pro*$cts
( pro*$ct-i* n$%eric(10" not n$ll
s$pplier-i* n$%eric(10" not n $ll
s$pplier-na%e ,archar5(J0" not n$ll
CO#ST>#T N-s$pplier-co%p
4OE>B# E (s$pplier-i* s$pplier-na%e"
E4EE#CES s$pplier(s$pplier-i* s$pplier-na%e"
"7
9n this example, our foreign +ey called f0_forei.n_compreferences the supplier table based on two fields # the supplierHid and supplierHname fields.
sing an LTE T'LE state%ent
The syntax for creating a foreign +ey in an 3&TBA T3*&B statement is:
-
8/12/2019 Plsql Full Notes1
57/136
LTE T'LE ta!le-na%ea** CO#ST>#T constraint-na%e 4OE>B# E (col$%n1 col$%n5 #T N-s$pplier 4OE>B# E (s$pplier-i* s$pplier-na%e" E4EE#CES s$pplier(s$pplier-i* s$pplier-na%e"7
Oracle/PLSQL: 4oreign eys )ith casca*e *elete
hat is a oreign NeyW
3 oreign Neymeans that values in one table must also appear in another table.
The referenced table is called the parent ta!lewhile the table with the foreign +ey is called the chil* ta!le. The foreign +ey in the child table will generally reference a pri%aryNeyin the parent table.
http://www.techonthenet.com/oracle/primary_keys.phphttp://www.techonthenet.com/oracle/primary_keys.phphttp://www.techonthenet.com/oracle/primary_keys.phphttp://www.techonthenet.com/oracle/primary_keys.php -
8/12/2019 Plsql Full Notes1
58/136
3 foreign +ey with a cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table with automatically be deleted. This iscalled a cascade delete.
3 foreign +ey with a cascade delete can be defined in either a 5AB3TB T3*&B statement or an 3&TBA T3*&B statement.
sing a CETE T'LE state%ent
The syntax for creating a foreign +ey using a 5AB3TB T3*&B statement is:
CETE T'LE ta!le-na%e(col$%n1 *atatype n$ll/not n$llcol$%n5 *atatype n$ll/not n$llB# E (col$%n1 col$%n5 #T s$pplier-pN P>K E (s$pplier-i*"
"7
CETE T'LE pro*$cts
( pro*$ct-i* n$%eric(10" not n$ll
s$pplier-i* n$%eric(10" not n $ll
-
8/12/2019 Plsql Full Notes1
59/136
CO#ST>#T N-s$pplier
4OE>B# E (s$pplier-i*"
E4EE#CES s$pplier(s$pplier-i*"
O# DELETE CSCDE
"7
9n this example, weGve created a primary +ey on the supplier table called supplier_p0. 9t consists of only one field # the supplierHid field. Then weGve created a foreign +ey calledf0_supplieron the products table that references the supplier table based on the supplierHid field.
*ecause of the cascade delete, when a record in the supplier table is deleted, all records in the products table will also be deleted that have the same supplierHid value.
!e could also create a foreign +ey (with a cascade delete) with more than one field as in the example below:
CETE T'LE s$pplier
( s$pplier-i* n$%eric(10" not n$ll
s$pplier-na%e ,archar5(J0" not n$ll
contact-na%e ,archar5(J0"
CO#ST>#T s$pplier-pN P>K E (s$pplier-i* s$pplier-na%e"
"7
CETE T'LE pro*$cts
( pro*$ct-i* n$%eric(10" not n$ll
s$pplier-i* n$%eric(10" not n $ll
s$pplier-na%e ,archar5(J0" not n$ll
CO#ST>#T N-s$pplier-co%p
4OE>B# E (s$pplier-i* s$pplier-na%e"
E4EE#CES s$pplier(s$pplier-i* s$pplier-na%e"
O# DELETE CSCDE
"7
9n this example, our foreign +ey called f0_forei.n_compreferences the supplier table based on two fields # the supplierHid and supplierHname fields.
-
8/12/2019 Plsql Full Notes1
60/136
The cascade delete on the foreign +ey called f0_forei.n_compcauses all corresponding records in the products table to be cascade deleted when a record in the supplier tableis deleted, based on supplierHid and supplierHname.
sing an LTE T'LE state%ent
The syntax for creating a foreign +ey in an 3&TBA T3*&B statement is:
LTE T'LE ta!le-na%ea** CO#ST>#T constraint-na%e 4OE>B# E (col$%n1 col$%n5 #T N-s$pplier 4OE>B# E (s$pplier-i* s$pplier-na%e" E4EE#CES s$pplier(s$pplier-i* s$pplier-na%e" O# DELETE CSCDE7
-
8/12/2019 Plsql Full Notes1
61/136
Oracle/PLSQL: 4oreign eys )ith set n$ll on *elete
hat is a oreign NeyW
3 oreign Neymeans that values in one table must also appear in another table.
The referenced table is called the parent ta!lewhile the table with the foreign +ey is called the chil* ta!le. The foreign +ey in the child table will generally reference a pri%aryNeyin the parent table.
3 foreign +ey with a Eset null on deleteE means that if a record in the parent table is deleted, then the corresponding records in the child table will have the foreign +ey fields setto null. The records in the child table will notbe deleted.
3 foreign +ey with a Eset null on deleteE can be defined in either a 5AB3TB T3*&B statement or an 3&TBA T3*&B statement.
sing a CETE T'LE state%ent
The syntax for creating a foreign +ey using a 5AB3TB T3*&B statement is:
CETE T'LE ta!le-na%e(col$%n1 *atatype n$ll/not n$llcol$%n5 *atatype n$ll/not n$llB# E (col$%n1 col$%n5
-
8/12/2019 Plsql Full Notes1
62/136
CETE T'LE s$pplier
( s$pplier-i* n$%eric(10" not n$ll
s$pplier-na%e ,archar5(J0" not n$ll
contact-na%e ,archar5(J0"
CO#ST>#T s$pplier-pN P>K E (s$pplier-i*"
"7
CETE T'LE pro*$cts
( pro*$ct-i* n$%eric(10" not n$ll
s$pplier-i* n$%eric(10"
CO#ST>#T N-s$pplier
4OE>B# E (s$pplier-i*"
E4EE#CES s$pplier(s$pplier-i*"
O# DELETE SET #LL
"7
9n this example, weGve created a primary +ey on the supplier table called supplier_p0. 9t consists of only one field # the supplierHid field. Then weGve created a foreign +ey calledf0_supplieron the products table that references the supplier table based on the supplierHid field.
*ecause of the set null on delete, when a record in the supplier table is deleted, all corresponding records in the products table will have the supplierHid values set to null.
!e could also create a foreign +ey Eset null on deleteE with more than one field as in the example below:
CETE T'LE s$pplier
( s$pplier-i* n$%eric(10" not n$ll
s$pplier-na%e ,archar5(J0" not n$ll
contact-na%e ,archar5(J0"
CO#ST>#T s$pplier-pN P>K E (s$pplier-i* s$pplier-na%e"
"7
CETE T'LE pro*$cts
( pro*$ct-i* n$%eric(10" not n$ll
s$pplier-i* n$%eric(10"
-
8/12/2019 Plsql Full Notes1
63/136
s$pplier-na%e ,archar5(J0"
CO#ST>#T N-s$pplier-co%p
4OE>B# E (s$pplier-i* s$pplier-na%e"
E4EE#CES s$pplier(s$pplier-i* s$pplier-na%e"
O# DELETE SET #LL
"7
9n this example, our foreign +ey called f0_forei.n_compreferences the supplier table based on two fields # the supplierHid and supplierHname fields.
The delete on the foreign +ey called f0_forei.n_compcauses all corresponding records in the products table to have the supplierHid and supplierHname fields set to null whena record in the supplier table is deleted, based on supplierHid and supplierHname.
sing an LTE T'LE state%ent
The syntax for creating a foreign +ey in an 3&TBA T3*&B statement is:
LTE T'LE ta!le-na%ea** CO#ST>#T constraint-na%e 4OE>B# E (col$%n1 col$%n5
-
8/12/2019 Plsql Full Notes1
64/136
!e could also create a foreign +ey Ewith a set null on deleteE with more than one field as in the example below:
LTE T'LE pro*$cts
a** CO#ST>#T N-s$pplier 4OE>B# E (s$pplier-i* s$pplier-na%e" E4EE#CES s$pplier(s$pplier-i* s$pplier-na%e" O# DELETE SET #LL7
Oracle/PLSQL: Drop a oreign Ney
The syntax for dropping a foreign +ey is:
LTE T'LE ta!le-na%e*rop CO#ST>#T constraint-na%e7
4or exa%ple:
9f you had created a foreign +ey as follows:
CETE T'LE s$pplier
( s$pplier-i* n$%eric(10" not n$ll
s$pplier-na%e ,archar5(J0" not n$ll
contact-na%e ,archar5(J0"
CO#ST>#T s$pplier-pN P>K E (s$pplier-i*"
"7
CETE T'LE pro*$cts
( pro*$ct-i* n$%eric(10" not n$ll
s$pplier-i* n$%eric(10" not n $ll
-
8/12/2019 Plsql Full Notes1
65/136
CO#ST>#T N-s$pplier
4OE>B# E (s$pplier-i*"
E4EE#CES s$pplier(s$pplier-i*"
"7
9n this example, weGve created a primary +ey on the supplier table called supplier_p0. 9t consists of only one field # the supplierHid field. Then weGve created a foreign +ey calledf0_supplieron the products table that references the supplier table based on the supplierHid field.
9f we then wanted to drop the foreign +ey called f+Hsupplier, we could execute the following command:
LTE T'LE pro*$cts*rop CO#ST>#T N-s$pplier7
Oracle/PLSQL: Disa!le a oreign Ney
The syntax for disabling a foreign +ey is:
LTE T'LE ta!le-na%e*isa!le CO#ST>#T constraint-na%e7
4or exa%ple:
9f you had created a foreign +ey as follows:
CETE T'LE s$pplier( s$pplier-i* n$%eric(10" not n$ll
s$pplier-na%e ,archar5(J0" not n$ll
contact-na%e ,archar5(J0"
CO#ST>#T s$pplier-pN P>K E (s$pplier-i*"
-
8/12/2019 Plsql Full Notes1
66/136
"7
CETE T'LE pro*$cts
( pro*$ct-i* n$%eric(10" not n$ll
s$pplier-i* n$%eric(10" not n $ll
CO#ST>#T N-s$pplier
4OE>B# E (s$pplier-i*"
E4EE#CES s$pplier(s$pplier-i*"
"7
9n this example, weGve created a primary +ey on the supplier table called supplier_p0. 9t consists of only one field # the supplierHid field. Then weGve created a foreign +ey calledf0_supplieron the products table that references the supplier table based on the supplierHid field.
9f we then wanted to disable the foreign +ey called f+Hsupplier, we could execute the following command:
LTE T'LE pro*$cts
*isa!le CO#ST>#T N-s$pplier7
Oracle/PLSQL: Ena!le a oreign Ney
The syntax for enabling a foreign +ey is:
LTE T'LE ta!le-na%eena!le CO#ST>#T constraint-na%e7
4or exa%ple:
9f you had created a foreign +ey as follows:
-
8/12/2019 Plsql Full Notes1
67/136
CETE T'LE s$pplier
( s$pplier-i* n$%eric(10" not n$ll
s$pplier-na%e ,archar5(J0" not n$ll
contact-na%e ,archar5(J0"
CO#ST>#T s$pplier-pN P>K E (s$pplier-i*"
"7
CETE T'LE pro*$cts
( pro*$ct-i* n$%eric(10" not n$ll
s$pplier-i* n$%eric(10" not n $ll
CO#ST>#T N-s$pplier
4OE>B# E (s$pplier-i*"
E4EE#CES s$pplier(s$pplier-i*"
"7
9n this example, weGve created a primary +ey on the supplier table called supplier_p0. 9t consists of only one field # the supplierHid field. Then weGve created a foreign +ey calledf0_supplieron the products table that references the supplier table based on the supplierHid field.
9f the foreign +ey had been *isa!le*and we wanted to enable it, we could execute the following command:
LTE T'LE pro*$ctsena!le CO#ST>#T N-s$pplier7
Oracle/PLSQL: niM$e Constraints
hat is a $niM$e constraintW
3 $niM$e constraintis a single field or combination of fields that uniIuely defines a record. $ome of the fields can contain null values as long as the combination of values isuniIue.
#ote:
http://www.techonthenet.com/oracle/foreign_keys/disable.phphttp://www.techonthenet.com/oracle/foreign_keys/disable.php -
8/12/2019 Plsql Full Notes1
68/136
9n Oracle, a uniIue constraint can not contain more than /2 columns.
3 uniIue constraint can be defined in either a 5AB3TB T3*&B statement or an 3&TBA T3*&B statement.
hat is the *ierence !et)een a $niM$e constraint an* a pri%ary NeyW
Pri%ary ey niM$e
Constraint
%one of the fields that are part of the primary +ey can contain a null
value.
$ome of the
fields that
are part of
the uniIue
constraint
can contain
null values
as long as
the
combination
of values is
uniIue.
Oracle does not permit you to create both a primary +ey and uniIue constraint with the same columns.
sing a CETE T'LE state%ent
The syntax for creating a uniIue constraint using a 5AB3TB T3*&B statement is:
CETE T'LE ta!le-na%e(col$%n1 *atatype n$ll/not n$ll
-
8/12/2019 Plsql Full Notes1
69/136
col$%n5 *atatype n$ll/not n$llQE (col$%n1 col$%n5 < col$%n-n""7
4or exa%ple:
CETE T'LE s$pplier
( s$pplier-i* n$%eric(10" not n$ll
s$pplier-na%e ,archar5(J0" not n$ll
contact-na%e ,archar5(J0"
CO#ST>#T s$pplier-$niM$e #>QE (s$pplier-i*"
"7
9n this example, weGve created a uniIue constraint on the supplier table called supplierHuniIue. 9t consists of only one field # the supplierHid field.
!e could also create a uniIue constraint with more than one field as in the example below:
CETE T'LE s$pplier
( s$pplier-i* n$%eric(10" not n$ll
s$pplier-na%e ,archar5(J0" not n$ll
contact-na%e ,archar5(J0"
CO#ST>#T s$pplier-$niM$e #>QE (s$pplier-i* s$pplier-na%e"
"7
sing an LTE T'LE state%ent
The syntax for creating a uniIue constraint in an 3&TBA T3*&B statement is:
-
8/12/2019 Plsql Full Notes1
70/136
LTE T'LE ta!le-na%ea** CO#ST>#T constraint-na%e #>QE (col$%n1 col$%n5 #T constraint-na%e7
4or exa%ple:
LTE T'LE s$pplier*rop CO#ST>#T s$pplier-$niM$e7
-
8/12/2019 Plsql Full Notes1
71/136
9n this example, weGre dropping a uniIue constraint on the supplier table called supplierHuniIue.
Disa!le a niM$e Constraint
The syntax for disabling a uniIue constraint is:
LTE T'LE ta!le-na%e*isa!le CO#ST>#T constraint-na%e7
4or exa%ple:
LTE T'LE s$pplier*isa!le CO#ST>#T s$pplier-$niM$e7
9n this example, weGre disabling a uniIue constraint on the supplier table called supplierHuniIue.
Ena!le a niM$e Constraint
The syntax for enabling a uniIue constraint is:
LTE T'LE ta!le-na%eena!le CO#ST>#T constraint-na%e7
4or exa%ple:
-
8/12/2019 Plsql Full Notes1
72/136
LTE T'LE s$pplierena!le CO#ST>#T s$pplier-$niM$e7
9n this example, weGre enabling a uniIue constraint on the supplier table called supplierHuniIue.
Oracle/PLSQL: ChecN Constraints
hat is a checN constraintW
3 checN constraintallows you to specify a condition on each row in a table.
#ote:
3 chec+ constraint can %OT be defined on a +>E.
The chec+ constraint defined on a table must refer to only columns in that table. 9t can not refer to columns in other tables.
3 chec+ constraint can %OT include a S'QE.
3 chec+ constraint can be defined in either a 5AB3TB T3*&B statement or an 3&TBA T3*&B statement.
sing a CETE T'LE state%ent
The syntax for creating a chec+ constraint using a 5AB3TB T3*&B statement is:
CETE T'LE ta!le-na%e(col$%n1 *atatype n$ll/not n$llcol$%n5 *atatype n$ll/not n$ll
-
8/12/2019 Plsql Full Notes1
73/136
The 69$3*&B +eyword is optional. 9f you create a chec+ constraint using the 69$3*&B +eyword, the constraint will be created, but the condition will not be enforced.
4or exa%ple:
CETE T'LE s$ppliers
( s$pplier-i* n$%eric(6"
s$pplier-na%e ,archar5(J0"
CO#ST>#T checN-s$pplier-i*
CAEC (s$pplier-i* 'ETEE# 100 an* 9999"
"7
9n this first example, weGve created a chec+ constraint on the suppliers table called chec+HsupplierHid. This constraint ensures that the supplierHid field contains valuesbetween and 7777.
CETE T'LE s$ppliers
( s$pplier-i* n$%eric(6"s$pplier-na%e ,archar5(J0"
CO#ST>#T checN-s$pplier-na%e
CAEC (s$pplier-na%e 2 $pper(s$pplier-na%e""
"7
9n this second example, weGve created a chec+ constraint called chec+HsupplierHname. This constraint ensures that the supplierHname column always contains uppercasecharacters.
sing an LTE T'LE state%ent
The syntax for creating a chec+ constraint in an 3&TBA T3*&B statement is:
-
8/12/2019 Plsql Full Notes1
74/136
LTE T'LE ta!le-na%ea** CO#ST>#T constraint-na%e CAEC (col$%n-na%e con*ition" .D>S'LE7
The 69$3*&B +eyword is optional. 9f you create a chec+ constraint using the 69$3*&B +eyword, the constraint will be created, but the condition will not be enforced.
4or exa%ple:
LTE T'LE s$ppliersa** CO#ST>#T checN-s$pplier-na%e CAEC (s$pplier-na%e ># (=>'K= =Kicrosot= =#+>D>=""7
9n this example, weGve created a chec+ constraint on the existing suppliers table called chec+HsupplierHname. 9t ensures that the supplierHname field only contains thefollowing values: 9*M, Microsoft, or %9693.
Drop a ChecN Constraint
The syntax for dropping a chec+ constraint is:
LTE T'LE ta!le-na%e*rop CO#ST>#T constraint-na%e7
4or exa%ple:
LTE T'LE s$ppliers*rop CO#ST>#T checN-s$pplier-i*7
9n this example, weGre dropping a chec+ constraint on the suppliers table called chec+HsupplierHid.
-
8/12/2019 Plsql Full Notes1
75/136
Ena!le a ChecN Constraint
The syntax for enabling a chec+ constraint is:
LTE T'LE ta!le-na%eena!le CO#ST>#T constraint-na%e7
4or exa%ple:
LTE T'LE s$ppliersena!le CO#ST>#T checN-s$pplier-i*7
9n this example, weGre enabling a chec+ constraint on the suppliers table called chec+HsupplierHid.
Disa!le a ChecN Constraint
The syntax for disabling a chec+ constraint is:
LTE T'LE ta!le-na%e*isa!le CO#ST>#T constraint-na%e7
4or exa%ple:
LTE T'LE s$ppliers
*isa!le CO#ST>#T checN-s$pplier-i*7
9n this example, weGre disabling a chec+ constraint on the suppliers table called chec+HsupplierHid.
-
8/12/2019 Plsql Full Notes1
76/136
Oracle/PLSQL: >n*exes
hat is an >n*exW
3n in*exis a performance#tuning method of allowing faster retrieval of records. 3n index creates an entry for each value that appears in the indexed columns. *y default,Oracle creates *#tree indexes.
Create an >n*ex
The syntax for creating a index is:
CETE .#>QE >#DE in*ex-na%e O# ta!le-na%e (col$%n1 col$%n5 < col$%n-n" . COKPTE STT>ST>CS 7
>%9D>B indicates that the combination of values in the indexed columns must be uniIue.
5OM->TB $T3T9$T95$ tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose a Eplan of executionE when$D& statements are executed.
4or exa%ple:
CETE >#DE s$pplier-i*x O# s$pplier (s$pplier-na%e"7
9n this example, weGve created an index on the supplier table called supplierHidx. 9t consists of only one field # the supplierHname field.
-
8/12/2019 Plsql Full Notes1
77/136
!e could also create an index with more than one field as in the example below:
CETE >#DE s$pplier-i*x O# s$pplier (s$pplier-na%e city"7
!e could also choose to collect statistics upon creation of the index as follows:
CETE >#DE s$pplier-i*x O# s$pplier (s$pplier-na%e city" COKPTE STT>ST>CS7
Create a 4$nction@'ase* >n*ex
9n Oracle, you are not restricted to creating indexes on only columns. Fou can create function#based indexes.
The syntax for creating a function#based index is:
CETE .#>QE >#DE in*ex-na%e O# ta!le-na%e ($nction1 $nction5 < $nction-n" . COKPTE STT>ST>CS 7
4or exa%ple:
CETE >#DE s$pplier-i*x O# s$pplier (PPE(s$pplier-na%e""7
9n this example, weGve created an index based on the uppercase evaluation of the supplier_namefield.
-
8/12/2019 Plsql Full Notes1
78/136
=owever, to be sure that the Oracle optimizer uses this index when executing your $D& statements, be sure that >--BA(supplierHname) does not evaluate to a %>&& value.To ensure this, add PPE(s$pplier-na%e" >S #OT #LLto your !=BAB clause as follows:
SELECT s$pplier-i* s$pplier-na%e PPE(s$pplier-na%e"4OK s$pplierAEE PPE(s$pplier-na%e" >S #OT #LL
ODE ' PPE(s$pplier-na%e"7
ena%e an >n*ex
The syntax for renaming an index is:
LTE >#DE in*ex-na%e E#KE TO ne)-in*ex-na%e7
4or exa%ple:
LTE >#DE s$pplier-i*x E#KE TO s$pplier-in*ex-na%e7
9n this example, weGre renaming the index called supplier_idxto supplier_index_name.
Collect Statistics on an >n*ex
9f you forgot to collect statistics on the index when you first created it or you want to update the statistics, you can always use the 3&TBA 9%6BK command to collect statisticsat a later date.
The syntax for collecting statistics on an index is:
-
8/12/2019 Plsql Full Notes1
79/136
LTE >#DE in*ex-na%e E'>LD COKPTE STT>ST>CS7
4or exa%ple:
LTE >#DE s$pplier-i*x E'>LD COKPTE STT>ST>CS7
9n this example, weGre collecting statistics for the index called supplierHidx.
Drop an >n*ex
The syntax for dropping an index is:
DOP >#DE in*ex-na%e7
4or exa%ple:
DOP >#DE s$pplier-i*x7
9n this example, weGre dropping an index called supplierHidx.
Oracle/PLSQL: Creating 4$nctions
-
8/12/2019 Plsql Full Notes1
80/136
9n Oracle, you can create your own functions.
The syntax for a function is:
CETE .O EPLCE 4#CT>O# $nction-na%e . (para%eter .para%eter"
ET# ret$rn-*atatype>S 3 S .*eclaration-section'EB># exec$ta!le-section.ECEPT>O# exception-sectionE#D .$nction-na%e7
!hen you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:
. >## The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
2. OT# The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
/. ># OT# The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.
The following is a simple example of a function:
CETE O EPLCE 4$nction 4in*Co$rse ( na%e-in ># ,archar5 " ET# n$%!er>S
cn$%!er n$%!er7
c$rsor c1 is select co$rse-n$%!er
-
8/12/2019 Plsql Full Notes1
81/136
ro% co$rses-t!l )here co$rse-na%e 2 na%e-in7
'EB>#
open c17
etch c1 into cn$%!er7
i c1noto$n* then cn$%!er :2 99997en* i7
close c17
ET# cn$%!er7
ECEPT>O#AE# OTAES TAE# raise-application-error(@50001=n error )as enco$ntere* @ =33SQLCODE33= @EO@ =33SQLEK"7E#D7
This function is called "ind5ourse. 9t has one parameter called name_inand it returns a number. The function will return the course number if it finds a match based on coursename. Otherwise, it returns a 77777.
Fou could then reference your new function in an $D& statement as follows:
select co$rse-na%e 4in*Co$rse(co$rse-na%e" as co$rse-i*ro% co$rses)here s$!&ect 2 =Kathe%atics=7
Oracle/PLSQL: Creating Proce*$res
9n Oracle, you can create your own procedures.
-
8/12/2019 Plsql Full Notes1
82/136
The syntax for a procedure is:
CETE .O EPLCE POCEDE proce*$re-na%e . (para%eter .para%eter" >S .*eclaration-section
'EB># exec$ta!le-section.ECEPT>O# exception-sectionE#D .proce*$re-na%e7
!hen you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:
. >## The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
2. OT# The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
/. ># OT# The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.
The following is a simple example of a procedure:
CETE O EPLCE Proce*$re p*ateCo$rse ( na%e-in ># ,archar5 ">S cn$%!er n$%!er7
c$rsor c1 is select co$rse-n$%!er
ro% co$rses-t!l )here co$rse-na%e 2 na%e-in7
'EB>#
-
8/12/2019 Plsql Full Notes1
83/136
open c17etch c1 into cn$%!er7
i c1noto$n* then cn$%!er :2 99997en* i7
insert into st$*ent-co$rses( co$rse-na%e co$rse-n$%!er",al$es ( na%e-in cn$%!er "7
co%%it7
close c17
ECEPT>O#AE# OTAES TAE#
raise-application-error(@50001=n error )as enco$ntere* @ =33SQLCODE33= @EO@ =33SQLEK"7E#D7
This procedure is called >pdate5ourse. 9t has one parameter called name_in. The procedure will loo+up the courseHnumber based on course name. 9f it does not find a match,it defaults the course number to 77777. 9t then inserts a new record into the studentHcourses table.
Oracle/PLSQL Topics: Creating Triggers
9nsert Triggers:
'E4OE >#SET Trigger
4TE >#SET Trigger
>pdate Triggers:
http://www.techonthenet.com/oracle/triggers/before_insert.phphttp://www.techonthenet.com/oracle/triggers/after_insert.phphttp://www.techonthenet.com/oracle/triggers/before_insert.phphttp://www.techonthenet.com/oracle/triggers/after_insert.php -
8/12/2019 Plsql Full Notes1
84/136
'E4OE PDTE Trigger
4TE PDTE Trigger
6elete Triggers:
'E4OE DELETE Trigger
4TE DELETE Trigger
6rop Triggers:
Drop a Trigger
6isableBnable Triggers:
Disa!le a Trigger
Disa!le all Triggers on a ta!le
Ena!le a Trigger
Ena!le all Triggers on a ta!le
Oracle/PLSQL: 'E4OE >#SET Trigger
3 *B"OAB 9%$BAT Trigger means that Oracle will fire this trigger before the 9%$BAT operation is executed.
The syntax for an *B"OAB 9%$BAT Trigger is:
CETE or EPLCE T>BBE trigger_name'E4OE >#SET O# table_name
http://www.techonthenet.com/oracle/triggers/before_update.phphttp://www.techonthenet.com/oracle/triggers/after_update.phphttp://www.techonthenet.com/oracle/triggers/before_delete.phphttp://www.techonthenet.com/oracle/triggers/after_delete.phphttp://www.techonthenet.com/oracle/triggers/drop.phphttp://www.techonthenet.com/oracle/triggers/disable.phphttp://www.techonthenet.com/oracle/triggers/disable_all.phphttp://www.techonthenet.com/oracle/triggers/enable.phphttp://www.techonthenet.com/oracle/triggers/enable_all.phphttp://www.techonthenet.com/oracle/triggers/before_update.phphttp://www.techonthenet.com/oracle/triggers/after_update.phphttp://www.techonthenet.com/oracle/triggers/before_delete.phphttp://www.techonthenet.com/oracle/triggers/after_delete.phphttp://www.techonthenet.com/oracle/triggers/drop.phphttp://www.techonthenet.com/oracle/triggers/