Transcript for:
Oracle SQL and PL/SQL Overview

[Music] welcome to Oracle SQL and plsql session and in this series this we are going to learn Oracle plsql so if you ask for Oracle plsql so do we need to have knowledge on SQL yes if you have SQL knowledge then working with plsql is very very simple then what is the difference between SQL and plsql so this is the agenda for today's session let me explain what is the difference between SQL and plsql but this is the agenda for today's session so we will see the introduction about plsql and what is the use of plsql and architecture of the plsql okay the architecture of the plsql and explain the benefit of plsql so what are the benefits we do have over to SQL so what is the benefits and benefits of the subprograms so that I will explain okay the subprograms I will explain here and you will get clear idea so what is the difference all this okay and if you take so what is plsql first of all so plsql is nothing but procedural language extension to SQL so that's the meaning okay procedural language extension to SQL so what is procedural language say for an example SQL we are writing so if you see this SQL so plsql is nothing but it's a procedural language for this SQL okay so procedural language extension to SQL normally how we will write SQL so we'll write SQL statement right so whether it we will write select statement or we will write DML statement ddl statement all this and we are going to write this in development environment after that we are going to deploy into the QA R higher environments like production all this so you are going to prepare SQL statement so that we are going to deploy it so even if you if you're going for any reportings reporting okay you have to take some reporting from the data warehous or database and you have to send it to some customers they have to run some SQL queries and it should be executed on the database and that whatever the query resultant we are getting so that should be stored as a CSV file or something then you are sending into another customer so for all this right so you'll be writing only SQL statement so procedur statement means so you're informing the article database that okay do this check the condition okay you check this condition if statement you check the condition if condition okay so you are checking some conditions and you are informing article that if the condition is satisfied you execute this particular SQL statement okay the condition is not satisfied so you can execute the sql2 statement here you can inform right you can procedurally you can mention that okay do this do this all this looping statement so you can write some Dynamic SQL Dynamic table Creations your Dynamic column creation all this you can write by using this procedural language so procedural language extension to SQL that is the plsql so plsql is an oracle propriatary language so if you take Oracle database so this is the plsql is the propriatary language so for an example if you take SQL Server then we will call it as it's a tsql transact SQL sometimes they will say like if you're going for SQL Server database then you'll be using tsql that is nothing but transact xql so if you go to some other databases like Terra dat so in Terra dat you'll be using the bch script so bch is nothing but the same way you'll be writing it is similar to our plsql okay so based on the different databases you'll be using so if it is Oracle database it will be called it as plsql it's Preparatory language for the arle and how the plsql statement will be executed so if you see this this particular statements whenever you are giving any plsql statement to this plsql block so you are sitting here user is sitting here so we are providing some plsql statement to this particular plsq block then this is what the database server completely a database server so inside the database server you'll be having a plsql engine this plsql engine receives the plsql block from the user and inside we have one plsql blocks so this plsql block separate the procedural language procedural code so that into one particular Pipeline and SQL statement into one pipeline so procedural code will be sent to procedural execute procedural statement executor okay to this block it will send only procedures and if it has any SQL statement in the plsql if pass any SQL statement the plsql segregate that SQL statement to this SQL statement executor and then it will execute it and it it it returns result to the plsql engine so then plsql engine produce a result so this is what it will happen Okay so if you're writing only SQL statement yes SQL statement if you're not writing any any plsql code then this SQL statement execut only it will be executed plsql code whenever you're executing and it will be executed by two different executor so one is procedural statement executor the other one is SQL statement executor say for an example if you consider this particular example this particular example so you can see here one example if you take if a is greater than b then so some condition you are testing then you want to execute some SQL statement okay some condition you are testing even you can put some other conditions okay so banking domain if you take a lot of conditions will be there right if the condition satisfies you execute this particular statement if the condition is not satisfied you execute this particular statement if you're going to execute this particular code by using plsql engine this code you are going to send then plsql block segregate this code into two different codes one is procedural statements and SQL statement so what is the procedural statement here right so this condition you checking the condition here if a greater than b then okay that will be executed by this procedural statement executor okay so you check the conditions yes if a is greater than B I'm giving a value is 10 is greater than five yes the condition satisfies then this particular plsql this it will produce okay yes then this particular PLS scale engine inform this SQL statement executed that so you execute this SQL statement here so then this will be executed an example this condition is false then else condition this procedural statement executor will check for the else conditions then it will give you okay this is else condition so if it is else if execute this statement sql2 then sql2 will be Pro will be sent to the this particular block it will execute and it will give you the result and this plsl engine will give you the result so this is what it operates okay clear I hope you are clear so this is what the plsql engine will be executed so that's what that this is called context switching so what is context switching like switching is happening between procedural engine so procedural statement executor and this SQL statement executor so sometimes it will take some performance issue I will tell you later if you making this so without context switching without switching this SQL and plsql we can do some modifications so that I will explain later okay so whenever we are going for any looping statements very big looping statements then it will take lot of contact switching will be happening okay so you're going for very big looping statements and every time this will be ex this will be tested and then it will be given to here again it will be processed here so it will be happening here and there so then it'll be taking lot of time so how to avoid that context switching that we will see it later okay so what is this switching happen so whenever you are giving any procedural statement to plsql engine it segregates two different statements so I hope you are clear then what is the benefit of plsql so why do we need plsql actually so in plsql you can go for procedural statements right so do this and then if you are not if you're not going to do this then go for the other statement so that procedural you can execute the one more thing is improved performance if you're going for any PSL statements performance will improved see if you are getting any statement from the applications so you are getting some SQL statements some select statement or insert statement update statement any statement you are getting from applications if it is simply SQL then we have to write multiple multiple SQL quer is happening to the database okay the database is happening but if you take the Oracle database plsql if you're writing so you can have application here and from the applications you can write lot of plsql code here so if then this particular SQL statement else SQL statement then if you're starting if then you have to put the endf then after that if you want to SQL yes you can put some SQL before also you can put some SQL so that you can write it here some procedurally you can write so for that it will be improved some performance will be improved so in plsql everything you can write different different modules modules by modules you can write that's a one advantage main advantage so sub programs so you can write lot of subprograms and then you can put all the subprograms into one combined package so you can write so that we can do it on the plsql code so modularize you can do it so whenever you are going to write plsql so how can I write plsql okay so if you take plsql very first time I want to write any plsql code it's very simple so there is two types of plsql are there so one is anonymous block other one is named block so what is that so two blocks in plsql two blocks in plsql so what is the two P blocks so you can go for so the first one is anonymous block so that means it will not have any name names okay so it will not have any names so just it will it will not have any names so you can write Anonymous block and you'll be having a named block so named block we are going to give name at that particular block we are going to give the name so there are different named blocks are available so you can see the procedure is one of the named block so we are going to store the procedure so inside the database right as an object but Anonymous block we are not going to store it okay Anonymous block you have to execute it so every time we have to execute so that will not be stored as the object in the database but this named block stored as an object okay so stored as an object in database in Oracle database always so you have how many named blocks are there you have the procedure you have the functions right then you have the packages okay so package so what is mean by package see package is nothing but you are writing procedure you are writing functions lot of procedures lot of functions you are writing and if I want to combine all this into one package yes you can put all this into one package so you are going for one project in the project so many people are writing the codes so you are writing one code another person is writing one module another person is writing one module but we have to put all this into one single package so that we can combine it the the package okay then we have the triggers triggers will automatically get fired so whenever we want to execute okay we are checking some conditions if the condition matches then we want to do some we want to execute some trigger right so you are going to are going to drop some amount from a back account then suddenly you'll be getting the mail alert and message so how it is happening so the back end this is one kind of trigger right so we have the triggers same way we have the collections yes so we will see these are all the named blocks but other concept we'll see one by one so how first we will go for anonymous block okay so first we'll go for anonymous block so what is anonymous block anonymous block means it will not have any naming standard okay it will not have any names in the code so you can have here so first we will see Anonymous block you sessions we will see that Anonymous block then we'll go for the named block okay first we have to understand what is anonymous block and how can I execute Anonymous block see whenever we are going to write any plsql code it's very simple so first you have to have you have to declare the statement okay you have to declare it so so whatever we are going to use variable so that we have to declare it and so all the variable declaration we are going to do it here okay so whatever the variable we are going to use so those variable you have to declare it yes you can have lot of variables so if you know already a programming then this plsql is very very easy okay so programming plus SQL that's it so you know you know if you know python code if you know C++ code or C code Java code then the P SQL is very very easy the programming plus SQL so here it's like a declare like an variable declaration you can go for any variable declaration so you can do it right so you will be doing some variable declaration in this declaration part then we can write begin and end so always you need to have begin and end begin and end OB you need to have definitely begin and end then normally you'll write this particular slash slash represents end of the code okay so if you see here is clearly showing that begin has been started here then you are ending here right so you can see like this so begin and end automatically it is making this way right so begin and end so in the inside the begin and end you can have the executable statement so whatever the executable statement or assignment you are going to do it all the assignment executable statement you can write it here executable statements so that we can write it here so body of the statement okay so what we are going to write SQL statement some declaration declaration we will do it here but assignment I I want to do it so those assignment I can do here variable assignment or something value I want to assign you can do it in the executable statement but you can put some so you are you want to do some exceptions okay some exceptions you want to handle it yes you can handle the exception in between the begin and end you can put exceptions whenever you have exceptions yes you can put exception and you can write exception handling code here so inside you can put exception handling code so exception handling code handling code so if you this is the blocks of plsq so if I want to write a plsql code so this is the way we have to write clear so what is that first you have to start with declare begin and end so whenever we are going to have a plsql statement it is very simple start with begin so whenever you are starting with begin don't forget to use end so always use this way only you have to write this way only so whenever you're using begin and do end then inside you write start writing the code inside you start writing the code whatever the code you want you can write any SQL or anything like executable statement you can write but begin whenever we are making input end here okay don't forget here why because so if you are going to write multiple subprograms then you will forget this end then you may not know where you have started where you have ended so lot of end statement you are going to write if statement or case statement you you can write so those statement also will have the end okay so that's why whenever you are starting with begin put end okay so in this in this basic plsql block the anonymous blog so this particular declaration part so declaration part is optional okay if you have you have variable then we can declare it if you do not have any variable no need to do the Declaration this declaration part is optional okay in any Anonymous block the Declaration part is optional but this begin and end the body of the statement is right and also this exception exceptional handling also is an optional okay so except handling is an optional one so you may write or you may not write the exceptional handling but begin and end right yes definitely you need to have you need to write the this is mandatory one okay this is mandate so that's what we will write the SQL statement clear on this the anonymous block so this is what you have to write okay I hope you are clear if you have any questions so please raise through the chat I will respond this is very basic okay you will not get any doubts yes so this is very basic psql Anonymous block with named block yes the same way we will write but slight differences will be there that we will see it later I hope you are clear on this so now I will open the SQL Developer so you may ask question why you have closed the SQL Developer so why because I want to show you how to execute plsql statement that's it so you can open whatever the schema you want and in plsql normally so you have uh I hope you have some knowledge on cc++ right so you'll be writing some print of and scanof so what is a scanof scan of you you want to scan the variable right and what is the Sprint printf you'll be printing that particular value whatever see are writing big code and sometimes you want to uh you want to find out some logs logs okay up to here this has been executed so always you'll be making that particular some commented out and you'll be making some print right yes this has been executed this has been executed all this we'll be making so that we can make it here by using one package is called dbms output okay so what is the package one package it's a package from article database I will while explaining the packages I will explain what is this dbms output so if you make dbms output dot so you'll be you'll be having lot of methods so these are all some functions they should have already written but this one I will tell you so what is the dbms output so whatever we are going to write any plsl code you want to see the result here itself okay in real time how will you see the result the result will be ex select statement will be executed you'll be making in the log right you'll be logging it but here I want to know which one you want to get it so everything you want to put then you can use this dbms output dot one method called put underscore line so this will be this will be a package this will be a method name okay method is nothing but function name then inside whatever you are making so that will be printed here okay displayed here how can I display so whenever you want to write any plsql statement okay it's very simple statement if you want to write so as I told whenever you are writing begin and end so start with begin and end this is very simple plsql code very simple plsql code okay I'm not doing any here uh it needs some data input I'm writing something like input okay so here I'm going to write you see this I'm going to execute this one this is very basic psql code you can see here plsql procedure successfully completed yes even if you're going to execute Anonymous block then definitely you be you'll be getting this particular result okay it has been executed but how can I find the value okay so I got one question if the stored procedure fails where can we put the condition in the block on failure yes so normally we will put the commments and we'll put this particular dbms output and we will make so this particular statement in log so log will be captured okay I will I will come to that okay so can we keep it in after the end slash signifies the end of the block so why we mention this comma see whenever your psql engine executes the code right so it will be executed up to here up to here then what is this one this states that this particular ended that's it this particular end statement has been ended here but you may have lot of end statement right inside so that's why we are making here to mention that LSL Chang in that okay up to here it will be executed and you can see here every statement SQL statement to terminate that particular statement we are using this semicolon right so Oracle assume that okay this is very simple plsql statement I'm going to execute it so it will be executed and where can I find the result see in order to find the result you can go with two way one is you can use view so go to view I want to see the view what is the package name it is a dbms output right so you can go to the dbms output here and the dbms output will be opened here right so in below you can see the dbms output and here you want to connect it which schema it is HR schema click on Plus HR click on okay then it will be connected so I'm going to execute this particular statement one more time here then this dbms output whatever we are getting that will be displayed here okay it is showing that plsql procedure successfully completed and it is displaying the welcome so you can you can execute multiple times yes every time it will display the statement here right yes this is very simple and this is dbms output okay so I'm going to close this this is one way of executing it and you can put one more so if you want to display here itself you can set server output see you want to sometime sometimes you want to make any okay server output on so if you are going to make this way so one time you can execute that's it we are informing Oracle that server output we are making on here so whatever the server output we are getting so that will be displayed here okay so one time no issues just execute some Vari variable declaration we will do it right if for an example in databases we'll make set this particular database that means if schema lot of schemas are available lot of databases are available you want to execute without mentioning the schema name you'll be making that set variable so you can mention that okay the set the server out put on is nothing but here itself you will be you are set you are informing server that okay so you can show the output here itself so only one time you can make it and multiple times they're executing it it will show you the result okay so here itself it will show you server output on so no need to execute multiple times here you can remove it after that so only one time if you're executing then throughout the session it will be stored so here you are getting this output so I hope you are clear see here within codes whatever you are mentioning so that will be printed here okay you whatever you are mentioning here so that will be printed here welcome to plsql session okay plsql session just I'm mentioning here then if I'm going to execute so you'll be getting this particular output here at you can remove and then you can sh see this is very simple plsql code that's it very simple plsql code variable declaration is optional and this exception is optional but this is mandatory one right okay I want to add two numbers you assume that I want to add two numbers and I have to put the result okay it has many if I want to add two number numbers or multiply two numbers we have to get the numbers and everything right uh dbms output yes it's a keyword dbms underscore output is the package inside the package lot of methods will be there okay in the method they'll be mentioning article already mentioned that whatever as a user going to give within the codes so that you can go and display here that's what they have already mentioned okay so what is that what is the package name dbms uncore output is the package name is this is the package name okay in dbms output dot dot is nothing but if you put the dot so inside the package how many methods are available all the methods will display right see dbms output disable do enable G line get line lines so lot of lines are there right so you can put put line so this is one of the method so in the method whatever you are going to give within the codes that will be displayed that's it okay and variable you can display I hope you're clear okay I want to add two numbers so if I want to add two numbers say for an example so here I have one number so I will take one number here so five is one number and six is another number you assume that two numbers I want to add two numbers so one number is five another number is six we know it's a hardcoded value right so I want to add this no number and I need to get the result like here it's 11 this 11 should be displayed how can I do it right first one number okay I want to add two numbers so how will you write the code so normally you have to write begin end and within the begin end you have to write the addition of two numbers right so what is the two number we are going to get the two numbers we have to first you have to decare it right so without declaration we cannot do it so first you have to go for declare so what is the number first number it is similar to see declare you will not have any semicolon declare you will not have any semicolon beginning you will not have any semicolon only end will have the semicolon clear so declare begin and end this is the complete Anonymous block so inside the Declaration so always you have to do inside the Declaration you have to do the variable declaration so what is the variable declaration I want to get two numbers I will go for one number number one I can take N1 so whatever the variable you want to give you can give the naming convention okay I'm I going I can make it like number one also okay so number one is nothing but it is a variable name okay variable and what is the data type so this number one is data type is number data type so number of 10 digit yes you can go for number one number of 10 digit see will it take decimal values no it will not take decimal values I can go for number one and number two here and I can I have declared variable here and I can go with dbms output dbms underscore output dot put underscore line this is very simple one by one we'll go okay so straight away I should not go with complex scenario now we will go Basics then only you will get clear idea so what is this dbms uncore output.put uncore line I'm going to add two numbers right the addition of two numbers the addition of two numbers are numbers are that's it I want to display the value here right so how can I display see I want to display the numbers and I I can put within bracket within bracket I can write okay number one so here I can write number one plus number two am I correct number two but here if you see this I'm getting some error what is error see this is some hard Cod value right this is some operations we are doing some operation here so that we are make it in dbms output so if you want to make it you have to do concatenation so this is some some word some hardcoded word with that we are to put some variable addition right so that we are making then why it is showing like a red color Mark here so means we haven't closed that statement so that's why you are showing but see this so what what what is missed here which one is missed here just I have explained number one and number two and I'm just making that number one plus number two so what is missed here in this Anonymous block if I'm going to execute which one is missed okay sorry here output actually so that's error we are getting invalid identifier means some T I have missed here okay so previously I'm going to execute this okay I'm going to execute this so what is the error we are getting see here put line must be declared okay see remember it's a put underscore line I forgot that's why we are getting line must be declared yeah definitely you may also get this kind of issues so make sure that you are have to make very clearly it's showing like plsql procedure successfully completed the addition of two numbers are it is not displaying anything why why because we have to put values right we haven't provided any values the exception is exception handling is man uh it is a optional one you may write you may not write exception handling see when we will go for exception handling for an example you are you are making that two numbers number one number two number two is zero but you are going for number one divided by number two is exception right so zero divide error so you'll be getting that that is called exception if you want to handle it yes you can handle it but since I haven't initialized the value so we are not getting any value right so that we have to initialize the value where we have to initialize the value I have to initialize the value inside the blocks even in the Declaration part also you can put but you can initialize here also so so inside I'm just initializing it anywhere you can initialize I will tell you how to initialize but see here so normally this is what we will initialize right see in plsql in plsql if you want to assign a value you have to use assignment operator in plsql the assignment operator will be Po and equal okay if I want to assign a value this is called assignments okay assignment operator is always you have to use colon equal the colon equal only you have to assign the value directly if you're making equal so that cannot be done so what is that you have to make colon equal then only the value will be assigned yes you can go for number two you can assign it here number two colon equal you can put five here yes the value will be assigned here the value has been assigned you're taking number one and number two and you can make that value what is the numbers you'll be getting the addition of two numbers are this way if you're going to execute now you'll be getting the addition of two numbers are 15 but you are not getting one space here if you want one space yes you can put the space here so inste of making addition of two numbers if I want to make addition of 10 and five that way I have to make right instead of making the addition of two numbers I want to make here the assign the addition of addition of within codes right I can put again the this way the addition of number one right you can put the value here number one and can put and here yes the addition of you can you can make here addition of 10 and five right so 10 and five here you can put not within codes within Cotes is nothing but hardcoded value numbers are right how to put this way and you can use numbers are right so if you're going to execute this way see here you're going to execute this way it will show you the addition of 10 and 10 okay why it is 10 and 10 it is showing it should be number two here s you have just copied right that's why see this is what you have to correct it so you can execute and then it will show you the addition of 10 and five numbers are 15 whatever the way you want to make it yes you can make the display you can make the display okay so can we declare the value inside the Declaration part itself can we make it here can we make it here no in this way we cannot make it and if you see this you can the Declaration part itself you can put colon equal to 20 colon equal to 10 this way we can put okay so if you see this first we are declaring so we have declaring the variable variable name and data type and we are making 20 and 10 and we are executing it it will give you 30 right see this is what but you have to make assignment operator this is a hardcoded value right the addition of that is hardcoded value correct this is a variable can I use variable inside the codes what will happen if I use variable inside the codes instead of displaying the variable here instead of mentioning the actual value here it will display number one it see here right if you add this way codes that is why I'm giving it should be displayed whatever the value I'm getting in the runtime right see today I'm mentioning here but how the Real Time Project the Real Time Project this value will be run time it may get today's date yes whatever the job is today's running so that date you might be getting it execution date right and previous date so all this are hard code it's not hardcoded value it is a variable value it's a dynamic value so that we cannot make it here the number one that is value so here and again it is a hardcoded value number two is variable and here numbers are so that is again hard quod value then we are we have to put the concatenation then you have to put number one plus number the addition of 20 and 10 are 30 that's what you have to get right and I have to get the today's date so we're going to make this is a plsql job you are going to run it for an example you want to process all the data for a particular day and you'll be getting the today's date right today's date execution run ID all this you have to get it right run ID is a sequences it it can be generated so that you'll be getting it correct so that's not the hardcoded value this is not the hardcoded value this is a variable value okay so you can mention this way to get the values okay I'm going with something like 20.5 year and here 10.5 so what could be the value 10 20.5 10.5 the numbers we need to get so what should be the value I need to get the total value any guesses I'm going to execute this one the addition of so what is the value I will get it is it 30 or 31 31 but error 30 okay so it's a 3 2 why it is 32 21 and 11 is taking right so if you're mentioning this way it is making the rounding off okay so if you're making this way it is taking rounding off so if you do not want to make round off so you have to mention the decimal point here you have to mention the decimal point here yes I mention this way and you have to make this way here okay so 30 31 so if you forget to mention this decimal point then value itself it will make it like a rounding off clear so do very carefully see you may have this kind of error in realtime project you may think that okay it will give you if you're sometimes we will make it this way whole number and you may get realtime project you may get decimal point and that time it will make round off the aggregation the reconsideration process will fail okay so they will do reconcilation right so for an example you getting the data from bank and you're getting some deposit amount from the bank that they'll go for end of the day reconciliation so they making the aggregation value then whenever you are doing the development kind of work in this way then it will fail so you have to make the Declaration part very correctly in declaration block the number 1 equal to 20 what if if I assign a new value in this begin block reconcile means they are making the see for an example you are having some business you are having some business you are selling 10 products today okay 10 products today 10 products today okay you assume that 10 products you are selling so what is reconcilation 10 products you are selling each products the the bank the bank through bank you are doing all the transactions each product selling they will take some Commission you assume that they will take some commission so you have you have you have done some transactions the customers done some transaction for 1,000 rupees you assume that okay $1,000 out of that $10 is commission so this $10 the bank will take it okay at the end of the day they will put you in the bank so what is the amount they will put the bank so 1,000 minus 10 so 9 9 right they will put this amount in your bank account am I correct see at the end of the day you have to do the reconciliation work so whatever the amount you got it in the bank it's one example I'm saying it is a bank deposited amount in your bank it is why because you have the business uh you have used your bank account and at the end of the day they will take some you take Amazon okay you take Amazon you are selling products to through Amazon Amazon is taking some amount and then at the end of the day they will put deposit amount they will put you right and they will be sending the bank is sending one statement bank statement shows that 990 but whatever the actual value you are getting is you are not correctly are are making that consolidation when it is showing like 980 or 985 okay this is the difference right so if you do the end of the day day balance check everything $5 is getting something somewhere it is missing so that is this is called reconciliation the reconciliation process will run see lot of if you take IRCTC or whatever it may be right so they will stop the even Lac or any process if you take they will stop the process in the night time right so 12 to 12:15 so 12 to 12:15 or 11:45 to 12 so they will do it for some some break some 15 minutes break so why they are doing it this one so they will have they will not have any transaction between this timing right so why they are doing it so that is for mostly for the reconciliation purpose okay lot of purpose is there so one is for reconciliation purpose so what is the reconcilation purpose see they will they will start the business by 15 so 12:15 only so they need to have some break right from from 12:15 to next day 114 sorry 11 59 59 so this this second I will they will go for so from here to here whatever the transaction happen so that they will match it it's what reconciliation why because continuously they have the business right they cannot do the reconcilation it will have all continuous data will be flowing so they cannot stop it on on uh one particular place for the reconciliation purpose so if you take if you want to test it yes today night 11:45 p.m. or something you go and check the some banks and you go and check IRCTC or Lac or whatever it may be they'll be stopping the process for 15 to 20 minutes right for their reconciliation purpose okay some some critical Banking and all they will not do it they do have some other process to overcome but some process they will keep it say for an example here instead of making this way I can go with here I can go with result okay so I can have one more variable called result equal to so here you can put that value yes result equal to N1 + N2 right so what is missing here always use col and equal col and equal so this way you can use but one thing I forgot here so one error you may have you you may get it so what is error I may get it yes you have to declare this result also for an example if I'm going to execute see how it will be executed is showing like result must be declared right so this way you might be getting error what is that how article will be executed arle will get this number 20.5 it will store the value the variable called number one it will get get stored in the plsql block and then it will get another number 10.5 number two variable it will get stored and then it will add these two number but which variable it will go and store it right result but I haven't declared it right what is this it is a variable it is a Vare or it is a number or it's a date data type right so that I can make it here okay result so result also it is a number data type 10 comma 2 okay so that way you have to declare it and you can execute and now you are saying that the addition of 20.5 10.5 are 31 so this is one thing I hope you are clear you can go for complex scenario also yes so this is one thing do we need to add semicolon at every statement yeah that's what I told if you see this this is one variable variable declaration you have to terminate another variable declaration you have to terminate another variable declaration but begin will not have any termination decare will not have any termination so but how can I get the values run time see here I'm going to make you can put Amberson here the Amberson symbol you can put Amberson symbol here and you can put number one the variable name here right and you can put here another Amberson symbol then you can put number two then this will take the data from the runtime you're going to run any P it is showing like enter the value for number one the real the run time you can give any number enter the value for number two I can give some numbers if you click on okay then it will produce a result okay it is giving you the numbers the addition of this number and this number are this one is it giv that yes you can make this Amberson symbol to get the value from the user okay in the run time if I want to get the value from the user then you can put this way the erson symbol also okay this is one way of getting the values yes for an example here I'm I'm making that value even you can put you can put here something like this and in the begin in the begin I'm going to make number 1 equal to 20 and sorry you have to put always semicolon equal and number two equal to 30 so I I so what I'm going to do now or you can get some hard coded value here 50 from here I will take some 100 here so if I'm going to execute see what I did it here so here in the Declaration part I have assigned some value yes is called initial execution right number one I'm getting giving like 50 number two I'm giving 100 and here result I'm storing a variable and here so what I did it again I'm assigning some value for the same variable so what will happen so why we are doing it here okay so if I'm going to execute if I'm going to execute the statement what could be the result 50 or 150 yes it is 50 only okay so why because initi we are initializing a variable here okay first we are giving some value and then after that we are overriding the value okay so we are overriding the value and this is what it will take pipe symbol for are concatenating two strings got it you should have practiced SQL okay that's what SQL is needed see I want to put welcome to okay plsql State psql session right I can put plsql session single codes from Dual right so I'm making this way so what is this pipe symbol represents this pipe symbol represents the concatenation concatenation of this one and this one right so you can see see one more thing if you have SQL statement and plsql statement in the SQL Developer don't use semi control enter okay if you have SQL statement and plsql statement in the same window normally how we will execute PL sorry SQL statement we'll give the cursor here control enter right but if you have plsql statement don't use control enter if you're using control enter it will start executing this particular statement also that's why you getting some error right so don't use it just select the statement run it so what is this so what is this see this is nothing but the pipe symbol right it will concatenate the same way can you add two numbers can you add three numbers sorry can you multiply three numbers will you able to do yes you can able to do it to now we have seen how to use the variable how to declare the variable and how to display the variable here but in realtime project are we going to use dbms no this dbms output.put uncore line to display only the intermediate result that's it but instead of dbms output you'll be doing some SQL statement here and we are going to see this okay so here we have seen so this statement we have uh the finally we have seen right so we have two dat two data so two variable we have declared here and so number one we are giving some value number two we are giving some value and we are getting here see normally in realtime project we can initialize the value here right so we will initialize the value some days you might be doing some some some data will be initializing it and then you are using the value here yes you can do this way and if you're doing it yes this value will be taken right but so the thing is you thinking that I should not change in the Declaration part I'm going to declare it and this value wrot any package wrote any package or any functions or procedure or anywhere we should not change it the some value they will fix it globally right so if I want to fix it you can give that as a constant okay so if you are declaring a value as constant after that if you're trying to change the value you cannot change it okay so number one constant number of 10 you are making some value here you are trying to change that value you cannot change it so this you might be getting this particular error right see expression number one cannot be used as an assignment Target see this error you'll be getting it whenever you are executing any plsql statement you're executing any plsql statement it will give you the line number and column number line number six 1 2 3 4 5 6 so this particular line is the error right line number six column number one okay this is the one it is getting failure it is clearly mentioning that expression number one cannot be used as an assignment Target so that means you have already assigned a value as constant clear so what is that constant variable declaration if you in the Declaration part if you are making like a constant anywhere in the program the value cannot be changed that is called constant and here I'm going to have one more thing so this is constant I'm not changing here just I will make it here okay so this value what I'm going to do so this value I'm making like null value here null can I use this way so 0+ null what is the result you will get it this is zero the constant value I'm not changing here if it is executed what could be the result if you do any addition okay you have some known value you have some unknown value so 40 plus unknown value what is the value you will get it unknown value right so if you're adding or if you are doing any arithmetic operation on null you'll get null only so if you go and execute you'll be getting null why why because this is zero yes no value but here it is null so null plus 0 you'll be getting null only so that's why you are getting null here but I'm I want to make so this value should not be a null value anywhere in the code so that you can make it like not null okay that you can make it like not null see constant you have to make after the variable name you have to make constant but if you want to make not null you have to put the variable name after the data type we have to mention the constraint name normally this is what we'll mention right variable name data type constraint so that way so what could be the result now so if I'm going to execute what will happen if I'm going to execute you'll be getting error so what is the error why because here I have mentioned this number two as a not null variable right but here I'm assigning null value this is this is what you are getting line number seven column number 10 and expression is of wrong type so this this is the wrong type you're getting it so this particular error you'll be getting so seven 1 2 3 4 5 6 7 so here some error is happening so plsql will try to give exact line number so where is the error So based on the line number you can find so why because if you're going to write a very complex plsql code right so that time you may not get where you are getting the error so you have to check where you are getting the error this is called not n variable declaration if you have defined as not null you cannot give null value anywhere in the program so that's a not null value zero is value right and uh space is value you're giving some space is so that is also value it will take some asky value but null or empty can say empty here we do not have word as a empty in article and blank sometimes these are all some informal uh language for defining the null so null is the correct word okay so all are similar null only so that means nothing in the in the the memory it will not assign any value inside plsql statement can I select some data from any table okay for an example I'm going to write some plsql Anonymous block okay I'm going to write some Anonymous block so here I have some Anonymous block here so in this Anonymous block so this is what I'm going to write plsql block you assume that this is plsql block so in plsql Block I'm going to write some code okay so begin I'm going to write I want to take the data from some tables okay I want to select the data from some tables right so begin and end I want to select data from some base table I want to select the data from some table is it possible yes it is possible so if I'm going to write any DRL or DML statement inside the plsql block for an example table is available here okay so in the database table is available you want to bring some column values to this particular plsql block so that means if you want to take all the data so that particular particular data should be stored in one one variable so one variable you have to use it here you have to declare some variable in the variable you have to use select statement so what is the select statement you can write you cannot write this way so normally you will write select the column name the column name say for an example I want to select customer name okay customer name that should be selected from the base table and it should be stored into one particular variable here so select column name into a variable name here okay customer name from from customer table you assume that it's a customer table yes I can go with customer table and you cannot write you cannot write all the statement you cannot bring it here you can use only V class for one record so you have to use one record only at a time you can take it for an example customer ID some value so this is what I can write it so why because this is the customer table if I want to take a value from this base table this is the base table you assume that this is the Bas base table you have so from the base table if I want to select the data to the plsql block I can write at a time only one data that to you have to store a variable here so that variable we have to declare it yes we have to declare the variable Vore just underscore name is nothing but Vare 2 okay so we have to declare it if you're not declaring it you cannot fetch the value here you have to declare it okay so I missed it if I want to select more than one record from the base table either you have to go for cursor okay you have to go for cursor or you have to go for bu collect so these two you have to go but these these things we will take it later I will take this table employee table okay employee table some has it has some value any any table you can take it no issues for an example I want to read the data from this employee table okay for mobile number of this customer I have to take into the plsql block so how can I write so you have to go with so normally how will you write the plsl statement be begin and end right so start with begin and end inside the begin and end you have to use the select statement so what is the select statement select so can I use star here no we cannot use star here right have to use the column names so which column I have to use I can use the column name phone number Select phone number column from the table employee this way I'm using bar employee uncore ID equal to this ID see lot of concepts are there I will take one by one but understand the basic okay this way I'm going to write it if I'm going to execute this this will give you an error okay see here you are getting line number three right 1 2 3 three line number three and you getting an into class is expected in this select statement so that's what it is giving you theor right so what is that whenever you are writing select statement you have to put into so select phone number into one variable yes we have to put one variable so that variable we can assign it to V phone number right see this I'm selecting the phone number from the base table okay assume that from the base table customer or employee whatever it may be you're selecting the phone number from this table and you are storing it into a variable called V phone number what is this V phone number that we have to assign it here right we have to decare it you have to go for declare and inside the Declaration part you have to go for V phone number is nothing but it is a barcat two of some 100 okay so this is the way you have to declare it can execute yes you can execute but it will not be displaying anything it will it will simply show like plsql statement has been executed right but if you want to display some value here you can put dbms underscore output dot put underscore line you can put the phone number of the employee right we so you have to put single codes the phone number of the employee 102 say for an example the one2 I have to get it from the from the user that think in that way okay employee so how can I get it is you can go for pipe symbol to concate this value with variable then we have to variable this variable you have to assign it here right so then you have to put semicolon and execute it will get the phone number from the base table so what is the one2 right see here 102 the phone number 4569 should be printed okay we see see it is displaying right yes this is what but if you remove this if you remove this I'm going for like select all the phone numbers whether this particular value will get stored no it will not get stored by default to use some looping you have to use some cursor so cursor is nothing but it's like something like it's a looping only but C statement we'll see later this is what you are getting the error so if you are getting this type of error right this type of error line number four exact fch returns more than requested number of rows if you're getting this kind of error that means you are using some select statement select statement is getting more records so if I want to go for more than one column more than one column phone number I want to go for phone number and the job ID right so phone number comma Vore JB ID and we can put select JB ID into this one right so what is g v job ID V job ID also a v two of 30 okay so you are writing this way number single codes and job ID is single codes you can put V jav ID see here the phone number of the employee is this is the phone number and job ID is tester see here if you want to get one space yes you can give space accordingly then you can run it so this is the way you can execute it this dbms uncore output output underscore line dbms is one of the package it's article predefined package whatever we are going to give inside this one Oracle will display that that's it okay so whatever you're going to give inside so Oracle will take that and it will display so that value here that's it okay I hope you are clear so how to write select statement select statement inside the plsql block DML operations in plsql so how can I write insert statement insert statement inside the plsql block say for an example I'm going to take so this particular statement here insert into customer okay I will go to select star from customer I have something here then I'll go for customer one both are similar table only see here I have some value I will remove some value here okay customer one I will truncate customer table truncate table table name customer table I'm truncating see this customer one is backup table you assume that I want to check the data if the data is present here some some some sort of question we have to write so that we will take it later if condition and all but so here I'm writing the data is available here then don't do anything if the data is not available do insert operation so how can I do the insert operation see here I'm trying to insert insert into customer you can mention the column names actually so in real time project don't directly star and all so definitely don't use star you can see here select star from the table name and the custom ID right so always mention the column names here see customer ID comma customer name comma mobile number right mobilecore number comma age City ID right so these columns we have to mention one by one city ID values I'm writing see this is very simple plsql code begin and end and we are writing the insert statement and we are committing it also so why because you need to commit it right so whenever you are writing DML operation in Oracle database you have to do the commit so before that I will show you so in the customer table I do not have any data I'm going to run this plsql code so this time I'm not using any dbms output you can see here it will simply show like plsql procedure successfully completed that's it that is what you'll be getting in the log right your procedure will get succeeded but after that if you go and check in the database table so it should have taken the data right see this is very simple sorry insert statement hardcoded value I'm just inserting it right that's it but here so this is commit statement I have committed I have committed then I want to go for one more statement here how can I write insert statement inside the PLS yes see this is way you can put and I'm going to write insert into customer okay I'm going to use select statement select select all the columns from customer one where cust ID not in 100 so why because I'm using that 100 already available I think the customer one also will have the these columns right see we can remove this I'm not using hardcoded value right so directly I'm using select statement right select column names from the table name that's it right so why do I need to use all this just you can execute it will be executed so before that I will show you the customer table I have one record but customer one table you have many records since this is the primary key in the customer table I'm not selecting this that record okay you can even you can put equal symbol so I'm going to execute this showing like plsql procedure successfully completed if you're going to check this it should have loaded all the data right see all the data has been loaded see this is I'm inserting the values by selecting the values from another table so you can give hardcoded value but this is the way we will write normally so you can for an example you can check some run IDs previous run IDs or something you can check it if this particular days is today's date run has been already loaded you are checking some data if it is already loaded then don't do anything if it is not loaded for today's date you load the date something like that you can put you can write some plale code but those things we will take it after if statement all this but this is simply insert into I hope you are clear right so how can I take insert into yes this is the way you can put insert into select I want to update some record so insert statement yes you can do insert update statement yes you can put some update statement see here I can update date customer it customer name equal to this name okay so even if you are if you're not updating and you can see here I'm not I'm not committing it what will happen okay and I'm not using where class I have executed so procedure has been executed we can check in the customer table then all this are has been can I roll back now can I roll back if I roll back what will happen roll back completed and will it have previous record or see you have executed some some plsql block but you forgot to mention commit inside the plsql block you are closing the session okay so I just executed roll back if you go and check here customer table has been rolled back okay so that's why we have to use roll back or commit or something but we have some other concept so that I will explain later so we have the transaction that concept we have later I will explain the same way if I want to if I want to do the delete statement yes you can do the delete so delete from table name where condition you can put some where condition all the data will get deleted and select statement we have seen if I want to put all this into single statement yes you can put and if you want to make dbms output so that you can put it here in our previous session we have started with plsql introduction so if you if you take plsql so we do have two different blocks one is anonymous block other one is named block so Anonymous block will not have any names it will not be stored okay it will not be stored in the database the named blocks will have a name and it will be stored as an object in the database you can compile and compile one time and execute multiple times so that is the use usage of these stored procedures okay so we do have so four different procedures we have procedures functions packages and triggers we will see these concept later okay so first we will complete all the anonymous block and then we'll go for the named blocks so whenever we are going to write any Anonymous block or procedure or functions so this is what you need to have so Anonymous block means we have to execute this one manually okay so every time we need to execute it it will not be stored as an object in the database but named blocks compile once execute multiple times so that's a concept so here we have declaration declaration part and begin and end so within the begin and end you can can write the executable statement and within that you can write exception handling it's like a try catch block you can you can see in another environments right another languages same way we have this is executable statement if any exception is happening we can handle it in the exception handling block so if you take any Anonymous block begin and end will be a mandatory one this exceptional will be optional one and declaration also it's an optional one so very simple simple statement if you want to write simple Anonymous block so this is begin and end within that I do not have any variable to declare so that's why I'm not declaring here so just I'm writing I do not have even exception handling also so we have seen different Anonymous block how to write it so how to assign a value okay so in plsql if you want to assign a value you have to go for assignment operator colon equal that is the assignment operator so whenever you want to assign a value you have to assign this way only so we have we have seen so what is assignment operator so what is the use of this decimal decimal point right so how to assign the values and how to make it like a constant how to make it like a Nal declaration part so everything we have seen I have given all the statements I hope you have seen all this right so how to write select statement in plsql statement right so in plsql Block if you want to write a select statement you can write the select statement by using into right so into keyword so into is nothing but like a local variable so without variable you cannot write any select statement in vsql this select statement should take only one record to store so if this particular select statement is is selecting more than one record then this will not be executed you need to go for either you have to go for cursor or you have to go for bul collect so those concept we will see later and we have seen DML operations we have seen select operations DRL okay R DML this is also sometimes we'll call it as DML and we have seen insert statement update statement and delete statement how can I write inside the plsql block same thing we can do it in the forthcoming sessions also so we'll be writing okay same select statement insert update delete so anything you can write in the named blocks as well so we will see later then now we will go to the the next concept I hope you are clear yeah if you have any questions you can ask me through chat so I will answer your questions okay say for an example here I have one table you assume that I have one table I'm going to create one table simply I'm just creating create table table name Open Bracket customer ID then this is number data type I can give some 8 digigit number customer name is v two of some 40 or 30 whatever it may be and then I'm writing some data birth it's a date column and customer mobile number okay mobile number so number data type number of 10 digit and then City okay it's a v Cat 2 of 30 or 40 so I have done everything I have executed table has been created and I'm going to insert the data how can I insert insert into customer values customer ID okay some customer ID customer name I can use some customer name customer data birth so data birth I have to use so how can I use you have to use 2core date you have to mention this so what is the format we are writing so you have to mention the same the same way you how to write the format say for an example I'm writing in this way single codes again single codes say mm/ dd/ y y y this way I'm writing that's it okay this is date column whenever we are inserting a date so we have to mention the format right so mobile number so some 10 digigit mobile number I'm going to insert it and city it's a Vare right so always Vare should be in the single quotes we have already seen this just you can insert it on row got inserted and I'm just inserting one more so whatever the number of row you want to insert you can insert it okay so different customers I'm inserting you assume that okay so just I have changed the names and yeah I'm going to insert it it's a DD right okay so DD you can put any date so I'm going to insert these two records inserted and I'm going to commit it so always make sure that you are committing it okay so commit so how many records are there in the table three records are there in the table select start from customer you can see three records are there in the table so so here you may ask question yes I have already explained this but if you're asking this okay how it is showing in this format we have we are making 1982 in this format only right how it is taking in this format see it is taking the date in this format only in the database it will take in this format only but the SQL Developer is showing you the data in this format if you want to check it you have to use two car function to select it okay so we want what we are going to do we need to take we need to take the mobile number of each customer inside the vsql block we have to display so how can I display say for an example if I'm going to give the customer ID then I have to display the mobile number I have to give customer ID as the input how can I write the query sorry how can I write the plsql statement so first you have to go for begin and end right see the requirement is if I'm going to give customer ID as the input it should display the mobile number so how can I write begin so whenever you are writing begin so start with end so begin and end within the begin and end you need to select the mobile number right to select the mobile number Select mobilecore number you have to use into so into variable mobilecore number I'm just using this way mobile number then from customer table can I write this way no we have to write where class right so where class where customer ID equal to you have to give some customer ID say for an example I'm giving hardcoded value if you want you can make some Amberson customer ID you can put customer ID here okay it will ask so what is the customer ID you want to have so just I I want to declare here right I'm going to use declare and inside I can get whatever this variable I'm going to use here it's a number data type so number of 10 digit I'm using and it will take so I'm going to print here right so how can I print dbms output put underscore line bracket the mobile number right we can mention this mobile number is single codes then I can put the concatenation so what is the mobile number you have to print this mobile number right yes so just I'm taking I'm going to run this so will it run so what is the output you will get it here so dbms output right you have to open the dbms output you can open it from here go to view dbms output you can click on the plus symbol just to connect to the H schema it has been connected so now I'm going to execute it if I'm executing then it will display the mobile number of this particular customer it it should display this mobile number here right you can see here yes the procedure is successfully completed mobile number of the customer is this one it is displaying okay it's correct so your your code is running fine even if it is a procedure or function or whatever it may be today your code is running but think about after some times later the customer in this particular table they want to change the mobile number like with some data type okay some character data type plus 91 I some so I have to put some character character data type I have to use this way plus 91 I the mobile number so like 10 digit mobile number I have wrate so how can I get this value how can I use this you assume that in the customer table so how can I modify the mobile number I have to previously it is number of 10 after that I have to change it to Bar 2 how can I do it any idea so what is the code I have to write alter table table name right alter modify can I write it directly alter table table name customer modify what is the column number mobile number Vare 2 of 40 so it has the data in the table so we cannot run this right the column to be modified must be empty to change the data type yes it should be empty so what we can do now so how can I create a backup table create table we will move the data to backup okay so we will do in this way customer backup so I'm just recollecting all this information to you okay so it's we have already seen these topics already multiple times we have seen But one more time I'm just recollecting so we will take re we can do this way or you can go for renaming the table so we can just rename okay rename customer table to customer backup so just I'm renaming sorry we should we should not use since if you have plsql code you should not use control enter select it and execute table has been renamed so if you go and execute the statement will not be executed why because you do not have customer table so this table I'm going to create with Vare 2 data type now okay I have renamed I'm going to create the table with vat to data type say for an example V Cat 2 of some 20 V Cat 2 of 20 I'm just writing okay so I have changed it so what I should do now insert into customer insert into customer select start from customer backup right select execute three rows got inserted after inserting what should should I do so commit so commited and no need to have this even if you see this if you want plus 91 you assume that I want plus 91 for this particular column so if you go and check in this table customer table there won't be any plus 91 right but I need I need + 91 so how can I take + 91 here I need plus 91 for this so what I will do I will truncate the table I will truncate the customer table one more time I will do you customer I will trunk it okay customer table I'll trate all the data that's it we have erased so we are going to insert it insert it while selecting you select you need the plus 91 for all the records right so you select customer ID customer name c name comma data birth comma you need mobile number so mobile number you need plus 91 right so you have to use single codes plus 91 I single codes concatination mobile underscore number correct comma City see this way you have to select it if you have select this way if you if you run this particular select statement it should select with plus 91 for all the records even if you put case statement for an example you have something like country here country here based on the country you have to add the code okay if it is country India have to add plus 91 if it is USA plus one it is any other country based on the country you have to add it so this way I'm going to insert it if I'm going to insert it three row got inserted and you can commit it you can check the customer table it has the data type with V to data type right okay so you assume that your Source team has changed the data type so now you are going to run the same same plsql code what will happen will it run I didn't change anything on the plsql code right so will it run any idea if I'm going to run it will not run so what is the error you are getting so what is the error you are getting so something like plsql numeric r value error character to number conversion error something we are getting this type of error in plsl code so why we are getting it previously it was running fine our code but after the base table changes it is not running see to avoid this kind of issue right so if you are changing any data type always don't hardcode the values in the plsql blocks so don't do not hardcode if you are selecting any columns from the table do not hard code this data type what you can do you can use percentage type so always you have to make so what is the schema name so what is the schema name H schema so normally we will write schema name schema name dot table name base table okay B Bas table name percentage type so which which column it is we have to use mobilecore number percentage type so this way you have to use okay so mobile number alone we are using percentage type means it will take the data type of mobile number column from the base table got it so we are not defining any data type if the base table has data type say for an example what is what is this one this is our plsql block this is our base table so previously the mobile number here we have defined as mobile number here we have defined as number data type in the base table so we just selected this number data type here so we have seen what is the data type of mobile number in the base table the same data type we have used in our plsql block for this same number number data type number of 10 we have used so our code is running fine but they have changed from number to number to Vare to they have changed so because of this changes our code is not running right what should I do now okay so that is why don't take the data type as it is from here so don't take it so you inform plsql block whatever the data type of this particular column this particular column we have so that data type you take it from here okay the column name percentage types column name percentage type if it is one single column yes you can use percentage type if you are going to take mult multiple columns that is our entire all the columns you are going to have okay 10 columns you have only need to no need to mention 10 times you can mention percentage row type the entire row we are taking all the data type of ENT record so that is your percentage row type percentage Row typee the entire data the entire record data types we are you're retrieving from the base table to plsql block so I hope you are clear now so if I'm going to run now it should be executed see it is running correct previously it was not running after changing that it is running so I hope you are clear so what what is the use of this percentage type always they will be writing percentage type what is the difference between type and row type see type means only one column you are taking one column data type you are taking from the base table so that is percentage type percentage row type means all the column values say for an example how many columns are there in the table how many columns are there in the table you have 1 2 3 4 5 see all the columns I have to take should I mention five times this way no need to do this you can go for simply you can mention table name percentage row type no need to mention that column name you can go for row sorry row type okay so percentage row type you can use it so this way if you're using then it will take all the columns data types okay so how can I write that I will take percentage row type you have to mention percentage row type this way you have to mention okay so percentage row type so this way see what is the the you can mention entire table right so no need to mention one particular column so you can mention okay so entire table bore customer then if I want to write it you can put select star into this one so I'm writing I'm not writing any one column I'm taking all the columns into V customer this from customer where customer IDE always you have to take only one record only you should not take more than one but after that if you want to go for you want mobile number or you want to have some some customer name you want to make it the customer name you want to print the customer name yes you can put customer dot dot this variable name dot cust name right yes you have to mention this way and if you need data birth any other column okay okay so if you have 10 columns yes you can mention all the 10 columns here customer data birth is if I'm going to execute so here I'm not using only one column here I'm using all the columns then I'm using see here it has taken all the columns right so this will have entire row data types clear on this this variable now it will have entire tables data types so this way you have to practice percentage type percentage roow type if any vsql statement in the Real Time Project any procedure or any functions you can write anywhere okay procedure procedure also will have the Declaration part function also will have it so there if you want to write it yes this way you can write percentage row type percentage type so if you go to Real Time Project definitely they'll be writing percentage type percentage row type so that time don't get surprised so this is what they are taking the data from the base table data type so next is concept is control statement see this control statement is used in all the named block as well as Anonymous block so whatever you want to write this control statement yes you can write so what is control statement you have two different control statements we have so one is if statement the first one is called if statement the next one is called case statement okay so you can write both case statement and if statement if you take if statement there are three different types of if statement okay simply you can write if so always you have to write condition only so if condition the condition is true if condition if the condition is true what should I do here statement you have to run the statement then you can write end if see remember whenever you are starting if make sure that you are making end if so that's why don't put only end if you put only end right if you're going to write multiple lines of statement then you will foret to mention okay you'll forget whether this is the end or end of this particular if statement or the last begin and end so you'll be having lot of start and end right so you'll forget you'll get confused where it is ending so always if you're starting if make that end if okay this is simply if condition that's the condition satisfies then the statement will be executed if the condition is not satisfied it will come out of the if statement it will not do anything then you can go for the same way you can go for if condition then statement you can go for else else statement so you can put some El statement the condition is false then automatically this statement will get executed so this statement will get executed that is what you have to write if if else if then else okay then you you'll have else if see I want to test multiple conditions not only this condition I have to write multiple condition this is similar to your all the programming language if you can you can see in python or you can see some cc++ Java all this you'll be having right so if condition then statement you can go for El if I want to test one more condition you can go for for LF see here in plsql the LF you should not write this way El s if okay remember how should I write e i sorry El s if LF okay so else if condition then statement okay else so you can go for n number of elip no issues you can go for one more elip so any number of else if statement you can write okay so this is what three different types of if statement if condition then statement end if all this where should I write all this so you can write this one inside of the anonymous block or inside of the named block also so named block we will make uh after some time okay L shift means so any multiple conditions okay so you you want to check it if condition else if okay this if you should not write so else if that's it okay else if that's it okay uh we should not write one more time if that's what we have written here right so else if condition then statement this is if statement this is condition okay based on the condition we we have to go for so you may ask here what is a statement see here you can write any DML statements okay you can write any DML statements so that's what you are you are going to make this if condition so first we will see the if statement then we'll go for case statement see we have seen case statements already in the select one right so select statement we have seen how to use this case statement but in plsl how can I use the same way I will write the syntax case when condition then state okay so this way you have to write so I can make in end case and write use this way this is simple case you can go for multiple condition else conditions you can use else statement okay else statement here you can write okay so make sure that this semicolon you are you are making correctly and this is case when right if you want to test multiple conditions so how will you test if you want to test multiple conditions case when conditions then statement if you want to test multiple conditions you have to test from here right so when condition then statement when condition then statement I'm going to write the if statement okay it's very very simple if statement is not that much complex here okay so you can see here it's very simple that's what I have explained so we have two types of conditional statement one is if statement another one is case statement if condition then statement so whenever you are making if make end if if condition condition one then statement one the condition is true else we can go for else statement end if then else if condition then multiple times finally you can write else okay and if I'm going to execute this particular code this particular code so what what should be the result it's very simple see we have Boolean data type in plsql so what is the data type plsql data type we have we have one topic that I will take but we have Boolean data type in plsql see a is Boolean I'm I'm defining as false first I'm giving the Valu is false begin if a if a means if a is true then this will get executed right if a is true then it will get executed but a is not true is false then this particular line will be executed clear if condition then statement else I'm making see here the condition is false can I write without L statement can I write without El statement yes you can write without El statement if condition then statement end if so what should be the answer if I'm going to execute what could be the answer if I'm going to execute this one so it will check the condition condition is not satisfied it will come out of the in if okay so you'll get null value right so nothing will be showed only simply it will show like procedure completed successfully that's it it will not display anything so that's why we have made the L statement if if statement is it is not satisfied then automatically it will go to the El statement okay it's very simple only see here I'm taking this one so even you can take different way so a is number okay you can take some a number data types okay so you can assign some value here here is 10 and B you can put some number here I'm making 20 b here I have given hardcoded value see here we are doing only dbms output right in real time project are we going to do like this no if you want to see the data in the log then we will write dbms output otherwise we will not write this we will go for some executable statements okay some select statements insert statement update statement anything we will use it here assignment some some values we will go for so if a is greater than b if a is greater than b then okay so you can put a is greater than right so a connation is the single quotes greater than greater sorry greater than have to make B right B else you can go for the opposite statement you can make B is greater than a see here if I'm going to execute this it will it should show like 20 is greater than 10 should display in that way right so 20 is greater than 50 right sorry 10 is that correct yes so we can write this way say for an example here I'm going for some five five here I'm writing 10 so which one should be displayed now so 10 is greater than 5 correct so it will display in that way so we are writing some if statement so if you can write so clear you can write some if statement here this way you can write and for an example here I'm going to execute this particular statement see what are all the lines will be printed you check here and then answer it a is Boolean data type we are assigning true B is Boolean data type we are assigning false okay so a is true B is false see first very first we are we are looking at very small small programs if you go to realtime project same way they should have return so that time you'll get to know all this very clearly so which lines will be executed this is and R not a KN of B all this see one by one then take one or two minutes then answer it very easy okay if you if you see this a is Boolean data type I'm giving true B is Boolean data type I'm giving false okay so A and B true and false this is and condition right both should be true then only this will go to inside right so this will not get printed so you will not get this output okay you will not get this line number one so why because a and condition so both should be true then only this and will be true right so it will it will come out out of the if if a r b a r b yes true or false R Clause right any one is true then this will be true it will go inside this line number two will get printed end if it will come out right if not of a so not of a is a is true not of a is false then this will not be executed this will be executed right so else condition this will be executed line number four will be executed then end if if not of B not of b means B is false not of B is true then this will be executed so the answer is line number 2 45 so I will execute 2 4 5 okay got it line number 2 4 5 so not of b means B is already false not means negate it will negate so true this will this condition will become true then it will go inside see this way we should have written some logic inside PLS scale block BL so that time don't get surprised okay yeah we do have some Boolean data type in our some data quality framework and all we used to have this kind of this kind of uh analysis we will do rules we'll check it so now I'll go to the next one so this one I will show you so what this will do you can tell me this is taking the data from employees table right select star from employees table so employees table I'm just running it what is the maximum of salary here here 24,000 okay so as of now the max maimum of salary is 24,000 I have not5 records that's okay so what I'm going to do now so what is this this statement will do so what this particular statement will do this Anonymous block will do how many records it will get deleted so will it delete maximum salary record one record will get deleted are you sure see first of all you understand here you first go to begin begin and end okay select maximum of salary so what is the maximum salary 24,000 this will be stored in this particular variable so Vmax salary it will store this 24,000 okay assume that 24,000 begin Vmax salary into this one from employees okay dbms output the maximum salary I'm just printing it if V Max salary greater than one lakh whether it is greater than one lakh no this is 24,000 so it won't go for the statement right delete from employees where salary equal to whichever the salary we are having here that we are passing here and then we are deleting it so if I'm going to execute this one no records one5 records already available right yes if I'm going to do one more time it will not delete any record see I have executed plsql procedure it has been executed you can go and check it won't delete any record same one5 records are there so what I'm going to do now so here I'm going to write one particular statements okay this one I'm adding one zero one zero I just added right I just added one zero I will commit it commit successful if I'm going to run this 105 how many record it will get deleted zero sure one right so it will go and delete one record see here oh Integrity constraint validated child record found okay for this right we have some child record so that's why we are we are not able to delete it it is trying to delete this particular record the last record but for this something maybe maybe this job ID or this department ID you might be having the record in another table okay so that you have to check it why it is not able to delete so that you have to trade so in this way you can put some UND delete cascad and then it will get deleted okay we are just trying to delete the record so this way you can write see here we are getting some exception right we running this we are getting some exceptions this exceptions you can handle it okay here we are getting some exceptions OA some number it's getting right so this one we can handle it without getting the error we can handle this that should be handled by exception handling that we will see later but this one you got it right so how we will write if then else in real time also we will have the same kind of if then statement so here we might be doing some select here we might be doing some insert update delete any operations we will assign some value then we will go for one by one so now we'll go to the case statements so case statement also it's similar to if statement only you'll see this so this is the syntax of the case statement and you can use for an example if I'm going to take this particular example here V salary I'm taking so normally in real time project first don't declare the variables first you go to the begin and end within the begin and end you try to do the statement after that after that you can so within that whatever the variable you need for here so that you can declare it here okay so that you can declare whenever you you are going to write it I want salary yes salary into V salary I'm going to store the salary into variable so that I'm storing here salary into V salary from employees where employee ID equal to whatever the employe ID here I'm passing that I'm taking case when this is fa salary all this okay this very able to store the the local employee ID that's it okay even I can put Vore EMP ID just I will take here so based on this particular employ ID whatever the employ ID we are passing it will display so 120 I'm passing the employee ID here we are making the 120 employee salary is 8,000 it is displaying like low Sal okay is displaying the salary first V salary I fund low salary that's it clear so this is one case statement case we are writing in in the you can ask me the same question okay so we have the same type of case statement in SQL also then what is the use of case statement in plsql see in plsql in SQL statement case statement is used in select only right but here based on some condition you can do some DML operations here you can write select statement you can write any other DM statement also okay if you see this same thing we have written the same thing we have written here we sell the number of 10 we are passing the hardcoded value that's it okay so just we are passing that so instead you can pass this way also so Amberson this one is nothing but we are getting it see as I told say for an example you asked me so how the where the the where class will work right sorry the case statement will work I will take one example here same way we'll put this way we'll add we'll commit it case statement in the PLS plsql is for making the any DML operations but here I want to add manually I'm adding The Columns okay real time uh you cannot add it like this you will not have access in development you will have access so I'm taking the column name as country r car to of 20 here I'll make okay so I have added one more country if you go to data you'll be seeing this country as India okay so you can make some other country here some other City here see based on the country you have to put the code you assume that I'm going to write some other name here some other datea birth some number here some country here I go for Singapore okay some I will write I'm not sure what is the city I write this way okay so I have written So based on this country we have to add some country code here okay you assume that this is the table right customer table you have to take mobile number and Country you have to take the new mobile number here so select customer ID okay comma customer name comma mobilecore number data bir City Country then new mobile number you have to write so I'm writing case case when when country equal to India single codes okay country equal to India then mobile number is + 1 + 991 I single codes concatenation mobile number mobilecore number this way I have written okay case when then this way okay so you can go for the same way you can go for number of I add it right okay then concate okay when country equal to USA I'm just writing plus one I when country equal to then I'm writing country code okay just I'm writing so else if you want to write else code yes you can write else same similar country only you want and we have to end we can put end case or end that's fine this is alest name new phone number phone number okay from customer table okay so put only end don't put that in place so I'm going to execute this so you get getting correctly right whenever you are having the country this way you getting added new new new phone number so this column is derived column this column is not available in the table right say for an example one particular customer SC India iest lower case so your code will work so what is the what is the phone number you'll get for this particular customer so you will get India here okay country itself you are getting not country this is phone number right so you need to get phone number here yes mobile number here I need to get but it's not a correct one right see this is also India you are getting this way so what should I do now to fix this issue the upper off lower of all this okay so this is initial letter Capital so if you want to go for okay unit cap so unit cap means initial letter is capital that's fine you can use see I am using only sorry I'm using only for this country alone India alone but if you want to use it for other countries you can use it so now it is getting correctly okay so different yeah this is what the case statement you are to use it in the select statement okay so I hope you are clear so how to write the a statement normally whenever you want to have a new column then we have to go for a statement a derived column okay in plsql we have three types of looping so what is that looping simple Loop can go for simple Loop one is so you can start the loop you can write some serial code and you can go for end Loop so how it will come out of okay so you have to write exit when some condition when I have to exit from the loop so exit when this particular condition meets you exit that you have to write okay you have write exit when this condition meets you have to go to the end this is normal simp looping Loop in Loop okay then we will go for while loop so we have three types of loop one is simple Loop y Loop for Loop what is y Loop so y Loop you will check the condition first if the condition satisfied then we'll get into the loop this will not check the condition it will go inside the loop then it will execute one time then only it will check the conditions okay but here first of all it will check the condition if the condition satisfied then only it will go inside the loop otherwise it will come out of the loop that's a i Loop par Loop means par Loop par condition some some you can write some conditions it will go inside the loop okay so it's very very simple so what is that meaning of this one I want to print this hello some five times five times I want to go for this Loop I'm just starting Loop begin Loop whenever I'm starting Loop make sure that you are making end Loop same way like if statement right you can put in end Loop Loop and end Loop first I'm printing this that's why I told Loop statement if you're going for at least one time it will execute the statement one time at least it'll execute dbms output see here I'm writing dbms output you can go for any other DMS here so I'm writing okay so welcome here welcome to real SQL plsql okay welcome to plsql I'm writing so first it will go for welcome to plsql c c is first zero right so welcome it may take okay you can go for one here greater than five that F okay see first time assigning value C is number initializing the value with one dbms output welcome to plsql one time it will one print one c equal to C + 1 1 + one two now right is two now right take take two exit when exit from the loop when C is greater than five that time you can exit exit so now it is two only again it will go inside the loop it will execute one time plsql 2 now it's a three it will not exit one more time it will go inside so it will print like four so this will go this way five times it will print see whenever you are writing looping statement you write in Loop simple Loop you have to make exit when condition okay exit when condition how many times to print five times to print you can see this welcome to plsql 1 this way it will come see that is why I told if two or three members if two or three members are getting salary more than one lakh you can Loop into you can go into the loop and execute the same if statement if if it is no record found if the count is like a zero then you can come out of the exit when count is zero so that statement you can write it so all the salaries those who are getting more than 10,000 it will get deleted automatically so even you can write one simple statement delete from table name be salary greater than all this you can write simply but I'm telling you can the same way you can go multiple times Loop yes you can check it here this is simple Loop sometimes this is also in interview they will ask this type of questions first I'm assigning c equal to 0 first I'm incrementing the value Cal to C + 2 two will be printed exit when C is greater than 10 end loop again it will go for so from 0 to 10 whatever the odd are even number is there it will print correct even number right see 2 4 6 8 10 all the even numbers will get printed why because I'm adding two two two numbers right here so that's what you can add see you can write any any type of looping statement so page number page 150 okay so something like 50 times we are writing right page one50 like 50 or 50 50 times you are are going to the looping so even you can get some this value from the user and check whether you are going to the particular range also from this range to this range you have to print yes you can get some two ranges from the user then you can print so next one is while loop see this is the Syntax for the Y Loop so please make sure that you are remembering the syntax while condition Loop executable statement and end Loop that's it see first it will check for the condition that's advantage of O Loop so first of all I want to check the condition if the condition satisfies then only I have to execute the statement then you can go for y Loop okay don't go for simple Loop at least one time it will execute right so that's right you can go for y Loop so here if you see this why the declaring the variables c equal to Zer here begin while C is less than or equal to five yes hello it will print one time right so five times it will print so you have to put increment here yes you have to you have to use increment the the variable you have to put increment so whatever the variable you are using right so that variable you have to increment so otherwise it will not be incremented if you don't use this it will go for infinite Loop so sometimes you may do this kind of mistake you'll be using if you are not using this the buffer will go out of the range so you'll be getting see how many times it has printed see here right see buffer size is 20,000 so all the 20,000 it has printed so it is Raising on application error it's an exception actually buffer overflow so limit of 20,000 bytes so that's why we are getting this so if you are writing any looping statements don't forget to use this increment value so if you forget to use increment then it will go for infinite Loop okay six times is printing right so since I'm starting from zero and less than or equal to I'm making okay so if you don't use equal to so here it will go for five times only since I'm starting from zero right so that's why otherwise you have to start from one you can put less than or equal to five so when will you go for while loop so first to check the condition with the condition satisfice then you have to go for the white Loop then far Loop so far Loop is the same way see this is the syntax of for Loop for variable okay so this is syntax remember this far variable in you have to use in in in keyword the reverse is optional one what is that the reverse is optional one low value two Dot and then high value you have to use this F the low value two dots we have to go for high value then the same way looping executable statement and here no need to increment why because here here itself you are mentioning what is the low value and value for an example if you using first then you have to use end value then up to low value so 5 to one you have to make this way so I'm just writing this C1 is the number begin for C1 in one to five five times I have to go for this Loop looping end Loop and we have this end is for this begin so if you if you keep the cursor here it will show you so where it is starting where it is ending so even the the notepad++ also it will show you so if you are making so this way you can see here it will show you where it is starting where it is ending but here it is not showing the end Loop right but here it will show you in the plsql developer it will show you so five times I have to go for so just to you can execute this way five times it execute so if you if I want to go for 5 to one here this one you can write uh normal value only okay one 125 but this will be printed in reverse order it will take you can see here 5 4 32 1 right if you are taking first value will be print see this value have to give low value high value only but here it will take so I comment this one 5 4 3 2 1 okay no need to change here you are changing here right reverse so that will be reversed in our today's session we are going to learn the important topic in plsql is verer so the what is cursor so cursor is defined as a private work area Okay a private work area where the SQL statements is executed SQL statement in the sense select statement or any DML statements okay there are two way two different cursors are available one is implicit cursor another one is explicit cursor so implicit cursor handled by Oracle okay it will handle whenever we are executing any select statement or any DML statements Oracle handles this implicit cursor and if the user if I'm a user I want to handle it I want to handle all this cursor then I have to raise explicit cursor okay explicit cursor so if you see this in the in the database we have the base table so from the base table I need to take take the data from here so from the base table I have some data here say for an example it is my employees table you assume that my employees table so in this employees table I have all the data say for an example I have one not 7 records so here I have one7 records right if I want to fetch the data from this employees table to this plsql block it might be a named block or it might be a Anonymous block if I want to fetch the data how many records I can fetch it at a time you can fetch it with only single record right so you have to use definitely a v class yes you have to use a v class so with that V class you can get only one record you can fch from the base table so Oracle in the plsql block say for an example here you are having you want to Fitch salary of the employee then Oracle will open a memory variable here okay so memory variable so that variable we are going to have Vore EMP salary you remember we have seen Vore EMP salary then we can Define this variable whatever here we have so that V Clash whatever we are defining so that salary will be stored here say for an example that salary is like 50,000 so that salary 50,000 or 5,000 whatever it may be so that salary here we are fetching so here we are fing and then within this block we can use this variable to I wherever we want to use it yes we can use so this is like normal statement so we have already seen but we will see one more time I'm going to declare I'm going to begin okay I'm going to begin and end so I'm going to make end here and so whatever we are going to have some select statement I want to write it so how will you write the select statement so select I want to take salary okay assume that I want to select the salary so how can I take the salary here I will take select salary into Vore salary so this variable we have to Define right so this Vore salary it should be a we have to decare it this variable so you can go for this variable is nothing but you can use this particular schema this particular table salary percentage Ty right so just we can Define the type and you can use this one and you can use from employees where where employee _ ID equal to some ID I'm just passing so this is the one right so then you can you can get some dbms output you can put dbms uncore output dot put uncore line put underscore line then I can have the salary of the employee yes so just single codes concatenate and get this particular salary just print the salary here it will be printed you can make so this is the way we will put some simple plsql statement right so what it will do so what article will do the same way it will take the variable here V salary or vemp salary whatever it may be you can use vemp salary also so this variable will be will be opened here and this this one will get stored the value will get stored from this base table but how many records we can take only one record I'm going to use it say for an example I'm going to make tbms output or you can put sver set server output on okay you can use here set server output on and you can make clear screen also say for an example every time the clear screen we are clearing clearing it here right clear screen so this one instead of manually doing it you can do this F if you're going to execute this I'm closing this dbms output just I'm going to execute this okay you are getting some error okay dbms okay this is the one so this is the package name dbms output so you can execute one more time one more time if you're executing clear screen will get this one will be cleared I can use this the salary of the employee is 8,000 so this 8,000 will be stored in this in this plsql block this variable will get stored right and say for an example I have I'm going to have more than one record okay more than one record I have cut out then what will happen so what will happen you'll be getting error right exact F returns more than requested number of rows so that you will be getting it right so you you can handle it through exceptions so you remember we can put exception here so exceptions and inside the exception you can so this exception concept I will take later but here you can use exception when so some sometimes so we have some predefined exceptions no data found this is one particular exceptions say for an example here I'm going for 220 okay 220 here no data found see this no data found then I have to display something like no data found for found for this for this employee okay so if you're getting that employee number in variable that you can you can place it here so that you can make here but see this is when no rows right then you can put one more exception too many rows this is one more exceptions it's a predefined exception too many rows then you can put return from base table so you can whatever you want to place you can place it here and I'm going to run it so this is something like exception so this exception is not user defined exceptions these exceptions already arle predefined exceptions so that I'm going to execute it so I'm going to execute it so we are getting no data found for this particular employee right has been handled if you if you do not handle so what will happen flash star and star slash so these these things I have commented out and I'm going to use it one more time if I haven't handled you are getting some exception right you are getting some error are getting error right so Oracle 0143 no data found so this is the one we are getting it and this this one we are handling here okay no data found just we are handling it the exception name is called noore dataor found when no data found we should get this particular error yes it is not a ex it is not error it is it has been handled here are you getting any error no right so that is what the use of exceptions but exception we will see later in detail when no data found then we are getting this particular code too many rows then we are getting this particular code okay so I'm going for this one too many rows right so this time also I I have handled we will not get any error we will get error we'll get message like many rows are returned from this base table okay so we have to handle this actually so how can I handle this so we are getting lot of Records if you are going to get more than one row right normally we have to take more than one row right see within one with one row we cannot do anything so how can I get more rows so that's what we can go for some different some different method okay if I want to fetch more than one row from the base table then we have to go for one concept called cursor okay cursor say for an example you take so this is the base table so this is the base table you have so this is my base table you can put some table here I hope you are clear so what is this this particular statement right so I will give you the statement so this is normal normal scenario this is normal select statement select statement in plsql Block just if you want to do the select statement in plsql Block just we are taking that right so this is the same thing you can see here so this percentage type also we have seen okay in this variable we are taking entire table entire record and then we are placing it here we are making only one record but if you're going for many records then you will get error right so if I want to go for more than one row you can handle it in two different way one is curs sir another one is composite variable okay instead of using scalar variable so this is scalar variable this variable is called scalar variable of of type this salary whatever the salary data type we have so that particular data type it will be taken so this one we have seen already to handle this we'll go for two types of variable one is composite variable and the other one is cursor so so what is composite variable the composite variable we will see later while taking the collections okay so we have vray nested table all this associative array all this we are the collections so that we will take it later but if you want to go for composite variable so let me explain this this things set server output on you know right right then we are taking the diar begin and end so within that we are defining select statement we need to take the data from the base table right select salary I'm going to select the salary from the base table called employees but here I'm not using V Clause I'm going to take all the data like bul collect so select salary into this one we will put right this one we will put but this is called nested table okay NT nothing but Nest table this option we will see later point of time in the collections for an example I'm going to Define customer name customer name what is the data type I'm going to Define we are going to Define like V two of some 100 right and mobile number mobile number I'm going to define a data type of number data type number of 10 so what is this data type okay so they have Oracle already defined this customer name if it has characters and uh special characters all this you can Define like V and if it has only numbers you can Define like number these two are article predefined data type right the same way we can Define our own data types okay our own data type so this is the type is the keyword and this is the data type for an example this is variable this is a variable name like a mobile number right and this is a type of this data type okay say for an example customer name is type of Vare 2 we know already Vare 2 what is the Vare 2 will do we know already mobile what is this number will do same way do you know what is this ntore salary type it is one data type okay it is one data type is nothing but we are defining that data type is type n type is a table of number of 10 so just we are defining it this one I will tell you later in table this concept so we have defined this particular data type here and we are fetching the data like a bul collect and we are we are going for the for Loop so far I in this particular variable so First Data to last data we are iterating and we are getting the salary here if I'm going to execute this particular code you'll get all the salaries so whatever the number of salaries you have 107 times right so it will get printed so all the salary we are getting it this is called bul collect so we can Define we can fetch the data if I want to fetch more than one row from the base table so one method is you can go for composite variable by using the bul collect yes you can do in this way the other one is a cler okay other one is a cursor so if you are not able to understand this we will take this one later I will explain this while taking the collections I will explain clearly okay so we have seen for an example we will take one table we will take one table select start from any table you can take employees table you assume that we are having the employees table data you have some data here right okay so we will take this particular table okay so we will take this particular table I will take up to some data here you assume that I have some some data here I will take all the data but I'm I'm taking only these many data okay so you have the base table here it has some data this is my base table you assume that this is my base table from the base table I need to take the data to my plsql block so you have some this base table you have some first name you have some salary all the data we have and we have to bring this data to my plsql block so this block I have to bring it so what I can do now so first we can so we are going to do we are going to Define one work area Okay so just we are defining one work area here just we are we're going to Define one work area in this work this is temporary place it's a private work area you assume that it is a private or area this is simply one place in the memory we are allocating private work area so in this work area so what we are going to do we are going to take whatever the column we needed say for an example I need phone number I need salary or I need first name I need salary so whatever the column needed from the base table so that I'm going to bring it here okay so in this area I'm going to bring it here so I will take I will take so this work area I'm going to take so this is my first name or I'm going to take phone number or whatever it may be I'm going to take the phone number here or Sal or first name here so I will take first name here and I'm going to take salary here this is my salary column whatever the number of column you want you can take it entire column also you can take so entire table you want to take you can put percentage type so just I'm taking this private area and I'm I'm taking the first value from here right so this is the first value so each and every value I'm going to take from here and salary is 24,000 yes so each and every value I'm taking 1 by one I'm taking the value so what I'm going to do all the value I'm going to fill it here so whatever the value I want so next value Nina yes I'm going to bring it here the salary of the Nina is 177,000 so like this I'm going to fetch all the values so in this work area I'm going to Fitch it after that I'm going to Define two different variables in the plsql block so this is my variable right yes I'm going to make Vore EMP name one variable and I'm going to take Vore salary it's another variable so this variable I'm going to fetch the data from here say for an example First Data I'm going to take so I will take First Data here so this one this data I'm going to pass it here then this will get the First Data so this data will be stored here and then it will go to the next date okay fch one by one it will fit all the values and at the end it will go and finish it okay so it will it will come out of the this work area this work area is all the data one by one and then it will come out so once it is empty you can come out of this one so this work area we will call it as cursor okay this work area what is that this work area in the plsql Block in the memory location is called it as cursor see Oracle will Define this type of work area is called implicit cursor when we are going to do so that is called explicit cursor whenever we are going to Define an explicit cursor or whatever it may be so first we have to so four step we have to follow so first you have to declare first one is you have to declare okay so you have to declare the cursor so when you are going to declare the cursor this private work area will be opened in the memory declare the cursor say for an example I'm going to cursor name I'm going to make C1 see if I'm going to declare this cursor called C1 in memory it will create this particular work area Okay assume that in the memory it will take then I'm going to do open cursor you have to do open cursor so you have to open the cursor so first you have to do the first you have to do the declare cursor then you have to open it if you're going to open it it will take that value from this particular base table to this or carer it will bring the data from here to here then it needs to fetch one by one so third step is you have to fetch the values one by one it will go the into the loop and once it is you are not finding any values it reaches the the end values like there is no value to Fitch from this particular work area then you have to close the cursor see remember you have to close the cursor so this is the fourth step you have to do so what is the fourth step so first you have to declare the cursor open the cursor pitch and then close so these four steps we have to perform so always make sure that we are performing these four steps whenever we are working with cursor okay so this is the fourth step we have to do so one by one it will go to the each and every cursor and then it will Fitch the value and finally we have to close it okay so what the first step First Step you have to declare the moment you Define a declare cursor the work area the private work area will be open it's like empty only it will be opened and if you're going to open the cursor it will take the value from this base table to this work area it will fill up all the data and then it will fch one by one all the value to the local variable here and finally if the value you are not finding it it will close it so declare the cursor you are declaring the private area so if you want I can write it here so declare so what is that declare you are you are defining defining or declaring defining the private work area right memory area you are justed doing it in the Declaration part so then next one is open if you're going to open it then whatever the query you are going to write okay it's a select statement right so open cursor is nothing but select statement you're opening more than one data you have to bch from the base table right qu is going to execute and it will fill the data into this private work area then you are you are fetching the values right so fetch value to local variable local variable then fourth one once it is all the values are empty you can close the cursor close the cursor when cursor is the the cursor is empty so this is what four step you have to follow remember this so these four steps you have to use it okay so first you have to declare open Fetch and close so these four steps you have to follow so cursor is Road level operation or columnar operation okay so the cursor is row level operations each and every record if you want to fetch more than one row from the base table you have to go with cursor only it's like a looping statement okay got it okay so we do have two types of cursor so what is the cursor I have explained so there are two types of cursor one is implicit cursor another one is explicit cursor so implicit cursor explicit cursor so what is implicit cursor so implicit cursor by default okay these are created by default when a DML statements like insert update delete or the select statement is executed so that time it will be executed it will it will be handled by Oracle itself it has four attributes so always cursor has four attributes you can find is open found not found and row count so what is this see these first three okay first three will return Boolean buan result okay is open yes or no found yes or no true or false and this one it return not found yes true or false so that's the way it will open it and row count it will return how many records say for an example in this example we are using row count if you're using 20 records from here first 20 records you are taking the row count will give you 20 here so first three will return buan data type and last one will return return how many number of Records it switched from the base table so implicit cursor is a session cursor okay it's a session means we are opening this session right so this this is a session cursor session cursor that is created and managed by Oracle okay it's managed by Oracle whenever you are executing any SQL statement like insert update delete or select statement whenever you execute any SQL statement an implicit cursor will be created by Oracle and it will handled it Oracle opens an implicit cursor every time you run a DML or select statement as a user we do not have a control on implicit cursor but we can get information from its attributes but we can get information from attributes there are four types of attributes are there okay say for an example you take yesterday we have created one table called customer right so this table we have created you assume that I want to update one record here this record I have to update this record I have to update so how can I update it I can go and choose I'm going to use one cursor okay so that I'm going to use it here okay see I will take this particular statement this particular statement so what the statement will do if you see this so just I'm going to first to set server output on I'm going to make this clear screen begin and end so Within the begin and end there is no declaration here I I'm not writing any declaration part here I do not want any declaration just I'm going to execute one DML operation that's it update customer set mobile number equal to set mobile number equal to plus 91 I mobile number where customer ID equal to whatever the customer ID I'm going to give or where country equal to India so whatever it may be okay can use it if SQL okay this is the so this is the implicit cursor you can use this way okay the cursor name is called SQL here if SQL percentage not found not found that means there is no record to update right so dbms output we are just making that no rows are updated no rows are updated just we are making in this way okay no rows are updated else how many rows we are going to update okay so we rows are updated you can make dbms output put underscore line SQL percentage this is the row count so this is the cursor name implicit cursor attribute so this attribute will give you how many record it is getting so we can found like not found found row count and is open cursor is open not open the same way you can put it is not open then you can open the cursor okay sometimes if already cursor is opened you will be getting error like cursor is already opened so if you want to open the cursor you can use the same way if SQL percentage or cursor name percentage not opened is open not office open whatever it may be the same way you can put then you can open the cursor but here we are taking the row count clear so I'm going to give this customer ID this customer ID how many records it will get updated so I'm going to give this customer ID it will show you this one row updated one rows updated plsql procedure completed successfully so I should have executed fully so I'm going going to execute one more time so I will take this particular data and I'm going to make one row updated vsql procedure completed okay so one more time I'm going to execute the same statement I'm going to execute one more time I can use some other different different value customer ID so this time it should print no rows are updated right yes see no rows are updated so who is handling this percentage not found and percentage found this found or this row count all this who is handling Oracle is handling it we haven't defined any cursor here right so this is called implicit cursor implicit cursor you can use implicit cursor so for this kind of the way okay so so sometimes they might be using percentage found percentage not found all this see don't get surprised so these are cursor attribute if SQL percentage is open means it's a implicit cursor if we are defining any cursor name so that cursor name we have to Define okay so these are all implicit cursor we are not going to yes SQL percentage is the name for the implicit cursor this is like implicit cursor so what is explicit cursor so that is what we have seen here so we are going to make the explicit cursor now from this base table we have to take the explicit cursor so how can I take explicit cursor so that we will do it now so how can I do it if you see this I hope you're clear what is this implicit cursor in order to make explicit cursor you have to go for it's a user defined cursor right I told it's a user defined cursor first you have to do the declare cursor open the cursor fetch the cursor the values to the local variable and close the Sor okay so how can I declare the cursor the Declaration part where should I do it so I have to do it on the Declaration part you can do decare cursor on the procedure or on the functions okay anywhere you can put procedure or on the functions or Anonymous block also how can I do the Declaration part so this is the first one so how can I declare the cursor so this is the syntax cursor is the keyword cursor name is Select statement so you can use V class but this V class can have more than one record okay if you see this CER C1 is Select employee name from employees but I'm not using any W class here and we are taking this see this is called cursor variable it will store the data so in memory it it will open the cursor variable C1 so this private area we will call it as C1 now this cursor now it is called C1 got it cursor if you're making that declaration part cursor C1 one work area will be opened here in the name of C1 cursor C1 is this is the keyword okay select employee name alone I'm selecting if you want you can select more number of columns also we will see one by one then open cursor F cursor name okay so you can put fetch cursor name into variable whatever the variable you want put you can put here like EMP name or whatever VMP name variable 1 2 3 whatever it may be so this is what see here I will take this particular example here this is one of the uh statement and I will use it you see this so what we are we are writing so type okay we are writing declaration part yes declaration part so I have given here the explanation you can see here first I'm opening the cursor so first I'm defining cursor C1 is Select salary from employees okay so I'm not using any V class here I'm just selecting employees alone employees alone from this base table so just I'm taking this salary from this base table to this plsql block yes I have first declared it declared the cursor C1 it has been here it will be C1 it will be named it will have the salary alone now it's not the first so if you want you can have salary phone number whatever it may be and begin So within the begin and end I'm going to open the cursor so here I'm just opening the cursor whenever you are opening the cursor don't forget to close the cursor if you're opening the cursor and closing the cursor in maximum okay maximum in one particular session you can have okay if you if you take this go to admin schema in admin schema you have some two different views if you see this okay if you want to check how many cursors are opened now so one view is called this is a dynamic view the system will Main we are not going to maintain this view that is why I'm writing it here an admin schema so if you open this select star from in admin schema you how to run it so if you open this you can able to see all the type of the parameters so whatever the parameters you can the session has opened you can able to see all the open parameters here but I want to go for name here is open cursors okay so I want to just to check open cursors how many cursors are opened here you can see maximum you can go with 300 300 cursor you can open it in the one particular session okay maximum cursor you can open in one particular session 300 only if you are opening more than 300 cursors in one particular session then we need to kill okay so we have to kill that particular see maximum number of cursors per sessions so that's what you can see this open cursor is maximum value is 300 it will be maintained in one particular table called parameter so maximum how many cursors you can open this is for Oracle LG So based on the server it it differs you can go and check it in your realtime project also if you have access read access you have just to check it this this will be handled by the DBS if more number of cursors are open then the plsql code you'll be getting error like the cursor opened is exceeded so that's what you will be getting error then the DBA team will close that cursor so unwanted cursors they will close it so that's why Always you use close the cursor whenever you are opening just close the cursor here okay clear and just open open the cursor right we have opened it so declared it opened it after that you have to do the fetch values to a local variable so fetch cursor name into salary here right VM salary okay just first record I'm taking this will take first record okay first record so what is the first record here 24,000 it will take next one second record 17 record third record we are pitching one by one okay we are fetching one by one so do I need to if I have hundreds of record do I need to write 100 times like this no no need to write okay so dbms output if you can see here three times I have written it is taking the data so it has opened the cursor has been opened all the data it has been moved to the session variable all the salaries has been moved to the session variable but you are assigning one by one okay take one record so take one record fch one record to this value fch one record to this value so three times only we have used so that is why we are getting three times value has been printed here okay so 300 open cursor in implicit cursor okay so both implicit and explicit cursor both you can open it like 300 okay per sessions okay so this is what so once open we are closing it we are coming out but we need to get all the values right not only not only three records we need to get all the values so instead of using this way instead of using this way we can go for looping we can use looping statements so what is the looping statement here okay say for an example here I can after open the cursor open C1 and you can go for something like looping okay so here you can use Loop so you can use Loop so open then Loop whenever you are opening the loop before closing the cursor you have to use end Loop okay so use end Loop and no need to mention multiple times here remove this okay looping so looping so whenever you are doing looping you have to put this one right you have to put exit when so exit when when I have to exit exit when C1 percentage not found then we have to okay we can put here okay it will come out oh sorry okay if you see this I just opened the cursor I'm just looping it why because I may not know how many records in the base table contain right see it it may have some 50 records 500 records or 5,000 records whatever it may be so I have taken all the records here then fetching all the records into the loop one by one okay so first record it will go second record it will go third record and fourth record so all this when the cursor not found the attribute not found it will print the values and it will enter the loop and the close the cursor so you can see here so I just uh closed it I'm just opening and you are seeing all the values right see how many times if you want to put the row count right you can put row count so C1 you can put okay so after this you have to put end of the loop after that you put row count before closing the cursor you have to use C1 percentage row count right so how many records okay so 105 records you can put total number of total number of record which from base table B table conation then you can put here I okay so just we are making that and this way if you want you can put one more line also dbms output simply I'm putting one line that set so finally it will print this way can see here is printing total number of record fed from the base table is 105 so these many records it has been fetched from the base table got it so this is what you can go inside the cursor and you can fetch the values whatever I have explained here so that I have given here okay so you can use this way example two this is example three yes okay so here what we did we did only one column right we have did only one column salary I want to go for multiple columns multiple columns right so you can use this way okay you can use this way decare first you can take the cursor okay cursor C1 is select first name comma salary from employees you can take begin and end within the beginning and end I'm opening the cursor looping so whenever you are making looping then the end Loop F C1 into vemp name vemp salary so what is these two these two are local variable to use these values inside the plsql block got it if I want to use these values inside the plsql block then I have to use the local variable these two local variable I have to Define right okay VMP name is this first name data type VMP salary is salary data type so that way I have defined here so these two are variable and this is local variable this is cursor and exit when that are not found and we are going to The Loop so this time I'm just printing both EMP name as well as salary both I'm just printing it okay so if you want you can put one single codes yeah this way also you can open it and this time it'll go for multiple times so it will print employee name and the salary here multiple times if you want the same way if you want to put the the number of record it fed if there are millions and billions of Records then what kind of Performance Tuning we can apply okay so that's what it will not millions of billions of record it's a Memory location that's it okay will take the data from this memory location so you need to have enough memory that's it to take this memories place right this place so cursor you have to once you are going to close it this memory area will get closed this cursor will get closed say for an example next one so what is this next one this particular statement will do so take this next one next statement just to go through you tell me so what it will print just to go through that even if it is wrong that's fine take declare begin end so just we are opening the cursor here C1 looping okay Loop and end Loop fetch C1 into my name my employee number my salary three different variables right three different variables we are pitching fetch C1 into this three variable so here if you see this fetch C1 into this two variables we put it and this is three variable exit when see 1 percentage row count is greater than five okay greater than five so that means if I'm going to fetch more than five record I'm going inside the loop one by one fetching okay first record fetching second record fetching third record fourth record fetching okay if the row count is greater than five or I'm not finding any cursor then you can print this come out okay exit when we are writing this condition exit when C1 percentage row count is not okay what is this my salary my salary is nothing but employees. last name do last name you are taking sorry my name is last name my employee number is employee ID my salary is salary here all the percentage type right so we know already so cursor we are defining so what is this cursor we are defining cursor C1 is Select statement select last name employee ID salary from employees order by salary descending okay so what is this statement will do what is this statement will do so we have seen only select statement right but here we are using order by class so order by salary descending order okay so we are ordering the salary descending order then we are see here uh 1 lakh first record is 1 lakh we are ordering like a descending order right 1 lakh next one is 50,000 you assume that okay some values are there then 45,000 in that way we are ordering 40,000 some values are there we are ordering 25,000 okay and then sorry then 13,000 you assume that in that way 13,000 these are all some values and so on you are getting so we have we have fetched the values from here to the work area and we have stored the data in the descending order and we are going with one by one all the records and then we are in the descending order we are going with if it is more than five we are coming out that means highest five SED employ right correct so it will select okay highest five salaried employee the employee salary one if you want to put some count number also you can put here okay highest one employee this name employee ID this one this much amount he is earning okay 1 2 3 4 five then this coming out of the loop got it so just how we are iterating the values inside the cursor yes the same way we will do it in our plsql block so remember this if you don't close the cursor what will happen if you don't close the cursor if you are not closing the cursor I'm just opening and I'm making that okay so this time it has been ended right but the cursor is not closed cursor is not closed so don't use this always close it so why because if more than more cursor is opened in this way so multiple in the same session multiple queries are running then you'll get some errors okay you have to always close the cursor so remember this then say for an example you want to know how many cursors are there in the HR schema how many cursors are there in the HR schema you can execute this particular statement here open cursor is one of the views or dynamic View can execute here you can find in HR schema so these many cursors are opened in this particular session okay so that means I have opened this SQL Developer this so This SQL statement I have executed today right select start from customer yes I have I have executed this one I have executed right parameter I have executed select salary from employees yes so that is one of the cursor we have defined so this is May arle has used it name salary so first first time we have selected only salary next time we have selected first name comma salary and this is the way we have done it right so these are all the cursors we have used today session cursor catched it's both these two cursors are open so other cursors are be closed so that information you can see here okay but these things what is cursor catching all this the DBA will take care we are not going to use it okay so if you want to if you want to learn more than that yes you can go to learn more than this okay so what is this cursor session all this okay yeah I hope you are clear so what what this will do and cursor with return class okay so cursor with return class cursor with for Loop solve this one by one we will see so what is this cursor with far Loop see whatever we are doing it like one by one we are doing it right so that you can do it with cursor for Loop okay it's a best method to fetch the data from the base table you can use for Loop for an example if I want to learn what is cursor for Loop and this is what the syntax so there are different types of cursors so this one we have seen bul collect and these are all the different types of cursors the cursor we have two types one is implicit cursor another another one is explicit cursor in the explicit cursor we do have different cursors so one is the named sorry the named cursor that we have seen right so this is what we have seen so far named cursor and we do have cursor for Loop so that we will see parameterized cursor and Rift cursor so this concept we will see rest of the sessions okay so named cursor only we have seen we have opened the cursor we have fish the values and all this cursor for Loop that we will see now and ref cursor this is nothing but reference cursor it will do the same whatever we are doing it here same operation it will do so we do have four two different RI cursors one is nothing but strongly typed RI cursor another one is we L typed ref cursor by default if you are not defining any return type it will take weekly typ typed R cursor so those things we will take it later okay I will tell you so these are all the different types of cursor and we have seen implicit cursor already that we are we are not handling it see you have some 100 records here 100 records here it is looping one by one it went up up to 100 the next record what it will get next record it will get not found right cursor not found this is the cursor is the place see this is my cursor right this is the cursor of my system the same way in system it will point the cursor see one by one record record is not found it's what you will get it are not found four attributes we have so first three attributes are bullan data type Boolean data type the fourth attributes are it will return the count of the values see this is the base table you have multiple records from here you are declaring the cursor here we will declare one work area here right open the you are going to open it then you are fetching all the values from the base table to your plsql work area here your memory locations so this is what the cursor fetching the values one by one you are iterating the values right and then if it is not found it will iterate one by one if it is not found it needs to come out of the loop right needs to come out of the loop that's why we are making C1 not found so when it has to come out if you don't use it what would will happen if you don't use it what will happen F C1 into all this it will go right it will go out of out of range it will go for right so it is Raising some exceptions buffer overflow why why because it is going out of the loop out of all the records one by one all the records it's making it is not going to the next record is going to the always last record it is not coming out of the loop so you have to come out of the loop okay so we have seen cursor all the cursor types and everything so just we'll go for next one is called uh cursor for Loop the same thing we can achieve by using the cursor for Loop so in the cursor for Loop we can go for a composite data types okay so we can Define multiple multiple columns and we can go for composite data type what is that so this is syntax of this cursor for Loop so remember always this Syntax for this is record index okay for so normally we'll put it like a record record or record index in this is the keyword okay for record index in cursor name looping statement Loop and end Loop inside we can go for any executable statement okay clear so the cursor name we are defining it here and this is what you have to do and this is like inside the inside begin and end but before that we have to declare the cursor okay so how can I do it all this we'll see one by one so here I'm going to use so first I'm going to it okay so declare begin and end okay so within the begin and end I want to fix the data so for an example here I'm just defining the cursor how will you defign the cursor uh for an example I want to take phone number salary from the base table I want to take phone number and salary from the base table cursor C1 okay the cursor name you can put any name is okay this is the syntax cursor cursor name is Select statement so I'm going to select select phone underscore number comma salary from employes got it so what is this this is a declaration part after declaration part what you have to do here after declaration part we have to open switch close right so instead that instead of open close and all this you can go for for the cursor for loop it's very simple you can use for the record index you can use record or record whatever it may be can go for rec okay so if you see the Syntax for recording index in cursor name in C1 then you can take Loop so Loop end Loop right so take Loop and end Loop so here we have to write some dbms output you can write dbms output here okay I want uh this one right the r do phone number phone underscore see it's very simple no need to define the data type and all the columns one by one here local variables and here r dot salary so this is nothing but R is nothing but composite data type okay so if you see this this R is nothing but composite data type composite means multiple columns are there that's a composite data type Okay so so end Loop no need to close the cursor all this so just you can execute so this is the okay I'm just uh making the phone number I will put one iph here if you want to put salary here you can put salary single Cotes okay so this way if you're going to execute you'll get cursor for Loop okay salary for this employee is nothing but 6,000 right salary is 6,000 and phone number then put the same way here phone number of the employee you want to take phone number online then you have to take uh salaries another line it's how you are taking that okay you have to Define phone number single codes so you can Define it okay phone number you are taking the phone number here and salary here so if you're using cursor far Loop then no need to open switch and close it so automatically this far Loop will take that cursors okay if you see this this is the cursor for Loop if you want to go for a particular Department yes you can use particular Department here so this one I'm going with verer with parameter a cursor with parameter and we are using some some parameter here say for an example here I'm using this particular statement you assume that this particular statement okay so what the statement will do this particular statement will do so first I'm taking declaration part these two are variable you know already these two we have seen already variable I'm just defining two so one cursor is for Department ID 30 Department ID 30 another cursor is for another select statement so here I'm using two different statements even you can have this is two different tables also okay this is one table this is another table you can use so this one is selecting Department from 60 this one is selecting Department 30 is two different cursor so begin open cursor Loop unfit Department ID 30 we are we are looping it and we are getting it right in Loop then close this cursor whatever we have opened just we are closing it and again we are closing one more cursor then we are doing it right so we we we have two different select statement so for that select statement we have used two two different cursor we have opened and closed see here so this is the department ID so if you want you can put one more dbms output you can see you can mention that okay this these datas are from Department ID 30 these datas are from Department ID 60 so we have printed all the values here so how many cursor we have used two cursor we have used two different cursor but instead of using this way you can go for cursor with parameter okay so cursor with parameter so whatever we are defining here for an example it's very simple if you use this way the same statement how much line we are typing instead we can go for this way this is cursor for loop with parameter instead of writing this way two cursors we are writing right so what is that this way we are defining two different cursors so ccore Department 30 ccore Department 16 so instead of writing this way we can write the cursor by using parameterized one so how can I write so declare declare begin and end so inside the declare I'm just declaring cursor okay just to take the cursor cursor name is Select statement right select salary okay from employees where class you have to put where the department ID you have to pass it where Department uncore ID equal to whatever the department I'm going to pass as a parameter okay say for an example I want to pass the PRM underscore D number so this is the parameter so this parameter I have to pass it on this cursor here I have to use it's a department number as the number data type so this way I can use the cursor with parameter so why because instead of using select first name salary from employees where Department ID equal to 30 instead of using this way whatever I'm going to use the select statement here okay the select statement here and this select statement this select statement here we are we are passing Department ID 30 and we are getting all the values so instead of that we can use single cursor with parameter so if I'm going to use C parameter 30 is open this one then it will take 30 is Department if I'm going to use C 60 then it will take Department ID 60 here okay so it's very simple we can use it okay how can I use it's very simple first we have declared the cursor then what you need to do open cursor right so we have to open the cursor with parameter I'm going to open the cursor with the parameter for an example 30 see open the cursor okay so with parameter 30 I have opened open the cursor so after opening what I have to do open the I have opened the cursor I have to fetch the values so how many records I have to fetch all the records which are available on the department ID what we are passing here okay so can I go with Loop yes you can use Loop and endloop right same thing I'm going to use so open cursor I can take this one dbms output okay these two lines you can take okay so I have to fetch it right yes I have to use fetch cursor name fetch cursor name into what is the variable this select salary right so this variable you have to use correct so I have to use Vore Sal I'm just defining as number data type okay so this value this particular V salary so Fitch C1 into V salary is it clear it's similar okay so opening the cursor with the parameter 30 just looping and looping just fetch cursor name into P salary exit when Department okay this is not the correct one exit when C1 not found then we are making only V salary we are not taking here right this is end Loop then after end Loop what I what should I do I have to close this cursor whatever I have opened the same way I can open one more cursor with 60 that's it you can just copy it and paste it if you want to go for something you have to write it here before going for the loop so this is the data for Department uncore ID 60 right 60 so I'm just giving the hardcoded value in real time you will be passing through parameters so these values you can pass it through parameters okay before looping do it otherwise what will happen it will go for number of times how many number of times it is it is uh going for that many number of times it will print so you have to close the cursor close C1 so this end is for this begin okay I think we have defined correctly okay if you see this we are getting this is this is the data for department number 30 1 2 3 4 5 six six records we have and department number six 60 we are getting 1 2 3 4 5 five records we have right so but here you can put C1 percentage row count why because we have opened and closed it right so how many records so total number of Records in department number 60 you can put it if I want to go for one more time for one more uh one more Department then you can copy this and paste it one more time you can use the same use of the code okay this is called cursor with parameters cursor with parameters yes this this is the one cursor with parameters this is cursor without parameters okay so you have two two types of data right yes we can use this way cursor name cursor cursor name parameter list is Select statement that's what we have written if you see this cursor cursor name the parameter list here is Select statement clear then we are using the same parameter list here in the v class then it'll be passed so I hope you clear so how to use cursor with parameter this is single table right but if I want to go for two different tables the employees and departments so that is what rep cursor will be used okay if it a single Department yes you can pass the only parameter here then cursor will be taking employees table say for an example I want to go for Department table can I use single cursor no I cannot use it why because this cursor is for employees table right I cannot use the same cursor for Department table so that department table number of columns are different I cannot use it here so that you can go for this is the same way cursor with parameter okay so C C1 number is the parameter name and this is a number data type is Select statement employees where Department ID equal to this number we are passing yes we are creating temp variable here so this variable will have all the employees row type okay full entire employees data it will be it will take a holding so for temp this is a variable name right in this is the for cursor for Loop and we are passing the parameter here previously we haven't passed parameter right cursor for for record in C1 see here for temp this is a composite data type in C1 we are passing a parameter that means we are passing only for Department ID equal to 30 so in that department 30 it will be printed with parameters okay so employee number employee name department and salary so no need to use the close cursor so why because we have used cursor for Loop okay it will do the same same thing okay it is doing the same thing selecting the data from this but we have used different method to take it so I hope you are clear so this is cursor par loop with parameters yes cursor with parameters yes that's what we have seen now and this is for one particular table this is for one particular table right we want to go for two different tables I want to use one for employees table and the other one is Department table this this one also I have given just to go through that this is for cursor for loop with parameters then we can go for say for an example I want to go for here we are using here we are using one table right but I want to use two tables so how can I use it so if I want to use two tables how can I use it can I use the same cursor I want to go for a department table I want to go for Department table cursor I can take C2 okay so if you want you can take uh C1 and I'm not using any parameter here cursor C1 is Select Department ID this one I I want to use two different tables there is no join condition also you assume that there is no join condition also select Department uncore name that I'm taking select salary from employees right Department name from departments so I'm not using here where CLA just I'm taking all the data cursor C1 underscore EMP cursor C2 underscore Department okay so this one will hold employees data this one will hold departments data right okay so I want to go for salary or Department name or whatever it may be salary Vore Department uncore name if you want to print Department name it is to where cat two of some 100 characters so begin so what you need to do so open the cursor C1 uncore for EMP right so I have opened the C1 departments this is the data for employees data employees table so this is the data for Department's table okay I'm not using any uh cursor parameter here open cursor just I have opened it here I'm going for the loop which C1 into V salary right yes it's correct V salary exit when not found we are just printing V salary ending Loop and close the cursor and we can open the another cursor this cursor we have to open and we have to Loop PCH this one into this particular variable right C2 Department into this one exit when C2 is not found here also okay not found and end Loop we are making Vore Department name see all the department we are taking okay close C2 one everything I have written correctly anything I have missed exit when this is not found okay first it will take all the employees data with salary and then it will go for Department data right so not7 times then 27 times it will go for even if you want to print the number of times it is going for the loop yes you can write the loop loop statement here okay so instead of going this way to define two different two different cursor for two different tables we can use RI cursor so what is the drift cursor see here we are writing the statement here right in the Declaration part itself we are writing the select statement so instead of doing this select statement you define the cursor here you define the cursor here then dynamically you can pass the select statement inside the begin inside the begin whatever the select statement you want so that you can pass it inside the begin and end so you you if you want to write okay if you want to write employees table yes you can write employees table if you want to write Department table yes you can write Department table got it so that's what you can write ref cursor so how can I write the ref cursor I will tell you so what is ref cursor it's a reference cursor reference cursor is a data type okay it is a data type that holds a cursor value in the same way that Vare 2 variable will hold a string value same way okay so how so how the vat 2 will hold a string value same way you can have ref cursor reference cursor also a reference cursor can be opened on the server and passed to the client as a unit so as AIT okay so it is not one single single records as a unit you can open and Fitch one row at a time and you can use the ref cursor inside the block okay inside the plsql block okay so reference cursor is a plsql data type whose values is memory address of the query so query work area so cursor is same way right work the memory address of the work area in the database and you can see here so how can I define the ref say I have given explanation just to go through what is the syntax of declaring the ref cursor so if I want to declare the rep cursor you have to use the syntax here declare type so this is the keyword okay so the these things you are to remember uh so how can I remember all this yes you have to do multiple times practice practice so then only you can able to remember in the Declaration part you have to declare see this is same like how we will do the data type how we have defined already bar data type number data type the same way the one data type you are defining type this is cursor variable name yes ref cursor this is the return return type see this is optional one this is optional one if you are using this if you're using this return return type then this cursor will become strong ref cursor okay if you're using return return type it will become strong type ref cursor any ref cursor which has a fixed return type is called strong ref cursor remember this if you are using this statement in R cursor then it will become a strong typed RI cursor if you don't use this then this cursor will become a weak typed RI cursor so weak Type R cursor are those which do not have any return type okay so there are two different reference cursors are there so that is nothing but strong Type R cursor weak Type R cursor okay so how can you declare it just this way you can declare it see here is this strong type or weak type it is a strong correct okay so why because we are defining return data type correct see if you are using mobile number mobile number it is Vare data type Vare of Vare 2 of some data type you are defining you assume that what is this mobile number is a variable name right what is this one the data type the same way this is is nothing but a variable local variable of type this type is nothing but R cursor strong ref cursor got it so type this one we are using the keyword ref cursor so this is the way we have to use okay so this form of cursor variable called strong type of cursor because the cursor variable is always associated with the specific record structure or type okay so how to do it so this this is what we will go for how can I go for rep cursor that I will I will uh we will do it here instead of going for two different tables where we can use one simple rev cursor okay so I will remove these statements first we will go for I will remove all okay so first we will take dear declare data type begin and end okay so I want to define a reference cursor so how can I define the reference cursor type so this is I can take ref cursor shall I take this way this is the cursor name right this is the cursor name type so type cursor variable name is Rev cursor that's it if I'm not using any return data type so this will become this will become a we type rep cursor reference cursor so what is it see why we are going for reference cursor so dynamically we want to assign a select statement inside the begin and end previously we have defined two different select statement right two different select statement we have written one select statement for employees one select statement for departments we are start coding the select statement here instead we can Define the cursor declare the cursor here within the begin and end we can use the select statement we can dynamically use the select statement okay so how can I take that record type we have to use right we have to use this reference cursor type I want to go for some employees data okay so I can use okay I will use R cursor underscore employees list of type this one if you want to use for departments yes you can use one more variable the same way okay so you can use whatever you want so employee type uh departments type so all this you can use on the same cursor itself within begin and end so within begin and end we have to open the cursor right so open the cursor you can take open the cursor name see it is uh it is giving the the reference right open cursor name for select statement see always you have to use how the reference cursor we have to open right if you see this I have explained here always you have to use okay okay so you have to use for so while opening you have to use this way see rev cursors are opened with and open for okay you have to use open cursor variable for statement open cursor variable for select statement I want to select uh first name okay so first name you want to select see cursor the select statement itself here only I'm defining I'm not defining in the Declaration part here itself I'm not defining here only I'm defining okay in the beginning and end only we are we are defining open then we can go for looping so I will take Loop and end Loop here Loop and loop fch into I want name right V first name this V first name I have to Define here you can use for an example you want to use V first name employees table that particular data type yes you can use it here okay so I have used here into V first name exit when not found we are defining the employees data here end Loop here open right close this one see normally we will write select into no need to use here so here itself we are defining the cursor right no need to use that in so I'm going to just execute for only for employees table the employees table it has taken okay now I will go for the same way for Department table okay this way I will go for Department table so open the same cursor you can take no issues but I can use different statement I can use Department name here from Department table Department table Loop into V Department name but this one we have to declare not found in Loop close it okay so before opening I will make one dbms output here we are closing the cursor here we'll make this is Department details single codes so I will take the same one I'll go for before opening I'll take this is employees details okay so I'm going to execute this department name from departments output statement change V first name so I'm going to execute now I'm not defining two different cursor in the Declaration part I have defined only one cursor okay I have only one cursor but this cursor I have used here okay with two different statements two different tables so that's what see but all this are doing the same type of operations only okay it depends on the functionalities you can use this is employees details so you can iterate all the data and you can get all the data here see this type of coding definitely they will write in realtime project when they are getting more data to iterate they will write this way so what it will do so it will open the variable here and then it will fish all the data okay next one we'll go for strong Type R cursor so how to do the strong type rev cursor see it's very uh simple again the thing is you have to make that's a simply a data type declare begin and we have to write this way ref cursor ref cursor normally if you if you are writing this way this will become weak typed right we have to write return return return data type see what is the return data type we can write we can go for employees percentage row type so what is this we have seen already this is it will take entire employees data all the row type it will take say for an example it will Define in this way so it will take this way so each and every columns it will take okay a data types and then this is the one right we can use this particular data type we can Define so employees list so reference cursor this is variable that's simply we will write the same variable right employes R cursor this variable I have defined with this particular type of this cursor and I will go for the fch statement same way you can Define open cursor for select since you have used entire table data type you can use star star here okay you can use star here it will fetch all the data from the base table all the columns it will store it in the variable here in the plsql block start from employees no need to use V class here so since we are using cursor you can use looping statements end Loop and inside the end Loop the same way we have to use this way which this is the same one we have taken into vcore employee name so which one I have to take employee name we are not going to take only one particular column we can take entire row okay so employee row exit one this is not found dbms output see this dbms output only whatever we want to have this column say for an example I want to have employee name right the employee underscore name you can put single codes Vore list DOT first name yes employee salary salary so this is the entire row right entire row first name then salary see all this you can take one by one all the columns whatever you want end Loop so after end Loop we have to close the cursor so don't forget to close the cursor okay okay some error we are getting oh we haven't defined this one we haven't defined this is nothing but employees percentage row type it is taking all the employees details okay one by one it is taking all the employees details okay I want to go for so can I use the same way I can uh can I go for Department stable yes but the thing is you have to Define departments row type here if I want to go for departments here I have to Define departments here strong type rep cursor I hope you are clear so how the cursors will be defined one by one all this in our previous session we have seen Anonymous block and from today we will start with named blocks so what is named blocks so for anonymous block we will not have any any names okay so we have to execute it and it will be come so it will be compiled and then it will show the result okay and Anonymous block will not be stored in the database it will not be stored but the thing is if you are executing the same same queries or or same logic every day you can compile it and store it in database okay store it in database as an object so every time so any select statement or anything if you're running so Oracle will first it will do the parsing so parsing means it will check the select statement or any any statement it will check it converts these languages into machine executable language then it will check for execution plan and it will execute so if you're running the same query multiple times every day every time it needs to pass that query right so instead if you have stored as an object database it'll be very easy so that is what we can go with named blocks so we do have named blocks like a stored procedure so we have the procedure yes we have the stored procedure we have the stored function so you can say it's a function right so you can have functions and we can have packages so packages and we can have the triggers okay so these four we are going to learn from today so first we will start with procedure so I hope you have practiced our previous class sessions like you should have completed cursor right we have seen cursor and all the control statements and everything so we can have this whatever we have seen so far like if statement looping statement cursor and all this inside these blocks okay so here also you can have the same thing and that is what the stored procedure stored function packages triggers will do so what is this differences named blocks you will see so stored procedure or function or packages or triggers are nothing but it is a precompiled object stored in database so you can compile it and you can check the errors and you can successfully save that object in the database after that you can execute multiple times okay so what is procedure what is stored procedure name itself it shows that it's a stored procedure right okay it will be stored in the database as an object so stored procedure is nothing but it is a precompiled object so we will compile it one time we will check the errors we will compile it successfully compiled or not we will check it then it will be stored as an object in the database so like a table views and all this right this the plsql object it will be stored in the database so you can compile once then execute multiple times okay that is the advantage of procedures and it provides reusability so that means as I told here you can compile it one times you can check the errors and everything no need to again and again check for the parsing and errors all this right so once it is compiled that's it and after that you can execute so procedures are explicitly executed by the user you can execute procedures or you can schedule it you can pass some parameters okay so we do have three types of parameters in parameter out parameter inout parameter so those things we'll see so by using these parameters only we can make the procedures Dynamic procedures so we do have two types of procedures in our one is static procedure other one is dynamic procedure see static procedures means it will not contain any arguments simply procedure alone it will have and it will it will it will display always the same result even you can have any database table you can write it but the thing is so if the database data if it is changing then result will change otherwise it will display the same always so we do have Dynamic procedures so this is what we will use mostly in our realtime projects okay so what is dynamic procedure Dynamic procedure means it will have an arguments so might be a in in parameter out parameter or in out parameter that's what it'll have so for any procedure it contains two parts it is very simple if you know how to write an anonymous block anonymous block we will go directly with the decare right instead the anonymous block if I want to give some input how will you give input input in Anonymous block you will use the Amberson symbol right Amberson symbol you can Define some variables so you can get this variable with some variable okay Vore a is one variable it's a number data type then you can assign some value then see the way you can get the value for this particular variable during the run time but here in named block you can go for parameters or arguments we'll call it such a parameter AR argument if you are providing the parameter then it will become a dynamic procedures so this is the syntax of the procedures I'll tell you it's very very simple so it'll contains two different part one is proced procure specification and procedure body okay so two things it will have what is that procedure specification and procedure body so specification is nothing but this is the procedure specification you can have and if you see this it's very simple you can go with create procedure so one way you can write simply this way create procedure so this is a keyword whatever you are seeing in the blue color it is a keyword GRE create procedure procedure name okay so sometimes you will call it it's a proc name it's nothing but it's a procedure name then you can put whatever the argument we want to pass and then so is and begin so you can use is or as you can put anything begin so begin and end this is what we will write see here within this is and begin we will write variables declaration all this variable we will put so here we are going to have within that is and begin we will write all the Declaration part but this is the specification okay so procedure specification then what is the meaning of r replace so what is the meaning of r replace so if already procedure exist okay if already procedure exist then you can use if you want to change the procedure functionality you can put R replace so one more time you can execute on the same name and then it will get it will get replaced so that's what you can use so create or replace so we we cannot write create or replace table table name right but we can write create or replace procedure name of the procedure argument and you can give so what is the input parameter or output parameter so that you can mention that in means it is input parameter what is the data type of this parameter so that we have to Define here okay so this is the syntax of the procedure syntax of the procedures see here create R replace so this one is optional one R replace is nothing but optional one so create or replace procedure procedure name and you can have list of parameters we can have parameters here it's we will call it as argument okay like argument means we are passing the value right so that's at parameters also you will be passing like values for the parameter you can use either as as is anything you can use okay so it's R if you're using this way this is R condition so anything you can use it defines you are starting your body of the procedure so this is the body of the procedure inside you can have the Declaration so whatever the variable you are going to use here so that variable you can declare here and begin and end it is an executable section here and you can have the exceptions so you can have the exception handling the exception we will see is separately how to handle all the exceptions now you can have this this is the procedure okay so how to write procedure create or replace procedure procedure name and list of parameters is declaration begin and execution exception exception handling exception handling is like optional one so if you want you can use it otherwise you can leave it so normally the try catch block you you use right in our any other programming language we will use try catch same way here we will use try this if you're getting any exceptions you can handle it here that's what we'll use exceptions okay first we will start with static procedure so what is a static procedure so if you see this is is nothing but it marks the beginning of the body of the procedure okay so that's what in between is and begin that's what we will write all the Declaration part okay so here we can have this say I'm going to write create or replace and first I'm not using any replace you assume that I'm just uh using this create procedure procedure name as begin just inside I'm just using only dbms output welcome to plsql session just I'm making this F okay so end that's it it is it is similar to your animous block right but instead in the procedure if you see this procedures so as of now there is no procedure like greetings right so here there is no procedure like greetings but if I'm going to execute one procedure will be created so if you refresh here on the left hand side you'll be seeing this procedures right so it will have only this particular definition that's it it's compiled and it will be stored here and whether it will be ex Ed no will be compiled that's it see procedure will always compiled it will be compiled okay if you want to execute it you can execute this way excc procedure name that's it so you can use this way excc then procedure name so it doesn't have any input parameter right so you can go and execute then so it will show you the result so whenever you going to execute so that that time only the procedure will be called and then it will be executed see here and you can execute multiple times you can execute multiple times here so it will be executed so this is static right so always it will give you the same result is it static okay so can I create same procedure one more time no I cannot create it I will show you name already used by an existing object so that's what we will get it but I want to modify here plsql procedure session something like that I have to modify how can I modify either you have to drop and recreate instead of drop and recreate you can go for create or replace if already exist if already exist it will be replaced so welcome to plsql procedure session so just I'm writing this way so if I'm going to compile it so it will be compiled then if I'm going to execute whatever here I have updated so that will be Ed now so if you want to drop this procedure so you can make drop procedure procedure name so just you can make this drop procedure procedure name you can drop it will be dropped okay can I create procedures with table name you have the table here customer right yes on the same schema can I create a procedure on the same name of a table or views or anything if I'm trying to create it will give you the error right so everything is like object so you cannot use a table name or say for an example views I'm going to use it so views employees undor V so in this name sorry employees uncore V right so this is the one if I'm trying to execute this will also throw an error saying that name is an already name is already used by an existing object okay so all the object will be stored in one place so it will be like a like a primary key and you cannot use the same name of your table or views or any other objects you cannot use it on the same name okay even for function also you cannot use the same name of your tables and Views so this is one concept here create or replace procedure procedure name and all this so draw procedure procedures see if I want to execute the procedures say for an example I have executed this procedure right so I'm going to use contr C this procedure create a replace I'm going to execute this procedure has been executed okay but we are using replace option so that's what replace you can use but you cannot replace other object name okay other object name you cannot replace it if already existing procedure it will replace but other object it will not not be replaced so if you have created a customer as a table can I create customer as a view can I create customer a procedure no we cannot create right we should not create it that object should not be replaced correct that is a different object so that's why we are getting that Terror so I hope you are clear and that that is what it will not be created so okay I have created a procedure so how can I execute this procedure normally you have to use simply in real time of project also if you are calling any procedures in Informatica Power Center or anything so just you can use excc the procedure name that's it okay you can use so different way you can execute this is one of the way so just execute if you have any input parameter you have to provide this is one one way you can use execute okay so completely you can put execute and procedure name so this will also be executed or you can go and execute in the anonymous block okay so begin and end with so this is begin and end and you can use the procedure name here that's it okay so this way also you can execute a procedure so if it is a even if it has any argument yes you can pass the argument here you can use begin and end can use this way even if you're going to C call the procedure in plsql post SQL or pre SQL or anything so this is the way you can call it so what is a different way one is execute procedure and excc procedure that is also execute only and within Anonymous block you can call it even inside the procedure also you can call multiple times okay you can call so execute procedure execute procedure name this way you can call okay so how can I use some parameters see we do have three types of parameters so there are three types of parameters are available so what is the parameter see one is in parameter so in in means passing a value to the to the procedure okay so you are giving a value to the procedure run time so just you can use in and then you can pass a value at the run time so whatever the value you want to pass so you remember we have used cursor right something you have used cursor or Anonymous block anything we can use but we have collected some employee ID or some other information Department ID by using Amberson symbol instead you can use input parameter that means you are giving a value to the procedure next one is out parameter so Out means you are getting a value okay so you are getting a value from the procedure so that's what you can go with so input and output parameter the other one is in out so inout means the same variable okay same variable or parameter can be used as input as well as output okay so that's what we have three types of parameters so this is the three types of parameters we will use in our even in realtime project also so do we have any limitations limitations on the number of parameters no you can pass any parameters runtime run in the runtime mostly like wherever you are finding some something like art symbol in real time project it will be passed as a parameter okay you'll be getting this value on the run time might be a table name or batch date or today's date something like that you'll be getting from the uh so whenever you are running that so that time you are getting so batch date what is the batch date if you are running a batch on this particular date so that particular date will be passed so that is what see that is dynamic value even in parameter also you are not passing static value so today is today's date tomorrow's is tomorrow's datee will be passed and Depends all this parameter values are Depends and it will change dynamically so if you see this particular procedure so what it will do it's very simple procedure create or replace procedure procedure name so I'm taking one more procedure if you want you can use the same liting itself okay so what is this see always use parameter the parameter name here and define whether it is a input parameter or output parameter and what is the data type see here while defining the data type no need to mention what is the length of that particular data type okay so precision and scale no need to mention Vare 2 date or number that's it okay no need to mention Vare 2 of 100 no need to mention Mar to of 200 and all so instead instead of using hardcoded data type here even you can use any other database table percentage type so instead of using hardcoded data type you can use percentage type as well so percentage type we have already seen how to use it and so yes as as anything you can put begin and end just we are taking that one procedure okay dbms output and I'm giving some hard coded value hello and I'm giving some person name so whatever the person name I'm using so that particular name I have to print it here so even how many times I have to print so that is also I can use by using looping statements yes so how many times you have to print the value so previously you have seen like something like in the looping statement okay we will give the value as some hardcoded value right instead you can use as a procedures and you can give in this way so I'm going to execute so this will be compiled and if I want to execute this you have to use CC procedure name you can put the procedure name here and within bracket you have to pass value okay so it's a Vare right you can pass within the single codes so you can use single codes so you can use anything here okay so any name you can use it here I'm going to execute this okay it will be printed with this so whatever I'm passing here so that will be printed here and this hello it's a hardcoded value so that we have passed okay so if it is Ware I'm not giving a Vare I'm just passing like this okay whether it will be executed if it is a number yes you can pass this way but here it's a character right if I'm going to pass this way it will give you error in this way so what is the error you are getting whenever you are getting error you can see here OA this is article predefined exceptions will give you some numbers starting from 0 to one it is like article predefined one so above one is orle user defined exceptions so user defined exception while coming I will teach you what is this number say that and here we have the line number so where is the line number here itself you are getting thereor and column number 21 okay so here the wherever you are passing this value so that is that is what you are getting one and encounter the symbol when expecting one of the following okay it is expecting something like this one so that is what it is getting error okay usually compilation error we are getting that and it is expecting something like double codes or single codes so you have to give always single codes or double codes you can execute like this and it will be executed okay so can I give double codes here so here see I'm using double codes here so what will happen even if I'm going to give double codes is showing that it should be declared it is taking like a declaration okay variable so don't give that so always if it is a v care give it like single codes okay so date if you're giving yes what is the date format you are defining the same format you have to give or while giving the date format you have to define the format so this way you can execute in parameter so this is very simple procedure right but we will use the procedures more and more okay so I will tell you how to use it this is input parameter so I'm giving a value right what is that this is input so that input will be taken here that will be printed can I take output from here can I take output from here yes you can take output from here so whenever you want to take output from here say for an example if I want to take output from here so this way I'm going with so what is the procedure will do so what is this procedure will do just I'm creating one procedure total one so total or addition whatever it may be so I have to pass one number here input parameter number to another one parameter and total I will get it as a output parameter so number number one is number here number two also number and total is it's also number but total you have to get it outside create or replace procedure procedure name so in and output parameters how many parameters I'm using three parameters out of this three two are input one is output so we have to remember this as I'm am I going to use any VAR here no I'm not using variable just I'm I'm finding the total so total equal to N1 + N2 this is equal is nothing but assignment operator total equal to N1 + N2 so just I'm using N1 and2 so I'm going to compile it so what is the result I will get it what is the result I will get it so that result you can get it as the out parameter here out parameter here I'm going to execute this so it will be compiled how can I execute this see directly we cannot execute since it has output parameter okay if I'm if I'm going to use the say excc and procedure name and I'm going to give some numbers two numbers can I use this way no I cannot use it this way since it has output parameter it will show you that wrong number of types of argument see why you have to pass three arguments right I have passed only two arguments so that's what you will be getting error the third argument you can pass it like a any value like something like X but x what is it you have to have to inform arle that what is this X so X is nothing but it should be declared right if you're passing anything any anything other than this N1 N2 or total so that should be declared you cannot use this way so if you want to call this you have to go for anonymous block so that you have to use it so how can I use it so this way you have to use see here this way you can use or I will tell you the other way okay the other way two ways we can use so what is that first way yes so begin and end you can call this procedure total one I'm passing 7 comma 8 comma X so what is X here x is nothing but number I'm just declaring here so that X I'm printing here the total of two numbers are this one so this is dynamic value right 7A 8 is Dynamic value I'm passing what is this total one total one I'm not declaring anything from here so that is nothing but a procedure which already we have defined we have already executed and I'm going to execute this way it will give you 15 so for an example I'm changing the numbers from here I'm changing the numbers here then it will so only I'm changing the input and output based on that it is changing right so this is the way you can call the procedure this is one way the other way is the other way is you can use this way say for an example here in the procedure itself create or replace procedure procedure here inputs number y to as number as total one is a number begin N1 equal to N2 this way you can pass and what is the output so here itself I have declared total so what is the output I can get so that should be a even you can call excc directly you can call this and you can use but here I'm not using any output parameter so why because I I have called this one here itself okay so data type is already defined again why should be declare output data type where I'm declaring this data type is variable we here using right that variable okay whatever the variable here I'm using so that variable I'm using Okay instead of total I can use add additions so what is this addition so that I'm defining here it's a number then addition I'm placing so here if I'm going to use it will give you the value you can give some other number also okay so I have defined only number so but you are getting yes value are getting right so this is the way you can Define it it's a different different wa but here I'm not using out parameter just I'm using only two input parameter so that input parameter itself you can go here but if I want to take anything out yes you can call the anonymous block and the block you have to use okay can I use same same parameter as an input and output yes you can use if you see this create R replace so I can use create R replace procedure procedure name this is procedure name inout multiplication I'm passing one value here so some number it is the inout parameter so input and output a number okay so both we are passing so it is not in space output okay in out so not in this way this way normally we'll say it's a in out parameter so this is this parameter we will call it as it will act as both input and output as I do not have any declaration part here I'll go to begin So within the begin and end I'm going to pass x equal to X into five so this five I'm just giving hardcoded value if you want you can give any other number also here okay something like so what is the number you want to pass you can go for a a is number input parameter X into this a how many times you want to proceed see whatever X say for an example X I'm passing like 10 this 10 will be passed here 10 into 5 50 so that will be stored on the same varable aable X so x equal to X into 5 it will store on the same variable it will be replaced and this will be passed to the out also on the same while coming it will come value as 10 while taking as an output it will take value as 50 it will be multiplied by this number and then it will take then how can I call this same way you have to go for since it has output parameter to go for anonymous block so how can I use the anonimous Block it's very simple declare begin and end within the begin and end I'm passing inout multiplication x what is this x if you want you can directly pass value here 40 here but I'm passing x equal to 6 so here I'm just declaring so that X will be passed here this x will be passed to the above one and the 6 into 5 it will be multiplied and 30 will be printed here so if you see this if you see this this alone you might be thinking x equal to 6 in out multiplication so I'm passing X here and here so it is not printing like six it will print 6 into 5 so 30 30 will be printed here and see this even we are calling it from the the anonymous block right instead you can go for the procedure also why we are getting some error we'll check before that I haven't compiled here that's why we got error and this this time if you see this has been compiled and you are getting this value so in this example we have seen if you are seeing in is nothing but input parameter if you seeing out so that is the output parameter if you seeing in and out both are there then same variable can be used as a input and as as well as output create or replace process procedure procedure name so what is the procedure I'm creating total salary for this procedure what is the input parameter I'm passing I'm passing employee ID as the input parameter okay so if I'm going to pass employee ID as the input parameter for this procedure it gives the total salary for an example in this select star from employees table in this table you have the salary the commission percentage so for each and every employee we have to find out what is the total salary or whatever the employ ID I'm passing here so that should be given here okay so how can I do this how can I find see here I'm just writing this so I will get the input employ ID from here I'm using select state inside the procedure it's very simple select salary plus salary into nvl of commission percentage comma zero so you know already why I'm using this nvl if commission percentage is null I'm going to use zero to replace then I'm adding with salary otherwise you'll get null value after finding this calculation I'm inserting this value into one variable so what is this variable this variable is local variable so local to the procedure right so without that I cannot take it inside that inside the procedure I cannot use it from employees so I cannot use directly this so why because without cursor I cannot use the select statement right so without cursor if I want to use it I have to use V class where employe ID equal to whatever I'm passing here that will be passed here for an example I'm passing 104 here so 104 will be passed here so for that it will find Total salary is 6,000 that particular 6,000 will be stored here with a number data type so that salary I'm just printing here okay I'm going to compile it okay so how can I execute this do I need to use anonymous block or I can use execute so directly I can use execute or Anonymous block you are to use yes EXC procedure name total salary you can pass it here bracket you have you to use some employee ID you have to use this employee ID okay so 106 you that 106 I'm passing just I'm passing this procedure successfully completed it is giving you the value like the total salary of the employee 106 is 4,800 whatever I'm passing here just I'm printing here this total salary of the employee the input value whatever I have passed it I have taken here also is this one so this is very simple right to get it say for an example I'm going to pass something different number what will happen this 306 is not available in the employees table okay you are passing this this value what will happen it will throw an exception so that is what it is throwing exception no data found from the objects right no data found you are getting see this is one kind of exception if no data found so we have handled it in the exception right so that exception we have to handle here the exception you have to put but this exception concept we will see later say for an example you want to pass Department ID 20 so for the Department ID 20 you have to get all the values so what will you do have to define the cursor inside that see this way you have to use so here here you can use where class see this is f each statement we are writing instead you can go like this so instead of decare you can use create or replace procedure procedure name so this should be followed where class followed by Department ID see always remember whenever I want to go for multiple rows have to go for the cursor okay so we are already seen this right so this way you have to use cursor and you have to use exit when not found if the record is not found it will come out of the loop okay this way you how to write so what is the Pro if you see this procedure you can see very small small only we are writing then we will go for something like a if statement case statement looping statement all this inside the procedure so that time you can able to understand okay so first you go to the procedure create or replace procedure procedure name something like proc one I'm just giving okay employee number is the input parameter it's a number data type P is the output parameter so it's output parameter is nothing but it's a employees table entire table I'm taking that's a entire table I'm taking okay percentage Road type is and begin is and begin I do not have any variable to declare just I'm going here select star into P from employees where employee ID equal to employee number so here I'm just passing the employee number so I'm going to compile it so it will be compiled right so I haven't declared any variable I haven't provided any P variable I'm not displaying here anything but here in the anonymous block okay and the anonymous block if you see this declare employ number as number temp so this is employees row type I'm using begin employe number equal to 105 so proceed one employee number temp this temp will have entire percentage row type right so from this temp variable I'm taking that employee ID first name and salary so these three I'm printing for this 105 employee will print all this I can pass this different value the run time then I can get a different value here so if it is if it doesn't have any number if it doesn't have any value inside then so you might be getting error so that should be handled by the exceptions so exception I will take later so this way you can use percentage row type entire row if you want to take yes you can take en row so whenever you are running the procedures so where it will be stored so here it will be stored as an object okay if you see this total salary this is like invalid so if you see this something like invalid here so why because in the total salary procedures we are writing some select start from employees and execute total salary all this we are passing right so that's why you can remove this you can compile it now then if you refresh okay it will become valid one right so these are all the procedures are available as of now okay in real time project you will not able to see this right the Real Time Project you want to check how many procedures are there are you want to use some metadata tables so you have one table called all underscore procedures okay you can execute this all underscore procedures here okay so all underscore procedures we have all the these are all the metadata tables yes you can take procedures okay these many procedures are there 13 procedures are there it might be a procedure or function so you can see here okay so you are seeing 13 right so why so here the object type is procedure or function or Trigger or packages everything it will display we have any package no that's why package is not showing see all the four named blocks it will show you so triggers procedures functions packages so packages we'll see tomorrow or next week but this is what it will show you okay procedure and functions so in this particular schema how many procedures are there and all this details so you can see here on the all underscore procedure so one more table is there user underscore procedures okay so user underscore procedures also you can check here same way so both are like same table only so there is no differences of this and you can find procedures function all this and you can use user procedure is nothing but whatever the user you are going to run so that procedure alone it will show you all underscore procedure means other schema procedure also you can able to see here see here all procedures it gives all the procedure system procedure also showing right but user procedure it will not show you that all the procedure it will show you only for the particular user what is the user HR schema yes HR schema user procedure alone it will show you okay but how can I get the source code of the procedure so you want to get the source code of the procedure then you have to use all Source the one of the table okay this table will have all procedures data you can executes say you can see here allore source so it will give you this text here so you can take the take text from here it will show you so all this so what is this allore Source but it will contain all the procedure Source but you have to go for only whatever the procedure we have written right so that we have to go for so if you use allore source code it's a source code here we are taking only text where order equal to HR and type equal to procedure and name equal to add job history or any name you can put here whatever we have written so just take and execute you'll get this procedure code so how many input parameter we are passing 1 2 3 4 5 so if you see this procedure if you see this procedure are you seeing any input parameter here are you seeing any input input parameter here so actually you have to replace this double codes if you are not seeing any input or output it will automatically takes by default it it takes us input parameter so I will replace this so what is the procedure we do procedure add jav history we have to pass employee ID start date end date job ID Department ID all this we have to pass it will take what is the data type here it will take job history table employee ID that data type it will take here we are not defining a data type hardcoded value so runtime what is the data type of this particular table contains it will take so better you you have to use schema name also here in realtime project don't forget to use the schema name and sometimes if you have multiple databases so you have to use database name do schema name. table name do column name so but here I'm not using whether it is a input parameter or output parameter so if you if you don't use it will take it like a input parameter only okay if you don't use it say for an example here I'm not using this one as the input parameter I'm not using as an input parameter okay just simply I'm going to execute this will it be executed so I'm not using any input parameter yes it will be executed if you want to execute so you can use procedure name you can pass some person name so you can put any name so here you can put any name here so it will be taken as input parameter okay so if you don't Define by default what is the parameter it will take it will be input what is the type of the parameter is the input parameter it will take but if you want to Define yes explicitly you can Define as in parameter got it like why why do we need this procedure or functions or whatever it may be if you ask okay what is the get employees all this if you're asking the in real time project for a particular project you'll be writing some transformation logic you'll be writing some transformation logic if you want to write a customized transformation logic or customized business Logic for our project in in plsql code in SQL code so that time you'll use procedure so procedure with cursor this one we'll see now see here I'm passing this one where row number less than or equal to three instead you can use it for this way I told right I'll tell you this so I got one question do we have any alternative to cursor see alternative to cursor cursor is nothing but as I told if you want to go for something like a looping okay so you want to get more data from uh base statement base table then we will go for the cursor right if if want to go for instead of cursor if you want to go for anything else you have to go for B collect so that we will say after some time what is the difference between create or procedure versus S there is no differences you can put anything okay so you are asking I think what is the difference between is and us right you can you can use anything say for an example here okay so I'm using instead of s I'm using is so that's what I told already so both are R function or functionality you can use this right so where I have explained yeah here so anything you can use no issues so this tells that so within that is are begin it's a declaration part okay is r as defines that okay it is starting the Declaration that's what you will go for the Declaration part both you can use okay so even if you're using is you can execute this one it will be compiled it will be compiled there's no issues okay so now we will go for so how to get the uh employee details if I'm going to give uh for a particular table I want to get like three employees okay say for an example three employees I have to get so create only three employees or departments whatever you may be you can go for create or replace so this is something like you are you are going to to create or replace I'm just writing the procedure procedure name is I'm going for the some get employees okay Gore employees so this is the procedure name I'm going to write I do not have any input and output parameter and Is So within and this and begin if you want you can declare any variable you can go for the end so this is what we will write within that you will write so how can I use the cursor so how will you use the cursor here so where will you use cursor in the Declaration part right so you can use the cursor so how will you declare the cursor so cursor C1 is Select statement right so I want to select I want to select the name of the employee and salary if you want you can uh get phone number or whatever it may be so I'm just selecting first name salary from employees where I want to go for it's like uh I'm getting I'm not passing any value here where row number less than or equal to five so five employees I want to get okay so five employees so what is this first name and salary so that we have to declare here right so Vore first name yes it's a variable uh you can go with employees table DOT first name percentage type right what is the salary here same way so why because I have to print this Vore salary same employees table salary data type so I have declared it but instead of declaring first itself you can first write the code and then you can write so how will you start the cursor first of all so what will use after cursor declaration what you have to use to go for open right open and close within open and close you can start the loop loop end Loop correct So within the loop and end Loop you can use the code fetching Fitch C1 right C1 into first name that is what we have used here we first name that's it right exit one C1 percentage not found okay so here I can go with the dbms output dbms I want to display just dbms output _ line I have to put employee name this name I'm passing here employee name and same way I have to pass employee salary V salary okay see what is that I'm going to do you can use okay for an example top five salary employee you want to take yes you can write the select statement here order by salary descending right can use where this one order by salary so the statement if I'm going to execute this alone I'm just executing I'm getting these five salaries of five right 1 2 3 4 five okay just I want to display top five salary employees data on the procedures so here I'm just using order by class Okay order by class this is one of the statement you assume that okay and if I'm going to compile it procedure compiled that's it right so how can I execute it excc get employees that's it I'm not passing any input parameter just you can use execute see here see top five see if you want to print if you can print it this way whatever the way you want to print you can print it here so can you modify this procedure whatever I have explained I have given us an exercise here itself can you modify it now just to try I have to get the Department ID from the user okay get employees Department ID from the user so how will you get so how will you get inore Department uncore ID it is a in parameter what is the data type you can use number data type instead of using like this you can use this way also not salary here you can use Department underscore writing okay so this way also you can use create uh previous one you are clear right get employees see this one is available here you can if you want you can check it here so how to use this where Department uncore ID equal to inore Department right you are getting the first name and salary get employees is it correct is it correct or not yes get employees you can pass the department ID here in 30th Department how many employees are there select star from employees where Department ID equal to 30 you can ask me so why you are writing this much complex procedure if you're writing simply like this then it will give you the result right okay there are six employees are there it will give you the result why you are writing complex see this is not only for this type of issue so we are passing this as a parameter the runtime can I use this way can I use instead of this how can I the value is run time okay it is not only simple SQL we are getting the value from the table and we are processing it okay so I'm going to execute the statement now see I have to get six records this first name I have to print and their salary should be printed so you can go here it will be printed are you getting it see one time only we are executing right if you are passing different one 60 I'm passing So based on this department ID sorry I think I have closed based on the department ID you will get this value so even if you want to go for okay how many employees are there first you have to print number of employees then all the employees their salary you have to print you can put some some table form form also here so you can if you want to format it yes you can format by your way so what is functions so this is also one of the named blocks it is very similar to plsql procedures and what is the function what is this function and what is this function what is this function will do see all are functions right so what is these functions these functions are Oracle predefined functions right these functions are Oracle predefined functions see it is like a global globally if I'm going to give these are all the global requirement yes for all of them if you are going to give some string I want to take this okay it's a reusability I want to take from the given string I have to take the substring or from the given all the values I have to take maximum value or rank I have to take see these are all the predefined function Oracle already defined it if you open the function yes you can see the source code so how they have written so go for okay these many characters you have to pass and you have to take this value so you can you can see this functions how they have written so if you're using Su St Str this is a string right it's a substring su sub string of given string all this you are giving that means it will take from the given string it will take some string right you want to go for some string yes you can take from here I want to take yes I can go for substring see how they have written this one I have to pass some arguments here okay this is the string I have to pass okay so this many uh character I have to three to five characters I have to take so something like that okay 1 2 three you have to take yes it will take one two three so from first character to three characters you have to get these things they have already defined if you open this this function Oracle so they have defined okay you have to if they're giving from here start position from here to here these many characters I have to take so those things they have already defined Oracle itself whatever the functionality is common for all the users they have already created the article has created but if you go to a particular project if you're I'm going to particular project in the project I will have my own transformation logic right for the reusability so I will I want to find out tax calculation for my all my employees okay salary calculation for all my employees okay something different okay so you want to calculate bonus for all the employees something like that you have to calculate see this is my customized one for my employees I'm going for the customized one right I cannot expect this one from my Oracle database I cannot expect this one so I can write my own functions for my project needs this is also reusability purpose only but I can write my own customized function or procedures for my project see one difference is if I want to execute function you can execute like in the select statement right if I want to execute a function how we are executing this function s statement right but I cannot execute the procedure on the select statement so remember this function you can execute on the select statement procedures you cannot execute on the select statement that is one difference okay so if you see this function is a named plsql block which is similar to procedure yes the major difference between plsql function and procedure function return always a value okay it will return always a value whereas procedure may or may not return a value okay it may return or it may not return you are using out parameter yes it will May return value but if you're not using it may not return a value right but function you have to always use the keyword called R okay without that the function will not be compiled a function is a subprogram yes that returns a value when it is called it is stored in database or declared within a block functions can return more than one value via out parameters functions can accept default values yes so any values this is the same way we will Define create R replace the keyword will change here function that's it function function name you can same way you can open the bracket here you can put parameter 1 parameter 2 parameter 3 in or out data type and you can close it you can use one extra keyword here return whatever we are returning here value so that return value data type you have to mention here if you returning a value here in the substring what we are returning we can return character we can return anything right so that data type you have to Define here okay return value so what is the return value yes you have to use is begin you can use the Declaration statement begin and end you can you write the executable statement here then you have to write return return value exception exceptions okay so you can have the same way what is this function will do okay what is this function will do it's very simple function so it's our own function we are writing fcor count is there any function like fcor count in Oracle no right we are writing that's our own function so are we passing any value to this no we are not passing any value return values number data type yes and begin we are using the Declaration part so this one you can do after some time select count of star okay we are selecting the count of star into some variable from employees return whatever we are using here that will be returned see how can I execute this function you can execute simply on the select statement so function should always executed on the select statement so you have to use this F count this way can you okay it will give you how many number of records are there in the employees table so always it will give you the same result this one our own function right whether we are passing any input parameter no we are not passing any input parameter so that is the way we can pass here an example if you want to pass so count why select count of star uh count of star has taken yes we are we are finding that how many number of records are there right see this is SQL statement this SQL statement we are going to execute this will be stored this value right so that's it so within the beginning and end you can use any select statement so that we can use it count of star it will give you only one record so that will be stored here any it will going to return only one value right can I use Group by here Group by if you use Group by uh can function return more than one value yes we can return okay as here I have mentioned right function can return more than one value bya out parameter yes we can do okay we will come to that can I use Group by here Group by Department ID I'm using Group by Department ID I'm passing like Department ID here so I'm going to execute run time we are getting error right exact fetch returns more than requested number of rows are you getting it now I think your question is why we are using the aggregated function yes even if you're using aggregated function in the select statement anyhow it will return one value only right but if you're using Group by it will return more than one value that is why we are getting this error got it if you getting this kind of error okay you can assume that okay we are returning more than one value what you are to do to resolve this what you have to do yes you can use cursor here okay I hope you clear yes this is what see now you are able to understand right if you are getting any error related to this in realtime project so that time okay you have to understand why this error is happening and all this that's why we are going with very slowly one by one okay even if you are going to have some complex procedure or function that time you should able to understand so what is this function will do create or replace function function name this is the input parameter I'm passing so return Vare 2 as V result so V result I'm storing here it's nothing but hello whatever I'm passing the person name and return this one and end so you can use simply end wherever you're starting begin and end or you can put the function name or procedure name whatever it may be so then you can use select hello function Open Bracket you have to pass person name so any person name you can pass here from dual dual is the Dy table don't use control enter you have to select this one and then you have to do see I'm passing only Nic but it is printing hello ni so from where it has taken the H it has taken from here okay so this is very simple okay so can I use the same way something like salary hike we'll go for uh employee ID I'm passing Okay so employee ID I'm passing as input parameter so what is the function we do create R replace function employee function we are passing employee ID as the input so employe ID is the input in number and here I'm passing return as number salary new salary begin select salary into V salary from employees where employee ID equal to this one if V salary greater than or equals say 10,000 then incrementing by 10 percentage else 20 percentage end if return so I'm going to execute this okay so how can I compile it select sorry how can I execute it select employee function get I have to pass employee number right from Dual this is one employee ID I'm passing 9,600 but how can I pass is it possible to use here on the employees table employee ID first name salary salary hike so is it possible to use this way instead of 120 what I can use what the employee ID from employee sorry from employees see this is what is this employee uh some new salary okay incremented salary you can use this way it's a column name see we you assume that we have already executed you forgot about this I'm just executing this forget about the procedure the function which I have already executed so just simply I'm executing employee ID first name salary and you may ask question what is this empore function right this is user defined function so what is this you have to check in the function here it's a user defined function go here you check what is this empore function and what it takes what it will return just you can give here oh sorry salary salary here and I'm going to pass see it is giving employee ID first name salary un incremented salary so where from this where where is uh taking the logic you able to understand now see this logic has been taken from the function whether this function is Oracle defined already no we have taken our own function we have we have written our own function for our project and we are passing as the input parameter from the table and it is calculating the salary for all the records you can store this result into one table if you are going for complex logic now you are able to understand right how this function will be used in realtime project if you are using some complex logic you are executing insert into one table okay some table you are writing table one you can use right right see whatever the column in the table one contains these columns it will get inserted over there and the calculation we have done it so from this you can take the reports got it so instead of every time to find the calculation here we have written already calculation we have already pre compiled so from the pre-compiled object you can take it so what is the advantage of functions so we can write our own user defined functions so what is the functionality of this one create or replace function salary hike same way I'm passing as the employee ID as the input parameter return is number Select job ID I'm selecting the job ID salary into V job V salary from employes where employed equal to this one case when V job equal equal to _ VP see whatever you are providing the employee adid that particular employee job is adore VP then if their salary is less than 20,000 incrementing by 50% else we are not doing any raise end if coming out of the end if when job equal to clerk then see like this we are performing see based on the job based on the salary we are incrementing some values see these type of calculations is the business logic so now you are able to understand see this job ID clerk is not available you have to use jav ID the exact jav ID here then it will be executed correctly so I'm going to execute this see the same way you can use select employee ID comma first name see actually you have to use correct jav ID here but I haven't used correctly I'm using only clerk there is no job ID like clerk then it will go to else part race equal to Z it will put then you can use salary here comma J ID this function name So within bracket you have to pass employee ID right from employees so it will be this will be calculated for all the employees this is one of the user defined function if you run this okay case not found see some issues are there inside the case that we have to uh check it actually okay this case statement we are getting some issues right so in the case something here I think so some issues is happening we have to find but this is what you have to run this and if you put one particular employee it will find but some other employees is not finding see so this employee is giving you the result but other employees it is not giving because of the the job ID it is not available there on3 yes 103 what is that we have to check select star from employees so what is that 103 see adore VP is working correctly so some other job is coming it is not working so that means some cas a statement it is not performing we have to go for uh else part so this is the one we are going for the leier non leier create or replace function is leier we have to pass here as the number return data type is Vare to it will return like whether it is a leap here non- leier the thing is we are passing here year is the number only select whatever we are getting the year just we are making here 1 February 2021 okay into this one DD m n y y y into this one right variable day if variable day equal to 29 then here we are taking DD only last day last day of the given one are you getting it last day of the 1st February 2021 or 2023 last day it will give you 28 if it is 28 it will give you non leap year if it is 20 9 it will give you T here I have executed I'll go for select the sleep here of 2023 I'm passing from Dual actually instead of year you can pass higher date as well it is a non leap here you're passing 24 it will give you assign reper so you can rephrase this particular statement to get the higher date from our employees table get the higher date of employees table okay so from the higher date you can take the year part alone then take this one same same functionality from where the date is calling see how they are calculating 2023 assume that I'm passing a value 2023 from the 2023 we are making 01 Feb see different log you can follow we are concatenating 01 F concatenation two care of whatever you are passing here it will be concatenated like this right yes after that we are converting this into two date DD m n i y format yes we have converted in this format from this format we are taking last day of the one last day what the last day of this one for 20 February February 2023 28th Fe last day of the month right 2023 from this last day we are making two care we are taking only DD DD part means date part alone we are taking so from here I'm taking 28 so if it is 28 I'm making that non leap here if this DD is giving you 29 then we are passing leap here we are not passing any see this is one way the other way is whatever the higher date you might be passing inside inside the higher date you can get the date part you can divide by four modulus you can use REM remainer you can use see different way of finding that is your your wish see this is what if you are able to understand what is last day what is two day two date it will do what is two car it will do what is dual what is IF function return else function all this are we have seen already right one by one we have seen already create or replace function function name what is the return return is data type okay Vare 2 it is returning why because it is returning Vare 2 only it is returning right a leap here yes return work to is within the day we are we are passing a variable then we are getting it say for an example Su St sub string of given string given string comma and passing only five five here R I'm passing 1 comma 3 okay 1 comma 3 what is this functionality called so there is a difference so here how many argument you are passing two argument here how many argument you are passing three argument right but function name is single function single function name right in this function you are passing two argument in this function you are passing three argument how it will work so if you are giving like this already you should have created a function like this right should have given one argument or two argument or three argument but here I'm passing in two way this will also work right this will also works how what is the functionalities meaning we we should have learned this concept in data structure it's called function overloading correct function overloading that that means you are giving same same function name with a different number of argument right same function with a different argument see if you are giving two argument one function will be called if you are giving three arguments another function will be called whichever the function you have already executed with two argument so that will be called here whichever the function you have executed compiled with the three arguments that will be compile called from here so this is called function overloading this concept you'll see tomorrow session okay but I'm giving you the heads up what is the meaning of at symbol before variables yeah those are parameters at emplo EMP ID means you are passing the value so those are parameters see this employe ID you are passing from outside the package say for an example function or procedures or whatever it may be if you want to call this e excc some procedure name okay employee hike salary hike something like that and I'm passing employee ID here right this one 2 is the hardcoded value I'm providing now I know the value but in real time how it will be passed this value will be passed like this at employe ID what is at employe ID that will be given the run time through the through some scheduler or or parameter files you should have some parameter files and all this so This denotes Dynamic value at the run time okay so we do have concept of dynamic SQL all this so that time I will tell you all this okay this is one more example of functions just I have created one table I'm I'm creating one table employ info so employee employee ID here it is a primary key first name and last name just I'm creating table called employee info and I'm creating one more table employee address details this is employee details and their address details we are maintaining it in different table so different table so how can I join these two table so by using this employee ID okay this employee residing this particular address right so for each address I will give address ID in this address this particular employee this particular City that's what we are maintaining the the relationship if you see this reference which one is the parent table if you give reference for this employee which one in this two tables which one we have to create first employee table or address details table which one should be created first we should create employee info table why because we are referencing here if you straight away if you go and create this table since you are referring this table it cannot be created that's why we are creating this table first and then second okay this table so okay so even I got one question from one of the student this kind of error okay if you getting this kind of error if you're finding this kind of error it is clearly showing that okay so always if you're running any statement if you're getting any error right okay just to look at this error okay this is what you can improve your knowledge so it's very simple showing name is already used to by an existing object that means we have already created able it has been already created on the same name you cannot create one more time right so even if you are not able to understand just copy this error Okay contrl C and Google it and find okay what is error so that is what we can improve our knowledge okay so now I'm going to insert some data okay two two records I'm just inserting right so two records for this employee info two employes ra and John so that I'm going to insert it and I'm going to insert their address here right which address they are look working at if you want you can make it like a different address okay so if you want you can make it like a different address here so I'm just uh two rows inserted I will insert another two rows I'm just committing it so finally we can commit it no issues and I'll go for some functions you see this what is the function will do what is the function will do creating a function get complete address okay so to get the complete address I'm creating the function what is this create or replace okay I got one question related to this one how to use alter procedure statement alter procedure means you want to alter the definitions alter the definition you have to use replace see normally alter alter function we will not do it we will go for create or replace so you have already created but again if you if you want to modify it that's why we are using or replace okay so create or replace function function name we are passing the employee ID as the input parameter even if you don't mention here input parameter error or it will it will execute see even if you don't mention here in by default it will be it will be considered as input parameter okay so remember the sometimes they will not give any input parameter here in here if you're are making function or procedure something like this if you're if they're not mentioning anything here you can assume that okay it's the in in parameter get complete address okay address is Vare right so just that that is the reason I'm returning thisat 2 no need to mention the length of the workat 2 just you can mention return work to and within this is and begin it is a variable variable I'm getting employee details it is a Vare to of 13 character I'm just mentioning okay begin to begin and end within the beginning and end I'm using one select statement that's it okay select all the details okay name Pyon it will take first name right EMP do first name what is EMP EMP is nothing but employee info we here we are making like lest name right so employ info what is address address is nothing but employe address details see there is no table like address there is no table like EMP whatever this is alas name okay table alas we have seen column alas right but this is called a table whenever you are looking at any join queries that's what you have to interpret okay first name you are getting and last name city state country and zip code the employee ID is there in different table you are passing the employee ID okay it will get joined these two values okay then you'll get employee address as the put right employee details employee details is nothing but the conation of all the values right all the values so if you pass this employ ID here you'll get conation of all the values and it will be stored as the employee details so 130 characters 130 characters even you can go for th characters no issues and if you see this how many characters maximum I can use where to 4,000 character correct if if I have more than 4,000 character how can I handle it log okay your log you want to send one particular log into one particular column job error log you have to okay this is a job ID this job has been executed status equal to fail if the status equal to fail you have to put the error log status equal to success then put your success log that should be in one particular column so how can I handle it if it is more than 4,000 character you are to use l large object okay so this is what we are selecting here and we are storing it into this temporary variable right this variable after that so select statement into this and we are returning it finally so you can use okay so if I'm not going to use end get complete address will it fail or it will not fail so what is this why we have to use end this one is it mandatory or without this also I can run so without this also you can run so why we are writing this way right just we are mentioning okay here we are starting here begin and we are ending this the end statement we may have so many end statement right uh end if end Loop so all this you'll have case statement all the end you'll have so we may not know if you're going to write a big quy right big plsql code we may not know which end it it is so if you're using lot of use statement case statement and procedures and everything inside the package you are using lot of end statement then we may not know so which one it is getting end that's why I'm using end here okay so even if you don't use yes it will run so I'm just compiling it so compile on time so get complete address compiled so how can I execute if you have employee ID in one particular table and this is a table right say for an example select start from employee info okay employee info this is a table right this table I'm just executing so what is the column you'll get you'll get first name yes first uncore name sorry employee ID you want right so I need employee ID first name then there address I do not want last name I need address so how can I pass here how can I get the address here just to take this address get complete address here so this is the function name so use the function name here you have to pass the EMP ID right so that should be that should be a parameter here right in parameter yes if you F this this EMP ID or the corresponding employee ID it will display the address right see here see how you are displaying it from where you are getting this this particular details you are running the statement from employee info but how you are getting the employee address so you can make address as the column name so we are not joining the table we are not joining the table right somebody has already did the code somebody has already did the code your customer is running the code okay your customer they they are running some calculation they want to do it they have employee ID if they are making the employee ID here it should calculate your yearend tax you assume that in that way they need to get yearend tax for every employee ID and their first name all this details they will send only employe ID as the input parameter that's it but how they are getting all the details here somebody else already return this particular code so that's what you will write the function see this function is called user defined function so what is the function it is called user defined function so our customized function so this function is not available in Oracle by default right so we have written how we will write substring inst string concate all this same way we have used one function called get complete address see even if you're if you're removing all this okay if you're removing all this you are saying your customer that in our project we have one function called get complete address if you pass the employee ID it will give you the address that's it we may not know what is there in the inside the this function right see that is the use of functions in real time project even if you want to run this way you can run from Dual yes from Dual if you're running you have to hard code the value right so just I'm passing the value in here but if you want to run this way also you can run it since it is a function you can pass it from statement this emplo statement okay and you can execute so you can simply mention that I'm passing the value here so this way also execute dbms output get complete address of 20 so I'm just passing this right see it will give you complete address of the 20th employee this way also you can execute same way we are having person person salary details we are giving the same way here we create our replace function we are calculating the tax some calculation we are doing it so inside but you can write this way okay say for an example select full name we are selecting and we are selecting the salary as annual salary and salary. year just we are taking which year and calculate tax so what is this calculate tax with a function name so this function name will be called and even you can pass this value since we are using this person right so one one what is this from which table it is coming so one1 it's coming from person so you can mention that person dot person table person do person ID that you can pass it here no issues person ID is there in two tables right so you have to mention person. person ID so if you are passing this way don't need to pass like hardcoded value here that's it so you can pass it if two persons are there you'll get all the two person name dual you can you can know use it dual you can use it no issues dual means you are passing the value hardcoded value right so here you're using dual du means you passing the value right you're passing the hardcoded value if you see this I go to the above one see this is what dual so what is the difference you are seeing here above one and this one you only can put but you you cannot put employe ID first name here so why because it's a dual dumy table I'm passing 10 as the hardcoded value here right coded value but if you have hundreds of employee ID or all the employee ID one by one we don't need to pass like this right you can run like this and it should be executed for will be passed in the run time this DML operations in plsql customer okay this table I will take so this table I will take we have to have the table create table uh table name customer customer ID it's a number data type customer name it's we to of some 30 and mobile number right mobile it's a number of 10 32 I think it's age it's a number and this is something like City ID you assume that okay so this is the one table I'm just creating just I'm missing right parenthesis where I'm missing okay here number okay so I will create this table I have created yeah you can so always if you're writing any table statement always follow this type of statement okay like this so even if you're going to give it to this uh ddl statement to your deployment team always give like this formatted one you you have to give always so just formatted and try to give okay uh this table we have created right this is anonymous block this is anonymous block if you want to use it uh stored procedure create or replace okay procedure procedure name insert customer this is a procedure name right then you are not using uh any variable here right you are not using any variable and just to begin So within that you are passing some values here and you committing and ending right begin and end you if so you can pass this values these values we have right see these values you can pass it if you want to pass in the runtime yes you can pass all the values in the runtime but here I'm just hard coding see if I'm going to send this value okay this value here as it is this way every time if I'm going to call this procedure then always it will insert this same data so we have to use SRS right so just I'm using it and uh I have created the procedure okay just I have created the procedure if I'm going to take this table select star from this table how many Recs will be there in the table can you tell me how many records will be there in the table so now I'm going to execute this if I'm going to check this one how many records will be there in the table if I'm going to execute the statement nothing will be there in the table why I just compiled it that's it right I just compiled it I haven't executed it right I haven't executed so how can I execute ex ex EC cure name so do we have any parameter no I do not have any parameter just execute this then only it will execute it will enter one record into the table one more time if I'm going to execute one more time execute it will in uh insert one more record right one more record one more record see you have compiled one time but executed multiple times multiple record will be created if you have if you have created this customer ID as primary key then you can handle it by using exception here got it now see how to use insert statement inside the procedure yes this is what see here but I want to make it like a parameter so what will you do the parameter same way you have to pass for an example here in customer ID right sorry inore C ID as the number data type sorry number data type next one what is this inore ccore name it is the where Car 2 data typee right where Car 2 then next one is mobile number right inore mobilecore number this is number data type comma okay say I will use this way see this is number then AG City ID so age incore age it's a number inore ccore ID it's a number okay so should I mention that input parameter no need if you're if you're not mentioning here that will be taken as input parameter only create or replace procedure procedure name all this see here if you want if you want you can mention here the customer ID customer name and mobile number okay so just I'm passing that I'm icking submit and if you want to handle a text exceptions so you can put exception just I have used it rate okay rate table table name I will use rank table table I have used so here I'm just executing it there is no data I have compiled already right I have compiled procedure has been compiled if you see this there is no data in the table but if I can I execute this directly no I cannot execute right so I cannot execute a wrong number of type of argument insert cust see how many argument I have to provide five argument I have to provide first argument okay this value then second argument okay this value third argument mobile number then some okay you can mention this age and City ID procedure completed successfully so we already committed here just take execute see it will insert right so you are passing the value here we are not writing insert statement and all just we are passing the values it will go and insert see you can execute the same statement with different records so this will be passed in the runtime also means as a parameter you can pass it you can put some parameter here the run time you will get the parameters you'll pass it here data will get inserted see this is what you have to use insert statement in the plsql procedure next concept okay so I will take this one this function so what do this function will do select substring of given string welcome to plsql session so I'm making here 1 2 3 7 8 9 10 11 12 so just I'm making 12 here from Dual so what the result we'll get it select substring just I'm using plsql session so here I'm passing this F 12 what the result you will get it so I'm passing here 12a 5 okay so 12 comma 5 I'm passing from from du see same function name right substring of this one so first statement what is the result second statement what is the result correct first statement we will get plsql session right it's very simple you'll get plsl session second statement we'll get plsl alone right same function same function only function name is same but number of argument will be different right see here you are passing two argument here you are passing three argument if you pass two argument then it will be calling different function if you are passing three arguments it will be calling different function these two function will be already there in the system see if you take this example the above one what do the function will do this function create or replace function add number a as input parameter b as input parameter return return type is a number c is number cal to a plus b return C so just I'm executing it compilation completed okay one more one more function I'm writing in this same function name I will use add number same function name I will use but I will use one more here C underscore number return D equal to number D equal to a + b + C okay a plus b plus C just I'm compiling it so compiled right has been compiled so now I'm going to use this way select add num 4 comma sorry 3 comma 4 comma 5 from Dual so what is the result oh sorry return d right otherwise it will return C see I'm using return D I have compiled it see I'm going to use this one so what is the result okay I'll get the data well okay I'm get I'm I'm running 3 comma 4 3 comma 4 insufficient input parameter okay but we have compiled right we have compiled a is number B is number two argument only we are passing also we have compiled already right has been compiled then it should return seven but it will not return seven it will throw error okay wrong number of number or type of argument right so that's what we will get error why what happened here see very first time if I'm if I execute it will be compiled okay it will be compiled right it will be compiled add number but if you are this number alone if you are going to use if you going to use this one now it will execute so why because I have executed this alone but after that I have same function name I have recompiled it recompiled it the same time I have again I have recompiled it it will replace so that's why if I'm going to execute this you cannot do it but I have to handle it this function overloading this is called function overloading any idea how can I use the function overloading see in order to handle this we have to put these two into package we need to put this into a package what is package function overloading see function overloading means same function name with different number of argument we are passing uh if you are passing here two different argument then it will be called different one three different argument it will be called different function this is called function overloading so what is package first of all so you are you are going to some some tour you assume that or you are going somewhere you're native or somewhere you are going so first what will you do so you will you'll put all your things into one single package right you'll bag you bag it and then you'll go right same way in project so we need to do some code we have to for the entire project we have to do some code so your lead is saying that okay okay so all of them don't work on one single uh component we will split this into multiple subprograms okay entire code we will split split into multiple subprograms like this okay so one person you work on this sub program one person you work on this one one person you can work on this one see for an example addition subraction multiplication division you assume that in this way see all the persons are different different we are working on it okay you compile it you say whether it is successful or not just you another person compile it you say it's a compiled or not so you are multiple persons are working in different different way and all after that after doing it you can make all this into single package so this is the single package we are making we are making all this into single package package and this is called package so you can write any subprograms here okay inside you can write sub programs anything package will have two different parts okay so what is that package will have two different parts the first one is package specification so you have to write the package specification your lead is saying that you assume that uh specification you are going with we are having these many subm modules we have to write he will write see all this okay so this is the procedure so this is the function this is the procedure all this we have to write three or four we will write it here just we will declare it okay you assume that it's like a declaration specification we will do it that's it so what needs to be done and then package body so inside the package body only okay whatever we are writing here whatever the package we have the procedure or function so that definition we will write it here simply we'll say it's a add number Okay add number or add multiplication something multiplication of numbers something like that we will Define only here declare here but we will Define it so what is it see all this we will write it separate separate module then we will compile it we'll check it so once it has been compiled and everything we will put it in single package so whenever it's like a modularity so whenever I want to call this one so no need to remember this package this procedure name so if you if you take this package name if you click on the dot it will list down all your procedure or function names inside the package okay so that's what we'll go for the package so if you have the package two different parts what is that the first one is package specification specifications you'll have so you have to declare here the variables functions and procedures just to declare it package will have two different parts one is package specifications another one is package body inside the package body you can write your functions procedures or whatever inside the function function you can go for listed function inside the procedure you can call the functions so you can write anywhere and you can handle it exception if you want even cursor also you can write it okay so anything you can write inside the function you can write see all this cursor all this you can go for C cursor everything you can write it this is what package will have so package is nothing but it is group logically related components okay logically related components so plsql this is plsql type you know right so vsql types and named blocks variables data structure and exceptions so you can put it and subprograms so procedure and functions yes the two are subprograms you can write it it has two parts one is specification another on this body okay so enable the Oracle server to read the multiple objects into memory at once so that's what uh the the performance wise so entire package okay it will read multiple object into memory at once so that's what it will do whenever we are going for any package calling and if you see this this is the components of plsql package so we have the package specification you have the variable here we are the procedure a so we just mentioning that procedure a that's it so whatever the variable you are declaring here so that should be a public variable it's like a global variable so if you are mentioning a variable here that will be visible to all the P all the procedures and functions if you are defining any variable here that will be a private variable okay private variable it will be private to this particular package body and if you are run if you are declaring any variable inside the procedures that is called local variable okay so you can Define multiple way so I will explain this later okay so here if you see this this is local variable this is private variable this is public variable okay package visibility we will see so that time you will come to know what is this if you see this so what is this this is called This is called package specification what is this create or replace here we will write the keyword called package okay we will write only create or replace the keyword called package package name and you know as it is like as an is or as you can put and public type so this is public variable that's why we are making that public type variable declaration uh subprogram specifications and end then package name so you can put the package name here so that you can assume that see this is package specification so you can do all this so if you have the package body create or replace package you will have one keyword here called package body okay just simply if you have the the keyword called body then that means it's a package body okay you see here this is package specification just we are creating one package called commission package so what is this variable standard commission first we are declaring like initializing the value 10 percentage in commission that's it it is the public variable just we are declaring and then we are mentioning that procedure procedure reset commission okay this is the commission right after that we are we are making some new commissions we are resetting it whenever we passing here yes so this is called package specification okay then we'll write the package body so if you're writing package body you'll be having a keyword called body here yes see so you'll be having a package then we'll be having a body package name and you'll be having a private type here so previously it's a public type right see here it's a public it's a global variable but this is if you want to privately you want to declare some data types yes you can declare here sub program bodies and then begin and end within that you can put all this so that is what you will go for package body you see this you'll be seeing the keyword here body of the package same name you'll use it here whatever the package specification we have mentioned here right here we have mentioned cmore package right the same name we will mention here what is this create or replace package body commission percentage is we are writing one function here up to this one function this is called one procedure how many sub programs we are writing two sub programs see here in the package body we will write create or replace package package body but in the the function the function right the function or procedures here we will not write create our replace are we writing any create or replace function function name all this no directly you can write function and directly you can write procedure that's it function function name parameters return if it is function yes you have to WR return we are return returning Boolean data type yes we are returning see this is what we are returning some some something we are calculating and returning procedures so procedure here the function name is validate procedure name is reset Commission that's it uh how to invoke invoke means how to call this see always if you want to call you have to make execute so you have to use the keyword called execute we have to go for this way okay execute package name dot the function name package name dot function name or procedure name anything you can put see this is the package name this is schema name so this is my first package I'm just writing first package here I'll go for here I will write create or replace package package one is this package body or package specification so package create or replace package package name is this package body or package specification this is package specification so here I'm not using any variable just I'm using two different subprograms one is procedure another one is function so what is the procedure it is simply one greetings one one procedure person name input parameter anything you can call whatever we have seen so far the function or procedures so that you can call here no issues see we have return after that first you can separately you can you can execute then you can bring it all the subprograms into your package body create or replace package body same name you have to provide here what is the package specification you have mentioned the same name you have to mention here as procedure here and whatever here we have mentioned okay procedure we have mentioned yes have to mention here and person name in Vare just I'm mentioning this okay hello I'm just making this one function so you can go for function two function three sorry any function you can write it here you are you are making that's why I told so multiple end will be there right so if you don't use end multiple end will be there so that's why you can use end what is this you are ending this creating one okay just execute it should be compiled yes we have compiled it both then if I'm going with you can use execute so you can use execute procedure or function name anything you can use first package if you're are placing this dot right see here this is execute dot if you're pressing dot it will it will show you whatever the functions or packages are available that means you are making all this into functions and procedure into one single package so that's what you have to use first package here and after that you can use whatever you want okay gretings one what is this greetings one that you have to know uh you should know so what should be passed here yes you have to pass some some value here as the character yes inside you can pass pass you can make okay so here I'm passing something like plsql so it will print right see hello plsql it's printing that's it so if you want you can use hello function also again function name so you can pass some some value here right hello function but this is the function here so you have to pass hello function here select statement right select from Dual that's it so it will print here so I'm just passing plsql but it has concatenated with hello so that's what you can call this this is very simple package very simple package see whatever here we are using so that should be used here so I go for one more package here see one more package employee function so this is also similar only this one we have already seen right see first of all for package you have to write functions and procedures separately then if you want you can take that so you remember we have seen overloading concept right first I will take create or replace package package name here package overload add numbers first I will take procedure first I will take one procedure add now is see this is the one a number B number procedures add number a number B number and C number so just I have compiled it's a package I have executed these two procedures both are procedure okay so I will go to the package body now see this is the package body I'm just writing package body I'm just writing so create R replace package body and this is the package name first procedure add number to argument I'm printing that the sum of two number are this one the sum of three numbers are right three numbers are this one so A + B + C just I'm converting into a character I'm just printing it so I will compile it has been compiled so now I will this is the procedure right exe is the one you can write the same thing with function also it's up to you so I'm going to use package dot add number right add sorry add underscore num I can use two different numbers here okay just I'm using two parameters only so if I use two parameters it will be called so it will call this procedure okay this procedure so I'm going to execute it it will give you the sum of two numbers are 11 okay same thing I'm going to make it like three okay three numbers or anything any numbers you can put same package same function name I'm going to call this it will print sum of three numbers are same procedure you can write it like a function next one forward reference see what is the forward reference here what is the forward reference in plsql statement this this one they will ask interview also forward reference what is forward reference in plsql declare procedure procedure one is begin I'm writing dbms output put Line This is the procedure one end okay declare I'm declaring procedure here within the declare I'm procedure I'm declaring procedure to begin and end so within the begin and end end I'm using procedure one right so what will happen if I'm going to execute if I'm going to execute what will happen which one will be printed so what is the output I will get it so this should be printed right this is the procedure one okay just if I'm going to execute it should be printed so this is procedure one okay so what I'm going to do now I will call procedure one here inside begin okay I will call this procedure inside this procedure one what will happen what is the result this is procedure one declared inside the procedure I'm calling but begin and end we are calling only procedure one what is the result okay begin and end this is declaration part right declaration part begin and end which one I'm calling I'm calling procedure one what is procedure one procedure one is still this one only so it will be called say same same result only right yes same result only so I'm calling procedure two what is the result I will get it so both will be printed right yes this is normal this is normal scenario okay so inside procedure two I have called procedure one okay now I will change it inside the procedure one I will call procedure two okay procedure two then I'm calling procedure one here so what is the result this is procedure two procedure two then one two and one okay okay first two will be printed then one will be printed see what I did first I'm calling I'm create I'm declaring the procedure one so inside the procedure one I'm calling procedure two then I'm printing here then here I'm declaring the procedure two begin and end inside begin I'm using procedure one see this type of question they will ask in interview I'm going to execute okay we are getting error why see why we are getting getting error procedure to not declared in this scope why any idea first we have first we have we have we are declaring procedure one inside the procedure one begin and end right inside the procedure one begin and end we are calling procedure two while compiling the compiler may not know what is procedure to what is this what is this P2 right so you may not know what is this this compiler may not know what is this see after coming into this compiler knows it okay this procedure Ro is procedure something like but it it cannot able to compile that's why you are getting procedure two is not declared in this scope this is what error you will get it see to a this particular error we can declare it before procedure PR two we have to just declare it see we are not going to do what is what is this PR two see we have used PR two here we are to mention PR two is nothing but a procedure okay so we are informing compiler that when very first itself okay wherever you are finding PR to that is nothing but a procedure we are giving heads up then we are going inside okay it will assume that okay it's like a procedure we may get data we may get the Declaration part this particular procedure okay it will assume it will not fail it see here it will not fail okay so now we are getting this is procedure number two this is procedure number one see this type of error message you might be getting in realtime project or they will ask you in interview this is called forward reference what is this this is called forward reference so you are giving the reference before itself you can use the same thing within the package also within the package also you can use see here create or replace package body you to use the procedure here same way okay procedure begin and end just you can use this see instead of deare you can go for package package also you can do so what this is called This is called forward reference okay so next one is called package visibility okay so package visibility see what is this this is the same thing we have seen Now package visibility see if you see this create package ABCs we are we are using one Function One procedure another procedure end package okay we have created one package this is package specification it's a global package variable public function yes this is global variable public function and procedures okay now we'll go for create package body ABC create package body ABC as something like we are declaring this variable is called private variable this is global variable public variable this is private variable if I'm using function one here just I'm using Function One procedure one and uh procedure two and again I'm using procedure two two here procedure one inside the procedure one I'm using procedure three right that is what we have seen now so will it be executed yes it will be executed this is not uh forward reference see I'm not calling this I'm just declaring it so this will be executed right but this procedure P3 whether I have defined here declared here no right whatever we are using inside this procedure P3 it will be a local variable okay got it local variable see inside procedure two we are calling the procedure three that's okay it will be executed so how we have seen the forward reference all this same way here also it be used okay so what is the advantage of packages see First Advantage is modularity so enh capsuling related construct So within single so you are you are putting the logically group element into single package for easy maintenance right easier application design yes coding and compiling into uh package specification differently body separately so you hiding the information yes so just you declare it and you can Define all this function inside the package body so two different uh modules you are using it so that's what you can maintain all the modularities and everything so we have already seen procedure function packages and everything we will see what is the difference between function and procedures when to use function when to use procedures and how the packages will be used realtime environment so packages we have seen uh if if you're going for any realtime usages so most of them are packages so within the packages we'll be having a subprograms right it might be a procedure it might be a functions so it depends on the the functionality they will write procedure or functions so if you take difference between functions and procedures so we know already the procedure yes we will always write procedure so procedure if you take procedure alone okay if I'm saying that stored procedure if I say it's a stored procedure stored procedure can be classified into three different stored procedure okay if I'm saying like a stored procedure it is not Al loone procedure okay it's a stored procedure so whenever you are going for procedure right and function and packages so these three are stored procedure so what is the stored procedure like it's a pre-compiled object stored in a database and we can call we can store it as an object and once you it has been compiled you can call this in multiple um multiple times okay so compile one time execute multiple times so that's a difference right so if you take procedure here then function here then procedure if you're writing any procedures so if you recollect the whatever we have seen procedure so procedure it will be used you can execute procedure by using execute procedure procedure name right so you can write procedure name here just simply will be so procedure one so you can if you want to execute even if you're going for any Informatica statements if I want to call any procedure in the plsql sorry in the psql or post SQL so you can give this kind of keyword execute procedure name this is the way we will execute but how can I execute the function if I want to execute the function you have to use the select statement right select say for an example function one so from so if I do not have any special table then no need to use this table name you can use the Dual so you can call this so if I have any argument for this function then you can can use this particular AR arent here then it will be used so user defined function we will write the function for our project and predefined functions so Oracle has already functions right single row functions aggregate functions analytical functions all the functions are available already so this is the procedure and functions so procedure you can you may return or you may not return a value but function you have to return definitely you have to return a value that is a single value value function you can write return return value but you have to write single value if I want to return more value you have to return return by like a array of values or you have to go for some collections so that's the way you can return but you can return only one value only at a time okay so if you take this procedure or functions so procedure if you take function function can be called in SQL statement okay so any SQL statement the DML statement select statement all this you can call this functions right but procedure cannot be called by using SQL statement so you have to use the keyword execute procedure procedure name right we have to execute we have to make this procedure name is execute procedure this procedure execute procedure name or you can go for anonymous block so inside the anonymous block you have to use the begin and end so within the begin and end we have to use this procedure name so you have to just call it so if you have any input parameter output parameter so you have to use so that's a way you have to use right so that so that's what we will use the function and procedures and functions so why we are using functions so normally it's like a user defined functions user defined function means I want to calculate tax so I want to pass the employee ID and their country okay some some details I will pass employee ID and Country something in that way or it will read the data from the table which country they are belongs to now and based on that the tax calculation we have to do it okay so what is the tax form and all this it should need to perform so we will write the functions accordingly so it's a user defined functions we are writing our own functions for the particular Project Specific okay so it cannot be done by Oracle right so inside the function yes you can call use the Oracle defined function predefined functions but we will write the functions here if you take this procedures this is for computational purpose so any computation like tax calculation all this computational purpose we can write this functions we can return a value and if you take the procedure so procedure used for logical data flow okay so you have to do this if statement all this right so you do this okay control statement looping statement and DML statement all this if I want to do like a procedure way then you can go for this logical data flow or procedural statement yes you can go for this one as I told already procedure may or may not return a value okay you can go for out parameter inout parameter these two only return a value right we can use out parameter in out parameter even if you're using Informatica stored procedure we will call it by using this input parameter we will pass a value to the parameter out parameter we will get the value from the procedure and we will process it in the informatical level the same way here also okay so if I want to take any value out of the procedure then you have to use out parameter and if I want to use you can use in and out parameter both if you don't mention anything it will take it like a in parameter right but function should always return a value if you don't write return it will compile okay it will compile but while executing it will fail okay so procedure can have DML statement so can I have DML statement inside the procedure yes we can have the DML statement you can go for update statement insert statement right we have written so we have written uh select statement insert statement update statement DML statement in Anonymous block DML statement in procedure we can write it but if you write function if you write any DML statement like uh insert update okay delete statement so you can write it but you cannot call okay while calling that select statement so if you write writing functions and if you're writing inside the function you are writing the DML statement we will see this these options okay so these two we will see how it will perform so I can write some DML statement in the procedures I can write some DML statement in the functions I will show you how it will be defined but if I if I use directly like DML statement it cannot be called it will compile but it will cannot be called you have to use autonomous transaction C if you want to use any DML statement you have to make that statement as child statement okay that should be a autonomous transaction you have to make it so here the procedure you you can use return keyword if you use return keyword it will terminate the procedure it will come out of the procedure okay simply if you're using return can I use return keyword in procedure yes we can write okay if you're writing two uh two statement one is for one uh code you are writing the other one is other uh keyword okay so if I'm going with two statement then it will it will be returned immediately okay but here return statement is used to return a value so that's what we will write so here we can write one keyword called returning so returning means again it's used for DML statement if you're going for any procedure and if you're using returning uh you can return the variable okay so from the procedure you can use the keyword called R learning so how to use all this we will see one by one so that you'll get clear idea okay so first if you take okay the statement I will show you I'm going to create one backup table okay or any table so for employees table I'm going to create one table called This is employees table I'm just to creating one table called employees uncore backup with this particular data so how many records are there 16 records are there and I'm going to create table table name so employees _ back just I'm creating one one backup table and I can I can show you it has 16 records in the table okay so here if I'm going to use this particular statement this is the procedure three create R replace procedure so procedure name so we are going to calculate new salary for that each and every employee and employee ID I have to pass it like in parameter so if you want to differentiate then you can make it like in capital employe ID I'm going to pass it like in parameter commission percentage we have to pass it like again it's a in parameter right so here I just mentioned like in but here I'm not mentioning in that means it's a in parameter only then I'm getting so you have to pass employe ID and commission percentage both input parameter then you will get new salary as the output okay since it has in input and output you have to call this particular procedure from the anonymous block only or from the another procedure only create or replace procedure procedure name then argument you are passing as begin okay so I do not have any any variable to declare between these two and I'm going for DML statement can I write a DML statement here in procedure yes we can write okay update statement if I'm going to write DML statement if I want to get the value okay so normally you can use so I'm going to call some update statement right update employees set salary equal to this one okay salary equal to salary where employee ID equal to this one whatever the employee ID I'm going to pass here that for the particular employee ID it will update the employees this particular backup set the salary equal to salary into commission percentage right so that means total salary it is calculating it is setting the salary so here for this salary so if it is null then 24,000 if it is 0.4 then it is a 196 19,000 something some value you'll get it if I'm going to pass this particular employee ID and the commission percentage okay new commission percentage this commission percentage will be calculated and it will be passed here you'll get value so returning salary into new salary okay so returning salary so what is the salary here the salary into new salary so this new salary it will be taken out it's a number data type okay so here we are using the keyword called returning so if you use any DML statement okay if you don't use this returning then it will update it will update in the database but you want to get so what is the salary you want to check it okay so for an example this particular employee I'm passing 24,000 and commission percentag is some 0.5 so 50% increment on the salary so 36,000 it should be you have to get it right so I'm going for this this particular um procedure just I have compiled it so you have to use the keyword called returning so if you use this particular one Anonymous block declare and just I'm using that function name the procedure name right so call new salary and uh it's a it has three arguments first argument is 102 it's like 102 then commission percentage is 0.1 okay so even you can call it like 100 commission percentage is 0.5 so 50% increment on the salary so out will be printed here so I'm going to make so view dbms output okay so I'm going to call this so what is X is a number yes it's out parameter right we have to Define here otherwise it will not be printed so that's what I'm using in out here so don't use control enter since it has the page has the procedure already use this way okay so it is printing 36,000 here so it is showing here right x value but I'm going to check here whether it has been updated or not or this see in the table also it has been updated with 36,000 right it has been updated and we are getting that return return value also in the DPMS output okay so if I want to get the value from the procedure then you have to use keyword called returning so this is returning okay so you can use return so return keyword say for an example here I'm going with so if so if employee id employe id in right this is the input parameter I'm just using IF employee ID is null then so then I'm going to use you come out of the procedure you can use return okay so if you are using IF in if say for an example I'm going to pass for the parameter I'm going to pass here here as a null value if employe ID is null value then here how it will perform if I don't use this one okay if I don't use this one how it will perform here the employ ID I'm passing n where I do not have any null value here then it will not update it will not return any value right but if I want to handle it you can handle this way something like if employee ID is null then you return don't do anything just return so from the loop you can just come out of the loop that's what you can go for return keybo so if you use return keyword then it will be return from the procedure okay so just I'm going to compile this one again one more time I will compile and I'm going to use if I use the 100 yes it will be used the value will be uh 30,000 it will make the new value right but I can go with null value so this way I can call I'm passing null it should not do anything right see procedure successfully completed we are not doing anything just coming out of this one so if you don't use here so what will happen so I'm not using here just I'm making that if statement okay so if statement I'm not using if statement so if I don't use this one just I'm going to execute this so I'm going to call this null value so do I have any null value null employe ID no right I do not have any n employ ID then it will not perform anything okay this value will not be performed any value also it is is not returning okay if I'm passing something like like a different number 99 it is not there so this is also should not perform any value okay so I I will give you one more example if I use return it will not do any uh it will come out of the procedure create uh replace procedure proced procedure name okay so procedure one begin and end so just I'm writing some dbms output okay so I'm just writing welcome to procedure one okay so line number one so line number two just I'm writing another procedure here so begin and end plsql uh so which one I have missed here okay use single codes not semicolon okay so here I'm writing procedure two so you assume that I'm writing on procedure inside the procedure I'm writing one line of statement any DML statement ddl statement or anything okay so based on the condition some condition it should not take this particular statement if you're writing this way if I'm going to call this procedure excc so proc one it will call both right if I'm writing this way it will call both it will print we come to procedure one it'll come to procedure two like two lines are there and I'm going to call the procedure now they should print procedure one and two right see here procedure one and two but if in between if I'm going to write keyword like a return okay so in between if I'm going to write keyword like return even though so after this return keyword if you're writing any other statement the procedure will not consider after the return ke it will terminate the procedure okay terminate the execution it will come out of the procedure okay so yeah procedure one see some connection issue previously that's issue but even if you're going to make it like procedure one it will execute okay so I'm going to used this way procedure one has been compiled and execute procedure one it should print welcome to procedure one only right so it should not print welcome to procedure two since we are writing the keyword called return it will terminate the execution of the procedure okay so this is the one thing uh you can remember whenever you are using return keyword so but in the function if you're using return keyword you can write right say for an example addition of two numbers we have used return keyword addition of two numbers add two numbers we have used uh Anonymous block we have used but here I want to write function addition of two numbers so this is the use of return keyword in procedure return keyword in function is different return keyword in procedure is different so if I want to write return keyword in function okay so how will you write create or replace function function name okay so addition of two numbers okay we can write something like addition is the function name I can pass number one number data Type M to number data type both will return PN return number data type begin end so this last class will terminate the procedure which the function okay you are intimating that okay this is what so I'm going for sum okay sum is the keyword I will use sum one or addition one addition equal to num one plus num 2 right two number I'm getting return add one so what is add one we have to we have to declare it right number data type so I'm going to execute it this function compiled then how can I call the function select this addition okay just I can call the function name addition we have to pass two numbers any number you can pass from Dual so here I'm going to call this function it is it is making this two so this return keyword in function to return a value but return keyword in procedure to terminate the procedure to terminate the procedure we will use the return keyword so I will I will put this as well okay return keyword in function sometimes they will ask in interview you should know the difference somebody is writing return return keyword in function return keyword in procedure so return keyword in procedure used to terminate the procedure that's it it will come out of even though it has hundreds of lines after that it will come out of the procedure okay so I hope you you are clear so can we write any DML statement ddl statement inside the procedure uh sorry function DML ddl we have used already in uh in the procedure can I write DML statement DL statement inside the function so I'm going to execute this function so I'm just returning some workare so create or replace function function name just one function name is called Welcome or Greetings or whatever it may be I'm returning varar to let begin return welcome so welcome is like it's called a static function always it will give you the static value right this function is compiled so this function if I'm going to call this function how can I call Select welcome from Dual right I can call so this way if I'm going to call this you'll get like a welcome right so this is so here I have WR the function here I'm rning the function the the or you can go for welcome to plsql function just I can write why because name both are looking like similar so I'm going for this one so if I'm compiling it you'll get welcome to plsql function so this way it will get return so this is the return keyword I'm going to return something like here I'm returning one value but can I write some DML statement I'm going to write some DML statement something the update statement I will write it for the same the employees table whatever we have seen I'm not passing any uh value so just I'm using update table name set salary equal to the salary return this return okay we can write it this way if I'm going to execute what will happen so we are uh getting something like invalid identifier okay this value is not there just I can use select salary salary equal to salary okay set salary equal to salary I'm not using this I'm going to call this function so if you use it will compile as I told if I use any DML statement inside the function it won't throw any error you see here it won't throw any error while compilation but you are going to call this function from the select statement then the DML statement you will get error cannot perform DML operation inside a query okay can I write like this inside this can I write some function can I write DML statement uh in the single row function or aggregate function or you are seeing the analytical function are you seeing any DML statement inside that but if I want to execute any DML statement inside the function we have to make this transaction as the child transaction so do you know how can I make this transaction as child so we have to use pragma autonomous transaction pragma autonomous transaction if you use pragma autonomous transaction you have to use the commit keyword right so you have to use the keyword called commit now if I'm going to execute function is compiled and I'm going to call this okay so welcome to plsql see you can put set salary plus 100 okay for each and every employee I'm going to make in the salary 100 this particular table before that I will show you select start from employees table if you take this particular statement you can see so here this is what the Sal for before this function okay then I have added I can add okay 100 is fine so I'm going to compile it okay so if I'm going to execute now so this is this has been called the function has been called and if you go and check here so you are getting this 100 added so that means this statement update statement has been executed right so if I want to write any DML statement inside the function make the statement as child statement transactions so how can I make this as a child you have to make pragma autonomous transaction and use this commit keyword so whenever you're using pragma autonomous transaction you have to use commit statement so this is one of the differences the the same way can I use ddl statement ddl statement any create table or alter table or anything can I use any ddl statement directly inside the function so I'm going to write some function create or replace function underscore ddl I'm going to return number dat type begin end within that I'm going to write some ddl statement can I write a directly ddl statement is it possible we have seen uh Dynamic SQL right so Dynamic SQL create table table name customer just I'm writing on table okay like customer ID it's a number data type just I'm writing after creating it okay so I want to create this table just I'm creating it so can I write this way so create create table table name so this way if I'm going to write it so you cannot write directly so we cannot write any uh even drop table table name customer okay so customer or customer One customer 3 okay there is no table called customer here I can create customer table itself I'm just using that I'm dropping the table customer one I'm just creating table called customer and dropping the table but can I use this way no I cannot use right so create you cannot use in the uh directly we cannot use then how can I use create table you remember create trunet we have used uh truncating uh dropping a backup table we have used remember so how can I use you have to use the execute immediate right like you have to go for dynamic SQL immediate you have to make this as the single codes so if you use this way yes you can call but still it will compile okay see here function function ddl has been compiled but I'm going to call this select from Dual okay so if you are writing any ddl statement if you're going to do it if if you're going to write it directly it will compile okay you can write Dil statement how we have written DML statement here DML statement some statement we have written and we can compile it but while executing the statement we get we are getting error right same way here if I'm going to execute we are getting error cannot perform ddl commit or roll back inside the function okay so remember we cannot do it then how can I call this as I have mentioned you have to use child transaction you have to make it like a child so inside the function so it's a ddl okay so you have to make the child transaction make it as child transaction how can I make child transaction that means we are segregating the transaction okay so how can I make this transaction a child so you have to use the keyword called pragma transaction pragma okay autonomous transactions then after this you have to use the commit right so after create it will be Auto committed but you have to use explicitly the keyword called commit so now if I'm going to execute after refreshing it this table will not be there but you'll have one more table called customer this function I'm going to call now okay so HR function ddl insufficient privilege okay so what I will do in this schema I will try to create so to perform ddl so even though I'm using like a pragma autonomous transaction I do not have privileges for HR schema it's a DD statement right that's why they're not allowing so we can run it in admin schema right so function whether here we have access okay system function no we do not have any privilege some dumy value we will return return zero okay so I have to return some value right but here I do not have any value to return okay so here I do not have any value to return just I'm returning zero so I'm going to call this still I do not have permission here execute immediate drop table table name okay so here uh this Express Edition we do not have permission okay so but this is what you have to use if I want to use the function this is what you have to use okay yeah in real time here we do not have permission but in real time if you want to use it any ddl statement inside the function then you have to make this so these are all the difference between uh functions and procedures so just to go through uh you will get some uh clear idea so what is this function procedures private procedure and uh public procedure so what is it so I'm going to write on procedure create or replace I'm going to create one package so sample package I'm just cre creating the package called sample package this package body or package specification procedure I'm writing one procedure here so procedure one here okay procedure one I'm writing one more procedure here procedure two so just I'm using two procedures here that's it okay so this is the procedure specification yes I have to write the body of the procedure right I have to go to the same way you have to use so here I have to use one more create or replace package body the keyword called body here sample package and you have to write so here I have to write procedure so just I'm writing one procedure here procedure procedure one as begin and end just I'm simply using one procedure here bbms underscore output output underscore Line This is procedure one public procedure this is called public procedure public procedure okay so you can write the same way one more procedure this is a procedure one and procedure two I can write so procedure two here this is also public procedure so why it is public procedure okay so if you have written the procedure in the package specification that will be a public procedure right yes so if I'm going to execute this these two are public procedure then if I want to call this then then how can I call this procedure I want to call this execute the package name if I want to write uh execute package or execute package name dot the procedure name can go for the procedure name here I'm writing the procedure name one here then it will print so this is the procedure the procedure one public procedure okay this way I'm just writing then I'm going to call the second procedure so this is also public procedure only so public procedure can be called in the package okay but I'm going to write one private procedure so here anywhere you can write no issues just I'm writing here inside this procedure I'm going to write procedure three this is a procedure three private procedure so what is the private procedure so here I'm going to compile it it will be compiled but I can I can call procedure one procedure two but I cannot call procedure three can I call procedure three no okay so you'll be getting error so we are getting error like procedure three must be declared okay okay this is called private procedure but this private procedure can be called inside any procedures okay inside the procedure can I call it yes here I can call see here private three uh procedure three I'm just calling and I'm going to compile it one more time so from from outside you cannot call it okay from outside we cannot call it any private procedure but if I'm going to call procedure two then this particular line will be executed after that this will be called so this is nothing but here we have return this will be called so you'll be getting two lines of uh statement see here this is a procedure two and public procedure so this is the way uh you can distinguish between private procedure and public procedure so whatever we are writing the procedure here these are all these are the procedure called packaged procedure so packaged procedure means we are writing inside the package right so that is called packaged procedure this is a this is not the package procedure this is called private procedure okay sometimes we will call it as like uh local procedure okay so I'm going to write some local procedure here so if you are writing any procedure inside the anonymous block uh in the Declaration part the Declaration part you are going to write any procedure that is called local procedure even you can write the local procedure inside the procedure also inside the procedure declaration part so how can I write Anonymous block decare begin and so normally here I can write some statement right so I'm going to write some procedure here in the Declaration part I'm going to write procedure okay so here I will take this procedure deare procedure one this is a procedure local procedure so whatever this one I'm writing the Declaration part declaration part declare procedure procedure one as begin statement you can write any statement here and so we have completed here then begin and end we are going to call this procedure simply so if you want to call it so do I know what is this no I not know but we have declar already so this is called local procedure if you can see here so in the Declaration part the Declaration part of the procedure or Anonymous block you can call it so even you can write it uh this way okay so this one in the anonymous block I will call so that you can differentiate local procedure in Anonymous block local procedure in the Pro inside the procedure also so how can I do it inside the procedure so create or replace procedure uh procedure name okay I can write procedure simply Ro so sometimes if you're if you're going to the your Real Time Project they'll be writing like this they're creating the procedure here and they're using it I'm going to execute this procedure not procedure one I'm going to execute this procedure excc procedure name right so if I'm going to execute it is executing this is called local procedure okay so you cannot write execute procedure one so if you have already procedure one that will be executed you have compiled but uh yeah say this is different one this not the correct one so already we have compiled on the same name that's printing but you cannot do it this way so you have to execute this way only so this is called local procedure local procedure inside uh Anonymous block local procedure inside the procedure declaration okay so declaration part you have to do it that's a local procedure if anybody is writing procedure inside the begin and end so that is called local procedure so in the Declaration part can be right procedure yes we can do it okay so I hope you are clear practice all of this in our today's session we are going to learn what is autonomous transaction so what is autonomous transaction first of all what is transaction so if you take this table okay so these statements so whatever we are doing it here okay say for an example I have table customer I will drop the table if I have the table customer okay okay so table customer is not available so I'm just creating the table so this is one particular transaction right yes after that I'm going to insert on data into this table I'm inserting it I'm inserting it see each and everything is like a transaction see one insert another one insert so all these are transactions so if you go and check this table so yes we have the all the four records if I'm going to commit so then only the transaction will get committed the database the other members say for an example I have did I did insert operations but without committing it I'm trying to close so what will happen right so it will say you like it will theor like core has been modified do you want to do the changes yes you have to do the commit or roll back so we have to do right so that we have to do see if you're if you're doing it see if you're I'm I'm trying to close this entire it will show you commit changes or roll back changes it is asking right so why because previously I have closed this window only but I'm closing entirely here then it is asking do you want to commit a roll back if you are not committing it other members log to the database they cannot able to see these differences this data only table customer ID customer name this alone will be available in the table I have to commit it or I have to go for roll back if I'm doing roll back so all this transaction will be rolled back right it'll be rolled back here it will be rolled back if you go and check here nothing will be will be there in the table see these are all called transactions right this these are all called transactions yes I'll go to the next scenario the scenario we have already seen okay we have already seen this I'm going this see I will create I have created the table right already created a table I'm just executing this insert statement with with save point so you can create save point before or after install statement anywhere you can create save point I just create I just created the save point after the insert statement after inserting the record into the table I just created a save point so what is a save point it's like a bookmark so if you ask do I need to create save point for each and every insert statement No need it's based on the scenario you can do the roll back or commit based on the save point okay so you can have a number of transactions okay number of transactions after that you can have the save point and roll back see if I'm going to do a roll back here roll back to B if I'm going to execute see here before executing I will show you how many records are there four records are there in the table up to here it is there if I'm going to execute roll back to B what will happen if I'm going to execute roll back to B how many records will be there in the table roll back up to B it will be rolled back it will be rolled back so you'll be having two records alone correct see this concept we have already seen our I have to execute roll back to B then after that if you see this then you'll have only two records so we are rolling back so this is called save point right save point okay but we are getting One requirement okay One requirement we are have we are having lot of lot of insert statement update statement and I want to make this statement alone should be emitted or roll backed is it possible the statement alone remaining other statement should get the roll back are committed okay this this statement alone or the statement alone a particular statement should get committed or roll back is it possible in this way so you can use roll back or commit up to this point up to a then up to here it will be rolled back right all this will get roll back if you're making commit to B then up to here will be committed so you cannot make this alone roll back or commit see if you want to make this alone separate transaction okay a separate transaction we have to go for autonomous ragma autonomous transactions what is it see if you are taking this commit and roll back database okay you are you are doing that insert update insert update whatever the T you are doing it this you can do so if you want to to make a set of transaction as child transaction okay child transaction so this one will be executed separately and whatever the transaction we are going to make it like a pragma transaction these two are already blocks of code if you want to make it it will be transferred to the another block okay like child transactions so it will be trans transferred to the child transactions then this transaction will be kept separately this transaction will be kept separately for an example this alone you want to commit it yes you can commit here or if you want to roll back this alone other transaction you have to commit it yes you can do other transaction committed and this alone roll back so you have to make this transaction alone as a child transaction then you can go for fragma autonomous transactions okay so it will be make it it will make this transactions as child transactions yes if you see this I will give you an example so that you can able to understand okay this example one if you see this I'm just creating the table called customer okay I'm just creating the table called customer I will drop the table first of all I will drop the table call the customer drop table table name customer I I will drop it then create table customer just customer ID one column I'm just creating it I'm just inserting the data into the table so what is the data I'm inserting I'm inserting the data thousand okay one record will get inserted another one record will get inserted how many records are there in the table customer table two records are there okay so if I'm going to do select star from the table how many records will be there two records if I'm going to execute this this particular statement this is something like looping statement what is child transaction that is what I told if you want to make a transaction as child transaction we have to make this transaction as pragma autonomous transaction that is called we are we are bringing the transaction as a child transaction okay parent child right we are making the transaction as child okay so if you see this if I'm going to start begin and end within the begin and end I'm taking the for Loop and for I in 1003 so I start okay you can put 1,2 okay I'm starting from th000 right can you can put 1,2 here 1,2 to one 1 one0 so I'm starting the loop so it will start from here this number 1002 and it will go for one10 it will go into the loop insert into the customer table values whatever the I we have it will get inserted end Loop and here I'm rolling back what will happen so if I'm doing roll back how many records will be there in the table whether it will roll back only these transactions are nothing will be there in the table I'll execute this okay I'll execute this plsql procedure successfully completed if I'm going to execute this how many records will be there in the table any idea I did roll back here it's a separate plsql code so this is insert statement this is separate plsql code how many records will be there in the table no records you can see here no records will be there so why because it's a transaction right it's a transaction you are applying the roll back statement but for an example you want to make this as a separate transaction as a child transaction I do not want to disturb the existing th000 or, one I do not want to disturb this on this particular table you want to bring this transaction as child transaction then you have to use pragma autonomous transaction this is called autonomous transaction we are making the transaction is Tous transaction you can see here what are we going to do two different scenarios you can see this okay and I'm going to execute as of now there is no data in the table right there's no the data in the table I'm going to insert two records in the table two records in the table one row one row and here I'm starting from here right two and declare ragma autonomous transaction okay begin for I okay for I in 1002 to0 then Loop insert into end loop after doing that whenever you are using pragma autonomous transaction okay the Declaration part we have to use keyword called pragma autonomous transaction this is called keyword if you use this keyword then this transaction will be kept as a child transaction okay it will not disturb the existing one the existing one we have already inserted two records right see th000 we have inserted then 1,1 we have inserted and then remaining record we are making like a child transaction so this is 1001 right so this is 1,1 and we are going to make they're going to make the next transaction is child transactions see these transactions like, 2,2 to 1,3,4 so this way we are going to make up to, 10 right one 1 0 we're going to make see these transaction we need to make it like a separate child transaction so how can I make as a child transaction we have to use the keyword called pragma autonomous transaction whenever you are using pragma autonomous transaction either we have to commit or roll back okay without this commit and roll back back this pragma autonomous transaction will not be executed will will not be compiled so I'm going to do it I'm just execute this particular procedure PLS scill procedure successfully completed it has been completed now if you check how many records will be there in the table how many records will be there in the table I just committed here any idea Okay so 1,000,001 so this will be as child transaction I have executed if you go and check here all the 11 records will be there right yes 11 records will be there 1,000,001 but the thing is we haven't committed these two transactions right we haven't committed we just committed only this child transaction correct we just committed this child transaction alone but we didn't we had to commit or roll back this parent transaction right we had to commit our roll back these two transactions we haven't committed if I'm going for roll back if I'm going to roll back will it roll back after rolling back how many records will be there in the table I'm trying to roll back here so if I'm trying to roll back this will be committed automattic already we have committed right this has been already committed but if I do roll back whatever the transaction we have here okay this transaction will be rolled back so if you see this only this child transaction whatever we did it it will be available okay go and check here the th000 and th1 should not be available here got it so that means we made this transaction as the child transaction so how can I make the transaction as child so we have to use the keyword called pragma autonomous transaction so how to make so even if you want to do roll commit yes you have to do the commit here okay so you have to do the commit here it will be committed then see there is no point of committing now why because we already rolled back right if you want to do any insert update or delete operations apart from this block then it will be separated make it like a main transaction so that will be a main transaction this will be a child transaction if you want to make any transaction as child you have to make the keyword called pragma autonomous transaction are you clear so what is that ragma autonomous transaction if you see this same thing I'm doing here create table customer customer ID number create or replace okay so I'll go here the same thing I will explain one more time here if you see this this is another example that's it if you see this I'm just uh I will okay this is also customer ID right okay I will drop the table drop table table name customer table I will drop it execute this alone so I have dropped the table and I'm just creating the table here customer table and I'm making this create or replace procedure so this time I'm creating a procedure here procedure procedure _ autonomous transactions is within the is and begin we are to use ragma you can go for declare how we have defined previously you can go for declare So within the declare and begin you you can use prag aonomus or if it is a procedure yes you can use this way so inside the procedure also if you are making any keyword pragma autonomous transaction then you have to use the commit or roll back okay I'm doing it see pragma autonomous transaction begin insert into customer values so I'm just inserting one value after that I'm just committing it okay I'm just committing it so I'm going to execute this how many record be there in the table so nothing will be there in the table right see why because we have just uh compiled the procedure if I'm going to execute the procedure it will insert one record into the table right as you can see here one record into the table but we just committed that's that is what we have did it we have committed so it's a child transaction right it is a separate transaction we are making okay so if you see this this is different okay so I'm just deleting the record from the table right I'm just deleting the record from the table so what will happen see if you are if you are selecting this table yes you'll be seeing one record right I will go and delete the records delete from customer okay so I'm using this delete from customer so what will happen here one row deleted yes it is a separate transaction after that I'm making that is what I have deleted right yes what I will do I will go for the another scenario so this scenario I will go for I will drop the table I will recreate the table so it's no need to drop and recreate just I'm doing it it is a child transaction I'm I'm creating it okay so delete from customer so there is no data in the table right there is no the data in the table right so I'm just uh making this execute customer it will get it will get loaded into the table okay execute one record should be executed now it should be inserted now okay I'm doing this what will happen declare begin insert into table name insert into table name customer values I'm just inserting already one record is available I'm just inserting one more and one more time I'm calling this procedure autonomous transaction okay you assume that this one I'm just calling then it will insert one more record here right one more record here say for an example I will even I will delete it no issues I will delete it I'll show you nothing is there in the table if I'm going to execute how many records will be there in the table so you'll have two records in the table right see why two records we have already inserted one record inside this Anonymous block we are calling this procedure this procedure will be called and here it will be executed okay here it will be executed okay so now I'm going for if you are doing roll back here or if you're doing roll back here okay so I will do this delete statement delete from table name I will just commit it nothing there in the table okay is nothing is there in the table so I'm doing roll back here how many records are there in the table no right so we already comp compiled this this one we already compiled okay if you are going to call this call this autonomous transaction we have here we are committing it so one record should be there but here we are doing the insert into then we are doing the roll back so whether two records will be there or one record alone will be there if I'm going to execute this before executing I'll will show you nothing is there right but here I'm inserting the record we are calling this procedure here also I'm inserting previously we did commit but now we are doing the roll back whether two records will be rolled back or one record will be available okay 1,000 sorry 10,000 will be there in the table see now okay not 10,000 it is one lakh correct so that is what we are making making a transaction as child and parent now you now you are getting it what is child and parent transaction right okay so if you want to make any transactions as a child transaction within the procedure or package or anywhere if you're mentioning like pragma autonomous transaction then you have to uh you can make that transaction as this way okay I'm going to execute this it will be compiled but here I'm going to execute it okay you'll get error so why this why this error is happening what is the issue here any idea so active autonomous transaction deducted and it should be rolled back right either roll back or commit see here this one I used pragma autonomous transaction but I didn't commit or roll back correct so either I have have to commit or roll back right so here I have to use commit or roll back if you do commit or roll back if you compile it again if you're calling this then you will not get that error got it if you are getting this type of error Ora sorry Ora 06512 active autonomous transaction detected so that means you are using pragma autonomous transaction you are making this transaction as child transaction but you are not committing or roll backing that transaction and you are using that then you will get this this particular error so active autonomous transactions are explicitly committed or rolled back so remember whenever you are using pragma autonomous transaction yes you are to actively explicitly you have to commit our roll back I hope you are clear what is autonomous transaction so I will go to the next concept see try to do this insert into table test insert into one and two I have inserted okay here I'm doing the pragma autonomous transaction begin insert into this one so whenever you are doing it you have to do the commit or roll back right I'm just doing the commit then insert into how many records will be there in the the table after this which record will be there in the table now you can answer look at this you tell me the correct answer okay so here I'm doing rolling back here I'm doing the commit okay this one we will do first table table name test right there is no table so I will execute all this one row inserted and if you see this table select star from test table I'm just executing it then one two we have right then I'm doing this ragma autonomous transaction I'm doing the commit and I did one search statement if I go and check here there will be three records here right three records but I'm doing the roll back if I do roll back only one record will be there three and four okay so you have answered correctly okay so here I'm doing the roll back so here I'm doing the commit okay how many records will be there in the table Frank table just I'm doing the rank table test insert into table name one record I'm inserting and I'm making this transaction is child transaction and inserting one more record and here I am checking it how many records will be there so you have two records and again I'm going to so here if I commit yes you'll have two records 1 2 5 six clear so I hope you are clear what is autonomous transaction right yes the same concept you will see in our triggers also so triggers we have one concept called mutating error so what is mutating error that while checking we have to make that transaction as child so then it will not get impacted so what is trigger in Oracle say for an example you are working in production environment okay you are working in production environment you have a table then what happen who will insert or update the data into the table normally who will insert update data into the table in production in PR environment I'm saying in production environment no one will have access to insert or update data into the table only system user not admin only system user will insert the record into the table right in production environment system user so that means the front end application will have a system user it will go and insert and update all the records whatever we are doing this transactions it will give DBA they should not do it actually they should not update for an example you have bank balance DBA will have access to change the bank balance yes they will have access but very restricted very restricted only few people will have access to that main table but they should not do any changes right if somebody is changing anything on this table say for an example uh I'm trying to delete record from this table it's a production table I'm trying to delete record from the table okay it's a junior person has trying to delete it but he forgot to put the V class okay you assume that some V class he forgot to put so that is why whenever we need a delete statement should be executed always they will say like you have to take the backup of the table okay always they will say backup of the table you apply the delete normally they will keep the backup like next one month then they will have some internally they will have purging statement it will Purge the backup tables but you forgot to take the backup also you are trying to delete and you have committed it is like a main table it's a production table you have committed see what will happen see all the data will go right all the data you will you will lose all the data if you have deleted or cranked wrongly then recovering this particular data from backup table it's very tedious process and we want to know who has deleted okay which user has deleted and at what time he has deleted all this somebody is changing okay somebody else is changing the record in this particular table right record in this particular table they want to check it if somebody else is going to update or insert on this table the the system owner should get notified automatically we we are not going to explicitly call that program say for an example procedure function packages we have to schedule it we have to explicitly call that right code but triggers will automatically get fired whenever any DML or ddl any operations happens will automatically get fired you are going to the ATM you are depositing some money or you are withdrawing some money or you are you are transferring money to different account so your transaction is happened you are doing some nft okay after after some time you'll get messages that this particular amount has been deposited to this particular account right and you are withdrawing some money from ATM or you are depositing some money to the ATM or you are going to the bank and you are doing it so immediately you will get notified right with the messages with mail OTP see all these are like a trigger event on the database side okay whenever you are doing any transactions on the olp the special kind of event will automatically fire a trigger is a special kind of stored procedure right it's a already stored procedure that automatically execute when an event occurs in the database server so what is that event see that event we have to define whether it is a DML event Ral event okay so different event we have to Define it so if it is DML event yes whenever ml triggers execute whenever a user tries to modify data through a data manipulation language okay like insert update delete statement table or view see what happened one particular person it's it's a real story one particular person so he has production access on the database in the client machine he did one code deployment so that code will take 0.00001 amount some dollar okay it will take from your current account your your bank account it will tore this account to his account he did the code and uh has been applied for several months and he did he got lot of even Crowes okay so but it's very very minimal you will not find it from your account balance you will not get you will not find it a very minimal amount see he did the code changes it is already happening and uh this has been moved to this particular account then they were trying to even reconciliation also they couldn't able to find it's very very minimal right they couldn't able to find but they are going for like a month end or the the the day and balance so very minimal they're getting from each and every account and it's like one aggregation is very huge like this aggregation while they are doing reconcilation they're finding this the aggregation of all the account we getting this much amount they couldn't able to backtrack and they did it okay so somebody has changed the code replied it because of this it has been moved this type of DML operations is happening on the table yes we should get notified the system owner will get notified say for an example you are deleting a record from the table okay without back without taking the backup then we can take okay so which record you are deleting before deleting the record okay you are trying to delete the record from here before deleting the record fire this trigger it will take backup of this record to backup table then it will get deleted even if you are deleting this who has deleted when it has been deleted which data had got deleted so this information will be stored in one of the backup table so that trigger we have already deployed if somebody else is trying to delete or modify or anything they trying to do you will get notified here which user what user okay so that's what the DML the usage see we have five different types of triggers one is DML trigger so DML you know right insert update delete operation you are doing it on the table we want to fire back end automatically it gets fired we are not going to do so we will tell them okay somebody deleting data from this table before delete you take the backup that's what we will say Okay insert into this so somebody is doing creating the table ranting the table renaming the table or any ddl operations okay then they will go for this ddl operations ddl trigger create on the table system trigger so what is the system trigger so you are log on to the system you log out the system you starting up the server you are shut down the server and all this so there particular say for an example when you are logging to the system HR user okay at what time you have logged in what time you have logged off all the information I want to capture it by default you can create this kind of trigger so mostly as a developer okay we will use this DML trigger and inside of trigger the other triggers like ddl trigger system trigger the other triggers compound triggers it's nothing but combination of all the DML operation that's called compound trigger but this system trigger and ddl Trigger will be handled by the DBS okay database administrator so we will see but we will see one one example for this ddl system trigger we do not have access but I will explain okay so how to use it and instead of trigger it's nothing but uh I hope you have seen you have already practiced our views views concept so how many types of views we have three types of we have right we have views is nothing but normal view in normal view we have SIMPLE view complex view right simple view means view on one single table complex view means view on multiple tables so if you are creating view on multiple tables the insert operation we cannot do it right can I insert or update on the view is it possible normally yes we can do if it is simple table we can do insert and op insert and update operations but if it is a two different tables complex complex view on complex view we cannot do insert update operations but if you are creating inside of trigger then we can do so that's what we will have the inside of trigger so we will see one by one all this clearly so I hope you are clear so how many types of triggers are there so five types of trigger one is DML trigger ddl trigger system trigger instead of trigger compound trigger so what is this we'll see one by one so Oracle allows you to Define procedures see here the types of triggers we have normally row level trigger two types of triggers are there one is row level trigger other one is statement level trigger so what is row level trigger whenever you are going to insert the record a trigger will get fired automatically each row okay the trigger will get fired automatically so this is row level trigger statement level trigger means it is entirely for DML statement ddl statement statement level on on statement level okay there there are 12 types of ddl sorry DML triggers are available so we have two firing points so one firing point is nothing but the one is before and after so what is before and after see before insert I'm trying to insert the record into the table okay I'm trying to insert record into the table this is the one table I'm trying to insert record into the table it has some tend records okay it has three records or 10 records or whatever the number of Records you have before inserting record into the table I have to execute some backup you assume that that is called before insert trigger on the roow level after insert okay after insert after inserting the record into the table you have to take the backup so something okay some operations we have to do on this table yes after insert somebody has inserted you have to you have to do it after updating the record so you have to send uh your balance to your customer saying that see after uh some credit happen update happened this is your new balance so after update after delete see after delete you will not have the data the data will get deleted normally but you cannot get previous data but after delete what whatever you want to perform yes you can perform so you have six different triggers DML triggers for row level and the six different triggers for statement level there are 12 different triggers will be there for the DML triggers so what is this see what is this tml trigger before insert it indicates that the trigger will fire before the insert operation is executed so that's the meaning okay so how can I create a trigger it is also a stored procedure right right is a pre-compiled stored procedure yes we create create or replace trigger trigger name create or replace trigger trigger name before insert okay before insert on table name for each row means it's a row level trigger okay for each row means is a row level trigger so we do have statement level trigger so you will make it like a statement for each statement that means it's a statement level trigger okay so we'll you'll see the statement level trigger after some time okay so if you're not mentioning that will become a statement level trigger okay so this is what for each row means this row level trigger it's optional one before insert these options you can make before insert before update before delete after insert after update after delete like uh roow level trigger statement level trigger okay this way you can UND declare see here declare here so here we have to use declare before begin and declare you can do the variable declaration or if you want to make any ragma Tous transaction yes we have to use nuar before this declare and uh in between the declare and begin you have to use variable declaration all this this is called begin okay so begin trigger code you can write the trigger code if you want to have exceptions you can have the exception when exception is happening you have to handle it here okay so this is the syntax of the trigger you should remember all this so I want to drop the trigger so you have to drop trigger trigger name say for an example you have one table this is one table okay so if you you have one table then you have Associated trigger okay so you have Associated trigger see this one if you have Associated trigger if you are trying to drop the table it you cannot able to drop the table so if you're trying to drop the table so you cannot drop it see first you have to drop the trigger then you have to drop the table okay so so remember so first you have to drop the trigger then only you have to drop the table so if trigger Associated if you're trying to drop the table it will say like so with this table the trigger has been Associated the Syntax for disabling the trigger yes you can use alter trigger trigger name disable alter trigger trigger name enable alter table table name disable all triggers you can make it the syntax to enable all the Triggers on a table yes this is what all triggers enable that's it you will use the keyword enable Okay say for an example I'm just creating one table so what is this after delete so table I'm just trying to delete Sorry create create Table after delete username uh date and time and I'm just using count create table I'm just creating on table customer details select start from okay I think we have one table here customer uncore sore customer right I'm just uh creating one table yes I will create this table here customer details I just created one table if you go and check in the customer details some records are there this is the main table so this is main table it is very sensitive table you assume that we have a sensitive table so what I'm going to do I'm just creating one more table here see when whenever some delete operations is happening in this table we should take backup to this particular table okay customer backup figure at this table we should take some backup you assume that select star from sdore customer ID there is no data we are just creating just creating and we should know who has deleted and when it has been deleted so that information we should know so for that I'm going to add two columns in this customer backup trigger so date of deletion is one column I'm adding and I'm doing this who has deleted this these two information we have to capture on the table okay so you can go for lot of other informations we will see whenever we are going for ddl operations ddl trigger so that time I will show you okay so this is what I will go for here and I'm doing the customer select start from customer backup right yes see here nothing is there in the table as of now nothing is there date of deletion all this but how many records there in the customer details table we have some five records are there in the table okay so now I'm going to create a trigger okay so now I'm going to create a tri whenever somebody is deleting the record from here it should take back back up before that it should insert the record into this table automatically see for that I'm going to write this particular trigger see what is this create or replace trigger trigger name before deletes okay so I'm trying to delete some record here I'm trying to delete record here before delete on customer details table right on customer details table which table we are going to do on customer details for each row one by one okay so where wherever you have some for each row I'm going to execute this trigger yes for each row see these are all like a syntax right I do not have any declaration part here just I'm going with begin so insert into so whenever is getting fired this delete operations Happ in this table I want to insert the data into the backup table insert into backup values I'm just taking these details see what is this colon W what is this col and old right this is called the qualifiers see what is this if it is insert if it is insert you are doing insert record you're doing the insert okay you are inserting the record you will will have only new values after inserting you'll have new values right holder new only you will have you will not have colon old why why because you are newly inserting right for new Operation new insert colon old you will not have it only new you'll have it so why because you are only going to insert right update see you are going to update on this data see 100 is there here you're updating as 200 so that means before update you'll have old value right 100 after update you'll have new value 200 so you'll have both values you can take colon old colon new that you can take it you are deleting the card say for an example deleting operations see if you do delete operations so you are deleting it some records you have 100 you're trying to delete it which value will be there old or new if you're trying to delete so you'll have if you're trying to delete it you'll have old value only not new value see why because after deleting you will not find any new values right see that's what the old and new qualifiers we will say in this way so whenever we are having a triggers we want to take this insert into customer backup table values first we will go for customer ID right yes we'll go for customer ID first name mobile right mobile Street address okay you have address so here address not address one address hold dotzip code old. country right yes then system date why system date needed we have added two columns right so date of deletion when we have delete when has been deleted and who has deleted so we are capturing user user name who has deleting see this is for delete operation same way you can go for update operation okay before update on this table for each row you do this insert operations okay but I'm going to execute this you can see this insert operation you can check this one there is no data in the table so but I'm going to do the trigger I have compiled it so I have compiled okay so I have compiled here so I will take this one here okay I'm trying to delete the record from customer details which table I'm trying to delete delete from customer details where customer ID equal to customer details right can check here I'm trying to delete this record or this record okay you assume that this record I'm trying to delete so where I'm deleting I'm deleting from customer details but before that you see this customer backup do we have any record no but I'm trying to delete the record from this this is the only operation I'm doing it okay somebody's deleting or it's happening okay by default okay I'm trying to delete the record customer ID is invalid identifier somewhere we have customer ID was here right customer I'm trying to delete this customer see one row deleted from which table we have deleted we have deleted from customer details right yes one row has deleted from here but whatever the deleted record we haven't inserted any record into any table right but if you go here it will automatically store in this table whether we have fired no right from this table you can take that backup if you want so you can execute date of deletion and who has deleted if you have logged in yes your name will be captured automatically okay so any event is happening on the back end if you want to capture it yes you can go for the this kind of operations so we do have other trigger types so we have order of trigger execution then ddl operations instead of trigger system trigger so that we will see and also mutating error DML trigger we have seen DML trigger we did for only for delete operations right whenever we are doing the before delete we have performed even you can create the same kind of trigger for after update and before update so anything is fine okay any statement you can create So based on that old and new you have to use it gold and new you have to use for update operations it's depends on the different operations okay so just to try that any update operations so that you will get clear idea now we will go for ddl trigger so what is ddl trigger we have seen already here ddl so whenever you are doing the ddl operations altering a table dropping a table truncating a table granting revoking auditing all this comes under the ddl so if you are doing the ddl operations on the table yes it will get fired actually uh if you want to make ddl trigger so this is the syntax of the ddl trigger create or replace trigger trigger name after or before you can use okay after d operations before ddal operations you can do it and ddl Trigger log on log off for these three same statement okay log on okay somebody else logged into the system unauthorized user yes we have to make some trigger on database so entire database level you can make it or schema level we can make it see in our system we do have only privilege access for the schema level not on the database level so that's why I have created on the schema level in real time project who will create this ddl trigger uh DBS will create this ddb uh this particular trigger okay so here I will go go to this table I'll just create one table called so this is a table I have I will create this table ddl trigger table log okay this is a table I'm just creating it yes I have created one table okay so what is this this table will it will hold these informations like Oracle object name Oracle user okay which user has ranked the table or deleted the table okay sorry dropped the table all this and what is the execution date what event okay it's a truncate or drop or whatever ddl operations and object type and object owner okay so this information I have to store so I have created this table and I'm going to create the trigger say for an example I'm going to create this trigger on this particular schema so what is the schema HR schema create or replace trigger trigger name it's a ddl trigger after ddl operation okay after ddl operation we have to perform it's after ddl on this particular schema H schema begin insert into this table values okay which object you are truncating and who is TR who is truncating the when and then event okay which event see this will happen so all this details I'm just capturing so these are all like keywords okay these are all the keywords like a user or object name login user all these informations are keywords article system keyword okay I want I want to insert this information okay after this I'm trying to I'm trying to drop the table okay locations uncore one this table I'm going to rate it there is some data is available on location one but this table the table as of now there is no data in the table okay okay I'm going to truncate truncate table table name location one right location locations underscore one this table I'm going to do rank table so I'm just truncating after truncating I will go and check select star from this table right we'll check it yes nothing is there in the table if you go and check in this log table it will show you see we are creating only logs that's it so location underscore one this table from the h schema that user and uh system date today's date rank operations it's object IP is stable and on the owner is HR see this this is the schema name so which user has rank or deleted but this will only log it that's it who has truncated all this but if you want to see even you are truncating it if you want to take the backup you have to go for the backup table different options okay you have to use so this is what you have to use the ddl trigger okay ddl trigger see first you have to drop the trigger then you have to drop the associated table if you are going to drop trigger trigger name then you have to drop the this particular table right see in this table only it is inserting this is the trigger Associated table so I'm going to drop this table will will it allow this table drop table table name no it will not be allowed see it will show error like check the following error for more information okay necessary action so error during the table internal error so errors during something we are getting right this type of error see if you getting this type of error it will show you actually so we have the triggers that's why we couldn't able to drop it this is associated table if I want to make this one and you have to drop this trigger then you have to go for drop this table okay trigger first you to drop this trigger then drop the table this is associated table inside the trigger but if you are dropping the table ddl ddl trigger we have seen right ddl trigger so on this table we have created the trigger right if you are dropping this table Associated trigger also will get dropped customer details customer trigger right this is the customer trigger only we have created right right what is the trigger we have created yes customer trigger see I'm going to drop the table customer details right drop table customer details I'm trying to drop the table I I have dropped the table but I haven't dropped this customer trigger right if you see this refresh customer trigger also will get dropped clear this is DML operations but this is different this is nothing but inside the trigger we have used this table so that table we cannot drop it before dropping the trigger if you are dropping any trigger Associated DML triggers will will get automatically drop if you're dropping the table okay so that is one point you have to remember and log on log off yes the same way you can do create or replace trigger uh this operation we do not have access so that is why we couldn't able to test it here create table table name log on log off all this so we have we have order of trigger execution so what is this order of trigger execution so which trigger will be executed first which trigger will be executed second Ro level Trigger or statement level trigger so how it will be executed we'll see one by one see here I'm just creating one table called student okay student number we have this table if we have the table then we will mark this table as student one otherwise this table will get deleted okay there is no table called student we'll create this table student number student name student uh date of joining uh result okay just I'm creating on table and I'm creating one sequence create sequence sequence name that's it it will create from all the pre predefined value it will create so I'm just a creating one trigger create or replace trigger trigger name this is the order we are to normally this is the order it will execute before insert on student statement level trigger see why it is statement level trigger why because I'm not mentioning for each row if you don't mention this is each row so that means this insert statement whatever we are doing insert statement insert into table name you are selecting from some other table that is one single DML operation right so if you're going for a DML operation this way statement level trigger will get fired first so you can see this first step then second one second one is RO level trigger before insert this is the order it will make then third one you are making row level trigger after insert statement level trigger after insert okay then I'm going with some insert statement set server output on you know begin and end student so I'm just inserting one record into this table that's it okay if you see this so what is the order it is executed what the order it has been executed the trigger see inserting into this table automatically is getting fired the first one second one third one fourth one so this is the order will execute statement level trigger before insert row level trigger before insert row level trigger after insert statement level trigger after insert this is what the trigger will get fired on the database so if you're doing any operations on insert okay insert or update any DML operations so what is the order it will get executed so this is the order it will get executed okay if you have triggers on the DAT table on the same table multiple triggers are there right after insert before insert but this is the order it will get executed so it sometimes they will ask in interview if you have statement level trigger we have 12 different triggers we have right so how it will be executed first it will make statement level before row level before row level after statement level of in this only it will be executed so why you have used we have used sequence number right to show you that what is the sequence we have just we have appended the sequence number right so that's why more Triggers on the same data base this is order to be executed so drop table table name I'm just sorry I will drop this table so Associated rers will get automatically dropped so next one is instead of trigger so what is the meaning of instead of trigger so if you see this instead of trigger you are having some views so this is my table employees table okay one table you can take employees table you are creating views on this table on only on employees table is this complex view or simple view this view is called Simple view you are making read and write view so normally in realtime project most of the views are only read only view but sometimes we can go for read and WR view so if you are going for read and WR View if you are inserting any data on The View it will get inserted onto the base table only correct base table only view is nothing but virtual table that's it this is simple view what I'm going to do now I'm going to create a complex view on the table so we are going to combine employees table Department table and I'm going to make a join on this table and creating Department name as well Department ID Department name location ID all this this is called complex view see if you are going to do a complex view on this both of the table on this table this is complex view right if you are trying to insert the record into this complex view okay into this complex view then Oracle cannot able to find okay which table I have to insert okay it will get confused whether I have to insert on the employees table or I have to insert on the department table both will will not do it and we cannot do it here we cannot insert any data onto the complex view so if you're inserting you'll get error so insert on the complex view on it cannot be performed but here you can create instead of trigger if you creating instead of trigger yes this complex view insertion is acceptable create you know employees table yes yes we have employees table here we I will describe the table you have how many columns 11 columns are there and I'm doing the Departments table we are the Department's data so we need to join it I'm just creating a view see what is this view contains create or replace view view name empv V okay employee ID first name last name email higher date job ID employees where Department ID equal to 30 just I'm creating one view see what is this view right whatever the column we have not null that I have defined here first for last name yes then email is not null High date is not null jav is not null all the columns I have mentioned okay so why because if you are trying to insert the value this is simple view if you're trying to insert the value into this into this view all the not column should be defined here then only you'll get value to this base table right yes what I will do I will go and in we have created a view right I'll go and insert data on The View like this see I'm just trying to check whether employee view will have data yes it will have the data on Department ID 30 right yes I'm trying to insert one record here insert into values 300 so just I'm inserting this value so it will get inserted onto the base table right as of now there is no record 300 in this employees table right there is no data 300 see I'm trying to insert it where I'm inserting I'm inserting on the employees view on top of view I'm inserting where it will get inserted the base table it will go and insert see this conceptt we have seen already in the views concept right yes if you go and check now so this record also but this record will not be available on this view why why view is Department ID 30 only but here I I'm not providing any Department ID 30 is a null that's why you are not seeing here 300 but this value will be available in employees table if you go and check here you will have this view table that card has been inserted this is called Simple view okay so now I will create a a complex view okay what is this complex view so if you are doing insert operation on the simple view yes it will get inserted but I'm going for the complex view create or replace view view name same way as select employee ID first name last name email all this column from Department name also I'm taking Department ID Department name so Department ID from which table Department's table D table from employees department where Department ID equal to department so this is nothing but simply a select statement joining right if you see this it is selecting So based on that I will create this view yes the view has been created so now I'm trying to select the data yes you can select the data but I'm trying to insert the data here okay I'm trying to insert the data here I'm passing employee ID is 300 okay you can if you want you can make three 31 if you want or you can roll back it whatever we have inserted already roll back okay now I just I will create this View and insert into this both complex view going for 31 is nothing but employe ID so whatever the details I want I'm just mentioning all the details okay I'm trying to insert whether it will be inserted okay not enough values that means some value I'm missing here employee ID first name last name email higher date job ID job ID I insted here single codes _ okay job ID salary Department ID all this just I'm trying to insert it if I'm trying to insert here I'm getting error so what is error I'm getting so previously we did here it has been inserted on the base table but now we are doing it right cannot modify more than one base table through a join view so this is error we are getting it okay so to our separate statement we are to do it in order to insert the record we have to go for two different separate insert statement we have to do insert into employ separately insert into Department separately but here it is not possible that's what we are getting there but I'm going to create a I'm going to create a trigger now this trigger is called instead of trigger what is the trigger name is called instead of trigger so if you see this trigger create r replace trigger trigger name instead of trigger so this is the keyword actually instead of insert on this table declare uh just I'm making that variable I'm checking whether that record is exist or not if not exist I'm just inserting it okay so insert into table name we are making new records new Department ID Department name on the Department's table and employees table all the details I'm I'm getting so what is a new and old okay employee ID first name last name all the details just I'm taking this value new new values I'm trying to do it so just I'm doing the compilation of trigger I'm just compiling it the trigger has been compiled so now I will go and insert the record two tables complex view right this is complex view now so I'm trying to insert it see it is getting inserted see this trigger right this trigger will get fired on top of the view that is very important on top of the complex view only you can create instead of trigger on top of the complex view we can able to create the insert of trigger it'll get fired if you go and check here select star from uh departments yes you can check departments table first if you are checking here then this 280 will be available here right see 280 purchasing is available now same way if you're going to check in the employees table this 31 will be available see here 31 is available so I hope you are clear just I will roll back see this is the usage of instead of trigger instead of trigger means you can create you can load the data on the base table if it is complex view so I will give you the statement just to go through it so you'll get to know all this very clearly so One More Concept is called mutating error you assume that you have two two tables okay sometimes uh in real time project also you might be seeing this kind of scenario you somebody else is already loading this table you are trying to load the table data to this table what will happen you'll get error like the table is being loaded right you you you you can find this kind of issues uh in informatic Also the table is being loaded data yes you will be seeing so it will go for table lock and it will lock the table and it will try to one DML operation is happening on the table and the other DML operation will not be allowed on the same table it will say like the table is being loaded sometimes it will get hanged so you have to you have to unlock that table and then only it will load this data into this table but the same way you assume that we have two different table okay so we have two different table one is customer one and customer two two different tables is there so what is the table here customer one and customer two two different tables see in this table the Oracle mutating trigger occurs when a trigger references the table Tes the trigger okay resulting in this particular mutating is me means changing okay table name is mutating or this kind of error you'll be getting so what is this see mutating table is a table that is currently being Modified by an update any DML operation update delete insert operations you will encounter Oracle dis error if you have a row trigger that reads or modifies the mutating table okay so I will create two tables one is customer one and another one is customer 2 just I'm creating two tables what is the table customer one table has customer ID customer name another table customer two customer ID customer name okay so two tables I'm just creating okay so here I'll go to the insert into customer one okay I'm trying to insert the record into the table which table I'm inserting customer one table okay I have inserted one record in this table 200 okay so here I'm just inserting 200 so this is the way I have inserted here okay so I I will commit it can see I just committed I'll use a trigger now I'll create a trigger so what is the trigger is doing now create or replace trigger trigger name some trigger name I'm just giving mutate trigger after insert on customer 2 okay if you are doing any insert operations on this table customer 2 okay after insert on customer move for each row begin this transaction update customer one see whenever you are doing insert so you update the record into this table set customer ID this customer ID to be updated with this customer ID for an example some customer ID I'm giving here this should be updated here okay that's what I have created see I'm just creating one trigger trigger has been comp not it fired okay okay so now I'm going to insert the record into the customer two table see I'm trying to insert record here what is the record 100 I'm trying to insert it I'm trying to insert the record here if I'm trying to insert we'll be getting error saying that mutating trigger we are getting the same error right 0 491 are you getting this same error 04091 table name it's like a mutating this kind of error you are getting see sometimes you are getting error so why you are getting this error there are two tables right there are two tables one table customer one we have inserted we have committed okay that means we have committed then second table second table customer two in this table after in ERT on this table customer to we have to what we are telling in article okay after inserting here this insert statement I'm executing it after inserting it you fire this one right but what happened I haven't committed this right since I haven't committed this transaction is not it completed this is being loaded right being loaded C customer 2 is being loaded on the same table I'm going to take the data from this table to customer one so we cannot perform here see here table hr. customer 2 is mutating that means it is changing that means I haven't committed it so if you haven't committed it we cannot take the data from the table so to avoid this what I have to do avoid this kind of scenario you have to make this transaction as child transaction got it if you are making this transaction as child transaction then the next transaction it will get it will not get impacted okay that's why we are going for the child transaction you can see this same way I'm just creating one more trigger okay see one more trigger I'm just creating nothing after insert on customer two for each row just I'm making that declare pragma autonomous transaction since I'm using pragma autonomous transaction we have to do commit commit our roll back so just I'm doing it so trigger has been compiled now I'm trying to insert the record here so one row inserted now right so if you go and check on the customer two table select star from even the customer two I haven't committed right but still it is see customer 2 I have inserted with 200 sorry 100 customer 1 I have inserted with 100 sorry 200 already if you go and check customer one table now it should have 100 right okay so why because we haven't so why it is null value why because I haven't committed the outer transaction that should be oh sorry I have used control enter so I'm just selecting here is there just I will commit the outside transaction if you go and check here uh still nothing is there right but if you if I'm going to insert the record on this table okay customer 2 table customer two table where it is inserting customer one okay so I'm going to take this as 200 sorry 300 or 350 it's a maximum record we are taking right from this table customer two I'm taking this one row got inserted so this table I'm inserted on the customer 2 but I'm selecting customer one so if you are seeing the customer one previous whatever the data we have now you are able to see this I haven't updated here right so why because I haven't after inserting I haven't committed so that's why this data has been loaded not loaded so if you want to commit yes you have to commit here then it will get automatically populated when you are going to do any insert or update operation so this operation you have to perform you have to call this trigger some insertion happening on the customer to so that time it will get fired automatically see this time I'm going for 352 customer 2 I'm doing it but 350 will get inserted here updated here got it so this is what you can perform this mutating error if you want to make the transaction as child transaction you have to use the keyword called pragma autonomous transaction okay if you want to make this transaction is autonomous you want to commit our roll back so you have to use the same way here declare ragma autonomous transaction you have to make commits let us start our today's session I got one question but uh what is the use of 1 comma 2 here right say for an example if you have any table any table in realtime project okay normally if I want to modify the data okay in this table okay so I can go for customer one this one table this is customer one table right but I can go for see I have to create table like customer table based on this sore customer _ SD I need to create this table so how can I table how can I create this table customer right so I'm going to create normally how can I create the table so same like another table so you have to use create table table name as so you have to mention all the column names right but here I'm just to create the table create table table name customer as this statement so what is the statement sore customer SD see based on this table I have to create this table right so if you select this this particular statement see if you are keeping control enter then this particular entire line will be executed but if you are using this this particular if you're selecting this alone and then you are executing whatever you are highlighted that alone will be executed so you can see here this is what select I'm informing Oracle that create one table called customer as this result okay this result if you saying this then one table will be created like this if you go and check now it will be available like this right previously the customer table was not there but we have created as this way but sometimes see why we are creating like this if you're going to modify uh any any table okay any columns or you are going to delete the data or you are going to truncate the table or whatever you are going to do okay by using this particular um some trunet table or drop table or you are going to make some updation or whatever you going to do on realtime projects normally we will take a backup so create table table name so for an example this table you are going to modify normally you will take backup of this table so entire table will be backup and always you have to use the naming convention like this after some time after one month or three months see sometimes the the Oracle DBS team uh they will check the space of the database if more space has been occupied they will run The Purge queries in the statements they will run it see what it will do whatever the table has been created like underscore backup for more than 3 months or more than one month it will get deleted okay it will get purged so that's what they should have have some codes and they will run it periodically like one month after one month or or whatever the table is not used see the not used table what they will do DBS DBS they will collect informations okay these tables are getting loaded are but not used by any any of the consumers no one is using the table then why you are loading the table they will ask see no one is used to last six months of this table they will ask us they will send a mail to the corresponding owner of the table particular team they will ask do we need to have this particular table or can we drop the table they will ask in that way right so this is what they will maintain the space and everything unwanted table they will delete it so even they they will keep this particular table for one month or one or 3 months or something so in that particular time if you have wrongly deleted or if you are wrong drop the table so you can get the data from the backup table but sometimes we need to create table with structure structure alone so for an example any unsatisfied condition if you're making where 2 equal to 3 that two two will be equal to three no right it is unsatisfied condition so if you are making this like conditions so 2 = to 3 or 1al 2 or a equal to b or anything okay that means it will select only structure of the table not the data right so you using where 2 equal to 3 not the data it will select only structure of the table we are informing Oracle that okay create one table of this structure right so we are informing this way see here we have informed customer table will have the data but customer backup table will not have any data customer table will not have any data even if you're mentioning like 1 equal to two so what is the meaning of 1 equal to 2 sorry 1 equal to 1 1 equal to 1 you are using okay so 1 equal to 1 means it's a condition satisfied so it will create with data okay and after creating it say for an example you have created it so you have created it there is no data in the table if you want to insert all the data insert into this table select star from something like uh this table say for an example see I'm I'm I'm telling you this way select start from the this way you can select and five rows got inserted if you go and check all the data should be inserted see this way you can put whatever the way you want to create the tables you can use it see what is collections where it will be used select star from customer if you see this customer table you have some data of the particular table so you are seeing the data of the table yes then so what is this customer table so I'm just describing it DC customer okay so if you see the tables customer ID what is the data type it's the number data type first name what the data type Vare data type mobile it's a Vare data type see what is these data types it is a Oracle predefined data type right Oracle predefined data type if you see number data type see number means yes you have number data type it is a Vare it is a number or Vare so you can have this particular data type so you can have the values like this right see these are all Oracle pre defined data type so can we create any data type by ourself can we create any data type by ourself by combining two columns like customer ID and first name like this is number data type this is vat data type can I create in this way some array of values or can I create one data type like this entirely yes so that is called Collections and record so if you take data types in article okay there are two different majorly two different data types one is scalar data type scalar data type means you'll have number integer float see all these are numeric data type a character data type date data type and large object and buan data type like true or false see these are all the scalar data type so each and every field will have the information right yes number data type number of that number information will have then you you can you can have composite data type see in composite data type we have two different types one is record other one is collection see here record the meaning itself you can see the record is nothing but in in table we will call it as one record right see one record this way if I want to create composite composite means you want to create combining more than one column as a as a data type then you can go for two different data types one is record and the other one is collections okay so mostly this collections are used in the performance tuning okay so plsql code if you want to go for any Performance Tuning then you can go for the collections to get more data from the base table to local memory to load it and then process it so that's what we'll go for collections here okay what is record to different we have already seen this percentage row type so what the percentage row type will do so percentage row type means the entire record columns right so if you have 100 columns 100 columns in a table yes I want to take all the 100 columns data type yes you can take all the 100 columns data type in one one sing single percentage row type so one variable it will be stored but type is nothing but if the table contains 100 100 columns I do not want to take all the 100 columns I want to take only 20 columns out of it or 30 columns then I can go for the type here okay so we can mention that record type so what is this we'll see so if you see this plsql lets you to Define two kinds of composite data types one is collection other one is record the composite data type stores values that have internal components okay so internal components see major difference between record and collection is whatever the the composite data type it has record we can have different data types okay array of different data types you can have but collection you need to have a logically group same data type if it is number is all the elements in the collection should have a number data type if it is Vare all the elements in the collection should have Vare only no other data types are allowed okay so two different data types or three different data types then you can go for record if same data type you can go for collections okay you can pass the entire composite variables subprograms as parameters and you can access internal components of composite variables individually in a collection the internal components always have same data type right as I told in the collection internal components will have same data type are called elements you can access each element of a collection variable by its index so index you can uh make it but the this concept I will tell you while taking the collections okay but we will go for first uh record type so what is this record type okay see record type means a record type is a complex data type which allows a programmer to create a new data type with the desired column structure see I want to create a new data type okay so it's not a number it is not a worker it is not a data data type I I want to create my own data type with collection of the columns okay complex data type so that is what it is similar to structure in C A struct or structure is a collection of variables can be of different types under a single name okay the same thing we have seen cc++ and all right if you see the struct structure name this is record name here and data type Member One data type member two here I'm taking name so name is character some CA number is integer salary is float so this is structure of person okay structure of student structure of any other you can Define any other structure here see here I'm creating one structure here and then I'm accessing through structure right so if you go to print F person one. name the name will be printed okay see this is the one name right so I'm just uh making this and I'm getting the C8 number and salary all this see this is a structure in C the same way in plsql yes in plsql we have a record type okay in plsql is called record type a record type is a complex data type which allows a programmer create a new data type with the desired column structure it is similar structures in C okay it groups one or more column to form a new data type these columns will have its own name and data type a record type can accept the data yes if you see this I'll go for first scalar data type so what is scalar data type if you look at this particular code if you look at this particular program okay it has dbms output I'll go to dbms Output or I can make the server output okay so if you see this this is called scalar data type normally first name so normally what we will do we will Define where c 2 of 30 right and what is the salary salary we will mention that number of 10 the decimal then we'll mention number of 10 comma 2 something like this right see we will Define this way this is called the scalar data Tye so we are defining and we are getting the data from the base table and in psql code we are defining it and we are processing it see the same way instead of mentioning like this you can mention percentage type see percentage type means that particular column alone will be taken right consider percentage row type means entire record data type will be stored right entire data type entire column if you if the table has 100 columns all the 100 columns data type will be stored right so that's what you can go for the the percentage row type corre the percentage this is normally percentage type we have already seen this and percentage Road type okay so record type means a record type similarly simply means a new data type that's it so one record type is created it will be stored as a new data type in the database and you can do the declare and variable all this same way so how can I create it so you cannot create a record type at schema level okay at the database level you have to create okay record type to Define a record type specify its names Define its field you can see here this is the syntax create type the data type name okay is record and what is the column names and data types but this is syntax but if you want to declare it in the sub program that is your function procedure or whatever it may be you want to declare it you have to do Anonymous block or anything you have to do this way so inside you have to use the keyword called deare instead of create directly you can use declare this is data type name so like a Vare to we have the data type right the same way we are going to create our own data type data type name is record Open Bracket close bracket then all the columns that's it see this is the way you have to declare the type this is called record type okay in interview they will ask what is the record type see here we have the percentage row type see what what is percentage row type we have already seen this right so normally we will call it as it's a anchor data type anchor data type right if you look at anchor data type in plsql so this is what you'll have percentage Road type all this anchor data types in plsql if you go and search in this way so anchor data type means so this these two you'll get it uh if you go to Oracle site okay so you'll be having there will be a two anchor variables two database data type used percentage type and percentage row type see this is what they will say like sometimes anchor data type okay so you have to Define this way so you know sometimes they will ask this way so that's why I'm giving the name okay so if you see this what is this I'm just a cre select star into normally We Will We Will select one by one all the column right can I select by using star yes you can do the select star select all the columns into this record this is a record now entire row it will have the data type so from employees where employee ID equal to one 120 that means we are selecting only one record yes we can select only one record only we are not using any cursor here or we are not using any bul collect here so just we are selecting this way right but if you see this what is this it is the variable of row type entire row it will be taken employees table percentage Ro type see after that if you want to use it you can use the variable name okay this variable name dot the column name okay no need to Define each and every columns what is the data type right so first name whether we have defined no phone number whether we have defined no it will be having all the data entire information of entire record but see here if the table has 100 columns but we are using only two columns unnecessarily we are you are we are storing all the columns information right percentage Ro type see no need to go for all the column data types instead of this you can go for type record type so what is this record type I'm creating my own data type or my project okay if you see this this example okay declare end begin type this is a keyword right so what is the syntax here we have seen the syntax of this one five type this is the data type name see you know what is mean by number yes we know all this what is by 2 right same way I'm creating one data type called employee record types okay this is one data type ourself we are creating that's what we are defining like a type okay type this variable name okay the data type name of this type is record so what is the type of this uh data type of this variable is a record it will contain only two columns value one is first name value another one is salary so two information only it will have it see if you have hundreds of columns don't need to take all the 100 value all the 100 columns in percentage row type instead you can go for type and if you notice here there is a major the difference between type and sorry record and collections in record you can have different data type where two data type number data type right it's a definition we are defining it we are we are informing Oracle that okay so I'm creating my own data type called empl record type is a record okay of this particular record type and what is this EMP record it's a variable name right variable of this type so normally how we will Define okay I'm I'm defining a it's a number data type right number of 10 just I'm assigning a value number of 10 equal to something like 1 2 3 4 some values I'm assigning up to 10 digit you can assign but I'm assigning 1 2 3 4 so this 1 2 3 4 will be stored the variable called a of this data type same way so this is the variable so here yeah right same way I'm using EMP record of this data type and I'm not assigning any uh value here okay so just I'm defining it that's it here definition of this particular data type I'm declaring it and I'm going to use this variable do first name okay what is this okay so what is this I'm just hard coding the value I'm not taking from any table am I taking from any table no I'm not taking from any table I'm just hard coding the value unj and some values I'm giving salary and I'm printing here first name and salary so if you see this it will be printed so whether I have defined any number data type or Vare data type here no right but if you don't Define here don't Define here you cannot assign it directly right so what is this like this so I cannot Define so that is why we are using record if you want to create your own complex data type then you can create it by combining two or more columns it is similar to structure in C or any other languages next one what is this right same way I have used employee record type is record first name and salary two columns I have taken even you can take 10 columns if if the table contains 100 columns you can take 20 columns all this all the information will be stored in one particular data type okay and I'm using it I'm not using any this is declaration the definition declaration part declaration part I'm not assigning value but here I'm not giving hot coded value I'm just selecting first name salary into this single variable okay single variable is called record now okay record of this particular data type composite data type first name is Vare to salary is number eight see this is the same data type will be stored on the same order the order is very very important here if you're using first name and salary the same order you have to select here that is what it will be stored in this particular record and employees you have to select yes one record but if you want to go for Loop then you have to use cursor okay so just I'm using it and it is printing so from where it is printing from the employees table yes this is the way it will print okay so I'm going with see wherever you are finding the keyword called type that means we are defining it if you go to realtime project you can see this type of informations they might be using it and it is nothing but we are defining the data type okay so type employee record is record of this first name and this one same way I'm defining and here I'm using both first name as well as salary both I'm just uh making it yes getting okay first name is Matthew and Sal is this one if you're going for any other employee ID you'll get other data here right yes you are getting other data see what is the use of these Collections and this information right see collections are used in some of the most important performance optimization features in plsql you can go for bul collect yes you can use for bul collect you will use this collection only so we'll be using select statement that retrieve multiple rows from the single Fitch increasing the speed of data retrieval so bul collect we will see tomorrow so the time I will explain that so how this collections will be used in bul collect same way you'll be using one keyword in plsql called for all so if you use for all yes this is also be used in B collect and rest of the plsql code like for all means it will it will take all the data insert update or delete so that use collections to change multiple rows of data very quickly okay a collection is an ordered group of logically related element but record is different data type element a table functions yes plsql functions that return collection can be called in the from class of a select statement yes we can take functions also main purpose of using collection is to improve the performance using collection we can load all the record once from the database into local memory if you have huge volume of data so you know you remember the context switching so you have the SQL SQL block plsql block whenever you're using any plsql block so it will go to the database the SQL engine and plsql engine there there should be a contact switching it if you if you are making more contact switching then it will take a lot of memory to avoid that if you use collections to load all the data so whatever the data we need all the data into database from the data B to do your local memory then if you want to perform it perform the operations okay whatever the operation you want to perform local memory means you are bringing the data to your plsql block okay so that means it it store the data into local memory it reduces calls to the database contact switching will be reduced okay so that's what you can do collections will be used but collections are logically grouped same type of elements and uh the record is nothing but it is nothing but it's a different uh data types so you can see here another example of record type this is a data type is record first name salary okay I'm using it when first name salary and I'm just printing phone number so will it be printed phone phone number what is the error I will get I'm just printing first name salary here this record type selecting first name salary into this one using that but I'm just printing the phone number here so will it be printed no if you print phone number you'll be getting error must be declared so you have to declare that so where should I declare first you have to declare here and you have to select from here okay I have declared I'm not using it that's fine but you are to if you are declaring it you have to select and use it okay so that's uh one thing if you want to do it again you have to go for so this one okay uh this is null null constraint how can I use null constraint in plsql code so we know already right so we are defining set server to output on type employ record type is record first name barard to it is a not null data type okay it will not accept not null it will not accept null value just I'm assigning a value John here okay it's not null it will not be taken any null values okay so I'm just defining this one and I'm making this employee record type. salary equal to 100 see here salary I'm defining sorry I'm assigning value here but for first name I have defined Val here salary sorry name of this first name here itself so if I'm going to execute name and salary it will be printed but here I'm going to assign null value okay null value I'm assigning it's a not null column if I'm going to use null value for the not null you getting error you are violating that right you're violating so change the data how it is manipulated declare values do not violate the constraints so we should not violate the constraints so why because we have given as not null we are violating it and if you if you are declaring it and not using it yes that is fine you have declared it but if you're not using it no issues so next topic is collections the collections are used in some of the most important performance optimization features in plsql you can go for bu collect yes you can use for bu collect you will use this collection only you'll be using select statement that retrieve multiple rows from a single Fitch increasing the speed of data retrieval same way you'll be using one keyword in plsql called for all so if you use for all yes this is also be used in B collect and rest of the plsql code like for all means it will it will take all the data insert update or delete so that use collections to change multiple rows of data very quickly a collection is an ordered group of logically related element but record is different data type element a table functions yes plsql functions that return collection can be called in the from class of a select statement yes we can take functions also main purpose of using collection is to improve the performance using collection ction we can load all the record once from the database into local memory if you have huge volume of data so you know you remember the context switching so you have the SQL SQL block plsql block whenever you're using any plsql block so it will go to the database the SQL engine and PLS scill engine there there should be a contact switching it if you're if you are making more contact switching then it will take a lot of memory to avoid that if you use collections to load all the data so whatever the data we need all the data into database from the database to to your local memory then if you want to perform it perform the operations okay whatever the operation you want to perform local memory means you are bringing in the data to your plsql block okay so that means it it store the data into your local memory so it reduces calls to the database contact switching will be reduced okay so that's what you can do collections will be used but collections are logically grouped same type of element and uh the record is nothing but it is nothing but it's a different data types as I told the collections are same way is used most importantly for performance tuning okay whenever uh you want to avoid the context switching between the SQL and plsql engine then you can go for uh this collections okay so you can use the different keyword like bul collect for all table functions all this you can use this collections okay using collections we can load all the records from database yes we have seen this we have three different types of collections right see here this is what the record in a collection the internal components always have the same data type are called elements in a record the internal components can have different data type called field so here it is a element here it is a field okay so scalar data type we have seen so what is the scalar data type what is the record type and record type examples we have seen But collections we can go for collections if you see the collection a collection is a order group of logically related element okay uh sometimes in the realtime project so you'll be having a address address field right see the address is like a single Field address but the thing is how it will be stored in the database we want to store this address say for an example your name okay your name and your your your data your data birth okay data birth your skill set all this you're mentioning you are mentioning here address okay address we are not storing it as a single column see in address we need to take like a array array of values so first value we have to store the door number this is one data type only but we are taking array of values is first one door number second one the index will have street name third one you'll have area city state and postal code so like this in one single array you'll have all the values okay this is called collection so collection is ordered group of logically related elements with same data type the door number street name area city state postal code everything should be a Vare 2 data type what we have to Define and if you see this yes you know already this record type type we are defining and this is a record is it's a keyword record we are mentioning collections we do have three different types so one is V array it's a variable array n table okay table and the third one is associative array okay associative array are index by table normally you'll call it as associative array or index table see in the v array and Nest tables so if you take V and EST table so both will have the index the index part okay the value the array of value is is storing right if you take so this is the array of values it is storing the index value will be defined by Oracle okay this is one 2 3 4 and five one is nothing but it's a door number two is nothing but state name three is nothing but it this way if it is storing the value then this index will be this index will be created by and maintained by article okay so we are not we we are not going to maintain okay this index will be maintained by article but we do have some differences between V and Nest table but this one is like a key value pair okay so so like this we'll create a key corresponding value we'll store it see for this key value one what is the value here key value two what is the value here so key value three what is the value here it's like a key and value we will create that is what index by table this is called Index this is called value associative so both are associative that is called associative array so variable array LED table index by table associate see what is variable array if you see this this is a definition by Oracle so variable array so it's a variable size of array is nothing but it's an array whose number of elements can vary from zero to the declared maximum size actually for the V we are to define the maximum size of the AR okay it is one of the state data structure right yes we will Define our own structure it will start from 1 2 3 4 5 6 7 see here they are just mentioning the grades so BC some some value they are storing okay it's called elements okay so V of maximum size 10 1 2 3 4 5 6 7 8 9 10 they already defined 10 but as of now they have stored seven values to access an element of V variable use a syntax called variable name and index so index the lower bound of the index is one yes the upper bound will be whatever we are defining it the upper bound is the current number of elements it might be the 10 or anything the upper bound changes as you add so you can add but you have to declare here declaration part itself if you delete it it will be uh so it will be deleted so a lot of methods are there so you can use some some methods to use this okay so I will tell you what is this we'll go for see for any any elections you are to follow these five steps so first you have to define declare initialize assign access so that is what you have to use the collections this is the way you have to do what is defined so how can I define the same way see collection or record this is the way you have to do record means we we are mentioning like okay it's type of record right but here keyword type we are creating the data type right yes type this is the data type name V type simply this a data type name like a V to all this right same way this is the keyword of vray okay V array how many elements we are defining maximum eight see up to maximum eight only we can go for if you want to go for 20 yes you have to initialize first itself 20 of all our Vare to data type so everything like vat to data type see on the same data type only it's not like another data type see here here I have one collections see sometimes you'll be seeing in plsql code the keyword called type it might be a record type or it might be a collection type if this collection type you'll be see seeing the three different collection type V lest table associate or index by table of Vare 2 see maximum I'm defining eight so just uh whatever the data type here V type so that should be declared here so off type this one this is the variable name so V color is the variable name equal to V AR type of so if you are defining eight variable eight elements so all the eight elements you have to initialize it if you're initializing here itself then after that you can give different value okay one index value one I'm just giving value like red index value two I'm just giving the value like black so this way I'm just giving the value and if I'm going for V color of four 1 2 3 4 it will print like green so if you want to go for some address data type you want to store and you have to do yes initialization yes you have to do so how many numbers you are doing it that is the number it will take so I will give you an example here so that you will get clear idea so I have given some explanation here just uh go through this so index value what is element okay all this I have given methods lot of methods are available and three types of collections so one is associative array listed table and V so first we are seeing the v v is nothing but can be used in plsql Block in SQL statement and as the data type of columns in the table so V are always dense and indexed by integer when a v type is defined you must specify the maximum number of elements allowed in a collection declared with a type associative array is the most commonly used collection type yes associative array we are defining the but Nest table has have some powerful unique features so that can simply the code needed to use your collections okay so n table the upper bound we can after that we can modify so that's a use so why it is called variable size but variable size means you can Define for an example this V 1 2 3 4 five six six elements right after that if you want to Define some 10 10 elements yeah you can Define 10 elements but you have to Define variable size you have to Max maximum size you have to Define it so upper bound we have to Define so V arrays is nothing but variable size array it's array of value it's a predefined size index starts with one and cannot delete elements okay Elements by index in that in that way I can say so Elements by index we cannot delete it but if you're deleting the elements all the record will get all the elements will get deleted so I'll go for if you want to create the collections use the collection first we have to Define okay I have I have defined so this is the one defined then declare okay so declaring the column called address there's a variable called address of this particular data type is not a single single column value it will have one 2 2 3 4 5 6 7 seven values it will have and I'm just assigning one record for that okay so if you want to go for more records you have to use uh looping concept so you have to use address of one I'm just using this G2 to ABC flat number this one and I'm just accessing the four okay so I'm just using this if you see this what is the value okay there four I want to take only the so four is nothing but you can go for one is nothing but your door number your flat name uh your area address line number one address line number two this is what they will store the database so where wherever they are collecting all your address for the delivery all the information right they will store all the address like array of values so initializing first values we are not defining right here just we are initializing it so I'm not initializing here just I'm using this so if I'm going with without initializing it so all the seven you have to do so if you're doing it three so it will not be used see here reference uninitialized collection right so as I told you have to initialize first see these steps you have to follow Define declare initialize assign a value access it so access is nothing but this is called assigning a value accessing is nothing but so which element we want to take okay so this is what accessing element okay so this is four so it has more methods so collection will have more methods also if I'm not using initialize it will not be used you cannot use it and I'm not initializing all the values here just I'm using only four values I'm just initializing see for collections we have to first this seven step you have to follow so first you have to Define it these five steps you have to follow initialize if you don't initialize you the collection cannot be used okay so Define declare initialize assign and access it initializing the value with null but if you want to initialize with any other value yes you can initialize any other value so how can we pass the value dynamically so we have to use any select statement cursor all this so declare type V array type is vay of s and I'm just defining only four okay so here I'm initializing four so if you if you don't use these values one 2 3 4 right I'm just using only four so it will be it will be used but the thing is you can go up to seven okay seven but I have defined I have initialized only four so those four only you can use it if you're using fifth one okay even fifth one so it will you'll be getting error subscript beyond the count okay this this is a subscript is beyond the count okay so initialize we have initialized only four but we have used five okay so Define and declare is same no this is defined right what is collection okay collection is structure same like structure in C why do we need this collection can you create in in Vare 2 okay so in number data type can you store two values two column values is it possible number data type will be able to store two column values this value and this value is it possible is it possible to store 45 and 67 no right but I want to store okay we have assigned as null null null initialize it we have initialized with null if you want to go for any other value yes you can initialize with any other value if so we have defined like vat that's why we have defined like null if it is integer data type we should have initialized with 0 okay something like some any other number you can initialize no issues but here you are Define you are giving any other different value so that's why we have defined so we are initializing it this is declaration the definition part and declaring the address what is address here it is a variable variable of type what is a type what is the type what is the variable type data type it's a v type so address is a variable of type V array type what is V array type it's a variable array of seven so it has lot of methods to access each element so we have methods so what are the different methods are available so these are all the methods say for an example this one we have seen and collection methods we have different methods so one is limit so if I want to access if I want to access each element you can use this method so method is like nothing but functions right same way you know the collection methods you can use limit maximum number of value of V so what is the maximum value of V this array this one eight is a limit and count is how many elements are stored in the v okay so how many elements are stored here actually initialized so how many of them 1 2 3 4 5 6 7 8 so it will give you eight if you're initializing only three then it will give you three so first so first index value index value is one immutable means you cannot change it the index value can you change no you cannot change it even you could not delete any element by index last means last index value here you will get eight Primm means last element will be deleted so if you're using Primm of one Primm alone then this element will get deleted you'll be getting limit as 8 count as s delete means delete all the elements in V you cannot delete elements specifically extend extend the number of elements yes you can go for extend prayer n is nothing but index of prayer element next 10 means index of next element the prayer of six will be five next will be seven so that's what we'll get it okay so if you see this in between we cannot upend okay that's what I can say in between we cannot upend so we have to go with the value 1 2 3 4 in this way see first time how many value I'm just initializing it how many values I'm just initializing it I'm giving maximum V array value is eight but I'm giving only three 1 2 3 of this one variable type so we color of one is one equal to red black blue so what is the one you will get it here color of one you'll get red limit you will get 8 count you will get three first you'll get one last you will get three prior to three you will get two next of three you will not get any value so you'll get null after that I'm just extending one extend of so if you extend one then you are counting it you'll get count four then you'll be getting next of three will be four you trimming it R means last element will get trimmed then after that if you are counting same three you'll get it so what is the count value here we are getting right same count we will get it delete means all the element will get deleted we will get null value here finally see the same one should have printed here so first one red we are getting right color of one is red yes then limit you are getting eight is totally we have eight count is three only so why because as of now we have three first is one last is three prayer of three will be two next will be null so why because totally we have only three that's why next you are getting next of three you'll be getting null but you are extending one after extending one we are getting count will be four but instead of one if you're extending three so already three are there again three you will get six okay but if you extending five five you can extend but six extending can I extend six is it possible no why already three is there if you're extending six it will be nine but you have defined only eight right error the subscript was greater than the limit of V Okay so check the program logic and increase the V limit if necessary so but if you are going for five up to five the code will be executed so previously what is that value after that what is a value you can see here right see limit you are getting eight count is three after that after extending counter you are getting eight eight only so next will be four after that you are you are one element getting count equal to 7 finally you are deleting all the values so nothing will be there in the v it will be empty okay so that's why we getting count equal to zero so we cannot specifically delete some element okay can I delete the element by index no I cannot delete it this way you'll get error okay so wrong number of type of argument in the delete yes delete we cannot pass the number so we cannot pass any value here so these are are the methods to access the element array we have to use this value if I want to increase the size of this you have to go here only so you here here itself the Declaration part itself a definition part itself you have to go for increase so how can we delete the value by V we cannot delete by index that's a limitations that is what V if you want to delete it you have to go for Nester table so Nest table you can delete the element by index but here you cannot do it as you add or delete elements but you cannot delete the element by index in V we cannot delete Elements by index see but it cannot exceed the maximum size whatever the maximum size we have defined we cannot exceed beyond that the database stores a V variable as a single object the database stores a v variable as a single object okay it's single object it will be stored like this a single object if a v variable is less than 4 KB it resides inside the table of which is the column otherwise it will reside outside that the table but on the same table space they will keep it in the separately if it is more than 4kb okay so to get value of each index value of each index yes you have to use 1 2 3 4 in that way so this is Index this is the value so 1 2 3 4 this way you have to use in Nest table and variable array this is the index value so index value we cannot change it but in V array the maximum upper bound we have to Define it right the upper bound we have to Define it but the N table upper Bound in the run time you can change it the upper bound changes as you add are delete elements but it cannot exit the maximum size okay so next one is next tables so we have seen variable array so this is what the variable array and we have the methods uh the next one for an example I want to go for okay something like a different one okay the limit so here I do not want to go for any limitation okay the number of element I do not want to go for whatever the number of element we want in the future I have to increase it if you want to go for in that way then you have to go for lest table if you see this Nester table the same way you have to use type variable the data type name here is table this is the keyword if you're seeing something called table it is a nest table okay Nest table of Vare 2 of 40 so all this are Vare 2 it's a 40 and this data type this is a variable name and I'm just giving this value so how many values I'm giving 1 2 3 4 5 6 7 8 so first I'm defining eight I'm giving all the values so four is nothing but s see this way you can give the the upper bound but here we are not we are giving any upper bound just we are defining the L table while in the initializing it so how many elements we want just we are initializing it okay but here I'm just initializing four but I'm giving more values okay then you'll get error okay so un leave see here um you'll be getting more values right be getting error so yellow yes we getting error here subscript Beyond count so why because I have used four here I'm using more than four but if you don't use then that's fine you'll get the value so color of four equal to Green you will get it right so but after that you can extend so we have defined only four here but if I want to extend yes you can use the keyword called extend see here I just used three here three values I have defined here but after that I want to go for four four more values I have extended but fourth value I'm giving green and color of four if you get limit so what is the limit you will get now same limit I will print here before limit and count I will print here okay you can see this okay so just I'm using only three here okay first I thought okay I can have only three elements but after the in the program I need more elements and count equal to three okay but if you're not using here but count equal to two right you'll get in this SP limit you will not get it remember the limit keyword you will not get in the tables so why because there is no limit for this that's why you'll get null value so you have to remember this for NE table there is no limits okay limit is only applicable for V so that's why you are getting null here okay count you will get it count three after that you have extended four so count equal to next time you are getting here seven so index of first will be first will be the the one okay the first index last means last index so last index value is seven so index of first element index of last element so these are all the methods are available in L tables but remember the limit will not be printed okay so here in in Nester tables you can delete the element by defining the index number see here I'm initializing three I'm just printing it count you will get three right then extending four after that if you are counting after extending you are counting so how much you will get it you'll get uh count equal to 7 now limit you will not get it you'll get null and the index of the first element all this and delete of delete means you are deleting all the values and count your but you can delete a particular element see I want to delete two okay so two I want to delete it after deleting it I'll go for two here I'll go for three here I'll go for one here 2 okay so you can see here I'll show you first so first we are defining three right color of count will be you'll be getting three only why because I'm just initializing it three after that I'm extending four okay the fourth element will be green right fourth element just we are printing here green next will be limit so limit you will not get it null then again you are getting count seven so why because here I have increased four that's what I'm getting count seven so first will be first index value last index value so prior to three you'll be getting two and color of one okay so one we are getting red here right so after that two we are not getting okay so here so delete of two from last two element will get deleted okay so one two so that's why blue and green has been deleted right delete of two how many elements are there four green and blue so up to Black it will be there right so that's why the two we will get it okay so first it is giving first and then prayer here to and we have deleted one element and the first one got deleted red black blue green okay so I'm just deleting from here okay color of one is finally I'm printing color of one is red and two is black three is blue and after deleting it it is deleting all the values then you are counting zero okay so here after deleting the values I'm printing the same one after deleting it if you print the same one we are not getting anything okay so after deleting you will not get it so if you are delet deleting so from here okay so it is you are deleting the value of red so index actually it got errored out I haven't noticed here see if you see this V color of delete one we have four elements right 1 2 3 four elements but I'm deleting first one see it is possible in lest table so this element I'm deleting it after that you should not print that element if you are printing yes you'll get error but two and three finally it is getting here right yes you're getting it if you're going for the count now see here count is six one element got deleted okay so here I'm going for four so four means the fourth element you should not print here after deleting it okay so like this you can do but fourth element I'm not printing you're getting right red black blue and last finally you are getting six okay so this is what you can go for n table but if you're deleting all all the element will get deleted finally you will get error you are accessing the count right after that you are getting count you will get zero uh it will not be errored out since you are deleting all the elements so from here if you put delete then it will get deleted all the element if you are making delete of index number that particular index value will get deleted see like this you can put index so which which element I want to delete it okay so I want to delete some element here 1 2 3 4 some element I want to delete it is this element alone I can delete so this will not be a continuous value okay so uh in Neer table you will not have continuous value but in in the v aray we will have continuous value so why because you cannot delete it in between so is the difference between nested element is limit and variable size yes correct so but we can delete by index yes we can delete by index so that's what we have deleted So based on the index value we can delete it and if even if you if you see this so exist if we color of exist of two then we are printing yeah elements not available so instead of going for the error right so previously we got the error so instead of going for error so you can use this keyword called exist okay if second element is exist yes it is exist right okay so what is this we'll see see if you see this type Nest table of AR 2 just I'm making the three one 2 3 then extending four more elements I'm just making the fourth element is green and I'm trimming two if delete means index by value you can index by value you can delete but trim means from last you'll get trim so you are trimming last two elements right green and blue you are trimming it delete of two means deleting black and you are going for color of four here we are extending it right 1 2 3 again we are extending four otally seven will be there seven fourth will be up to here then you are trimming two okay so that's what it's uh reminding okay so if you see this totally we will have seven array of values okay 1 2 3 4 5 6 and 7 okay first three we are defining again we are extended four then we are having red black blue and then green right yes you'll have then Primm of two Primm of two means these two will be trimmed okay then delete of two delete of two means the second will get deleted so that's why if you're printing four it's printing one 2 3 4 it is printing green okay and limits so limit you are getting null for next table you will not get any limit after that you'll be getting count four why count four 1 2 3 4 this is null value you have already right count four then index of first element you'll get one index of last element will be five see five only right index of last element last two element we have trimmed it five if we color do exist two two is exist no right no right if you exist we are printing otherwise we are printing not available so it is printing not available if you're going for three three is available the color of three is available right if you're going like this so instead of element is not available you'll get the col of three is blue so why because three is available now so this is what you'll get so trim of two trim of two means trim will delete from the last okay trim of two means last two element will get trimmed so trim means last only it will come so trim of two last two element will get deleted but delete of two means delete it will delete by index value not like a so delete of two means it will delete the index value so this will get deleted are you clear what the trim will do so trim of three means last three will get trimmed if we have to delete first three no first three you have to mention that 1 2 3 so delete of 1 2 3 index by number you can mention last three if you want to delete it you can go for trim of three okay if you want to delete first three you have to mention this three times 1 2 3 okay so index by number you can delete it so now you are getting what is the use of delete and trim so next one is associative array see in this associative array so we can okay so both V array and Nester tables the index value predefined and maintained by article in associative array the index and elements will be defined by the user we can Define for an example just I'm making associative array plsql is nothing but I'm making that procedural language for SQL SQL structured query language just this is a key value this is the key and this is the value key value pair so index and this is a value so index we are defining what is the data type index of vcat 2 value of vcat here also index is vcat to and value is vat if you see this declare type V array type is table of Vare to if you seeing this index by this is called associ you'll have the table it might be an ester table or it might be a variable sry index table so that's why indexing is nothing but this is called indexing we are indexing we are giving instead of see if you see this previously it should be 1 2 3 but here we are defining our own index value so color of 1 2 3 4 so we are mentioning like that and then we are accessing it same way it is delete but we here we do not have any limit function Prim function extend function so why because we are we are not going to extend it we have to Define key value path that's it so that's why we don't have any extend we don't have any trim we don't have any limit so only first last count so delete means all this will get deleted so if you see this this is the one right first color of three you are getting three then first you can go for first last is three count so count we are getting and after deleting it after deleting it if you're getting count you'll not get any count count zero but instead of deleting all the values we can delete the particular index value okay color of three only I'm deleting one two three will be available so count you will get three finally okay can we add value at the last yeah you can add you can add value at last so here last only we can add in between we cannot add it we want to add any value here so you have to go for one more value the index and the value that's it okay so sorry so finally you can do it so if you are if you are printing again the count you'll get to one more value count is four see so you can add a value this is our key our own key and value right we are not defining like 1 2 3 4 anything you can print here like even you can mention like one and three then five 9 anything you can print this is this key value is subone value we are choosing it so whenever you have you're going to execute any plsql block the article divides the plsql block into two different statement blade so if you have any SQL statement within plsql statement any DML any statement if you have then but this plsql block will segregate procedural language into procedural statement executor and SQL into SQL statement executor so this is what Oracle server will will process and if you have any plsql statement within the plsql statement if you have any SQL statement like insert statement or any statement then from the p SQL block to SQL block this switching will happen so this is called context switching right so I have already explained this context switching will happen so because of this context switching it will take lot of time okay the performance issue if if you're going for millions of record okay so you are going to process like huge volume of record yes definitely you'll be facing the context switching so what is this context switching okay so how it will impact I'll explain to avoid the contact switching we can go for bulk collect so bul collect means okay so bulk collect reduces context switches between SQL and plsql engine right so what we are going to do instead of each and every row row by row instead of going with procedural block to SQL block so instead of switching like this okay so only one time only one time at a time we are fetching whatever the data we need to process in PLS scale engine we are going to collect bully and then load it into this plsql block okay so after that you can start processing it so that is what plsql Block B collectable to okay so here if you see plsql engine allows SQL engine to Fitch the record at once a bul collect is a method of fetching the data where plsql engine tells SQL engine to collect many rows at once and place them in a collection okay so you can do three things one is you can do collections by using sorry you can do bul collect by using three different way one is you can go for the select statement select column names B collect into collections fetch cursor so if you're going to use cursor you can use this keyword like a fetch cursor bu collect into collections or you can go for execute immediate okay Cory string bu collect into collections but we will use always collections you remember yesterday we have seen collections right three different collection types we have so any one we can use it so how can I use this B collect if you see this select column names bulk collect into bulk variable from a name like this okay fetch cursor name bulk collect into bulk variable so this is what you can use even you can go for two other word word called bulk binding so bulk collect means collecting the data so from here from SQL engine to plsql engine we are collecting bu bu and bulk processing is called bulk binding so that's called your bar all statement okay so for all helps us to perform the data manipulation language okay DML operations on data in bulk so if you want to do any data manipulation operations okay in the bulk okay in the bulk way then you can use a keyword called for all okay it is very similar to for loop with for Loop it will happen record level one by one it will happen right so record by record it will go for so since it is going for record by record it will take lot of time that's why we are going for for all statement to collect bulk data at a time okay so for looping statements so this is a statement we can go for for all it's a single word okay so looping variable in lower range dot dot higher range so this is what you have to go for so sometimes what will happen if you doing bul collect okay if you're doing bul collect if it has huge volume of data then bul collect also performance wise we may face issues so instead of going for bul collect completely you can use limit keyword okay so you can you use limit keyword okay so you can limit say 10,000 record at a time or 100K records at a time so that's what you can use the limit key good so we'll see all the examples one by one so what is context switching we will see now so I'm going to create one table called just simply I'm going to create bulk table just the name of the table called bulk table just I'm creating with one ID column okay ID column then what I'm going to do so I'm going to create one more table called bul Bing so to process the data so two different tables I have created see here I'm going to use one Anonymous block so begin and end only there is no dear keyword here just I'm using begin and begin and end so here I'm going to use the begin and end within the begin and end I'm going to use follow Loop so for I so this is a lower range for I in from one to this 1 million record okay so for what for I in 1 to 1 million record looping statements an end Loop insert into bulk table values I so whatever the value I'm I am getting that just simply we are going for Loop so once 1 million record has been loaded I'm just committing I'm just ending okay so if you see this particular table okay so I'm just taking this select count of star from this table bu table there is sorry I should not use control enter just you can go for count there's no count okay so just I'm going to execute so this is called context switching okay this is called context switching why it is context switching if you see this begin I'm using plsql statement right far far is nothing but like a psql statement so in this plsql statement I'm calling SQL statement right so this for I in this one this will be taken this will be taken care by plale engine but the insert statement will be taken care by this one so 1 million times row by Row the contact switching will happen from here to here you see this so for I S1 then looping insert the SQL statement only process insert statement but increment by plsql statement right so if I'm going to execute it will take more time you can see here it is context switching is happening and it is taking a lot of time to load 1 million record how much time it is taking we'll see it has been completed you can see here task completed in 26 seconds right so we can see this task has been completed by 26 71 seconds so almost 27 seconds right okay if you go and check the count here you'll be seeing this count is 1 million so 1 million record has been loaded if you go and check select star from this table you'll be seeing all the records one by one 1 million record is there in the table okay it is taking a lot of time 26 seconds is has taken to load one particular column with 1 million record yes so the same scenario we are going for bulk binding so what is bulk collect or bulk binding bulk binding is nothing but processing the data okay so how we are doing it if you see this to avoid this I'm just using the bulk collect so this is the keyword okay select this time I'm using select select Star World collect okay select star bul collect is the keyword okay so if you see this what is the keyword for bul collect so this is a syntax select column names bul collect into collections okay so select star bu collect into collections it's a what is this it is a table right so record type even you can go for this is table so table record type they're using record type is table of bulk percentage root type it's a collection right so collections we are using variable of this particular type record type variable so from the record type in the collections we are loading the so collection we are seeing it will take the data as array of values right array of values so that's why we going for for all so here I'm using for all statement to process the data so from the bulk table it's a table right one table we have loaded already so from the table I'm going to take now I'm not going to take uh from the from like this I'm not going to take since I have data in the table I'm going to take the data from table and I'm going to collect all the data and store it into one variable called collection variable called VT okay so variable table just I'm just creating one variable that's it for this table so I'm going just storing the data at a time single time I have collected all the data and stored the value in the one variable after that I'm going for the SQL statement right so this is called bulk collect so for all means bulk binding so single time okay it will not go for for what row by row single time it will go for insertion so for all I in one to vt. count you remember yesterday we have seen collection methods so what is the use of collection methods the count so it will go up to this vt. count up to this count value we may not know how many records are there the dynamic dynamically it will allocate the aray of values okay it will take the count and it is inserting same thing I'm going for this bulk bind table insert into bulk bind table right so I'm going to use this bu B table how many records are there now select count of star from this one so how much time it is taking to process this particular data as from zero I'm going to run it this is also loading the loading 1 million record into the state okay so selecting the data and loading the data that's it so select and then load see how much time it has taken not even 1 second right so 0. 987 that's it so instead of taking 26 seconds the same data it has taken by using the B collect B collect it has taken within one second right so if you are going for any realtime project if you if if you want to process like a millions of data so you can always use B collect okay it's a one of the performance Improvement method in plsql so this is the way we will go for B collect still we will see more examples so what is the statement will do the statement will do if you see the statement we have already seen the statement right so set server output on clear screen declare and end and just I'm using two variables first name and employee salary so begin first name salary into V first name V from employees employee ID equal to 120 so just I'm going to execute this one okay so here I'm getting the value in this F okay so select statement in plsql Block that's it if I want to go for more data more number of Records so here I'm just using this F what will happen so it will be error out right be error out you you are getting more number of records from the table be out so too many rows so I'm just handling it exceptions so whenever the exception is happening it will go to the exception block and it will take when too many rows yes it will raise one exception that is nothing but too many rows so when this error is occurring so it will be returned this particular one we are not terminating the procedure just we are handling it exceptions we'll see the exceptions next one we are seeing that many rows are return from the base table right so that's what I have written here so it has been handled procedure successfully completed but we are getting error okay the exceptions handled but if I want to go for multiple rows what I will do I use cursor right I use cursor so remember we have used cursor so cursor C1 select statement all the data and we are opening the cursor looping and fch the value exit when see not found if you're going like this so from the beginning to end it'll go and print all the values right this is cursor so you can go for even cursor it will take lot of time if you have millions of Records so that's a disadvantage here so each and every record it will go for the the context switching okay so it will take some time so to avoid this we are going for the bul collect and composite variable so remember I have explain this one while taking the cursor so we can do more number of data we can go for bul collect or we can go for cursor so bul collect if you're going with so just I'm selecting dickler within the end I'm going for the begin select I'm taking salary column alone select salary well collect into ENT salary so what is this this is a collection so table right Nest table so I'm using typee Nest table salary is table of number of 10 just I'm using this one called number of 10 okay so this variable I'm just assigning it and I'm assigning null Val for this okay so I'm not assigning here you you can remove this no issues so just you can variable Declaration of this particular type then we are going for the for i right for I into this one looping statement completely yesterday we have seen first right the. salary collection. first so from the first value to last value it will be going to the loop and it will be processed you remember yesterday asked what is the use of this collection collection methodolog this see this kind of looping so this kind of data processing Bel collect this collections will be used mostly okay so if you see this it will be performed very quickly so that is what we are going for the bul collect so bul collect information okay so even if you see this these are all the bu collect one and we have lot of concepts are there so we'll go through one by one okay so bul collect to avoid by contact switching so that's what we have seen okay so now we will go for one more examples so if you take this particular examples it's called a context s so we have seen already but this is for employees table so what is this particular procedure will do create or replace procedure just one procedure called increment so increase in the salary I have to pass Department ID as a input parameter and increase percentage so how much how much percentage I have to increase for this particular department so that we have to give both are input parameter so one is what is a employees. department data type increase percentage is number is begin so I do not have any variable to declare I'm going with for Loop so for this is a record right so record in in select statement so whatever the select statement whatever the department ID I'm passing from here so that will be past here right so that will be P here Department ID increase salary. Department ID in so this input parameter will be passed it will be placed here okay so it will be placed so for an example Department ID 30 I'm passing select employee ID from employees where Department ID equal 30 so all the employee will be taken and it will be stored in the record this employee record then I'm going for the loop or this one record dot I'll go one by one so update employees set this one employees so increase end Loop so just I'm going with so just I'm updating the salary for this right so I will go compiled I have compiled but I want to execute okay so I have compiled here and I have to execute so if I'm going to execute just this is the procedure name input parameters Department ID and percentage I'm passing the department ID okay any Department ID can pass 10 percentage increment on the salary right just just passing 10 percentage so but here I'm rolling back to to the the update statement if I'm going to update it for the 50th Department it will go and update or all the record 10 percentage increment that's what it will go for R Loop okay okay so wherever the employee ID here it is passing so those many record it will be passed here and it'll go to the loop okay so it will go to the loop for all the record in percentage increment on the the salary so this is like one by one one by one the record will be processed right this is one by one still here if you see this Loop every record will go for if you have some 1 million record or some 100K records so every one by one all the record it will go and update so it will take lot of time so to avoid this we we can use bul collect okay so we can use bul collect so what is that see BU collect you can use different way as I told so one thing is you can use St select statement or you can go for so bul collect to F employees data so the same way I'm using this bul collect select star bu collect into lore employees this is nothing but it's a collection in the collection it will be stored all the values from employees right so collection. count you'll be getting how many count right so each and every one value for this index just you you can go for record also in one two build the count I'm going for the loop so one time I'm just collecting that all the values so from this table and loading into this lore employes local variable okay of this particular table data type and from here I'm going for the looping so this way it will reduce the timing it will reduce the timing it will load the data so instead of going for looping the statement to print this even you can go for anything like insert statement update statement all this so any statement you can use so for all the record it will update so if you go to the previous statement in this previous statement we are updating only for 50th Department in person increment but we are not going to update just I have rolled back so because I do not want to update employe table that's why I have rolled back so you can use this way for the bul collect to avoid the bul to avoid the context switching we can Implement two different way so one is you can implement the functionality in pure SQL statement but this is also will take more time but you can use bu processing feature in plsql so that's the way you can increase the performance see this B collect is we are using limit here so limit okay somewhere limit will be defined I can take so this is called bu collect we are collecting data from the base table and storing into one plsql variable called Ln score employees after bringing the data to plsql block that is our local memory then we are processing it at one single time okay this is called bul collect it will be processed very quickly to avoid the performance performance degradation you can go for bul collect so here just we have used the limits okay the limit keyword so what is the limit so here we are using limit is 100 so you can use limit more uh number of limit okay it's a constant we are defining why because this number should not be changed and then this PLS integer what is that I will explain this is plsql data type okay plsql data type is called PLS integer this one I will explain later default value I'm giving like 100 so instead of taking the value more values just I'm collecting only 100 values see you can go for something like 10,000 record at a time okay so 10,000 record 10,000 record so that you will not get more performance issue even though you are going for bul collects you can use this way fch this is cursor okay so cursor bul collect into this one limit just you have to add one keyword called limit that limit you have to declare here that's it other than than that everything is similar this is cursor this is Collections and we are just looping and we are fetching and just here printing that's it so instead of printing you can go for in real time project you can go for uh SQL statements okay so this Loop for cursor Loop okay this Loop for for Loop inside for Loop so as we have seen already using cursor how it be performed and by using the bul collect how it will be performed what is the contact switching if if they ask in interview you can explain them okay so clearly if you are going for context switching so definitely it it'll be uh taking more time so instead of going for contact switching we are going for this one instead of printing the values if you use dbms output it will print a value in realtime project in instead of printing a value you can use some executable statement here you can some calculation you can go for okay some calculations you can use this this value you can use any values inside if I want to perform okay so mostly the create table statement and all we will not go for dynamic SQL okay but for something something like the back backup tables backing up the tables and deleting the backup tables from the from all the tables so just we will create some Dynamic sqo so to build p quy and update applications we'll go for dynamic SQL so static SQL you know already static SQL means we are writing SQL statement so that SQL statement if you're going to make okay any skl statement you are writing so this is static skl right this is static SQL the SQL statement will not change so it will take action when compilation if you take any psql statement also if you're writing plsql statement action will take when takes in the compilation but Dynamic SQL action when application is running so that's what it will take why because so if if you giving static SQL article knows it already Okay so article knows it so if you see this these are all the static statement whatever we have seen action when application is running it's a dynamic right it is add a query only it will not find okay so if you're writing any SQL statement static statement only compilation it will find the value but Dynamic value compilation it cannot find the SQL statement also will have all the values only while running it then you'll get the actions so that is what we will call it as late binding late binding means late processing Dynamic query will go for late binding static query will go for early binding so that's what the differen is so what is it to implement Dynamic SQL we can go with two different way so one is execute immediate other one is dbms uncore SQL see this dbms undor SQL we do not have permission here in the Real Time Project only we'll have we do not have so privileges to use this but we will see this execute immediate so what is execute immediate Dynamic DML dynamic ddl dynamic plsql block so anything you can use okay so DML statement ddl statement anything for an example I will go here I will create one table okay so I want to create we have any customer table so let's start from customer yes we have the table so I want to drop the table so is it possible to drop this table in any plsql block can I use begin and end drop table table name can I use no we cannot use any any ddl statement inside the plsl block okay so inside the plsql block we cannot use any DL statement as it is so if I want to use this you have to use execute immediate okay if you want to use it you have to use keyword called execute immediate so this one you have to use so if you're going for drop table you'll be using execute immediate and if you run this within the block plsql block any block okay so within Anonymous block or functions procedure anywhere if you want to use it yes it will be dropped okay it it will be dropped here so there is no table as of now if you go and check here there is no table customer okay say for an example I have some tables I'm just creating create table table name customer uncore black select start from sore customer _ SD this table I will create it one backup table okay so the same way I'm creating one more backup table okay okay so one more backup table here and I'm creating employees backup table just I'm creating some tables here the employes backup assume that we are creating the backup table so lot of people will create in realtime project we are creating so I'm creating one table called departments backup from Department's table just time creating many backup tables lot of tables are there if you go and check here select start from allore tables okay so where owner equal to this HR right so if you go and check this table sorry owner equal to this one so many tables are there right so I want to check how many tables are there with backup and table underscore name like single Cotes backup percentage single Cotes so there are four different tables are there so I have to delete all the tables with backup table how can I drop it can I use this way drop table table name so each and every single single table should I use no right no need to use single single table so you can create one plsql statement okay so drop all the tables okay so how to drop all the tables it can go like this so if you see this particular statement begin and end for this is record so just I'm taking the record you remember we have seen record here just I'm selecting the table select table name from the allore table where Upper of table name like backup so just I'm taking this so upper of table name equating with upper case even you can use HR schema where own equal to HR own equal to HR so I should not delete any other owner that's why okay so I will run it so I will check whether I'm getting only these four tables yes I'm getting only four whatever the value I'm getting this one I'm storing into variable called record and I'm going for the loop loop execute immediate drop table it's a hardcoded value drop table table name it will go into the loop it's like array of values first value it will take record do table name this value will be taken drop table table name and it will be executed immediately so if you go and execute how many tables are there with backup it will get deleted all the tables now if you go and check here none of the tables should be there clear it has been dropped table has been dropped that's it so if you want to drop multiple tables at the time or backup table or something then you can use this way this way you can use this is a statement even if you see this I will show you here one more statement for dynamic SQL create table table name as select star from customer details I think customer details there is no table now sore customer SD this table I will take with that I'm just creating one table called test one okay and selecting the test one so here I'm just uh selecting the table test one created I'm just selecting this is a table so just I'm dropping the table that's it okay it's a compilation so I'm going to do the compilation here and whatever I'm going to pass value for this execute to the procedure execute procedure name inside I'm passing the table this particular table will get Dro automatically right so here if you have so many tables even you can create as like a function then you can pass all the values as a parameter and everything will get dropped automatically okay so this way you can uh this way they will the mostly like the DBS team will process like this and see here one more example I'll show you create or repl procedure update Dynamic customer table so I'm just passing a table name a Vare and column name and customer ID execute immediate update table name set the column name okay so column name equal to system date where customer ID equal to this one so what is the column name I'm going to pass okay so just I will select select star from customer table I should have the customer table right otherwise it will not be executed correctly the table name also you can pass no issues you can pass the table name sore customer s this table you can pass no issues I'm just passing update statement here I can have this this value so what is the table name table name is this one okay table name is this one there is no column like uh data but here I will use mobile number okay I want to update the column mobile number with some other value okay so mobile okay sorry here it is taking system date right uh we should have taken some other values here some hardcoded value I will take okay just I have taken some hardcoded value that's it see I should have the column called Data bir here then it will take system date but here I'm passing this value do this but in this particular table I'm passing the table name and column name and the record which record I have to update so just I'm passing okay customer ID is not available here right so where is this customer ID here customer uncore ID right okay correct so I'll compile it one more time I will Ex Ute okay it has been updated and if you go and check here so I'm not mentioning which table right so in the runtime I'm mentioning which table it will go for so as I told this execute statement you can use DML statement and ddl statement also okay both you can use it not only for ddl statement so you can use it for ddl statement DML statement plsql block okay so this one here I'm using for DML statement execute immediate DML statement dynamically I'm passing right the values are dynamically passing what is this value will pass from the runtime right that's what I can go with table count so I want to know the table count I want to get it so execute immediate select count of star from the table name into this one and I'm just uh printing it if you want insert into status table uh stats table so here I'm I'm passing how many records are there in the table so this is the one but we need to have this stats table here okay so but we do not have access to this table so that's why we cannot insert it but if you want to take how many records are there in the count if you use this count tbms output count it will take select table name owner name allore table where own equal to HF right so each and every table it should print count for all the records but the thing is we do not have this table okay it will not be used if you're going to print only count yes it will print only count only how many tables are there in the HR schema for all the tables it will go and print okay so this is one thing we will go for one more statement different statement if you see this plsql block so what the block will do if you see this you can able to understand create or replace procedure procedure name generate new table I'm going to generate a new table create table statement create table table name that is what our whatever the value I'm passing here column names okay Open Bracket columns data types and using this dbms output right so execute immediate so here I'm going to pass this way I'm just compiling it execute immediate L string so L string is nothing but it's a variable whatever here it will be there it will be stored here in this variable just that variable alone I'm just executing otherwise you can go for execute immediate simply make that create table statement so you can use without execute immediate but thing is here it is a dynamic SQL right so dynamically you have to pass the values correct that's why we are going for execute immediate string we are getting it so if you see this just I'm taking columns okay so how many columns are there so only two values we are passing how we are passing three values product table ID number okay product name Vare to quantity number okay so so I'm just going to create create table table name that is products first one ID is number next one is product name whereare to quantity is number so just all this I'm just passing and it will automatically frame this SQL okay I do not have uh create table statement here okay this this we do not have privileges to execute but if you are doing it in plsql code so this is the way it will be executed okay instead you can go for this way the anonymous block uh you can try something like this so try like this it will be executed and uh even you can try this way also update statement right but create table statement we do not have permission so sometimes they will pass all this as a parameter so from the par file see normally here we are giving hardcoded value right say for an example for this procedure we are passing all this Valu as uted value here also I'm passing the value right this one update statement this will be passed from the parameter file also okay parameter 1 2 three in that way it will be passed but we will use if they are using any plsql state execute immediate so that is nothing but Dynamic SQL yeah this is one of the best example for backup table clearance so per statement we will use right so this is what you will do so if I want to use multiple tables I want to update here only you have to use pass you have to pass more parameters that's it but normally create table statement all we will not use any procedures like this create table statement will not create it see why because instead of using like this you can use directly create table statement but this kind of backup the table deleting the backup table all this we can do by using Dynamic SQL only we will frame mostly the DBA will frame this this kind of sqls next topic is called exceptions so what is except exceptions okay sometimes you are having very big SQL statement okay very big SQL statement you are writing plsql statement you're writing begin here and you are writing end and if you have any any declaration part you'll be declaring here right so declare and begin and end so within this begin and end you'll be you'll be having some executable statement okay say for an example you have the statement one you're executing statement two you're executing statement three so you have lot of statements if any if all the statement if it is executing fine that's fine but if anyone statement say for an example execute you are executing the statement it is getting error out so what will happen if it is errored out it will check whether we are handling this particular exceptions so you remember try catch block try catch something like that in Java or any programming language we'll have TR catch right Java we have TR catch block right so you try this if any exception happening you go to this particular block so try and catch it this is what we'll have so you can if you want to handle this particular error if you don't handle it this particular program will stop AB brly okay so it will middle of the code it will be stopped but we have to handle it so you can go for the one called exceptions you remember very first time it's S I have explained this exception is like not a mandatory one optional one so within this you'll be writing when some exception error code so error uh the exception name will be there so with that exception name you can so print something okay so print here okay you can inform the user that so because of this exceptions it is failing so you can handle it so you can handle it you can use some go to statement you can go to the particular statement if anything error happened you can go to the particular statement see what the compiler will do if any error is happening here it will shift this one to a exception whatever the error we are getting here okay some error we'll be getting so that error will check here whether we are using that error if it is not there then it will it will it will show like exception is happened it will print exception is happened if you have handled here so by default we'll go for one unnamed exceptions so that means we have two types of exceptions named exceptions unnamed exceptions so this is called exceptions exceptions are uh there are two types one is predefined exceptions it will be handled by Oracle if there is no predefined exceptions I want to go for customized exception handling then I can go for user defined exceptions this is implicit exception this is called explicit exceptions implicit means Oracle itself it will will throw exceptions explicit exception we are creating it in this we do have two types of exception one is named exception another one is unnamed exceptions what is named exception see article has some around 25 to around 15 to 25 named exceptions will be there here if you check here named exception will be there whenever exception is happening your Oracle code Oracle compiler will go and check this exception name whether you are using this exception in the exception handling block okay if there is no exception is mentioned at least you have to mention the exception with unnamed exception called others okay if you're using others so that is called unnamed exception so any abnormal condition that interrupt the normal flow of execution of your programs program instructions at runtime is an exception so in Oracle we have predefined exceptions and user defined exception so predefined exception is Oracle will take care user defined exception is explicitly we are giving the exceptions okay we are handling it so what is predefined exception it's a system defined exception or already defined maintained implicitly by Oracle like implicit exceptions we have two types of exception here named exceptions unnamed exceptions wherever you are seeing others keyword so that is unnamed exceptions okay so named exception means say for an example I will take this scenario I'll go and accept run this so what we are doing declare Vore salary it's a number data type so just I'm taking this Vore salary just I'm printing zero divide error so if I'm going to run this particular code you'll be getting error here right error here so what is the error number 01476 whether we have any exception redefined exception 01 476 if it is there we can handle it yes we have right so Z divide the exception name is called zero divide if I want to handle it this is called named exceptions if if you want to handle it I do not want to go for something like error doubt I want to handle it in the plsl code then I can use this like this okay like this if you're using like this this is called named exceptions zero divide is a name exceptions others is called unnamed exceptions so I'm just to both I'm handling it if any other exception is happening so this one I'm just creating it and I'm going to do it showing like deviser is equal to zero operation is not allowed okay so I'm using this one now do we have employees backup table I think no so if any other exception is happening okay so when others so this is also or iph 00942 so this is not happening here right table or view does not exist okay so 09 942 we have 942 but if you see this this is not going to this particular exception handling block okay there is no zero divide and others also it's not happening so that's why we are getting this table or view does not exist actually the table r view does not exist so that we are handling it so if you are defining correctly the keyword then it will be handled so you can use some you can check so what is this particular corresponding exception name if any exception is happening it will be passed to the particular exception handling it will be processed so if you remember for this if you see this cursor one while taking the cursor one I have taken the first one something like exception handling right if you see this I'm just running this if you are using one single record one single record where employee ID equal to 220 actually this is not available this 220 is not available if you don't use it if you don't use it handling exceptions then you are getting this error 01 403 01403 so you can check here 0141 403 no data found so we have to use this one no data found right yes if you're using that particular name no data found if there is no data found this it will be it will be executed too many rows then it will be executed when others any other errors then others will be captured unnamed exceptions you can see here this will give you no data found for this employee it is not getting errored out it is it is it has been handled but I'm going for too many rows too many rows so now if any exception is happening this control will be given to the exception handling it will check whether no data found no too many rows yes then it will be printed here see here this time it is printing many rows are returned from the base table right so that's what the exceptions should be handled so these exceptions are Oracle predefined exceptions so we have some 25 to latest exception article predefined exception sheet you can download and if you are using this way this is called Oracle predefined exceptions something like value error okay say for an example here you might be getting value error so what is value error so value error means number one I'm taking number of two but I'm I'm taking five numbers so it is value error this error name is called value error when value error then you are you are printing this way right when value error then you are printing error but you are going for something like a different one see cursor I'm using cursor C1 so what is the error I will get it so cursor I'm using here but the thing is I just declar the cursor I haven't opened the cursor before opening I'm just closing so it will throw error like invalid cursor okay so invalid cursor then if it is throwing this particular error invalid cursor we have something yes 0 1 01 invalid cursor error exception happened check the code this one we have written so this will be handled here so like this for the different type of error you can use this particular Oracle predefined one just you can handle it but some exceptions will not have name okay you can see here some table I'm just creating one table customer drop table if any table is there I just dropping it I I'm going to drop the table called customer I'm just creating with number of 10 okay so I'm going to insert the data into this values some more values more than more than so what is error you will get value larger than specified Precision allowed for this column so you are getting 0 0 right and also 01438 so do we have anything 01 438 01 438 no right yes this is called unnamed exceptions so in order to handle this unnamed exceptions if I want to give a name for this unnamed exceptions for an example like this I have to give a name for this unnamed exception invalid cursor login denied no data found something if I want to give a name for this unnamed exceptions you can use this F okay you can use keyword called ragma exception init it is nothing but you are you are providing a name for the unnamed exception there is no name for this exceptions value for largest specified right there is no name for that exception to to handle that particular exception we are using so we can use others say for an example we can use others here see here this is called unnamed exceptions if you want to handle it you can handle like right so if any error is happening so it will be handled by others block and it is showing like but others is not a named block right named exceptions it is unnamed exceptions for this kind of exception if I want to give a name for this particular exceptions I have to use the keyword called pagma exception in it okay so what the keyword pragma exception init so this one you have to use pragma exception init so you have to use this name okay so the name of the exceptions exception customer ID value limit I'm giving so this is like exception I'm declaring it I'm using that okay I want to give a name giving the name and I'm giving one particular number for that have you seen that so here previously it has this number 01438 the same number I have given while executing this one it will throw error like has been handled when this error happened it is printing so if you want to print this number yes you can print the number here so something you can print but it has been handled so this way you can handle unnamed so if you want to give try catch block we do not have that's why we have begin so begin is nothing but try if any exception happening catch it here that's what exception we do not have any try catch it is similar to Java I I have explained in Java we have TR catch but here we do not have TR catch then we have user defined exceptions say for an example I want to raise an exceptions for my customized problem customized one I have to raise a exceptions so how can I raise it see here it is giving you the error like table r view does not exist 00942 table r view does not exist I'm going to insert the value not enough values so these are all Oracle predefined one right it will be available up to up to this number so how how many digit five digit number so if you are if you want to give our own number then we have to give the number from 2 this number okay if you see this I will explain I'm just creating one table called employee exceptions employee ID employee name just I'm creating one table okay alter table table name add primary key I'm just altering it adding a primary key and I'm going here so what is this if you are adding a primary key on this table index will automatically created right index will be created and uh create or replace procedure procedure name add new employ employ ID in parameter first name I'm passing through this column insert into table name employee ID and employee name values I'm passing commit exception I'm using exception duplicate value on index and then duplicate value on Index this is exception normal named exception only then I'm using so here I'm raising application error application error I'm just text racing it by saying this number you are trying to insert duplicate value some employee ID okay so I'm going to compile it add employ right EX see add employee so I'm employee ID I'm just giving and employee name okay so some name I'm just giving you can use some begin and end also so I'm just using that it should be loaded select start from it should not get any err error out right so I'm just using this has been and one more Prime I'm trying to insert it what will happen so we are getting error customized one right or I 21 you are trying to insert a duplicate record I'm raising the exception myself I'm raising the exception right so for the customized one this scenario I'm using it so even I'm I'm raising customized one by using this named exceptions named exception only but I'm racing it okay I'm not handling it I'm just racing an exceptions so here if I want to handle it or if I want to raise it any other scenario then you can use this way say for an example racing an exceptions this is what you can raise it raise application name if condition can use okay so I will take this one employee union exception employee ID first name same table I will take okay same table I will take employee union exceptions but uh this table insert into employee exception first table itself I will take employee ID first name I have to pass if employee ID is less than 100 I'm raising an error exceptions okay so here I have to Define it decare it and I have trace it else I'm just inserting it and if when this error is happening raise application error that please enter email ID is greater than 100 okay so I'm raising an exceptions for my customized one okay so first name is invalid identifier sorry I forgot what is the name of the column I have given EMP name right so EMP name EMP ID EMP name okay I will compile it has been compiled so now I go and execute this one okay so I'm trying to execute excc name employee ID if it is less than 100 it will not be executed right it will not be inserted 101 so just I'm giving this so successfully compiled but if I'm giving 11 okay it should Pro error that please enter the employee ID is greater than 100 so explicitly I'm just racing an exceptions okay I'm raising an exceptions so here I'm not handling it I'm just racing an exceptions so that is what raising how to raise exceptions if anything happen how to raise it if you're if you're giving this way yes this is the way it will raise it and in exceptions they'll be asking questions like what is SQL code what is SQL error okay this one error ERM SQL code is nothing but it returns number code of the most recent exceptions see whatever we are seeing this one this is the number code this is the error message okay so something we are seeing here right these are all the number code so user Define one you have to start from this number only article predefined one 0 02 up to this number it will be available whatever you are seeing here these numbers are from user defined one okay if you see this this code whatever here we are seeing 0 divide by error when Z divide error just I'm printing what is the SQL message what is the SQL error code error message error code error message is or I 01476 deviser is equal to zero error code is this one so 1476 see sometimes they will ask what is this error code and error message all this you can explain this if they are using any plsql code so definitely they'll be using exception handling or they might be explicitly raising raising an exceptions by declaring the exception here it's like uh they want to raise exceptions for their particular project just they will raise it to this way temporary table in article we have two types of temporary tables in article one is global temporary table and another one is p uh private temporary table so what is temporary table say for an example if you are logging into the system okay Oracle database that means this is one session we'll call it as a one session okay you are logging into the database this is called one session okay one session you're logging into then I have to create some table you are executing some long SQL statements it's like contains some four to five tables or six to seven tables I do not want to join all the stable as it is I have to create some temp tables okay these two tables I have to join create temp table called a another three tables you have to join called B these two tables I have to make a join so finally but these two tables I do not want to keep it once the session has over right I do not want to keep it then I can create like a temp tables so if you if you're creating a temp table this is coming from 8 I Global temporary table okay so Global temporary table you can have it from the version 8i but private temporary table it's comes under 18 c version okay 18 c version only you have the private temporary table okay so we do not have access to create this private temporary table but we we can create Global temporary table I will tell you the concept okay so we have the global temporary tables so available from a I so if you see this I'm just I want to create one temp table on Commit delete rows so what is the meaning of on Commit delete rows right create Global temporary table so you are creating a global temporary table so my temp table ID with the description on Commit delete rul so if I'm going to commit it it will not have any data so I'm if I'm going to commit this finally it will not have any data here I'm going to insert some data okay in this table see here one something I'm just loading it will have one row just if you checking this you'll have the data right count is one yes count we have but here I'm going to do commit if you do commit then data will not be available okay so with before committing it data will be available so after committing it the data will be will not be available that's on Commit delete row so if you are going to commit delete the row so that's the thing and you can use so private temporary table we do not have Okay so on Commit preserve R this way but we may think that okay it will preserve the data so on Commit preserve row so I will log out from here disconnect from the session one more time I will connect the session okay so here I'm going with this table right okay so I will select the table select start from this is another session so we have the data right sorry we have a table but it should not have any data okay so now I'm going to insert some data here so what I will do this table I will drop it drop with the I'm going to drop the table recreate with so I have dropped it I can go here on Commit preserve row on Commit preserve row just I'm creating then I'm using this insert statement so insert into temp table one row got inserted you are seeing the data yes this here just I'm committing it then you are checking it the data will be available so I'm going to disconnect and reconnect it okay with another session we'll check it now whether this is available or not we are making on Commit preserve rules but why the data is not available so this is the advantage of temporary table okay So within the particular session the data will be available if the session is over okay even though you are making this on commit or or whatever it may be on Commit preserve R means particular session this will be available but next session it will not be available the structure it will be maintained okay structure it will be maintained even if you're having 1 billion record today okay yes uh within the session it will maintain all the data but the session is over okay if it has been the the connection has been closed uh if the particular session is over in the server then the data will get erased automatically so that's a advantage of global temporary table the structure will be available for next user okay next user I I am the user one I logged in I have loaded here 1 million data I'm using it now I'm using you are the second user okay you are the second user you are logging into the same system you are seeing the Global Temp table but you are loading another 2 million data here yes you can load it this session will have different data this session will have different data if you're closing it all the data will get released so that's a temporary table Global temporary table but private temporary table means okay the structure data will not be available okay okay clear structure and data everything it's a private to the particular session and if you want to create any private temporary table you cannot create as like this you have to create this way okay so this way you have to make on Commit drop definition that means commit if you're going to commit it will drop the definition it will not be available but it will automatically it will do this only need to do if you're coming out of the session nothing will be there but when we will create we are handling huge volume of data joining all this if I want to create if you want to load temporarily some data after that you should not keep that temp data right see you are creating some backup data backup Table after sometimes you are for you forgot to delete the data in the table so it is creating lot of memory space in the database so that's why they have created the Lo uh table called temporary table Global Temp table if you are logging into system you are creating some three columns another person going with the the table with the four columns you can create it that's a beauty of uh temp tables with same name but different structure you can create