Learning Oracle PL/SQL Developer

Hello and thank you very much for visiting Flembos Consulting Services Ltd. 's blog.

My name is Harry and today I am posting and tracking my personal progress as I learn the basics of Oracle SQL Developer.

I have no background in programming or database management myself, so getting one's head around the way it all works feels like learning a new language, except this one is not very forgiving for any mistakes that you make. Imagine trying to learn Spanish and each time you mispronounced something incorrectly or used the informal when the formal should have been used and the person talking to you yells: "ERROR". Not so fun. It does mean that you quickly learn from your mistakes and understand the necessity of proper syntax, as well as the importance of a neat and tidy sheet in front of you.

To elaborate on what I have learned so far, I shall give a bit of context. As I am not working on the database of a company, I still had to find data to use and compare. Therefore I used the first thing that came to mind when talking about data: sports. In this modern age the analysis of sports is a marvel, everything any team or player does is marked down and averaged throughout the season. For my table, I used a handy football statistics website to download the data they had to an excel spreadsheet, after that it is the simple process of creating the "insert" command in order to compile all of the relevant columns (which in this case is along the lines of 'full_name', 'current_club', 'position' etc.) and then create a table in the database. After this you handily line up the data from the first entry to the relevant column within excel before repeating the equation for the remaining entries.

Once the data was entered into the table, there were 572 players with information not only about who they were and for whom they played, but the statistics for their appearances, minutes played, goals scored away, clean sheets and so on. Now I'm a patient man and will happily scroll through the database to find the player I want and search for the amount of yellow cards they received that year, but that is only because it takes a few seconds to scroll. In a real world application there can be hundreds upon thousands of entries that poor old me would not be able to scroll through. So what do we do? Well obviously you create a function, duh.

Below I have the code I used to create a function in order to search for the relevant information for a player:

'

create or replace FUNCTION player_search(p_name IN VARCHAR2) RETURN varchar2 IS their_name varchar2 (256); playsfor varchar2 (256); natout varchar2 (256); fielder varchar2 (256); capsfor varchar2 (256); goalsfor varchar2 (256); assistsfor varchar2 (256); cleansheetsfor varchar2 (256); attackrank varchar2 (256); defendrank varchar2 (256); midrank varchar2 (256); begin their_name := ('%'||p_name||'%'); select full_name, current_club, nationality, fielding, appearances_overall, goals_overall, assists_overall, clean_sheets_overall, rank_in_league_top_attackers, rank_in_league_top_defenders, rank_in_league_top_midfielders into their_name, playsfor, natout, fielder, capsfor, goalsfor, assistsfor, cleansheetsfor, attackrank, defendrank, midrank from players where ('%'||full_name||'%') = ('%'||p_name||'%'); if fielder= 'Goalkeeper' then RETURN ( 'Name: ' || their_name || ' ' || 'Team: ' || playsfor || ' ' || 'Nationality: ' || natout || ' ' || 'Position: ' || fielder || ' ' || 'Appearances this season: ' || capsfor || ' ' || 'Clean Sheets: ' || cleansheetsfor); elsif fielder= 'Defender' then return ('Name: ' || their_name || ' ' || 'Team: ' || playsfor || ' ' || 'Nationality: ' || natout || ' ' || 'Position: ' || fielder || ' ' || 'Appearances this season: ' || capsfor || ' ' || 'Goals scored: ' || goalsfor || ' ' ||'Assists: ' || assistsfor || ' ' || 'Clean Sheets: ' || cleansheetsfor || ' ' || 'Defender Rank this season: ' || defendrank); elsif fielder= 'Midfielder' then return ('Name: ' || their_name || ' ' || 'Team: ' || playsfor || ' ' || 'Nationality: ' || natout || ' ' || 'Position: ' || fielder || ' ' || 'Appearances this season: ' || capsfor || ' ' || 'Goals scored: ' || goalsfor || ' ' ||'Assists: ' || assistsfor || ' ' || 'Clean Sheets: ' || cleansheetsfor || ' ' || 'Midfielder Rank this season: ' || midrank); elsif fielder= 'Forward' then return ('Name: ' || their_name || ' ' || 'Team: ' || playsfor || ' ' || 'Nationality: ' || natout || ' ' || 'Position: ' || fielder || ' ' || 'Appearances this season: ' || capsfor || ' ' || 'Goals scored: ' || goalsfor || ' ' ||'Assists: ' || assistsfor || ' ' || 'Attacker Rank this season: ' || attackrank); end if; end;

