Proper formatting of an SQL function using Workbench [closed]
If there's something that is uncleared please let me know don't just downvote it, please
Figuring out an assignment. "Please write a stored function which will return the course id that the course name contain a given parameter pattern string. You must use LIKE for the pattern match. Please refer to Courses table."
If a character such as Z is entered, and it is not in the courses name should display: No record found
If the input is blank: Please input a valid string
If the input is NULL: Please input a valid string
And if the input is J: CPS1231, CPS2231
I'm not sure where to go from here
Courses table
cid | name
--------+------------------
CPS1231 | Java1
CPS2231 | Java2
CPS2232 | Data Structure
Here's what I have so far:
CREATE FUNCTION `Work` ()
RETURNS INTEGER
BEGIN
declare msg varchar(20) default '';
if ((name is null) or (name='')) then
select "Please input a valid string" as message;
else
select group_concat(name) into msg from dreamhome.Courses where name like '%,_name%';
elseif ((msg='') or (msg is null)) then
select group_concat(" is not in the system") as message;
else
select distinct cid from dreamhome.Courses;
end if;
end if;
mysql sql function mysql-workbench
asked Nov 22 '18 at 20:21
Frit PocketsFrit Pockets
96
closed as unclear what you're asking by marc_s, Ken White, Rob, Madhur Bhaiya, Tetsuya Yamamoto Nov 23 '18 at 5:58
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
If there's something that is uncleared please let me know don't just downvote it, please
Figuring out an assignment. "Please write a stored function which will return the course id that the course name contain a given parameter pattern string. You must use LIKE for the pattern match. Please refer to Courses table."
If a character such as Z is entered, and it is not in the courses name should display: No record found
If the input is blank: Please input a valid string
If the input is NULL: Please input a valid string
And if the input is J: CPS1231, CPS2231
I'm not sure where to go from here
Courses table
cid | name
--------+------------------
CPS1231 | Java1
CPS2231 | Java2
CPS2232 | Data Structure
Here's what I have so far:
CREATE FUNCTION `Work` ()
RETURNS INTEGER
BEGIN
declare msg varchar(20) default '';
if ((name is null) or (name='')) then
select "Please input a valid string" as message;
else
select group_concat(name) into msg from dreamhome.Courses where name like '%,_name%';
elseif ((msg='') or (msg is null)) then
select group_concat(" is not in the system") as message;
else
select distinct cid from dreamhome.Courses;
end if;
end if;
mysql sql function mysql-workbench
asked Nov 22 '18 at 20:21
Frit PocketsFrit Pockets
96
closed as unclear what you're asking by marc_s, Ken White, Rob, Madhur Bhaiya, Tetsuya Yamamoto Nov 23 '18 at 5:58
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
1
Which dbms are you using?
– jarlh
Nov 22 '18 at 20:21
Doing it through SQL workbench not sure which one @jarlh
– Frit Pockets
Nov 22 '18 at 22:52
add a comment |
If there's something that is uncleared please let me know don't just downvote it, please
Figuring out an assignment. "Please write a stored function which will return the course id that the course name contain a given parameter pattern string. You must use LIKE for the pattern match. Please refer to Courses table."
If a character such as Z is entered, and it is not in the courses name should display: No record found
If the input is blank: Please input a valid string
If the input is NULL: Please input a valid string
And if the input is J: CPS1231, CPS2231
I'm not sure where to go from here
Courses table
cid | name
--------+------------------
CPS1231 | Java1
CPS2231 | Java2
CPS2232 | Data Structure
Here's what I have so far:
CREATE FUNCTION `Work` ()
RETURNS INTEGER
BEGIN
declare msg varchar(20) default '';
if ((name is null) or (name='')) then
select "Please input a valid string" as message;
else
select group_concat(name) into msg from dreamhome.Courses where name like '%,_name%';
elseif ((msg='') or (msg is null)) then
select group_concat(" is not in the system") as message;
else
select distinct cid from dreamhome.Courses;
end if;
end if;
mysql sql function mysql-workbench
asked Nov 22 '18 at 20:21
Frit PocketsFrit Pockets
96
If there's something that is uncleared please let me know don't just downvote it, please
Figuring out an assignment. "Please write a stored function which will return the course id that the course name contain a given parameter pattern string. You must use LIKE for the pattern match. Please refer to Courses table."
If a character such as Z is entered, and it is not in the courses name should display: No record found
If the input is blank: Please input a valid string
If the input is NULL: Please input a valid string
And if the input is J: CPS1231, CPS2231
I'm not sure where to go from here
Courses table
cid | name
--------+------------------
CPS1231 | Java1
CPS2231 | Java2
CPS2232 | Data Structure
Here's what I have so far:
CREATE FUNCTION `Work` ()
RETURNS INTEGER
BEGIN
declare msg varchar(20) default '';
if ((name is null) or (name='')) then
select "Please input a valid string" as message;
else
select group_concat(name) into msg from dreamhome.Courses where name like '%,_name%';
elseif ((msg='') or (msg is null)) then
select group_concat(" is not in the system") as message;
else
select distinct cid from dreamhome.Courses;
end if;
end if;
mysql sql function mysql-workbench
mysql sql function mysql-workbench
asked Nov 22 '18 at 20:21
Frit PocketsFrit Pockets
96
asked Nov 22 '18 at 20:21
Frit PocketsFrit Pockets
96
edited Nov 23 '18 at 20:19
Frit Pockets
asked Nov 22 '18 at 20:21
Frit PocketsFrit Pockets
96
asked Nov 22 '18 at 20:21
Frit PocketsFrit Pockets
96
asked Nov 22 '18 at 20:21
Frit PocketsFrit Pockets
96
96
closed as unclear what you're asking by marc_s, Ken White, Rob, Madhur Bhaiya, Tetsuya Yamamoto Nov 23 '18 at 5:58
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
closed as unclear what you're asking by marc_s, Ken White, Rob, Madhur Bhaiya, Tetsuya Yamamoto Nov 23 '18 at 5:58
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
1
Which dbms are you using?
– jarlh
Nov 22 '18 at 20:21
Doing it through SQL workbench not sure which one @jarlh
– Frit Pockets
Nov 22 '18 at 22:52
add a comment |
1
Which dbms are you using?
– jarlh
Nov 22 '18 at 20:21
Doing it through SQL workbench not sure which one @jarlh
– Frit Pockets
Nov 22 '18 at 22:52
1
1
Which dbms are you using?
– jarlh
Nov 22 '18 at 20:21
Which dbms are you using?
– jarlh
Nov 22 '18 at 20:21
Doing it through SQL workbench not sure which one @jarlh
– Frit Pockets
Nov 22 '18 at 22:52
Doing it through SQL workbench not sure which one @jarlh
– Frit Pockets
Nov 22 '18 at 22:52
add a comment |
1 Answer
1
active
oldest
votes
CREATE FUNCTION get_id(NAME IN
Courses.NAME%TYPE ,COURSE_ID_O OUT ARRAY )
RETURN COURSE_ID_O
BEGIN
IF NAME LIKE '_J%' OR NAME LIKE '_Z%'
THEN
SELECT COURSE_ID INTO COURSE_ID_O FROM
COURSES WHERE COURSE_NAME=NAME ;
ELIF NAME='' OR NAME=NULL
THEN
DBMS_OUTPUT.PUT_LINE('Please enter a valid string.');
END IF
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No record found If it is blank
or null: Please input a valid string');
END EXCEPTION
END
You might refer the correct syntax but for the above as general case it means the function get_id will take name and will return course_id_o array as there are multiple rows in it as O/p if the input coursename is J then it will print else the default error message
answered Nov 22 '18 at 21:03
Himanshu AhujaHimanshu Ahuja
6661216
I get the basics but there suppose to be 3 statements don't all three of them need an if statement?
– Frit Pockets
Nov 22 '18 at 22:54
Check what i have written why you require 3 seperate ifs one if else loop can do the needful because you want to transfer the control as if (input does nt match this go to elif and finally else in seperate it will forcefully check all the ifs seperately all the time but in if else it will check upto if its valid in the worst case it will go to else if the input is null or none.
– Himanshu Ahuja
Nov 23 '18 at 4:55
Ahh got it, thank you very much so it basically shortens it but does the same thing
– Frit Pockets
Nov 23 '18 at 20:21
Exactly :).....
– Himanshu Ahuja
Nov 23 '18 at 20:26
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
CREATE FUNCTION get_id(NAME IN
Courses.NAME%TYPE ,COURSE_ID_O OUT ARRAY )
RETURN COURSE_ID_O
BEGIN
IF NAME LIKE '_J%' OR NAME LIKE '_Z%'
THEN
SELECT COURSE_ID INTO COURSE_ID_O FROM
COURSES WHERE COURSE_NAME=NAME ;
ELIF NAME='' OR NAME=NULL
THEN
DBMS_OUTPUT.PUT_LINE('Please enter a valid string.');
END IF
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No record found If it is blank
or null: Please input a valid string');
END EXCEPTION
END
You might refer the correct syntax but for the above as general case it means the function get_id will take name and will return course_id_o array as there are multiple rows in it as O/p if the input coursename is J then it will print else the default error message
answered Nov 22 '18 at 21:03
Himanshu AhujaHimanshu Ahuja
6661216
I get the basics but there suppose to be 3 statements don't all three of them need an if statement?
– Frit Pockets
Nov 22 '18 at 22:54
Check what i have written why you require 3 seperate ifs one if else loop can do the needful because you want to transfer the control as if (input does nt match this go to elif and finally else in seperate it will forcefully check all the ifs seperately all the time but in if else it will check upto if its valid in the worst case it will go to else if the input is null or none.
– Himanshu Ahuja
Nov 23 '18 at 4:55
Ahh got it, thank you very much so it basically shortens it but does the same thing
– Frit Pockets
Nov 23 '18 at 20:21
Exactly :).....
– Himanshu Ahuja
Nov 23 '18 at 20:26
add a comment |
CREATE FUNCTION get_id(NAME IN
Courses.NAME%TYPE ,COURSE_ID_O OUT ARRAY )
RETURN COURSE_ID_O
BEGIN
IF NAME LIKE '_J%' OR NAME LIKE '_Z%'
THEN
SELECT COURSE_ID INTO COURSE_ID_O FROM
COURSES WHERE COURSE_NAME=NAME ;
ELIF NAME='' OR NAME=NULL
THEN
DBMS_OUTPUT.PUT_LINE('Please enter a valid string.');
END IF
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No record found If it is blank
or null: Please input a valid string');
END EXCEPTION
END
You might refer the correct syntax but for the above as general case it means the function get_id will take name and will return course_id_o array as there are multiple rows in it as O/p if the input coursename is J then it will print else the default error message
answered Nov 22 '18 at 21:03
Himanshu AhujaHimanshu Ahuja
6661216
I get the basics but there suppose to be 3 statements don't all three of them need an if statement?
– Frit Pockets
Nov 22 '18 at 22:54
Check what i have written why you require 3 seperate ifs one if else loop can do the needful because you want to transfer the control as if (input does nt match this go to elif and finally else in seperate it will forcefully check all the ifs seperately all the time but in if else it will check upto if its valid in the worst case it will go to else if the input is null or none.
– Himanshu Ahuja
Nov 23 '18 at 4:55
Ahh got it, thank you very much so it basically shortens it but does the same thing
– Frit Pockets
Nov 23 '18 at 20:21
Exactly :).....
– Himanshu Ahuja
Nov 23 '18 at 20:26
add a comment |
CREATE FUNCTION get_id(NAME IN
Courses.NAME%TYPE ,COURSE_ID_O OUT ARRAY )
RETURN COURSE_ID_O
BEGIN
IF NAME LIKE '_J%' OR NAME LIKE '_Z%'
THEN
SELECT COURSE_ID INTO COURSE_ID_O FROM
COURSES WHERE COURSE_NAME=NAME ;
ELIF NAME='' OR NAME=NULL
THEN
DBMS_OUTPUT.PUT_LINE('Please enter a valid string.');
END IF
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No record found If it is blank
or null: Please input a valid string');
END EXCEPTION
END
You might refer the correct syntax but for the above as general case it means the function get_id will take name and will return course_id_o array as there are multiple rows in it as O/p if the input coursename is J then it will print else the default error message
answered Nov 22 '18 at 21:03
Himanshu AhujaHimanshu Ahuja
6661216
CREATE FUNCTION get_id(NAME IN
Courses.NAME%TYPE ,COURSE_ID_O OUT ARRAY )
RETURN COURSE_ID_O
BEGIN
IF NAME LIKE '_J%' OR NAME LIKE '_Z%'
THEN
SELECT COURSE_ID INTO COURSE_ID_O FROM
COURSES WHERE COURSE_NAME=NAME ;
ELIF NAME='' OR NAME=NULL
THEN
DBMS_OUTPUT.PUT_LINE('Please enter a valid string.');
END IF
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No record found If it is blank
or null: Please input a valid string');
END EXCEPTION
END
You might refer the correct syntax but for the above as general case it means the function get_id will take name and will return course_id_o array as there are multiple rows in it as O/p if the input coursename is J then it will print else the default error message
answered Nov 22 '18 at 21:03
Himanshu AhujaHimanshu Ahuja
6661216
edited Nov 23 '18 at 5:04
answered Nov 22 '18 at 21:03
Himanshu AhujaHimanshu Ahuja
6661216
answered Nov 22 '18 at 21:03
Himanshu AhujaHimanshu Ahuja
6661216
answered Nov 22 '18 at 21:03
Himanshu AhujaHimanshu Ahuja
6661216
6661216
I get the basics but there suppose to be 3 statements don't all three of them need an if statement?
– Frit Pockets
Nov 22 '18 at 22:54
Check what i have written why you require 3 seperate ifs one if else loop can do the needful because you want to transfer the control as if (input does nt match this go to elif and finally else in seperate it will forcefully check all the ifs seperately all the time but in if else it will check upto if its valid in the worst case it will go to else if the input is null or none.
– Himanshu Ahuja
Nov 23 '18 at 4:55
Ahh got it, thank you very much so it basically shortens it but does the same thing
– Frit Pockets
Nov 23 '18 at 20:21
Exactly :).....
– Himanshu Ahuja
Nov 23 '18 at 20:26
add a comment |
I get the basics but there suppose to be 3 statements don't all three of them need an if statement?
– Frit Pockets
Nov 22 '18 at 22:54
Check what i have written why you require 3 seperate ifs one if else loop can do the needful because you want to transfer the control as if (input does nt match this go to elif and finally else in seperate it will forcefully check all the ifs seperately all the time but in if else it will check upto if its valid in the worst case it will go to else if the input is null or none.
– Himanshu Ahuja
Nov 23 '18 at 4:55
Ahh got it, thank you very much so it basically shortens it but does the same thing
– Frit Pockets
Nov 23 '18 at 20:21
Exactly :).....
– Himanshu Ahuja
Nov 23 '18 at 20:26
I get the basics but there suppose to be 3 statements don't all three of them need an if statement?
– Frit Pockets
Nov 22 '18 at 22:54
I get the basics but there suppose to be 3 statements don't all three of them need an if statement?
– Frit Pockets
Nov 22 '18 at 22:54
Check what i have written why you require 3 seperate ifs one if else loop can do the needful because you want to transfer the control as if (input does nt match this go to elif and finally else in seperate it will forcefully check all the ifs seperately all the time but in if else it will check upto if its valid in the worst case it will go to else if the input is null or none.
– Himanshu Ahuja
Nov 23 '18 at 4:55
Check what i have written why you require 3 seperate ifs one if else loop can do the needful because you want to transfer the control as if (input does nt match this go to elif and finally else in seperate it will forcefully check all the ifs seperately all the time but in if else it will check upto if its valid in the worst case it will go to else if the input is null or none.
– Himanshu Ahuja
Nov 23 '18 at 4:55
Ahh got it, thank you very much so it basically shortens it but does the same thing
– Frit Pockets
Nov 23 '18 at 20:21
Ahh got it, thank you very much so it basically shortens it but does the same thing
– Frit Pockets
Nov 23 '18 at 20:21
Exactly :).....
– Himanshu Ahuja
Nov 23 '18 at 20:26
Exactly :).....
– Himanshu Ahuja
Nov 23 '18 at 20:26
add a comment |
1
Which dbms are you using?
– jarlh
Nov 22 '18 at 20:21
Doing it through SQL workbench not sure which one @jarlh
– Frit Pockets
Nov 22 '18 at 22:52