ORACLE PL/SQL Fundamentals | Videos

language: TL

                  WEBVTT
Right?
Select statement.
Yeah, so...
You want to try this?
I mean, we can...
I already prepared
an employees table
in the database there.
All of you,
can you open the SQL
developer tools?
I already put
a shortcut in your desktop if it's not
already open.
Yeah, just click run.
Thank you, Christine.
How about, Jeremy?
I see Joseph.
Are you accessing
individual desktop or
you prefer to access one desktop
in your conference
or something?
Individual
Yeah, yeah.
And I think you have access, right?
How about, Miguel?
Do you have access right now?
Apo.
Can you open the SQL developer
right now?
It's the middle icon.
Nakabukas na, sir.
Yung sayo po, sir Miguel?
Ayos po.
Nakabukas na po yung...
Hmm...
Ang nakikita ko
right now sayo, sir Miguel,
is yung command prompt.
Yung CMD, yung terminal.
Okay.
So, can you click local?
See if it is loading.
For our password,
please type ashblue.
Jeremy has.
What is your nickname,
Jer?
Jerry.
Can you type
ashblue as password?
A-ashblue. A-S-H-B-L-U-E.
A-S-H?
Yeah.
B-L-U-E. Blue.
Okay. So, that's...
Oops. You closed the error,
sir Jerry.
So, for example,
in that case, so, we're trying
to debug here.
How can I share...
Yeah.
For sir Jerry,
how can I share...
Let me share, okay.
I'll reshare my screen
one moment.
Stop share.
Let me know if you can see my desktop.
Right now, I'm looking at
sir Jerry
account. As you can see,
the error is
ora-2541.
Meaning...
Meaning, the server
is not running.
Okay.
To fix that,
can you open the terminal, sir Jerry?
Yeah, command prompt. Yep.
Then, let's
check your hostname. Type
hostname. Enter.
Okay.
So, we need to match the hostname.
Can you
open a folder, please?
Okay. You see the
on the right side, the
tnsnames.ora,
can you open that file? Right click on
that. Then, let's
open that in with code.
With Visual Studio Code.
There you go.
As you can see,
the reason why it's not running,
first is the server,
the database
server is not
running. It is stopped.
One
moment. Let me grab the code.
lsn.
So, we have
a few ways to fix
that. So, we're gonna
replace... You see the
on the line 6, the ray
lhnw-resize.
We have to replace every
instant of that with your hostname,
jeremias-93dn.
Okay. Perfect. Now,
click save.
Ctrl-S, please.
Sir Jerry.
Okay. Awesome. We can close
that now.
Then, we
need to... Sorry. Go back
again to the folder, please.
Let's edit the other one,
the listener.ora.
Yeah. Let's edit that.
That's the last piece we need.
Right click and edit in Visual Studio Code.
Okay. You see... Yeah. There you go.
Thank you.
Then, I send... How can I
send?
On the...
Dito sa zoom, may send ako na
Google Sheet.
Can you click that po?
Yung Google Sheet.
I'll paste the code
to start the server.
Sorry. Sa zoom po, yung Google
nito. Sa...
Sa...
Sa zoom po,
I send them a link.
I see
two people already joined.
Ayun Google Numbs po.
Yes po.
Okay.
So, lahat na po ba? Na-update na natin
yung hostname natin.
Now, we need to start
using the command
lsnrctl
space start.
You... Nakita mo pa,
Sir Jerry, yung lsnrctl
space start.
There you go.
lsnrctl
start.
It's like shorter
for listener control.
ctl
Ito po yung
itatipe.
lsnrctl
start.
Space po.
Sir
Joseph.
Space start.
Okay. So,
good thing yan na nakita natin yung error.
So,
ibig sabihin yan, kailangan natin
irstart yung computer.
So, pag na change yun na yung
hostname, we have to
restart your computer.
Tapos, iraran natin ulit yung
command na lsnrctl
space start. Yes po?
Ano pang name mo po?
Yun nag-speak?
Miguel Miguel. Okay.
Unable to open.
lsnrctl
That's it. Okay po.
So, lahat po nung may ganong error,
please restart your computer po.
We need to restart the computer
para magtake effect yung hostname
na inedit natin.
Yes, please restart.
Thank you.
Okay. So, while waiting,
while restarting, if you can still
see my screen,
so, itatari natin
itong ecopy paste.
So, since this is sa presentation,
ipipaste ko yung
dun sa
interactive coding natin na
slides.
So, line 87.
Let me check your computer.
Google Chrome.
Okay, nanggulis
start pa din.
So, let's wait for it.
So, yun. So, while waiting
for the computer to restart,
so, in PLSQL,
the SELECT statement is used
to retrieve data from database
table. So,
yun. This statement can be used in
different ways depending on the requirement.
So, selecting data
into variables.
So, yung simple SELECT natin
pag may SQL lang,
SELECT into FROM,
while kapag sa PLSQL,
we again yung
naaral natin, DECLARE,
BEGIN, tapos END.
So, we declare variable,
we begin
where we transact,
where we query, then we can
output the
variable.
Okay.
So, let's try.
Marami pa naman tayong code nito try, pero we can
proceed muna
while we restart the computer.
So,
eto naman is handling
kapag no data found
or too many errors.
Pero maganda ma-try din talaga natin.                

on 2024-12-10

language: TL

                  WEBVTT
Okay, awesome.
So now,
can you try to open the SQL Developer?
Then click Local.
Let me share my screen here.
You should see, you should be able to click this Local folder here.
Pat may lamang siyang employees.
Please open the SQL Developer tool.
Tapos try natin itong first code nato.
I'll paste it in the Google Sheet.
So nakikita natin may table na.
You can use the filter like this.
Please look at my screen.
Hi, si Sir Ray may issue pa doon sa ano.
Ibig sabi niyo you're not connected po sa database.
Ash blue?
Okay.
So yeah, that's a problem because the hostname is quite different.
Can you open Command Prompt, Sir Ray?
Or Terminal?
Yeah, Command Prompt.
Okay, open.
Then type hostname.
Nabago niyo po yung hostname kanina, sir.
Can you click the folder, please?
Let's check if nabago niyo po talaga.
Yes, there you go.
Click that.
Then let's try to edit that listener.
Open with code.
Yun po, yung line 19, it should be Ray-F3LZ.
Yung nakakonek na po, you can refer to our Google Docs.
Tapos pwede niyo po i-try yung code doon.
While I'm helping Sir Ray.
Napalitan mo na po, sir.
Nasave mo na po.
Sir Ray.
Perfect.
Okay, you saved that.
Yung isang files po, na-edit mo na rin po.
Yun, yan yung TNSRA.
Okay, that's good.
Now you have to restart your computer again if the command will not work.
But yeah, let's try it.
But I think you have to restart it.
Yeah, please restart po again.
Thank you.
Sorry for that, sir.
Yun, as you can see, nakita ko, meron ng ano no?
Ah, hindi ata.
Okay.
Yan.
So magtatry tayo mag-debug.
So can you try that command, Sir Jerry?
Error, no?
Kasi ngayon, practice natin.
Try tayong mag...
Try naman natin gamitin yung SQL+.
So tingnan natin yung employees table.
Ano bang laman niya?
So pwede mong burahin ulit yan, sir.
Tapos, try natin i-describe.
I-apply natin yung natutunan natin ganito omaga.
So remove that.
Pero gagabitin natin later on.
So type mo describe employees.
Yung error niya, sabi niya walang first name, no?
Sititingin natin kung may first name ba talaga yung table.
So let's run.
Ano po ang laman niya?
May laman po bang first name?
Wala po, no?
Wala.
So we will alter the table.
Alam niyo na po paano mag-alter ng table?
Yan.
Para hindi po kay mahirapang maghanap dyan ang table,
pwede niyo po iklik yung filter
dun po sa taas, yan sa tables.
Click nyo po.
Tapos click nyo yung filter icon.
Tapos type niyo employees.
Indicate yung isang filter lang po.
Yan, employees.
There you go.
Pwede tayong mag-add tayo ng first underscore name at saka salary
para mag-run yung code natin, example code natin.
But I'll provide the code sa Google Docs
if you want to do it in the terminal
or run the SQL code.
So alter table employees,
add first name,
we declare bar chart.
Ito po yan na sa Google Docs.
Mag-add tayo ng first name,
tapos mag-add tayo ng salary na decimal.
Pwede na natin itong maran.
Hindi na siya mag-result sa error.
So Sir Joseph, it's running.
Sir Miguel, may error ka pa rin, Sir Miguel.
Wait, wait, wait.
Restarting muna.
Restarting, okay, okay.
No problem.
Pero na-change mo na ng maayos yung hostname.
Okay, okay.
Tapos si Sir Joseph nag-altern na.
Okay, alter employees, add first name.
Okay.
Kama.
Okay, so alter table employees,
add first name, add salary.
Can you...
Natry mo na pang error.
Error, alter table employees,
add first name, employees.
Anong error niya?
Pwede lang pa.
Try natin.
Ah, okay.
Sir Jerry, can you put new line after nung second ad?
Yan, enter.
Tapos remove the comma.
Yeah.
Can you try to run?
Okay.
Now let's describe employees.
Tignan natin kung na-alter na siya.
Kung nagdagan.
Or you can refresh the...
Yeah, describe employees.
You have to select kita para hindi mag-run yung ano.
Perfect.
So may first name na tayo may salary.
Now you can try na yung declare.
Yung first select natin.
You can remove it na tapos yung select na.
Okay, restarting si Sir Ray, si Ma'am Christine.
I'm looking at the Sir Joseph na screen.
Okay.
Can you try to run that na, sir?
Do you have an error?
Sir, yung kay Ma'am Christine, na restarting mo po.
Restarting na?
No, na, don't...
Ah, tapos na, okay.
Let me refresh my screen.                

on 2024-12-10

language: TL

                  WEBVTT