'

If you can understand this code; huzzah! You are much better at this than I am, although at this point that is not really an achievement.

If you are unable to understand this code; huzzah! That means you can learn something new, which is always good. I shall go through the code step-by-step in order to explain how it works and also so I don't forget.

For clarity, anything in this colour is the code and anything in italics is the explanation, but I'm sure you're smart enough to get that already.

Step 1: Creating the function

create or replace FUNCTION player_search(p_name IN VARCHAR2)

Here we have the simple declaration that a function is being created, it is good for the most part to use the phrase "create or replace" as it means that whilst you are editing your code or sweeping for errors, running the statement won't be a problem, if you were to simply put "create function" you would encounter an error stating that the function was already in use.

After this we have "player_search" this is the name of the function itself, it's always good to get into good naming practices, I could have called this either something very intricate or simple, but either would have been limiting if I wanted to create a similar and more complex function that would require a different and possibly hard-to-remember name. Luckily for me in this circumstance I can just put what it does on the tin. And then finally we have "p_name IN VARCHAR2" this is the declaration of the input variable and the expectation that whatever is entered into the search will be numerous characters (and not numbers). If you are unaware what VARCHAR2 means just give it a quick Google, it's what it's there for.

RETURN varchar2

IS

their_name varchar2 (256);

playsfor varchar2 (256);

natout varchar2 (256);

fielder varchar2 (256);

capsfor varchar2 (256);

goalsfor varchar2 (256);

assistsfor varchar2 (256);

cleansheetsfor varchar2 (256);

attackrank varchar2 (256);

