Working with SQL Plus

Intellezy Trainers
A free video tutorial from Intellezy Trainers
Computer Training Specialists
4.4 instructor rating • 198 courses • 152,965 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] All right now we're going to get into a little bit of an area that works with sequel plus and some of the ancillary things that we work with with in Piel sequel The first three is sequel DML and sequel DTL. Now these are two acronyms that you probably may have heard of may not have heard of. Now you have. So the sequel DML DML stands for data manipulation language. So this is what we are going to be working with quite a bit. These are the insert statements the delete statements the update statements all the things that we do within sequel to manipulate our data. So going to be used a lot within this course now sequel DTL or the Data Definition Language. That's not going to be used so much in our in our environment or in the things that were going to be working with. This is where we create and alter the underlying data or the tables within our database. We're not going to be messing with that. It's going to be primarily working with our DML the data manipulation language. So the DDL outside of the scope of this course we're not going to be working with it. So I just wanted to let you know. So Piel sequel with its sequel Plus there are some things that you need to know when we're working with incircle Plus like I said you're probably not going to use the sequel plus that much but if you do you need to know some of these rules actually you need to know all of these rules. So the first rule that we have is declare and begin still tell sequel plus that a program block is being entered. So our program blocks the declare the begin the end. Exception it all tell sequel plus sequel plus knows that those are reserved words within a script and it knows what we're talking about. The semi-colon does not terminate input into the buffer so as we're typing in our code within Supro Plus we're literally typing into the buffer of the machine when we put our terminate statement our semi-colon. That doesn't tell us equal plus what we're done editing our code. It just says that we've terminated that line of code. So if we want to tell sequel plus that we are finished entering everything in and we're done with our script we need to do that with a period or dot. The dot tells it oh they're done entering in their code. Pretty simple. The run or the slash command still executes or compiles our block and executes that code. So keep that in mind that is still the same. We've already seen that as a matter of fact. And then finally sequel plus variable substitution. The ampersand where we prompt the user for a Social Security number or a person's name or whatever that case may be that is still valid within siecle plus and that's a very powerful tool. So keep that in mind when we're working with in secret plus especially when we're when we're working with our variables. The variable substitution is a very cool feature. So keep that in mind now generating output within sequel Plus we've already seen this as well. But I still want to I want to discuss it a little bit more. Peale's sequel programs may write to the output buffer using the DBMSs on your score output command. So we've already seen this but before you can do this you must execute the set server output on command. We've also seen this if you don't execute that command. All output is going to be nullified. It did says I'm not going to waste my time because I never said to do that the syntax for the output uses the typical format which is we have a package name dot procedure name and then some parameter. The command that we're going to use is DBMSs underscore output dot put underscore a line. So right here is a perfect example of an output statement. So we say DBMSs database management system underscore output. I want to send something to the output buffer dot put. Line. So I want to put a line out there and then within the port the CS with a single quote around there that's where you can put any code that you want or any text that you want to put out there. So you might say the employee name is and then you can use a concatenation symbol which is two pipes right next to one another and I'll show you this a little bit. So let's see it in action. So I've already launched sequel plus and I've already logged in. You don't need to see me log in every single time. So now I want to create some output. I want to execute something I want to write a sequel script. Well the first thing we do we know is that we work within blocks so the first block that I want to use is a declare because I want to declare a variable. So watch what's going to happen. I'm going to sit him into the type of declare and as soon as I do this I get a number two and I lose that sequel prompt and what it's literally telling me as it says oh they typed in declare that is a program block for GPL sequel. I'm now in Ed. mode. So now I can type in whatever I want. I can start writing my code so I want to declare a variable something or just put a little. I'm going to put a comment to clear variable and then I'm going to call this X SSN and it is going to be a VAR car too and we going make it 10 characters long Vasa of that. So now I have my Begin. I don't need to terminate the semi-colon Terminator on that one. So now I can say I want to select SSN in two x axis and from employees. Where SSN equals 1 1 1 2 2 3 3 3 3. And then I can say TV and that's output. Put underscore line X SSN terminate it and then I'm just going to put my statement. So I'm done typing in my script. I have my declare I declared a variable I might begin I have my select statements so that's my program logic. I want some output and I'm done. But notice that I have a number 9 there its sequel Plus still considers me to be in edit mode. I need to tell it I'm finished typing the way you do this is by putting in a period. So I put in my period and as soon as I do that I get my sequel prompt again. Now I'm good to go. So now I want to execute my commands so I'm going to use my slash run. And it says the sequel procedure successfully completed but it never showed me my output. I forgot to do my set. Server output on. Now if I execute 1 1 1 2 2 2 3 3 3 3 so it shows me my social security number because that is what I did. I said select the Social Security number into X SSN from employee where the social security number equals whatever I entered in. So it's stupid. It doesn't really do anything but I wanted to show you that this is exactly how it works. Now if I want to see my code again I could just say list hit enter and there's my code. I can also tell it that I want to add in it. If I say edit. Notice what it does it automatically launches notepad. But look at my name the name up at the top says a f.. I EDT dot be you left so it says it's actually in the buffer I'm looking at the code within that buffer and I can change whatever I want. I can say Oh well I want my select statement to use instead of one by one. I want it to be 3 3 3 4 4 5 5 5. I don't know if I have that in there when I close this and say save it automatically gets posted right back into the buffer which is pretty cool so gives me a. Actually a nicer editor if I want to work with this. And now if I hit execute says 3 3 3 4 4 5 5 5 5 so it works. So my code is just a nice way of working between sequel plus and the buffer. But the big thing that I want do to stress to you in this particular part of the lesson is that when we're inside a sequel Plus we can type in our code on the fly. We can do whatever we want to do. But the big thing is is there are two big things one. When I'm finished entering in my code I need to say I want to post this into the buffer and I do that by putting in a period or the dot and then if I want to see any output you have to turn set server output on. If you don't have that Oracle is going to to not waste that energy to output anything to you the human. So you need to do this but single plus. I also wanted to show you how we can edit some code and within Windows it makes it real easy because it opens up Notepad lets you do whatever you want to do and when you say I want to save it you're not literally saving the file you're saving that output right back into the buffer which is pretty cool. So I want to get out of here. I'm just going type exit and it's disconnecting from Oracle and I'm back to my command prompt if I want to exit out again. I could just type exit again or quit and that'll get me out of my command prompt as well. So this entire lesson was to get you acclimated to appeal sequel. Some of the advantages the disadvantages. How you can deploy but the other thing I wanted to show you is the syntax and how you can work with sequel plus. I hope you enjoy that because that's probably about the last time we're going to be in sequel Plus we might do something real quick and dirty but for the most part we're going to spend the majority of our time with an equal developer. So let's get ready to have some fun.