Hi. In this lesson, we understand what is PL SQL? And how do we use it to deal with data processing. Till now we understood how to deal with a data manipulation, data definition, data retrieval, and even how to control access to data. But all these things were done using SQL, the non procedural language, it's time to move into procedural language called PL SQL. This is the procedural extension to SQL.

Oracle is providing this language so that you can write programs which make use of variables, conditional statements, looping structures, exception handling, and all programming artifacts, we use days to do data processing. We create PL SQL programs in the form of blocks. And the blocks might be anonymous blocks without any name, and they're just executed. But the most popular blogs or sub programs, which are in the form of procedures and functions, and they are stored in the database and they are executed in the database. And when the procedures and functions are stored in the database, they are called as stored procedures and stored functions. And we also have triggers which are used to implement business rules, complex business rules, which cannot be implemented using integrity constraints.

So PL SQL plays a vital role in the context of Oracle and PL SQL is an integrated part of Oracle. When you run a PL SQL block as you can see here, it contains two different types of statements, procedural statements and the normal SQL statements. All these procedural statements are executed using procedural statement executed. This is part of Oracle instance. But this can be part of even some other environments like Oracle Forms and we are not talking about Oracle Forms in this course. So, we focus on procedural statement executed that is part of Oracle instance.

When it comes to SQL statements, they are always executed in your database server and It is always executed by SQL statement executed. So, every block is consisting of some procedural statements, some non procedural statements namely SQL statements. Let us see how we can create a block, this is anonymous block a block without any name and you can just straight away Run this block, whether it is anonymous block or a stored procedure or a function. Every block is having a few important parts like declare a two part, executable part, Exception Handling part. declarative part is where we declare variables and also cursors and exceptions later. And executable part is where you put executable statements and they stay domains could be SQL statements as well as PL SQL statements.

Exception handling is where you handle the exceptions that are raised by executable statements. When statements fail, they raise exceptions. And those exceptions are handled by exception handler, which is an important part. Because when exceptions are not handled, your blog is unsuccessful, it is considered to be unsuccessful. So it's important to have exception handler so that we can handle exceptions. And here is an example for declarative part.

The declarative part is all about declaring variables. And each variable is having a data type. That can be one of the standard data types like number date, where can And so on, but more often we prefer to declare variables with the same type as a column in the table. If the column in the table is first name, and if you want the variable to have the same data type as first name of employees, then we have to use something called person test type attribute to get that type of first name and that type is used to declare this variable. I just have a convention of using v underscore as the prefix for variables, but you can follow any convention you like. So this is where we declare variables and you can even initialize variables by assigning a value and do remember the operator is different it is called an equal to because that is the end assignment operator in PL SQL.

Well, pl SQL just like SQL is not case sensitive. So you don't have to worry about giving it either specifically in uppercase or lowercase. It's up to you. Now, these are the data types we have the scalar data types are same, the only new thing is Boolean. Boolean is not available in SQL. You can't create a column of type Boolean, but you can create a variable of type Boolean.

And then we have some other data types which are more specific to PL SQL like composite types, reference types and lobs are again common to both SQL and PL SQL. Now here are the different options available when it comes to giving comments. So if you want to give any remarks In your program, you can do it in the old style, I mean the C style slash star star slash R, you can just give a comment with the two hyphens, whichever you like. Then select command in SQL is retrieving the data. And we have seen in all our examples in SQL, it was displaying the result on the screen. But Strictly speaking, select is not meant to display the data.

It is only meant to retrieve the data and our tools like SQL plus or SQL Developer, retrieve the data using select and display the data in its own way. You can see the output is displayed slightly differently in SQL plus compared with SQL Developer. It's because select is only about retrieving data, it's not concerned with the display, the display is actually done by the tool. Now, in the context of PL SQL, select cannot display. It is only retrieving data. And whatever data select retrieves, we need to copy that into variables.

So that we can use those variables in further process. And if you use select without into, then whatever data you retrieve, it doesn't go anywhere. So make sure you understand how to use into it's pretty simple. And here is an example. I want to find out the salary and job ID of employ 110. And I want to retrieve those two details and they must be copied into these two variables and that is done using a new class called into.

And if you don't do this, then these two values go nowhere. They can't be displayed, and they can't be stored anywhere. So in the context of PL SQL, select always comes with into. And this is where you give the variables. So salary goes into this job, it goes into it. And it's better you declare those variables with the same type as the column.

So we typically do this salary is of type salary column of employees. Job ID is of the type job ID of employees. So these are some fundamentals you need to remember when you're dealing with PL SQL. So what type of programs can be right here is a small demo program. The program is finding out The name of the employ who is drawing the maximum salary. While it might be a trivial program, but it does demonstrate the steps involved in writing a PL SQL program.

To start with, you need to declare required variables. So these are the variables I'm interested in, then I want to take the maximum salary of all the employees and copy that into this variable. And we'll be using this variable in further process. The next step is finding out the name of the employ, who is drawing that maximum salary. So where salary is equal to V max salary, but there is one limitation, this does not succeed if you have more than one employ, drawing the same maximum salary, but with the assumption that is not so I go ahead with this. So, I pick up the name of the employer and put it into this and this is to be done only when the salary of the employ is equal into maximum salary.

Fine, we got the data copied into this this is important. Now, what do you do with that? Now that name is to be displayed. So this is where we display the message saying employ with the highest salary is signed. So to display anything onto screen in PL SQL, you need to use a DBMS OUTPUT dot put line you will understand more about this particular structure. But this is called a package and this is a processor.

So for now, you can say it's just like any other ready made function to take an action and the action taken by this is printing this message But one very important step is this you have to set server output to on if you want the output from DBMS OUTPUT to come. So let's switch and then run the program and here is the program and I want to first make sure this is turned on without that the output will not come on to the screen and then we are finding out the maximum salary and then finding out the name of the employee with the highest salary. And finally we display this message. Now let us select the whole thing and run and it is going to give you the message like this. It is saying employ with the highest salary is Stephen and he is the one with the highest salary.

And you can see the message saying your PL SQL procedure successful completed. That's what it says when the Pl SQL block is successful. But if you by any chance remote days, if you're not copying that into the variable, let's see what happens. Now go ahead and run. and it is saying n into is expected with select because if the intent is not there, there is no way you can store the selected value anywhere. So in the context of PL SQL, whenever you select, you have to retrieve the data copied that into one or more variables.

Well, if you're wondering what if I retrieve multiple rows, not just one? Well, there is an exclusive lesson about it. It's called cursor handling. We'll discuss about that in another lesson. But for now, this is how you get started. So just declared the required variables.

Retrieve the required details and then print whatever you want to print onto the screen. Of course, you can use update command you can use delete, all DML commands are allowed. So in this lesson, we understood what is a PL SQL block? How do we deal with that? We understood three different parts declaration, executable body, and then exception handling. So, this is about PL SQL.

We just got started long way to go. But you need to understand how you execute a PL SQL block, just put the block in SQL Developer and run it and then you have to have begin and at least if you have variables, you can have a declared section, exception and handling and other things will come later. That's all for now. See you in the next lesson.