and handling exception ensure our code remain robust and reliable.
So in this example, we declare a variable, the EMP name, to store the first name of an employee.
Then we attempt to retrieve the first name from the employees 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 two many rows.
So can you try this data?
So can you try this on your end?
Courses for employee ID.
Since EMP ID ang gamit natin, first name, salary from employees for,
kasi wala tayo department ID, so mag-add na lang tayo ng department ID.
Let me create a department ID.
So let's try to create a department table.
Then let's insert data.
So create department table.
So I'm 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 departments, 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 a script.
I'm pasting it now in Google Sheet.
So after creating a table, since we already have employee table,
we have to alter the employees to add department ID.
So, we can also practice adding constraints.
So we can alter the table to add constraints.
So that the foreign key of department, which is department ID, is also referenced in the department.
We can also add a F, maybe for the department ID.
By the way, you can, in your computer,
pwede niyo pong i-copy paste. No need to type everything.
Q. Ano po dito sa dito na anong hindi makakap paste from local to the desktop?
Doa no masi na Firefox po yung button?
Sir Ray, okay ka na no? I mean, nakamove on ka na yun sa no data found?
Okay.
Kailangan mo na yung first step. Sinong nag-asada sa Tin?
Tin, kailangan mo na mag-alter ng table bago ka mag-constraint?
Ayan, yung code nasa Google Sheet din.
So, alter table employees, add constraint, FK department, foreign key, Tin, space, foreign key.
So para makita mo din, dun sa refresh, dun sa connections, pwede natin siya makita.
Yun, good. So may table na tayo. Then matatrain na natin yung sa cursor.
Let me fix the cursor.
Oo nga po. Mam Tin, okay ka na Mam Tin? Pwede mo namburahin Mam Tin kasi may department table ka na po.
Meron na po ba? Employee. 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, burahin niyo po lahat ng code niyo.
Tapos iwan natin yung insert.
Yun, so insert, violated parent key not found.
Ah kasi kailangan natin mag, try mo na natin mag-add ng department. May department dito na po ba?
May table ka na po for departments. Ayun, so nain-insert na.
Gusto po makita sa left side mo yung department table, Mam Tin. Sa left.
Connecting local, okay lang. Okay. Tapos connect ka ulit. Right click connect.
Nag-assign ng password. Ash blue.
Okay. Then tables. Yun, there you go. Nakita mo na po. May departments ka na.
Perfect.
Yan, tapos try mo na po yung cursor. Yung declare cursor, EMP cursor na sa Google Docs po.
Okay ka na sir Ray? Parang, ah, gamitin natin sir EMP underscore ID instead na employee ID.
Kasi dun sa employees natin, hindi employee underscore ID ngunit natin. EMP underscore ID ngunit natin as a 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 niyo natin clear yung script output, sir Ray.
Para pag nag-run tayo hindi medyo confusing.
Nagamit natin yung declare na kapag begin tayo.
Yan, so dynamic SQL is a powerful feature in PLSQL that allows you to construct SQL statement dynamically at the right time.
So let's walk through an example to see how it works.
So in this example, we declare a variable v and a store 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 colon 1 in the query.
This is where we bind a value at runtime.
So pwede tayo mag-bind 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 colon 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.
Dynamic SQL.
It will be at the bottom.
So we declare v query work or oops, sorry.
Then we v result employees that first name type.
So natatandaan niya yung percent type.
Ibig sabihin gagamitin niya yung data type ng column.
Tapos v query 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 v query into v result using 101.
Tapos output natin siya.
DBMS output that put line employee name tasyong result.
Let's try it.
Natry nyo po.
So pag no data found, kasi try using 2.
Kasi if you look at the table, sir Jeremy, pwede mo pong iklik yung tignan natin yung table ng employees mo.
May tri ka? Okay.
I think it's 2, right?
Or do you have 4?
Can I check your employees table?
Employees, yeah.
Then click data.
So we try to replace it with 4.
So dapat ano ba ba sa iyo si John?
Try to run that.
Narun mo na po, sir Jeremy.
Yun, nakita mo si John na yung lumabas?
Ah, 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, kay Alice.
Ah, yung kay Jane gawin natin 101.
Yung ID nya, palitan natin yung ID nya.
Tap natin 101.
Enter.
Tapos i-commit mo siya, yun nyo i-check.
Yun, commit.
Okay.
Try, run.
Ah, yung 4 palitan natin ng 101.
Yun, yun.
No, no, yung kabilang...
Yung gagamitin natin, which is using for...
So gagamitin yung 101.
So expect natin si Jane lumabas.
Yun, nakita niya po si Jane lumabas?
Okay po.
How about sir Joseph?
Okay na po.
Okay na lahat?
Nice.
So I think we need more data para mara natin ito.
So mag-for loop tayo.
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 let's try to create more data.
So I put an insert.
It's 10 items.
Can you try to insert into your employees?
Okay.
Nakita niya po na...
na try niya po mag-loop?
Gubahan na po?
Ako po.
Okay, last 5 minutes.
Okay na si sir Ray, si mga Christine.
Nakapag-insert na...
At tatay na lang ni mga Christine yung code.
Nakapag-insert na si sir Miguel.
Okay, mga Christine nila na nila.
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 declared the employee name, employee salary...
which employee name bar char, the data type, and employee salary and number.
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...
SelectInto.
This is one of the most common ways to populate variables.
When pulling data from a table, the SelectInto statement fetches data from...
database and store it in a variable.
For instance, you can use SelectFirstNameSolary 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 SelectInto, are essential tools 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...
so 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 IntTo clause directs the retrieved values into the variables.
Employee name for the first name, then employee salary for the salary.
It is important to note that SelectInto 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...
kagay nung ginawa natin kanina.
This is why exception handling is essential when using SelectInto.
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.
Then 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 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 magligin ng mga calculation or function or expression.
This assign employee salary the result base salary multiplied by 1.1.
Which could represent a salary adjustment or bonus calculation.
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.
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.
Siguribaba na natin.
Tapos so 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.
Update employees.
Set natin yung salary equals new salary.
Where employee ID equals empid.
Can you try this code in your end?
See if you're able to change the new salary.
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 na.
So yung 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 1, 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%.
So row type naman.
So 3pm mag-break tayo ulit.
250?
You want 250?
250, okay. 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 nating 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 the name variable, 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 ipis ko sa ano.
So yun, so yung row type, mamay ilalagay ko example, ito itas 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 employee stable with empid, phrase name, last name, department salary.
So ito yung sample natin ng data for employee stable.
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 ano yung meron kayo pero pwedeng 101 or 1.
Tapos we can access na yung individual fields nila using the dot notation.
So emp record nilipat natin dito tapos sinalik natin into from employees or empid.
So it goes to 1.
So dapat makapag-out po tayo.
Can you try it now?
Sigurang comment ko malo kasi google docs.
Yun dun sa fetch arrow or any comment.
Just put double dash.
Tapos unline to ayun.
Pag kinapipaser yung mali lang yun yung mga comment.
So we declare emp record employees.
Let me correct the code one moment.
Our last name has been declared.
First name.
Let me correct that.
So this should be first name.
And I put the correct syntax on the google docs.
So lahat po ba nakapag-run na?
May error pa rin.
Ayan okay na yung kay Sir Jer, Jerry.
Okay na po lahat nung Sir Ray.
Nakapag-run na rin.
I think we're good.
So naintindihan niyo na po yung % row type, correct?
Na pwede natin siyang ilipat dun sa isang variable.
I think that's it.
We can have a break.
Balik natin is 3.30.
Is that okay?
Okay.
Your stream is busy, guys.
Yes.
Yes po.
Ano mga na stream?
Ngayon po hanggang...
Sigat, you want 30 minutes?
Okay po.
Let's resume 3.30, okay?
Thank you po.
Thank you po.
Yes po.
Yes po.
Okay, great.
Let me share my screen.
Okay so...
Yung row type na pag-aralan natin na
na pwede niyang ilipat yung...
buong entire row...
dun sa isang variable.
Tapos we can access that using that notation.
So with row type, we eliminate the need to declare each column
as a separate variable.
As you can remember, pwede naman tayo mag-declare ng single variable
or single column using the % type.
Okay?
So this make your code cleaner easier to read
and less prone to error
cause by missing or mismatch variable.
And also the adaptability.
So one of the standout benefits of row type,
it's ability to adapt to changes in the table structure.
And better performance.
When working with entire rows,
% row type is efficient because it fetches all column in one operation.
So this reduce the needs for multiple queries
or individual column assignment
optimizing performance for complex operations.
So ito yung sample natin na cursor.
So nilipat natin yung...
So sinalik natin yung mga gusto natin.
Tapos nilipat na sa EMPrecursor.
So we open the EMPrecursor,
then we fetch all the employee record.
So dito siya nilagay sa employee record.
Tapos yun, pwede na siyang ma-access
gamit yung dat notation.
So employee record dat,
satin EMP, underscore ID,
first underscore name, salary.
Tapos don't forget to close it.
So pag inopen mo, ikak-close mo siya.
Tapos table-based records.
So we have two types of records.
So table-based.
These are the records based on the structure of a table
created using row-type attribute.
So yun nga, a row-type record automatically includes
all the columns of the table
and their respective data types.
So yung syntax natin is variable name,
employee, table name, present row-type.
Yung user-defined naman is
these are the custom records
where you manually define each field
and specify its data type.
For example, nag-declare tayo ng employee ID,
first name, last name.
Ang key difference lang nga is
yung table-based is automatically adapt
to table changes such as added or removed columns.
However, yung user-defined records,
it offers more control and flexibility
when you need a custom data structure.
So ganito yung example ng user-defined type.
Dinifine natin yung employee ID, first name, salary.
Nilipad natin sa employee record type.
Tapos, so de-clarify type, employee record.
Tapos yung employee record type,
nilipad natin sa employee record.
Ang gamit natin dito sa transaction dito.
So the code for employee record will go here.
So for example, here populating a table base
using select and to.
So nag-declare tayo, employee record,
employees, present row-type.
So sinelect natin as it is into employee record
were employees, from employees were employee,
EMP ID equals 101.
Then kapag i-output na natin siya,
so ito yun, employee record dot first underscore name.
Sa atin kasi first underscore name.
Tapos employer record, wala tayong last name.
So mag-error to paggigilangit natin.
But ganun yung logic nyo.
So gamit yung dot notation,
ma-access na natin lahat ng table ng employees.
Ito naman pag user-defined record.
So we declare record here.
So kung anong identify natin dito,
magagamit na dito sa employee record.
So in this case, employee ID, first name salary.
So for example, even may data dun sa employees,
pag hindi natin din-declare dito,
since it's user-defined record,
mag-error siya kasi hindi niya kilala.
So using PLSQL records in loops.
So let's see how we can use PLSQL records into loop
to process multiple rows of data efficiently.
So this approach combines the power of cursor
and records for structure and dynamic data handling.
Yeah, I think it's worth to try this.
Let's try this coding.
Kasi we have loops, we have cursor,
then we have records.
Let me paste it in the Google Sheet.
It's in the last here.
So we declare cursor.
Sinalak natin yung employee ID.
So in this case, yeah, so EMP ID sa atin.
Tapos first name.
Wala tayong last name.
But meron tayong salary from employees.
So employee record, nilipad natin yung EMP cursor
per centro type.
Tapos nagbigin tayo, nagloop.
Then we fetch.
Fetch EMP cursor.
Nagtatay po ako sa ano natin Google Sheet
that you can copy it.
Tapos pagka-fetch, siyempre exit natin
kapag walang laman.
Exit when
EMP cursor.
Is not found.
Percent not found.
Otherwise, mag-output siya ng employee.
So employee record dat first name.
Yan. Or
employee record dat sabi nating salary.
Tapos end loop, close.
Can you try yung code really quick?
Let me know if you bump an error.
We'll fix it.
Yung put line.
Let me paste the correct one.
One moment.
Okay.
Yan.
So can you try really quick in 2 minutes?
I put it in the Google Sheet.
The correct code.
Please grab again in the Google Sheet
the correct code.
Sir Ray, nakapag-run ka po.
Ano po yan, sorry.
Natry mo po yung code?
Sir Ray po ako sa account.
Ano po yung code?
Yung cursor po.
Ah, yung cursor na bago.
Hindi ka, naka-opya pa lang ako.
Okay po. No problem.
Ano po yan, please.
Sir Jerry nag-didi.
Ayun, sir Jerry, it's good.
It runs.
Good na din sa sir Joseph.
Try to play with the code.
Try to change something.
Plus 1 minute.
Okay na.
Yan.
Atin natin sir Ray.
Nira rin yung code.
Awesome.
Yeah, it's good.
Thank you so much for that.
Natututunan niyo po ba
as you work along the code?
So we use cursor,
nilipad natin data,
tapos we do looping na
don't forget to close the cursor.
Learning so far po?
Okay.
Thank you.
So,
the benefits of using
PLSQL records is
efficient handling of rules.
So with PLSQL records,
you can manage entire rows of data
using single variable.
So this reduce needs to declare
multiple variable
of each column simplifying
process of working multi-column data.
Also it's improved code readability
and
flexible data structure.
The user-defined
records allows you to
create custom structure that are not
tied to specific table.
This flexibility is particularly
useful when you need to combine fields
from different sources
or adapt your structure to unique application needs.
So siguro yung takeaway natin
dito is yung simplicity and efficiency
nya,
that it can handle complex data operations
with pure variables.
Yung clarity that we can write clean
maintainable code.
Tos yung adaptability nya na
kapag nagbago tayo dun sa table,
automatically magbabago na rin yung code
without re-declaring especially yung
percent throw type.
So again, ito yung sample ng
table base.
Ito naman yung sa user-defined
which you individually define
the column
or the record.
Then,
ito naman yung cursor base.
So
cursor base is the type
of record tied to the structure of a cursor
query making it highly
adaptable for dynamic queries.
Oops, sorry.
Then, the nested records.
Ang command niyan is
we can have a nested records
to allow you to organize
related data
hierarchically making it easier to
handle complex structure.
So in this example,
yung address type
defines a record structure for
address details.
Then, the employee record type includes
an address field of type
address type
creating a nested structure
you can easily fields
hierarchically like employee record
dot address
dot street.
So, nakonek natin siya
from employee record
dot address
yun.
Tapos, makakukuha din natin yung
dot street.
So ito nested na ito.
We declare type,
address type is record.
So yung street tsaka city.
Tapos yung
employee record type is record
of employee ID number, address
type. Tapos
nilipad natin siya dito, employee record type
employee record.
Kasi inano natin yung
address dito ito.
Yung buong address na ito, nandito
na. Kaya natin na access dito.
Kung gusto natin i-access yung
city, employee underscore
record address dot city.
Or whatever kung may dadig
dito, we can easily access
it using the dot notation.
Okay po.
So now let's jump
into conditional statement.
So it's fundamental in
PLSQL programming.
Let's allow it to
execute a specific block
of code based on certain condition.
So ito na yung mga if-else,
mga case.
So let's try with the if.
I know since you're a programmer
as well, you know the if.
So gano'to
maglagay ng if,
condition, tapos
then, tapos doon forget
the end if. So for example, this one.
So we declare
the salary number 50,000.
So
if salary is greater than
40,000 then mag-output
siya ng high salary.
So it's a Boolean expression that evaluates
true, false, or now.
Then if-else naman.
So pag if-else,
it's add an alternative code block
in the condition false.
Kung hindi siya nagsatisfy
dito, if it is false, then
it will go to else.
Kung
salary is
not greater than,
pupunta siya dito kasi ang
condition nyo dito.
So ang syntax if, condition, then
else, tapos, end if.
Tapos yun.
Yung if-else if statement
allows you to multiple condition with an
else as a final.
So I think let's try this para
natin really quick. I'll copy it in
the
Google Docs.
So we declare
salary number
colon 3000. If salary
else if
salary
yun po, nilagay ko po.
Try nyo po really quick.
Oops, itama ko lang yung condition.
One moment.
Wait.
Here, it's-okay,
it's greater than.
Else if, nyan.
Nagwork po sa inyo.
Try to
change value on the declaration.
Very high salary.
Moderate, yes.
Try to increase the number
of the salary.
Maybe put 60,000.
So it will say
very high salary.
So ganyan mag-condition sa
PLSQL. If condition,
then else if.
Ngayon, meron ding
para naman pag
case.
So using case statement
adds conditional logic directly into
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 for, like
else, I'll paste
something in the
here.
Para magamit natin yung
decode.
So in
case, impid
sa atin department
ID ang gamit natin.
So pag tan daw, HR papakita niya
pag 20
sales. Otherwise
20.
Can you try to
run the
decode?
Nag-run po yung code sa inyo?
Nakakita niya may HR,
sales.
So do you understand po yung decode?
So pag yung department
ID kapag 10, HR lalabas niya
pag 20 sales. Otherwise,
other yung lalabas.
Okay? So yun yung
purpose ng decode.
So using the case in a
select statement,
yeah, so
it says here, when salary
is less
than 40,000, then low.
When salary between
40,000 and 60,000, then
medium, else,
siya. Tapos
pag nag-case ka, mag-end.
Laging may end. So case end.
As, anong pangalan?
Salary category from
employees.
Ngayon po. So here, this is
really straightforward 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 EMP ID equals
to 1, for example.
So if salary is greater
than 40,000,
so ganun din. Yung value
na maukuhan natin dito,
yun yung pagbabasahan.
So yung select nating salary
nilipat dito sa
variable ng salary.
Tapos yun yung lalagin natin
ngang condition.
So pagusing the code 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.
Yeah. So
case is add complex
conditional logic directly in state
select statement. So
if we
if we select into
useful in PLSQL blocks for
decision making based on query
result. Yung decode
naman, it
very simple,
straightforward, it's 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 very
powerful tool for incorporating
conditional logic
directly into your queries.
So
in 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
the functionality, it can handle both
simple value matching
and complex
Boolean condition.
So this is the simple
case syntax.
So case, then the expression.
So when value 1,
then result 1, when value 2,
then result 2.
Puwede pa mag-add na marami. So when value 3,
then result 3.
Else, pag wala
nagfold din sa condition,
then we will go to else,
which is the default result.
So in this case,
a simple case, so we
select employee ident department.
So case department,
sales, gagun siyang sales team
pag HR human resource,
pag IT, then tech department,
else, other department.
So, oops.
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 ask, ano yung magiging label n'ya.
Then yung case statement,
for salary
number, colon,
equal 45,000, and
salary category, data type
bar, karina 20.
So we begin with,
kumaga pwede natin
ilipat yung result
nito. So nag case siya,
when salary
greater than, less than 40,000,
then low lanabas n'ya. When salary
between 40,000
and 60,000, then medium.
Else, ay.
Tapos, pwede mo nang i-input yung
salary category.
Okay?
So again, we
din-declare natin 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
talang, then you can try it later.
I'll just
put it in the
in the Google sheet.
Case
statement,
so we declare the salary number,
salary category,
salary case,
else, ay, and
then we input.
Okay.
Yan, napins 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,
best for matching specific
values directly. So yun nga, yung
example natin dun is when 40,000
then low, when
60,000 then medium, else, ay, and
search case naman is
flexible in idea for evaluating
complex condition.
Yung example
natin yung case, when salary is
less than 40,000 then low,
tapos yung other condition natin
when salary between
40,000 and 60,000 then medium,
else, ay,
ay.
So default result is yung
else.
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 ma-anticipated values na
walang magfall
na true dun sa condition dun sa
kasi yung
naman sa kasi yung
naman sa kasi yung
naman sa kasi yung
naman sa kasi yung
naman sa kasi yung
naman sa kasi yung
naman sa kasi yung
naman sa kasi yung
naman sa kasi yung
naman sa kasi yung
naman sa kasi yung
naman sa kasi yung
naman sa kasi yung
naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa k
yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman sa kasi yung naman
Now let's look example how to demonstrate or 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.
So exception when low salary then DBMS put line 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 talaga sa exception when low salary then output niya salary is too low error.
So let's try that.
Then try to change the number.
So if you change the number it should say salary is acceptable.
Nakatanya pag hindi natin binago it's 30,000.
It says salary is too low.
Try to play with the numbers.
Yun, yung erase 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 predefined exception we can use raise no data found for example or raise my exception.
So parang nirease na ito.
When it's true the low salary kaya niya ipapakita ito.
Otherwise hindi niya marirease yung exception.
Ito yung ipapakita niya salary is acceptable.
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.
So yung SQLERM 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 the when adders 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 SQLERRM 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.
So 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 we know and we identify 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.
You know, it's a low level system error that typically request Oracle to support and diagnose the end result.
Then the 4031, it's meaning just 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,
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 a core when a program exceed the database configured limit for 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.
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, it 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 exceptions 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 siya, maganda ma-lag natin siya 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 log, 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 log 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 we seek for critical errors like for example 608.00600, it's recommended to consult Oracle support.
These errors often involve deep technical troubleshooting.
It may 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 exhausts 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 retrieves the error message associated with the error code.
So in this case, when no data found, we can put the code and the error message to get more information on why we have that error.
So these are the example of the built-in in the PL SQL.
So yeah, 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 ito yung code.
So insert into 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, with expect exactly one row of data.
So here we have select salary into salary from employees.
So pag wala siyang nakita when no data found, it will fire this message.
Pwede rin tayong maglagay na kapag wala siyang nakita, ibabalik niya yung default values ng salary.
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 selecting to 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 application, 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 yun.
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 variable as exception.
So if yun nga yung 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-a-get po.
Thank you po.
Using user define exception with raised application error.
So raised 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 raised 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.
In nga 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 raised application error with user define exception ensure that the PLS-CL programs communicate errors effectively.
Making debugging an application handling more intuitive and standardized.
So pa na ba natin kagimitin yung raised application error?
So check if salary is below the threshold.
So we use the raised 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 is yung raised application error can only be used within the PLS-CL blocks.
It's typically employed in begin or exception section.
Then execution flow nya when invoke, it terminates the execution of the PLS-CL 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-agrease yung 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 raised 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.
So in your company you can have documentation that 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 pag successful siya, tsaka nyo lang ibabawa siya account balance.
Otherwise, mag-relation error which is this one.
But kung pumasa siya, if it is false, then gagawin niya to.
Babawasan niya account balance.
Then display the output.
Using application, raise application error in stored procedure.
So here we create or replace procedure.
Check age as begin.
So if age is less than 18, so mag-relation 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 standard dice 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 para atos pwedeng i-log dito yung error sa database.
Ito 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 sparingly since 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 make debugging easier.
So ito yung sample natin ng logging and re-raising the error.
So we declare device or numbers colon zero result number.
So pag nag-error siya pumunta siya dito then ir-raise niya yung error.
Why use pragma exception in it?
Pragma exception in it is a directive that allow you to associate a user-defined exception with oracle error code.
This enables siya to handle a 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 step one.
Then 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 1.
So we insert 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 and 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.
Kanyari, 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 man, minsan we have to rollback all the changes especially in financial transaction if like error in the middle of or if there's any discrepancy.
So the rollback command undoes all changes made in the current transaction.
So example of that is when we update employee, sinat natin yung salary nyan ng 65,000 din sa employee ID na 1,005.
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 nya.
To 70,000 where EMP ID is 1,002.
Tapos natin rin 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, it 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.
Yeah, it's on the last page.
Then after the rollback, please interaction field change to rollback.
So try natin palitan yung first, wala tayong last name.
Wala tayong last name.
Tapos yung department ID natin is 10.
So yung nasa o ba, yung ginagawa ko yung tama.
So the key difference is commit.
It save changes permanently.
It save all changes made in the transaction permanently to the database.
Once issued, changes cannot be undone.
Once na nakumit 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.
Freeing resources for other operation.
For example nga yung pag-withdraw.
So kalangan na check lahat bago ma-initiate yung commit.
So structure of nested blocks.
So we have the declare.
Mga declare na mga variables.
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 itong starting outer block natin.
Then another nested.
So nag-begin tayo ulit.
Din-clear natin yung inner divisor resolved.
Then nag-begin tayo ulit.
This will cause division zero by error.
Tapos pag may error dito, dito siya papasok.
Tapos after nabigin may exception siya.
Yan exception.
Then end.
Tapos may end pa rin yung begin dito.
So dito yung exception nya.
Yung sa labas ng...
Sa labas ng...
Sa loob ng begin na dito.
Kaya make it sure kapag nang-code 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 na mag-read.
Tapos make it sure may proper comment.
Especially sa mga functions, sa mga conditions.
Using variables across nested blocks.
So we declared the salary number.
So we begin with starting block initial salary.
Tapos nag-declare tayo increment.
So ang pinag-usapan natin dito yung variables na salary
na magigamit natin dito.
That we can use across the block.
So variables scope yun.
So variables in the outer block are accessible within the inner block.
But inner block variables are local to that block.
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 a 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 its thing.
So you should see the starting outer block, initial salary, inner block updated.
Then the final salary.
Nag-run po sa inyo.
Siguro before we end, do you have any more question, clarification?
Or yeah, so our topic for...
Parang wala naman ako nakita.
When I stop the presentation or accidentally stop, nagsibagos na ng URL.
Go ahead po.
Add na lang yung question sa laptop namin. Siguro evolve na lang namin.
Yes, yes please.
Tapos i...
Kailangan lang, add normally and then subang-up and then...                

