SQL: MySql create FUNCTION, VIEW, PROCEDURE, mysqlprocedure

SQL: MySql create FUNCTION, VIEW, PROCEDURE, mysqlprocedure

Use geovindu; # function DELIMITER $ drop function if exists f_GetDepartmentName $ CREATE function f_GetDepartmentName (did int) returns nvarchar (400) begindeclare str nvarchar (100 ); select DepartmentName into str from DepartmentList where inclumentid = did; return IFNULL (str, ''); end; $ DELIMITER; DELIMITER $ drop function if exists 'geovindu '. 'F _ getauthorname' $ create function 'geovindu '. 'F _ getauthorname' (did int) RETURNS varchar (400) BEGIN declare str varchar (100); return (select ifnull (AuthorName, '') from AuthorList where AuthorID = did ); END $ DELIMITER; # test SELECT f_GetDepartmentName (1); SELECT f_GetAuthorName (1); # view/* view */select * from geovindu. views; desc View_BookInfoList; show create view View_BookInfoList; select * from View_BookInfoList; DELIMITER $ drop view if exists View_BookInfoList $ create view 'geovindu '. 'view _ BookInfoList 'AS select BookInfoID, BookInfoKind, BookInfoPlace, BookInfoSeries, region, BookInfoStatus, region, BookInfoISBN, BookInfoBarCode, BookInfoName, region, BookInfoAddDate, region, BookInfoPrice, bookKindList. bookKindName, BookPlaceList. bookPlaceName, f_GetAuthorName (BookInfoAuthor) from BookInfoList, BookKindList, BookPlaceListwhere BookInfoList. bookInfoKind = BookKindList. bookKindID and BookInfoList. bookInfoPlace = BookPlaceList. bookPlaceID; $ DELIMITER; # Delete DELIMITER $ drop procedure if exists 'geovindu '. 'deletebookkind' $ create procedure 'geovindu '. 'deletebookkind' (IN param1 INT) BEGIN Delete From bookkindlist WHERE BookKindID = param1; END $ DELIMITER; # query all DELIMITER $ drop procedure if exists 'geovindu '. 'proc _ select_bookkindlistall' $ create procedure 'geovindu '. 'proc _ select_bookkindlistall' () begin select * FROM bookkindlist; END $ DELIMITER; select * from 'geovindu '. 'bookkindlist'; SELECT * FROM bookkindlist; # Count DELIMITER $ drop procedure if exists 'geovindu '. 'bookkindcount' $ create procedure 'geovindu '. 'bookkindcount' (OUT param1ID INT) BEGIN select COUNT (*) into param1ID From bookkindlist; END $ DELIMITER; # update DELIMITER $ drop procedure if exists 'geovindu '. 'proc _ Update_BookKindList '$ create procedure 'geovindu '. 'proc _ update_bookkindlist' (IN param1ID Int, IN param1Name NVarChar (1000), IN param1Parent Int) beginif not exists (SELECT * FROM BookKindList WHERE BookKindName = param1Name) then # IF the same record exists, UPDATE BookKindListSETBookKindName = param1Name, BookKindParent = shard = param1ID; else update BookKindListSET BookKindParent = shard = param1ID; end if; END $ DELIMITER; # query a DELIMITER $ drop procedure if exists 'geovindu '. 'proc _ Select_BookKindList '$ create procedure 'geovindu '. 'proc _ Select_BookKindList '(IN param1 INT) begin select * FROM BookKindList WHERE BookKindID = param1; END $ DELIMITER; call proc_Select_BookKindList (1 ); # insert a DELIMITER $ drop procedure if exists 'geovindu '. 'proc _ Insert_BookKindList '$ create procedure 'geovindu '. 'proc _ upload' (IN param1Name NVarChar (1000), IN param1Parent Int) BEGIN insert into BookKindList (BookKindName, BookKindParent) values (param1Name, param1Parent); END $ DELIMITER; # insert a returned value DELIMITER $ drop procedure if exists 'geovindu '. 'proc _ Insert_BookKindOut '$ create procedure 'geovindu '. 'proc _ Insert_BookKindOut '(IN param1Name NVarChar (1000), IN param1Parent Int, out id int) begin if not exists (SELECT * FROM BookKindList WHERE BookKindName = param1Name) then # If the same record exists, insert into BookKindList (BookKindName, BookKindParent) VALUES (param1Name, param1Parent) is not added; # set ID = Last_insert_id () SELECT LAST_INSERT_ID; end if; END $ DELIMITER;