Creating and Declaring User-Defined Types

Intellezy Trainers
A free video tutorial from Intellezy Trainers
Computer Training Specialists
4.4 instructor rating • 225 courses • 158,433 students

Learn more from the full course

Oracle PL/SQL Fundamentals vol. I & II

A complete introduction to PL/SQL database programming language

19:59:18 of on-demand video • Updated May 2020

  • 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 top to fill in the initial information for the best employee and then in the best employee We're going to the bonus payment and we're breaking it down into that hierarchy. And we're literally Phili in the values for it that we use our output line and then we test whether or not the person is going to get a company car. So if the best employee bonus payment Dohm company car equals true then we output a line that says company cars also provided. And then you have the best employee bonus payment vacation weeks. If it's greater than zero then show how many extra vacation weeks we're granted. And then you come down here and we have our manager so we have the select statement which fills in the manager information so it gathers the best manager and then we're saying the best manager is going to get a $10000 bonus. Company car you get two weeks of vacation time and then we put that information as well. Again we're using if statements. Something we haven't seen so far. The if statement Don't get too hung up on it we will be working with this in detail later on. But we're using an if statement to see if some value is true and all we're doing is we're identifying the best manager which is the name of the variable and then we know inside of that variable. We have a field for bonus payment but then bonus payment consists of three different alternate fields company car vacation weeks and whatever the other one is. So now if I run this process at 5 now we have our output. So down here the best employee is Narod in research gets $5000 company cars provide it and gives one extra week of vacation time. And the manager is security number 3 3 3 4 4 5 5 5 5 kits 10000 a company car. And it says number vacation which granted is one. And I think that's wrong. Did we say to employee best manager of best manager bonus payment out here I had best employees so I need to change that to best manager. So now if I run this now extra vacation weeks granted is too. So here you can see that you can reference anything you want. So this this example might be a little bit much. I mean it might just take you right over the top. The thing is you need to remember is that all we're doing is we're building on everything that we've done so far. We started off with a simple type then we went to the percent type the percent road type then we created a table we created a record. We started creating our own user defined types and we're using each one of those to build upon what we're trying to do. It's not that difficult once you understand the underlying procedures for creating the variables and creating a prototype or a record or a table. It's not that difficult. It's just a matter of using what we've learned and incorporating them into what we're working with to make our end result more powerful. Very very cool. And this was just the Declare statement. Imagine what we're going to do when we start getting into the again and the exceptions and everything else. It's very very powerful. Sequel is amazing.