达梦数据库DDL语句加字段
ddl语言自定义数据库字段
ddl语言自定义数据库字段<br><!--#include virtual="/public/back_top.asp"--><br><html><br><head><br><meta http-equiv="Content-Type" content="text/html; charset=gb2312"><br><link href="member/css/common.css" rel="stylesheet" type="text/css"><br></head><br> <body> <br> <div style="margin-top:15px;"><br> <%<br> sFileName = "alter_manage.asp"<br> tableName = "C_alter"<br> tableName2 = "product"<br> action = Trim(Request.QueryString("action"))<br> %><br> <br> <%<br> Select Case action<br> case ""<br> Call ShowContent()<br> case "add"<br> Call add()<br> case "addsave"<br> Call addsave()<br> case "modify"<br> Call modify()<br> case "modifysave"<br> Call modifysave()<br> case "delall"<br> Call delall()<br> End Select<br> %><br> <br> <%<br> Sub ShowContent()<br> Call gRsClose()<br> sSql = "select * from view_alter"<br> <br> oRs.Open sSql,oConn,1,1<br> <br> %><br> <table width="98%" align=center cellpadding="3" cellspacing=1 bgcolor="#cccccc"><br> <tbody><br> <Form method="post" id="form1" name="form1"><br> <tr bgcolor='#efefef' height='20'><td colspan="5" align='center' style='font-weight:bold;'>字 段 信 息 管 理</td><br> <td align='center' style='font-weight:bold;'><a href="<%=sFileName%>?action=add">添加</a></td><br> </tr><br> <tr bgcolor='#FCFCFC' height='20'><br> <td width="5%" align="center">选择</td><br> <td width="16%" align="center">产品类别</td><br> <td width="14%" align="center">字段类型</td><br> <td width="20%" align="center">字段名称</td><br> <td width="35%" align="center">字段意义</td><br> <td width="10%" align="center">操作</td><br> </tr><br> <%<br> do while not oRs.eof and not oRs.bof <br> select case oRs("a_type")<br> case "nvarchar(255)"<br> a_type = "文本类型"<br> case "int"<br> a_type = "整数类型"<br> case "float"<br> a_type = "浮点类型"<br> case else<br> a_type = "未知类型"<br> end select<br> %><br> <tr bgcolor='#ffffff' height='20'><br> <td width="5%" align="center"><%Response.Write("<input type='checkbox' id='checkzi' name='checkzi' value='" & oRs("a_id") & "'>" & vbCrlf)%></td><br> <td width="16%" align="center"><%Response.Write(oRs("c_name"))%></td><br> <td width="14%" align="center"><%Response.Write(a_type)%></td><br> <td width="20%" align="center"><%Response.Write(oRs("a_name"))%></td><br> <td width="35%" align="center"><%Response.Write(oRs("a_detail"))%></td><br> <td width="10%" align="center"><a href="<%=sFileName%>?action=delall&checkzi=<%=oRs("a_id")%>">删除</a> <a href="<%=sFileName%>?action=modify&a_id=<%=oRs("a_id")%>">修改</a> </td><br> </tr><br> <br> <br> <%<br> oRs.movenext<br> loop<br> %><br> <tr bgcolor='#ffffff' height='20'><br> <td colspan="6" align="center"><br> <%<br> Response.Write("<input type='checkbox' id='checkall' name='checkall' onclick='selectAll();'><font color='#ff0000'><a style='cursor:hand;'>全/反选</a></font>" & vbCrlf)<br> Response.Write("<input type='button' value='删除所选' class='input_submit' onClick=""checkSelect('" & sFileName & "','?action=delall');"">" & vbCrlf)<br> %><br> </td><br> </tr><br> </form><br> </tbody><br> </table><br> <%<br> Call gRsClose()<br> End Sub<br> %><br> <br> <br> <%<br> Sub add()<br> %><br> <Script Language=JavaScript><br> function doSubmit(){<br> if(parseInt(document.getElementById("a_classid").options[document.getElementById("a_classid").selectedIndex].value)==0)<br> {<br> alert("请选择产品类型!");<br> return false;<br> }<br> <br> if(parseInt(document.getElementById("a_type").options[document.getElementById("a_type").selectedIndex].value)==0)<br> {<br> alert("请选择字段类型!");<br> return false;<br> }<br> if(document.getElementById("a_name").value=="")<br> {<br> alert("字段名称不能为空!");<br> document.getElementById("a_name").select();<br> return false;<br> } <br> <br> if(document.getElementById("a_detail").value=="")<br> {<br> alert("请填写该字段的意义!");<br> document.getElementById("a_detail").select();<br> return false;<br> }<br> <br> }<br> </script><br> <table width="58%" border="0" align="center" cellpadding="3" cellspacing="1" bgcolor="#DEDFDE"><br> <form name="myform" method="post" action="<%=sFileName%>?action=addsave" onSubmit="return doSubmit();"><br> <input type="hidden" name="Action" value="AddSave"><br> <tr><br> <td colspan="4" bgcolor="#eeeeee"><div align="center"><strong>字 段 信 息 <br> 添 加</strong></div></td><br> </tr><br> <tr><br> <td bgcolor="#FFFFFF">选择类别:</td><br> <td width="580" colspan="3" nowrap bgcolor="#FFFFFF"><%=gMakeClassSelect("a_classid","p_class",0)%></td><br> </tr><br> <tr><br> <td nowrap bgcolor="#FFFFFF">字段类型:</td><br> <td colspan="3" nowrap bgcolor="#FFFFFF"><label><br> <select name="a_type" class="INPUT" id="a_type"><br> <option value="0" selected>请您选择类型</option><br> <option value="int">整数型</option><br> <option value="nvarchar(255)">文本型</option><br> <option value="float">浮点型</option><br> </select><br> </label></td><br> </tr><br> <tr><br> <td nowrap bgcolor="#FFFFFF">字段名称:</td><br> <td colspan="3" nowrap bgcolor="#FFFFFF"><input name="a_name" type="text" id="a_name" size="80" maxlength="50" class="input_text" > </td><br> </tr><br> <tr><br> <td bgcolor="#FFFFFF">字段意义:</td><br> <td colspan="3" nowrap bgcolor="#FFFFFF"><input name="a_detail" type="text" id="a_detail" size="80" maxlength="50" class="input_text" ></td><br> </tr><br> <br> <tr bgcolor="#efefef" height="30"><br> <td colspan="4" align="center"><br> <input type="submit" name="Submit" value=" 提 交 " class="input_submit" ><br> <input type="reset" name="Submit" value="重 置" class="input_submit"><br> </td><br> </tr><br> </form><br> </table><br> <%<br> End Sub<br> %><br> <br> <br> <%<br> Sub addsave()<br> a_classid = GetSafeStr(Trim(Request.Form("a_classid")))<br> a_name = GetSafeStr(Trim(Request.Form("a_name")))<br> a_detail = GetSafeStr(Trim(Request.Form("a_detail")))<br> a_type = GetSafeStr(Trim(Request.Form("a_type")))<br> a_regtime = now() <br> '判断主表中要添加的字段是否存在<br> eSql = "select * from syscolumns where id=object_id('"& tableName2 &"') and name='"& a_name &"'"<br> Call gRsClose() <br> oRs.Open eSql,oConn,1,3<br> if not oRs.EOF then<br> Call ErrorMsg(sFileName,"主表中字段已经存在在,三秒后返回",1)<br> end if<br> Call gRsClose() <br> <br> '将字段相关信息添加到从表<br> sSql = "select * from "& tableName &" where 1=1"<br> Call gRsClose() <br> oRs.Open sSql,oConn,1,3<br> oRs.AddNew()<br> oRs("a_classid") = a_classid<br> oRs("a_name") = a_name<br> oRs("a_detail") = a_detail<br> oRs("a_type") = a_type<br> oRs("a_regtime") = a_regtime<br> oRs.Update()<br> <br> '添加字段<br> <br> oConn.Execute("ALTER TABLE "& tableName2 &" ADD "& a_name &" "& a_type &"")<br> Call ErrorMsg(sFileName,"添加字段成功,三秒后返回",1)<br> Call gRsClose() <br> End Sub<br> %><br> <br> <br> <br> <%<br> Sub modify()<br> %><br> <Script Language=JavaScript><br> function doSubmit(){<br> if(parseInt(document.getElementById("a_classid").options[document.getElementById("a_classid").selectedIndex].value)==0)<br> {<br> alert("请选择产品类型!");<br> return false;<br> }<br> <br> if(parseInt(document.getElementById("a_type").options[document.getElementById("a_type").selectedIndex].value)==0)<br> {<br> alert("请选择字段类型!");<br> return false;<br> }<br> if(document.getElementById("a_name").value=="")<br> {<br> alert("字段名称不能为空!");<br> document.getElementById("a_name").select();<br> return false;<br> } <br> <br> if(document.getElementById("a_detail").value=="")<br> {<br> alert("请填写该字段的意义!");<br> document.getElementById("a_detail").select();<br> return false;<br> }<br> <br> }<br> </script><br> <%<br> Call gRsClose()<br> a_id = Trim(Request.QueryString("a_id"))<br> sSql = "select * from view_alter where a_id = "& a_id<br> oRs.Open sSql,oConn,1,1<br> If not oRs.eof and not oRs.bof Then<br> a_classid = oRs("a_classid")<br> a_name = oRs("a_name")<br> a_detail = oRs("a_detail")<br> a_type = oRs("a_type")<br> End If<br> Call gRsClose()<br> %><br> <table width="58%" border="0" align="center" cellpadding="3" cellspacing="1" bgcolor="#DEDFDE"><br> <form name="myform" method="post" action="<%=sFileName%>?action=modifysave&a_id=<%=a_id%>" onSubmit="return doSubmit();"><br> <input type="hidden" name="Action" value="AddSave"><br> <tr><br> <td colspan="4" bgcolor="#eeeeee"><div align="center"><strong>字 段 信 息 <br> 添 加</strong></div></td><br> </tr><br> <tr><br> <td bgcolor="#FFFFFF">选择类别:</td><br> <td width="580" colspan="3" nowrap bgcolor="#FFFFFF"><%=gMakeClassSelect("a_classid","p_class",a_classid)%></td><br> </tr><br> <tr><br> <td nowrap bgcolor="#FFFFFF">字段类型:</td><br> <td colspan="3" nowrap bgcolor="#FFFFFF"><label><br> <select name="a_type" class="INPUT" id="a_type"><br> <option value="0" <%If a_type="" Then Response.Write("selected")%>>请您选择类型</option><br> <option value="int" <%If a_type="int" Then Response.Write("selected")%>>整数型</option><br> <option value="nvarchar(255)" <%If a_type="nvarchar(255)" Then Response.Write("selected")%>>文本型</option><br> <option value="float" <%If a_type="float" Then Response.Write("selected")%>>浮点型</option><br> </select><br> </label></td><br> </tr><br> <tr><br> <td nowrap bgcolor="#FFFFFF">字段名称:</td><br> <td colspan="3" nowrap bgcolor="#FFFFFF"><input name="a_name" type="text" class="input_text" id="a_name" value="<%=a_name%>" size="80" maxlength="50" > </td><br> </tr><br> <tr><br> <td bgcolor="#FFFFFF">字段意义:</td><br> <td colspan="3" nowrap bgcolor="#FFFFFF"><input name="a_detail" type="text" class="input_text" id="a_detail" value="<%=a_detail%>" size="80" maxlength="50" ></td><br> </tr><br> <br> <tr bgcolor="#efefef" height="30"><br> <td colspan="4" align="center"><br> <input type="submit" name="Submit" value=" 修 改 " class="input_submit" ><br> <input type="reset" name="Submit" value="重 置" class="input_submit"><br> </td><br> </tr><br> </form><br> </table><br> <%<br> End Sub<br> %><br> <br> <%<br> Sub modifysave()<br> a_classid = GetSafeStr(Trim(Request.Form("a_classid")))<br> a_name = GetSafeStr(Trim(Request.Form("a_name")))<br> a_detail = GetSafeStr(Trim(Request.Form("a_detail")))<br> a_type = GetSafeStr(Trim(Request.Form("a_type")))<br> a_regtime = now()<br> a_id = Trim(Request.QueryString("a_id"))<br> <br> <br> '将字段相关信息添加到从表<br> sSql = "select * from "& tableName &" where a_id = "& a_id <br> Call gRsClose() <br> oRs.Open sSql,oConn,1,3<br> <br> aname = oRs("a_name")<br> atype = oRs("a_type")<br> on error resume next<br> oRs("a_classid") = a_classid<br> '判断主表中是否有相同的字段(保持主从表中的数据一致)<br> set rs1=oConn.Execute("select 1 from syscolumns where id=object_id('"& tableName2 &"') and name='"& a_name &"'")<br> If rs1.eof and rs1.bof Then<br> oRs("a_name") = a_name<br> End If<br> rs1.close()<br> oRs("a_detail") = a_detail<br> oRs("a_type") = a_type<br> oRs.Update()<br> <br> If a_name<>aname Then<br> '修改字段名称(判断主表中是否有相同的字段)<br> oConn.Execute("if not exists(select 1 from syscolumns where id=object_id('"& tableName2 &"') and name='"& a_name &"') EXEC sp_rename '"& tableName2 &".["& aname &"]', '"& a_name &"', 'COLUMN'")<br> End If<br> If a_type<>atype Then<br> '修改字段类型<br> oConn.Execute("ALTER TABLE "& tableName2 &" ALTER COLUMN "& a_name &" "& a_type &"")<br> End If<br> <br> Call ErrorMsg(sFileName,"修改字段成功,三秒后返回",1)<br> Call gRsClose()<br> End Sub<br> %><br> <br> <%<br> Sub delall()<br> checkzi = Trim(Request("checkzi"))<br> If checkzi <> "" Then<br> <br> Dim z1<br> z1 = tableName<br> Dim temp_id,last_id<br> last_id="("<br> temp_id = Split(Request("checkzi"),",")<br> <br> For i=0 To UBound(temp_id)<br> last_id = last_id & Trim(temp_id(i)) & ","<br> Next<br> last_id = Left(last_id,Len(last_id)-1)<br> last_id = last_id & ")"<br> 'last_id为最终要删除的记录ID<br> <br> '取出要删除的对应字段<br> sSql = "select a_name from "& z1 &" where a_id in " & last_id<br> Call gRsClose()<br> oRs.Open sSql,oConn,1,1<br> dim a_names<br> a_names = ""<br> jj=1<br> do while not oRs.eof and not oRs.bof<br> If jj <> oRs.recordcount Then<br> a_names = a_names & oRs("a_name") &","<br> Else<br> a_names = a_names & oRs("a_name")<br> End If<br> oRs.movenext<br> jj = jj + 1<br> loop<br> Call gRsClose()<br> <br> '删除主表对应字段<br> a_name = Split(a_names,",")<br> for i=0 to Ubound(a_name)<br> oConn.Execute("ALTER TABLE "& tableName2 &" DROP COLUMN "& a_name(i) &"")<br> next<br> <br> '删除从表中字段的意义信息<br> sSql = "delete from " & z1 & " where a_id in " & last_id<br> oConn.Execute(sSql)<br> Call ErrorMsg(sFileName,"成功删除所选记录,三秒后将返回……",1)<br> End If<br> End Sub<br> %><br> </div><br> </body><br></html><br><!--#include virtual="/public/back_bottom.asp"-->