Stored Procedures in MySQL | Advanced MySQL Series

00:12:36
https://www.youtube.com/watch?v=7vnxpcqmqNQ

Summary

TLDRThis lesson introduces stored procedures in SQL, which are reusable blocks of SQL code that can simplify complex query execution and enhance performance. The instructor demonstrates how to create a simple stored procedure, execute it, and introduces the use of parameters for dynamic input. The video emphasizes best practices, such as using delimiters to separate SQL statements, naming conventions for parameters, and resetting the delimiter after writing a stored procedure. Useful tips, like checking for existing procedures before creating new ones, are provided to help manage databases efficiently.

Takeaways

  • 💾 Stored procedures help save and reuse SQL queries for efficiency.
  • 📝 Use CREATE PROCEDURE to define your stored procedures.
  • 🔢 Parameters allow dynamic inputs in stored procedures for flexible execution.
  • 💡 Best practice involves properly naming parameters to avoid confusion.
  • ✅ Use delimiters for separating SQL statements inside procedures.
  • 🔄 ALTER PROCEDURE can modify existing procedures.
  • 📊 Store procedures enhance performance with efficient query management.
  • ⚙️ Delimiters can be customized, such as using dollar signs instead of semicolons.
  • 🗂 Best to check and drop existing procedures before creating new ones.
  • 🚀 Stored procedures are essential for managing complex databases.

Timeline

  • 00:00:00 - 00:05:00

    The lesson introduces stored procedures in SQL, highlighting how they save reusable SQL code for executing complex queries efficiently. Initially, a simple query is created and stored by naming it 'large_salaries.' This stored procedure selects rows with salaries greater than or equal to $50,000. The instructor explains the basics of creating a stored procedure, running it, and verifying its creation. Emphasis is placed on using the correct database context to ensure that stored procedures execute properly.

  • 00:05:00 - 00:12:36

    The discussion moves to best practices, including using delimiters to manage multiple queries within a single stored procedure. The instructor introduces changing delimiters to avoid errors from mixing queries. An example is shown with two queries stored under 'large_salaries2,' demonstrating how delimiters separate them. Further, a parameter is added to the stored procedure to accept input values like employee ID, allowing dynamic data manipulation. Naming conventions for parameters are suggested to reduce confusion. The lesson concludes with plans to explore triggers and events in the next session.

Mind Map

Video Q&A

  • What are stored procedures in SQL?

    Stored procedures are saved SQL code that can be reused to execute complex queries, simplify repetitive code, and enhance performance.

  • How do you create a stored procedure in SQL?

    You can create a stored procedure using the syntax "CREATE PROCEDURE procedure_name AS BEGIN SQL_statements END;" and execute it by calling the procedure.

  • What is a delimiter in SQL stored procedures?

    A delimiter is used to separate different SQL statements within a stored procedure, commonly a semicolon is used, but it can be changed if needed.

  • Why use parameters in stored procedures?

    Parameters allow stored procedures to accept input values dynamically, which can be used within the SQL code to customize execution.

  • How do you call a stored procedure in SQL?

    You call a stored procedure by using the "CALL procedure_name(parameters);" syntax.

  • What is the purpose of using a delimiter in stored procedures?

    Delimiters in stored procedures help to separate SQL statements, avoiding errors by clearly defining where one statement ends and another begins.

  • What is best practice for naming parameters in stored procedures?

    Use clear, descriptive naming conventions like adding "_param" or "p_" to the parameter name to distinguish it from table column names.

  • Can you change a stored procedure after it's created?

    Yes, you can alter a stored procedure by using the "ALTER PROCEDURE" command or by editing it through an SQL editor interface.

  • Are stored procedures efficient for performance?

    Yes, they are efficient as they allow code reuse and execute complex batch queries quickly, often with optimized execution plans.

View more video summaries

