How to check whether have the same name in the system in MySQL cursor-CodePudding

In MySQL excuse me how to check whether have the same name in the system of the cursor? How to write SQL code, thank you!

CodePudding user response:

The light means

CodePudding user response:

reference 1st floor AHUA1001 response:

light standard refers to what

Is the Cursor
How do I want to check whether I have defined in the system or are using a cursor, or call a cursor,

thank you

CodePudding user response:

refer to the second floor McXhh2005 response:

Quote: refer to 1st floor AHUA1001 response:

Light standard refers to what

Is the Cursor
How do I want to check whether I have defined in the system or are using a cursor, or call a cursor,

Thank you

You have said is a cursor, defined in the storage process,

CodePudding user response:

You can try this statement
SELECT * FROM mysql. Proc t WHERE t.d b=SCHEMA () AND UPPER (t.b ody) LIKE '% % CURSOR;

CodePudding user response:

reference 4 floor AHUA1001 response:

you can try the statementsSELECT * FROM mysql. Proc t WHERE t.d b=SCHEMA () AND UPPER (t.b ody) LIKE '% % CURSOR;

You this is the query whether there is a definition of the Cursor in the process of storage, I mean in the same stored procedure, first check whether there is a certain Cursor is defined or use, if use a Cursor is in use, turn off it,

I defined in a cycle of the cursor, so in order to realize the following code in Chinese first described the line function, can you tell me how to write? Thank you very much!

SELECT * FROM mysql. Proc t WHERE t.d b=SCHEMA () AND UPPER (t.b ody) LIKE '% cur1 %';
DECLARE cur1 CURSOR FOR the SELECT * FROM the test.
The open cur1;

Checks if cur1 exists, if there's a first shut down

CodePudding user response:

To summarize, your demand is divided into three parts,
1, determine which stored procedure using the CURSOR,
2, determine which consists of the storage process of CURSOR, executing,
3, shut down the running that contains the CURSOR of the stored procedure,
Is this meaning?

CodePudding user response:

refer to 6th floor AHUA1001 response:

to summarize, your demand is divided into three parts,
1, determine which stored procedure using the CURSOR,
2, determine which consists of the storage process of CURSOR, executing,
3, shut down the running that contains the CURSOR of the stored procedure,
Is this meaning,

refer to 6th floor AHUA1001 response:

to summarize, your demand is divided into three parts,
1, determine which stored procedure using the CURSOR,
2, determine which consists of the storage process of CURSOR, executing,
3, shut down the running that contains the CURSOR of the stored procedure,
Is this meaning,

Thank you very much. Not complicated above, because I am in a loop calling is similar to the following code, I just need to check in the process of the current storage cur1 exists, if there's a closed,
Need to implement the following product description the corresponding code can, thank you!

SELECT * FROM mysql. Proc t WHERE t.d b=SCHEMA () AND UPPER (t.b ody) LIKE '% cur1 %';
DECLARE cur1 CURSOR FOR the SELECT * FROM the test.
The open cur1;

If cur1 exist then
Shut down
End the if

CodePudding user response:

In my current knowledge reserves, can SHOW the PROCESSLIST; "" Statement query is executing process, the query out the id of the statements by "kill id." To the end of the process,
But "SHOW the PROCESSLIST;" Query results, not sure is the progress of the cursor, you can use the cursor in the execution of statements, to determine whether you need to process, but need to be able to determine the uniqueness, otherwise it will manslaughter other processes,
In addition, also ask, why do you want to end the executing cursor, such operation, can lead to shot the wrong process, lead to incomplete data line, do not recommend such operations,
Best can detailed say your demand, there may be other solutions to deal with your problems,

CodePudding user response:

Is that there are two Cursor, one is the outer layer of the Cursor, a Cursor is inside, because the inner Cursor is defined within the outer Cursor, for application security, so in outer Cursor each take a line and then perform the inner Cursor, I'll check the inner Cursor for existence or open, if any, was first released in closed, and then execute the inner some related operations, such as the definition of a Cursor is to want to realize this function, thank you!

CodePudding user response:

I understand, it is a nested loop, right,
If so, you don't have to consider whether the inner loop is over, because there is no end of the inner loop, the subsequent won't perform,

CodePudding user response:

Because I sometimes because of a certain conditions need to jump out of the inner loop, a nested loop, so I want to check whether there is the cursor in the code, otherwise once again open the existing cursor will go wrong

CodePudding user response:

This way, you are in a CURSOR, is actually a cycle each cycle should have a condition, such as
The OPEN cursor_name;
The FETCH cursor_name INTO i_1, i_2... ;
WHILE the s & lt;> 1 AND aaa=1 DO
.
The FETCH cursor_name INTO i_1, i_2... ;
- if you want to jump out of the loop, here to set a is not equal to the value of the aaa, the aaa=2, for example,
END the WHILE;
The CLOSE cursor_name;
As long as give in the circulation condition, can jump out to set up a cycle of value,

CodePudding user response:

Jump out of the loop, I know, now the question is how to determine whether a cursor in circulation has been defined and used,

CodePudding user response:

Which prawn can help have a look, thank you!

CodePudding user response:

Which prawn can help have a look, thank you!

CodePudding user response:

Jump out of the closed loop cursor inside the line ah, shut down also determine it why,,,

CodePudding user response:

If you inner loop jump out, secondary to still need to continue the loop just inner loop, also is very simple, set a variable as a sign,
If it is need to continue then the inner loop, flag variable to true, flag variable to true, no longer in the open loop cursor to go,

CodePudding user response:

Don't know you know, when you some reason to jump out there may be two cases, 1, jumped out, you close the inner loop at the same time the cursor went, next time again open, not an error, 2, if you still want to keep the current position of inner loop, the next into the inner loop is to continue the position loop, this kind of situation you will need to define a state variables, such as setting up state variable to true is to keep inner loop position (when you jump out of some reason), then the next time when entering the inner loop to determine if the state variable is true, is no longer open cursor, because the cursor has been open, after the normal circulation within the set state variable to false,