12:04
2024-12-10 13:02:57
6:53
2024-12-10 13:18:23
3:49:58
2024-12-10 13:29:28
9:46
2024-12-11 07:45:15
5:40:21
2024-12-11 09:08:49
5:22
2024-12-11 17:54:14
1:42
2024-12-11 18:04:44
2:05:58
2024-12-12 09:00:08
1:01:06
2024-12-12 12:26:38
3:59
2024-12-12 15:09:36
51:24
2024-12-12 15:24:00
8:06
2024-12-12 16:34:52
2:35
2024-12-12 17:17:14
3:05
2024-12-12 17:26:54
Visit the ORACLE PL/SQL Fundamentals course recordings page
WEBVTT
-->
yung sa iyo sir joseph ano pang error mo ah yeah remove the space yeah okay
-->
sir jerry okay na sir jerry you can clear that na tapos you can paste yung declare begin and ganun yan save that
-->
can you clear your ano your screen using the pencil the eraser yeah the lugs i mean on the script output line six
-->
one two three four five six first name salary ah yung space mo po dun sa employees that space first name sorry i put first name yeah
-->
dun sa line two mo po yung employees that
-->
talaga hey Miguel sir Miguel okay
-->
I'm looking at sir Miguel
-->
okay
-->
okay lang sir
-->
dun sa ad mo sir Miguel
-->
lagay ka ng new line dun sa first line mo yung second ad mo
-->
yeah
-->
the second ad
-->
pero pwede rin naman pala
-->
second ad enter
-->
tapos barihin mo yung kama
-->
tapos i-run mo siyang
-->
run mo yung script na yan
-->
okay so na-alter na yung ano
-->
na-alter na so you can remove it na
-->
as you can see dun sa script output
-->
na-alter na siya
-->
so we can remove
-->
hanggang dun sa ad salary
-->
pero you can you can verify kung
-->
ito type mo describe employees
-->
para ma verify mo na
-->
okay
-->
yan employees
-->
can you run that script
-->
select that run
-->
so may first name yung salary
-->
hindi nasama no
-->
so add natin yung salary so remove na yung ad first name
-->
palita mo na siya ng ad salary
-->
kasi kailangan natin siya
-->
later on sa
-->
second function natin
-->
si ma'am Christine okay ka na
-->
ma'am Christine okay
-->
okay so may first salary na
-->
so yung may first name and salary na
-->
you can try the code na
-->
first name salary
-->
okay looks good sir Jerry
-->
is this your first time
-->
to use Oracle SQL developer
-->
okay now I'm just wondering
-->
okay
-->
lumabas na yung output sa
-->
you sir
-->
sir Jerry
-->
may error ka pa din
-->
line 6
-->
oh okay okay
-->
so yeah let's correct that
-->
good eye
-->
EMP ID
-->
yeah
-->
tapos yung
-->
ano yung sa line 6 yung
-->
or mo po yung dalawang
-->
bar
-->
pagdikitin mo po yan yan yan
-->
yung dalawang bar yan sa gitna
-->
yan yan try to run it
-->
no data pound so EMP ID
-->
okay verify natin
-->
may 101 ba ang alam ko
-->
one and two lang ane so put one
-->
dun sa
-->
101 change the 101 to
-->
101
-->
try to run that
-->
sir Jerry naran mo na po ayun na
-->
completed na ngayon
-->
di ba hindi lumalabas
-->
kasi kalangan natin iset
-->
server output
-->
on
-->
eto po yung command
-->
ipipase ko doon
-->
set server
-->
output on
-->
sa taas po ng declare mo
-->
sir Jerry
-->
type mo po set server output on
-->
para makita natin na lalabas yung
-->
employee name tsaka employee salary sa taas po
-->
sa taas po ng declare
-->
yan
-->
set
-->
space
-->
server output
-->
space on
-->
tapos
-->
i-exercise po natin na
-->
uppercase
-->
yan perfect
-->
then on
-->
lagyan niyo po ng
-->
semicolon
-->
after na on
-->
to end na single
-->
na isang
-->
execution siya
-->
mabagal po ba yung server
-->
ahem
-->
yun
-->
nakita mo na may employee
-->
name and employee salary na
-->
pero walang laman no
-->
you know why
-->
kasi
-->
kasi yung
-->
yeah
-->
kasi din declare natin
-->
nakita mo yung declare capital V
-->
pero
-->
nung sinun natin small v
-->
addis sa tuto tayo mag
-->
debug
-->
difference we declare sa taas
-->
V capital
-->
tapos nag select tayo
-->
but nung sinun show na natin small v
-->
wala pa rin so
-->
B EMP name
-->
one moment
-->
one moment
-->
one moment
-->
one moment
-->
select first name
-->
salary B EMP
-->
B EMP name
-->
ah
-->
alisin na natin ay
-->
wait lang so from employees
-->
first name salary
-->
B EMP name
-->
employee name first name
-->
yun lumabas na yung name mo
-->
salary na lang
-->
one moment
-->
for
-->
that
-->
and back
-->
nice
-->
I
-->
see
-->
sir
-->
Jeremy
-->
figure that
-->
out
-->
I'm looking at
-->
your screen right now
-->
okay
-->
Sir Miguel, can you run the script?
-->
How about you, Mam Tin?
-->
Okay, I think you grasped the idea.
-->
So let's continue kasi marami pa tayong slides.
-->
Okay, so let's go back to our screen.
-->
So siguro yung try naman natin pa na pag no data found.
-->
So we will use no data found or too many rows.
-->
Okay, so now let's talk about handle scenarios where a query might return either no data or too much data.
-->
This is crucial when working with PLSQL because we often reply on queries that expect a specific result.
-->
And handling exceptions ensure our code remains robust and reliable.
-->
So in this example, we declare a variable BMP name to store the first name of an employee.
-->
Then we attempt to retrieve the first name from the employee's table for a specific employee ID, in this case 999.
-->
However, what happens if the employee's ID with 999 does not exist or if due to an error, multiple rows are returned.
-->
This is where exception handling comes into play.
-->
So yung tatandahan natin dito yung no data found saka too many rows.
-->
So can you try this data?
-->
Gamitin natin when no data found then DBMS output line no employee found with a given ID.
-->
One moment, let me paste that.
-->
So declare, we select.
-->
So now let's see what happens if the employee's ID with 999 does not exist or if due to an error, multiple rows are returned.
-->
So now let's see what happens if the employee's ID with 999 does not exist or if due to an error, multiple rows are returned.
-->
Are you looking at my screen right now?
-->
So I put it in our Google Sheet.
-->
So let's continue.
-->
So na get siyo na po yung no data found tapos too many rows.
-->
Yes, ma'am Christine?
-->
Sir, wait na po.
-->
Sige po yung one.
-->
Okay po.
-->
Mas maganda pag tinatry, no?
-->
okay
-->
nice
-->
sir joseph
-->
or everyone
-->
para matari natin yung too many rows
-->
output
-->
pwede tayo mag add
-->
pwede tayo mag add ng more records
-->
din sa employee
-->
para matari natin
-->
pero ganoon na may logic kung
-->
more than one yung
-->
na query nya
-->
din siya sa too many rows
-->
so let's move
-->
self explanatory naman
-->
so let's
-->
try this cursor
-->
this is more interesting
-->
oops
-->
i'll paste the code
-->
so try to copy it
-->
declare
-->
cursor
-->
select
-->
then begin
-->
then we open the cursor
-->
now we will try to do loop
-->
then
-->
we will fetch the
-->
cursor into B EMP
-->
salary then we exit the loop
-->
when EMP
-->
cursor
-->
is not found
-->
then we output it
-->
dbms output
-->
then we end the loop
-->
then we
-->
close the EMP cursor
-->
then end
-->
so can you try
-->
this on your end?
-->
cursor, EMP cursor
-->
select employee id
-->
since EMP id
-->
ang gamit natin
-->
first name salary from
-->
employees per
-->
kasi wala tayo department id no
-->
so mag add na lang tayo ng department id
-->
let me create
-->
department id
-->
so let's try to create
-->
department
-->
let's try to create a department
-->
table
-->
then
-->
let's insert
-->
data
-->
so create
-->
department
-->
table
-->
so i am
-->
pasting the script
-->
to the
-->
google sheet
-->
so we create a department table
-->
with
-->
department id
-->
and department
-->
name
-->
then we insert data
-->
on the department
-->
department id and department name
-->
then i think let's insert
-->
more data to
-->
employee id
-->
that we can use later on
-->
1,2,3
-->
let's say 4
-->
5,6
-->
first name salary department id
-->
values
-->
so we have
-->
to alter the table
-->
so that
-->
it will add the department
-->
id
-->
let me create
-->
jones script
-->
i am pasting it now
-->
in google sheet
-->
so after creating a table
-->
we have to alter
-->
since we already have employees table
-->
we have to alter
-->
the employees to add department id
-->
so
-->
practice natin yung pag add
-->
constraint
-->
so pwede tayo mag i-alter natin
-->
yung table
-->
na magadd yung constraint
-->
na yung foreign key ng department
-->
which is department id
-->
reference din sa department
-->
pwede yung f
-->
siguro para sa department id
-->
nalang din
-->
one moment
-->
or
-->
m
-->
m
-->
m
-->
m
-->
m
-->
m
-->
m
-->
Okay.
-->
How are you ma'am Christine?
-->
Sir?
-->
Yeah?
-->
Ano po dito?
-->
Ano po dito?
-->
Hindi makapag-copy paste from
-->
local to the desktop
-->
gawa no?
-->
Masin na Firefox po yung gawin.
-->
Try ko mag-anaw.
-->
Hmmmm
-->
Set server output on
-->
Set server output on
-->
Set server output on
-->
Declare
-->
Declare
-->
Sir Ray?
-->
Sir?
-->
Ok ka na no?
-->
I mean nakamove on ka na yun sa
-->
I mean nakamove on ka na yun sa NoDataFound
-->
NoDataFound
-->
Ok
-->
Ok
-->
Ok
-->
Kailangan mo na yung first step
-->
Kailangan mo na yung first step
-->
Tin, kailangan mo na mag-alter ng table
-->
Bago ka mag-constraint
-->
Bago ka mag-constraint
-->
Bago ka mag-altern ng department
-->
Bago ka mag-altern ng department
-->
Na sa yung code na sa
-->
Google sheet din
-->
So, alter table employees, add constraint
-->
FK department
-->
Foreign key
-->
Tin, space, foreign key
-->
Tin, space, foreign key
-->
Para makita mo din
-->
Dun sa refresh, dun sa connections
-->
Dun sa refresh, dun sa connections
-->
Pwede natin siya makita
-->
Pwede natin siya makita
-->
Yun, good
-->
So may table na tayo
-->
Matatrain na natin yung
-->
Yung sa cursor
-->
Let me fix the cursor
-->
Let me fix the cursor
-->
Let me fix the cursor
-->
Let me fix the cursor
-->
Let me fix the cursor
-->
Let me fix the cursor
-->
Let me fix the cursor
-->
Let me fix the cursor
-->
Everything in that
-->
Everything in that
-->
义 筒 筒
-->
Math
-->
Ayun, okay na pa siya yung monitor.
-->
Okay pa.
-->
Ayun.
-->
Okay na, sir Jerry, nung nakita ko may output ka na po.
-->
Sir Joseph.
-->
Sir Miguel, okay ka na po? Nakakapagpuri na?
-->
Okay na po. Siya pa, sir na po nago'y.
-->
Okay.
-->
Okay na din po si Sir Joseph.
-->
Oo nga po.
-->
Mam teen, okay ka na mam teen?
-->
Pwede mo numberin mam teen kasi may department table ka na po.
-->
Meron na po ba?
-->
Employ.
-->
Pwede niyong irine yung insert niyo po ngayon, tignan natin.
-->
I-select mo lang po yung, i-highlight niyo po yung code na irine niyo po.
-->
Already exists na po, so okay na.
-->
So sa insert na lang, buruhin niyo po lahat ng code niyo.
-->
Tapos iwan natin yung insert.
-->
So insert, violated parent key not found.
-->
Kasi kailangan nating mag, try muna nating mag-add ng department.
-->
May department dito na po ba?
-->
Table ka na po for departments.
-->
Ayun, so na-insert na.
-->
Try mga po insert po yan.
-->
Ayun, okay na po.
-->
Nice.
-->
May data ka na po.
-->
Okay lang yan.
-->
Next.
-->
Gusto po makita sa left side mo yung department table,
-->
Mam teen, sa left.
-->
Ang nakita ko ngayon employees e, no?
-->
Try nyo nga po muna i-refresh.
-->
Click mo po yung refresh button.
-->
Yan dyan po.
-->
Pag wala po, try nating mag-disconnect.
-->
I-right click natin yung sa taas ng tables.
-->
Right click.
-->
Okay na din sa local.
-->
Right click.
-->
Disconnect.
-->
Connecting local.
-->
Okay lang.
-->
Tapos connect ka ulit.
-->
Right click connect.
-->
Mag-assign ng password.
-->
Ash blue.
-->
Okay.
-->
Then tables.
-->
Yo, there you go.
-->
Nakita mo na po.
-->
May departments ka na.
-->
Perfect.
-->
Yan, taos try mo na po yung cursor.
-->
Yung declare cursor, EMP cursor.
-->
Na sa Google Docs po.
-->
Okay ka na, sorry.
-->
Parang...
-->
Gamitin natin, sir, EMP underscore ID.
-->
Instead na employee ID.
-->
Kasi dun sa employees natin,
-->
Hindi employee underscore ID yung ginagin natin.
-->
EMP underscore ID yung ginamit natin.
-->
As primary key.
-->
Okay, so yeah.
-->
Last 5 minutes to fix all the bugs
-->
and run the SQL.
-->
There you go.
-->
Okay, try to run that.
-->
Pwede na natin clear yung script output
-->
survey.
-->
Para pag nagirang tayo,
-->
hindi medyo confusing.
-->
Sir, okay na po.
-->
Okay.
-->
Yeah.
-->
Sir Ray okay na din, no?
-->
Okay.
-->
So let's move on.
-->
So I'll continue my presentation.
-->
So good thing at least nakapag hands-on tayo.
-->
Nakapag debug tayo,
-->
nakapag create tayo ng table.
-->
Nagamit natin yung declare,
-->
nakapag begin tayo.
-->
So dynamic SQL
-->
is a powerful feature in PLSQL
-->
that allows you to
-->
construct SQL statement
-->
dynamically at runtime.
-->
So let's walk through an example
-->
to see how it works.
-->
So in this example,
-->
we declare a variable v underscore query
-->
to hold our dynamic SQL statement.
-->
Okay.
-->
So the query is designed
-->
to fetch the first name of an employee
-->
based on their employee ID.
-->
Notice the placeholder column 1
-->
in the query.
-->
This is where we bind
-->
a value at runtime.
-->
So pwede tayo magbind ng data.
-->
So using the execute immediate
-->
statement,
-->
we execute the dynamic query
-->
into clause specify
-->
where the result should be stored.
-->
In this case,
-->
the variable will be v underscore result.
-->
The using clause binds the value 101
-->
to the placeholder column 1
-->
in the query.
-->
Finally, we use dbms output
-->
that put line display the employee's name
-->
showcasing how dynamic SQL
-->
provide flexibility to handle
-->
varying query structure.
-->
So this technique is especially useful
-->
for scenario where query structure
-->
or parameters need to be determined
-->
programmatically at the runtime.
-->
Siguro, try natin to.
-->
Let me paste this code.
-->
Sige pa.
-->
Dynamic SQL.
-->
It will be at the bottom.
-->
So we declare vQuery,
-->
workar,
-->
oops, sorry.
-->
Then we
-->
vResult, employees,
-->
that first name type.
-->
So natatandaan niya
-->
ano yung percent type.
-->
Ibig sabihin gagamitin niya yung
-->
data type noong column.
-->
Tapos vQuery colon equal.
-->
So nag-assign tayo ng dynamic
-->
SQL.
-->
So in this case,
-->
select first name from employees
-->
where since wala tayo employee ID,
-->
we replace it with EMP
-->
underscore ID equals colon
-->
1.
-->
Then execute immediate.
-->
So vQuery
-->
into
-->
vResult.
-->
Using 101.
-->
Tapos output natin siya.
-->
DBMS output
-->
that put line employee name
-->
tasyong result.
-->
Let's try it.
-->
Let's try it.
-->
Natry nyo po.
-->
So pag no data found,
-->
kasi wala,
-->
try using 2.
-->
Kasi if you look at the table,
-->
sir Jeremy,
-->
pwede mo pong i-click yung,
-->
tignan natin yung table ng
-->
employees mo
-->
maitrika.
-->
I think it's 2.
-->
Do you have 4?
-->
Can I check
-->
your employees table?
-->
Employees.
-->
Then click data.
-->
We try to replace it
-->
with
-->
4.
-->
Dapat ano ba sa iyo si John?
-->
Try to run that.
-->
Narun mo na po, sir Jeremy.
-->
Nakita mo si John na yung lumabas?
-->
Ah, 10.
-->
101. So dapat doon sa employees mo,
-->
may 101 ka.
-->
Try natin i-edit yung
-->
isang name doon ng 101.
-->
Punta ka po sa employees,
-->
sir Jeremy.
-->
Tapos i-edit natin yung
-->
ke Alice.
-->
Yung ke Jane, gawin natin 101.
-->
Yung ID nya,
-->
palitan natin yung ID nya.
-->
Tape natin 101.
-->
Enter.
-->
Tapos i-commit mo siya,
-->
yun nyo i-check.
-->
Try, run.
-->
Yung 4, palitan natin
-->
ng 101.
-->
Yung gagamitin natin,
-->
which is using for,
-->
gagamitin yung 101. So expect natin si Jane
-->
lumabas.
-->
Yun.
-->
Nakita niya po si Jane lumabas.
-->
How about sir Joseph?
-->
Ok na po.
-->
Ok na lahat.
-->
Nice.
-->
So,
-->
that is the
-->
select for
-->
Dynamic SQL.
-->
Ok na?
-->
Naggets nyo po yung logic nya?
-->
Yes.
-->
Nice, congrats.
-->
Ngayon naman, try naman natin
-->
mag loop.
-->
Practice natin yung loop.
-->
So, I'll paste the code
-->
in the
-->
Google Sheet.
-->
Ato naman,
-->
let's try the
-->
looping.
-->
So,
-->
we declare a variable.
-->
Type
-->
nametable
-->
of employees dot
-->
first name.
-->
Then, we declare
-->
vnames nametable.
-->
Then, we select first name,
-->
bulk, collect,
-->
into vnames
-->
from employees
-->
where department
-->
IDs stand. So, I think we need more data
-->
para mara natin to.
-->
So,
-->
mag loop tayo
-->
for i in
-->
one dot
-->
v underscore names
-->
dot count loop
-->
dbms
-->
put line employees name
-->
vnames i tapos
-->
end loop then end.
-->
I'll try to
-->
create more department
-->
more user on the department ID.
-->
One moment.
-->
Pero you can
-->
paste the code na.
-->
Okay, department ID 10.
-->
Okay.
-->
So,
-->
I'm going to
-->
create a new
-->
Okay.
-->
So,
-->
Okay.
-->
So, let's try to
-->
create more data.
-->
So, I put an insert.
-->
It's a 10
-->
items.
-->
Can you try to insert
-->
into your employees?
-->
Can you try to insert
-->
Can you try to insert
-->
Okay.
-->
Okay.
-->
So,
-->
Okay, nakita n'yo po na natrae n'yo po mag loop, gumawa na po.
-->
Okay, last 5 minutes.
-->
Okay na si Sir Ray, si Ma'am Christine. Nakapag-insert na.
-->
And tatrain na lang ni Ma'am Christine yung code. Nakapag-insert na si Sir Miguel.
-->
Okay.
-->
Okay, Ma'am Christine looks good.
-->
Yeah, I think all good. So let's continue.
-->
So the fundamentals of populating variable in PLSQL is variables that are essential part of PLSQL programs.
-->
So in PLSQL, nakita natin na variable must be declared before their use.
-->
Typically, we declare variables at the beginning of the block or within a declare section.
-->
So in our example, we declare the employee name, employee salary, which employee name bar chart, the data type, and employee salary and number.
-->
Tapos yun, so we also have a direct assignment using the colon or equal operator.
-->
So you can directly assign a value to a variable.
-->
Next is select into. This is one of the most common ways to populate variables.
-->
When pulling data from a table, the select into statement fetches data from database and store it in a variable.
-->
For instance, you can use select first name salary into employee name, employee salary from employees where EMP ID equals 101.
-->
So these methods allow you to dynamically assign values based on the query result.
-->
Both methods, direct assignment and select into, are essential tool for working with variables in PLSQL, enabling you to manipulate and utilize data effectively within your programs.
-->
So now let's explore the practical example how to use this method in different scenarios.
-->
So for example, looking at the code block, we select the query, we query retrieve the first name and salary of an employee from the employee's table with the employee's ID 1001.
-->
The into clause direct the retrieve values into the variables, employee name for the first name, then employee salary for the salary.
-->
It is important to note that select into requires the query to return exactly one row.
-->
If no matching record is found or if multiple rows are returned, PLSQL will raise an exception, kagayon nung ginawa natin kanina.
-->
This is why exception handling is essential when using select into.
-->
You need to manage scenarios where no data is found using the no data found exception.
-->
Multiple rows are returned which raises too many rows exception.
-->
Let's look how we can initialize and populate variables in PLSQL using default value expression.
-->
So you can assign default values to variables during declaration.
-->
This ensure the variable have meaningful initial values even if no, wala naman explicit assignment is made later in the program.
-->
So the example is employee name bar chart 50 so we put it unknown and salary number 0 then after that in the begin the code goes there.
-->
Variable can also be assigned using expression or calculation or function result.
-->
For example, employees underscore salary colon equal base salary times 1.1.
-->
So pwede ka magligay ng mga calculation or function or expression.
-->
This assign employees salary the result base salary multiplied by 1.1 which could represent a salary adjustment or bonus calculation.
-->
So by using default values and expression effectively, you can ensure your variables are always properly initialized and dynamically updated based on the program's logic.
-->
So let's move on the example.
-->
Let's try to do this.
-->
Let me paste this in the Google.
-->
So I paste it in the Google.
-->
So we declare empid number then current salary then increase rate then the new salary siguri babana natin.
-->
Tapos begin so we put comment so here we fetch the current salary so we select salary into current salary from employees.
-->
Where employee id equals employee id.
-->
Then we calculate the new salary so new salary colon current salary then we update the employee salary so update employees.
-->
Set natin yung salary equals new salary where employee id equals so this will be empid.
-->
Can you try this code in your end?
-->
See if you're able to change the new salary.
-->
Oh debug here.
-->
Select salary into current salary from employees.
-->
I think we don't have current salary column.
-->
One moment.
-->
See the error you're getting at?
-->
Let me paste the correct code.
-->
As you can see may error kasi yung empid.
-->
Let's try it.
-->
So issue ng unang unang code yung empid variable inside the work clause conflicts with the empid with the column from the employees table.
-->
So to fix that nagdagdag tayo ng v underscore sa empid.
-->
Verify nyo kung nagbago yung salary ng target natin na user.
-->
So kung wala yung 101, since sa employee table niya may one, dapat nagbago siya.
-->
Again pag walang lumalabas, don't forget the set server output on.
-->
Nakakita nyo po, every time you run the code, it adds by 10%.
-->
Can you try to run?
-->
Kasi walang output, new salary din declare mo ba sa taas?
-->
New underscore salary colon number.
-->
Ok naman.
-->
Ba't di lumalabas yung colon or date employees, set salary, cost me salary or empid?
-->
Yung current salary.
-->
Yun, sumabas na.
-->
Ok good.
-->
Good no? At least natututunan natin magdebag, macheck yung error.
-->
So row type naman.
-->
One moment.
-->
So 3pm, bibig tayo alit.
-->
250, 250, you want 250?
-->
250 ok, let's have a break.
-->
So let's explore the row type.
-->
So it's an attribute in PLSQL which is a powerful way to work the entire rows of data from a table query.
-->
So what is a row, percent row type?
-->
So percent row type attribute allow us to declare a record variable that can store an entire row of data.
-->
So pwede natin ilagay yung bang row ng data.
-->
This means that the variable can hold multiple columns with their respective data types matching the structure upon a table or cursor.
-->
So the syntax will be variable, name, space, table name, percent row type.
-->
So variable name is the name of variable you are declaring.
-->
Then table name, percent row type is the table or cursor whose row structure the variable will adapt.
-->
So ito yung example natin yun ipinsko sa ano.
-->
One moment.
-->
So yun.
-->
So yung row type.
-->
Mami ilalagay ko example.
-->
Ito ito natin pagbalik.
-->
So let's continue.
-->
So let's for example declaring variable with a row type.
-->
So let's look at the practical example of declaring a variable with row type to work with an entire row of data from a table.
-->
So suppose we have employees table with empid, phrase name, last name, department salary.
-->
So ito yung sample natin ng data for employees table.
-->
So here's how we can declare use a row type variable access data from this table.
-->
So let's try this before we do a break.
-->
I'll paste the code in the application of row type.
-->
So let me check.
-->
So we declare emp record employees row type.
-->
So we declare record variable.
-->
Tapos we begin.
-->
One moment.
-->
So we begin.
-->
We fetch.
-->
So we fetch the record into variable.
-->
So select asterisk into emp record from employees or empid.
-->
So check na lang kung anong meron kayo.
-->
Pero pwede 101 or 1.
-->
Tapos we can access na yung individual fields nila using the dot notation.
-->
Baban natin para mas madaling makita.
-->
So ito try natin really quick na gamit yung emp record yung buong data ng employees nilagay natin dito.
-->
So mas madali natin siyang ma-access na yung department ID nya, yung first name nya, tapos yung salary.
-->
So emp record nilipat natin dito tapos nilag natin into from employees or empid.
-->
She goes to 1.
-->
So dapat makapag-out po tayo.
-->
Can you try it now?
-->
Maybe my comment is wrong because of google docs.
-->
Doon sa fetch arrow or any comment, just put double dash.
-->
Tapos unline to ayun.
-->
Pag kinapipaser yung mali lang niya yung mga comment.
-->
Refresh.
-->
So do we declare emp record employees?
-->
Let me correct the code one moment.
-->
Last name must be declared.
-->
First name.
-->
So let me correct that.
-->
So this should be first name.
-->
And I put the correct syntax on the google docs.
-->
So lahat po ba nakapagrana?
-->
May error pa rin?
-->
Ayun, okay na yung kay Sir Jerry.
-->
Okay na po lahat nung Mr. Ray.
-->
Nakapagrana rin.
-->
I think we're good.
-->
So nintindihan niyo na po yung percent throw type, correct?
-->
Na pwede natin siyang ilipat dun sa isang variable.
-->
I think that's it.
-->
We can have a break.
-->
Balit natin is 3.30.
-->
Is that okay?
-->
Okay.
-->
Your stream's busy, guys.
-->
Yes.
-->
Yes po.
-->
Ngayon po hanggang.
-->
You want 30 minutes?
-->
Okay po.
-->
Let's resume 3.30, okay?
-->
Thank you po.
-->
Thank you po.
-->
Thank you po.
-->
Ika ko nang lasa ng tubig. Sa mga rin?
-->
Sa ano?
-->
Sa plastik.
-->
Hindi mas nasa.
-->
Tapos nga.
-->
Ito?
-->
Tapos nga tayo.
-->
Tapos ko lumawan ng...
-->
Ay, may nga pre?
-->
Kasi?
-->
Hindi...
-->
Hindi na dago kong papibali na chocolate.
-->
Chocolate.
-->
Tapos na.
-->
Tapos na.
-->
Tapos na.
-->
Tapos na.
-->
Tapos na.
-->
O, pwede.
-->
Tapos na.
-->
So,
-->
hindi nila.
-->
So,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
complex operations.
-->
So,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
so,
-->
oops,
-->
oops,
-->
oops,
-->
oops,
-->
oops,
-->
else if
-->
Nagwork po sa inyo
-->
Try to change value on the declaration
-->
Very high salary
-->
Moderate
-->
Try to increase the number of the salary
-->
Maybe put 60,000
-->
So it will say very high salary
-->
So ganyan maagcondisyon sa PLSQL
-->
If condition then else if
-->
Okay
-->
Ngayon mayroon ding
-->
Pano naman pag
-->
Case
-->
So using case statement adds conditional logic directly into a query
-->
So it evaluates condition and returns specific value based on the result
-->
Tapos yung decode
-->
Using a decode as alternative to case in oracle
-->
So oracle's decode function provides simple conditional logic within a select statement
-->
Though less flexible than case, it is useful for straightforward evaluation
-->
Like I'll paste something in here
-->
Para magamit natin yung decode
-->
So in this case MPID
-->
Sa atin department ID yung gamit natin
-->
So pag tan daw HR papakita nya
-->
Pag 20 sales
-->
Otherwise 20
-->
Can you try to run the code?
-->
So
-->
Nag-run po yung code sa inyo
-->
Nakita nya may HR, sales
-->
So do you understand po yung decode?
-->
So pag yung department ID kapag tan HR lalabas nya pag 20 sales
-->
Otherwise other yung lalabas
-->
Okay
-->
So yun yung purpose ng decode
-->
So using the case in a select statement
-->
So it says here when salary is less than 40,000 then low
-->
When salary between 40,000 and 60,000 then medium
-->
L size siya
-->
Tapos pag nag-case ka, laging may end
-->
So case end
-->
As aning pangalan? Salary category from employees
-->
So this is really straight forward and easy to understand how the case work
-->
So using if with select into
-->
So we declare a salary
-->
So select salary into salary from employees who are employee ID or MPID equals to 1 for example
-->
So if salary is greater than 40,000
-->
So ganun din yung value na makuha natin dito yun yung pagbabasahan
-->
So yung select nating salary nilipat dito sa variable ng salary
-->
Tapos yun yung lalagin natin ng condition
-->
So pag using decode naman, alternative to case
-->
So kung yung department niya at sales, lalagin niya sales department
-->
Kung HR yung nalagin natin human resource
-->
Otherwise, other yung ilalagin natin
-->
You can put more
-->
I think it's only two cases
-->
So case is add complex conditional logic directly in select statement
-->
So if we select into useful in PLSQL blocks for decision making based on query result
-->
Yung decode naman it very simple, straightforward, just alternative for the select
-->
So the key benefit of that is the decode is straightforward for basic evaluation, readability, and performance
-->
While decode is less powerful than case, its simplicity makes it a valuable tool for quick basic transformation in Oracle SQL queries
-->
Now let's try to do a summarize
-->
So basic syntax, the case statement in SQL is a very powerful tool for incorporating conditional logic directly into your queries
-->
So search case, it's evaluate multiple Boolean condition in return result based on the first condition that evaluates it through
-->
Then the key benefits of that is dynamic logic adds if-else behavior directly to SQL queries
-->
Or PLSQL blocks
-->
Custom categorization enables you to label or group data dynamically based on specific criteria
-->
Then versatility, it can handle both simple value matching and complex Boolean condition
-->
So this is the simple case syntax
-->
So case, then expression
-->
So when value 1, then result 1, when value 2, then result 2
-->
Pwede pa mag-add na marami, so when value 3, then result 3
-->
Else, pag wala nagfold din sa condition, it will go to else, which is the default result
-->
So in this case, a simple case, so we select employee ID and department
-->
So case department, sales, gagal siyang sales team pag HR human resource, pag IT then tech department, else other department
-->
So yung search case syntax naman is case when condition then result
-->
When another condition then result 2
-->
So in this case, when salary is greater than 40,000 then low
-->
Kapag yung salary between, so another condition, kapag nagtruto then medium
-->
Otherwise, pumunta siya sa default which is yung high
-->
Then for, don't forget to end
-->
Then as, ano yung magiging label niya
-->
Then yung case statement for salary number colon equal 45,000 and salary category datatype 20
-->
So we begin with, kumaga pwede nating ilipat yung result nito
-->
So nag case siya, when salary greater than less than 40,000 then low lalabas niya
-->
When salary between 40,000 and 60,000 then medium, else high
-->
Tapos pwede mo nang i-input yung salary category
-->
So again, dineclear nating dito yung salary category
-->
So don't forget to declare kapag ginamit mo siya dito
-->
So let's try this one really quick
-->
And since we have the data already, we can use that
-->
Or yeah, I think it's almost the same
-->
I'll just put it na lang, then you can try it later
-->
I'll just put it in the Google sheet
-->
Case statement
-->
So we declare the salary number, salary category, salary case, else high
-->
And then we input
-->
Na-paste ko na po, just in case you want to copy this code
-->
But you already have copy in the PDF
-->
So key point, simple case
-->
So best for matching specific values directly
-->
So yung nga, yung example nating dun is when 40,000 then low
-->
When 60,000 then medium, else high
-->
Search case naman is flexible in a therefore evaluating complex condition
-->
Yung example nating yung case, when salary is greater less than 40,000 then low
-->
Tapos yung other condition natin when salary between 40,000 and 60,000 then medium, else high
-->
So default result is yung else
-->
Now let's ensure that the result is returned even if no condition match
-->
It provides fallback logic
-->
So mahalaga din yung else
-->
Para baka may mga anticipated values na walang magfall na tru dun sa condition dun sa mga when under cases
-->
Then usage of SQL and PLSQL
-->
So versatile across queries for dynamic data categorization
-->
So effective in PLSQL blocks for procedural language and conditional assignment
-->
So I think the takeaway is the case statement adds valuable conditional board
-->
Logic to both SQL queries and PLSQL codes
-->
It allows for dynamic decision making
-->
Making your programs more adaptable and data processing more streamlined
-->
So now let's trap the errors
-->
How we can trap the errors
-->
When an error exception occurs in PLSQL block
-->
The block stop executing at the point of the error
-->
Control is transferred to the exception handling section of the block
-->
So you can define how to manage the error ensuring the program respond appropriately
-->
By using exception in PLSQL you can write a robust program that handle the errors gracefully
-->
Ensuring smooth execution even in unexpected situation
-->
Okay let's look at a specific sample of handling different types of exception in the next section
-->
So yun yung exception
-->
So it's an error of unexpected condition that disrupt the normal flow of the program
-->
Example including the missing data or division by zero or invalid operation
-->
Yung exception handling section naman is
-->
It's a dedicated part of PLSQL that begins with the keyword exception
-->
It specify how to handle different type of exception when they occur
-->
Pero si PLSQL may predefined exception siya
-->
Sample of that is no underscore data underscore pound and two underscore many underscore rows
-->
It trigger when a query returns multiple rows for a single row fetch
-->
Yung user defined exception
-->
It's a custom exception we define for a specific scenario in your program
-->
It's very useful for handling application specific errors that are not covered by predefined exception
-->
So with exception handling you can ensure that your PLSQL programs are resilient
-->
Responding effectively to errors without abrupt failures
-->
Now let's look example how to handle both predefined and user exception
-->
So please analyze this code
-->
So we declare the employee ID or EMP ID number
-->
Salary then we begin
-->
Select salary into salary from employees
-->
Or employee ID equals employee ID
-->
Then we output the line
-->
So we put here the word exception
-->
So we put exception when data no pound
-->
Sabi niya hindi niya nakita yung ID na yun
-->
So ito it's a predefined
-->
When too many rows then sabi niya more than employee pound with ID
-->
Or you can say when others then DBMS catches any other unexpected error
-->
So the structure is we declare begin
-->
So we know the declare begin and end
-->
Now we add another blocks with this exception
-->
So we say when exception name then the result
-->
Ano yung message natin or ano yung gagawin niya
-->
Will it revert the transaction or will it do something
-->
Same with this and this
-->
So the other one that is predefined is the zero underscore divide
-->
It raised when there's attempt to divide by zero
-->
Or invalid number when there's a data conversion issue
-->
At least malaman natin yung mga predefined then na pwede natin magami
-->
So example of that
-->
So we declare salary number low salary exception
-->
So begin if salary is 30,000 then raise low salary
-->
And if DBMS put line salary is acceptable
-->
Exception when low salary then DBMS put line error salary is too low
-->
So I think let's try it to try the exception sample
-->
I'll put the code in the Google sheet
-->
Trapping errors
-->
Let's really quickly try that
-->
So we declare the salary number we assign 20,000 value
-->
We declare low underscore salary exception
-->
So sabi na if salary is less than 30,000
-->
Gagawin niya raise low salary
-->
And if tapos po talagang sa exception
-->
When low salary
-->
When low salary
-->
Then output niya salary is too low error
-->
Okay so let's try that
-->
Then try to change the number
-->
So if you change the number it should say salary is acceptable
-->
Nakita niya pag hindi natin binago it's 30,000
-->
It says salary is too low
-->
Try to play with the numbers
-->
Yung raise yan yung word na statement is used explicitly to raise an exception
-->
So exception and PLSQL are used to handle errors or unusual condition during the execution program
-->
So to trigger the pre-defined exception we can use raise no data found for example
-->
Or raise my exception
-->
Then when my exception
-->
So parang niraise na to since when it's true the low salary
-->
Kaya niya ipapakita to
-->
Otherwise hindi niya mariraise yung exception
-->
Ito yung ipapakita niya salary is acceptable
-->
Okay po
-->
So raise application error
-->
So we declare salary number
-->
So if salary is less than 30,000
-->
So we can raise application error
-->
So it's built in as well
-->
Explicit in the PLSQL
-->
And we can have an error code
-->
Then a message salary below minimum required level
-->
And if then we in exception
-->
So if it is when others we can raise this built in error code
-->
Which I will explain in a bit
-->
So yung SQL ERM is a function to retrieve the error message for the exception that occur
-->
So it's a built in function that returns the error message associated with the most recently raised exception
-->
It is typically used in exception handling to provide meaningful feedback
-->
Or lagging about the error that occur
-->
So when others block catches the raised error and display the custom error message
-->
So the custom error messaging provide specific and meaningful error message tailored to your application requirements
-->
The error numbers allows you to categorize user defined errors using a unique error number range
-->
Then the debugging support the SQL ERRM makes it easy to log or display error message for troubleshooting
-->
So using raise application error ensure that your PLSQL program enforce business rules clearly and communicate errors effectively
-->
So let's try to move on the internal errors
-->
So internal errors in PLSQL are the unexpected condition or system level issues that occur during the execution of program
-->
You know this error often stem from database malfunction, maybe resource constraint or underlying system problems
-->
So let's explore common types of internal errors
-->
It's good that now we know and we identify, oh this is more on server or this is more on my error
-->
So we know, so first is the ORA00600, it's an internal error
-->
Indicates that a serious issue with the Oracle kernel
-->
So if you're the one who is managing the server, it's really critical
-->
It's a low level system error that typically request Oracle to support and diagnose and resolve
-->
Then the 4031, it's meaning insufficient shared memory
-->
So when Oracle cannot allocate memory from the shared pool especially if you're using the cloud
-->
Then common cause include insufficient memory in the shared pool or higher memory demands from SQL statement or PLSQL blocks
-->
Then the 1555, meaning snapshot is too old
-->
You know triggered during long running, read consistent cursor, Oracle cannot retrieve a consistent snapshot of the data
-->
Then the 01000, it's a maximum open cursor exceeded because it's such limit
-->
It's occur when a program exceed the database configured limit per open cursor
-->
Commonly caused by not closing the cursor properly in the code
-->
So kapag hindi natin kasi clean ocean cursor, nakabukas lang siya, nasa memory lang siya
-->
So pwede natin ma-receive yan kung maraming nakabukas or yung iba hindi nagsasara or hindi nagsara sa code
-->
So ma-mari-receive natin yan
-->
So sa system level nature, so the internal errors usually indicate system level problems or misconfiguration that require investigation beyond the application logic
-->
Preventive measure na pwede natin gawin, addressing this error often involves optimizing system resource, improving query performance or tuning the database parameter
-->
So pag yung ano naman, 600 is typically require Oracle support for resolution
-->
So understanding these internal errors help us identifying the root cause of critical issues implementing preventive measures
-->
Next let's discuss or mitigate and troubleshoot this type of errors effectively
-->
So in this example, we use sql errm, it returns the error message associated with that exception
-->
So as I said, it's a built-in function and also we use when others, it capture all the exception that are not explicitly handled by specific exception clauses
-->
It's useful for addressing unforeseen error including internal ones like 600 or 4031
-->
So the effective error handling includes not only catching error but also lagging them
-->
So pag nakatch natin sya, maganda ma-lag natin sya for diagnosis and resolution
-->
So by capturing error details, you can review and address issues systematically
-->
So let's explore an example of plugging internal errors
-->
So we can insert in the database, for example into error lag, the error code, error message, error time
-->
So the values is sql code, then the message sql errm and error time the system date
-->
Then we output antenna error correct has been logged
-->
So this is the suggestion how we can log the error
-->
So lagging errors to database table ensure that internal issues are recorded for analysis and resolution
-->
Making your PLS code programs more maintainable and reliable
-->
Next we'll discuss the best practices for designing and managing error lag table
-->
So when to seek the oracle support
-->
While internal error handling PLS code is robust, certain critical errors require expertise of oracle support for resolution
-->
So for critical errors like for example OR8-00600, it's recommended to consult oracle support
-->
These errors often involve deep technical troubleshooting
-->
It might be issue with the oracle kernel or underlying configuration, maybe misconfigured database parameter settings
-->
Or maybe you have patches or updates, problems that require software patches or upgrades
-->
And lastly is hardware limitation, maybe the resource already exhaust or constrained that may impact database performance
-->
So in summary we have internal errors, we have common internal errors
-->
Handling internal errors, use the when others
-->
To catch all the exception and log details for further investigation
-->
Then what other you can do is consult oracle support because they are expert in their server's database
-->
So as they often require advanced solution beyond application level troubleshooting
-->
So again the SQL code retrieves the numeric code of the error
-->
Then the SQL ERM retrieved the error message associated with the error code
-->
So in this case when no data found, we can put the code in the error message to get more information why we have that error
-->
So these are the example of the built-in in the PLSQL
-->
Meaning integrity constraint violated, cannot insert null, existing state, the package has been discharged
-->
Unable to allocate shared memory, so better if you have this on your side to check the error code
-->
So again how do we log the SQL code in SQL ERM
-->
So this is the code, so insert into the error code message and system date
-->
Kapag no data found, so when selecting the statement of no errors from table or view
-->
So it does not occur with normal select queries but specifically with select into we expect exactly one row of data
-->
So here we have select salary into salary for employees
-->
So kapag wala siyang nakita when no data found, it will fire this message
-->
Pwede rin tayo maglagay na kapag wala siyang nakita, ibabalik niya yung default values ng salary
-->
So don't forget the end, so begin, if you have begin you always have an end like this
-->
Begin and end then you can have exception that when data no found itang gagawin niya
-->
So you can use the no data found to control the flow of your program
-->
Such as inserting a new row if none exists
-->
So for example in this case nag select siya but walang na found
-->
So it will just insert the new data in this case for example
-->
So then making it output so inserting default record with salary 30,000
-->
So the purpose of no data found help handle cases where select into retrieve no rows
-->
Tapos we can catch the data found in the exception block to avoid errors and apply custom logic
-->
So kapag walang nakita what will you do?
-->
Then the application it display message assign default values or trigger alternative action when data is no found
-->
So step 2 user define exception, first we declare the exception
-->
So declare the exception in the declare section of your PL SQL block
-->
Then we raise the exception, use the raise statement to trigger the exception when a specific condition is met
-->
Then we handle the exception so add an exception handling block to manage the error when it's raised
-->
So you can say when custom exception kapag nag raise siya ng custom exception dito siya papasok
-->
Don't forget to declare it, so custom exception exception so it's very important to declare
-->
So baka nakalimutan niya i-declare tapos nag-run ka nito so mag-error yan
-->
So you have to declare it para magamit mo siya dito at dito
-->
So example of other user define exception, so salary number
-->
So again we declare low underscore salary variables as exception
-->
So if you know your salary is lower so we raise natin yung lower which is ginamit natin dito under the exception block
-->
So when low salary then just make it error or if you have other logic that you want to put
-->
Nag-gets po
-->
Thank you po
-->
Using user define exception with raise application error
-->
So raise application error produce allows you to define custom error codes and message for user define exception
-->
This provides standardized and controlled way to handle unique error scenarios in your PLS-CL program
-->
So let's explore how it works
-->
So yung syntax natin is raise underscore application underscore error
-->
Tapos yung error number comma error message
-->
So yung error number is a custom error code in the range of negative 20,000 to negative 20,999
-->
Then the error message display when the exception is raised
-->
So the advantage of that it has a clarity
-->
Inya yung pinakita natin
-->
Let me paste the code here
-->
You can try it later on
-->
I'll paste it in the google
-->
Or I think I already have
-->
User define exception
-->
By the way tomorrow we have multiple choice quick exam 30 items
-->
So let's move on
-->
So the advantage of that is
-->
Go ahead
-->
Okay so the advantage of that is you have the clarity, the control and the standardization
-->
So I think using raise application error with user define exception ensure that the PLS-CL programs
-->
Communicate errors effectively making debugging and application handling more intuitive and standardized
-->
So pa na ba natin kagimitin yung raise application error
-->
So check if salary is below the threshold
-->
So we use the raise application error yung error code
-->
So again from negative 20,000 to 20,999 ang pwede natin gamitin
-->
Then salary is below the minimum required level
-->
So pag nag error siya dito siya papasok
-->
When others then dbms put line error tapos yung error message
-->
So again yung error number negative integer between negative 20,000 and negative 20,999
-->
It's a reserve for user define errors
-->
This range help differentiate custom error from oracle predefined ones
-->
Then the error message a descriptive string up to 2048 character that explain the error
-->
So ang key points natin yung raise application error can only be used within the PLS-QL blocks
-->
It's typically employed in begin or exception section
-->
Then execution flow nya when invoke it terminates the execution of the PLS-QL block
-->
The error code message are returned to the calling environment making them accessible for further handling or logging
-->
So ito yung sample syntax natin
-->
So here nag raise siya ng error code
-->
So ang magiging code nito is yung negative 20,001
-->
Tapos yung error message natin ito yun
-->
I think let's try this
-->
Let's try really quick
-->
Para makita natin siya
-->
I'll paste it now in the try this raise application error
-->
Okay I pasted it
-->
Try to paste or try to check
-->
So if salary ang gagawin niya yung raise nya and if put line tapos exception when others
-->
Salary is greater than or less than
-->
One moment let me fix the code
-->
Okay I paste the correct code in the Google sheet
-->
You see the error code it change
-->
Then try to change the 20,001 to negative 20,002
-->
You see the error code and error message as well
-->
Na-check nyo pa
-->
Try to change the error code to negative 20,003
-->
So in your company you can have documentation that hey if it is 20,001 this is the equivalent something like that
-->
Okay so let's move on
-->
We have 30 minutes more for today
-->
So example of raise application error in business logic validation
-->
So here we declare account balance, withdrawal amount
-->
Then if withdrawal amount is greater than account balance then raise application insufficient for request withdrawal
-->
Then account balance is account balance minus withdrawal
-->
So pag successful siya saka niya lang ibabawa siya account balance otherwise mag gradation error
-->
Which is this one
-->
But kung pumasa siya if it is false then gagawin siya to
-->
Babawasan niya account balance then display the output
-->
Using application raise application error in stored procedure
-->
Okay so here we create or replace procedure
-->
Check age as begin
-->
So if age is greater less than 18 so mag rais application error
-->
So here we just raise it but we don't have exception
-->
So we can do that in creating procedure
-->
So it helps you to create meaningful user friendly error message for unique application condition
-->
Improving your code
-->
How about when others to catch non-defined errors
-->
So here we try to 100 divide by divisor which is 0
-->
So the when others is placed at the end of exception block
-->
It will catch any error not handled by other specific when clauses allowing it to log display
-->
Or handle the error in a standardized way
-->
So kapag hindi pumasa dun sa ibang mga when
-->
So dun siya pumunta sa when others
-->
So it's better a good practice to have when others
-->
Then parataos pwedeng ilag dito yung error sa database
-->
Eto naman yung paglalag natin no non-defined errors
-->
But of course you have to create your table error log for it to record into your table for all the errors
-->
So again sql code returns a numeric error code associated with exception
-->
Then the sql erm is the message
-->
So best practice when others use sparring license when other cache owner using mainly as a last resort of the error handler
-->
Log and re-raise in complex application log the error and re-raise it using raise
-->
So the error can propagate up to higher level handlers
-->
And provide context ensure meaningful error message or log to makes debugging easier
-->
So eto yung sample natin ng logging and re-raising the error
-->
So we declare device or numbers colon 0 result number
-->
So pag nag-error siya pumunta siya dito then ir-raise niya yung error
-->
Why use pragma exception in it?
-->
So pragma exception in it is a directive that allow you to associate a user-defined exception with oracle error code
-->
This enable siya to handle specific oracle errors as name exception in your code
-->
Making your exception handling more readable and organized
-->
So without the pragma exception in it you can only handle oracle errors code using the when others
-->
Catch all or by directly comparing sql code by using pragma exception in it
-->
You assign a meaningful name to specific oracle error code
-->
Allowing you to manage it with descriptive user defined exception
-->
So let's say you want to handle the 1400 error
-->
Which is raised when a null value is inserted in the column that cannot be null
-->
So here we declare the exception we associate with oracle error using pragma exception in it
-->
Handle the exception in the exception block
-->
So here's the example
-->
So we declare null value error exception
-->
This is the step one
-->
Then the step two is we associate with ora 1400 the null value error
-->
Minus 1400
-->
So we attempt to insert a null value into null column
-->
So for example we declare we say to the table that first name should be not null
-->
And we're trying to insert a null
-->
So when we do this it will go to the exception
-->
Then it will go or fall into this specific error
-->
So alongside this main column it is important to follow best practices for the film
-->
Oops sorry where should I have here one moment
-->
So handling multiple errors with pragma exception in it
-->
So we declare the null value error exception then unique violation exception
-->
Then we put the pragma exception in the null value error as negative 4800 error code and unique violation negative one
-->
So we insert employees employee ID for his name
-->
So if the error is null
-->
So here's the message of attempting to insert null into null column value
-->
Or if the unique violation was triggered
-->
It will do the when unique validation it will say duplicate value found on the unique constraint
-->
Kasi yung employee ID is primary key and it should be unique
-->
So let's begin insert
-->
In this example we have the commit statement
-->
Am I correct one moment
-->
By default the commit statement is used to save changes made during a transaction permanently
-->
So ito yung code natin we insert into employee is the value
-->
So this commit function ensure that the new employee record is saved permanently
-->
So yung auto commit kapag naka off hindi siya mas save unless you put this commit
-->
Then the rollback kasi minsan we have to rollback all the changes especially in financial transaction
-->
If like error in the middle or if there's any discrepancy
-->
So the rollback command undoes all changes made in the current transaction
-->
So reverting the database to its previous state
-->
So it's very helpful when error occurs or when you want to cancel a series of operation
-->
So example of that is when we update employee
-->
Sinat natin yung salary niyo ng 65,000
-->
Dun sa employee ID na 10005
-->
Then we rollback
-->
The rollback command undoes the update operation so the salary remains unchanged
-->
So let's try to do the commit and rollback
-->
So let's try to code this one moment
-->
I'll put that in our coding
-->
Let's try the commit and rollback
-->
So we begin at inserting new employees
-->
So in our case it's employees and EMP ID
-->
Then first name
-->
Wala pa tayong last name eh
-->
We only have department
-->
ID then salary
-->
Tapos i-update natin yung employees
-->
Is set natin yung salary niya
-->
To 70,000
-->
Where EMP ID
-->
Is 1002
-->
Tapos na try natin i-commit
-->
Pag may error pwede natin i-rollback
-->
So try natin gawan siya ng error
-->
For example we will insert
-->
Or here in the update
-->
I-try natin mag-update pero wala yung ID
-->
So try natin if mag-rollback siya
-->
If it is throw an error
-->
Rollback
-->
I think I'll make it down one moment
-->
Ya it's on the last page
-->
Then after the rollback
-->
It doesn't change the rollback
-->
Kasta nandito rin natin i-insert is
-->
ID and department ID ba
-->
So try natin palitan yung
-->
Wala tayong last name
-->
Wala tayong last name
-->
Tapos yung department ID natin is 10
-->
So yung nasa ba yung ginagawa ko yung tama
-->
Okay
-->
So let's continue
-->
We have like less than 20 minutes
-->
So the key difference with
-->
Oops
-->
So the key difference is commit
-->
It saves changes permanently
-->
It saves all changes made in the transaction permanently to the database
-->
Once issued
-->
Changes cannot be undone
-->
Once na nakomit mo di mo nang babalik
-->
It's commonly used after successful transaction like insert, updates, or delete
-->
Rollback and that's all changes made in the transaction
-->
Restoring the database to its previous state
-->
Useful for handling errors or canceling transaction that did not meet expectation
-->
Ensure that no unintentional changes are saved
-->
May isa pa yung locks
-->
Yung both commit and rollback release any locks held by transaction
-->
Bring resources for other operation
-->
For example nga yung pag-withdraw
-->
Kalangan na check lahat bago ma-initiate yung commit
-->
So structure of nested block
-->
So we have the declare
-->
Declare number of variables
-->
Then we begin
-->
Then we declare the inner block declaration
-->
Begin
-->
Then inner block code
-->
Then we do the exception
-->
So if may error dun sa inner exception
-->
Dito siya papasok
-->
Otherwise kung may error sa loob na to
-->
Dito yung exception nya
-->
So outer block exception handling
-->
So basic nested block
-->
So outer variable
-->
We declare number
-->
Then we say this is the outer block variable
-->
So we declare inner variable number 50
-->
Then we output the inner block variable
-->
Then we output the accessing outer variable from inner block
-->
Kasi accessible naman to sa loob nya
-->
So this is how you do nested block
-->
Kasi you have declare declare
-->
So pwede ka pa magkaroon nyan dito ng declare
-->
So nested block with local exception handling
-->
So nag declare tayo
-->
Then we begin
-->
Putline iting starting outer block natin
-->
Then another nested
-->
So nag begin tayo ulit
-->
Din declare natin yung inner divisor resolved
-->
Then nag begin tayo ulit
-->
This will cause division 0 by error
-->
Tapos pag may error dito
-->
Dito siya papasok
-->
Tapos after ng begin may exception siya
-->
Exception then end
-->
Tapos may end pa rin yung begin dito
-->
So dito yung exception nya
-->
Yung sa labas ng o sa loob ng begin nato
-->
Kaya make it sure kapag nangcode kayo
-->
It's properly indented like we discussed a while ago
-->
Tapos all caps yung DBMS yung mga keywords
-->
Para madali siyang mabasa
-->
Kasi pag marami ng code
-->
Medyo mahirap naman mag read
-->
Tapos make it sure may proper comment
-->
Especially sa mga functions sa mga conditions
-->
Using variables across nested blocks
-->
So we declare the salary number
-->
So we begin with starting block initial salary
-->
Tapos nag declare tayo increment
-->
So ang pinagusapan natin dito yung variables na salary
-->
Na magigamit natin dito
-->
That we can use across the block
-->
So variables scope yan
-->
So variables in the outer block are accessible within the inner block
-->
But inner block variables are local to that block
-->
Okay
-->
So local exception handling
-->
You can handle a specific exception within the inner block
-->
Without impacting the entire outer block
-->
And also data sharing
-->
So outer block variables can be modified in nested block to share across scope
-->
So nested blocks are a powerful feature in PLSQL
-->
For structuring code, managing error codes, and controlling variable scope
-->
Helping you to build clear and robust PLSQL program
-->
So yeah, so iteration and looping
-->
Siguro, siguro let's try this
-->
Para tomorrow we can have the new topic
-->
Which is iteration and looping
-->
So let's try this
-->
Let's try to play with using variable in the nested blocks
-->
Let me put that in the Google sheet
-->
Step 3
-->
Okay so we declare number salary
-->
50,000 begin
-->
Then we declare
-->
Then we increment
-->
Number
-->
Then we begin again
-->
Okay let's try this block
-->
Did you try it guys?
-->
Let me know if you run any error
-->
Try to play with the number as well
-->
Try to change the number
-->
See if it is increment
-->
See if the operation does
-->
It's staying
-->
So you should see the starting outer block, initial salary, inner block updated
-->
Then the final salary
-->
Nag-run po sa inyo
-->
Nice
-->
Yes
-->
Siguro before we end
-->
Do you have any more question, clarification
-->
So our topic for
-->
Go ahead
-->
Okay one moment
-->
Thank you po
-->
Yeah
-->
Ayun na po
-->
Nagbabago siya pag na exit ko
-->
May nagbago po? May nag-add po ba ng question before?
-->
Parang wala naman o
-->
Wala naman ako nakita
-->
But yeah here's the
-->
When I stop the presentation or accidentally stop
-->
Yun nagbabago siya ng URL
-->
Go ahead po
-->
Add na lang yung question sa laptop namin
-->
Siguro evolve na lang yung question
-->
Yes, yes please
-->
Tapos i-
-->
Okay
-->
Then you have your PDF
-->
For the entire tutorial
-->
You can do advanced reading
-->
Then yeah I would love to hear your questions
-->
Awesome, awesome, awesome
-->
Yeah thank you
-->
Yeah so if you have any more question
-->
I think we can call it a day
-->
Thank you so much for your time guys
-->
See you tomorrow at 9am
-->
Thank you so much
-->
God bless po, bye bye po
-->
Thank you everyone
-->
Tapos dito na lang po yung
-->
Photo ng G-Drive ang sabi level na si recording
-->
Thank you, see you tomorrow
-->
Okay ma'am
-->
Okay na po, thank you po
-->
Okay po
-->
Okay po
-->
Okay po
-->
Okay po
-->
Okay po
-->
Okay po
-->
Okay po
-->
Okay po
-->
Okay po
-->
Okay po
-->
Okay po
-->
Okay po
-->
Ayan! Ayan na ba? Ayan na ba?