on 2024-12-10

language: EN

                  WEBVTT                

on 2024-12-10

language: TL

                  WEBVTT
And can you click double, can you double click the local mountain? Okay, yeah
And try the code
You can press ctrl enter to run the code
Okay see
So Ray already finish it
So Jerry you're good is it running or you bump an issue
I'm a pace
You are you all using Windows or Mac
Okay
Ayo parin mag pace
Yun may nakapag pace na si sir joseph nakapag pace na
Sir joseph tremios set server output on
Sir Miguel di pa napag pace
Pace
Okay na si sir joseph
Sir Jerry okay na sir a okay na si mom teen okay ka na po mom teen
I'm Christine okay ka na po na tremopo
Ayo nag dalawa lang okay na sir Miguel and perfect
Awesome awesome
Then yung for loop naman so yung for loop natin
For counter
So the for loop in PL SQL provide a convenient way to execute a block of code a fixed number of times it eliminates the need of
explicit initialization
Condition checking and incrementing making it concise choice or fix iteration
So let's explore the syntax and the example so for counter in
Start value then that that then n value loop, okay, so
Yeah, let's try the 169
Really quick see if you're able to output the counter just copy paste it in our interactive
Bullshit
The slides 169 you can click ctrl enter to run it easily
Okay, see sir Miguel able to run the code awesome. Okay. Yeah, that's pretty easy
Then in PL SQL the while statement is used to repeat a block of code as long as this specified condition is true
So the while loops check the condition before each iteration
If the if the condition evaluates to true the loop execute
If false the loop stop so the condition so this is logical expression that evaluated before each loop iteration
The loop continues as long as this condition
As long as this condition is true, okay
Here's an example to demonstrate the while loop in this example
We use a counter variable to control the loop and print values from 1 to 5
So first we initialize the counter then in the condition check the loop check if the counter is greater or equal to 5
Then the execution if it is true it prints a counter the then loop exit if the
Condition will become a false the while loop will not execute if the condition is initially false, okay
Again, the while loop will not execute if the condition is initially false
Use the while loop when the number of iteration is not known in advance and depends on the condition that may change
during the loop execution
So again, we we know the for loop it's a control structure that allow you to execute a block of code
So we need a loop variable
So a variable that takes on each value in this specified range
starting from start value and end value
Okay, you can also use reverse
to iterate from a higher value down to a lower so
in this example
We initialize the for
Loop initialize the counter variable to 1 then we iterate
So printing county of the counter in each iteration so the variable counter automatically increment after each loop, okay?
So you can use reverse
Keyword to iterate in the opposite
Direction from a higher to a lower value. So in the example counter start to 5 and
decrements down to 1
Okay, so use reverse
so in in PLSQL the go to statements allow you to transfer control and
Conditionally to a labeled section of code within the same block
This can be useful in certain cases such as breaking out of deeply nested loops or by passing parts of the code
however, the use of go to is
general discourage in a structure programming because it can make code harder to read and maintain
so
We say go to label name label name. This is the identifier of the label you want to jump
To labels must be declared within the same block as the go to statement
so labels are identify the identifies followed by a colon and
Can be placed any PLSQL statement to mark it as a target for go to?
So here's an example using the
The go to statement with labels
So can you try that in your that is slide 174? Can you try it now?
So while you're trying so I'm gonna explain the label declaration
So two labels start loop and end loop are declared within the declare section section
So loop execution the loop increments counter in checks if it is greater than five
Then the go to statement when counter exceed five
The go to statement transfer control to the end loop label
It's keeping the rest of the loop
The exit after go to end loop as executed control jumps directly to the line
After the end loop printing the message exited the loop
So our key points is use sparingly meaning
Magpaka-addicts a go to go to should be used sparingly as it can lead to code that is difficult to follow
it can jump to
That label and the person who will follow the code needs to jump to that code
Especially Kapag nested or and aminang code
So labels are local labels can only be used with the same PLS call block where they can were there declare
Readability relying heavily and go to can reduce the readability
So it's best reserved for specific cases where alternative logic might be more complex
Are you able to run the script on slide 174?
This one I highlighted it
Let me know if you're able to see to to run it I think all good
Mountain can run that
Then you can press ctrl enter to run anytime the code I
See surgery able to run the code
Sir Miguel has some issue. Yeah
Okay, yeah, I see it now yeah, you can run thank you for playing with the code
Okay, so let's move on
So cursor so our handling is done two exceptions which allow you to you know manage error gracefully
So let's discuss more in depth in cursor
So again, we study the implicit and explicit cursor
So again implicit cursor it's
Automatically created in PLS quale whenever a select statement return a single row or
During insert update or delete operation the key features of that is it's managed entirely by PLS quale
So no explicit declaration is required
It simplifies operation or only one row is expected example of that is
Automatically handles through three triple in
Begin, you know update employees set salary
Equals salary plus 1000 where employee ID 101
While the explicit cursor it's defined explicit by programmer to process multiple rows returned by select query
It provides greater control over querying process
It's allow operation like fetching rows one at a time enabling detailed row by row processing
So
So there are four step of using the explicit cursor
first is we declare the cursor so define the cursor and specify the
SQL query it will execute for example cursor
Then your cursor name is then the query select or the select statement
Then next is open the cursor. So execute the query and set up the result
Then open the cursor name. So once we open the cursor name, we can you know fetch the rows
Fetch the cursor name retrieves one at a time into variables
So for example, we fetch cursor name into variable one variable two
Then after once we open the cursor, we have to close it close the cursor
Release the resources associated with the third cursor then
Close the cursor name
So let's try the
It's like 178 so we have to run first is web we we have to add last name
I think because we don't have last name
So I put in one seven date the alter table employees add last name
So please add last name then let's try to run the cursor
So it start from declare so it's like 178 please run the to query
First query is adding a last name. Second query is
Learning the cursor employer cursor
So the explanation of that is we declare our employee cursor
is declared to
Then after that we select the first name and last name from the employees table
Then we open the cursor seeing the open statement
Execute the SQL query and prepares the result set
Then we fetch the data so the fetch statement that retrieves
Each row into the first name and last name variables
The loop continues fetching rows until not found is true meaning there are no more rows
So we close the cursor the closed statement release the cursor and please up resources
Everyone are you able to run the code? I can see some of you sir Miguel already run the code
As well sir Jerry
Yeah, I'm Dean
Okay
Okay, so let's continue
So what are the cursor attributes
So PL SQL provides four key attributes per cursor that help you control in managing them
versus percent pound it will return true if the last fetch return a row
Otherwise false then percent not found
Returns true if the last patch did not return a row otherwise false then percent row count
returns the number of row
Fetch so far then percent is open return true if the cursor is open otherwise false
so key points, so
Implicit versus explicit use implicit cursor for a single row operation and
Explicit cursor when dealing with multi row queries
then control
explicit cursor offer more control over query execution and row handling
Then closing a cursor for efficiency when done is crucial to pre-app resources
So don't forget to close the cursor when you open it. So yeah
So cursor attributes provide more important information about the state of a cursor during
Execution these attributes can be used with both implicit and explicit cursor
To help you to control the flow of operation its cursor attribute
Return to specific type of information helping to manage cursor effectively
Here are the four main cursor attributes
found
returns the last fetch or your data manipulation language return
Operation affected one or more rows. Otherwise it return false
It's typically used after a fetch statement to check if rows are being returned
Yeah, then
Cursor is PLS code commit built-in attributes that enhance their usability to provide additional control
2k attribute is percent row count and percent open. So let's understand their functionality and
Usage so percent row count returns the number of pro fetch or affected by the cursor so far
It's useful for tracking row processing in loops after manipulation operation
Okay, then is open is returned to if the cursor is currently open otherwise return false
Useful to check the cursor state before attempting operation like fetch or close
Okay, so again row count and is open
Our valuable attributes for enhancing control and debugging during cursor operation, you know, it's Murray
ensuring smooth and efficient program execution
again, that is the
Summary so for the cursor percent pound percent not pound and
percent row count and percent is open
So what is the so first we declare the cursor we open the cursor we fetch it
Then we close the cursor. So that's the
step using the explicit cursor
Now let's try it in the code. Can you try the slide 184, please?
See the step by step. I also put comment there so you can see step one. We declare the cursor step two
We open the cursor
Let me know if you can run the script
Yes, but yes
Ion so
Denuclear natin yung employer cursor. So you employer cursor on the line to
Tai you need to find on Diba
So indicia implicit meaning indicia you built in a cursor knee
PLSQL so on difference non as I explained a while ago
Mass dynamic shama and it will use in the multiple multiple
We can declare multiple cursor
tapos pedig natin magamit yung cursor neon or yung
Yung queer neon come up a pan is a variable
Nung
Yeah, yes, pedimus ang i-reuse
pedimus ang i-open
Tapos al of non pedigari mag-open ang another cursor to to use that data
So again yung yung for example employer cursor natin it holds the first name and last name of employees
Kasi yun yung query natin e, correct? So we select the first name last name of the employees
So see employer cursor she nag-hold lat ng first name and last name
So what we did is we loop it. So we fetch the employer cursor
Into first name and last name so we get the first name last name
So since watay nilagay for example address or maybe
Avatar or picture so in the ari natin siya magagamit dito
so it's very useful kapag multiple yung queries mo or
For example, you need a piece of this and the stable you need another piece on the stable then
You can leverage that using the explicit cursor. So you explicit cursor. It's your
user predefined
Depending nga dun sa query mo or sa data na gusto mo gusto mo ilagay sa
Yes
Yes
Yes, yes
Yes, yes, so for example cursor
employee
Underscore salary is select salary from employees
Tas pwede mo siyang magamit
Yes, yes. Yes. Okay
So yung again yung implicit cursor yung declaration nya sa implicit it handled automatically by PLSQL
Yung explicit naman it must be declared
explicitly by the user or programmer
Pagdating naman sa row yung implicit it handles only one single queries while yung explicit
Cursor it handle multiple rows or multi row queries
yung life
Yeah, yung life cycle naman nya yung implicit PLSQL manage it automatically
Whilst explicit cursor tayo yung programmer must manage it explicitly
Yes, yes, yes, pero kung dependin sa usage mo kung single row lang naman yung kailangan mo then just use this
implicit cursor
Then yung attribute dun sa implicit cursor yung SQL percent found SQL percent not found
As SQL percent row count while this explicit
Meron tayong percent found
percent not found percent row count percent is open
Tapos yung flexibility nya mas flexible siya si explicit
Well, implicit is limited lang to simple operation lang
Kung simple lang implicit, but if you want more complex go to explicit cursor, okay
So let's try declaring variable
It is the process of defining placeholder that in store data values
temporarily during program execution
So we we type variable name
yung pwede nating gapan data type
It is not
And if we you not null it's an optional to ensure that a variable cannot hold a null value
Then initial value it's optional as well. It allows you to assign value
When the variable is declared
So in our case we I think we tackle it yesterday
Then
The percent type attribute
again it
Remember we have a percent row type
Now it holds the entire row of the table, but for example if we want to hold lang yung
Salary no employees we use percent type so employees yun yung table name dot
yung field which is yung salary
percent type so if we want to hold the
the
Variable or the the column of the for example first name we say employees that first
Rscore name percent type. Okay, so yung not null optional siya
Tapos kung gusto mo siyang lagyan yun here
We begin
Putting a value on the employee salary
So let's try step by step I think
Yun atutunan natin how to open the cursor tapos if you fetch natin yung cursor name
Then we have an example
On opening and fetching the first row. So again, we declare the employee cursor
Then tapos from this query tapos we can fetch it na and use it
To display whatever we want or we can we can
So ang step natin is open the cursor name
Fetch the data then for don't forget the close kasi pag hindi natin siya clean hose
It can leads to error or
Kumagang open pa rin siya sa memory
so
Be sure to always close your cursor name
So pag mag-fetch naman tayo ng multiple rows, so we loop it
So so we fetch the employee cursor into first name and last name
exit natin kapag
Diba lalagay natin ng percent not pound so employee cursor percent not pound
Okay, if you want to check yung percent row count pwede rin
Kung baka ilan na siya kung ganyarin gisat na mag-stop sa 10 lang
and one moment
I think it's better to try this. Let's try this slide 190
Let's try opening and fetching the first row 190
I'm gonna highlight it
I about try to use per percent row count
exit it for example. I want I will require you to
To display only the five employees. How do you gonna use the percent row count?
I want I want to stop or I want to show only the five employees
Can you try to apply percent row count?
Where you gonna put the row count?
Yeah, there you go
in the exit when yep
Dapat five lang yung makita natin pag niren yung screen row count
Again you can click ctrl enter to run it maybe because Miguel wala kang data doon
If you look at the table if you click your table, maybe wala naman talagang first name yung employee
Yung table mo can you check and then click data data tab yan
Yan, so wala ang first name yung nilagay natin no yung first underscore name
Kaya wala siyang nandetect, but you can put anything now then commit the changes
Yun tinitin na mga nis EMP name, dun sa first underscore name yung pang
Panganan na column
So let's let's put any name then commit okay, try the code again
There you go
Okay, you're learning so far guys
Okay, so let's dig more
Again we use cursor not found to exit the loop or if any condition you want
So here we exit
The employee cursor would not found which we already tried
Then don't forget to close the cursor name. So close statement is used to release
again the resources
Assosciated with the cursor. So once it's a task completed fetching the data
Closing cursor is important because it helps to pre-up server memory and avoid potential resource leak
Especially when working with multiple cursor or in application that frequently open and close cursor
So when to close a cursor after fetching all rows and inside exception handlers
Okay, so let's try the complete cursor life cycle with close. Let's try the I
Think you have this already
Yeah, it's the same code we have but yeah, it it closed the employee cursor
But it's in the document. It's on slide 194
So again, our key points here is mandatory closing always close a cursor then automatic close on program exit
So PL SQL automatically close course at the end of the block
However, it's best practice to close them manually for clarity and resource management
Also close in error handling if an error curve close and open cursor
Within the exception handling block to prevent resource leak. Remember we
studied the error
Trapping errors yesterday. So you have to put you have to make it sure that the cursor is open
Also, sorry close
So using closes potential in PL SQL programming so ensuring efficient use of
Database resource and keeping the code basically and manageable
So, yeah, so we studied our for loop we tried it as well
This one is for
So using reverse index sample
for loop
Iterase over each row in the result set of a cursor. So pano naman nating gagamitin yung for
Duna sack cursor na ginawa natin. So
for record variable in cursor name tapos loop then and loop and so
So a record variables. It's a record variable that holds the current from the cursor then the cursor name
The name of the cursor being a
iterated so alternatively you can directly use SQL query within the for loop
inside of the name cursor
so
Example of that is we declared a cursor employee cursor
To select the first time a last name from the employees then the cursor for loop
The loop iterate over each employee cursor then automatic handling so automatically opens the cursor
Fetch fetches each row into employee record and closes the cursor after the loop completes
That then it can output the the loop print each employees first name and last name
You can also use the rec query instead of cursor. So this is equivalent using cursor, but without needing to declare it separately
So again, our key point series the numeric for loop best for iterating over a specific range of integer
So numeric for loop then we have a cursor for loop. It's automatically handles
cursor operation making it ideal for row by row processing of
query result then the reverse
Reverse keyword allows numeric loops to run in descending order
Now let's try to
Use the update
Can you try the slide one nine eight, please? So this time?
We're gonna declare the cursor
So while you're trying I'm gonna explain
We declare a cursor. So we have employee cursor defined by
Select employee ID and salary from the employees table
so kong wala kayong
ID na
20 doon sa department ID
Check the table and you can add ID or edit one of your
Table I believe you have all departments ID so check the data if kumay 20 kayo or
Para ma-makapaguran yung code
Don't worry
All this code will be sent to you as well for your future reference
So no need to say but if you can save as well if you want
Check that so right now. We don't have 20 in the department's
So you can add another table or edit?
other one
So in the department sir Miguel you can click the the data para makita mo po yung laman
on the department's tab
At 10 a.m. We're gonna have a short break sir Miguel may department ID ka na 20 sa table mo
Okay
And then sa departments mo may table ka na ba may ID ka na ba na 20
Yeah, click main data sir sir Miguel click mo yung data
Next to column yan yan so okay meron ka na pala 20 no yung HR, okay
Yes, you can you can try to run the code
Magkakaiwalay ba kayo ng table or magkatabi kayo or nasa isang conference kayo
Or nakalaptop kayo lahat okay, then you're sitting in one in one room, okay, I'm just trying to
Imagine your current setup right now
So nakapag-run na ba ang lahat
Okay, so that is the update statement you can play it more in a bit
They have any more questions so far or we can continue okay, I guess I think it's a no, okay
Okay, are you can one moment, let me check in my google sheet nakapag please get anong line
Yes
Hi, I don't saw audience, okay
Yun yung tanong what will happen if the cursor
So what will happen if the cursor is not properly closed during procedure call any side effects of
Negative effect
First is memory leak so an open cursor consume memory and other system resources
So if it is not closed these resources remain occupied, okay leading to memory leaks
so X also exceeding open cursor limit kasi
Pwede kayong maka-receive nun for example
Most database have a limit on the number of open cursor as a session can hold so
We can see that in the open cursor parameter in Oracle
So if cursor are not closed the limit might be exceeded causing the session to throw our
Error like or a think that's zero one thousand. It says maximum open cursor exceeded
also the performance degree
Degradation I think because keeping unused cursor open can degrade
Performance as resources are necessarily unnecessarily consumed
Kasi may memory so mabee slow yung program along the way
So it will slow down the database and you know it reduce efficiency for other operation or session
Also the lack retention some core some course cursor might hold locks on resources
So for example, um, you're concerned a on me like me on table
So pag hindi mo clean hose na kalak parin table na yon. So failing to close the cursor could result in lock contention
so preventing other session from accessing those resources and
Potentially leading to dead locks. So that's the worst scenario we can
Experience kundi nothing it closed. So hanapin pa natin kung anong cursor yung naka-open
para lang ma unlock yung table
Also connection stability issues over time and close cursor can accumulate
destabilizing the database
connection causing errors or even crossing the session or application and
Lastly, I think is the logical error in application. So, you know not closing a cursor
Might lead to unexpected behavior in application that depend on
Proper cursor management for instance data might remain stale or on process or
Operation might fail to execute execute as intended
So yeah. Yeah, so best practice natin always close the cursor. Okay
especially yung ano ex you might explicitly close you might explicitly cursor
Tapos use implicit cursor when possible
So implicit cursor automatically managed by the Oracle or by the database reducing the need for manual handling
So later, I'll give more sample of implicit cursor since this is
the second question
Then utilize the for loops, okay, so when working PLS QL
Use the cursor for loops as they automatically open fetch and close the cursor
Then monitor the open cursor. So regularly check the number of open cursor in the database
Using monitoring tools or queries in Oracle we can say querying
V open cursor. Mama. I'll provide the code how to see the open cursor
Yeah, so next did I answer your question pop sir Miguel
Thank you so much
Then other one is can we have another example for implicit cursor? Sure
Let me create
using explicit cursor
Let me paste in the
one moment
In the Google Docs in slide 198 parent parents, please it
slide 198
Implicit cursor
So we declare first name
Okay, can you try the?
Scenes in MP ID tile. Yeah. Um, can you try slide 198 implicit cursor and make it bold?
But this is the example of implicit cursor
Though so the implicit cursor execution is the select then into a statement
Automatically open the cursor. So when we have a select
It's the implicit cursor this select into it fetches the data into the specified variables
Be employee name and be employee salary and closes the cursor
So no explicit cursor management is required you don't need to manually open
Fetch and close the cursor the database handle it for you
Then also exception handling errors like no data found too many rows
If more than one row matches condition should be handled explicitly in the exception block
I'll give you another more another example using DML
statement
Again slide 198 parent another example
Yes, but
Yes, yes
Yes, yes correct
Tapos in the Monaclai clothes in the Monaclai if it's then because you know, so it might yes
Okay, you're welcome
So you update statement that rain up uba natin
Nakakapag update na tayo. Okay
Yes, yes
Okay, let's move on
So ito naman yung example natin for conditional update. Hmm. So we just um
We declare the cursor cursor with conditional check inside the loop check if the seller is
Less than four thousand then we update
Using the update statement if the condition is true
We set the salary to four to four thousand for that employee
Then output so the updated salary for each qualifying employees is printed
So we have direct update you use for simple updates or with a free wear cross to specify
Which row to modify then we have the cursor update with update
This approach allows row by row control and each following when different values or condition are required for each row
Then conditional updates using conditions inside the loops
provide flexibility for applying
specific updates based on criteria
So, yeah, so they don't example no update. We will have a coding in
Slide 205
One moment. So yeah, so for update clause
It locks all all row
That cursor select so it don't for update if you're looking at my macros, sir
So it locks the database para in the in the moon a shock bag
Oh, so this is useful in multi-user environment where multiple session may attempt to modify the same data
The effect the for updating yeah, you know it allows return by the select query
So other sessions will be unable to update or delete this row
Until the transaction is committed or rollback
So in this case we declare the cursor
oops
So in this case we declare the cursor
We loop and update
the for loop
Iterates over each lock row then update the statement modifies the salary for each employee
Then we're current off
So we're current off clause directly updates the current row in the cursor with simplify updates
Then don't forget the commit. So after the updates commit is called to release the lock. Okay
So for the for update of clause is similar to for update
Okay, so but it's more specific it locks only the column listed in the clause
This is useful if you need to lock only certain columns for update rather than the entire row
So the effect of that is the poor update of clause
locks only the specified columns
Other user will be able to view the row but cannot modify the specified columns until the transaction again committed the rollback
So again, we declare the cursor in this example
The employee cursor then
We we have the select statement here
Then we for loop the cursor
Then update the employee so if there's any error
It will commit all the transaction here, then it will release the lock. Okay
so if there is any error here, you can return an error and
Roll back the changes
so for update
It locks the entire row meaning no part of the row can be modified
By the other session well the for update of locks only the specified columns
Allowing other columns in the row to be updated by other session
So again importance note locks held until commit or rollback
Lock set for update or for update of our health until you commit or roll back the transaction
We're kind of so when updating or deleting rows in a loop we're kind of allows you to direct update
the current row without a specifying condition
then concurrency control using the for update or for update of ensure
consistency and multi-user environments
By preventing other user from modifying data that you are working with
So yes update so this is our kind of clause is used in conjunction with the cursor to update
Or delete the current role that the cursor is pointing to so this clause is specially
Useful when working within the loops that fetch row from a cursor as it allows you to perform operation
Directly on the current row without the need to specify condition based on the column values
Okay, so it's 1008
Let's have a break on 1010 then let's
Go back, maybe
1040
Then 1040
But yeah, well then when you come back when you have a chance
It is it's like
205 okay, so let's have a break. Let's assume 1040 be sure to run this code, okay?
It's like 205. Thank you guys have a quick break
Okay, BAM
Young current off naman it usually use a update shaka delete
So again, you're right Tom. I know you know current row dun sa for loop
Yes, kasi kung
Kung hindi tayo gagamit ng current off ipipace ko yung sample dito
Ibo bold ko siya
So ganyan niya magiging tura niya
Yes, so we're EMP ID employer record that EMP ID
So this young wind current off
Sure, tayo na union current na nasa loop. Yes
Dun dun sa for loop. Yun yung current nyan record kasi info about row by row. Yoni is record
The the lahat pag walang work law siyempre not know
not know
Salary dis employees table
The bang best practices kapag mag-update ka siyempre dapat may wear kasi otherwise baka accidentally
Ma-update mo lahat ng salary no employees
Or yung table so nandiyan na pan lahat
Every okay
So yan it's really good na natatari natin yung code also I put exception handling
Kung
Gusto natin maglagay na exception handling, which is we must or we need to
But that is kumay error mara rollback natin mara rollback natin kasi
Kasi di ba yung for update nilalak natin yung rose de ba
Dito yung sa for update nilalak natin yung rose
So pag hindi natin ni rollback
Nakalak pa rin yung rose. So hindi magagamit ng iba
Yung table na yun or yung data na ina update
So let's move on so let's try the work current of
With delete so ngayon naman magdidilid tayo gamit yung work current off
So let's try that it's
Slide 206
So make it sure may data tayo na yung salary niya is less than three thousand
Tapos tingnan natin yung data na yun kung madidilid
Yung mga so make it sure may din say employees employees table niyo meron kayong salary doon na
Papasok dun sa requirements na yun so if not maglagi tayo or mag hard code muna tayo for now dun sa employees table
To check the employees table just click on your left the employees then click the data column
Tapos may kita na natin yung data ng employees. So make it sure may pasok sa condition
Tapos ii pag niran yung code i-refresh nyo
Balikan yung employees table dapat mawala na yung yung employees na may salary na less than three thousand
So before running the code make it sure na check nyo yung data ng table to do a comparison
Don't forget to to commit I see some people are editing some files don't forget to commit
Changes
Okay, sir Miguel line on ID 1006 18 yeah, try that oh
I think we have our in employee ID. It should be EMP ID
Everything good so far. Did you see that your data was deleted?
Yes, okay
Can you know I am a delete all good the man
Okay, so let's continue
So yeah, the committee statement is a key tool in PL SQL for saving changes made during a transaction
So when working with a cursor especially in a multi-user environment
Strategic use of commit helps manage locks
Maintain data consistency and optimize performance
So let's explore how it works. So locks for update. It's when a course is
With for the poor update clause rows fetch the cursor or lack for duration of the transaction
So the impact of that is prevent other session from modifying those rows until the transaction is committed or rolled back
Row by row commits so the commit changes after processing each row to reduce lock contention and ensure changes are saved
incrementally, so one of the use cases ideal for large data set
We're committing after processing's all rows could lead to lock contention or roll back overhead
Then number three is the really really slack after a commit all locks held by transaction are released
Making rows available for other session
so the important of that is it reduce contention in multi-users environments and
ensure data
Consistency, okay. So we use the commit command to release lock after processing
So again the benefit of the commit with cursor is reduce lock contention
incremental processing and performance optimization
So using commit
Strategically with cursor ensure that changes are saved
Reliably locks are managed efficiently and performance is optimized
especially in multi-user environment
So yeah, let's try the commit
More in in this it's it's it is in slide 208. Can you try it?
Tapos try din natin mag run
Try to change the value
Tapos without the commit
So dapat hindi siya mag take effect
Yeah, 208 slides 208
Be sure to check your tables to see if the changes made
In the data be sure to refresh
Don't forget to refresh the tables
Okay, I will proceed now because we have more
Script that we will try
So this is the example of commit and we can see
For example updated two rows. It will say a paid salary employee ID 1 0 1 1 0 2 or whatever the employee ID
So ito naman yung example ng robe ro commit within the loop
And as you can see yung commit
Nilagay natin dito sa loob ng for loop
so after
Dito ng script ikokomit nyan agad
so
Dependent sa business logic nyo kung gusto nyo
I-process mo na yung buong for bago ikomit or every row ikokomit
So if you want to row by row commit iput nyo lang yung commit nyo inside the for loop, okay?
Then here's the conditional commit after set number of rows
So for example dito
Sinasabi natin na
Row count modules or mod 10
So every 10 rows mag commit tayo ng transaction. So it committed after 10 rows
We can try it
We can use the department ID 10. So it is
slide 2 1 0
Instead of 30, I think we have more department ID 10 in your record
So make it sure make it sure you have more rows
Let me know if you need more data in in the employees. I can provide insert or you can insert more data
So basically ito yung gusto nating hi highlight yung if pro count mod nakikita nyo dito nag commit siya
sabi-sabihin
Kinokomit niya kapag 10 rows na gagawin niya na yung process neto
Please try the slide 2 1 0. Thank you
Hindi 10 throw
Pag nakasampong ro na siya so i-update niyo from 1 to 10
Tapos mapapasana ulit yung 11 12 13
So konyari hanggang 30 lang yung record mo
Hindi niya na mapaprocess yung 11 12 13 kasi hindi na siya pumasok dun sa pangsampong row
Kasi di ba yung committing yung nagpaprocess ng transaction, but since in the final we have the commit so kung ano man yung naiiwan ni
mod
Nikomit dito
Ikukomit nyo pa din
Kumaka ang gusto yung ipakita dito is
you can have a conditional commit after set number of rows or
after certain condition na gusto nyo po
May error ka sir Miguel or
May see
Okay
Which out of sequence, let me check that
Oops
Sinipilang kasalita
Miguel yes, yes, anong area yung fresh out of sequence
Kasi nag commit
Kasi may commit ay sa ending
Itas mo sir at line one o pati nga lang line one sir
So we declare course your employee one moment sir, let me try your code really quick
Pati nga po ng error mo sir Miguel, can you try it again?
Okay, one moment hold on so at line 10
Okay, yeah, that's fine you can fix them
It says here you young fetch out of sequence, it's a core
when a cursor fetch is attempted after the cursor has been closed or
A work work current operation is performed and of course, so that is no longer valid
So in our case for employee
Hmm, yes, one moment. Let me try my hand
Yun yes, so parang nangyari
Kasi yung unang 10
Yes
Throw nang error na dito tapos wala tayong exception handler
So kong may exception handler tayo we can we can catch that
So let's you do you want to put exception handler into that code
Sige
Nagyang kalang
Exception
Slide 210 with exception
Okay, I've pasted it nasa Google Sheet, so we declare cursor
Then nagbigay tayo na update so if commit mod 10
So we add exception so when others
error updating employee ID
So we tried to roll back
Finally we commit pero pag nag error parin din sa commit
exception when others
Can you try it with exception slide 210 with exception handling?
Para malaman natin kung ang exception ba is dun sa inner for loop or
Salabas na sa outer
So using the exception handler
We can detect
San yung nag error
So dapat marasim natin unexpected error occurred 0 1 0 0 2 fetch out of sequence
Yeah, good good, how about the others, but
Sir, Joseph, are you good? You're able to run the code with exception handling
Everything good. Okay, let's move forward
So ito naman it's a simple validation with if statement
so it's on
Slide 210 we can quickly paste it
Let's test it in five minutes. So pag in the na nakita or yung yung yung value is
It's less than 3000 which is yung 25
Mag-i-insert sa dapat mag-i-insert niya yung name na ilalagay niya
As possible na pag niran niyo ale magkakano kayo ng
So try to change the name
So it is a sample simple validation with if statement
So again, we define the variables no employee ID
Which is yeah employee ID first name last name and salary
So we use a validation check if statement is to ensure that salary meets the minimum threshold
So if the salary is below 3000 and error message displayed and the insertion is
So if the salary is in is valid the employee data is inserted into the employees table so
You insert into employees
Mag-i-effects ya, but I think
Employ in EMP ID yung satin yeah EMP ID
So benefit of validation first is data integrity so it prevents valid or inconsistent data from entering the database
also error prevention, so avoid runtime error caused by invalid data during database operation and
Custom logic, it's allowed tailored validation rules based on business requirements
So validation using if statement are ensures that operation are only performed when data meets
specific specific criteria
safeguarding the integrity of your database
Let's move on so it anaman yung
ma-apply natin yung LC so this is
slide 212 or 212
Can you quickly try it?
Para ma-apply natin yung if LC LC else then and if
So don't forget the end if
kapag magkakrokin ng if
I think we don't have to have a job title
Oh, yeah, pero hindi naman kailangan kasi we just just declaring it here. So try the slides 212
Try to play with the numbers try to change the variable
Okay
Yeah, so let's move on so cursor parameters is allow you to pass values into cursor
When it's open
Making cursor more flexible and reusable by using cursor parameter you can customize the behavior of the cursor based on different inputs without
redefining each time
So when declaring a cursor you can specify one or more parameters each with a data type
These parameters act like a function arguments and allow you to pass values into the cursor when you proceed
So that the syntax is cursor name
The name of the cursor then the parameter one parameter two and so on
The parameter of the cursor so each define with a specific data type
Then the select statement the SQL query that use the parameter
So let's have an example of the cursor with single parameter, can you please
Try the slide 214, please. So again, oh
Precise we declared the employer cursor
is defined with depth ID or department ID so
Use it that is used in the work loss
employer cursor depth ID number so
you
Depth ID then the data type
Tapos after non select employee ID. So in our case EMP ID
First name last name salary from employees were department ID depth ID
Yeah, so nakapag-gretat ayon
Cursor with a single parameter tapas we open open statement passes the parameter 10. So in this case
Since meron tayong department ID na 10
Makapag open tayo tapos from that we can loop it na
So fetch employer cursor into be employee first name last name
tapos exit tayo kapag
employee cursor not found
Are you able to run it successfully?
Okay
Okay, now let's try with multiple parameter
that is on
Slide 215 now in in our example
So the purpose of that is the cursor employer cursor accept two parameters
Depth ID or the department ID and mean salary for minimum salary
So the query is cursor employee cursor then depth ID number mean salary number
So we open an employer cursor. So casino made
Department ID na 20 na may salary na minimum of 4,000
So we select that kasi yung we're not in department ID cause depth ID and salary
Is greater greater than or equal the minimum salary?
Then we fetch and display the data
Okay
Process each row in the result so set individually using a loop and fetch
Then we close the cursor don't forget to close the cursor to release resources once processing is complete
So our key points here is dynamic
Querying so cursor parameters allow the same cursor definition to be reused for different input values
Reducing redundancy also it offer flexibility
Parameters like depth ID and mean salary enable you to filter your rows dynamically based on runtime inputs
Parameter data types. It's a cursor parameter should match the data types in the query. They do not require default values
Okay, so in the in the name and cannot have my default values
Open statements or when opening the cursor you must provide the values for all declared parameter in the correct order
So cursor with multiple parameters enhance code
reusability and flexibility
Allowing you to dynamically filter and process database on varying condition now. Let's look more advanced cursor using scenario. Oh
Yeah, I so let's have a workshop
So here's the task
So you'll give in 40 minutes
to do this
Then we automatically have a break of 12 to 1
so I'm still here for until 12 if you have any question, but
Let's do this task. Okay?
So we have a create our own cursor with parameters department ID and job title for the job title
Create the long-term job title don't sad apartment table pause a pocket on you how we can fetch employee and
matching the criteria if any question just ask long pop
then
create your own Google Docs
paste your answer there and
Export as PDF then submit the submit the PDF
Once you have successfully run the code, okay
Thank you
You
Just message me lampa pag my question and it along power
Sorry
Hello po hello
Miss and the bomb a error taya tapos sinasabi line ganito line to line tree
Para makita natin or main able natin so worksheet yung line number
Can you go to tools on the menu tools
Thus preferences
Tapos anapin natin yan yung code editor click natin yung plus
Tapos line gather
Line gather tapos i-check natin yung show line numbers
Para mas madali natin ma-dali tayo mapag-debug kung nasa yung line number na yan, okay
Tools preferences line gather, okay. I hope that helps
So siguro lunch na tayo
balik tayo ng 1 10 p.m
So I'll give you 10 minutes for to wrap up the code so pagbalik nyo let's wrap it up tapos
Review natin yung mga answer nyo, okay
So enjoy your lunch guys
Hello, I'm busy talaga nila mag coding. Oh
Let's run the lunch
Okay, please wrap up your workshop or your task
10 more minutes
Yes, yes
Sa goa po kayo ng google docs tapos
Pusha kan in PDF once you pay sa google docs you can download it as a PDF tapos you can attach here
Or you can email it back to mom chat
Sir, pwede consolidate na lang po namin
Okay, no problem, yeah
It lasts two minutes
Okay, so yeah, I see now you know may gilagawa pa but that's okay. Um, did you learn something while doing it?
Yeah, so I'll send the answer and the notes that you can use as well
Kapag-ia play nyo na but it's a good thing that you can also
Send your work
Email the work so I can evaluate tonight once you send to mom chat. So please consolidate but I'll send it now
And we will discuss it as well I'll send it in
Here
So yung at the example code
So you can run it later compare it to your
answer
Then see the adjustment we have then also I provide an explanation
on on the task
Okay
then we can discuss it by tomorrow morning kumay question or
What are the difference?
but at least
Nakita nyo how how we can use
different
Fronction
queries
Yung mga natutun natin at least na-apply natin. So let's continue the lesson. I'm on now
slide 216 out of
414
So let's continue. So ito yung answer niligay ko na rin sa google docs with explanation
So additional okay
We can do that later on but I think we can escape not say, you know how the exception work
Roll back and you understand the row by row commit
okay, and
This is also additional task, but I think for time purposes
Let's skip this and you can you can do that we can do that this after if we have time tomorrow
So let's discuss naman yung procedure function and packages when it comes to
PL SQL pano tayong gikirit na procedure
Ano ba yung function and packages have you ever created a procedure semi sequel or function?
anyone
Nakapag kikirit na po kayo ng procedure functions kanong views
Okay, that's fine. Yeah, so I at least I know how we can tackle it or we can skip ko niya ilat kayo nakapag
Create anong procedure nakapag great anong function no views
In the next I know I'll teach the trigger what is the purpose of trigger and how to create a trigger
Okay, so let's continue
So since we already have a table employees no need to create that so let's proceed
So let's try to create a sequence can you type this or if you want to copy paste it it's on the
Slide 2 to 1 so let's try to create
a sequence
It's in 2 to 1 then how gonna how we gonna use it?
I put the application there the insert into employees
So yeah, let's try to create a sequence
So create mon natin sequence pag nakikirik niya sequence saka tayo pwedeng gumamit ang insert into
Dapat may message kayong marisip na sequence created
Si sequence emp under underscore s e curated tapos na rin nating mag insert into
So pag nakarisip tayo ng unique constraint ibig sabihin yung primary kissi natin is yung emp ID
Which is yung next ball because I see the question 1 2 3 so the judge if you look at the employees table right now
Yeah, so may I didn't I know one so if you can edit it for now just for the testing purposes make it like
200
201
Sir Miguel e back modem buck me young code modon. Yeah. Yeah tapos bago yung table mana employees
Punta tayo sa employees table
Tas baguhin natin yung 1 2 6 mo baguhin natin ng kanya 201 202
Yung mula sa ano mo em 1 to 4
para
Makita natin na mag insert mama
Yeah, 202 to 203
to 204
Yung six yan
Okay, tapos save
commit, okay, then let's try
to insert
One raw inserted in nothing konag insert punta ka po sa employees
15 refresh on 16 ish ah so nag to shah so mag try ka po mag insert so dapat ang susunod na ID 3 na
Yon, okay
Na try nyo po nakita nyo po sir joseph
na grant po sa inyo
Yun kasi po yung ID na pinapasok natin ngayon is
Either one to na meron ng existing kaya nagkakalim pa kayo ng ano error if you can edit yung employees table po
Sir, you said go to your employees table
Tapos edit natin yan ganoon natin
200 202 ganun
Yung limang records na
double click
You can edit that yeah, go mo pong 201
Sir, Joseph, okay, then the next one
Okay, perfect then commit the same save and commit and commit
Like say po ba
Yun so refresh natin sir, Joseph
refresh
Yes
Parang di nagdagdag no
Okay, then let's try the code insert tayo alit tignan natin what happened
Yun so pong for na yung sequence natin, oh
Can you try again to insert sir Joseph ting natin kong magiging 5 9 ID
For na so try mo let mag insert iran natin alit yung command
Okay, so five so i-bibig sabihin yung sequence gumagana
Okay, so you can create more sequence if you need and you can increment by two by three or whatever number you like
So let's proceed
So ngayon naman, try natin mag create ng view
I
Think I prepared it as well
On the line to to to let's create a view
Nakaka na try nyo na po mag create ng view. I mean before
Okay, so yeah, you can copy paste it
Yes, but
And upon question po
Hindi ka nagagamit ng sequence gagamit ka na ng logic for example yung last ID no
no
No table mo kumaga yung sequence
Function na built-in
for
Kumaga yung sequential sequence lang siya
Pedarin yes
Yes, so depended on sa logic nagagawin mo yes
Pedarin yes, okay
Na try nyo na po mag create ng value. Ah, sorry ng view you can also
Then para matawag natin siya di ba isa select to select it we can we can simply
So young creating view
It's like a virtual table that is based on select query. So it allows you to simplify
Complex queries or provide a specific data set data subset to the user. So now let's try to create a procedure
So mag create naman tayo ng procedure by the way para magamit yung
View it's you can simply select as there is from for example high underscore salary underscore employees
tapos whatever the
Laman ng view yun may kitama siya
ngayon naman try natin mag
Hindi ko nilagay dyan, but can you type this procedure? So update salary
So yung procedure no, so it update the salary
It's update the employee salary. It takes two parameters
One is yung employee ID and the new salary. So in our case
EMP underscore ID
So application ya
We can type begin update and as your salary
Tapos kung sinin natin gusto kong i-update tapos kung ilan yung salary
I put that in the Google sheet in the slides two to three application
Once na may procedure na kayang
Tapos check nyo yung tables na kung nag-update yung
Once you're
Kunag-update yung salary so ibig sabi nag-run yung procedure ng tama
Yung were employee ID niyo ang gamit natin sa table employees is EMP underscore ID so make it sure mapalitan
So where EMP underscore ID equals P employee ID
Tapos may kita mo rin yung procedure dun sa left if you want to see all the procedures
I mean if you want to see all the procedures also you can type describe
Then the procedure name for example describe update underscore salary
To to show the details of it
Yan janser joseph may kitang mo din yan may procedure na ano
Begin updates underscore salary may procedure ganas array
Okay, so it's already existing
Update salary
Where EMP underscore
System that update underscore salary
Update set salary because P new salary were EMP equals P employee ID
May 101 ka ba na ID can you check yung
Table mo po na employees
Okay
Number four, okay
Trimak po ilat-run yung 15 to 18 na line run
At ini-ignore nya kayo refresh can you refresh that
Okay, can I see your query sir a local 12 yeah
Update salary but you manage
We employ ID
Create procedure ni pangalan ng procedure name mo sa local 12
Update salary
Can we try to change maybe update salary one ganun?
Para makita lang natin tapos i-run mo siya para mag create yung procedure
Okay, tapos yung yan okay the compel siya
Run
Yun so nag update siya hindi natin alam po na nangyari sa procedure
Siguro naka filter ka sige repress natin yan procedures click mo nga po yung local mo baka naka filter kapo
Click my local mo tapos click mo yung filter yan
Baka naka filter ka
Yun
So may error ka dyan sa ano kasi na naka begin so mali yung procedure
Naisama natin yung begin doon so you can you can simply delete that
Okay
Okay, I think let's move on
Now let's try to
Ano naman create a function
So this time let's create a function really quick
It's on
224 slides to 24 I
Put application there and another sample and PLS quick PLS quail
slides to 24
So yung function sa PLS quail block it performs a calculation or action
In return and return a value
Functions are useful for encapsulating logic that needs to be reused. Okay
So the dysfunction the calculation bonus take a salary
As an input and returns a calculated 10% bonus
So I put the application as well in the slide to 24
and
another sample in PLS quail
Sample, let me so we declare
B salary B bonus then we begin
Then we called to calculate the bonus that is B bonus we calculate the B bonus and
We display the result
Then we end
So you should have the output the bonus salary of salary is then the bonus
Yes? Yes
Yes, po kasi I'm planning this a function Maria share
Yes, yes
Siguro yung gusto kong iano sa function
iaboy natin yung side effects kasi yung function
You know should not perform
Any database modification kung gusto mo may database modification use procedure pero kong
calculation
Maganoon tapos it's in return single data single value use function. Okay, so
Let's let's remember the single responsibility principle
so meaning a function should not do one thing and
Should do one thing only and it do do it well
Pasta ganyan niya yung function can be embedded some SQL queries
So design them for performance
Let's avoid yung ma complex logic or multiple database calls
sa aloob ng function
using SQL queries tapos ensure na they are
Deterministic to avoid performance issue with repeated calls in large data set
Tapos it's a good practice then to have exception handling
sa aloob ng function para kung may error man
makapagbato siya ng messages and
It avoid running runtimes error or calling blocks
And I think it's a good practice to put comments
Like what is the purpose of the function?
For example in our case we we can comment a function calculates the total bonds for an employee then we
Define the parameters emp ID stands for employee ID
Then that returns the total bonus amount this help us our colleague
In your future self to to remind what this function does
Okay, so since you try the function, let's move on
So again summary
SQL provides a variety of database object to organize and manage data in
logic effective in logical effectively, so each object serves a unique purpose
announcing performance
reusability and security so
First is we have table we know table is store structure data
We have sequences generate unique numeric values
It's commonly for primary keys and it configure
Configurable to start the value so and also the increment step and the cycling option
Also the indexes the purpose of that is to improve query performance by speeding up the data retrieval on
specific column especially
Millions of data then the views the purpose of that is to create a virtual
tables for simplified and secure data access
so
Do not store data they they provide a query abstraction layer, okay?
For example create view employee view as you know select first last name
Where employees were salary for greater than 4,000 for example?
Then the procedure so again it encapsulate reusable action to perform operation and database
so later on we will
Learn more about the procedure the in and out and the in out that allowing flexibility
So and lastly is the function so it's for form calculation or operation and return a result so
Marami patayong sample in the next slide
So here
We have syntax for parameter in procedure and function
So here we said param in
your param to out
Sabihin mag output shadowing param 3 in out so bibigyan na example nyan in a while
Then some functions, so we create a function param on in
param to in
tapas return
Ano yung data type return data type as
tapas so begin tayo na at the name return value
So let's try this so let's try to
Create a procedure manage salary, so we calculate the bonus then we increase the salary
Let me check if I put that in the
It's
Let's try this slide to to seven
Then on the Google she on the Google Docs. I also put the application and
Trigger option on trigger because the year will be discussed later on but you can create your trigger now
Yeah, let's let's try it. Let's create a procedure manage salary
Again the employee ID. Let's
Replace it with EMP ID if you see what I'm gonna highlight it so you can see
So if it is successful it should say procedure manage salary compiled
Then here's the application
Let me know if you can see the update salary for employee ID 101 calculated bonus final salary
Try to call the procedure and check also the data in your database
One moment guys, I'll be right back two minutes
Yes, I'm sorry I'm muted
Yeah, yeah go ahead not try enough up a lot you know, let's go back here in
Yes, but I ran po na ran you know, but my procedure na po
Okay, you procedure for another in po
Yes, but not right not right. Okay good
You put that position so create a function so create or replace function in this case we
Create sorry
We calculate the total compensation
So the logic in this function is inside the begin block the function adds the salary and the P bonus parameter and return
the result, okay
So I'm input nothing detail issue salary and bonus
So function with parameters enables dynamic computation making them ideal for reusable and
consistent logic and PL SQL programs
So let's see it in action. So here we declare salary bonus
Then this is how we gonna use it
so
We calculate the compensation
B salary and B bonus the input then we display the display the total compensation
So here no man po is yung parameter with cursor. So here
We create a cursor employer cursor
So with a parameter P depth ID or department ID then add the employee ID and salary so we begin
with
open employee
Then so meaning we're passing the 10
Here does a course or simply a cursor
Tapas i papasa niya dito
So mamimit niya requirements kasi may department ID tayo na 10 so pag may may data tayo papasok sa loop
So fetch employer cursor into employee ID salary
So sa ngayon in output lang natin yan, but you can create your whatever the logic you want once you loop the data
So don't forget to close the employee cursor
So you can assign default values to parameter in the procedure and function so for example
Create procedure example prop param on in then the data type number then default niya is 100
So multiple parameter types you can mix in out and in out parameters in a single sub program
Young so and number three is error handling with out and in out parameters
So ensure that out and in out parameters are always assigned a value within the sub program to avoid
runtimes error
So benefit of that is prevent unhandled exception ensure predictable outputs
So why parameters are important first is dynamic code it allows the sub program to adapt based on the input provided
reusability
Same code can be used for different scenarios
By passing parameter values and also maintainability. It's simplifies modification and enhancement by
isolating logic with sub programs
Parameters enhance the flexibility and the reusability of PLS code sub programs
enabling you to create maintainable and dynamic solution that adapt to your
various input and output
so here
Just the general structure of our procedures. So we create a replace a procedure then param on if it is in
Data type or if it is out or it's it's the param is in out data type
Then the begin where we put the logic of the executable statement and exception
Then don't forget to end up procedure name. So so first we declare then we execute
so from begin
We execute whatever we want to execute
for example this one
We update the employee salary plus by Barnard we employ the ID calls PM ID
Then the exception so it handle run times error that the core during execution of the program when data no data
No data pound. So sub in your no record pound
Then here's the example of creating update employee salary
Oops, one moment
Yeah, so here we have a parameter of input employee ID and increased percentage
The the percentage may we increase the salary then we have the output parameter the P new salary
As an out and P bonus and out so it's returned out the updated salary after applying the increase
In P bonus return the calculated bonus based on the new salary
So in the declaration section, we use V current salary a local variable used to temporarily store
the employees current salary then for the executable section we
Fetch the current salary
The select into statement that it leaves the current salary of the employee here
Then we calculate the new salary
So the salary is updated by applying the percentage increase here we we add the
percentage increase
Then we calculate the bonus the bonus is calculated as 10% of the new salary
So whatever the new salary times 0.10 or up 10%
Then updated statement the state the updated salary is written back to the database
So we update that employee we set the salary to the new salary
with all this calculation
Then we do exception handling kapag walang nakita
No data found so if a final error employee ID
when others unexpected error occur, so
Hindi natin mag-account ng natin yung error para makita natin kung saan nag ano yung mali
Okay
So again calling the procedure so we declare
This
This is how we call the procedure as we practice a while ago
Then types of error in PLS quills so syntax error
Occur during compilation due to incorrect syntax. So mandalis natin tong
Nikita then the runtime error. It's occurred during execution due to invalid operation or wrong input
Such as division by zero or a missing table
So logical errors as well. It caused unexpected behavior due to incorrect logic in the code
So example of syntax error. So for example this one we begin
So it says 0 0 2 0 1 identify our DBMS put outline must be declared
So type of a shadow but put underscore line
So our solution at a and let's put an underscore. This is a sample of runtime error
So that declared I know in a V zero B result number
That's we're trying to divide by zero
You know number which result to device or is equals to zero
so our solution at 10 is magkaran tayo na exception handling, so
Nag begin title snagdeg nagdagdaga na exception when zero divide then the BMS output put line cannot divide by zero
Or you can log it in the database
Gonna then pug no data found so we add an exception
pug no data found
Just put or output this or put that in your error lag
Yeah, but too many rows
for example nag
When we
So that error message zero one
For to to the exact fetch return more than requested number of errors
The because the cost is the select end of statement is designed to fetch exactly one row
When there were close matches multiple rows this error is raised. Okay
so our solution at 10 is
But the time of limit or put a tile
mag-use the cursor
So mag-exit I win in because sir not pound
Or pretty nothing a limit canary. We're department ID
And the row num equals one so by the time I limit
Then here's how you do debugging using put line
so the BMS output put line and
Which is
namananatin
Tapos ito yung the practice natin last yesterday yung paglalagay ng SQL code and
SQL er
Errm, which is built-in
commands
Plsql Oracle database
Then key characteristic of procedure
In my encapsulation combines logic into reasonable unit can accept can accept input in
Out or both in and out data type
Then here's the example
Okay, can can we try this I didn't put that in the since this is
Make me long a man. Can you type this in your?
desktop, please
Para matray natin yung with parameters
So type creator plays procedure calculate area
Okay, I'll try to put that in the document
It will be line 244
So example so create or replace calculate area so we declare length
Then we begin
Then we declare a number calculate area and I'll give you know po side slide 244
So don't forget to end
Once you declare the procedure
So for example you create procedure calculate area you have to end the name calculate area
The manna pin procedure nya mayor po
I put the correct code in the document
And so pag successfully compiles as per se procedure calculate area compiled
Was the procedure compiled you can call it by begin the calculate area
Yung calculate area ma po sir joseph my space po I mean
Nalagay mo po atanay space. May be show yung line number mo para mas madali natin makita and
At line for 1234 cannot be used an assignment target so declare right area number
Once na macrate na yung procedure yung pedin masya erase, okay
Line for 1234 yung calculate underscore area
column one
May procedure yung na bike out calculate area. Ayun calculate area
Patangin na po na calculate area mo, sir
Miguel
Create or replace
non edit of non
Additional procedure can you remove mo na yung non additional in number?
with
Yes, but
Sir Miguel, but I'm gonna get a little
Is what sir Miguel game not a positive scream
But you can also use a non-additional
Now word not sure with that
Don't push a line one
You're not additional
non additional
Pins are line one more. It should be create or replace
Procedure and just remove that
procedure calculate area
nagrun
In out in number is
King
Can you try to create?
procedure another sir Miguel tapos copy mo yung ano ko
yung nasa google tapos lagin natin calculate area to
Para we can we can have a comparison
Wow si mom teen tapos na
Yun see sir ray nakita ko na rin okay na si sir Jerry ganun din
Sir Joseph naran mo na po I
Mean nakapag calculate na siya
Yun, okay, sir Miguel na lang
Creator place procedure calculate area. I think it's look good
Okay, it compiled good, let's try to run it
Awesome
Awesome awesome
Welcome po and so at an amen yung with in out na parameters
and with in
so
Let me put this in the
So let's try to create this procedure. I'll try to put this in the
Google shit one moment
This will be slide two four five
Let's create a procedure one moment
And so increment value the input and output parameter then
Yeah, then the exception so I add another no when when others
Then it will raise an application error then we
increment a value
so here
We can do application
And so mag-declare tayo ng current value
tapos increment
Then we begin we call the procedure increment value so nam equals current value increment by
Then increment then we display the output
Okay, so let's try to create the procedure in
slides two per five
Let me know po pag nilgrana po yung code sa inyo
Kuna pag increment na ng value based on the input
So it's very simple lang no nag-add lang tayo but in your real life or work you can make
more
execution
complex
Okay, let's let me check your screen
Okay, so let's move on so users procedure
So it lists all the procedures owned by the current user so
Can you try this code please
Validate
So if we put select asteris user for example object name
Para makita natin yung lahat ng user procedure
Nag ginawa natin like update salary calculate bonus manage salary calculate area
So you can select that you can you can use that
Then for the user source you can do this query so it display the source code of the procedure for example
the procedure is
Select text from user source
Where name for example ang procedure name natin is calculate area
Then order by line
So it shows the the source code
Kunyari, I know though may click naman na sa left, but if you're working on the terminal
You can you can see the code you can output the code
the source code of that procedure
You can give it a try especially this source code
Just type select text from user source or name
Then the procedure name you use for instance we use calculate area
Oops
One moment
Let me share my screen
Yun so pag gusto nating
In ya makita yung code no procedure name you can use this code this query
You know in PL SQL
Store procedure can be invoked from various contexts based on their design and the presence of parameter
So one of that
The ways to call the store procedure is anonymous PL SQL block
This procedure is called directly within the anonymous block
Okay, so we will give example later on then the other PL
SQL sub program in my function or procedure na din define natin
Then the triggers which we
explore later on then the application that we can use for Java that net Python or
SQL plus
So here in
This is the procedure for
using out parameters
So, please remember this code so we create or replace procedure get salary so we take in
EMP and as for IDs as in then salary as out
Then we begin we select the salary into
EMP salary
From employees or employee ID or EMP ID employee ID cause EMP ID. So the execution will be
exec get salary
101 colon salary then we can print the salary in an SQL plus
Then we can have expected output salary, so this is SQL plus again, it's for terminal, right?
So once you are connected with X
SQL plus you can also do or execute a function like in this or procedure in this instance
We use exec get salary
then
Displaying result will DMS put outline. So in this case we
output the employee name and
the department of that employee
So, I'm sorry
Next one
So don't forget to set server put on server output on then exec
Show employee details then what is the parameter?
So the expected output will be employee name John though then department is IT
then
About working with multiple out
parameters, so in this case we have two out parameters
So we begin with select first name last name salary into
EMP name and salary
Salary so from employees or employee IDs EMP and s for ID so execution and SQL plus
So variable name, so we define variable name
bar chart variable salary number then we exec
Or execute get employee info
by the input
Then we expect like this. So we print the name then the salary
So name will be John though salary is sixty thousand
So how about for in out parameters in action? So like this we create update bonus
So the process will be EMP bonus plus
500 so in in out parameter
The EMP bonus is
It's both input and output
The initial value of EMP bonus is passed to the procedure
modified and returned
Okay, so the logic the procedure incrementing the current bonus by 500 for the
specified employee ID so you in and out by the show my default for me bonus tapas
Magbabago ng bonus for example may bang parameter
So
Pag nag nag nag nagiging kanang in and out so it can accept value
default or any value then it expect to out a
New value or shopper in kumala niyong binago din sa process
So it's like the bind variable for the in and out parameter
So first we assign the initial value for example bonus nya 1000 then we call the procedure
expect update bonus
101 bonus then we print it like this
Whoops, so like declare time initially
tapos
tinawag natin update bonus
ni 101
Tapos print natin siya remember naglagay tayo dito ng
Ano yung process nya whatever the bonus na iin nya plus 500
kaya pagdating dito
so si 101 na ID
so 500 yung
1000 for example ito in our code lang natin kaya naging 1500 then we create or replace
Replace debug demo so step one we
The procedure started then exception
So error data no pound so ito yung pagkumbaga we can use the DBMS output for debugging
Para makita natin yung step nasa na ba tayo especially if you're working with the current code
You can you can debug it de ba parang you cannot eat the big elephant
But if you chunk it de ba makakain mo rin siya parang ganun so using using the
Debugging putting output
Outputing the the text
yung result
Mas madali mo siyang madi debug then the execution and SQL plus so we set server output on then just type
exact then the
procedure name so here
Based in sa una nating slide so ito yung output step one procedure started
Tapos kung error
no data found
Tapos structure naman pag out parameter yung procedure so param one in
ito lang tatandaan nyo yung out na data type meaning it expect to
To return or to have new value
logic to calculate or retrieve values from param 2
So
This is the sample of creating a procedure with an out parameter
So we create a procedure
So we have an input number in then output
Square as out number then we just multiply the input
the same input number
and then
That that will be the output square. So if we call it in the procedure in SQL plus
So we define a variable as square result number then we execute calculate square 5
Then the square result komaga dito natin lalagayin square result
Don't forget to to define it then we can print that so since 5 sha 5 times 5
25 25 no result so if we put 6 it will be 6 times 6 36
So how about we by the way mag
break down on 3 p.m.. So let's finish the entire up to trigger
So we create or replace procedure so for example in in this case. We have to as we discussed before
emp name and
the out the multiple out so we call the
Procedure to
We will call the procedure in a bit so here we select it and to call the procedure
We expect get employee details see no you're 101
Tapos canina sha elulipat say emp named in emp salary
So if we print that it will just print emp name then
emp salary
Then we declare the result number calculate 6 result
Yung kanina yung square
Diba so yung pag 6
Since it's multiple the function here multiply it so it will be 36
Then this is the syntax for create or replace procedure name
So
Output parameters are using a store procedure to return values to calling the program this parameter is specified with the keyword out
In the procedure definition so when the procedure is called the value assigned to the out parameter inside the procedure
It's available to the caller after the execution
So we will have a sample later on
So one moment
So here we call the procedure
So get employee name so the result will be employee name gender
Then this is the syntax for creating a function so parameter name name of the input parameters
Then the return data type so we have to specify the data type the value
The function will return then return use inside the function body to return a value
so
We already create a function in a while ago
Then we can simply have a return
Have a return function here, so it just multiply by 12 the monthly salary
So we use the function so we declare
5000 then we prepare the annual salary
Then we begin annual salary
Colon equals so was we assigned the function of get annual salary
so
5000 will be multiplied by 12,000
That's why when we output the annual salary it will be 60,000
So again the key points of that is its returns of value
the function the
reusability because you can use it
However, you like then calling in SQL so function can be both invoke in SQL queries if they are
Deterministic meaning it always return the same result for the same input
So my advice is do not perform
DML or
The operation like insert update delete in the function, okay?
Then make it sure you add exception handling to
To catch all the errors and
Avoid resource intensive operation in the function used within SQL queries to prevent
Performance issues, especially when processing large data set
so here let's
This function calculate the bonus
So here we we want to calculate the bonus or an employee based on the salary in performance
So the business rules if the performance rating is 5 the bonus is 20% of the salary
So here if the p rating performance bonus is 5
So let's add the bonus, the salary times 0.20 or 20%
If the rating is for a salary is 10%
else
Come again. This is a qualified so the long criteria. Maybe the rating is 3 to 1
Then the bonus will be salary time is 5%
So that's how you create a function this is the employee
So using the function and PL SQL block so
Ganun na natin siya gagamitin
So calculate bonus salary. We declare salary here then rating. Okay tapos magigamit na yung function
So here we expect the bonus is 10,000
So here using the function in SQL query
So pwede rin natin siya gamitin sa select so kanyari select employee salary calculate bonus
salary comma performance as bonus
from employees
so
May kita na natin yung
Maga ito yung magiging yung result nya
Using using that function
So we can use function in the select as well
So again handling error and showing errors so we add exception
Tapos ito yung mga common system defined exception like no data found too many rows
Zero divide invalid number and others so others catches all the others exception that are not
explicitly handled
So it best practices to always have exception
So you have something to refer to if error arise
Then is example of displaying the error so
Like this one so when zero divide we say DBMS put line division zero is not allowed
Since especially weren't when you're using division, okay
So
Always use exception then we are using SQL
SQL ERM, which is a built-in
Function in PL SQL so it provides the error message
Associated with the most recently raised exceptions
Here's you handle that so we'll again understand when others
so
Is to show you your error message
If you want to show error code you say you put SQL
Code
Here the example of handling multiple exception
I think it's done
This is the custom error handling so the bar we can we can have
Our own custom error handling so here
Don't forget to declare
invalid operation as exception
Okay
Baka
Dumeratiya kayo sabigin so be sure na ma declare nyo muna as exception yung variable na yun
so pag narration yung
Variable na yun as exception pag narration yan
Meaning depends a logic
Yun mapupunta siya sa exception. So a relation exception na yun, then it will fire whatever the
Logic you want to do like if you want to roll back or if you want to log in the database
So you can do it you can do it
So again key function so SQL code returns the numeric code of the last exception
then the SQL SQL ERM
Returns the human readable error message associated with the exception
kasi yung mga error code ni
Oracle is yung or a the 0 1 4 7 6 which is not relevant to the user if we can show it
then describe function name, so if we want to check or
if we have a lot of
Function we can
type describe to see
What are the what is that function what it does do and the source code?
For example in this instance we sorry for example in this instance
We type describe calculate total price so the function will be total price and it will return a table type
ano yung mga
Argument name so P price anindita type number tapas if it is in out or in out
so
This is the example of calling the function in PLS SQL block
So the output will be total price 110 then in SQL query calculate total price
So when to use the describe so to understand the structure of a function before using it in your code
You know to verify the types of
modes
so kasi misan they have the function pero di natalang kung in out but or in out
So describe command is available in SQL plus and SQL developer in similar tools
So the limitation of describe commands does not show function internal logic view the code use
so
Tapos
To inspect the internal logic of a function use the following query
You at all select text from all source or name
ano yung
Function name and and coanong type nyakong function procedure
It will show you the source code. I think we can we can try this one. Can you try this one in your end?
I think we have already have calculated calculate total price
Can you try on your end to see if the source code will appear?
So in SQL plus po namin gagawin? Yeah, pwede sa SQL developer
Walang lumabasayong surgery baka may function kapo bang ganun calculate total price
Higip pa po go down
Oops stop and functions
Calculate bonus. Try mo na lang po yung calculate bonus. Okay
So megita natin yung code, no
So
PL PL SQL function are versatile and can be invoked in various contexts below is the breakdown
Where how function can be called using the calculate area which we done a while ago
Then
Calling fields SQL
So ito naman yung nested function call
So nested function call in PL SQL allow one function
To invoke another to build more complex logic below is an example where a double area
Function call a calculate area. So salaw ng function may tinawag pa siyang function
Okay to compute and return, you know double area
So here we have input parameters length and width are passed to the double area
Then the nested function double area calls the calculate underscore area function to compute the area
So the the result from calculate area is stored in the local variable L
underscore area
so double area returns L
Underscore area times 2 or multiply by 2 which is double the calculated area
So this first ensure that the calculate area function is ready created
Then create the double error function when you gonna try it
Okay, but it's simply
after calculating the area just multiply by 2
So here's here's will be the example
So then remember in the in the calculate area we just multiply it so 10 times 5 is 50 then
The double will do multiply by 2 times 2 so that is 100
So
We can also function with default parameters
So in this case the tax rate the default of that is 10
So pay time like assign on default value don't set up trade
in this case in the calculate tax
So mandatory versus optional parameters, so
P amount is mandatory so no default value
While P tax rate is optional
So wala nilagay na tax rate so may default na kasi siya na 10
so the flexibility and function called the function can be called with or without
Specifying the tax rate kasi may default value na siya, but the amount should have value
The P amount
So yung tax rate walang pre-nobite na data
It will use the default 10, but if you pass explicitly for example 15 or 20
The function use that value instead of the default
So in this case we calculate the tax
1000 comma 12
So the tax will be 120 then here. We didn't pass the 12 so it will
Default to 10
So the answer will be 100
So calling function in select statement which aggregate function can be combined with aggregate function in SQL queries
Example using a function with aggregates is select some calculate area length with a
Total area so best practices is use appropriate context
Okay, then
Use
Validate the parameters and avoid overuse in queries
So this appropriate context yung function should perform lightweight
determinants of calculation in SQL queries to avoid performance bottlenecks
Use for simple calculation like area tax or discount
Then validate the parameter ensure that the function handles
invalid or null input gracefully to prevent runtime error
So we can do you can do if p-length is null or p-width is null then return zero
To handle missing values
Then when I say avoid overuse queries, it's using complex or non
deterministic function in SQL queries, you know that can degrade performance
especially with large data set
Yeah
So, I think let's have a break let's
Resume by 320 is that okay?
320 p.m. Okay
Dady magbukdaw tayo sa birthday ni mommy
Imaingin pa niyan?
Hindi naman, ikaw lang maingay
Hindi ako maingin na
Maingin ako
Manood ka si tutorial nito ninyo
Anong turo?
Ayano, SQL
Tanap ko, ano, ikaw yung nagtuturoan
No, underwater is teaching
Anong naginginig mo business?
No
Database, SQL
Shrug your career language
Pan ba niyan si nagturo?
Kasi kailangan
Ikaw naman naman do
Si atin ba
Kitang nyan?
Yung slide show mo
Balang naman naman nating?
Yes
Ikaw mababalang ka?
Yes
Pag tumuro ka?
Nagtunog ko, ako
Sige
Baka pangbalik ka malami
Baka pala-baka
Pag-back natin sa post-part
Hindi ka naman matan sa drums
Oh, ba?
Wala akong drums
Pwede pa?
52 minutes na?
Wala pa lang nga
Kanyang napag-a-lip si Ikaw naman
Kanyang napak-a-lip nang naging naging naga
Bwede di sabihanin siya sa uo sa'yo?
Wala sabihanin siya sa uo
Kasi di lang naging
Mabasang salad
Ano na?
Bago niyo
Bago niyo
Ano na?
Bago niyo
Bago niyo
Yan, good no?
Nakita natin yung trigger
Nag-a-nag-agraphyre
When we insert the data
It seems nag-fire yung trigger
So trigger works, correct?
Tapos to test the package
Pero up to 4 investors lang
Up to 4 investors lang, correct?
So medyo malaking investment siya lalabas
Hindi yung parang 500,000
Yeah, pwedeng
For example, yung isang kakailala namin
Kanyang iririkoy natin 2 million
So siya lang kausap natin
Tapos siya, marami siyang
May group siya na investor din
Basta siya lang yung representative or kausap
Kasi pag maraming investor
Based din sa
Ano namin, magulo
Otherwise you can give me more positive
Impact
Pag maraming investor
But for me I discourage
A lot of investor
Maybe
We can strategize
We can build
Shares
They can buy a shares but they have no
Voting power
At least what I'm saying is
Only poor person
Can have a voting power
Yeah, we do it
Like share
Hindi ko pa nakakalculate
Yeah, ganun
For example, ako invest 2 million
Invest 4 million
Mas malaki yung shares mo
Tapos siyempre
May voting power ka din
Ganun, when it comes to decision
Kumaga
I'm planning to build a corporation
Wherein
May board tayo
And you're one of the investor
Para pagdating ng meeting
Here's the plan
May voting power
Tayo
Kung ko'y ko'y pinamataas
Mas matas ang voting power
So, ibig sabihin ninyo
Pwede pa kami
Kumili ng shares
1% for example
Yes
Siguro yun
Maybe we can ask yung presyo per share
Kung paano ko kailalagay
Kung magkana yung
Valuation ng company
We'll make it
Legal lang kasi I cannot
As
Investor agad-agad
And I learned
I should be
Allowed to
Accept
Investor
So, I just make
It legal for us also
For your protection as well as
Investor na you're protected
With the law
And also the valuation
Of the company so we will invite
Auditors and other business
And we will show
The financials
And how much already invested
In SSBC then
We can evaluate it
And we can calculate how much
Per share
Yung amount
Yung mga SSBC established
As a corporation na ba?
Hindi pa po
Yes po, next year pa po
January
Partnership?
Soul po
It's under my name
Next year sir
Lalabas siya sa ano
Parang iriregister natin siya sa set
Yes po, correct
Parang magkaka
Secondary license ka
To sell shares
Yes po, correct
Ano timeline yan
Kung ganoon ang plan
Mga middel of next year pa yan?
Mga Feb or March po
May ayabagda accounting na tapos
Maglalakad na
Top na mga nga yun pa yun ay
Kasi for us to expand
And
Magawa yung mga plans
We think
Talaga na we need help of
Others people's money or investor
Na passionate
But since it's for
Kulang na kulang talaga
Parking space
Very ample parking space
Marami tayong parking space
Hindi kaya
Hindi naman
Outside
Pangalawa
It's really for parking
By the way my church is in Jiel
Jesus is Lord Church po
Yes po
Yes po
Still active in your church
Yes po
In the ministry po
So I have 5 minutes
Yes
Brother Perry
So
May question lang ako last
For example
In investment amin is 1 million
That's it
But that's the ideal
Pero again nothing yet is final
We might
Make a final proposal
Or
Based on what we discussed
Yes
Correct
Correct
Yes sir Perry
Basically what
To redeem the stocks
So may mga legalities pa yun
Correct yes po
And you're allowed to
Sell shares
Based on the valuation
Thank you so much
Thank you so much for your time
Yes po
That's for sure
Yes po
Yes po I will
That's the way I work
Thank you so much
Thank you Ram nice to meet you
Thank you bye bye                