defendrank varchar2 (256;

midrank varchar2 (256);

"RETURN" is very simply the way in which the function will be returned, and it will obviously prefer it to be VARCHAR2 also. Below that we have all the other variables and their corresponding VARCHAR2s. Although many of the results will be numbers instead of characters, VARCHAR2 can handle it. These values may mean very little to you currently but right now they're being established as equivalents within the function for the relevant columns in the table.

STEP 2: Begin

begin

their_name := ('%'||p_name||'%');

Here it is established that "p_name" is the equivalent of "their_name".

select

full_name,

current_club,

nationality, fielding,

appearances_overall,

goals_overall,

assists_overall,

clean_sheets_overall,

rank_in_league_top_attackers,

rank_in_league_top_defenders,

rank_in_league_top_midfielders

into

their_name,

playsfor,

natout,

fielder,

capsfor,

goalsfor,

assistsfor,

cleansheetsfor,

attackrank,

defendrank,

midrank

Here you use "select" to choose which of the columns you wish to gather and then which corresponding established variable you wish to put it "into". As you can see each name is in the same order as the corresponding column, and the names are similar enough for one to find the relevant variable. Although as previously mentioned this is not always the case, so keeping a log of what you've already used and why is helpful. Once the function has been created it is the data from these columns that will be collected. This is an example of syntax that be hard to get your head around, I originally thought it made more sense to simply put "full_name into their_name, current_club into playsfor," but it does not work as such, the nightmare of the "ERROR" screaming Spaniard haunts me still. Don't forget this.

from

players

Very simply, the table from which we are taking the columns, seems easy enough to remember but can be one of those things you forget. The table in this circumstance is called "players".

where

('%'||full_name||'%') = ('%'||p_name||'%');

Another circumstance where it is clarifying the link between the function's input variable: "p_name" and the table "players"'s equivalent "full_name".

STEP 3: Nice and Tidy.

if

fielder= 'Goalkeeper' then

RETURN ( 'Name: ' || their_name || ' ' || 'Team: ' || playsfor || ' ' || 'Nationality: ' || natout || ' ' || 'Position: ' || fielder || ' ' || 'Appearances this season: ' || capsfor || ' ' || 'Clean Sheets: ' || cleansheetsfor);

Now doesn't this look like a confusing piece of whatever this is? Yes, although please forgive me, I am very new at this. Basically here I've set up an "if" statement in order to give relevant data to the search, if one is searching for a "Goalkeeper" they won't care about how many assists they have, as they probably have none unless they like to launch the ball. For this I specified that if the searched for player's "fielder" (position) is as a "Goalkeeper" then the following information within the brackets should be returned. However if they are a "Forward" then the information will be different, including the lack of a statistic for how many clean sheets they got that season, as that is not the "Forward"'s primary role, but it does include their "attackrank".

elsif fielder= 'Defender'

then return ('Name: ' || their_name || ' ' || 'Team: ' || playsfor || ' ' || 'Nationality: ' || natout || ' ' || 'Position: ' || fielder || ' ' || 'Appearances this season: ' || capsfor || ' ' || 'Goals scored: ' || goalsfor || ' ' ||'Assists: ' || assistsfor || ' ' || 'Clean Sheets: ' || cleansheetsfor || ' ' || 'Defender Rank this season: ' || defendrank);

elsif fielder= 'Midfielder'

then return ('Name: ' || their_name || ' ' || 'Team: ' || playsfor || ' ' || 'Nationality: ' || natout || ' ' || 'Position: ' || fielder || ' ' || 'Appearances this season: ' || capsfor || ' ' || 'Goals scored: ' || goalsfor || ' ' ||'Assists: ' || assistsfor || ' ' || 'Clean Sheets: ' || cleansheetsfor || ' ' || 'Midfielder Rank this season: ' || midrank);

elsif fielder= 'Forward'

then return ('Name: ' || their_name || ' ' || 'Team: ' || playsfor || ' ' || 'Nationality: ' || natout || ' ' || 'Position: ' || fielder || ' ' || 'Appearances this season: ' || capsfor || ' ' || 'Goals scored: ' || goalsfor || ' ' ||'Assists: ' || assistsfor || ' ' || 'Attacker Rank this season: ' || attackrank);

"What on earth is going on within those brackets?!" I hear no one ask as I silently type this. Anything in the the brackets that is within two apostrophes are marked by SQL as a quotation, this means that as we have a "return" command, when it is prompted the message that is delivered is complimented with an explanation of the data being viewed, instead of a number that some might struggle to interpret. The two pipes "||" that are shown in between each quote and the aforementioned variables are known as 'concatenations' meaning that they signify and 'and' as the word 'and' cannot be used as it already means something else, probably 'and'.

end if;

It's important to end the if statements so Developer doesn't throw a tantrum.

end;

Finally add the cherry on top with a neat "end" and semicolon. This tells the programme that you've said all you wanted to say and your function is ready...unless there's an "ERROR".

---

So once that was done and the function was successfully compiled it was then on to test the search.

To do this I used the following code:

select player_search ('Olivier Giroud') from dual

The "select" specifies that I wish to use the function called "player_search" in order to find a specific player, as part of the syntax it is important to use brackets and apostrophes. Then you put that you wish to take this information "from dual"; this means that you wish to find this information from the system itself. A good practice for when there are multiple tables with shared data across the system. As you can see here I chose to search for the player 'Olivier Giroud' and the report that the search made as a result is as follows:

'Name: Olivier Giroud

Team: Chelsea

Nationality: France

Position: Forward

Appearances this season: 27

Goals scored: 2

Assists: 4

Attacker Rank this season: 87'

Another one for 'Morgan Gibbs-White':

'Name: Morgan Gibbs-White

Team: Wolverhampton Wanderers

Nationality: England

Position: Midfielder

Appearances this season: 26

Goals scored: 0

Assists: 1

Clean Sheets: 5

Midfielder Rank this season: 107'

And finally 'Virgil van Dijk':

'Name: Virgil van Dijk

Team: Liverpool

Nationality: Netherlands

Position: Defender

Appearances this season: 38

Goals scored: 4

Assists: 2

Clean Sheets: 21

Defender Rank this season: 4'

From this you can see that I am already on my way to being a master programmer and professional hacker. Although the latter probably isn't as marketable at this stage.

The important thing from here on out is recognising what I did in order to create the function, and the mistakes that I made along the way. At this point I understand (as I hope I've shown) how the script itself works. However there are many more ways to make it neater, as well as ways in which the data can be stored and managed. There are procedures and packages to be made, statistics to and player's form to be put into 3rd normal form and much more fun to be had whilst I lose my mind trying to figure out what on earth is wrong with what I just tried to input. But that's all part of the process.

Thanks very much for reading, if you have any questions, feel free to get in contact.