12:04
2024-12-10 13:02:57
6:53
2024-12-10 13:18:23
3:49:58
2024-12-10 13:29:28
9:46
2024-12-11 07:45:15
5:40:21
2024-12-11 09:08:49
5:22
2024-12-11 17:54:14
1:42
2024-12-11 18:04:44
2:05:58
2024-12-12 09:00:08
1:01:06
2024-12-12 12:26:38
3:59
2024-12-12 15:09:36
51:24
2024-12-12 15:24:00
8:06
2024-12-12 16:34:52
2:35
2024-12-12 17:17:14
3:05
2024-12-12 17:26:54
Visit the ORACLE PL/SQL Fundamentals course recordings page
WEBVTT
-->
Okay, let's start. Good morning everyone. Today is the last day of our training. Lahat po ba ay present?
-->
Good morning. So please brace yourself kasi puro coding tayo today which you love doing.
-->
So I prepared a lot of coding sa interactive natin and I present it nicely.
-->
So let's continue the discussion.
-->
Okay, awesome. So yesterday we test this function.
-->
So if we drop dependent function, so the impact of that is the other function that is dependent on it will be invalid.
-->
Okay, so for managing, finding the sub program, so to manage the complex database applications is crucial to understand the dependencies and hierarchy of the sub program.
-->
So using dependency queries helps identify how sub programs relate to each other.
-->
So for example, we can do this query if we want to find all the dependencies, for example in this function.
-->
So in this case, if we type this script, we will get the get length. It's a function. And get width is a function.
-->
So all these two is the sub programs of this main function, the calculate area.
-->
So bago, para bago mag delete, for example, you're intended to delete or drop the get width, you're aware na, oh, it's dependent on the calculate area.
-->
So the calculate area will not work or considered invalid since the get width function will be dropped or missing or deleted.
-->
So here's the example of a function when creating a package.
-->
Okay, so we're gonna try it in a bit. So it is straightforward. So it's just showing how we can debug a package.
-->
So a debug package is designed to streamline the debugging process by logging execution flow, parameter values, and intermediate result during the execution of PL SQL logic.
-->
So the advanced feature of advanced debugging features is using the sys timestamp.
-->
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 is 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.
-->
Also 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 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's 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 selective 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 priority because it has a default value normal.
-->
So in the new call, 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 others of program 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 subprograms becomes invalid, recompilation attempts to restore it to valid state.
-->
So for example, in that case, query invalid objects.
-->
So user object tables query 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 recompile 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 a 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.
-->
So let's jump into it. Let's start coding.
-->
Mas okay po ba yung document today?
-->
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 ayano.
-->
So nakita natin na-updated tapos nag-calculate din siya ng bonus.
-->
So meaning the trigger works.
-->
So best practices dyan is re-validate the data.
-->
Ensure that only meaningful changes.
-->
For example, actually salary updates are lagged.
-->
Also let's monitor the performance.
-->
Regularly clean up the employee audit table to avoid excessive growth.
-->
Then also add error handling.
-->
Incorporate troubles exception handling in 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 coding for you guys.
-->
I-check nyo rin yung table ako nagbabago ng data.
-->
What are the output.
-->
So make sure to check the employee's table or the audit table or the employee audit table.
-->
Let me know when it's okay to proceed to slide 339.
-->
Okay, last 5 minutes.
-->
Christine, patikin niya po ng lagged employee changes.
-->
Mana trigger.
-->
I'm looking at your screen right now.
-->
Okay, so nag-insert naman siya.
-->
Pero wala parang 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 na after na-insert nga.
-->
Let's try to run that.
-->
Unique constraint.
-->
So 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 lagged employee changes.
-->
Insert into EMP old salary, new salary.
-->
Can you try to add output doon sa trigger nyo?
-->
Lagyan natin ng DBMS output kung talagang nag-lag siya.
-->
Siguro hindi pa tayo kasi nag-create ng ano?
-->
Yung old salary.
-->
Wala po kami dun.
-->
Ah, yung column, sir.
-->
Wala po kami dun.
-->
Yeah.
-->
Mag-alter tayo ng ano?
-->
Let's create old salary.
-->
So it might not working because of that.
-->
Nagita tayo ng column na old salary.
-->
Also, let's add DBMS output put line after ng values.
-->
Yung sa begin insert into para makita natin na madetect natin na nag-update siya.
-->
Kasi ang nadetect nyo lang na trigger yung hi earner, no?
-->
Or kasi yung trigger na ginawa tin 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 ito.
-->
So try to update.
-->
Kanyari mag-update tayo ng any table dun sa employee.
-->
For example, let's update the salary.
-->
So let's use the test case.
-->
One moment.
-->
Alam ko may update ako dyan 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 ma'am Tin.
-->
Nag-try ko pa mag-update ma'am Tin.
-->
Tapos nag-blog po sa audit, employee audit.
-->
Tama po, no ma'am Christine?
-->
Try nyo po yung ibang mga use case na pinipar ko po dyan.
-->
Yes po.
-->
339 na po?
-->
Ano po? 339 na tayo?
-->
Sige.
-->
Let's go.
-->
So let's try 339 please.
-->
Timer na natin para hindi masyado marang oras.
-->
So 10 minutes.
-->
Timer start now.
-->
So 339 na po tayo.
-->
So statement triggers.
-->
So while you're doing that,
-->
so statement triggers,
-->
so it's a concept,
-->
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
-->
and 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.
-->
Magka para.
-->
So it's 339 chat.
-->
Sige, 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 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 niya i-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,
-->
ano yung preview state nya.
-->
Last four minutes.
-->
Also try the test case four,
-->
which has invalid update
-->
to simulate and 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 mag may when.
-->
So when close to restrict trigger execution
-->
to a specific condition.
-->
So imagine we want to log only specific changes
-->
such as when the employee's salary exceeds
-->
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 let'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.
-->
Okay, 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 a 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
-->
before each row being deleted.
-->
So our condition
-->
inside the trigger body is
-->
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 invoked. So
-->
an error code of
-->
negative 2001
-->
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
-->
8 minutes more to try it.
-->
Then we will have a short break.
-->
Readable naman po yung document no?
-->
It organize much nicely po.
-->
So last 7 minutes
-->
to test it. Then we will have a short
-->
Sir Joseph, try mo nga po
-->
compile ulit yung trigger mo po.
-->
Sir Joseph
-->
na compile na siya.
-->
Na-delete yung HR mo
-->
Sir Joseph. Or may HR ka ba
-->
dati?
-->
Check mo yung
-->
check mo yung
-->
kunaka before
-->
update
-->
before
-->
delete sorry before
-->
delete on department for each row
-->
if all department name.
-->
Tos mag-add ka ulit ng HR.
-->
Try natin ulit.
-->
You change it to department
-->
space name.
-->
Last 2 minutes, we will have our break.
-->
We will resume at
-->
10-20 a.m.
-->
So we will have a 20-minute break.
-->
Sir sorry, nilalaro ko po yung phone ko.
-->
Sino po ang nagsasalita?
-->
Si Miguel Pardus?
-->
Si Miguel po. Yes po, Sir Miguel.
-->
Game.
-->
Yung HR po, diba
-->
igual siya originally?
-->
Yes.
-->
So dapat
-->
pag nagburuh ako ng
-->
pag binuro ko na si HR
-->
dun sa table
-->
kapuburahan na dapat siya to.
-->
Tama po diba?
-->
Oo tama kasi not equal eh.
-->
So ibig sabihin kung HR siya
-->
Nag try ako, hindi pa rin siya
-->
nabuburahan.
-->
Yung HR?
-->
Apo.
-->
Nerecompile mo naman yung trigger no?
-->
Yes po.
-->
Sir try ko na ko yung delete.
-->
Nakatingin ako sa monitor mo po.
-->
Successful.
-->
Ayun sir, nandun pa din.
-->
So expected mo dat
-->
madilit na siya, tama.
-->
Kasi not equal yung nerecompile mo na ano?
-->
Apo.
-->
So dapat mawala siya.
-->
Patingin niya po ulit ng code mo.
-->
One moment.
-->
Sige, i-check ko lang.
-->
Tapos balikan kita.
-->
Department underscore name yung
-->
ano mo no? Patingin niya ulit ng table mo.
-->
I mean yung table mo na
-->
Department.
-->
So department name.
-->
Pwede ka magligay sir Joseph
-->
ng
-->
Pwede ka magligay
-->
ng else.
-->
Tapos
-->
mag erase tayo ng application error.
-->
Pero this time, for example
-->
2002
-->
HR
-->
about to delete. Parang ganun.
-->
Para makita natin na
-->
hindi nga siya pumasok dun sa condition na yan.
-->
Tapos instead
-->
of
-->
less than greater than
-->
pwede mong itray yung
-->
exclamation point equal
-->
or not equal.
-->
Sino po yung nagsalita?
-->
Okay.
-->
Okay, let's recompile.
-->
Okay.
-->
Nag-error siya. Ano yung binata niya?
-->
Nag-successful pa rin?
-->
Nag-successful pa rin sir.
-->
Nag sabihan
-->
di siya nagt-trigger. Wala
-->
binabalik eh.
-->
Okay.
-->
Nag-error
-->
May else na tayo no?
-->
Okay sir.
-->
Tapos hindi rin siya
-->
nag
-->
begin exception.
-->
Sino po ito?
-->
Adrian.
-->
Patingin po.
-->
Patingin nung
-->
trigger mo po.
-->
Prevent,
-->
delete, department name,
-->
erase, application error.
-->
Sir, chat ko po yung
-->
code ko dun sa chat
-->
makikita.
-->
Sige po.
-->
Chat ko dun po.
-->
Patingin nga nung table mo
-->
sir. Depth name ba ginamit mo
-->
or department name? Dun po sa department
-->
table.
-->
Sir Adrian.
-->
Department underscore name.
-->
Okay. Patingin nga po ako nung table sir.
-->
Department underscore name.
-->
Okay yung HR yung one.
-->
Okay. Sige.
-->
So i-recompile natin yung ano?
-->
Okay. Try to delete.
-->
Patingin nang departments.
-->
Ayun. Cannot.
-->
Naggray siya ng error pero
-->
dinilit niya pa diyan. Tama?
-->
Patingin nga po nung department.
-->
Okay. Patingin nga po alit nang
-->
trigger. Yung trigger statement.
-->
So creator replace
-->
trigger prevent
-->
delete before
-->
delete on department for
-->
each row.
-->
Try mo nga po. Dun sa end mo
-->
alisin mo na yung
-->
prevent delete
-->
follow all the
-->
names.
-->
Check ko lang sir ah.
-->
Alright.
-->
For each row.
-->
Patingin nga po alit sir
-->
sir Adrian.
-->
Adrian.
-->
So create or replace
-->
for each row.
-->
So we begin
-->
then we end
-->
call our
-->
department name.
-->
Naggray siya ng error pero dinilit niya
-->
pare no?
-->
Yung before mo. Try mo nga po
-->
ano? All caps yung before.
-->
Try lang natin. Tapos tama naman
-->
yung ano?
-->
prevent hr
-->
okay
-->
insert
-->
patingin nga natin yung
-->
departments.
-->
Check mo natin table kung may laman
-->
na hr.
-->
Okay.
-->
Wait wait
-->
so unexpected
-->
unexpected error
-->
occurred. So
-->
naggray siya ng error
-->
pero dinilit niya.
-->
Patingin nga po alit
-->
ng table.
-->
Okay.
-->
Sige.
-->
Ganito na lang.
-->
So let's have a break.
-->
So let's
-->
return 1030.
-->
Then I'll try to replicate
-->
the error. The issue.
-->
Okay?
-->
Yes po.
-->
Oh no
-->
1020
-->
tama ba?
-->
15 minutes. Yeah. Okay.
-->
Mga 1040 na.
-->
Okay lang.
-->
Yeah.
-->
Okay.
-->
so
-->
so
-->
so
-->
so
-->
so
-->
so
-->
so
-->
so
-->
so
-->
so
-->
so
-->
so
-->
so
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
I'm not sure why my video is closed. Let me fix my video.
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Ano ba?
-->
Let me know if we can move on to the next sample.
-->
Yes, po.
-->
Okay.
-->
So let's continue.
-->
So here we already have the...
-->
Okay, so let's continue.
-->
Here we have...we already created the table employees
-->
and we are able to insert sample data.
-->
One moment.
-->
So here we 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 debt ID.
-->
So I just return the sum of all the total salary.
-->
So the total salary for example in this case
-->
using the procedures is 85,000.
-->
Sorry, the function since we create this function.
-->
So let's dive in into Dynamic SQL.
-->
So PL SQL enable us
-->
enables the 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.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Yes.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
So I hope yung mga test cases it help you to understand more
-->
dun pa sa topic na on each topic.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Yes po.
-->
Okay. Yeah.
-->
So let's move on. So try the
-->
slide 367 na tayo.
-->
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 slide 367.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
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
-->
operations that involve variable object names or structure.
-->
So you know, we have a dynamic query.
-->
So the 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 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, 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.
-->
Change na lang kung depth ID on.
-->
Also you can change the one to whatever the ID you like.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Yes.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Yeah, that's fine. Let me check.
-->
Are you able to send the script?
-->
Sir Miguel.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Because of I think the data type.
-->
Can we check yung employees natin?
-->
Yeah, yung employees table. Tapos, pano natin din define?
-->
Yan, yung columns. Tapos,
-->
so, worker 50.
-->
Check natin, pano natin din clear doon?
-->
Yeah.
-->
Worker 2. Tapos,
-->
how do we?
-->
It should be match the data type properly.
-->
So, doon sa define column,
-->
asa na yung B cursor ID?
-->
Sa line 20, maglagay tayo ng
-->
define column, maglagay tayo ng comma 50
-->
doon sa line 20. Next to V name. V name, comma 50.
-->
Three mga po. Ayun, okay na no? Nag run po.
-->
So, ang issue natin is yung data type mismatch.
-->
So, kasi din define natin na 50 yung phrase name.
-->
So, yun po.
-->
Sir, sorry. Kung nangyari is yung dulo
-->
nilagyan natin sinang size ng bar cartop.
-->
Ano pag yung B name is number?
-->
Wala na. Hindi na. Kasi wala naman tayong size doon.
-->
So, okay na yung number.
-->
Yeah, kasi yung string nagbabago may 50, may 100, 200, ganoon.
-->
Which is goods din kasi we're sure na yun yung
-->
part ng validation din.
-->
Yun. So, siguro let's have a lunch. Tapos balik po tayo ng
-->
1 p.m. Okay po?
-->
So, you want more 10 minutes to test the code?
-->
Okay.
-->
Okay. So, let's back at 1 p.m. Okay?
-->
Thank you sir Miguel.
-->
Kasi ayosin na.
-->
Hindi ba? Ano ba nalawin bukod kailangan po ng Graphics Card?
-->
Yakan naman. Natalag natalag ang anay-anay.
-->
Natalag mo nga ako ng ano.
-->
Pwede ako ng Graphics Card.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.
-->
Okay.