on 2024-12-10

language: TL

                  WEBVTT                

on 2024-12-10

language: EN

                  WEBVTT
You logged in your Google account?
There you go.
You logged in your Microsoft account?
You have a Microsoft account now?                

on 2024-12-10

language: TL

                  WEBVTT
it will be invalid.
So, for managing, finding the subprograms, so for managing, to manage the complex database
applications is crucial to understand the dependencies and hierarchy of the subprogram.
So using dependency queries helps identify how subprograms relate to each other.
So, the advanced feature of the advanced debugging features is using the CSTIME stomp.
It's for precise logging with date and time.
Also, dynamic levels, adding conditional logging levels like debug or info or error to filter the messages.
Then to log to table, so to extend the debug package to insert logs into a custom debug log table to persistent storage.
Okay, so here's the example of logging a message per debug package.
Then our expected is we will have a info here, process started, then the debug here, intermediate value calculated, error, division by zero, and counter.
Then here it's calling the debug package or tracing the execution.
So, here the execution flow, the procedure start by logging the input value using the debug package.
It checks whether the input is valid or non-negative.
If invalid, it logs an error in exit.
For valid input, it calculates the square root and logs the result.
So the debug levels is the debug, it's used to trace the start of the computation.
Then the error is logged when invalid input is encountered.
Then the info is used to display the computation result.
The benefits of that is, first is enhanced debugging.
So the debug package captures both normal execution flow and error condition making troubleshooting easier.
Also, clear logs using multiple debug levels like debug, info, error helps in identifying issues quickly.
And also reusable logic so this structure can be extended to other subprograms for consistent debugging and error reporting.
Best practices is doing that is handle edge case.
So ensure input validation and error handling cover all potential issues.
Then integrate debugging with business logic.
So use debug logs to capture intermediate state and critical calculation.
Also, toggle debugging so it introduce a mechanism,
for example in a package level variable to enable or disable debugging dynamic during runtime.
So yeah, so positional and name notation.
So positional notation is parameter are passed in the exact order they are defined in the subprogram.
It's simple and concise for short parameter list.
Name notation, it's parameter explicitly named when passed to the subprograms.
It enhance readability especially for long parameter list or when some parameters have the default value.
Then mixed notation, it combines positional and name notation.
So one of the restriction of that is name notation parameter must come after all positional parameters.
So the benefits of the name notation is it improves clarity
because it explicitly identify the purpose of each parameter.
It's also reduce ambiguity particularly for subprograms with similar parameter types.
It also support default parameter so it enables keeping parameters with default value.
So best practices, use name notation for long parameter list.
Avoid mixed notation unless necessary.
So if use ensure potential parameters can first to prevent syntax error.
Then leverage default value.
So use name notation to select the override default values without specifying all parameters.
Okay, so here's the default values for parameters that allow subprograms procedure function
to be extended without breaking existing calls.
So if a parameter is not passed during invocation, the default value is used.
So unlike this, we have an old call.
We have one parameter here.
So it's the order ID.
So it still work even though we don't have the D priority because it has a default value normal.
So in the new call, so we pass order ID and we pass priority.
So the high will be used instead of the normal.
So recompiling procedure and function.
So why recompile?
So dependency updates, changes to reference object like tables or views or other subprogram
might invalidate dependent procedure or function.
So make sure you recompile your subprograms, your main function.
Ensure these dependencies are resolved.
Then also you have to fix invalid subprograms.
Kasi when a subprogram becomes invalid, recompilation attempts to restore it to valid state.
So for example, in that case, so query invalid objects.
So user object table is queried to identify valid objects.
The status column filters object that need recompilation.
So recompile objects.
So the execute immediate statement dynamically generates and execute the alter compile statement.
So both procedure and function can be recompiled using this method.
So object type, the query handle multiple object types like procedure, function, and package.
So example output will be no visible output, meaning the block does not generate direct output.
Or check object status, use the following query to verify the status of the object after recompilation.
So best practices, test after the recompilation, track the changes, and use specific recompilation.
Recompilation procedure and functions ensure the integrity and stability of your PLSQL application,
particularly after schema modification or dependency changes.
So yeah, let's begin the exciting part.
Let's dig more about on the triggers.
So what are the triggers?
Triggers are PLSQL block that execute automatically in response to specific database events.
So events like can occur in insert, update, delete, operation on a table view, or timing.
Triggers can run before, after, or instead of the event.
So the key components are the trigger name, the timing, kung before ba siya, after, or instead of.
Then anong event, insert, update, delete, or combination of these.
Then the scope, so for each row, execute the trigger of each affected row or in the row level.
Then omit the clause for statement level triggers.
So let's do the coding.
Can you visit the PLSQL interactive coding?
I prepare coding sample test cases there.
So let's start with slide 338 please and try it in your computer.
Just open the link.
Let me know if you can open the link.
I prepared it in Notion to make it more presentable and easy to read.
So let's go for the slide 338 first.
Let me know if you're able to open or not.
Thank you.
Okay, yeah, so let's jump into it.
Let's start coding.
Mas okay po ba yung document today?
Nga pa, sir.
Yes, po.
So I'll give you 5 to 10 minutes to try that.
Also I prepared the test cases.
Please let me know if we're good to proceed.
Making sure yung EMP ID, ayun oh.
So nakita natin na updated tapos nag-calculate din siya ng bonus.
So meaning the trigger works.
So best practices dyan is revalidate the data.
Ensure that only meaningful changes.
For example, actually salary updates are lagged.
Also let's monitor the performance.
So regularly clean up the employee audit table to avoid excessive growth.
Then also add error handling.
Incorporate troubles exception handling case the employee audit table is unavailable
or encountered as an error.
Let me know if you want to proceed on the next coding.
On each slide, I really prepare a coding for you guys.
I-check nyo rin yung table ako nagbabago ng data.
What are the output?
So making sure to check the employee's table or the audit table or the employee audit table.
Let me know when we, when it's okay to proceed to slide 339.
Okay, last 5 minutes.
Christine, pati nga po nung lag employee changes mo na trigger.
I'm looking at your screen right now.
Okay, so nag-insert naman siya.
Pero wala pa rin ano no?
Nag-insert na ba siya din sa table mo na audit?
Wala pa po.
So meaning hindi nag-trigger, correct?
Hindi nag-trigger.
Kinukumit ni naman after nai-insert nga.
Let's try to run that.
Unique constraint.
So try, yeah, let's try that.
403.
Yes.
Okay, try to run with the commit.
Run.
Okay, so hi earner.
Refresh.
It's working.
Yes, sir.
One moment.
Let me check the code.
So lag employee changes.
Insert into EMP old salary.
New salary.
Can you try to add output dun sa trigger nyo?
Lagyan natin ng DBMS output kung talagang nag-lag siya.
Ah, siguro hindi pa tayo kasi nag-create ng ano?
Yung old salary.
Wala po kami dun.
Ah, yung old one, sir.
Wala pa bumi.
Yeah.
Mag-alter tayo ng ano.
Let's create old salary.
So it might not working because of that.
Nagita yung column na old salary.
Also, let's add DBMS output put line after ng values.
Yung sabi gain insert into para makita natin na madetect natin na nag-update siya.
Kasi ang nadetect nila na trigger yung hi earner, no?
Yes.
Or kasi yung trigger na ginawa natin is after update.
So pag nag-update lang, hindi naman pag nag-insert, after insert.
So after update lang.
So let's try to...
Yeah, so matitrigger lang siya.
Kung nag-update tayo, hindi naman natin sinabi na after insert gawin niya to.
So try to update.
Kanyari mag-update tayo ng any table dun sa employee.
For example, let's update the salary.
Okay.
So let's use the test case.
One moment.
Alam ko may update ako diyan ginawa.
Yun, yung test case one, yung update employee set salary.
So let's try that.
Dun sa yung test case one, yung may update.
Masa slides 338 pa rin po kayo, no?
Ayun, nakita ko may...
Meron na si Mam Teen.
Nag-try ka pa mag-update Mam Teen tapos nag-blog po sa audit, employee audit.
Tama po, no, Mam Christine?
Try nyo po yung ibang mga use case na pin-appear ko po diyan.
I love her.
Yes po.
339 na po?
Ano po?
339 na tayo?
Sige.
Apo.
Let's go.
So let's try 339 please.
Timer na natin para hindi masyadong marang oras.
So 10 minutes.
Timer start now.
So 339 na po tayo.
So statement triggers.
While you're doing that,
statement triggers,
the concept of statement triggers,
it execute once for an entire DML operation.
So regardless of how many rows are affected,
this type of trigger is useful when
we want to perform operation like logging or enforcing business rule at the statement level
rather than the row level.
So I prepared examples to you in slide 339, statement triggers.
So ang key point natin dyan is statement trigger execute only again once per DML operation,
not for each row affected.
So they are the ideal task for like plugging an action
or enforcing database level rules.
So 8 minutes to try the statement triggers.
Since you already have the employee table and the audit table,
so you'll just use that as trigger.
Let's move na sa 340.
Gawan naman tayo ng set update time.
So it's row level triggers.
So create tayo ng trigger na set update time.
Tapos ang timing natin is before update on employees.
So row level trigger execute once for every row affected by the DML operation.
This is in contrast to a statement trigger which execute only once per statement.
To create a row level trigger, we use for each row.
So these triggers are, let's start with that, 10 minutes.
So use case natin dito is these triggers are particularly useful when we want to apply changes
or validation to each row individually.
Such as automatically updating timestamp,
enforcing business rules at the row level,
and logging detailed changes for each modified row.
So we step by step.
So we define the trigger name,
set update time in descriptive to its function.
Then the timing trigger set to execute before update.
This means it will run before the actual update operation is applied to the employees table.
Then the row level clause,
the for each row clause ensure that the trigger files for each row being updated.
Sir, so meaning ang mangyayari po,
si trigger muna talaga yung unang babasahin ng script
before talaga niyay run yung update statement sa ano?
Correct, yes.
Kasi yung timing natin is before update.
Seven minutes for row level triggers.
Mam Tin, natry mo na po yung ano?
Yung 340.
Sir Jerry, natry mo na po.
Last six minutes for trying the row level triggers.
Be sure to check the data kung nagbago anong preview state nya.
Last four minutes.
Also try the test case four which has invalid update
to simulate an update triggers, an error or constraint violation for example.
Last two minutes.
Okay, for those who are done with 340, let's jump with 341.
Okay na po.
Yes, 341 na po.
Yeah, 341.
So I also include additional notes tapos I show expected result.
So yung 341, it's pano na pag magmay when.
So when clause to restrict trigger execution to specific condition.
So imagine we want to log only specific changes such as
when the employee's salary exceed a certain threshold after an update.
So instead of running the trigger for every update operation,
we can use the when clause to limit its scope.
So again, the when clause works with both statement level and row level triggers
but it's most useful with row level triggers where you can use colon new and colon old
pseudo records in a condition.
The colon new refers to the new value after the operation.
The colon old refers to the original value before the operation.
The when clause must reference valid expression and condition.
So that's last seven minutes to test that with other test cases.
Also don't forget to read my notes there to test effectively.
Last three minutes.
If you want to clear your audit table, you can truncate the table.
So let's move on slide 342 please.
The selective trigger using if.
So yeah, let's talk about selective trigger while you do the coding
and how we can use if condition inside the trigger bodies to handle complex logic.
So sometimes we need more flexibility with the simple when clause offers.
This is where this if statement comes in handy within the body of the trigger.
So imagine we have a company policy that prevents deleting the HR department
while other departments can be deleted.
The HR department must remain intact.
We can enforce this rule using a trigger with an if condition.
So let's break it down.
So we have the trigger name prevent, depth, delete that indicate the purpose of the trigger.
Then we have the timing.
So the timing natin dito is to execute the before delete.
This means it runs before any delete operation on the department's table.
Then it is our row level trigger.
So we use the for each row clause ensure that the trigger execute for each row being deleted.
So our condition inside the trigger body is the statement.
If the statement checks if the value of all department name or depth name is HR.
So our action if the condition is true.
So we raise an application error is invoke.
So an error code of two zero zero negative two zero zero one
and an error message cannot delete HR department.
So if the condition is met the delete operation is prevented and the error message is played.
So yeah I prepared test cases please try it.
So eight minutes more to try it then we will have a short break.
Readable naman po yung document no it organized much nicely po.
So last seven minutes to test it then we will have a short break.
Nung pwede ka maglagay ng else tapos mag raise tayo ng application error.
Pero this time for example two zero zero two HR about to delete parang ganun.
Para makita natin na hindi nga siya pumasok doon sa condition na yan.
Sir Miguel or everyone let's try to remove the exception.
The exception when others then remove natin yung tapos i-recompile natin tapos let's try the test cases.
Yun yung possible issue kasi naglagay tayo ng exception.
Guma na po sir Yusef sir Adrian.
So yun yung nakita natin so when we remove the exception block kasi so I realized na yung exception block was unnecessary.
And it also might be misleading in this context kasi kapag nirase na yung application error it executed immediately.
And it stopped the current operation which in this case the deleted the delete case.
So yeah maray pa tayong i-code so let's move on to three four three please.
Yung not equal mo sir Miguel guma na po kanina nung inaris mo pa yung exception.
Okay yun tapos if you're ready na let's go with three four three four four actually.
So while I'm explaining the the test let me highlight the three four four.
So you know yung showing errors and triggers so triggers can enforce business rules by erasing errors.
When certain condition are met let me check baka may exception din dito let's remove the exception muna.
So I'll give you 10 minutes to try kasi marami pa tayong mga i-code and we have final exam.
Your learning so far po sa actual coding natin.
So again yung trigger details natin so we have the trigger name validate is it correct yeah the validate.
So dito sa sample ko validated salary but yung gagawin natin is yung three four four.
Which is yung commit in triggers.
So basically yung when the trigger executed within the same transaction as the triggering statement.
So therefore commit rollback and save point statement are not allowed directly inside the trigger.
However in scenarios where independent transaction are necessary.
For example lagging action autonomous transaction can be used yun yung pragma autonomous transaction.
So by using yung solution natin is yung autonomous transaction nga so by using pragma.
Autonomous transaction directive you can create a block within the trigger that operates independently on the main transaction.
This allow you to perform operations like committing data to an audit table without affecting the parent transaction.
I'm not sure why my video is lost let me fix my video.
Hello po.
Yes po I put a dual sample you can click that in the
in the
Google sheet dual sample.
To better understand yeah.
I also put example and description.
So dual table is a special built in table in Oracle database.
So its primary purpose is to provide a dummy table for performing operation that do not explicitly require a table.
Yun so kung gusto nating yeah just continue to test the dual.
So how to disable a trigger so just type alter trigger trigger name then disable.
To enable it just change it to enable then to disable all the triggers on a table.
Alter table employees disable all triggers.
So again to enable that alter space trigger space trigger name enable.
Then to enable all the triggers on a table our alter table which table enable all triggers.
So for the naming convention
using clear and meaningful naming convention for triggers help improves code
readability and maintainability.
Below are the recommended convention so
action based naming so format trigger underscore table
underscore action.
Yun so the purpose is to indicates the table
and the action that fires the trigger. So for example log
underscore an in table employee and the action action underscore updates.
Purpose based naming so for example trigger underscore table underscore purpose.
So indicate the table in the specific purpose of the trigger.
Then combination naming so for complex system you can combine both convention to include the action and the purpose.
So in our case for example trigger underscore table underscore action underscore purpose.
Sample is for example log which is the trigger and underscore employee the table.
Action salary then the purpose is to update.
So again use clear concise names to reflect triggers for purpose include both
the table
and the event action in the name of the better traceability.
Follow your organization naming standard for consistency.
So in this uh in the next section we'll cover the sample data like order film employee.
So let's dig more on the sample data. So here's the we create an orders table.
Then here we insert we already know that.
Then we query the output so we write a plsql trigger automatically change the order status to ship.
When the total amount exists to exceeds to 200.
So when we say new the total amount greater than 200 we change the status to ship.
Then
355 okay, so the film table represent a movie database storing details like title genre release year and ratings.
So here we create films.
Then we insert data into films.
Then we query the output so engage use case. So we create a procedure to get top films.
Uh genre
One moment
um, yeah, so let's I think let's try the
the genre so go to slide 357.
Let's create and table
Let's create the film and insert data then let's try to create procedure
and
create a test cases for the procedure. Okay, so slides 357
Here if we we can move on to the next sample.
Yes, okay.
So let's continue.
So here we already have the um, oops.
Okay, so let's continue. Um here we have uh, we already created the table employees
and we are able to insert sample data.
Um
so one moment three four nine.
So here we um select the employee then we create total department
salary.
So we just select the sum sum of the salary into total salary.
From employees where department id equals depth id. So I just return the sum
of all the total salary.
So the total salary for example in this case using
the procedure is 85,000.
Sorry the function.
Since we create this function.
So let's dive in into dynamic SQL.
So PL SQL enable as
enables a seamless integration of SQL statement directly into blocks
to perform database operation. This capability allows
for static and dynamic interaction with the database making PL SQL highly versatile for application development.
So
static SQL is embed SQL statement directly within PL SQL block used for operation that don't require
dynamic query generation. So always include exception handling blocks for
robust error management and performance static SQL statement in PL SQL are
pre-compiled resulting in faster
execution. So let's have samples. So let's do the
slide 363.
Okay.
I'll be right back guys three minutes, please try the code. Okay?
I'm back. Is it done? All good? Okay, awesome.
So while working, so
three advantages of using BIND variables, it's improved security kasi it prevent
SQL injection by separating query logic from user input. Also it enhance performance. It's allow the database
to reuse
execution plans. So reducing overhead and also flexibility makes queries dynamic well
maintaining control over variable substitution.
So one of the the three common errors in
in binding variables is the invalid placeholder. So ensure the placeholder is
start with colon, colon one, colon two, etc. Match the bind values using clause.
Also
some common errors are data type mismatch though the binds value data mass
match the columns data type.
Then the query syntax. So validate the query string for correct SQL syntax before executing.
Okay.
Yeah, so let's move on.
So dynamic SQL that is
SQL that constructed and executed during runtime.
It enables flexibility for a situation where the exact SQL command is not known during coding.
So yung use case natin dito is yung example handling unknown tables or columns.
Execute queries on tables columns not known at compile time.
Example generating reports from dynamically named tables.
Also executing the del statement that create alter and drop tables or other schema objects dynamically.
Example of that is creating a temporary table for processing.
Dynamic query generation. So construct queries based on runtime condition or inputs.
Example is filtering query result based on user experience user preferences.
And last is variable
table names or column values. So dynamically switch between table or columns at runtime.
So example of that is, you know aggregating data from
different tables in a loop.
And so yung basic syntax natin is
execute immediate then the dynamic SQL statement into target variable using the bind values.
So yeah, so yung benefit ng dynamic SQL flexibility efficiency and power.
So just be cautious lang. So security always use bind variables to avoid
SQL injection vulnerabilities.
Also the performance dynamic SQL bypass SQL statement caching.
Which might impact performance for frequently executed commands.
And also error handling use robust exception handling to manage
potential runtime errors.
Best practices, let's use bind variables to ensure security and performance optimization.
Keep it simple, avoid a really complex dynamic SQL structure.
Then for debugging log constructed SQL statement for debugging and audit rail.
And use exception handling so we can handle the error gracefully using the exception blocks.
So here's the example of constructing dynamic SQL. So here we declare, sorry.
So we declare then here we use our dynamic SQL query.
So we
put it in the big query and
we assign an amount. So let's try that. So let's go with the
native dynamic SQL. So let's try the slide 366, please.
Okay po. Nasa 366 na po. Nakita niyo po yung link.
So mas maintindihan natin siya no. So nagsin sa
If you're looking at the slide 366, nag-declare tayo ng mga default value.
Tapos pinasa natin siya binayid natin din sa big query.
Yes, sir. Puro binding siya ngayon. Yes.
I also prepare mga test cases.
I-try natin yung test case 2 also we insert with missing column.
Tapos mag-try tayo mag-annun ang invalid column.
So I hope yung mga test cases it'll help you to understand more
dun pa sa topic na on each topic.
Let me know po pag if you want to proceed now or if you have any question.
So while doing that, yung advantage ng NDS or
Native Dynamic SQL is simplified syntax. So it eliminates the need of
explicit handling of cursor and binding like in the DBMS SQL package.
Also it improve readability, more intuitive and easier to maintain
than low level dynamic SQL mechanism. Also it's efficient because it's offer better performance
due to its direct integration with the PLSQL engine.
Let me know if you want to proceed on our next practice.
Which is DDL and DML with dynamic SQL.
So the DDL is the create outward drop and the DML is insert
update statement that are not possible in static SQL.
So slides 367.
Okay na po tayo sa 367 or you still need more time.
So again yung dynamic SQL, it allows us
executing the DDL or data definition language and data manipulation language or DML statement
at runtime. So this flexibility is particularly useful for operation that involve variable object
names or structure. So yung na po tayo na yung dynamic query. So execute immediate
statement is used to be execute a dynamically constructed DDL statement.
Like in our case it's a create table.
Then creates a table named temp table which with column ID and name.
Then the result should have new tables created in the database.
So again best practices for dynamic SQL use exception blocks for gracefully handling errors.
Testing validate the constructed SQL string before execution to avoid runtime error,
runtime errors and security use parameter binding to prevent SQL injection when
constructing queries with user input. Let me know po if good na tayo sa 367 so we can proceed. Thank you.
Okay na? Okay good. Thank you for that.
Now let's try naman yung constructing and executing dynamic queries. So let's do the slide 368.
So dynamic SQL na it's providing flexibility to construct SQL statement at runtime. So allowing
for dynamic update, insert and other operations. So we are now in slide 368.
So kalangan natin ng employees table. I think which you already have created.
Then we can jump on the test case to update the salaries for department one.
So change na lang kung depth ID on. So where depth ID okay.
Also you can you can change the one to whatever the ID you like.
So let's move forward.
So here we using DBMS SQL. The package is provide advanced capabilities
for dynamic SQL execution including handling cursor and managing complex
functions. So let's see what's in the package.
So here we have the
As you can see yung difference nung DBMS SQL sa execute immediate. Yung execute immediate
as you remember na ginagawa natin kanina it's very simple it's straightforward dynamic SQL.
So if depending sa use case nyo kung or depending sa kalangan nyo kung gagamitin nyo ba DBMS SQL or
execute immediate but the execute immediate if the query or statement does not require extensive parsing
or dynamic handling of bind variables use the execute immediate. Yung advantage nung execute
immediate is ease of use kasi mas cleaner siya and easier to write for scenario involving dynamic SQL.
Also performance optimized for most cases so the SQL pars and executed in one step.
It's also support bind variables so you can pass parameters using the using which make it secured
and efficient. So more concise siya compare sa DBMS SQL. Ang limitation nung execute immediate is
limited lang siya flexibility for handling dynamic column
column list or dynamically constructive returning clauses so it cannot also dynamically
bind variables or columns by name. While yung DBMS naman it's a complex dynamic SQL where you need
kapag magkakonstra kayo or magbabind kayo nung query pars for example column names table names or
also it's dynamically determined query result or columns type at runtime and it execute queries
that require handling cursor or dynamically fetch result. Ang advantage naman ng DBMS SQL
yung flexibility niya because it can handle more complex scenarios such as dynamically determining
table or column names. Also cursor management kasi it provides control over cursor
cursor and result set allowing for complex operation like multiple fetches and yung dynamic
bind variables niya that it allow us to dynamic binding by name or position. So ang limitation
lang ito it's complexity it's more verbose and harder to write compared to execute immediate
and performance overhead it requires parsing and binding separately which might result in
you know slightly lower performance for simple operations. So my recommendation is use execute
immediate for most scenario where the SQL is relatively straightforward. Bind variables are
sufficient to pass dynamic data. Use DBMS SQL if you need to dynamically determine table or column
names if you require to control over cursor dynamic bindings or complex query processing
and if you have scenarios where the number of type bind variables is not known at compile time.
So in most cases execute immediate is preferred because of its simplicity and better performance
for common use cases. However for advanced and highly dynamic scenario DBMS SQL offers
because of I think the data type can we check yung employees natin?
Yeah yung employees table tapos pa na natin define yung columns tapos
so worker 50 check natin pa natin din clear doon
yeah worker two tapos how do we
it should be match the data type properly
so dun sa defined column asa na yung bcursor id
so kasi identify natin na 50 yung i think your first name correct
so yun po
yun so siguro let's have a launch tapos balik po tayo ng 1 pm okay po
so you want more 10 minutes to test the code
okay so let's back at 1 pm okay one hour break thank you sir miguel                

