Creating and Declaring User-Defined Types - Pl/SQL Tutorial

Creating and Declaring User-Defined Types is a free tutorial by Intellezy Trainers from Pl/SQL course
Link to this course(Special Discount):
https://www.udemy.com/course/oracle-plsql-fundamentals-vol-i-ii/?ranMID=39197&ranEAID=Gw%2FETjJoU9M&ranSiteID=Gw_ETjJoU9M-cSB4pxtDq5r77f_U85ngHA&LSNPUBID=Gw%2FETjJoU9M&utm_source=aff-campaign&utm_medium=udemyads
This is the best Pl/SQL Course
Course summary:
Over 90 lectures and 20 hours of content
Prepare your enviornment
Introducing Database Program Units
Creating and Maintaining Packages
Using System-Supplied Packages
Creating and Maintaining Database Triggers
Implementing System Event Triggers
Work with Oracle database tools
Understand and work work language features
Work with DECLARE, BEGIN & EXCEPTION clause
Understand and use nested blocks
Use explicit cursors
English [Auto]
The last thing I'm going to show you is how to create a user defined type the user defined type is where you want to add one particular field into a nother data type. But there's more of a hierarchy involved. Now it seems kind of weird is kind of hard to explain to be quite honest it's something that you have to kind of see to kind of get a feel for it. But the perfect example of this is an employee bonus. Now for most employee bonuses you get cash so you have one field to identify an employee bonus. So it's a numeric type whatever. Well there might be some situations where a bonus consists of more than just a numeric value. Maybe it's a salary increase maybe you get a company car maybe you get more vacation time maybe you get some other perk maybe you get a key to some executive washroom. Who knows. I mean personally I've never gotten any of this stuff so I don't know what you might get. Who knows. But when you go to create a data type this data type might be might consist of those three things a numeric value car and vacation time and then you give this a name and then you can use this name in some other data type or as the data type for something else. So let's take a look at this as a means of giving us more hierarchy. So here's an example of a user defined data type. So we have our bonus compensation and it is of type record. And so we have our cash payment. We have a numeric six company car which is a boolean value and then you have vacation weeks which is a numeric too. So it's basically where we're creating a type of record and we've given it a name. So it's nothing we haven't already seen but we're calling this bonus compensation. Well then down below we create another record. So here we have type employee record is record and here we have the social security number we have the last name we have the department but then we have the bonus payment and the bonus payment is of type bonus compensation. So now let's think about this for a second inside of the employee record down here. It looks like we only have four fields when in fact we have six because we have the social security number. We have the last name we have the department name. And then we have a cash payment company car and vacation weeks because that's what is stored inside of the bone as compensation. So now you kind of see where we can start creating a record or we can create a data type that is more than the sum of its parts. So you can see a little bit more. So let's take a look at this I've opened up the complex user defined project and here we have the same page that we already saw where we have our bonus compensation is of type record. And it says it's a cash payment company car and vacation weeks. Pretty simple. Then we have our employee record in our employee record has a souces social security number last name department name. And then it has the bonus payment which is of type bonus compensation. And then we even have the manager record and a manager record says it consists of the Social Security number and a bonus payment which is the bonus compensation as well. So we have the employee which has a little bit more but then we have the manager and all we care about is the Social Security number. Then all we're doing is now that we've declared those tables or those records now we use them. So the best employee is using the temp record. Best manager is using the manager record data type. So these are just user defined data types that we've worked with. So then the we use a couple select statements so the select statement says I'm going to grab the Social Security number the last name the department name into the best employee social security number last name department name from the employee department and work on. We have all of this. We've seen this before in the earlier lessons and then we have the best employee we say the bonus payment the cash payment is $5000 the bonus payment company car equals true and the bonus payment vacation weeks they get one extra week. So literally think about this for a second. We're using the select statement at the