在做快捷查询时,触发器如下啊。
向cargo2中的更新数据,令cargo1中的valid字段1
create trigger test1
before insert on cargo2
for each row
begin
update cargo1 set valid = '1' where number = new.number;
end//
create procedure Pcargo3 (in num varchar(20))
begin
select * from cargo3 where id = (select id from cargo3 where number = num
by id desc limit 1);
end;//
K, 0 rows affected (0.00 sec)
有动态处理sql语句的存储过程
create procedure pTest8(in id int,in lie varchar(20))
begin
set @sql=concat('select ',lie,' from caseinfo where id = ',id);
prepare stml from @sql;
execute stml;
end//
show procedure status --显示数据库中所有存储的存储过程基本信息,包括所属数据库,存储过 程名称,创建时间等
show create procedure sp_name --显示某一个存储过程的详细信息
create procedure pAttfile(in num varchar(20),in col varchar(20))
begin
set @sql=concat("select ",col," from attfile where id = (select id from attfile where ",col," !='' and number = '",num,"' order by id desc limit 1)");
prepare s from @sql;
execute s;
end//
原变量名为column试了很久未成功,改成col成功了。
function attShow($n,$c){
global $host,$user,$password,$database;
$m=new mysqli($host,$user,$password,$database);
$q=$m->query("call pAttfile('".$n."','".$c."')");
if($q != NULL){
$r=$q->fetch_array(MYSQLI_ASSOC);
if(!$r){
return " ";
}else{
switch ($r[$c]) {
case 'Yes':return '<font color="red">YES</font>';break;
case 'No':return '<font color="red">NO</font>';break;
default:
return '<a href="upload/'.$r[$c].'" target="_blank">DownLoad</a>';break;
}
}
}else{
return "isNull";
}
}
上述存储函数,为何要这般定义,不解吧,一个下午的时间均在解决此上了。原因不明啊。
不能直接global啊。不行
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
以下是在做仓储管理系统三次升级时用到的。
<?php
$success=1;
//disable the autocommit feature
$m->autocommit(FALSE);//设置不自动提交
$sql="update basket set ORDERID =ORDERID - '9' where id='4'";
$result1=$m->query($sql);
if(!$result1){
$success=0;echo 'failed1';
}
//section 2
$sql2="update orders set TOPAY = TOPAY - '2' whesre id = '1'";
$result2=$m->query($sql2);
if(!$result2){
$success=0;echo 'failed2';
}
echo $success?"true":"false";
echo "<br>";
if($success){
$m->commit();
echo "the swap took place!Congratulatios!";
}else{
$m->rollback();
echo "there was a problem with your swap.";
}
$m->autocommit(TRUE);
?>
有以下注意点:
浪费了我很长时间啊MYSQL只有 INNODB和BDB类型的数据表才支持事务处理,其他的类型是不支持的!
<?php
include_once("conn.php");
$id=$_GET[id];
$conn->autocommit(false);
if(!$conn->query("delete from tb_sco where id='".$id."'"))
{
$conn->rollback();
}
if(!$conn->query("delete from tb_stu where id='".$id."'"))
{
$conn->rollback();
}
$conn->commit();
$conn->autocommit(true);
echo "ok"
?>