随笔备忘

最新推荐文章于 2024-10-09 12:58:26 发布

weixin_30922589

最新推荐文章于 2024-10-09 12:58:26 发布

阅读量63

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/we1700/archive/2011/08/22/2149005.html

版权

//创建临时表空间
create temporary tablespace test_temp
tempfile 'E:\oracle\product\10.2.0\oradata\testserver\test_temp01.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;

//创建数据表空间
create tablespace test_data
logging
datafile 'E:\oracle\product\10.2.0\oradata\testserver\test_data01.dbf'
size 32m
autoextend on
next 32m maxsize 2048m
extent management local;

//创建用户并指定表空间
create user testserver_user identified by testserver_user
default tablespace test_data
temporary tablespace test_temp;

//给用户授予权限
grant connect,resource to testserver_user; (db2：指定所有权限)
grant dba to name;(用于赋予DBA权限)否在在数据导入导出是会出现(ERROR 只有DBA才能导入其他DBA导出的数据)

//导入导出命令
imp userName/userPwd@sid file='D:/ddd.dmp' full = y

exp userName/userPwd@sid file='D:/ddd.dmp' ignore = y

=======================================2011.7.13================================

//-------存储过程中可以用这样的方式进行值存储
select field_a ,field_b into a,b from table2 where field_c=c;

//----存储过程可以放在packge 里还有 packge_bodies

============================================2011.7.17===========================
业务逻辑判断很是累人

============================================2011.7.21============================

用于存储大文件
bool TUpLoadTxtData::loadTxtData1(AnsiString sqlhead,AnsiString filePath)
{
    AnsiString sql = "",strTmp = "",strCell = "";
    TStringList *sl = new TStringList();
    sql = "begin ";
    sl->Clear();
    sl->LoadFromFile(filePath);
    for (int i = 1;i < sl->Count;i++)
    {
        int j=28;

sql += sqlhead;

        strTmp = sl->Strings[i].Trim();
        while(j>0){
        strCell = strTmp.SubString(0,strTmp.Pos("\t") - 1);
        strTmp = strTmp.SubString(strTmp.Pos("\t") + 1,strTmp.Length());
        sql += strCell + "','";
        /*strCell = strTmp.SubString(0,strTmp.Pos(" ") - 1);
        strTmp = strTmp.SubString(strTmp.Pos(" ") + 1,strTmp.Length());
        sql += strCell + "','";   */
        j--; }
        sql += strTmp + "');";
        //ShowMessage(sql);
        if (i%1000 == 0)
        {
             sql += " commit;end;";
             try
             {
                dm->oq_txtData->Close();
                dm->oq_txtData->SQL->Clear();
                dm->oq_txtData->SQL->Add(sql);
                dm->oq_txtData->ExecSQL();
                dm->oq_txtData->Close();
             }
             catch(...)
             {
                dm->oq_txtData->Close();
                sl->Free();
                sl = NULL;
                return false;
             }
             sql = "begin ";
        }

}

    if (sql != "begin ")
    {
        sql += " commit;end;";
        try
        {
        dm->oq_txtData->Close();
        dm->oq_txtData->SQL->Clear();
        dm->oq_txtData->SQL->Add(sql);
        dm->oq_txtData->ExecSQL();
        dm->oq_txtData->Close();
        }
        catch(...)
        {
        dm->oq_txtData->Close();
        sl->Free();
        sl = NULL;
        return false;
        }
    }

    sl->Free();
    sl = NULL;
    return true;
}

======================2011.7.21===
update TO_FTFYMX f set
mx_rygz =(select sum(b.bzxx_bzls * l.ltftz) from to_bzxx b,tmp_ltftz l
where b.bzxx_xxdm=l.cx and b.bzxx_ctlxdm=l.cz and l.ltftmc='旅客列车服务人员工资及福利费' and b.bzxx_wsfcc=f.mx_cc)

=====================2011.7.24====
sql语句中进行字符串连接的语句
update td_jzd set jzd_jmc = jzd_jmc||'局'
======================2011.8.2======
总结以前的

    TIniFile *iniPTASS = new TIniFile(".\\para_PTASS.ini");
    OraSession1->Username = iniPTASS->ReadString("database","username","ptass");
    OraSession1->Password = iniPTASS->ReadString("database","password","ptass");

    OraSession1->Server = iniPTASS->ReadString("database","ip","127.0.0.1") + ":1521:" + iniPTASS->ReadString("database","sid","ptass");
    iniPTASS->Free();
    try
    {
        OraSession1->Connected = false;
        OraSession1->Connect();
    }
    catch(...)
    {
        ShowMessage("服务器连接错误");
        Application->Terminate();
    }
//-------------------------------------------------------------------------------------------------------------

try{

         dm->hs_show->Close();
         dm->hs_show->SQL->Clear();
         dm->hs_show->SQL->Add(sql);
         dm->hs_show->Open();
         dm->hs_show->First();

if(dm->hs_show->RecordCount<1)
{

ShowMessage("没有相关信息！");
return;

}

while(!dm->hs_show->Eof)
{

             asg_ykhs->Cells[0][asg_ykhs->RowCount-1]=Trim(dm->hs_show->FieldByName("HS_SFRQ")->AsString);
             dm->hs_show->Next();
             asg_ykhs->RowCount++;

}

asg_ykhs->RowCount--;
dm->hs_show->Close();

   }
   catch(...)
   {

dm->hs_show->Close();
ShowMessage("检索数据库失败没有asg_ykhs相关信息！");

}

=========================================================2011.8.15=====
UPDATE A SET (A1, A2, A3) = (SELECT B1, B2, B3 FROM B WHERE A.ID = B.ID)
WHERE ID IN (SELECT B.ID FROM B WHERE A.ID = B.ID)

========================================================2011.8.18=======
两个整型数，不准用if 、switch 、？：等判断语句求出两者最大值

int max (n[])
{

return n[(n[0]-n[1])>>31&1] //可以求得

}

=======================================================2011.8.19========
merge into test_cc a
using
(
select id,name,to_char(sum(to_number(flag))) as flag
from test_cc
group by id,name
) b
on (a.id=b.id and a.name=b.name)
when matched then
update SET a.flag = b.flag;

---删除重复行

delete A where rowid not in (select min(rowid) from A group by id,name,date);

--select 查询多条进行更新
update id set (a,b,c)=(select a,b,c from ida where ida.id=id.id);

=========================================================================

转载于:https://www.cnblogs.com/we1700/archive/2011/08/22/2149005.html