Sql:mysql Create Function,view,procedure

Use Geovindu, #函数DELIMITER $ $drop function if exists f_getdepartmentname $ $CREATE function f_getdepartmentname (did int) Returns nvarchar (+) begindeclare str nvarchar (+); Select Departmentname into str from departmentlist where Departmenti D=did;return ifnull (str, "); end; $ $DELIMITER;D Elimiter $ $DROP function IF EXISTS ' geovindu '. ' f_getauthorname ' $ $CREATE function ' Geovindu '. ' F_ Getauthorname ' (did int) RETURNS varchar (+) BEGIN declare str varchar (+); return (select Ifnull (AuthorName, ') from Aut Horlist where Authorid=did); END $ $DELIMITER; #测试SELECT f_getdepartmentname (1); SELECT f_getauthorname (1); #视图/* 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, Bookinfoauthor, BookInfoPress,  Bookinfolanguage, Bookinfostatus, Bookinfooperatorid,BOOKINFOISBN, Bookinfobarcode, Bookinfoname, Bookinforemarks, Bookinfoadddate,bookinfopublish, BookInfoPrice,BookKi Ndlist.bookkindname,bookplacelist.bookplacename,f_getauthorname (Bookinfoauthor) from BookInfoList,BookKindList, Bookplacelistwhere Bookinfolist.bookinfokind=bookkindlist.bookkindid and Bookinfolist.bookinfoplace= bookplacelist.bookplaceid;$ $DELIMITER; #删除DELIMITER $ $DROP PROCEDURE IF EXISTS ' Geovindu '. ' Deletebookkind ' $ $CREATE  PROCEDURE ' Geovindu '. ' Deletebookkind ' (in param1 INT) BEGIN Delete from bookkindlist WHERE bookkindid = param1;end $ $DELIMITER; #查询所有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; #统计DELIMITER $ $DROP PROCEDURE IF EXISTS ' Geovindu '. ' Bookkindcount ' $ $CREATE PROCEDURE ' Geovind U '. ' Bookkindcount ' (out param1id INT) BEGIN Select COUNT (*) into param1id from Bookkindlist; END $ $DELIMITER; #更新 DELIMITER $ $DROP PROCEDURE IF EXISTS ' Geovindu '. ' proc_update_bookkindlist ' $ $CREATE PROCEDURE ' Geovi Ndu '. ' Proc_update_bookkindlist ' (in Param1id int., in Param1name NVarChar (+), param1parent int) beginif not EXISTS (SELECT * from Bookkindlist WHERE bookkindname=param1name) and then #如果存在相同的记录, name update not updated Bookkindlistsetbookkindname=param1name, Bookkindparent=param1parentwherebookkindid=param1id; ELSE UPDATE Bookkindlistset bookkindparent=param1parentwherebookkindid=param1id; END IF; END $ $DELIMITER; #查询一条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); #插入一条DELIMITER $ $DROP PROCEDURE IF EXISTS ' Geovindu '. ' Proc_insert_ Bookkindlist ' $ $CREATE PROCEDURE ' Geovindu '. ' Proc_insert_bookkindlist ' (in param1nAme NVarChar (param1parent Int) BEGIN insert INTO bookkindlist (bookkindname,bookkindparent) VALUES (param 1name,param1parent); END $ $DELIMITER; #插入一条返回值DELIMITER $ $DROP PROCEDURE IF EXISTS ' Geovindu '. ' Proc_insert_bookkindout ' $ $CREATE PROCEDURE ' Geovindu '. ' Proc_insert_bookkindout ' (in Param1name NVarChar ($), in Param1parent int,out ID int.) BEGIN IF not EXISTS ( SELECT * from Bookkindlist where bookkindname=param1name) then #如果存在相同的记录, do not add INSERT into Bookkindlist (bookkindn        ame,bookkindparent) VALUES (Param1name, param1parent);      #set id=last_insert_id () SELECT last_insert_id () into ID; End If; END $ $DELIMITER;

　　

Sql:mysql Create Function,view,procedure