How To: Cursor In MySQL (3 Min) | Learn Cursors Using Stored Function, Loop
In this tutorial, you'll learn how to create and delete stored functions and use cursors with a loop in the MySQL database.
—
Facebook: / gokcedbsql
—
Video Transcript:
—
Connect MySQL In Python: • How To Connect To MySQL In Python (2 ...
—
Hi guys, this is Abhi from Gokcedb. In this video, you're going to learn how to use Cursors in The MySQL database. Let's start by looking at the entity relationship diagram.
I have seven tables in my database right now, but in this tutorial, we'll mostly be working with the property table. Let's run a select query where we select the property name, property county, and sizing acres from the property table and limit the output to 5 records. Here, we got the output in a table format but say our requirement was to get the output in a comma-separate version format.
To accomplish that we're gonna use a cursor. Let's open the SQL file and look at the code. We start by setting the delimiter to dollar dollar so it doesn't interfere with the semicolon which is used for regular SQL statements.
Use the create function function name syntax followed by the returns keyword and the data type of what this function will return. A deterministic keyword implies the character of this function. The meat of this function is defined between the beginning and the end markers.
Here I'm using the declare keyword to declare a bunch of variables. For the property variable, I'm setting a default value of an empty string, and for the done variable. The default value is 0.
Cur is the cursor variable that will be used for the select query. V underscore done, is the handler variable which will be set to 1 when the cursor reached the end of the table. Next, we open the cursor and label the loop properties underscore loop.
Inside the loop, we fetch the cursor into the property underscore name, property underscores county, and property underscore size in acre variables. Next, we have the if-else block. If the done variable is set to 1, then leave the loop else.
Set the properties variable to the concatenated value of property name, county, size, and acres. I'm using the end keyword to end the if block and the loop block. Ude close cur to close the cursor followed by the return keyword where I'm returning the concatenated value of v underscore properties variable.
At the end of the file, we reset the delimiter back to a semicolon. Let's take a quick look at the MySQL Alias. I'm using the config file to pass in the authentication details.
To execute the create function SQL file, input it to the MySQL Alias. Use the show function status keyword to check the status of your function. And if you want to look at the definition, use the show create function function name syntax.
To call the get to underscore properties function, use the select statement. Here, you can see the output was formatted in a comma-separated version format. Finally, if you want to drop this function, use the drop function function name syntax.
There you have it. Make sure you like, subscribe, and turn on the notification bell. Until next time.
Смотрите видео How To: Cursor In MySQL (3 Min) | Learn Cursors Using Stored Function, Loop онлайн, длительностью часов минут секунд в хорошем качестве, которое загружено на канал Gokce DB 16 Февраль 2022. Делитесь ссылкой на видео в социальных сетях, чтобы ваши подписчики и друзья так же посмотрели это видео. Данный видеоклип посмотрели 1,273 раз и оно понравилось 12 посетителям.
