SQLServer向MySQL移植笔记(一)自定义函数

2. CREATE?FUNCTION?getfiletype?(@kuozhanming???varchar(100))??
3. RETURNS?varchar(100)?
4. AS??
5. BEGIN?
6. ????declare?@filetype????varchar(100)
7. ????declare?@index?int
9. ????set?@filetype=''
10. ????if?(@kuozhanming?is?null?and?@kuozhanming?=?'')
11. ????????select?@filetype?=?''
12. ????
13. ????????
14. ????else
15. ????????begin
16. ????????????SET?@kuozhanming?=?Reverse(@kuozhanming)
17. ????????????SELECT?@index?=?charindex('.',@kuozhanming)?
18. ????????????if?(@index?<?2)
19. ????????????????select?@filetype?=?''
20. ????????????else
21. ????????????????begin???
22. ????????????????????SET?@kuozhanming?=?left(@kuozhanming,@index?-?1)
23. ????????????????????SET?@kuozhanming?=?Reverse(@kuozhanming)
24. ????????????????
25. ????????????????????SET?@kuozhanming?=?Lower(@kuozhanming)
26. ????????????????????
27. ????????????????????
28. ????????????????????SELECT?@filetype?=?
29. ?????????????????????CASE(?@kuozhanming)
30. ????????????????????????WHEN?'doc'?THEN??'application/msword'
31. ????????????????????????WHEN?'log'?THEN?'application/octet-stream'
32. ????????????????????????WHEN?'ini'?THEN?'application/octet-stream'
33. ????????????????????????WHEN?'exe'?THEN?'application/octet-stream'
34. ????????????????????????WHEN?'dll'?THEN?'application/octet-stream'
35. ????????????????????????WHEN?'rar'?THEN?'application/octet-stream'
36. ????????????????????????WHEN?'txt'?THEN?'text/html'
37. ????????????????????????WHEN?'html'?THEN?'text/html'
38. ????????????????????????WHEN?'htt'?THEN?'text/html'
39. ????????????????????????
40. ????????????????????????WHEN?'bmp'?THEN?'image/bmp'
41. ????????????????????????WHEN?'jpg'?THEN?'image/pjpeg'
42. ????????????????????????WHEN?'avi'?THEN?'video/avi'
43. ????????????????????????else?@kuozhanming
44. ????????????????????end?
45. ????????????????end
46. ????????end
47. ????
48. ????return?@filetype
49. END

1. drop?function??if?exists?getfiletype;
2. DELIMITER?|
4. /*?
5. ?取出文件名中扩展名的文件名称?
6. ?比如?
7. ?c:/1234/5678.txt?
8. ?则返回?txt对应的?text/html;?
9. */?
10. CREATE?FUNCTION?getfiletype?(t_kuozhanming???varchar(100))??
11. RETURNS?varchar(100)?
12. BEGIN?
13. ????declare?t_filetype????varchar(100);
14. ????declare?t_index?int;
16. ????set?t_filetype='';
17. ????if?(t_kuozhanming?is?null or t_kuozhanming?=?'')?then
18. ????????set?t_filetype?=?'';
19. ????else
20. ????????SET?t_kuozhanming?=?Reverse(t_kuozhanming);
21. ????????SET?t_index?=?locate('.',t_kuozhanming)?;
22. ????????if?(t_index?<?2)?then
23. ????????????set?t_filetype?=?'';
24. ????????else
25. ????????????SET?t_kuozhanming?=?left(t_kuozhanming,t_index?-?1);
26. ????????????SET?t_kuozhanming?=?Reverse(t_kuozhanming);
27. ????????????SET?t_kuozhanming?=?Lower(t_kuozhanming);
28. ????????????SELECT?CASE(?t_kuozhanming)
29. ????????????????????????WHEN?'doc'?THEN??'application/msword'
30. ????????????????????????WHEN?'log'?THEN?'application/octet-stream'
31. ????????????????????????WHEN?'ini'?THEN?'application/octet-stream'
32. ????????????????????????WHEN?'exe'?THEN?'application/octet-stream'
33. ????????????????????????WHEN?'dll'?THEN?'application/octet-stream'
34. ????????????????????????WHEN?'rar'?THEN?'application/octet-stream'
35. ????????????????????????WHEN?'txt'?THEN?'text/html'
36. ????????????????????????WHEN?'html'?THEN?'text/html'
37. ????????????????????????WHEN?'htt'?THEN?'text/html'
38. ????????????????????????
39. ????????????????????????WHEN?'bmp'?THEN?'image/bmp'
40. ????????????????????????WHEN?'jpg'?THEN?'image/pjpeg'
41. ????????????????????????WHEN?'avi'?THEN?'video/avi'
42. ????????????????????????else?t_kuozhanming
43. ????????????????????end?into?t_filetype;
44. ????????end?if;
45. ????end?if;
46. ????
47. ????return?t_filetype;
50. end|
51. DELIMITER?;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!