Understanding PL/SQL

Intellezy Trainers
A free video tutorial from Intellezy Trainers
Computer Training Specialists
4.4 instructor rating • 225 courses • 158,602 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] In this lesson we're going to start talking about the language features that are going to find within Piel sequel The first they were going to start off with is understanding P.L. sequel. What is it what does this language do what is it for we're then going to talk about the advantages of real sequel. And there are many different advantages and you probably know some of them but some of you may not. Well they talk about the performance advantages. We're going to talk about the sequel programmed deployment. How do you deploy these different applications and you're going to find that program deployment is actually pretty simple. We're then going to understand the structure of a sequel program block and if you understand that you're going to be able to work within those different blocks that we were going to talk about. We're going to learn a little bit about the language syntax. Basically in this part we're going to focus primarily on how are you going to comment these different blocks and how can you start to use them to understand a little bit of what we have to do as a program as as we start programming with the sequel. And then finally I'm going to show you how to use P.L. sequel within sequel Plus you're going to find out you're not going to spend a lot of time in sequel plus. But there are some idiosyncrasies that you need to be aware of and we're going to discuss that. We're also going to learn that we're not going to use Google Plus that much I'm going to show you this in this lesson. But after that we're probably going to use sequel developer because it gives us more control and it also gives us everything the sequel Plus will give us but it gives us a whole lot more. So let's dive in. The first thing we need to understand is what is Piel sequel. Well first before we can understand Piel sequel we need to understand sequel and something that a lot of people don't really think about when we start talking about sequel or the structure of language. Is that sequel is declarative. Now what does that mean. It means that when we write some sequel code we are literally telling the database what we want but not how it's supposed to go get that data. So and that's really a good thing. It's something that makes sequel very easy to work with and that is one of its pros. One of the advantages is that it is simple it's a simple language to work with and once you understand how to use sequel The world is your oyster when it comes to the database. The biggest downfall to sequel is that it doesn't tell it how we want to get that data. And sometimes we as developers want to tell the database how it's supposed to retrieve that data we might want to say hey I want you to retrieve this data and then I want you to go through record by record and make some modification or I want you to take that data. But only a subset of it. I want you to throw it into a temporary holding area that I'm going to use later wrong and that is where we start to run into problems where we're starting to work with sequel. Sequel is declarative. It allows us to say hey I want to retrieve it or I want to insert something into my database but it doesn't necessarily give us the programming flexibility that we want. While that is where the P.L. comes from. So P.L. sequel is the merging of those two worlds. It gives us the declarative nature of a sequel but it also gives us a procedural language. So the logic features of a procedural language are included within the sequel and that's also where the ill comes from. So it gives us the best of both worlds. It gives us sequel but it also gives the programming language that we can use at the same time. And that is where we really start to see some of the power of working with Oracle. So the advantages of sequel. Well the first thing is its portability P.L. sequel does not care about the operating system that you are working in. You can work in Linux you can work in unix you can work in Windows. It does not matter because sequel's says I don't care about any of that. All I care about is Oracle and Oracle is the is the environment where Piel sequel actually gets executed. Now what's cool about Piel sequel too is that it's just a text language you can use any text editor to write any sequel. But Oracle is going to take that language and it's going to compile and it's going to physically execute that code. So again it is independent of our operating system and all it really cares about is that we have Oracle as long as we have that we're good to go. The other thing that's nice about Siebel is it's simplicity. You're not going to have a lot of complex syntax that you need to learn. Once you understand some of the simplistic syntax APL sequel you can start working with it anytime you want. So that is what this course is going to focus on is getting you acclimated to that equal environment and how to start writing that code. So the performance advantages of APL sequel well for you to understand the performance advantages you need to understand a little bit about how it executes. So here's the first scenario and this is where we're executing some secret code but we're not using P.L. sequel. So you have your application server and you're executing three different scripts or a group of scripts and it gets sent to the database server and then that database server says Well here are your results. Now what you're going to find is that when we're talking about speed we're talking about seconds or maybe even milliseconds. But the speed is not just negligible you are going to see a dramatic speed increase when you start using Piel sequel. But my point is is that as long as you have the application server and you write some code the database server is going to do what you want it to do but peel sequel is a little bit more advanced than that. Here's an example where we are using Piel sequel. So you have your application server and you tell it that I want I want Oracle to execute this script. Well you don't necessarily need to send the script contents to Oracle. It could already be located on the Oracle database server. But the point is is that you say I've got three scripts to run Piel sequel Oracle is going to pick that Piel sequel script is going to compile and it's just good to execute is going to give you your end result. So we might have three different scripts that we're writing are three different sets of code that we want to run. And it might end up giving us only one data set that's the result of all of that work that it's that it has performed. But another thing that's very cool about Piel sequel is let's say that we have five applications servers up here so we have five different users banging on that Oracle database on that database server. And let's say that I have written some sequel code and I've executed it and now and a friend of mine wants to execute that code as well. Well what's really cool is that once that code gets executed it gets put into the cache of the database server and then the next time it runs it's going to run even faster. So when my friend executes that same code it doesn't really need to do anything it's all right that code has already been precompiled and it already knows how it's going to be optimized to run and it'll just execute. So you're going to find that that code is going to run very very quick. It's a very cool language and it's also very very powerful. So pale sequent program deployment. What you're going to find is that when we start working with Piel sequel there are several different ways that we can deploy this code and here they are. So you have your scripts you can have it embedded within a program or stored program or an Oracle object. So let's talk about each one of those. The script is nothing more than just a sequel script that you might execute using sequel plus. Now like I said you might use sequel Plus you might not. I still do every once in a while especially if it's a real simple command and I just want to execute it and find out what my end result is. But most of the time you're going to probably go to a sequel developer or you might use some other script editor to execute that script. It doesn't matter. Like I said it's nothing more than text. The other option you have is with it embedded in a program. So let's say that you are a C developer or maybe COBOL or C-Sharp or whatever the case may be and you can put that script inside of your code you can hardcoded within your application and then it gets sent to the Oracle database and it will execute that code right there on the spot. So every time your application says I want to execute something it it makes a connection to that database. It sends the script to the database server and the database server does whatever you want it to do. So that's another way. I really don't like to use this because if you embedded into your application if that database changes in any way you've got to change your code along with it. So it's kind of a hassle but I know a lot of people who still do this you might have a stored program such as a trigger or some type of a constraint that you might have placed on a table or a series of tables. So this is nothing more than a program that has already been stored within that database table itself. And so like a trigger if some condition is met within the database it will execute that code and do something else for you. It's very very fast and you never have to worry about it its program once you're done. And then finally you have an oracle object and this is nothing more than a program unit within an oracle object database. So these are the different ways that we can deploy our P.L. sequel scripts. We can simply write a script in sequel plus or incircle developer inside your application. It can be a stored program unit. It can be a trigger it can be something of that nature. So let's go take a look at this. So what I want to do is I'm just going to hit window are and I'm going to go into my command window and let's go on startup sequel plus. So I'm going to say sequel Plus like a type today plus and system I'm good at my username and it says I enter your passwords I'm going to put in my password and put in my passwords password. There we go. And now I want to execute some code. Well I have a code a little simple code file and if you want to see this is called Code sequel and you can download it at this time. So let's go and execute this. So I mean to say a r at C colon backslash that's in my test folder and it's called Code sequel. Now the moment I executed this it prompts me for a Social Security number and let me go ahead and increase my font size for you so you can see a little bit better. So my properties go in and I thought I would make this a 20 point font. Make it a little bit bigger. Right. So it says Please enter a value for your SSN. So I'm going to just put in 1 1 1 2 2 3 3 3 1 to many threes. And when I execute it says all right the old value says where SSN equals ampersand enter SSN which was my prompt. Excuse me. And then it says I'm going to put this in and it says My sequel my Piel simple procedure successfully completed. Well it did. It did execute. And let's go and take a look at this code real quick. I'm just going to type list and here's my code so I have my Declare statement. I am I've declared a variable for myself. It says again and I do my select into and I use my DBMSs output output line. Salary is something and then I even have my exception block. So in the event of an exception it will give me some type of result. So this is a sequel script. Notice that it's just nothing but a bunch of text. OK. Now I don't want you to worry about the syntax right now. Don't sweat this stuff right now because I'm just showing this to you. But like I said my code executed it did execute but it didn't show us anything because notice it said DBMSs output put lines salary is well they never said salary is anything. And the reason for this is because I need to execute a command on the server that says for this session I want my server output to come to me. So what I need to do is type set server output on now that I've done that I've just told Oracle that during this session right here anytime I execute something I want that output to come back out to me. Now remember how I was talking about we're going to use siecle plus and siecle Plus as has a few idiosyncrasies. Well here is one of them. When I want to execute my code now my code has already been accepted and it has already been pre-compiled by Oracle for me. The first time it ran it went ahead and did all that for me. And now all of that code is in the cache so it knows about it and it's ready to execute anytime I want. Well if I want to execute this code all I need to do is use the slash. So I put a slash right here and that's literally telling it to run. And so when I hit Enter Notice it says Enter value for SSN. I'm going to put in the same SSN 1 1 1 2 2 3 3 3 3 and we women most point out of the way. And now when I hit enter it says my old value my new value. And then right here it says the salary is thirty two thousand. So my server output tells the system to go ahead and show me that DBMSs output line and it gives me that code. So now let's say I want to run this again. So I just put my slash in and let me put it in just a bunch of nines. 1 2 3 1 2 1 2 3 4 and now instead of giving me a response or giving me a salary since that Social Security number does not exist. Notice what it tells me it says no employee found. Well let me take a look at this code real quick something to say. List the GM. And if we look at this code rockwork quick it again don't worry about the syntax. I'm just explaining this. Real quick we're going to go through this in so much detail you're going to be sick of seeing this code believe me but we have our declare statement. You have your salary. So that's a variable that I've declared called X salary and I've told it that I want it to be the same as the salary field within my employee table. We're going to talk about that don't worry about it. I have my Begin statement. I do my select into. I use my DBMSs output put line so if it finds that that record within the table then it says here I'm going to show you what the salary is but we the second time I ran this where I used all nines. Well that caused an exception. Notice it says exception. So when no records are found it says when no data are found then DBM a DBMS output put line no employee found. If I had run into any other error it would have said aring countered but cause unknown. So this is sequel plus. Well I'm sorry. No it is sequel plus but it's P.L. sequel with sequel plus. And so we I had to use that set so we're output on so that I can actually see any of this output. Otherwise it just executes and there may be some situations where you execute some code and you don't care about any output. Well if that's the case then you don't need to turn set server output on if I want to turn it off. All you do is server output. And now if I execute again and I say 1 1 1 2 2 3 3 3 3 when I execute it says it ran successfully but it's not telling me anything that the user wanted to see. So this is something that you need to know now when it comes time for us to actually start writing code individual lines of code within sequel Plus there's something else that we're going to need to worry about. But that's later on in this lesson. So the thing that I wanted to stress in this first part is when you're working with Piel sequel what is Piel sequel. It's our procedural language that ties into or works with sequel. And what are the advantages and disadvantages. What are the what are some of the ways that we can deploy our code. This was one way that we could deploy it. I just simply had to code file on my hard drive. I launch sequel plus and I loaded that into memory and execute by using the slash. It's really simple and we're going to get into more far more complex code than what we saw up here but this is all it is. You have your declare you have a begin you have an end. You can have an exception block. These are all ways that we work with P.L. sequel. It's really fun. So Holbein.