on 2024-12-10

language: EN

                  WEBVTT                

on 2024-12-10

language: NN

                  WEBVTT                

on 2024-12-10

language: EN

                  WEBVTT
immediate to show the single row select example. So here we have a dynamic query structure.
The queries is stored in the variable vantical query with a placeholder column one for the name
then the parameter binding and the answer is passed in a negative query using the using clock.
I provide more example on 370. So here we fetching the result the execute immediate statement
execute the query and the result is stored in 0 then the output is 0. So I provide test case
if it's invalid if no employee found so let me know if you want to proceed to the next slide thank you.
Yes
Yes
Yes
Yeah okay yeah thank you thank you so yeah so we can create
put it somewhere or I can export it to compile
then for dynamic directory management
so we're okay so we're good now with
so you can they you can they now create the directory and use it for utl file operation
so here we create our place directory so you can use it to execute immediately dynamically create that
then of course we have to ensure that we have the
proper permission and the grant access to make directly accessible to the user the brand
so next one so collections are data structure that can hold multiple elements of the same data type
they enable efficient handling of these arrays or records so there are three types of collections
associative arrays formerly known as index by then the nested tables that can store an
unbounded collection of elements suitable for scenarios requiring multi-level data
and we are is or vertical variable size arrays so picks up or size useful for
small collection or sizes okay so let's see the other example yeah so
so again it's a collection it's a data structure in those that it holds multiple values of the
same data so
so so it's more information but yeah you can try the example
slide
then collection collection can be iterated using
loops like for loop then arrays and arrays so for example
which is we are in
so order arrays with fixa then number three has set nothing and that's the table sample
nothing so it's an order collection it's a similar uh in function of the duplicates are
an associative array
it is a dynamics for sp value pairs index by number or so what are your benefits of using
plsql collection so it's efficient in data processing it reduce context switching between
sql and plsql also dynamic manipulation because it's handled unknown or dynamic
data structure effectively but operation as well perform both
methods are updated in but connect and for all which we will have some all later on
and also provide code organization because it encapsulated complex logic with usable data structure
also it enhanced performance in memory operation or passive compared to database level processing
so what are the best practices in using the collection use the right collection
so use associative array for sparse data and lookup tables use less than tables for bulk operation
use v arrays for ordered fixed size data set optimize performance use bulk connect and limit
manage memory by cleaning and use elements and also avoid complexity keeping collection
operation simple and predictable avoid performance and again use exception handling
always handle exception like no data found or value error when working with that collection
until sql collection are powerful tools for managing data efficiently within oracle databases
so by leveraging this structure developers can implement complex data operations and optimize
performance and maintain clean and moderate understanding the hierarchy it's very important
the changes in the cable structure when the whole type is used and custom records can be defined
it also improved readability so group and
example in this case you have this assumed table employee and the output is
index or before its index by tables arrays where they are stored using numeric
string based on indexes so in features index type of course numeric binary figure or string
base marker too dynamic annotation so no predefined size required six numbers so it's false support
non-convictus indexes is keeping for example keeping indexes then efficient lookup best suited
for lookups or so here we have declared so we declare the salaries of num array as an associated
array type with numeric indexes then assignments so values are assigned the indexes one and five
demonstrating the ability to keep indexes and dynamic behavior no need to pre-define the size
of the array output so values are previously respective indexes and displayed using dbms
output put line so our real world use cases division caching on beta so could we access data
from the cache for performance parts dt story so under scenario where intermediate values are not
then look up tables use the memory table for keep the three value pairs
so nested tables the month is some unvalid collection stored in the database or manipulated in memory
so indexing again it's index basically starting from one dense and
is far so initially dense but division can create gaps so maybe it's called sparse array so storage
can be stored in the database column or used in memory detail as your drop
one of the features as well for nested tables dynamic expansion so new element can be added
at runtime here we have declaration so non-table is declared as a nested table type of number
so number is initialized with values one to three and we have the data expansion that that
extend method that adds a new slot to the table then is forced behavior that that delete method
removes the value specific index creating a gap and every hand being so attempting to access the
results in the node data from error so under in these
so here's the sample of the various so various or variable size array our connection and
cholesterol with a fixed upper bound unlike nest and table or associated arrays they must be dead
so keep features nice bounded size so the maximum size is specified during the
closure cannot exceed this limit then structure so all elements are contagious meaning there are no
gaps or sparsity ordered storage element retainer order and access the integral subscript
ideal usage are useful when the number of elements is known and does not vary significantly
so here we declare v array underscore type as type is defined with a maximum size of five
so fruits is initialized within three elements of all banana and cherry so elements are accessed
using the index starting from one so dynamic expansion we can put that extend method and
still have for new element to fill the maximum size x is size is exceeded so iteration we can use
uh use a whole loop within the fruits that come to iterate through and through all elements
um
also predefined groups um use for cases where the number of elements is predictable
such as a list of weekdays or months so advantages is your compact designer so ideal
for a small data set long that was order of preservation so we ensure that consistent ordering
of elements that was is of use it's very simple and straightforward for unpredictable data sizes
beyond the declared limit and dense only does not allow gaps or sparse data
so let's have an example um so lastly the index by table or associated arrays
also known as associated arrays are powerful sql structure used for temporary in memory data
they allow you to store p-value pairs with numeric or string based keys making them ideal
for lookup tables or caching some features not dynamic insights automatically grows as
element are added flexible indexing it support numeric binary figure or string
and making it suitable for data set with non-configure not persistent data is stored
only in memory and does not persist across session so here we declare price table is defined
so we initialize the table tables dynamically populated with
then we can access the data using the key to fetch the associated by for example price
and limitation is not persistent data is not stored in the database
indexing constraint only support numeric and string indexes limited size restrict by available
memory so let's try the um but before that we we know that
we can have a direct assignment like this then using loop we can have the assignment based on
so let's try the non-existing elements um please do the slide three nine six please
it will raise an exception
okay
you
you                

on 2024-12-10

language: EN

                  WEBVTT                

on 2024-12-10

language: EN

                  WEBVTT                

on 2024-12-10

language: EN

                  WEBVTT                

on 2024-12-10

language: TL

                  WEBVTT                

on 2024-12-10

language: NN

                  WEBVTT                

on 2024-12-10

language: NN

                  WEBVTT                

on 2024-12-10