Get instant access to free YouTube video summaries powered by AI!
Subtitles
en
Auto Scroll:
  • 00:00:00
    hello everybody in this lesson we're
  • 00:00:01
    gonna be taking a look at stored
  • 00:00:03
    procedures stored procedures are a way
  • 00:00:05
    to save your SQL code that you can reuse
  • 00:00:07
    over and over again when you save it you
  • 00:00:09
    can call that store procedure and it's
  • 00:00:11
    going to execute all the code that you
  • 00:00:13
    wrote within your store procedure it's
  • 00:00:15
    really helpful for storing complex
  • 00:00:17
    queries simplifying repetitive code and
  • 00:00:19
    just enhancing performance overall so
  • 00:00:21
    let's take a look at how we can create a
  • 00:00:23
    stored procedure now we're going to
  • 00:00:25
    start by just creating a really simple
  • 00:00:26
    query we'll make it a little bit more
  • 00:00:28
    advanced as we go along and take a look
  • 00:00:30
    at the different things within store
  • 00:00:32
    procedures that you can do now let's
  • 00:00:33
    change this query let's say where the
  • 00:00:37
    salary is greater than let's do 50 000.
  • 00:00:40
    let's actually do greater than or equal
  • 00:00:42
    to fifty thousand we want to include Tom
  • 00:00:44
    and Jerry as well so let's go ahead and
  • 00:00:47
    run this now what we want to do is save
  • 00:00:50
    this really complex code within a stored
  • 00:00:53
    procedure let's come right down here and
  • 00:00:55
    we can create a super super super simple
  • 00:00:58
    stored procedure by just saying create
  • 00:01:01
    procedure
  • 00:01:03
    and pasting that now we just have to
  • 00:01:05
    name it so we have create procedure and
  • 00:01:08
    we'll call this large underscore
  • 00:01:10
    salaries and then we do a closed
  • 00:01:13
    parentheses now this is as simple as it
  • 00:01:16
    can possibly be it does not get any
  • 00:01:18
    simpler than this so let's go ahead and
  • 00:01:19
    run this
  • 00:01:21
    and if we go down we pull this up you
  • 00:01:24
    can see that it says create procedure
  • 00:01:26
    zero is affected it looks like it worked
  • 00:01:28
    and if we come over here to this refresh
  • 00:01:31
    button you should see now that under
  • 00:01:33
    store procedures it drops down we have
  • 00:01:35
    our large salaries that's exactly what
  • 00:01:38
    should have happened we wanted to save
  • 00:01:39
    that into our Parks and Recreation now
  • 00:01:42
    if you wanted to be careful you could
  • 00:01:43
    say use arcs underscore and underscore
  • 00:01:47
    Recreation this is not a bad idea but
  • 00:01:50
    you don't have to but you can specify
  • 00:01:52
    what database within your actual editor
  • 00:01:55
    window sometimes that is helpful but now
  • 00:01:58
    we've created it now let's see how we
  • 00:02:00
    can call it all we have to do is say
  • 00:02:01
    call
  • 00:02:03
    we're going to copy this entire thing
  • 00:02:04
    including the parentheses
  • 00:02:06
    and let's end it with that's right
  • 00:02:10
    that's not my colon let's go ahead and
  • 00:02:11
    run this
  • 00:02:12
    and as you can see it worked because we
  • 00:02:15
    got the exact output so we actually
  • 00:02:17
    called this stored procedure and this
  • 00:02:20
    code ran so it's just a select statement
  • 00:02:22
    so it worked perfectly now you can also
  • 00:02:24
    come over here to large salaries and
  • 00:02:26
    there's this little tiny little button
  • 00:02:28
    here that looks like a lightning bolt
  • 00:02:29
    and if you click it it's going to open
  • 00:02:31
    up a different window and we'll say call
  • 00:02:34
    Parks and recreation.large salaries so
  • 00:02:36
    you can do it that way as well but we're
  • 00:02:39
    not going to be doing it that way now
  • 00:02:41
    what we've written right here is not
  • 00:02:43
    best practice by any means and I'm going
  • 00:02:46
    to copy this down here because there's a
  • 00:02:48
    lot of different things that you need to
  • 00:02:50
    take into account when you're creating a
  • 00:02:51
    stir procedure for example this right
  • 00:02:54
    here is most likely not what you're
  • 00:02:56
    going to be putting into a stored
  • 00:02:57
    procedure this is super super simple
  • 00:02:59
    typically you'll be having multiple
  • 00:03:02
    queries and let's see what happens if I
  • 00:03:04
    try to put another query in here and
  • 00:03:06
    let's get rid of this
  • 00:03:08
    so we're going to select everything
  • 00:03:09
    where the salary is greater than 50 000
  • 00:03:11
    then we'll select everything where it's
  • 00:03:13
    greater than 10 000 which is everybody
  • 00:03:15
    let's call this large salaries two so we
  • 00:03:18
    have two different statements in here
  • 00:03:20
    and we want them all to be under this
  • 00:03:22
    large salaries too let's select
  • 00:03:24
    everything and let's run this
  • 00:03:26
    and we're getting an output which is
  • 00:03:29
    already not a good sign but we created
  • 00:03:31
    the stored procedure and then we
  • 00:03:33
    selected everything so what's actually
  • 00:03:36
    happening here pull this back down
  • 00:03:39
    what's happening is is this is creating
  • 00:03:42
    the stored procedure and this is just
  • 00:03:43
    some other you know random query but
  • 00:03:46
    that's not what we want what we want is
  • 00:03:48
    everything or both of these queries
  • 00:03:50
    within one store procedure the best
  • 00:03:52
    practice is to use something called a
  • 00:03:54
    delimiter now this right here is a
  • 00:03:57
    delimiter this semicolon so the
  • 00:03:59
    semicolon separates our queries from one
  • 00:04:01
    another it tells my SQL hey you know
  • 00:04:04
    this is a different query don't be
  • 00:04:06
    mixing these and cause errors you know
  • 00:04:08
    that's essentially what a delimiter does
  • 00:04:10
    and we can change the delimiter by
  • 00:04:13
    coming up here and saying delimiter and
  • 00:04:16
    we can change it to almost anything we
  • 00:04:18
    want now in my actual job I've seen it
  • 00:04:20
    done many different ways I've seen these
  • 00:04:22
    forward slashes I've also seen dollar
  • 00:04:24
    signs this is probably the one that I've
  • 00:04:26
    seen the most when I worked with data
  • 00:04:27
    Engineers data scientists database
  • 00:04:29
    developers and this one I see a lot and
  • 00:04:32
    then you'll come into the code and
  • 00:04:34
    you'll say begin
  • 00:04:37
    and let's go over here and let's tab all
  • 00:04:39
    of this and then we'll say end now when
  • 00:04:43
    we end we're going to end it with this
  • 00:04:46
    dollar sign so here's what's happening
  • 00:04:47
    we're changing the delimiter right here
  • 00:04:49
    to dollar sign we're creating our store
  • 00:04:51
    procedure and within it we are keeping
  • 00:04:55
    all of this so all of this code is going
  • 00:04:57
    to go into this one stored procedure
  • 00:05:00
    then at the end we are saying this is
  • 00:05:03
    the end right here of this stored
  • 00:05:06
    procedure these semicolons no longer are
  • 00:05:09
    the delimiter that's telling us when it
  • 00:05:11
    is the end of the stored procedure
  • 00:05:12
    that's what the delimiter does now it is
  • 00:05:14
    best practice at the end to change it
  • 00:05:16
    back right uh let me spell it right
  • 00:05:18
    because if you don't then you're going
  • 00:05:21
    to have to start using these dollar
  • 00:05:23
    signs for everything and how do you
  • 00:05:25
    spell delimiter
  • 00:05:27
    um and there we go now we've changed it
  • 00:05:29
    back to a semicolon afterwards so then
  • 00:05:32
    we can go and write other queries and
  • 00:05:33
    it'll act appropriately
  • 00:05:35
    uh let's go down so this is getting
  • 00:05:39
    closer to best practice let's go ahead
  • 00:05:42
    and run this entire thing
  • 00:05:44
    and if we pull this up we're not getting
  • 00:05:46
    an output that's a good sign if we pull
  • 00:05:48
    this up it's saying we already created
  • 00:05:51
    number two let's change that to three my
  • 00:05:53
    apologies let's go down here
  • 00:05:56
    now we've created the stored procedure
  • 00:05:58
    three now let's go over here we're going
  • 00:06:01
    to right click on this we're going to
  • 00:06:04
    say alter stored procedure and now you
  • 00:06:06
    can see that we have both of these
  • 00:06:09
    queries within the stored procedure
  • 00:06:11
    let's get rid of this and we're going to
  • 00:06:13
    go and call this so let's copy this
  • 00:06:16
    large salary is three
  • 00:06:19
    bring this all the way down
  • 00:06:21
    and let's say call
  • 00:06:25
    add store procedure if we run it you'll
  • 00:06:28
    notice we get two outputs we have six
  • 00:06:31
    and seven this result six is where it's
  • 00:06:34
    greater than fifty thousand or fifty
  • 00:06:35
    thousand uh or greater this one is where
  • 00:06:38
    it's greater than 10 000 which is
  • 00:06:40
    essentially the entire table now so far
  • 00:06:42
    we've done everything just by writing it
  • 00:06:44
    all out and that's fantastic but you can
  • 00:06:47
    also come over here to store procedures
  • 00:06:49
    and right click and say create stored
  • 00:06:52
    procedure now let's actually copy this
  • 00:06:54
    so we're just going to create the exact
  • 00:06:56
    same thing we'll create start procedure
  • 00:06:58
    and we can just paste this in here
  • 00:07:01
    now let's go ahead and do that there we
  • 00:07:03
    go and sure we'll call it new procedure
  • 00:07:06
    why not and if we say apply you'll
  • 00:07:10
    notice that it generates this script
  • 00:07:12
    right here and we can apply it and we
  • 00:07:14
    can create it we will in just a second
  • 00:07:15
    but let's take a look at it so we're
  • 00:07:17
    going to use Parks and Recreation that's
  • 00:07:20
    what I was mentioning before we're then
  • 00:07:22
    going to say drop procedure if exists
  • 00:07:25
    now this is something that I was going
  • 00:07:27
    to show you later but I'll just show it
  • 00:07:28
    to you now sometimes it is really
  • 00:07:30
    beneficial to write something like this
  • 00:07:32
    before you create it in case you've
  • 00:07:34
    already created a stored procedure with
  • 00:07:36
    that name that you are wanting to
  • 00:07:37
    replace so it's checking if it's there
  • 00:07:39
    and if that new procedure is already
  • 00:07:41
    there it's just going to drop it then it
  • 00:07:44
    comes down and let me see if I can zoom
  • 00:07:45
    in on this
  • 00:07:46
    and then it's going to create our
  • 00:07:48
    delimiter which it uses dollar signs so
  • 00:07:50
    MySQL is even you know validating what I
  • 00:07:52
    was saying earlier we're going to use
  • 00:07:53
    Parks and Recreation again and now again
  • 00:07:56
    we have to use instead of a semicolon
  • 00:07:57
    we're using dollar signs then we're
  • 00:08:00
    creating the procedure which is new
  • 00:08:02
    procedure we're saying begin end and
  • 00:08:04
    then it's even changing the delimiter
  • 00:08:06
    back so basically everything I said this
  • 00:08:08
    is kind of doing it for you
  • 00:08:10
    automatically now when I click apply it
  • 00:08:14
    went ahead and executed that SQL
  • 00:08:15
    statement and our new one is ready so we
  • 00:08:18
    can go ahead and alter that stored
  • 00:08:20
    procedure and it looks exactly the same
  • 00:08:23
    as this one out here which was uh large
  • 00:08:27
    salaries number three so it looks
  • 00:08:29
    exactly the same let's go ahead and get
  • 00:08:32
    rid of this get rid of this and let's go
  • 00:08:35
    down below the next thing I want to take
  • 00:08:37
    a look at is something called a
  • 00:08:39
    parameter now before I actually get into
  • 00:08:42
    this I'm going to copy all this down
  • 00:08:43
    here because I don't want to rewrite all
  • 00:08:44
    of it if I'm being honest
  • 00:08:46
    so let's paste this in here now
  • 00:08:48
    parameters are variables that are passed
  • 00:08:51
    as an input into a stored procedure in
  • 00:08:54
    the allow the store procedure to accept
  • 00:08:55
    an input value and place it into your
  • 00:08:59
    code let's take a look at what that
  • 00:09:00
    actually means now before I do anything
  • 00:09:02
    I'm just going to change this to uh
  • 00:09:04
    number four so I don't forget
  • 00:09:06
    so let's get rid of all of this we're
  • 00:09:10
    going to keep it somewhat simple because
  • 00:09:11
    we're looking at something new now when
  • 00:09:13
    I say we're passing through a parameter
  • 00:09:14
    I'm talking about when we're calling it
  • 00:09:17
    so let's say we've already created this
  • 00:09:19
    one I'm not going to you know run this
  • 00:09:20
    yet but let's say we've created it let's
  • 00:09:22
    say I want to pass in an employee ID I
  • 00:09:25
    want to pass in a specific person and I
  • 00:09:27
    want to retrieve their salary I know
  • 00:09:29
    their employee IDs I just want it to
  • 00:09:31
    pull up their salary for us so what
  • 00:09:34
    we're going to do is we'll get rid of
  • 00:09:35
    this and when we're calling it let's put
  • 00:09:38
    this down when we're calling it I'm
  • 00:09:39
    going to pass through a value like one
  • 00:09:41
    that's Leslie nope and then I want the
  • 00:09:43
    salary to be the output so I'm going to
  • 00:09:46
    select the salary so we're selecting
  • 00:09:48
    salary from the employee's salary but
  • 00:09:51
    how do we know that this one is the
  • 00:09:54
    person we're looking for well when we're
  • 00:09:56
    actually creating this parameter we
  • 00:09:57
    create it right in here that's what
  • 00:10:00
    tells the store procedure to accept an
  • 00:10:02
    input value when we're calling it down
  • 00:10:05
    below we're going to call this and
  • 00:10:06
    employee underscore ID now after we call
  • 00:10:10
    it after we name our parameter we need
  • 00:10:13
    to then give it a data type so I'll call
  • 00:10:15
    this an integer so we're telling the
  • 00:10:17
    stored procedure when somebody calls
  • 00:10:19
    this store procedure they have to pass
  • 00:10:21
    through an integer it can't be a string
  • 00:10:23
    or it can't be a date it has to be an
  • 00:10:26
    integer now what we're going to go do is
  • 00:10:28
    right down here we'll say where the
  • 00:10:31
    employee underscore ID that's from this
  • 00:10:34
    column in the actual table we'll say is
  • 00:10:36
    equal to the employee underscore ID
  • 00:10:40
    which is our parameter right here now
  • 00:10:42
    you may be thinking that's really
  • 00:10:44
    confusing they're named the exact same
  • 00:10:46
    thing can I change it the answer is yes
  • 00:10:48
    I actually encourage it so there are
  • 00:10:50
    some naming conventions that are out
  • 00:10:51
    there that I think are helpful ones that
  • 00:10:53
    I personally use
  • 00:10:54
    um but remember this is just kind of a
  • 00:10:56
    variable parameter name you can kind of
  • 00:10:58
    call it whatever you want so if I wanted
  • 00:11:00
    to say Huggy muffin I could uh and this
  • 00:11:04
    could be Huggy muffin so let's try it
  • 00:11:06
    with muffin I just came up with that off
  • 00:11:08
    the top of my head so don't judge me
  • 00:11:10
    um but we're going to create the store
  • 00:11:12
    procedure and then when we call it later
  • 00:11:14
    we wanted to return the salary where the
  • 00:11:17
    employee ID right here is equal to
  • 00:11:20
    whatever was passed through that
  • 00:11:22
    parameter that input parameter we're
  • 00:11:25
    going to keep it as one so it should
  • 00:11:26
    return 75 000. let's go ahead we're
  • 00:11:28
    going to create this
  • 00:11:30
    and now let's go right down here and
  • 00:11:32
    we're going to run it and we can see
  • 00:11:34
    that that is the salary and it worked
  • 00:11:36
    perfectly now like I was saying that is
  • 00:11:39
    not what I would actually name it uh
  • 00:11:40
    there are some naming conventions like
  • 00:11:42
    underscore param at the end so you kind
  • 00:11:45
    of want to keep it at least I recommend
  • 00:11:48
    you try to keep it similar to what
  • 00:11:50
    you're actually looking for and you can
  • 00:11:52
    either end it in underscore param or
  • 00:11:54
    there's another way you can do it which
  • 00:11:55
    is come right over here and do p
  • 00:11:57
    underscore and these are just ways that
  • 00:12:00
    you can tell the code or you can just be
  • 00:12:02
    able to visually see the difference in
  • 00:12:03
    the code so this is just what I
  • 00:12:05
    recommend then you put it right down
  • 00:12:07
    here you say where the employee ID is
  • 00:12:10
    equal to P underscore employee ID saying
  • 00:12:12
    this is the parameter that's being
  • 00:12:14
    passed through and put into our actual
  • 00:12:16
    query so that is all we're going to take
  • 00:12:18
    a look at in this lesson in the next
  • 00:12:20
    lesson we're going to be taking a look
  • 00:12:22
    at triggers and events
  • 00:12:25
    foreign
  • 00:12:27
    [Music]
Tags
  • SQL
  • Stored Procedures
  • Database
  • SQL Tutorial
  • Parameters
  • Best Practices
  • Delimiters
  • Code Reuse
  • Query Optimization