mysql——使用——函数——示例

table employee ( num int(50), d_id int(50), name varchar(50), age int(50), sex varchar(50), homeadd varchar(50) ); insert into employee values(1,1001,‘zhangsan‘,26,‘nan‘,‘beijing‘); insert into employee values(2,1001,‘lisi‘,24,‘nv‘,‘hunan‘); insert into employee values(3,1002,‘wangwu‘,25,‘nan‘,‘jiangsu‘); insert into employee values(4,1004,‘aric‘,15,‘nan‘,‘yingguo‘); select * from employee; create table department ( d_id int(50), d_name varchar(50), functione varchar(50), address varchar(50) ); insert into department values(1001,‘keyanbu‘,‘yanfachanpin‘,‘3lou5hao‘); insert into department values(1002,‘shengchanbu‘,‘shengchanchanp‘,‘5louyiceng‘); insert into department values(1003,‘xiaoshoubu‘,‘cehuaxiaoshou‘,‘1louxiaoshoudating‘); select * from employee; select * from department;

 

 

 

定义一个存储函数：

delimiter <span style="color: #808080;">&&</span>
<span style="color: #0000ff;">create</span> <span style="color: #0000ff;">function</span> name_from_employee ( emp_id <span style="color: #0000ff;">INT</span><span style="color: #000000;"> )
</span><span style="color: #0000ff;">returns</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">20</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">begin</span>
     <span style="color: #0000ff;">return</span> ( <span style="color: #0000ff;">select</span> name <span style="color: #0000ff;">from</span> employee <span style="color: #0000ff;">where</span> num <span style="color: #808080;">=</span><span style="color: #000000;"> emp_id );
</span><span style="color: #0000ff;">end</span> <span style="color: #808080;">&&</span><span style="color: #000000;">
delimiter;</span>

 

 

 

 

 

查看此存储函数的状态：

show <span style="color: #0000ff;">function</span> status <span style="color: #808080;">like</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">name_from_employee</span><span style="color: #ff0000;">‘</span>;

 

 

 

 

 

查看此函数的定义：

show <span style="color: #0000ff;">create</span> <span style="color: #0000ff;">function</span> name_from_employee;

 

 

 

 

调用此函数：

<span style="color: #0000ff;">select</span> name_from_employee(<span style="color: #800000; font-weight: bold;">3</span>);

 

 

 

 

 

删除此函数：

<span style="color: #0000ff;">drop</span> <span style="color: #0000ff;">function</span> name_from_employee;

mysql——使用——函数——示例

标签：alt   uda   values   iter   show   char   示例   returns   turn