12:04
2024-12-10 13:02:57
6:53
2024-12-10 13:18:23
3:49:58
2024-12-10 13:29:28
9:46
2024-12-11 07:45:15
5:40:21
2024-12-11 09:08:49
5:22
2024-12-11 17:54:14
1:42
2024-12-11 18:04:44
2:05:58
2024-12-12 09:00:08
1:01:06
2024-12-12 12:26:38
3:59
2024-12-12 15:09:36
51:24
2024-12-12 15:24:00
8:06
2024-12-12 16:34:52
2:35
2024-12-12 17:17:14
3:05
2024-12-12 17:26:54
Visit the ORACLE PL/SQL Fundamentals course recordings page
WEBVTT --> yung one six seven or yung one six eight? --> One six eight po. --> Ah, isig. Thank you po. --> Connected na po lahat? --> Ah, yes po. Working on... --> Okay. --> Password? Ash blue? --> Mam teen? There you go. --> And can you click double... --> Can you double click the local, mam teen? --> Okay, yeah. --> And try the code. --> You can press control enter to run the code. --> Okay, see... --> Sir Ray already finished it. --> Sir Jerry, you're good? --> Is it running or you bump an issue? --> Sorry, ma. Working on kami dun sa clickboard, sir. --> Hindi mag-paste. --> Ay, mag-paste? --> Yung... Are you all using Windows or Mac? --> Windows po. --> Oh. --> Try po namin. --> Okay. --> Ayo pa rin mag-paste? --> Yun, may nakapag-paste na. --> Si Sir Joseph nakapag-paste na. --> Sir Joseph, try mo yung set server output on. --> Si Sir Miguel, hindi po nakapag-paste. --> Okay na si Sir Joseph. --> Sir Jerry okay na. --> Sir Ray okay na. --> Si mam teen. --> Okay ka na po, mam teen. --> Ma'am Christine, okay ka na po. --> Na try mo po. --> Okay. --> Ayun, nagdaluwa lang. --> Okay na Sir Miguel. --> Yan. --> Perfect. --> Okay na po. --> Awesome, awesome. --> Then yung for loop naman. --> So yung for loop natin. --> For counter. --> So the for loop in PLSQL 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 for fixed iteration. --> So let's explore the syntax and the example. --> So for counter in start value then dot dot then end value loop. --> So 169. --> Yeah let's try the 169 really quick. --> See if you're able to output the counter. --> Just copy paste it in our interactive tool sheet. --> The slides 169. --> You can click control enter to run it easily. --> Okay see Sir Miguel able to run the code. --> Awesome, okay. --> Yeah that's pretty easy. --> Then in PLSQL the while statement is used to repeat a block of code as long as the specified condition is true. --> So the while loops check the condition before each iteration. --> 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 is true. --> 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 than or equal to 5. --> Then the execution if it is true it prints a counter then loop exit if the condition will become false. --> The while loop will not execute if the condition is initially false. --> Again the while loop will not execute if the condition is initially false. --> Use the while loop when the number of iterations is not known in advance and depends on the condition that may change during the loop execution. --> So again 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 the specified range. --> Starting from start value and end value. --> 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 currently of the counter in each iteration. --> So the variable counter automatically increment after each loop. --> 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 5 and decrements down to 1. --> So use reverse. --> So in PLSQL the go to statements allow you to transfer control unconditionally 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 generally discouraged in structural 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 identified 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 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. --> So loop execution, the loop increments counter and checks if it is greater than 5. --> Then the go to statement, when counter exceed 5, 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, exiting the loop. --> So our key points is use sparingly, meaning okay yung magpaka-addict sa go to. --> Go to should be used sparingly as it can lead to code that is difficult to follow. --> Kasi nga, it can jump to that label and the person who will follow the code needs to jump to that code. --> Especially kapag nested na or andami ng code. --> So labels are local. Labels can only be used with the same PLS code block where they're declared. --> Readability, relying heavily on go to can reduce 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 run it. --> I think all good. --> Mam tin, you can run that. --> Again, you can press control enter to run any time the code. --> I see sir Jerry able to run the code. --> Sir Miguel has some issue. --> 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. --> Error handling is done through exceptions which allow you to manage error gracefully. --> So let's discuss more in depth in cursor. --> So again, we studied the implicit and explicit cursor. --> So again, implicit cursor is automatically created in PLSQL whenever a select statement --> returns a single row or during insert, update, or delete operation. --> The key features of that is it's managed entirely by PLSQL so no explicit declaration is required. --> It simplifies operation where only one row is expected. --> Example of that is automatically handles row 33 in begin, update employee set salary equals salary plus 1000 where employee ID 101. --> Well, the explicit cursor is 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 there are four steps 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 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 cursor. --> Then close the cursor name. --> So let's try the slide 178. --> So we have to run first, we have to add last name. --> I think because we don't have last name. --> So I put in 178, 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 slide 178, please run the two 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 employer cursor. --> It's declared to then after that we select the first name and last name from the employees table. --> Then we open the cursor using the open statement. --> Execute the SQL query and prepare the result set. --> Then we fetch the data. --> So the fetch statement 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 close statement, release the cursor and press 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, Mantine. --> 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. --> First is percent pound. --> It will return true if the last fetch returns zero. --> Otherwise false. --> Then percent not found returns true if the last fetch did not return a row. --> Otherwise false. --> Then percent row count returns the number of rows. --> 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 explicit cursor offer more control over query execution and row handling. --> Then closing a cursor for efficiency when done is crucial to pre-up 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, each cursor attribute returns a specific type of information helping to manage cursor effectively. --> Here are the four main cursor attributes na pinagalala nito. --> So found returns if the last fetch or your data manipulation language returned. --> Operation affected one or more rows otherwise it returned 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. --> Two key attribute is percent row count and percent open. --> So let's understand their functionality and usage. --> So percent row count returns the number of row fetch or affected by the cursor so far. --> It's useful for tracking row processing in loops after manipulation operation. --> 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. --> Ensuring smooth and efficient program execution. --> So 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. --> Hello sir. --> Yes po. --> May tanong lang po. --> Yes po. --> Paano siya naging explicit na version versus noon saan saan? --> Ano yung difference na po? --> Ayan. --> So din declare natin yung employer cursor. --> So yung employer cursor on the line two. --> Tayo yung nag-define nun, di ba? --> Yes po. --> So hindi siya implicit. --> Meaning hindi siya yung built-in cursor ni PLSQL. --> So ang difference nun, as I explained a while ago, mas dynamic siya. --> And it will use in the multiple... --> We can declare multiple cursor. --> Tapos pwede natin magamit yung cursor na yun. --> O yung queer na yun. --> Kumagapang it's a variable. --> Yes, pwede mo siyang i-reuse. --> Pwede mo siyang i-open. --> Tapos sa loob nun pwede ka rin mag-open ng another cursor to use that data. --> So again, for example yung employer cursor natin, --> it holds the first name and last name of employees. --> Kasi yun yung query natin. --> So we select the first name, last name of the employees. --> So si employer cursor, siya yung nag-hold lahat ng first name and last name. --> So what we did is we looped it. --> So we fetched the employer cursor into first name and last name. --> So we get the first name and last name. --> So since wala tayong nilagay, for example, address or maybe avatar or picture, --> so hindi rin natin siya magagamit dito. --> So it's very useful kapag multiple yung queries mo. --> For example, you need a piece of this on the stable. --> You need another piece on the stable. --> Then you can leverage that using the explicit cursor. --> So yung explicit cursor, it's your user-predefined, --> depending nga dun sa query mo or sa data na gusto mong ilagay sa kanil. --> Iba may employee cursor ko tayo? --> So yung unang cursor ko natin, niliraturn niyo yung first name and last name. --> May niliraturn niyo yung first name and last name? --> Or may salari lamad niya? --> Yes, pwede. --> Basta related dun sa isang variable na yan? --> Pwede mo siyang magamit? --> Yes, yes, yes. --> So again, yung implicit cursor, yung deklarisyon niya 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 query. --> While yung explicit cursor, it handled multiple rows or multi-row queries. --> Yung lifecycle naman niya, yung implicit, PLSQL managed it automatically, --> while sa explicit cursor, tayo, yung programmer, must manage it explicitly. --> In terms of credibility, as well as explicit? --> Yes, yes. --> Pero depending sa usage mo, kung single row lang naman yung kailangan mo, --> then just use the implicit cursor. --> Yung attribute dun sa implicit cursor, yung SQL percent found, SQL percent not found, SQL percent row count. --> While dun sa explicit, mayroon tayong percent found, percent not found, percent row count, percent is open. --> Tapos siyempre flexibility niya, mas flexible siyempre sa explicit. --> While si implicit, 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 didn't store data values temporarily during program execution. --> So we type variable name, yung piligayang natin gaapan data type, it is not... --> And if we... --> Yung not null, it's an optional to ensure that the 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, I think we tackled it yesterday. --> Then the percent type attribute, again, remember we have a percent row type na it holds the entire row of the table. --> But for example, if we want to hold lang yung salary ng 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 variable or the column of the, for example, phrase name, we say employees dot first rscore name, percent type. --> Okay, so yung not null, optional siya, tapos kung gusto mo siyang lagyan ng yan here, we begin putting a value on the employee salary. --> So let's try step by step. --> I think yun, natutunan natin how to open the cursor tapos i-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 loop. --> So ang step natin is open the cursor name, fetch the data, then don't forget the close. --> Kasi pag hindi natin siya clean hose, it can lead to error or kumagang nakopen 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 we fetch the employee cursor into first name and last name. --> Exit natin kapag, laragin natin ng percent not pound. --> So employee cursor percent not pound. --> If you want to check yung percent row count, pwede rin kung baka ilan na siya. --> Kung ganyan rin gisa natin mag-stop sa 10 lang. --> 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. --> How about try to use percent row count to exit it? --> For example, I will require you to display only the 5 employees. --> How do you gonna use the percent row count? --> I want to stop or I want to show only the 5 employees. --> Can you try to apply percent row count? --> Where you gonna put the row count? --> Yeah, there you go. --> In the exit when? --> Dapat 5 lang yung makita natin pag niren yung screen. --> Again, you can click control 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 --> and last name yung sa table mo. --> Can you check? --> Click data. --> Data tab yan. --> Walang first name yung nilagay natin. --> Yung first underscore name. --> Kaya wala siyang nandetect. --> You can put anything now then commit the changes. --> Yung tinitin na mukhang is emp name. --> Dun sa first underscore name. --> Yung pang ano na column. --> 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 --> with 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 associated with the cursor. --> So once it has 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 lifecycle with close. --> Let's try the... --> I think you have this already first name. --> Yeah, it's the same code we have. --> But yeah, 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 PLSQL automatically close cursor --> 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 occur, close any open cursor --> within the exception handling block --> to prevent resource leak. --> Remember we studied the error --> trapping errors yesterday. --> So you have to make sure that the cursor is open. --> Sorry, close. --> So using close is essential in PLSQL programming. --> So ensuring efficient use of database resource --> and keeping the code base clean and manageable. --> So we studied the for loop. --> We tried it as well. --> This one is for, one moment. --> So using reverse index example, --> for loop iterase over each row --> in the result set of a cursor. --> So pano naman nating gagamitin yung for --> dun sa cursor na ginawa natin. --> So for record variable in cursor name, --> tapos loop, then end loop. --> So a record variable, --> it's a record variable that holds the --> current from the cursor, then the cursor name. --> The name of the cursor being iterated. --> So alternatively, you can directly use SQL query --> within the for loop instead of the name cursor. --> So example of that is we declared a cursor, --> employee cursor, to select the first and 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, --> fetches each row into employee record, --> and closes the cursor after the loop completes. --> Then it can output the loop print --> each employee's first name and last name. --> You can also use direct query instead of cursor. --> So this is equivalent to using cursor --> but without needing to declare it separately. --> So again, our key point here is 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 automatically handles cursor operation --> making it ideal for row by row processing --> of query result. --> Then the reverse keyword allows numeric loops --> to run in descending order. --> Now let's try to use the update. --> Can you try the slide 198 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 kung wala kayong ID na 20 --> dun 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 kung may 20 kayo --> or para makapaguran yung code. --> Don't worry, --> all this code will be sent to you as well --> for your future reference. --> So no need to save, --> but you can save as well if you want. --> Check that. --> So right now we don't have 20 in the departments. --> So you can add another table --> or edit other one. --> So in the department, --> Sir Miguel, you can click the data --> para makita mo po yung laman --> on the departments tab. --> At 10 a.m. we're gonna have a short break. --> Sir Miguel, may department ID ka na na 20 --> sa table mo? --> Yes, Sir. --> Okay. --> Hindi, dun sa departments mo --> may table ka na ba, may ID ka na ba na 20? --> Wala pa ko, Sir. --> Click mo yung data. --> Sir Miguel, click mo yung data --> next to column yan. --> Okay, mayroon ka na pala 20, --> yung HR. --> You can try to run the code. --> Magkakiawalay ba kayo ng table --> or magkatabi kayo --> or nasa isang conference kayo? --> Or nakalaptop kayo lahat? --> Okay, then you're sitting --> 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. --> Do you have any more questions --> so far or we can continue? --> Okay, I think it's a no. --> Okay. --> Sorry, Sir. --> Go ahead. --> Yung sa, ah, --> finish ko na na, --> sa Google Sheet ko. --> Okay. Ah, you can, one moment. --> Let me check in my Google Sheet. --> Nakapag-piss ka. --> Anong line? --> Do, --> Yes. --> Ay, ay, dun sa audience. Okay. --> Yun yung tanong. --> What will happen --> if the cursor --> is not properly closed --> during procedural 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, also --> exceeding open cursor limit --> kasi, um, --> pwede kayong maka-receive nun. --> For example, um, --> most database have a limit --> on the number of open cursor as a session. --> A session can hold. --> So, um, --> 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 an error like --> over, I think, the --> zero one thousand. --> It says maximum open --> cursor exceeded. --> Also, the performance --> degradation, I think, --> because keeping unused cursor open --> can degrade performance --> as resources are necessarily --> unnecessarily consumed. --> Kasi nga, may memory siya. --> So, magi-slow yung program along the way. --> So, it will --> slow down the database and, you know, --> it reduce efficiency for other --> operations or sessions. --> Um, also the lock retention, --> some cursor --> might hold --> locks on resources. --> So, for example, um, yung cursor --> na yun, nilock niya yung table. --> So, pag hindi mo cleanose, --> nakalock pa rin table na yun. --> So, failing to close the cursor --> could result in lock contention. --> So, preventing other session --> from accessing those resources --> and potentially leading to --> deadlocks. --> So, that's the worst scenario we can --> experience kung hindi natin i-close. --> So, hanapin pa natin kung --> anong cursor yung naka-open --> para lang ma-unlock --> yung table. --> Also, connection stability issues. --> So, over time, unclosed cursor --> can accumulate --> destabilizing the database, --> connection, causing errors, or even --> crashing 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 --> unprocessed, or operation --> might fail to execute --> as intended. --> So, yeah. --> So, best practice natin, always --> close the cursor. --> Especially, --> yung mga --> explicitly closed, yung mga --> explicitly closed 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. So, when working --> PLSQL, --> 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. Mamiya, I'll provide the code --> how to see the open cursor. --> Yeah. So, next. --> Did I answer your question po? Sir Miguel? --> Yes, po sir. --> Thank you. Thank you so much. --> Then, another 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 parin --> pero explicit. --> Slide 198 --> implicit cursor. --> So, we declare --> first name. --> Okay, can you try the --> EMP ID tayo? --> Can you try slide --> 198 implicit cursor? --> I make it bold? --> So, this is the example of implicit --> cursor. --> So, the --> implicit cursor execution is the --> select then into --> statement. Automatically --> open the cursor. --> So, when we have a select, --> it's the implicit cursor. --> 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 --> example using DML. --> Statement. --> Again, slide --> 198 pa rin. --> Another example. --> Hello sir. Yes po. --> Ayun na, gets na po namin. --> Bale pala, si --> implicit, pag single row. --> So, select into po yung ginagam. --> Yes, yes. --> Na hard na cursor din. --> So, si cursor --> pang collection talaga din. --> Yes, yes. Correct. --> Okay na po. --> Tapos, hindi mo na kalang i-close, --> hindi mo na kalang i-fetch din. --> Kasi, yun na yun. --> Yes. --> Okay. --> You're welcome. --> So, yung update statement na --> tray na po ba natin? --> Nakakapag update na tayo? --> Okay. --> Nung sa department ko, no? --> Yes, yes, yes. --> Okay, let's move on. --> So, eto naman yung example natin --> for conditional update. --> So, we just --> we declare the cursor --> with conditional check inside --> the loop, check if the salary --> is less than --> 4,000, then we update --> using the update statement --> if the condition is true, --> we set the salary to --> 4,000 for that employee. --> Then, output, --> so the updated salary for each --> qualifying employees is printed. --> So, we have direct update --> used for simple updates --> with a pre-wear clause 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, eto yung example --> ng update. We will have a coding --> in slide 205. --> One moment. --> So, yeah. So, for update --> clause, it locks --> all row --> that cursor select. So, eto --> for update, if you're --> looking at my mouse cursor, --> so it locks the database --> para hindi --> hindi mo na siyang bago. --> So, this is useful in multi-user environment --> where multiple session may --> attempt to modify the same data. --> The --> effect for update --> it allows return by the select --> query. So, other sessions --> will be unable --> to update or delete this row --> until the transaction is --> submitted or rolled back. --> 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 locked row. --> Then, update the statement, modifies --> the salary for each employee. --> Then, we're current --> off. So, --> after the update, the current row --> in the cursor will simplify updates. --> Then, don't forget the commit. --> So, after the updates, commit is called --> to release the lock. --> So, for update of --> clause is similar --> to for update. --> 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 for 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 or rolled back. --> So, again, we declare the cursor --> in this example. --> The employee cursor. --> Then, 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. --> So, if there's --> 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. While 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 roll back. --> Locks set for update or for --> update of are held until you commit --> or roll back the transaction. --> Where current op, so --> when updating or deleting rows in a loop, --> where current op allows you to direct --> update the current row --> without a specifying condition. --> Then, concurrency --> control. Using the for update or for update --> op ensure --> consistency in multi-user environments --> by preventing other --> user from modifying data --> that you are working with. --> So, --> this is --> where current op clause --> is used in conjunction with the cursor --> to update or delete the --> current row that the cursor is pointing --> to. So, this clause especially --> 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 --> 10.08. --> Let's have a break on 10.10. --> Then, let's --> go back maybe --> 10.40. --> 10.40. --> But, --> yeah, then when you come back, --> when you have a chance, --> it is slide 2.0.5. --> Okay. --> So, let's have a break. Let's assume --> 10.40. Be sure to --> run this code, okay? --> Slide 2.0.5. Thank you, guys. --> Have a quick break. --> A� --> Ano ba ginawa --> nya? Pinak-certify --> through copy na mali yun. --> Bakit naman ng ming machine pinabibigin ng --> certified through copy? --> Mabibintan na dito nung wala tayo alam. --> Tignan mo yung picture, --> certified through copy. Yung pinawan --> mo sa akin! --> Indi sabihin mo, sinabihan moZa sa --> Ati Lili. --> Hindi nakakamu nung iba-be. Tingnan mo? --> Nag-certify, tinanong ako diba doon, mami mag-certify through copy po. --> Ano ko hindi nasakan nalang mag-di-venta ko na? --> Sige, lago nga, ang munisip nyo naman nag-digay kita hindi may ari. --> Pin-certify through copy? --> Apeno hindi ikaw muna, lakihan mo, hindi po namin, hindi na po namin yan i-venta. --> Ialis na po sa forsake. --> Sige lang, ano nag- --> Pin-certify through copy na, kasi i-venta nila. --> Ano po namin? --> Ano po namin? --> Ano po namin? --> Ano po namin? --> Hi guys, we're back na po. Hello po. --> Okay, sir. --> Okay. Okay, copy, copy. --> Na-try nyo na po, yung, yung sign nyo yung code, yung slide 205. --> Okay. --> Hello. --> Yes, po. Sir, may question lang po. --> Dun sa update, yung current of, may ano po kung anong yung function niya? --> Karen o employee, of course sir. Meaning ito yung current sa loop? Yung po ba yun? --> Anong line yan? Yung for employee record? --> Apo, for employee record in employee car, sir po. --> Yung may update. --> Yes, yes. --> Ah, sabiig sabiig po nasa na yung loop, ah. --> Yes, yes. --> Pinukuhan yung card ng loop pa. --> Thank you, sir. --> Okay. --> May ginawa pa akong ano dyan, slide 205 with exception handling. --> You might want to try as well. --> Yung current off naman, it usually use update saka delete. --> So again, yun nga, you're right. --> Ano, yun yung current row dun sa for loop. --> Basta po inside yun yung for loop, po. --> Yes. --> Kasi kung, kung hindi tayo gagamit ng current off, ipipace ko yung sample dito. --> Ibo-bold ko siya. --> So ganyan nyo magiging tsura nya. --> Magbukha kita yung naman. --> So we're EMPID, employer record dot EMPID. --> At least yung, when current off, sure tayo na yun yung current na nasa loop. --> Yes. --> Dun sa for loop, yun yung current niya na record. --> Kasi yung for loop, diba? --> Row by row yan eh, each record. --> Pero what if, sir, pag walang karang ganun din po? --> Di lahat. --> Pag walang workload, siyempre lahat nung salary dun sa employees table. --> Diba, ang best practices kapag mag-update ka, siyempre dapat may wear. --> Kasi otherwise, baka accidentally, ma-update mo lahat nung salary nung employees. --> Or yung table. --> So nandiyan na pan lahat. --> Okay. --> Great. --> So yan. --> It's really good na natatry natin yung code. --> Also, I put exception handling. --> Kung gusto natin maglagay na exception handling, which is we must or we need to. --> Para at least kung may error, maro rollback natin. --> Maro rollback natin kasi diba yung for update, --> nilalock natin yung rows eh, diba? --> Dito yung sa for update, nilalock natin yung rows. --> So pag hindi natin na rollback, naka-lock pa rin yung rows. --> So hindi magagamit ng iba. --> Yung table na yan. --> Or yung data na ina-update. --> So let's move on. --> So let's try the work current of with delete. --> So ngayon naman mag-delete tayo. --> Gamit yung work current of. --> So let's try that. --> It's slide 206. --> So make it sure may data tayo na yung salary niya is less than 3,000. --> Tapos tingnan natin yung data na yan kung madi-delete. --> So make it sure, dun sa employees table nyo, --> meron kayong salary doon na papasak dun sa requirements na yan. --> So if not, maglagay tayo or mag-hardcode 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 dun sa condition. --> Tapos pag niran nyo yung code, i-refresh nyo, --> balikan nyo yung employees table. --> Dapat mawala na yung employees na yan na may salary na less than 3,000. --> So before running the code, --> make it sure na-check nyo yung data ng table to do a comparison. --> Don't forget to commit. --> I see some people are editing some files. --> Don't forget to commit. --> Changes, yeah. --> Okay, sir. Miguel line on ID 100618. --> Yeah, try that. --> Oh, I think we have error in employee ID. --> It should be EMP ID. --> Okay. --> Okay. --> Everything good so far? --> Did you see that your data was deleted? --> Yes po, okay na po. --> Okay. --> Kanina ayaw mag-delete? --> All good naman? --> Okay, so let's continue. --> So yeah, the commit statement is a key tool in PLSQL 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 per update. It's when a cursor is read for the per update clause, rows patch the cursor or lock 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 where committing after processing all rows could lead to lock contention or roll back overhead. --> Then number three is the release lock. So 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-user environments and ensure data consistency. --> 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 environments. --> So yeah, let's try the commit more in this. 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, 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 if updated two rows, it will say updated salary of employee ID 101, 102, or whatever the employee ID. --> So ito naman yung example ng row by row commit within the loop. --> As you can see, yung commit nilagay natin dito sa laob ng for loop. --> So after ditong script, ikok commit nyo na agad. --> So depending sa business logic nyo, kung gusto nyo i-process muna yung buong for bago ikomit, or every row ikok commit. --> So if you want to row by row commit, ipot 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 210. --> Instead of 30, I think we have more department ID 10 in your record. --> So make it sure you have more rows. --> Let me know if you need more data in the employees. --> I can provide insert or you can insert more data. --> So basically ito yung gusto natin i-highlight, yung if-row-count mod. --> Nagkikita nyo dito, nag-commit siya. --> So ibig sabihin, kinok commit nya kapag 10 rows na, gagawin nyo na yung process nito. --> Please try the slide 210. Thank you. --> Hindi 10th row. --> Pag naka-sampong row na siya. --> So i-update nyo yung from 1 to 10. --> Tapos pap-process nyo uli yung 11, 12, 13. --> So konyari, hanggang 13 lang yung record mo, hindi nyo na mapap-process yung 11, 12, 13. --> Kasi hindi na siya pumasok doon sa pang-sampong row. --> Kasi di ba yung commit, yun yung nagpap-process ng transaction. --> But since in the final, we have the commit. --> So kung ano man yung naiiwan ni-commit dito, ikok-commit nyo pa din. --> Kumaka ang gustong ipakita dito is you can have a conditional commit after a set number of rows or after a certain condition na gusto nyo po. --> May error ka sir Miguel or hindi naman? --> Petch out of sequence daw ko sir. --> May see. --> Checking po. --> Okay. --> Petch out of sequence, let me check that. --> Sir, nag-update siya pero may error siyang binitaw. --> Sinipiling ka salita? --> Si Miguel po. --> Miguel, yes, yes. --> Anong error niya? Yung fresh out of sequence? --> Apo. Pero nag-update po yung table. --> Kasi nag-commit. --> Ay, o po. --> Kasi nag-commit tayo sa ending eh. --> Ang iba yung mga talagang siguro ko. --> Itas mo ka sir, at line 1 po. Pati nga anong line 1 mo sir. --> So we declare, curse 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 po. --> Okay, one moment. Hold on. --> So at line 10. --> Okay. --> Yeah, that's fine. We can fix that. --> It says here yung fetch out of sequence. --> It's occur when a cursor fetch is attempted after the cursor has been closed. --> Or a work current of operation is performed on a cursor that is no longer valid. --> So in our case, for employee. --> Yung side effect niya po. --> Yes, one moment. --> Let me try my hand. --> Ayun niya sir. So parang anong nangyari. --> Kasi yung unang 10, nagcommit po di ba po? --> Yes. --> Parang unang 10, hindi niya nariksa yung parang konta na 10. --> Kaya siya nag throw ng error na ganito. --> Tapos wala tayong exception handler. --> Ayun po boys. --> So kung may exception handler tayo, we can catch that. --> Obo. --> Do you want to put exception handler into that code? --> Bade naman sir. --> Sige. --> Lagyan ko lang. --> So exception. --> Slide 210 with exception. --> Ok, I pasted na sa Google Sheet. --> So we declare cursor. --> Then nagbigay tayong update. --> So if commit mod 10. --> So we add exception. --> So when others error updating employee ID. --> So we try to roll back. --> Finally we commit. --> Pero pag nag-error pa rin din sa commit exception when others. --> Can you try with exception? --> Slide 210 with exception handling. --> Ok. --> Para malaman natin kung ang exception ba is dun sa inner for loop. --> Or sa labas na, sa outer. --> So using the exception handler. --> We can detect saan niyo nag-error. --> So dapat maresim natin. --> Unexpected error occurred. --> 01002 fetch out of sequence. --> Yeah. --> Good, good. --> How about the others po? --> Sir Joseph, are you good? --> You're able to run the code with exception handling? --> Everything good? --> Ok. --> Let's move forward. --> So ito naman. --> It's a simple validation with statement. --> So it's on slide 210. --> We can quickly place it. --> Let's test it in 5 minutes. --> So pag hindi niya nakita or yung --> yung value is less than 3000 --> which is yung 25. --> Mag-i-insert siya. --> So dapat mag-i-insert niya yung --> name na ilalagay niya doon. --> So it's possible na pag niran niyo ali --> magkakano kayo ng --> unique constraint validation --> or violation. --> So you have to change the employee ID. --> So slide 211. --> Tapos i-check niyo yung employee's table niyo --> kung nag-insert nga siya ng data. --> Try to change the name. --> The first name, last name, the name you like --> para mas makita niyo siya. --> Kasi I think we already have it diyan do. --> So try to change the name. --> So it is a simple validation --> with if statement. --> So again, we define the variables. --> 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 3,000, --> an error message is displayed --> and the insertion is skipped. --> So if the salary is valid, --> the employee data is inserted --> into the employee's table. --> So yung insert into employees, --> mag-i-effect siya. --> But I think EMP ID yung sa atin. --> 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 --> ensures that operations are only performed --> when data meets specific criteria, --> safeguarding the integrity of your database. --> Let's move on. --> So itan naman yung --> ma-apply natin yung else if. --> So this is slide 212. --> Or 212. --> Can you quickly try it? --> Para ma-apply natin yung if, else if, --> else if, else, then end if. --> So don't forget the end if --> kapag magkakaroon ng if. --> I think we don't have, --> do we have a job title? --> Pero hindi naman kailangan kasi --> we're 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 --> 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 --> 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 versus we declared the employee cursor is defined with depth --> ID or department ID so you see that is used in the work loss employee --> cursor depth ID number so you depth ID then the data type that was after non --> select employee ID so in our case EMP ID first name last name salary from --> department ID depth ID yeah so nakapag create a tie now 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 ma kapag open tayo --> tapos from that we can loop it na so fetch employee 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 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 --> department ID na 20 na may salary na minimum of 4000 so we select that --> kasi yung where natin department ID cost depth ID and salary is greater --> greater nor 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 --> 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 number may default values open --> statement so 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 --> 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 na lang --> kayo ng job title dun sa department table pause ipakita nyo yung how we --> can fetch employee and matching the criteria if any question just ask long --> 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 --> thank you --> Just message mo lang pag may question na, andito lang pa ako. --> Sorry. --> Hello po. Hello. --> Misun di ba may error tayo tapos sinasabi line ganito, line 2, line 3. --> Para makita natin or ma-enable natin sa worksheet, yung line number. --> Can you go to tools? On the menu, tools. --> Tapos preferences. --> Tapos hanapin 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, madali tayo makapag-debug kung nasan yung line number na yan. --> Okay? --> Tools, preferences, line gather. Okay. --> I hope that helps. --> So siguro lunch na tayo. --> Balik tayo ng 1 p.m. --> So I'll give you 10 minutes pa 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, ang busy talaga nila mag-coding, oh. --> Lunch mo na lunch. --> Thank you, ma'am. --> Okay, okay. --> Thank you, ma'am. --> Thank you, ma'am. --> Thank you, ma'am. --> Thank you, ma'am. --> Thank you, ma'am. --> Thank you, ma'am. --> Thank you, ma'am. --> Thank you, ma'am. --> Thank you, ma'am. --> Thank you, ma'am. --> Thank you, ma'am. --> Thank you, ma'am. --> Thank you, ma'am. --> Thank you, ma'am. --> Thank you, ma'am. --> okay please wrap up your workshop or your task I'll give you 10 more minutes --> yes po, yes po --> sa gawa po kayo ng google docs tapos sabi niya po sakin in pdf --> once you pay sa google docs you can download dito sa pdf tapos you can attach here or you can email it back to mamcha --> sir pwede konsolidate na lang po namin then pasun na lang namin via email --> okay no problem --> last 2 minutes --> okay so yeah i see na yung iba may ginawa pa but that's okay did you learn something while doing it --> so i'll send the answer and notes that you can use as well kapag i-apply 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 mamcha --> so please consolidate but i'll send it na and we will discuss it as well i'll send it in here --> so ito yung 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 the task --> okay then we can discuss it by tomorrow morning ko may question or what are the difference but at least nakita nyo how we can use different --> function 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 na kasi 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 escape 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 plsql pano tayong kikirit ng procedure ano ba yung function and packages have you ever created a procedure sa mysql po or function anyone --> na kapag kikirit na po kayo ng procedure functions na nung views okay that's fine yeah so i at least i know how we can tackle it or we can escape ko na ilat kayo na kapag --> 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 two two one so let's try to create a sequence it's in two two one 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 mo na natin sequence pag nak create ng sequence saka tayo pwedeng gumamit ng insert into --> dapat may message kayo ma-receive na parang sequence created --> sequence emp underscore s-e-q-r-a-t-ed tapos tayo natin mag insert into --> so pag naka-receive tayo ng unique constraint ibig sabi niyo primary kisi natin is yung emp id which is yung next ball kasi yung sequence natin one two three sunun-sunod siya --> if you look at the employees table right now yeah so may ID na tayo na one so if you can edit it for now just for the testing purposes make it like 200 201 --> sir Miguel i-back mo din i-back mo yung code mo doon yan tapos baguhin natin yung table mo na employees pwede tayo sa employees table --> tapos baguhin natin yung 126 mo baguhin natin yung 201 202 yung mula sa ano mo em 124 para makita natin na mag insert mamaya --> 202-203-204 yung six yan ok tapos save commit ok then let's try to insert one row inserted tingnan natin kung nag insert punta ka po sa employees 15 refresh 16 na siya --> so nag to siya so mag-try ka po mag-insert so dapat ang susunod na ID 3 na yun ok natry nyo po nakita nyo po sir joseph nag-run po sa inyo yun kasi po yung ID na pinapasok natin ngayon is either one or two na meron ng existing kaya nag-account po kayo ng error --> if you can edit yung employees table po sir joseph go to your employees table tapos edit natin yan ganyan namin natin 201 202 ganun yung limang records na yun double click you can edit that --> gawin mo po ang 201 sir joseph ok then the next one ok perfect then commit the save and commit --> nag save po ba? ayun so refresh natin sir joseph refresh yes parang hindi nagdagdag no --> commit ok then let's try the code insert tayo ulit tingnan natin what happened yun so pang 4 na yung sequence natin can you try again to insert sir joseph tingnan natin kung magiging 5 na yung ID --> 4 po yung lumabasok na --> ah 4 na so try ulit mag-insert i-run natin ulit yung command --> ok so 5 so ibig sabihin yung sequence gumagana ok so you can create more sequence if you need and you can increment by 2 by 3 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 222 let's create a view --> na-try nyo na po mag create ng view i mean before --> ah ok so yeah you can copy paste it --> yes po --> question po --> pano pag yung last value yung --> tadanda ka sa --> ano pong question po --> pano pag po yung --> ano yung last value po --> yung increment --> hindi ka nagagamit ng sequence --> gagamit ka na ng logic for example yung last --> ID nung --> nung table mo --> kumaga yung sequence --> function na built-in --> for --> kumaga --> yung sequence lang siya --> or pwede yung baguin yung sequence --> pwede rin --> yes --> oh yes so depende doon sa logic na gagawin mo --> yes --> ayun sa 10 lang po --> para mas --> para di na mag edit po nilang yung max value po --> then from there increment na lang --> pwede rin --> yes --> ok --> na-try nyo na po mag create ng value --> ah sorry ng view --> you can also --> ok --> para matawag natin siya --> diba --> to select it --> we can simply --> so --> creating view --> it's like a virtual table --> that is based on select query --> so it allows you to simplify --> complex queries or provide --> specific data subset --> to the user --> so now let's try to create a procedure --> so mag create naman --> tayo ngayon ng procedure --> by the way para magamit yung --> yung view --> you can simply select --> as there is from --> for example high underscore salary underscore --> employees --> tapos whatever --> yung laman ng view --> may kitang mas siya --> ngayon naman try natin mag --> hindi ko nilagay dyan --> but can you type --> this procedure --> update salary --> so yung procedure --> 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 ang application nya --> we can type --> begin update --> the salary --> tapos kung sila natin gusto kong i-update --> tapos kung ilan yung salary nyo --> i put that in the google sheet --> in the slides 223 application --> once na may procedure --> na kayo --> tapos check nyo yung tables --> check nyo kung nag-update yung --> once your --> kung nag-update yung salary --> so ibin sabi nag-run yung procedure --> ng TAMA --> so --> so --> so --> so --> so --> so --> the employee ID nya ang gamit natin sa --> table employees is EMP underscore --> ID --> make sure mapalita --> make sure mapalita --> So we're EMP Anuskar ID equals P Employee ID. --> Galing, no? Sir Joseph. Napalitan po yung five na may ano? Yan, five thousand. Si Adrian, no? --> Ya, ya. --> 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 Anuskar salary to show the details of it. --> Yan, yan sir Joseph. May kitang mo din yan may procedure na ano? --> Sir Joseph. --> May kitang mo din yan may procedure na ano? --> Yan, yan sir Joseph. May kitang mo din yan may procedure na ano? --> Begin updates Anuskar salary. --> May procedure ka na sir Ray? --> Ah, okay. So it's already existing. --> Update salary where EMP Anuskar. --> System.update Anuskar salary. --> Update the set salary cause P new salary where EMP equals P Employee ID. --> May 101 ka ba na ID? --> Can you check yung table mo po na employees? --> Ah, okay. --> It's the number four. Okay. --> Try mga po ulit, Eran. --> Yung 15 to 18 na line, Eran. --> Baka tinignore niya kayo ano? --> Refresh? Can you refresh that? --> Okay. --> Can I see your query sir? --> On Local 12? --> Update salary. --> Dapat ko mana siya yung? --> P Employee ID. --> Create procedure. --> Ano ni pangalan ng procedure na mo sa Local 12? --> Update salary. --> Can we try to change maybe update salary one gano'n? --> Para makita lang natin. --> Tapos i-run mo siya para mag-create yung procedure. --> Okay. Tapos yung... --> Yan, okay. --> Nak-compile siya. --> Run. --> Yun. --> So nag-update siya. --> So hindi natin lang po nangyari din sa procedure, no? --> Siguro naka-filter ka. --> Sige, refresh natin yan. --> Procedures. --> Click mo nga po yung Local mo. --> Baka naka-filter ka po. --> Click mo yung Local mo. --> Tapos click mo yung Filter. --> Baka naka-filter ka. --> Yun. --> So may error ka dyan sa... --> Kasi naka-begin. --> So mali yung procedure. --> Naisama natin yung begin doon. --> So you can simply delete the... --> 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 224. --> I put application there. --> And another sample in PLSQL. --> PLSQL. --> Slides 224. --> So yung function sa PLSQL block, --> it performs a calculation or action --> and return a value. --> Functions are useful --> for encapsulating logic --> that needs to be reused. --> So this function, 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 224. --> another sample in PLSQL --> sample. --> So we declare --> bsalary --> bbonus. --> Then we begin. --> Then we call to calculate --> the bonus. That is --> bbonus. --> We calculate the bbonus. --> And we display --> the result. --> Then we end. --> So we should have --> the output, the bonus salary of --> salary is --> then the bonus. --> Yes po. --> Sa function po, --> good practice po ba na --> mag return lang ng --> single value or specific --> value. Yes, it should return --> a value. --> Hindi siya parang procedure na --> may multiple --> operations sa noob po? Yes po. --> Kasi ang plan na natin sa function --> ma-reuse siya. --> Ah, so pwede siya ma-reuse --> inside the procedure din na po? --> Yes, yes. --> Siguro yung gusto kong --> iano sa function, --> iaboy natin yung side --> effect kasi yung function --> should not perform --> any database --> modification. --> Kung gusto mong may --> database modification, --> use procedure. --> Pero kung --> calculation, --> mga ganon, --> tapos it returns --> single data, single value, --> use function. --> let's remember the --> single responsibility principle. --> So meaning --> a function should not do --> one thing and should --> do one thing only and --> do it well. --> Basta ganyan niya --> yung function can be embedded --> sa mga SQL queries. --> So design them for --> performance. --> Let's avoid yung mga complex logic --> or multiple database calls --> sa loob 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 loob --> 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 then --> to put comments like --> what is the purpose of the function? --> For example in our case we --> can comment --> function calculates the total bonds --> for an employee. Then we --> define the parameters. --> empid stands for employee --> empid. Then the returns --> the total bonds amount. --> This help us our colleague --> in your --> future self to remind --> what this function does. --> Okay, so since --> you tried the function, let's move on. --> So again --> summary --> SQL provides a variety --> of database object to organize and manage --> data in logic --> and logical effectively. --> So each object serves --> a unique purpose. --> Enhancing performance, --> reusability and security. --> First is we have table. --> We know table is --> structure data. --> We have sequence generate unique --> numeric values. --> It's commonly for primary keys. --> And it configure --> configurable to start the value. --> 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 --> a specific column especially --> millions of data. --> Then the views, the --> purpose of that is to create --> virtual tables --> for simplified and secure data --> access. --> So do not store data. --> They provide a query --> abstraction layer. --> For example create view, employee --> view as select first --> last name. --> Employees for salary greater --> than 4,000 for example. --> Then the procedure. --> So again it encapsulate reusable --> action to perform operation on --> 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 --> perform calculation operation --> and return a result. --> So marami pa tayong --> sample in the next slide. --> So here --> we have --> syntax for parameter procedure --> and function. So here --> we said param in --> yung param to out. --> Kisabihin mag output siya --> param 3 in out. --> Bibigyan ko na example niyan in a while. --> Then sa function. So we create --> a function param on in --> param to in --> tapos return --> anong yung data type? --> Return data type as --> tapos. So --> begin tayo na ato na yung return --> value. --> So let's try this. --> So let's try to create --> the procedure manage salary. --> So we calculate the bonus --> then we increase the salary. --> Let me check --> if I put that in the --> one moment. --> It's 227. --> Let's try --> this slide 227. --> Then on --> the google docs --> I also put the application --> and the trigger --> optional trigger. --> Because trigger will be discussed later on. --> But you can create your trigger now. --> Let's try it. --> Let's create the procedure manage salary. --> Again --> the employee ID. Let's --> replace it with EMP ID. --> If you see one. I'm going to 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 2 minutes. --> Thank you. --> Thank you. --> Thank you. --> Thank you. --> or --> Or --> or --> or --> Or --> Or --> Or --> Sorry --> I mute. --> Sorry. --> Yeah, yeah, yeah. Go ahead. --> Natry nyo na po pala. Yung ano po. --> Let's go back here in --> Yes po. Iraran po. --> Naran nyo na po. May procedure na po. --> Naran na po namin yung --> yung trigger po. Naran na rin po. --> Okay. Yung procedure po. --> Naran na rin po. --> Yung procedure po? --> Yes po. Na natry. --> Okay. Good. --> Bonus po. --> Yung po. --> Tapos yun. So create a --> function. So create --> a 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 --> in the P bonus parameter --> and return the --> result. Okay. --> So ang input natin dito --> yung 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 total compensation. --> So here naman po --> yung parameter with --> cursor. So here --> we create a cursor, --> employer cursor --> so with a parameter --> p depth ID or department --> ID. Then the employee --> ID and salary. So we --> begin with --> open employee --> 10. So meaning --> we're passing the 10 --> here sa cursor --> employer cursor --> tapos ipapasa --> nyo dito. So --> mamimit nyo yung requirements kasi may department ID --> tayo na 10. So pag may --> may data tayo papasa ko sa loop --> so fetch --> employer cursor into employee ID --> salary. So sa ngayon inoutput --> 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 param1 in --> then the data type number --> then default nya is --> 100. So multiple --> parameter types you can mix --> in out and in out --> parameters in a single sub program. --> So n number 3 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 run times error. --> So benefit --> of that is prevent unhandled exception --> and 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 simplifies modification and --> enhancement by isolating --> logic with sub programs. --> Parameters enhance --> the flexibility and the reusability --> of PLSQL 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 --> procedure. So we create and replace a --> procedure then param1 --> if it is in data --> type or if it is out --> or it's 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 the procedure name. --> 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 --> equals PMID. Then --> the exception, so it handle --> run times early decor during execution --> of the program when --> no data pound --> sabi niya no record pound. --> Then here's --> the example of creating --> update employee salary --> So here --> we have a parameter of --> input employee ID --> and increase percentage --> the percentage we need to increase --> the salary then we have the --> output parameter the P new --> salary as an out --> and P bonus an out --> so it's return the updated --> salary after applying the increase --> and 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 employee's --> current salary. Then --> for the executable section --> we fetch the current --> salary the select --> into statement retrieves 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 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 .10 or --> 10% --> then updated statement --> 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 --> i-file nyo yung error employee ID --> when others --> unexpected error occur --> so hindi natin --> kumaga i-calculate lang natin yung error --> para makita natin kung saan --> ano yung mali. --> 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 PLSQL so syntax --> error --> occur during compilation --> due to incorrect syntax --> so mandalis natin --> itong nakita --> then runtimes error --> occur during execution due to invalid --> operation or wrong input --> such as division by zero --> or a missing table --> so logical errors as well --> it cause unexpected behavior due to --> incorrect logic in the code --> so example of syntax error --> so for example this one --> we begin --> so it says --> 00201 identifier --> dbms put outline must be declared --> so type po kasi sya --> dapat put underscore line --> so ang solution natin --> lagin natin na underscore --> sample naman ng runtime error --> so nagdeclare tayo ng --> v0 be result number --> tas we're trying to divide --> by zero --> yung number --> result to divisor is equals to zero --> so ang solution natin --> is magkaran tayo ng exception handling --> so --> nagbigin tayo tas nagdagtag tayo --> ng exception when zero divide --> then dbms output --> put line cannot divide by zero --> or you --> can log it in the database --> gano din pag no data --> found so we add --> an exception --> pag no data found --> just put or output this --> or put that in your --> error lag --> pag yeah pag too many rows --> for example --> when we --> so that error message 01 --> 422 --> the exact fetch return more than --> requested number of errors --> because the cause is --> the select enter statement is designed to fetch --> exactly one row --> when there were close matches --> multiple rows this error is raised --> okay --> so ang solution natin yan --> is --> pwede tayo mag limit --> or pwede tayo --> mag use ng cursor --> so mag exit tayo when empty --> cursor not found --> or pwede natin --> ilimit kanyari --> per department --> id --> and row num equals one --> so pwede tayong mag limit --> then here is how --> you do debugging --> using put line --> so dbms output --> put line --> which is ginagawa naman natin --> tapos ito yung na practice natin --> yesterday yung pag --> lalagay ng sql code --> and sql err --> errm --> which is built in --> commands sa --> plsql oracle --> database --> then key characteristic --> of pro procedure --> inga encapsulation combines logic --> into reusable unit --> can accept --> input in --> out or both in and out --> data type --> then here's the --> example --> okay --> can we try this? I didn't put --> that in the --> since this is make me lang naman --> can you type this in your --> desktop please --> para matari natin --> yung with parameters --> so type creator replace procedure --> calculate area --> okay --> okay i'll try to put that --> in the document --> it will be line 244 --> so example --> create or replace --> calculate area --> so we declare length --> then we begin --> then we declare --> rec number --> calculate area --> lalagay ko na sa 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 --> so --> so --> so --> so --> okay --> so --> okay --> okay --> so --> I put the correct code in the document. --> Pag successfully compiled, procedure calculate area compiled. --> Once the procedure compiled, you can call it by begin the calculate area. --> Calculate area po may space po. --> May show yung line number mo para mas madali natin makita. --> At line for 1234 cannot be used on assignment target. --> So declare rec area number. --> Once na ma-create na yung procedure, pwede mo siya erase. --> Line for 1234, yung calculate r-score area, column one. --> May procedure yun na ba ikaw? Calculate area? Ayun, calculate area. --> Patingin na po ng calculate area mo, sir Miguel. --> Create or replace non-editional procedure. --> Can you remove mo na yung non-editional? --> Yun number out with... --> Ayun po, sir. --> Yes po. --> Ata po yung pa si... --> Si sir Miguel, pa ito ng kasadita. --> Ayos po. --> Ayos po, sir Miguel. Game. Nakatingin pa ko sa screen mo. --> Pwede mong alisin yung non-editional na word. Not sure with that. --> Dun po sa line one. --> Yung non-editional, yun sa line one mo. --> It should be create or replace procedure. --> Yan, just remove that. --> Procedure calculate area na grun. --> In, out, in, number is... --> Can you try to create procedure another, sir Miguel? --> Tapos copy mo yung ano ko? --> Yung nasa Google. --> Tapos laging natin calculate area 2. --> Para we can have a comparison. --> Wow, si ma'am Tin. Tapos na. --> Yan, si sir Ray. --> Nakita ko na rin, okay na. --> Si sir Jerry, ganun din. --> Sir Joseph, narun mo na po. --> I mean, nakapag-calculate na siya. --> Yan, okay. --> Sir Miguel na lang. --> Create or replace procedure. --> Calculate area. I think it's look good. --> Okay. --> It compiled good. --> Let's try to run it. --> Awesome. --> Awesome, awesome. --> Thank you po. --> Welcome po. --> Yan, so eto naman 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 sheet. --> One moment. --> This will be slide 245. --> So let's create a procedure. --> One moment. --> So increment value, the input and output parameter. --> Then... --> Then the exception. --> So I add another... --> When others... --> Then it will raise an application error. --> Then we increment the 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 num equals current value increment by then increment. --> Then we display the output. --> Okay. --> So let's try to create the procedure. --> In slides 245. --> So let me put this in the Google sheet. --> So let's try to create the procedure. --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> ... --> sql plus you can also do or execute a function like in this or procedure in this instance --> We use exec gets alert --> 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 Jando then department is IT --> then --> How 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 in two --> EMP name and salary so from employees or employee IDs EMP underscore 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 Jando salary is 60,000 --> 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 kuneari may bonus tapas --> Magbabago ng bonus for example maibang parameter --> so --> pag nag lagi ka ng in and out so it can accept value --> default or any value then it expect to out a --> New value or siya parin ko wala niyong binago dun 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 --> 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 iya plus 500 --> kaya pagdating dito --> so si 101 na ID --> so 500 yung --> 1000 for example ito in hardcode lang natin kaya naging 1500 then we create or replace debug demo --> so step one we --> The procedure started --> Then exception --> So error data no pound so ito yung pag kumbaga 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 kong error no data found --> Tapos structure naman pag out parameter yung procedure so param one in --> ito lang tatandaan yung out na data type meaning it expect to --> To return or to have new value logic to calculate or retrieve values from param two --> So --> This is the sample of creating a procedure with an out parameter --> So we create a procedure --> So we have been 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 the variable as square result number then we execute calculate square 5 --> Then the square result kamagadito natin lalag yung square result don't forget to --> To define it then we can print that so since 5 siya 5 times 5 25 ka 25 yung result --> So if we put 6 it will be 6 times 6 36 --> So how about we by the way mag break tayo ng 3 p.m.. --> So let's finish the entire up to trigger --> So we create or replace procedure so for example 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 accept get employee details sino yung 101 --> tapos kanino siya ililipat say EMP named and 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 used in 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 jando --> 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 the 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 you 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 equal so was we assign 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 for 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 put the bonus the salary times 0.20 or 20% --> If the rating is 4, salary nya is 10% --> Else --> Kung magandis siya nag-qualified sa dalawang criteria, maybe the rating is 3 to 1 --> Then the bonus will be --> salary times 5% --> So that's how you create a function this is the employee --> So using the function npl 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 --> yun salary comma performance as bonus --> From employees --> so --> May kita na natin yung --> kumaga 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 define 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 --> Using SQL SQL ERM, which is a built-in --> Function in PL SQL. So it provides their message --> Associated with the most recently raised exceptions --> Here's your handle that so we'll again understand when others --> So we should 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 --> 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 --> The miracle is a begin so be sure na but declare new Muna as exception you variable now --> so pag narration --> Variable now as exception --> narration --> Meaning depends on logic --> You know what with the exception? So he released exception and 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 --> because the error code of --> Oracle is your or a does zero one four seven six, which is not relevant to the user if we're gonna show it --> then describe function names, 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 did this 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 time --> Ano yung mga --> Argument name so p-price and in data 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 because it means and they have the function parent in and 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 koning type nyakon 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? --> I --> Yeah, I think but in size scale developer, yes --> You --> You --> Well, I'm a little bit serious or Jerry, but I may function kapo bang ganun calculate total price --> Give up go down --> Oops, stop and functions calculate bonus. Try mo nalang pin calculate bonus. You know, okay --> So make it a natin yung code --> So --> I --> Feel feel 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 I --> Think now it is your calling fields SQL. So at the moment yung nested function call --> So nested function call in PLS will allow one function --> To invoke another to build more complex logic below is an example where a double area --> Function called a calculate area. So salob ng function may tinaugpas ang 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 there 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 --> So just first ensure that the calculate area function is already created --> Then create the double error function when you get a 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 pwede tayo mag-assign ng default value dun sa tax rate in this case in the calculate tax --> So mandatory versus optional parameters, so --> P amount is mandatory. So it's a no default value --> While P tax rate is optional --> So kong walang 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 ing nga pag yung tax rate walang pre-novate 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 others --> Invalid or null input gracefully to prevent run times 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 --> Um, yeah --> So, I think let's have a break let's --> Resume by 320 is that okay? --> 320 p.m. Okay --> Okay --> Ako daw magbabag kasi hindi --> Hindi magbabag --> Pero ano tayo? --> Sa coffee shop --> I --> Boss asan si George --> Asan si George --> Nasapan chon agad ba --> Okay --> Asan si nasapan chon, hindi na namin kung parang gagawin. --> Oh sige update update mo ako pre ha --> Oh sige na sige na talaga talaga --> Thank you pre --> Tingin mo bang yung pintumating mo yung darilangin pag magsasala? --> Weh? --> Kasap ko ba? --> Ah! --> Diyo wala na yung family, ma'am --> Suspital ko siya namatay? --> Kasap ko tayo? --> Kasap paano ulit? --> Paano na? --> Yung family may ano na ako ngayon --> Ano na naman? --> Ano na naman? --> Ano mong mahal ba akong magkain man? --> Salita ko ni George --> Hindi parang man na naman --> Sige namin ni Becca --> Hindi na ba? --> Hindi naman ko na Becca eh --> Mula na tayo ngayon na? --> Hindi baka mababigyan eh --> Okay --> As well --> Hindi --> Less na to, less na to --> I mean yes --> Hindi ang ingi pa nyan? --> Hindi naman, ikaw lang maingay --> Hindi ako maingay na --> Naman dito siya hindi siya maingay ko --> Parang maingay ako --> Manood ka si Tutor --> Anong turo? --> Ayano --> SQL --> Kala po ano --> Ikaw yung natuturaan --> No, I'm the one who's teaching --> I'm not teaching you business --> No --> database --> SQL --> Paano ba siya yung natutura? --> Kasi kadaan --> Ikaw na kung anong do --> Sige atin --> Kita dyan? --> Oo --> Yung slide --> Bailangan mo naman? --> Yes --> Ikaw mababalan ka? --> Yes --> Pagtimuro ka? --> Yes --> Pa? --> Magkana ko ha? --> Sige eh --> Baka pabalik ka marami eh --> Baka mabalik ka eh --> Pagbaka natin sa post pa --> Hindi ka naman matan sa drums? --> Oo --> Wala akong drums --> Bakit? --> Pwede pa? --> Sige --> 52 minutes na? --> Pwede pwede 10 nalang --> Hindi kanyang naman, nakikita ngayon --> Kanyang napakalim na hindi yung nagkaroon --> Magkakawa --> Bwede hindi sabihin siya sa awa sa'yo --> Wala sabihin magkakawa --> Kasi hindi naman magkakawa --> Oksan? Salid? --> Ano na? --> Sige eh --> Sige --> Sorry --> As marami --> Forgive this --> Ay --> Ayan naman siya --> Malik --> Muglasa ni --> Salaya naman sasitin yung drum --> Uy bwag na out na --> Bwag isa na kami bwag na --> Ayun na --> Bwag na bwag na --> Bawal na maingay --> Yung door naman ayusin ninyo naman --> Bapag maiwan! --> Alalain nyo sa ngayon! --> Bapag maingay bam! --> Okay great, so let's continue. --> So again, I'm just showing a sample calling a function, --> but you have the base knowledge on how to create a function. --> In and out ng process. --> In and out procedure. --> So calling the function in anonymous PLS SQL block. --> So here we verify that the calculate tags function is created with the following definition. --> Be sure to set server output on. --> Then we can run the anonymous block. --> So in this case... --> So first is we declare tags amount as number. --> Then we begin to call the calculate tags function. --> So we put the P amount 1000 then the tags rate 12. --> Then we ensure that the function exists, the calculate tags is created. --> Then we can run that. --> So anonymous PLS SQL blocks provide a quick and flexible way to test and utilize function like calculate tags, --> making them invaluable for ad hoc computing and debugging. --> You can learn more about that. --> So calling function with parameter and SQL+. --> So when I say SQL+, it is running on the terminal. --> So here we define the variable P amount, tags rate be solved. --> Then we begin, we initialize or assign values to amount and tags rate. --> Then we calculate the tags rate. --> The result will be 150. --> So that is the tags. --> For calling the function in SELECT with aggregates. --> So here we call the calculate tags on the SELECT. --> So we calculate the tags after calculating the tags. --> So the order amount, let's imagine that it is in the total tags. --> So we calculate the tags then we assign it from orders. --> Okay. --> So we can see what is included in the calculate tags if we use describe. --> So best practices for using function in SQL+. --> So enable the server output. --> Use the dual table for calling function without a specific table. --> Debug with DMS output line. --> Then leverage SQL plus variable. --> So pass parameter dynamically using SQL plus variable print. --> So the dual table is a single row or single column table provided by Oracle for evaluating expressions and calling function without needing a table. --> So example, benefits of modular programming. --> First is reusability. --> Code can be reused in multiple programs or script. --> Maintainability, it's easier to debug and update specific parts of the application. --> Readability, smaller modules make the codes easier read and understand. --> Scalability, modular design facilitate adding new function without major changes. --> Then encapsulation, packages allow hiding implementation details while exposing only necessary parts. --> So here's the example of modular programming in SQL. --> So like here we create replace procedure update salary. --> So the procedure encapsulate a series of statement to perform a specific task. --> They are commonly used for data manipulation and business logic implementation. --> So for example, in this case, our procedure is to update the employer salary. --> So we call the procedure. --> So for example, we begin to update salary. --> So here we take the employee ID that identifies the employee whose salary is to be updated. --> Then the new salary specify the new salary. --> Then the update statement modifies the salary column. --> So we set the salary with the new salary. --> Same here with the calculate bonus. --> So packages. --> So packages will be learned at the end of the module. --> But let's have a quick overview that the package is a collection of related procedure, function, variables, and cursor. --> It's also a modular unit in PLSQL that groups logically related like procedure, function, variables, and cursor. --> So the components of package, it's declare the public interface, specify which element, procedure, function, or variables are accessible outside the package. --> The package body contains the implementation details of the elements declared in the specification. --> It can also include private elements that are not accessible outside the package. --> So example of package is the employee management. --> So here, this is the basic index, create or replace package, employee package. --> So the procedure is mag-add siya ng employee, then function, get salary, we declare employee ID, then return the number, then that's the employee package. --> It's like we're wrapping all the procedure and function in one package to have another logic. --> So I will give example and we will have exercise, I think, by tomorrow. --> So this is the example of creating a package body. --> So procedures, so we add an employee. --> So begin, insert into employee. --> Then we have another function here, get salary. --> Pay employee ID, then we return it, return number, so salary number. --> Then here we select the salary into salary from employees where employee ID is employee ID. --> Then return. --> So we can call that using this employee package, this one employee package, that add employee. --> So here we use the function or procedure, add employee. --> And we take the parameter's name, Jando, and the salary is 60,000. --> So the output will be salary, then the employee, for example, get salary. --> So this is the function of 101. --> So trigger, I think I have that in trigger. --> So triggers are automatically executed in PLSQL blocks that run when a specified event occurs in the database. --> Triggers add automation to modular programming. --> Let's try to use the trigger. --> I prepared one example in slides 298. --> Can we try that? --> So first we have to create a function, isHighEarner. --> Then we create or replace a trigger. --> Then we prepare a test case that we can use. --> Can you try it please? --> Slides 298. --> So first you create a function, isHighEarner. --> Nasababa din siya. --> Tapos we can create trigger. --> Tapos pwede na natin siyang i-test. --> Pwede tayo mag-insert ng employee. --> And we can update. --> Dapat ma-trigger natin. --> So ang purpose natin is mag-fire yung trigger na ginawa natin. --> So isHighEarner, unahin muna natin i-create yung function. --> So I'll put it on top para hindi po kayo ma-confuse. --> So first we create a function, isHighEarner. --> Tapos we create, kasi mag-rereclame mo yung sabi niya --> kailangan i-declare yung isHighEarner. --> Tapos sababa, how to trigger it by insert or update. --> Yan, good no? --> Nagkita natin yung trigger na nag-fire when we insert the data. --> Tapos sababa, kasi mag-fire yung isHighEarner, --> Tapos sababa, kasi mag-fire yung isHighEarner. --> Ok na no? It seems nag fire yung trigger. So trigger works, correct? --> Nice ma'am Christine. --> Nakita natin na nag trigger yung ginawa niyo pong trigger. --> Ok, I'll continue now. --> So yun yung anonymous black or unnamed PL SQL blocks that are used for one time operation for testing. --> It's also with one time tasks such as testing reusable PL SQL module like procedure function or packages, running ad hoc data operation, prototyping or debugging PL SQL logic. --> So the structure of anonymous block is declare which is an optional, then begin, begin is a required, it contains executable statement, the exception which is optional and the end which is required that marks the end of the block. --> So the example of that is bonus, then we calculate the bonus. --> So again best practices in modular programming language, follow the naming convention, use a meaningful and consistent names for module like employee underscore PKJ or package, calculate underscore bonus, then use package, packages for organization. --> So group related procedure and function into packages, so we will dig more deeper later on with the packages and triggers. --> Then the benefits of that is improved readability, easier maintenance, reduce memory overhead since packages load as a unit. --> So it's minimize dependencies, so create self-contained modules to avoid tightly coupled code, avoid hard coding values, use parameters to make module flexible. --> So document the modules, so add meaningful comments and description in your code please. --> For procedure and function, describe the purpose, parameters and output. --> For packages, explain the overall purpose and key components. --> Then test independently, so verify each module functionality in isolation. --> So you can use anonymous block or SQL plus command to test, for example begin employee package.addemployee for example. --> So include both positive and negative test cases, mock dependent module or data if needed to focus on the target module. --> Then number six, optimize for use design module to be generic enough to apply multiple scenarios or application. --> So example of that yun nga yung calculate bonus. --> So siguro ang additional recommendation ko is yung error handling, make sure may exception kayo, then avoid overloading. --> Keep the module logic simple and focus on single responsibility. --> Performance optimization, use efficient queries and avoid unnecessary loops. --> Tapos index frequently queried columns to speed up module execution. --> And if you have a chance, use version controls. --> Use versioning to track changes in packages and procedure making it easier to roll back if needed. --> So adhering to modular programming best practices in PLSQL promotes maintainability, reusability and performance, ensuring robust and scalable database applications. --> So here's the example of PLSQL procedure. --> So we update the employee, then we commit the changes, then here we output, then we have the exception to handle all the error. --> Here we calling the procedure, this is how we can call the procedure which we already know. --> Here in this test case, make sure you have a test case for invalid data or negative data for example to make sure that the error will fire and to make your database more solid. --> Then create a procedure with, this is the example with in and out, double out as well, then with the exception. --> Then we have here the begin to retrieve all the employee details. --> So we already know that to bind our variables, so we have a variable here, we bind the data since the two data is out, so all the data will be passed here. --> So in this case, if we print vname, it will be the John Doe and the salary will be 75,000. --> If you look at here, so we bind employee name and salary as out, so it expect output parameter. --> Example of creating a function which we already did, so create a function, mag-input siya ng salary, so 50,000 through otherwise it falls. --> Then for anonymous block, so we use this begin then this is high earner, then this employee is high earner if the salary is 60,000. --> So here with the database data, so here the expect output will be greater than 50,000. --> For example, the employee ID is 101, if emp salary is less than or equal to 50,000, so the employee 101 is not higher. --> So if employee ID 101 does not exist, the no data found exception will be raised, so it will be on the L, so it's not a high earner. --> So step to execute, ensure that this high earner function exists. --> Yeah, I think we can try this since we have the function. --> Let me copy this in our Google Sheet, it will be 308, let me put that. --> So let's try to do this example, so we call the function if statement, since we already create a function is high earner a while ago, so this should run on your end. --> Let's try the slide 308, so our key point here is the select statement retrieves the employee salary from the employees table. --> Then we call a function is high earner, it's a function evaluates the retrieved salary to determine if the employee is high earner. --> Then dynamic evaluation, you can change the emp ID variable to test with different employees ID. --> So error handling, so we add an exception block to handle case like pag no data found or when the employee ID does not exist, any other unexpected errors. --> So best practices, always handle exception especially in select into statement because we don't know what might be the error. --> Also dynamic testing, test with valid, test with invalid data and edge case like employee's IDs. --> And reusability, so combine this block with other reusable components such as packages or procedure. --> So let's try this example, so we call the function if statement, since we already create a function is high earner, so our key point here is the select statement retrieves the employee salary from the employee table. --> You can try to change the variable we declare on line 33, maybe put number BMP NSQR ID 2, or I think, may error ka pa po no? --> Try mga po Iran, tingnan natin yung error. --> So let's proceed, I think every one of you got the code running correctly. --> So multiple condition naman tayo, so can you try to gamit naman tayo ng if else, really quick let me paste this, paste ko nalang yung if else. --> Or maybe I'll paste everything, so you can combine if else, so ganun pa rin, ginamit natin yung function na is high earner, dun sa if with multiple condition. --> So if high earner siya and EMP salary is less than 10,000, then ipo-put ngayon yung line na yun, mag-output siya ng employee earns a good salary but is not on top earner. --> Else if yung salary niya greater than 100,000, employee is a top earner, else this employee is not a high earner. --> Buma, nababokas ka ng bokas ng pinto, ang kulit mo talaga tutor, nagboig naman dito tutor. --> Okay the code is in Google Docs, I'll name it slide 308 if else. --> So you can play with the numbers, you can play with based on your database, okay 3 more minutes. --> Okay. --> Okay. --> Okay, I think everyone made the code running successfully. --> Go ahead Mamthin, salary less than 9, palitan niyo po yung 9 yung less than, alisin niyo po yung 9. --> There you go, awesome. --> Okay, I'll continue. --> So ito naman yung example ng trigger for salary validation which we did a while ago. --> So here we check the higher earner. --> Then a package in Pella School is a scheme object that groups related procedure function. --> So yun yung ina natin kanina yung package. --> So the step to create a package, so replace package, package name, tas yung procedure function. --> Itit bang na ulit siya. --> Okay, so deploy. --> So let's try to create a package. --> We will use it later on. --> Let me paste this code one moment. --> So it will be slides 315. --> Let me know if you successfully create a package. --> Sir, hello po. --> Yes po. --> A package body lang po talaga yung nasa. --> Ay, one moment po. --> Let me fix that code. --> Oh yeah, one moment. --> So first we will create a package. --> Let me paste it. --> So first we create a package installation. --> So hindi siya mag-return kasi wala pa ka-employee package. --> Then yung number 2 is, yun, ito na. --> So we can test it. --> So we need to create employee sequence. --> So we have to create a sequence here. --> And create a sequence. --> Create sequence employee sec, start with 1, increment by 1. --> Then we create the package body. --> Now we implement the method defined in the specification. --> So we create or replace package body, get salary, retrieve. --> So our EMP ID. --> Yung po, niligay ko na sa Google Docs. --> The sequence command nandun din po. --> Iligay ko na lang sa taas. --> Wala tayong employee name, palitan natin yung first name. --> And yung EMP ID. --> Tapos to test the package. --> Sequence, here, sequence. --> So possible na magkaroon kayo ng constraint violation kung nadeclear natin yung sequence na mag-start sa 1. --> So I suggest to start to 200 or 300, create or replace sequence. --> Okay, one moment. --> Okay, one moment. --> Kapag compile na po kayo ng package. --> Okay, great. --> So let's continue. --> So calling the procedure. --> You can call the add employee. --> Procedure to add a new employee. --> Then we can call a function. --> So here we employee package that get salary, which we declared doon sa function natin na package. --> Tapos sino yung ID. --> So we get the salary. --> Then we can output the salary. --> So private elements in package body here. --> So be private variable is declared inside the package body. --> It is not exposed in the package specification. --> It can only be accessed within the package body itself. --> So the public procedure is the add employee is declared in the package specification. --> So it's accessible outside the package. --> The procedure internally use the private variable, be private variable, to modify salary value before inserting the record into the employee's table. --> Number three is the encapsulation. --> The private variable is hidden from external access ensuring that the logic involved being be private variable is encapsulated and protected from unintended modification. --> So private variables is declared the package body and not visible again outside it. --> Then public procedure declared in the package specification and implemented in the body. --> So the expected outcome here will be a new record inserted into employee's table. --> So the salaries will be 50,000 plus 100 equals 50,100 as private variables is 100. --> So again the benefits of using private variables is encapsulation because it protects sensitive data or internal logic from being accessed or altered externally. --> It keeps the internal implementation details hidden. --> Also the control access, private variables can only be modified by logic within the package body ensuring consistency and flexibility. --> So internal variables can be modified or extended without affecting the public interface. --> So private variables in a package body enhance encapsulation and modularity ensuring that only intended functionality is exposed while protecting the package internal state and logic. --> So we mentioned that as well. --> So yeah again I says that modularity, the description of that is group logically related procedure, function and variables cursor into a single unit. --> Benefit of that is enhance the code organization and readability by categorizing related to functionalities, simplifying maintenance and debugging. --> So yung example natin yung procedure is yung add employee, yung function, yung get salary or calculate bonus. --> Then encapsulation, implementation details are hidden by exposing only the interface in the package specification. --> So private elements are defined in the package. --> So benefit, it protects internal logic and ensure that only intended function is accessible to the users. --> Then reusability, so pwede natin marius yung mga code. --> It promotes consistency and efficiency by eliminating redundancy. --> Also it will add performance. --> So the entire package is loaded into memory during the first call. --> So reducing the repeated this IO or input output. --> So the benefit of that is it reduce SQL parsing and compilation overhead kasi nililode na sya. --> So it improve execution speed for frequently called procedure and function. --> For example, we create a math package used in multiple reports for complex calculations. --> So it's preloaded nature reduce latency across call. --> So hindi mo na sya tatawagin ulit-ulit. --> Then of course security, so access control can be managed by granting or revoking privilege at the package level. --> So you can have a package that for example contain all the procedure and function that only for example for financial. --> So we can do that. --> And it simplifies the security management while ensuring sensitive operation are restricted to authorized user. --> For example, only HRO is granted to execute privilege on the employee package to update employee records. --> While read only access is given to the report viewers. --> So simplified maintenance. --> So it's provide the, you know, changing the package body does not require changes to the package specification. --> It's provide the interface remain consistent. --> Benefit of that is it enables data logic or bag fix without affecting dependent application. --> Okay. --> I think package improve code structure and readability. --> It enforce encapsulation and optimize performance. --> Also they enable reusability and centralized logic for consistent and efficient application development. --> Security features provide control access then. --> So making packages and essential component and PLSQL modular programming. --> So continuation of advantage of using packages is number seven, overloading. --> So packages allow overloading of procedure and function enabling multiple sub programs with the same name but different parameters list of types. --> Benefit of that is it simplifies the code by using the same name for similar functionalities while handling different input types of combination. --> Example of that is the calculate tax, salaries in the table, compute tax for bulk list of salaries in calculate. --> So gumaga yun yung package. --> And also a better dependency management. --> So a package is a specification independent of its body. --> Changes to the body or implementation do not affect objects that depend on the package specification. --> So benefit of that is it's minimize cascading recompilation ensuring system stability especially in large application. --> Then persistent state. --> So public variables declare in the package specification. --> Retain their values through the duration of a session. --> The benefit of that is also enables tracking of a session in specific state. --> Information or caching data to improve performance and avoid redundant computation. --> So for example public variables session user ID in the package stores the current user ID. --> So this variable can be accessed by multiple procedure like itataka natin yung activity or maybe ilalag yung event within the same session. --> And also error management it includes packages can include reusable and centralized error handling routines ensuring consistent error logging and reporting. --> So it simplifies the error handling by reducing code duplication and making error management more maintainable and traceable. --> Example of that is a procedure for lag error and utility package that writes error details, error code message timestamp to an error log table. --> Allowing developers to audit issues efficiently. --> Then code documentation. --> Packages naturally organize code into logical units with a clear interface making the purpose and usage of the package self evident. --> The benefit is to facilitate collaboration among the team members and is the onboarding process for the new developer by providing a centralized and well documented interface. --> Example of that is the report package contains all the functions and procedure for report generation such as generates summary report and export to CSV making it immediately clear what the package is designed to do. --> Then improve readability and manageability number 12. --> Packages consolidate related logic into single units simplifying navigation and reducing clutter in the database schema. --> It improves readability and maintainability of code especially in large scale application with numerous procedure and function. --> A package for example user management combines procedure for user authentication like authenticate user for role management, assigned role and logging for lag user activity into a one cohesive module making it easier to locate and manage related functionality. --> So packages are not just the tools for organizing and managing code. --> They provide a foundation for building scalable, efficient and maintainable database application. --> By consolidating related functionalities and enforcing best practices packages can significantly enhance the quality and reliability of PLSQL program. --> So what are the public sub program in PLSQL packages? --> So public sub programs are procedures and functions declared in the package specification and implemented in the package body. --> These sub programs are accessible externally providing the primary interface for interacting with the package. --> So what are the key features? --> So public sub program can be accessed by any application user or PLSQL block that has the required permission to use the package. --> So a procedure for example add employee and employee package can be invoked by any user with the necessary privileges. --> Number two is part of interface. --> So public sub program define the interface of the package detailing how to external user can interact with the package while hiding the implementation details. --> So it protects internal logic while exposing only the required functionalities. --> Then direct invocation. --> So public sub programs can be invoked directly from SQL. --> So if the function is deterministic and does not perform DML or insert delete update. --> Also in PLSQL block such as anonymous block or procedure and other packages. --> So enabling modular programming. --> So advantage of public sub programs is centralized access. --> Provide a single reusable interface for a package operation, modularity, security and ease of maintenance. --> Public sub programs in PLSQL package serve as entry point for accessing the package functionality. --> They provide a robust, secure and modular interface enhancing organization, reusability and maintainability. --> So here's the sample of public package specification. --> So here we replace the declare package, employee package that we did a while ago. --> So dropping a sub program. --> So dropping a sub program can invalidate objects that depend on it such as other function, procedure, triggers or packages. --> So dependent objects need to be recompiled or recreated after the change. --> So if calculate area depends with get length and get width, dropping either dependency will make calculate area invalid. --> So be careful in dropping it. --> Consider what will be the outcome if we drop certain package. --> So DL lagging, schema changes such as dropping altering sub programs should be lagged for auditing and traceability. --> Use custom lagging mechanism or database tools like Oracle Audits Rail to maintain record of such changes. --> So DDL mean data definition language statement executed in Oracle database. --> These are the operation that modify the structure of the database. --> Schema such as create, alter, drop, truncate, rename and comment. --> So the purpose of DDL lagging is for auditing, troubleshooting, debugging and recovery. --> So to enable the configure DDL lagging, we just type alter system set enable DDL dragging tool. --> Let me paste that in the slide 327. --> So I put in the slide 327 how to enable DDL lagging. --> To make this change permanent, add to the init.ora or SP file configuration. --> Let me paste this. --> So the allocation of DDL lags, once enabled, state my lag in the alert lag and separate DDL. --> Let me paste that. --> I think we can have multiple exam. --> I'll send now in messenger. --> One moment, let me grab a link. --> So it's just a 30 item. --> Once you got it, once I send you the Word docs, please create a copy. --> Write your name. --> Then export is once you're done, maybe bold the answer or color it whatever you like. --> Just make it whatever your answer is. --> Then send to me before 5 p.m. or consolidate please and email to mamcha. --> So here is the exam. --> Let me know if you all have the access. --> You can view it. --> It's a multiple choice so make a copy of the document in Google Docs then put your answer. --> Maybe bold. --> Let's make it uniform, make it bold. --> Then don't forget to write your name. --> Then after you answer, export as PDF then email it to mamcha. --> Then mamcha will send to me all the answer and I'll check it in my morning. --> Okay? --> Sige po. --> So I'm still here at 5 p.m. --> So just message me if I can't answer on your message. --> But I'm still here, okay? --> Smith po again ng 4.50 p.m. po. --> Then the deadline of answering that exam will be 4.50 p.m. --> Okay po? --> Okay po. --> Thank you po. --> Okay po. --> Pero pati ang tatagal ako ng classes. --> Susot mo kasi. --> Hindi mataman. --> No, no. --> Hindi yung magalit ng tarsal mo. --> Hindi po. --> Okay po. --> Okay po. --> Okay po. --> Okay po. --> Okay po. --> Dali. --> So hindi ako sleep talk ng muna-muna. --> Kailangan na na. --> Boy na. --> Ano boy? --> Mga egg boy. --> Mga egg boy. --> Mga egg boy. --> Hindi mag-lock ka naman. --> Kapag kanalinis na biniminit. --> Okay muna-muna. --> Okay, last 5 minutes. --> Please wrap it up. --> Save as PDF then send to mom chat. --> Thank you. --> ав --> Oh --> Yes po? --> Ano po yun? --> Xample nyo kanina. --> Yes. --> Bago pa tayo mag break down likelihood --> Ay believe you have all the PDF. --> Yung workshop ba? --> Apo. --> Yung result naon? Yung answer? --> Ganun? --> Yeah, I think it's on the group chat. --> Hindi ko po ba na-send? --> Let me send it again. --> One moment. --> Share --> Copying --> Okay, I pasted it --> in the --> Zoom --> Hello po. --> Yes po. --> Nasend ko na po via email kay Mamcha. --> Yung lahat po ng ano? --> Yung multiple choice po. --> Apo. --> Yung una sa workshop. --> Nire-review ko na po yung sa una. --> Yung sa workshop. --> Thank you so much. --> So, how is our second day? --> Did you learn po? --> Is it good na --> nag-code po tayo? --> Yes po. --> Mas gusto niya po --> Okay po. --> Yan. --> So, I have a meeting --> in 5pm as well. --> So, we have 10 minutes. --> In our PL SQL --> interactive SQL --> coding. --> Please look at the --> slide. --> One moment. --> Create a package --> slide --> 3-8 --> Yes. --> I put start on that. --> So, what we're doing --> is showing --> that if we delete or remove --> the dependencies --> on that --> function --> we will get a result of --> invalid. --> Again, slide 3-8. --> So, first --> we have to create a function --> getLength --> then create a function get --> width --> then create a function calculateArea --> but I think we already create --> a calculateArea so maybe put --> 2 calculateArea2 --> then we --> before we try to drop --> the getLength --> try to do the --> number 4 muna wherein we --> query the --> we're selecting the calculateArea --> 2 --> in the database and see the status --> of it. So, the expected --> output before dropping --> should be --> still valid --> as well. --> Then, quality --> check the calculateArea so --> select calculateArea2 from --> dual so --> expected output will be --> 50 --> since we multiply 10 --> by 5 --> then status --> query --> everything is in the --> document. I also put notes --> and the dependencies --> and --> recompile if invalid --> objects. --> Tomorrow --> po --> we will have --> another exercise --> more on coding so --> tomorrow --> tapusin ko lang po --> lahat ng --> slides ko --> then we can jump --> into coding na po. --> Okay po. --> Okay po. --> Ayos ko ba mo? --> Oh, pa mo mo, kumukha pa? --> Hindi ka pa naliligo. --> Ang bahawa mo pa. --> Ano ni, nagbiyos ka lang dami? --> Aris na tayo ba? Nagtehok na si Mami. --> Ayos ko ba? --> Sir, uh, good na po ba? --> Or tomorrow na po huli? --> Thank you po. --> Thank you. --> Okay, thank you po. --> Ba yan? Alam mo lang i-edit. --> Ayun, i-edit. --> Okay, I'm now... --> Hindi ka makaing nga? --> Ano? --> Oops, let's change the background. --> Change. --> Hi. --> Hi. --> Hi, pari. Nice to meet you. --> Nice to meet you. --> May kanina ben tanong pa dito sa office eh. --> Kaya... --> Yeah, this is my son, --> Hi, Ram. --> Sure, yeah. --> Uh, one moment. --> You're from what place? --> Sige, let's get to know each other muna. --> I'm... --> Yeah. --> So I'm from Das Macavite. --> But natira din po ako sa --> Valenzuela. --> So I'm --> a --> programmer in profession. --> I'm IT. --> I'm doing 14 years --> in web development. --> I help companies in their --> system, --> making it more easy to --> use, --> making automate all the manual --> tasks or processes. --> So I'm doing also mobile application --> in overseas, --> in several companies as well. --> And yeah, then --> just a quick --> background. --> I love badminton and last year --> I had a... --> I met coach Joard --> just last year then --> we had a really good time --> so every... --> almost every week po --> nag gumagala kami ganoon. --> Then we see the demand on the badminton --> world --> tapos the supply --> is few like here in Das --> Ma, dalawa lang. --> Tapos we experience is --> may time na pinapatayan kami --> ng ilaw kasi we play --> at TNC or sa --> town and country. --> So we had that frustration --> sabi nain, why not we build --> our own badminton court? --> So that's the start. --> We start planning --> for a month or two. --> Then we find investor --> so luckily we find --> investor and I invest my money --> as well. So in --> a month we're able --> to pull off --> everything --> to make it --> operational. It's not --> perfect. We had --> a lot of struggles --> but right --> now it's running for 6 months --> and I can say it's doing good --> and serving its purpose. --> yeah --> so again I'm IT in profession --> and that's the story on --> how I build the SSBC --> or the shuttle showdown badminton court. --> How about you sir? --> I'm Perry --> so malapit na siya dito sa --> MCX --> So right now --> I'm still working --> here in Pure Foods. --> Nakilala ko si --> Julian --> because of CCF --> kasi yung --> CCF --> would transfer --> to a new place --> na owned by Julian --> kasi ito --> kabi sa akin ni Pastor Raul --> yung ko owned by Julian --> may meron daw ino offer --> na investment --> so kaya binigin sa --> atin yung number --> ni Julian then I get your number --> from Julian. I see. --> So si --> Ram may isang ino --> is also active in sports --> kaya kita mo naman --> katawan niya --> so --> binagit ko kaya Ram --> kasi I'm also getting --> feedback or insight --> kaya Ram sa --> akin kasi there are both --> business inclined --> na mga --> people like me --> I'm employed --> kasi ako eh so yun --> we seem interested of course --> we still want to hear --> yung mga --> yung masasapihin mo --> yung mga --> yung mga thoughts. Ram --> hi sir --> I'm Ram --> yun sabi na ni --> Medyo active sa sports --> I used to play for --> Mapua ng college --> Sir graduated in Mapua? --> Apo apo --> So --> I graduated --> pero --> nasa creatives kami --> na sa photo and video --> tapos dito lang din ako sa --> Kavite sa Imus naman --> Sabermos naman --> Ah okay --> Lapit lang --> Kailangan kami sa --> Ulam o --> marami ulam --> lagi si Ram --> Yeah --> So yes --> I'm just loading the presentation right now --> just bear with me --> The canvase is really slow --> right now --> Okay it's loaded --> Let me share my screen --> So yun --> Really may passion is --> Again I'm into --> business na din --> So I resigned --> 5 months ago --> Because I want to build --> the business especially here --> in Badminton kasi I see the demand --> I see the --> I see the need --> Can I --> Can you let me share my screen --> Thank you --> Okay --> Sure --> Let me --> share my desktop --> Let me know if you can see my screen --> Yes --> Yeah --> So we also --> create our branding --> SSBC or Shuttle Showdown --> This is our main logo --> So this is the table of contents --> that I will discuss to you --> I hope you will not get bored --> I'll make it fast --> or you can say skip if you --> not interested but --> I will discuss our company --> our brand --> why you invest in --> SSBC and Percus Investor --> and later --> ROI forecast --> I'm not sure if this --> Yeah let's see --> So our company --> So Shuttle --> Showdown is a rising --> trendy sport venue that provides --> high quality badminton courts for --> both amateur and --> professional players in order to --> establish individual with expertise --> in IT --> Since IT nga pa ako --> the one who created --> yung --> booking system --> and now we have POS --> dun sa samin --> tapos ang dream ko pa is --> magkakaroon lang sila ng RFID --> kasi may school management system din pa ako --> So I'm gonna apply the RFID --> function --> So yeah --> So that's my plan to have --> a membership card --> that will launch by January --> so you will have --> a credit in your RFID --> then just gonna top it --> Ang problem na sila solve nun is --> yung sukule --> tapos siyempre yung efficiency kasi --> it's a human problem pa rin na --> misa nakakamali sila ng --> payment or vice versa --> nakakamali ng sukule --> tapos for easy tracking of the --> merchandise and the foods --> and the court rental --> and also our coach is --> certified badminton coach --> so si coach George po yung partner ko --> right now --> he is BWF level 2 --> so he has the title --> as well and he has --> a lot of --> experience and influence --> so --> So our mission --> is to serve the needs of sport --> into shares by establishing sports clubs --> in key cities in Cavite --> throughout the Philippines --> so hindi lang Cavite yung plan namin --> but this is just the starting point --> we want to build more --> badminton court and --> sport facility to --> other place as well --> because I'm saying this to be --> we will be --> a franchisor --> so we will --> we are starting creating --> our book na to be --> a franchisor para --> makapag-franchise yung mga --> gustong gumamit ng brand --> and everything like marketing --> we are collating na all the data --> that we have --> to make a franchise --> yun so hindi pa na ina-update --> so I think --> I'm not sure if this is the most updated but --> this is the founder --> me, Ray Villamar --> and my partner Joe Art Mateo --> and C Jam --> yung aming brand consultant --> so why SSBC? --> it's a badminton court brand --> like no other so SSBC --> was conceptualized by a branding expert --> in using internationally --> recognized best practices --> since Coach Joe Art --> also BWF --> when I say BWF badminton --> world federation --> level 2 na siya --> so an interior design and --> architectural concept of badminton courts --> were crafted by award winning architects --> of today generation --> then it --> infused with technology --> like yung booking system --> tapos magkakarin din ng --> cubing management system --> so no this cutting edge badminton court --> incorporates a booking --> system to enhance accessibility --> and also a home court --> to one out --> of four certified badminton trainer --> coaches in Cavite --> signaling credibility to the place --> so nag-add in ang value si --> Coach Joe Art --> konte lang silang --> certified na trainer --> ng end coaches here --> in Cavite --> so our core --> values so we're customer --> centric so we value customer --> needs --> feedback --> and strive to exceed expectation --> so we really --> on hand to --> ask them what --> they want to see --> that they want in the badminton --> courts so our customer centric --> approach foster innovation --> shape interaction --> and guides decision making --> to enhance the overall experience --> in creating long lasting --> relationship and a supportive community --> also we want to have --> a top of the line experience so we --> prioritize top notch player experience --> with inclusivity --> so our facilities are --> top notch providing top of the line --> court flooring --> we use taraflex and a high quality --> lighting for optimal performance --> lahat po nang pupunta sa --> court namin di say --> uy ang ganda ng ilo niyo uy ang ganda ng --> taraflex and we want to maintain --> that --> then we also have passions --> to sports so our core values includes --> a passion for sport --> teamwork and integrity so --> driving success in athletic --> endeavors and you know creating --> a supportive environment for --> active development also --> our commitment to foster community --> so we --> have --> we want to enhance the badminton experience by fostering --> community through --> tournaments workshop and personalized --> training session --> connecting players and promoting --> growth so we had --> 6 successful tournaments --> na i think more than --> 6 to 8 tournaments --> within 6 months --> so we have like 1 or 2 --> tournament per month --> some other are hosted with us --> especially me and --> other are my friends --> so we're hosting tournament --> and the --> feedback is --> really positive also i want to add --> that we have a burden --> to especially if --> we have the capacity or means --> we want to build a badminton --> court as a donation in --> some --> provinces that really deprive --> of you know --> having a good facility because --> of their location --> so we're --> dreaming that as well --> so our brand --> so ssbc brand --> promise is to bring joy --> and satisfaction for every badminton --> enthusiast in the Philippines --> our tagline is --> we're champion trains and --> dreams take flight --> so our brand personality is we're fun --> jolly --> we're humble we're learner --> inclusive outspoken --> hospitable and profession --> so this is --> our logo mark --> this is the full logo of the --> shuttle showdown --> so as you can see it's --> like a racket --> side by side --> it's the --> logo variation --> then this is --> our key elements --> so here --> the branding just showing --> that all --> the elements --> that we're showing has all --> the meaning like this symbolize --> opening or more opportunities --> in the future --> this illustration badminton racket --> symbolizing the sport ssb --> currently offers --> and arrows portray movement and --> direction in moving forward --> then shuttlecock illustration of --> shuttlecock symbolizing the --> sports in ssbc --> so --> this is the --> creative mood board --> that we are thinking --> though it's not yet implemented --> because we --> prioritize what we need --> in the court but --> this is the dream or the --> concept in the interior design --> so this is the interior design --> so some of them are --> implemented like the --> umpire chair, the blue --> tela --> tapos we have monitor na --> though one but our --> goal is each court will have --> their own monitor especially --> when we implement the --> queuing management --> app which i'm doing in progress --> then a silhouette --> of badminton --> player --> then on the --> side we will have the stalls --> the office --> then the lockers --> then and the --> restroom so this is right now --> currently designed for the size --> of our --> badminton court --> then we will have some messaging --> on each --> on each --> panel in the court --> say a lab ssbc or play with --> then on the exterior design --> though it's not yet implemented --> but the only implemented --> is the ssbc here --> but the silhouette is --> and the painting is not yet implemented --> so why invest in ssbc --> so --> only in ssbc --> we have training camps --> so we have accredited coach --> coach joard --> accredited in badminton world federation --> it's maximized the home court advantage --> training both aspiring --> and professional badminton players --> at shutter showdown --> also we have only in --> the kavite --> only as the ssbc have the --> booking system --> so walang nagugulo --> the convenient of they can pay --> they can book --> prior and they can see --> may available pa na --> court --> so this ensure players --> of the court is serve --> providing them with assurance --> having designated court --> for the game --> and also aesthetic inside the court --> so this is in progress --> but we're making it aesthetic at least --> branded by internationally recognized --> best practices and award winning --> architects --> so the court offer more than just a place to play --> it's a social hub --> to spend time with friends and family --> complete with --> instagram worthy photos --> and community building --> so one of our commitments is reflected in the design --> of our facilities --> aim at fostering a sense of community --> that will transform the badminton courts --> into vibrant community clubs --> so our approach --> is to have a franchise badminton court --> a purse of it's kind in branding --> so the badminton court is --> set to revolutionize the way into --> experience the badminton --> with state of the art facilities --> so yun talaga yung dream namin --> tapos mayroon tayong --> professional coaching --> coach, high quality flooring --> nataraflex and of course --> digital platform --> so using their card, using their mobile --> they can pay --> even the lights --> i can automatically turn --> it up and on kasi --> nakakonek namin sa --> smart switch --> so and i already program --> yung on and off --> kasi i have that also in my home --> so i just need to hook --> that once the --> membership --> is on --> magkakaroon ang our --> investment services is we will have --> a quarterly financial report --> then booking and queuing system --> upgrades --> tapos mga optional --> operational services and also --> we are considering to offer --> a co-ownership --> so with a range of investment --> services we are committed to helping our --> investors to achieve their financial goals --> at all best way possible --> so our team of experienced advisor --> work closely with us --> to understand the target market, unique needs --> and tailor strategies that aligns our goals --> so if you invest --> to us we will have a quarterly financial --> report to maintain transparency --> and keep our investor --> well informed about the company progress --> and future prospects --> also shared --> profit updates, up to date --> accumulated profit reports --> investment status --> reassurance ensure investment --> are well taken --> and expenses --> and overhead --> so you will see how much the expenses --> overhead costs --> transparency and expense and overhead --> costs affecting financial status --> then booking and queuing --> system upgrades --> lagi tayo nag-upgrade every month --> at least we launch --> a new feature --> product --> sa court --> then optional --> operational services --> ito yung isang offer namin --> na okay --> pero it's not yet final --> so just remember numbers --> not yet final --> so we can talk about it --> but this is the idea that --> 50% owned by the SSBC --> then 50% owned by the investor --> then the operation --> handled will be by --> SSBC for investor who --> just invest their money --> the other one --> the other one --> is co-ownership --> it is a flexible share agreement --> depending on investor capabilities --> and demand --> we're just limiting up to four --> co-owners --> group investor --> to build a one --> SSBC branch --> kasi pag marami na --> pag lumagpas na ng four --> five medyo magulo na --> so i think four is the maximum --> in one SSBC --> branch if they want --> to for example --> magtayo sa ibang lugar --> so hanggang four lang ng mga --> co-owners para hindi siya magulo --> location --> namin isyan nga sa --> sa --> Manggahan --> kasi very strategic talaga yun --> kasi gitnas siya --> the place is really --> good gitnas siya --> walang badminton court sa --> Manggahan sa Trece --> sa Amadeo --> so --> yung galing silang malapit lang --> galing Daxma malapit lang --> din so --> yun yung isa namin nakintang --> yung strategic location yung --> Julian tapos nabing --> kalsada pa siya --> so for me it's really good --> so as investor --> ito yung mga perks --> it may change --> but this is the initial --> perks so court rental --> four hours per month --> non-convertible to cash --> perceived value at 800 --> then five percent discount on --> all merchandise --> and food and beverages --> then pre-badminton training --> coaching for up to six sessions per year --> kay coach George --> then automatic upgrade to VIP membership --> so more benefits --> to be advised --> so pag one million --> we just --> add eight hours court rental --> then ten percent on all --> food and beverages --> and merchandise tapos --> may pre-badminton training --> coaching up to twelve sessions per year --> the perceived value is twelve --> thousand pesos then automatic --> upgrade to VIP membership --> i think i can make --> it like platinum we have now --> a platinum membership --> so again this is --> a example --> but it closely to the numbers --> for example --> you invest 500k --> and you want a monthly --> ROI plan --> so you will guaranteed to pay --> 25,000 --> guaranteed --> then --> maganda yung benta talaga --> pwede siyang tumas --> up to seven to ten percent --> per month --> so if you invest --> 800k for example you will receive --> a guaranteed of 40,000 --> per month --> do you have a question --> 24 months --> two year returns --> you just multiplied it to 24 --> yes po --> five percent yes po --> so parang --> kumita yung pera mo --> for example for two year --> 100,000 yung 500k mo --> but --> we are returning it --> monthly --> so 500,000 --> yes so 25 --> for two years --> yun --> yung five percent --> yes po yes yes --> so meaning the five percent --> is already demonstrated --> yes po --> kaya po --> kaya yung --> five percent yes po --> per month --> yes po --> since six --> actually hindi ko pa masabi hundred percent --> because it's just six months yung first three --> months nagpapakilala pa --> lang pero for example --> to be --> transparent ang grow --> sales namin nung October is --> 308,000 --> so 308,000 --> so --> yun nga lang marami expenses like --> marami kaming gustong ipabili kaya --> halos --> wala din --> tapos yun nagpapakapit pa kami --> CCTV --> yun nga yung experience namin --> dyan sa warehouse --> yung isang bad experience is --> hindi sinabi sa amin na --> nagpapadeliver pala sila ng tubig --> or may problem sa water --> could you imagine our bill --> no September --> sa water lang alone --> is 25,000 --> because --> the rate is so high --> because hindi --> hindi enough yung supply --> ni prime water that's why --> they need to deliver sa private --> entity --> in that --> the cost of that is --> higher yung water rate --> but right now --> this November they already resolved --> that hopefully hindi na maulid --> so bumaba na kasi --> nasa prime na ulit --> yes --> yes --> in my existing location correct --> sa Dasmaka --> Vita sa may --> yes sa Dasmaka Vita sa --> malapit sa Vista Mall --> Dasmaka --> or malapit sa --> Walter Mart pinapagitaan --> o siya --> yun po --> tapos yun --> pag quarterly naman --> for example we promised 7% --> so you will receive --> every quarter of --> 150,000 --> again don't --> confuse I'm not promising --> the numbers yet --> it can change --> so still we need the contract --> whatever we promised in the contract --> we will give it --> and we also have a business --> coach and consultant --> so --> we're yet to finalize it --> so we're --> yeah we're thinking --> actually we're thinking kasi na --> bumili talaga --> ng lupa tapos patayuan --> siya kasi it's more advisable --> like we're paying --> 100 rent alone --> kasi tas ang mahal pa ng kusa --> when I say kusa --> it's like a homeowner association --> use --> homeowner association use is 38,000 --> then our rent is --> 126,000 --> so para mga --> 160,000 just for rent alone --> sabi ko nga --> if I can pay 160,000 --> right 180,000 per month --> why not I make it alone --> or gather investor --> tapos we build our own --> diba --> we build our own kasi may demand --> naman po talaga and --> hindi maubos ang mga player kasi --> yung mga bata lalaki --> tapos may mga bata pa --> lalaki --> tapos it's already --> kung baga we already have the --> network --> actually we're planning --> actually we're looking for another --> another I'm looking for a land --> that we can build talaga --> wala pa --> but it's really --> parang high risk kasi --> parang papatak na ang rent --> sa kanya 300,000 to 400,000 --> per month --> yeah it's really --> risky --> if you can pay 400,000 --> or 350,000 --> per 1,000 --> square meter to --> 1,400 --> diba why not strategize --> to get --> investment on some --> people now willing to invest --> then build --> kung baga we buy --> a land na everything --> is owner or --> kasi may investor at least we have --> the physical or the --> we have the property --> na we can liquidate --> and you know the property the value --> is along --> sa kahabaan ng panahon tumataas --> yes --> and yeah yeah --> so right now --> based on experience in 6 months --> it's good naman kasi --> wala naman kaming utang and --> nababayaran naman lahat ng --> but I see it --> the renting --> is not a long term for me --> I really want to build --> you know saka --> yung mas maganda yung mas malaki --> malagay natin na ibang --> facilities --> yung itong numbers na to --> you say it may change but --> close to this --> number parin --> hindi naman --> hindi naman --> kasi kung baga we want --> we want to return to the --> investment to investor --> especially and --> kung magako I'm willing to put my money --> din eh kasi I see really the --> demand that tapos kung --> nilagin pa siya ng system --> tapos yung tao --> dalawa lang or tatlo --> it will run --> and the maintenance --> hindi naman din ganun kalaki --> yun ilaw lang tubig --> so --> I'm still positive with --> the badminton business --> especially malagin pa natin siya --> kanyari we can --> find a place na --> na ibang --> sports --> like yung pickle ball --> na sumisikat na --> pickle ball --> basketball --> so yeah --> actually we have that plan --> just need --> kasi misang busy din ako --> I have my own company --> so but I'm really positive --> on this one --> just to say --> so this is the sample --> of the yearly ROI --> plan if we put 8% --> so you can have --> 1.4 million in 2 years --> time because --> based on my later --> calculation before --> we can be ROI in 2-3 years --> 2 years is --> the most --> positive --> in 3 years is like --> okay so if there's some issue --> something like that --> back --> yung 720,000 --> I'm trying to compute --> 500,000 times --> 8% --> that's 40,000 --> so yung 40,000 --> I'm --> multiply it to --> how did you get --> 720,000 --> how did you get 720,000 --> kasi kung --> annual kasi to --> 100 times 8 --> that's 40,000 --> try to multiply --> that's --> 480,000 --> so ini bumabuhayin 720,000 --> oh nga --> yeah I think --> mali po yung calculation dito --> kasi if you apply that --> it will be 44% --> right right --> yes po I will --> okay --> yeah sorry for that --> confusion --> so I think I'm almost done --> so as franchise --> kasi --> so yun kasi inisip namin na --> maggawa talaga na maging franchisor --> tapos kanyari yung ibang lugar --> gusto yung mag franchise --> pag naboo na namin yung marketing --> yung everything from start --> tapos how it operate --> tapos kagawan --> ng book tapos mag --> paparegister --> pwede na tayong maging franchisor ng --> sports --> I think --> hindi po --> may two ways --> siyempre we cannot manage naman --> yung kumagayaman physical --> at siya it's not scalable --> so they will the one who manage --> that's why they will have the --> book ang role nalang --> po namin ni Jordan is parang consultancy --> or visitation --> or meeting yun nga consultant --> kung anong gagawin --> like we have a lot of programs --> like yung clinic, camps --> tapos yung mga promotion --> yeah --> yes po --> kasi we have our branding --> we sing the branding --> at least --> here in national in the --> Philippines --> thank you po --> thank you for listening --> this question --> sir Ray salamat po sa --> presentation natin --> mayroon lang ako ika clarify --> yes po sir --> yung investment natin --> saan gagamitin naman --> that's for a different --> brand ba sa lalabas --> same brand --> SSBC --> tapos --> actually you're --> the second one na nag --> presenta namin --> just a few friends --> and --> the concept is --> for --> another branch --> ng SSBC for example --> sa emus --> or sabako or ganoon --> so maghanap tayo, magtatayo --> tayo kasi may supply na kami --> ng taraflex and ilaw and everything --> so we can --> kami sinetap lang namin ng a month --> or plus 10 days --> mga 40 days --> set a finding --> supplier but it will be more --> mas may cleaner siya kasi --> we have the supplier na nung una naghanap kami ng --> supplier, nag-aantay --> but now we have a reliable supplier --> na magkakabit ng taraflex --> magre-renovate, mag-aayos --> gano --> so i'm looking for investor --> for the next --> court na ita tayo --> so vali wala pa tayong definition --> for this --> unfortunately wala pa po --> so that means --> wala pa rin yung parang --> nagiging --> monthly rental sa warehouse --> for example or expenses --> wala pa --> kung yung kay Julian --> kinumpit ko --> nagkumpit na ako noon yung --> kay Julian --> kasong mahal talaga --> pero maganda din yung kita --> if gagawin siyang 24 hours --> it's really good --> pwede naman --> kasa lang pag naisip ko na --> yung sustainability --> na parang kailangan --> mas sustain mo siya ng a year --> kasi magpapakilala ka --> kukuha ka ng mga --> kontak ka ng mga schools --> sa mga companies --> so it takes a year for them --> to gather parang gano --> yeah i offer Julian --> as investor as well --> sabi ko nga Julian ilibri mo muna --> ako ng 6 months tapos yun ang investment mo --> tapos --> ano --> may investment value ka na --> magbabayad na kami ng rent --> per month tapos --> may shares na siya --> um --> kakosapin niya daw yung nasa taas --> kasi ano lang siya --> nag-joke pa ngayon eh --> tag-sunod lang daw siya --> sabi niya --> yeah yung father niya --> pero nag-isip din kami yung --> ram na ano --> kasi sa SSBC --> dito ngayon yung current now running --> nag-isip din kami --> magpasok ng isa ang ganda --> lang ang investor --> ang purpose naman nung is --> um yun mag-i-improve pa --> um may --> konti na lang naman more on internal --> tapos --> siyempre ang ano rin is --> kunyari kagaya mo na for example businessman --> you can contribute --> din how to --> ano pa how to --> make it profitable pa --> yun --> but it's not yet --> un-final --> kasi kami dalo ni Joe Archung nag-di-decide --> which is my partner --> yes pwede --> tapos we can look siguro yung --> financial statement --> or annual report siguro --> or even monthly reports kasi 6 months pa lang yung company --> 6 months pa lang pwede naman --> we can do that for sure --> tapos kung ports natin --> dun is how many? --> 4 --> 250 pesos per hour per court --> yes --> 540 --> so wala na siyang room for --> parang --> palakihan --> wala na no no --> sagad na siya --> sagad na siya --> misan po bisita po kayo --> let's meet there --> tapo --> so yun yung papunta --> kasi kung --> galing ako ng governor strike --> so --> kaliwa --> kakaliwa ka pa vista mall --> pag-itas mo ng vista mall yun ay po yung --> samin --> kakanan ka dun sa may mga warehouse ng JNT --> copy project --> yes po copy project correct --> yun yung paway yun --> yes po so dire-direcho --> yes po --> dun sa left mo may --> BASIO --> tapos sa kanan --> may malawak na --> kalasada --> which is yun yung dana ng mga --> truck JNT dun kami --> papasoknan --> yes correct --> anggang 12 midnight po --> ATM to 12 midnight --> so --> ano pa yung --> i-enhance po sa kanya --> kung maximize na yung --> area niya --> after nung --> CCTV mga monitors --> tapos yung --> yung mga internal like yung --> silhouette saka yung external --> yun po yung mga yung mga nandon sa --> plan yun po yung gusto namin i-implement --> yes po yun --> how you say that --> yung mga customers would still increase --> it will add a value --> especially kung each court will have --> monitor and it have a system --> of for example like queuing --> kasi one of the biggest --> chunk na --> nag-rent every night is --> yung mga queue master --> which they do their queuing --> so tayo lang yung may ganung --> system --> na may queuing system na --> may kita yung score o may kita yung --> yung kasunod na maglalaro --> max ka ng ano yun --> sa ba na talagang magiging --> kasi otherwise --> kung --> so i think --> tamang possible you might --> proceed --> yung financial statements --> sure --> may questions pa ako --> doon sa investment --> part ulit --> for example hindi kami pumasa doon sa --> existing courts nyo --> magkana yung biniim yung --> i --> buoin para sa bagong --> SSBC branch --> kung mag rent --> tapos mga 1,000 square --> meter mga 6 million --> pero kung --> pero kung bibili tayo --> for example yung price --> ng warehouse --> is 10 million --> tapos pwede namang hulog --> yan yung for example yung lupa --> siguro mga 15 --> million 15 to 20 million --> the good thing with that naman is we own the lot --> we have our own property --> tapos siyempre pag --> tinayayan pa siya ng warehouse type --> na matas yung ceiling --> anything --> we put there --> it's the investor property --> whoever invests --> with them --> up to --> up to 4 investor lang --> so medyo malaking --> investment siya lalas --> hindi yung parang 500,000 --> for example yung isang --> kakailalan namin --> kanyar iririkoy natin --> 2 million --> siya lang kausap natin --> marami siyang may group siya --> na investor din --> basta siya lang yung representative --> or kausap --> kasi pag maraming investor --> based din sa --> ano namin is magulo --> otherwise you can give me more positive --> impact --> pag maraming investor --> but for me I discourage --> a lot of investor --> maybe --> maybe --> we can strategize like --> we can build a shares --> then 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 something like that --> we do it like share --> hindi ko pa nakakalculate --> yeah --> ganun so magkakaroon tayo kasi --> for example ako invest 2 million --> invest 4 million so mas --> malaki yung shares mo --> tapos siyempre --> may voting power ka din --> when it comes to decision --> I'm planning to build --> a corporation --> wherein may board --> tayo and you're one of --> the investor para pagdating --> ng meeting okay so here's the --> plan so --> may voting power tayo --> tempre the more you --> kumiko yung pinakamataas mas matas --> yung voting power --> so hindi sabihin siyo --> pwede pa kami pumili ng shares --> 1% for example --> siguro yun --> maybe we can ask yung preso per --> share --> kung magkana yung --> valuation ng company --> yeah --> we'll make it legal lang --> kasi I cannot ask --> 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 pa --> hindi pa po --> but yes po --> next year pa po January --> partnership or --> soul --> soul po it's under --> my name --> uh --> yes po --> correct --> secondary --> license --> yes po --> correct --> timeline --> middle of next year --> or --> accounting na --> tapos maglalakad na --> ready to talk na mga jo --> um yung painay --> kasi for us --> to expand --> and magawa yung mga --> plans --> we think talaga na we need --> help of other people's money --> or investor --> na passionate din sa sports --> and we just believe --> in our passion and --> in line with our core --> values --> yes --> yeah we're --> considering it --> but since nga --> apat lang --> yeah --> actually mayon ramin gusto ko lumipat sa --> amin kung at least five --> or five to eight --> they're really ready to move --> but since it's four kulang na kulang --> talaga --> parking space --> very ample parking space --> marami tayong parking space --> parking space --> hindi kaya --> hindi naman --> well it's outside --> pangalawa --> it's really poor parking --> by the way may church is in Jael --> jesus is lord church po --> kay brother eddie po yes po --> eddie po na yes po --> Still active in your church. Yes po. In the men's ministry po. So I have five minutes. Yes, Brother Perry. So, for example, --> One million, let's say. Uh huh. Uh let's say five percent na yung sinasabi niyo. Yung five percent na yun would stop at um two years or drederecho siya? --> Kung hindi mo siya kukunin, drederecho siya. Kumaga pwedeng interes lang ganun. So um or pwedeng kung per month or every quarter. So depende sa atin, depende sa mapag-uusapan. --> 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. --> 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 it. Thank you so much. Um if you have a question, just message me, okay? Thank you Ram. Nice to meet you all as well. Thank you. Bye bye. --> Daddy, can you please connect to my internet? --> Oh, na-format mo na? How do you do it? I don't do it. Nakakakit ka ng, ano? MNRT, hindi nga? Marik mo namin, hindi mo na siya nilating. Ako na dito.