数据库常用资料

=======================================================================
sqlserver 连接 oracle的方法：
EXEC sp_addlinkedserver @server = 'gztest', @srvproduct = 'oracle',
@provider = 'msdaora', @datasrc = 'gztest'

EXEC sp_addlinkedsrvlogin 'gztest', false, 'sa', 'gzzb', 'j2eeoracle'

select * from openquery(gztest,'select * from 表名')

exec sp_dropserver 'gztest','droplogins'

SELECT * FROM gztest..GZZB.T_ZJ_JBXX AS T_ZJ_JBXX_1

=========================================================================
游标用法：


/*导入专家数据*/
ALTER PROC [dbo].[sp_Exch_ZJXX]
as

--声明变量
declare @zjbh nvarchar(32)
declare @newZjbh nvarchar(32)

--声明一个游标mycursor，select语句中参数的个数必须要和从游标中取出的变量名相同
declare mycursor cursor for select zjbh FROM GZTEST..GZZB.T_ZJ_JBXX where zt=5

BEGIN


--打开游标
open mycursor

--从游标里获取数据赋值到变量中
fetch next from mycursor into @zjbh

--判断游标的状态 0 fetch语句成功；-1 fetch语句失败或此行不在结果集中；-2 被提取的行不存在
while(@@fetch_status=0)
begin

--显示每次用游标获取的值
print '游标成功取出一条数据'
print @zjbh
print dbo.fun_GetMaxZjbh()

/* 第一步：获取最大专家编号 */

set @newZjbh = dbo.fun_GetMaxZjbh()

/* 第二步：更新BS库中数据专家编号及状态 */

--更新专家基本资料表中对应的专家编号
update GZTEST..GZZB.T_ZJ_JBXX set zjbh=@newZjbh,zt=4 where zjbh=@zjbh
--更新专家回避单位表中对应的专家编号
update GZTEST..GZZB.T_ZJ_GZDW set zjbh=@newZjbh where zjbh=@zjbh
--更新专家特长表中对应的专家编号
update GZTEST..GZZB.T_ZJ_ZJTC set zjbh=@newZjbh where zjbh=@zjbh
--更新专家职业资格表中对应的专家编号
update GZTEST..GZZB.T_ZJ_ZYZG set zjbh=@newZjbh where zjbh=@zjbh
--更新专家技术职称表中对应的专家编号
update GZTEST..GZZB.T_ZJ_JSZC set zjbh=@newZjbh where zjbh=@zjbh
--更新专家测试成绩表中对应的专家编号
update GZTEST..GZZB.T_ZJ_SCORES set zjbh=@newZjbh where zjbh=@zjbh


/* 第三步：将BS库中专家信息导入CS库中 */

--将专家基本资料插入专家信息临时表
insert into TB_BI_ExpBaseInfo_History(FC_ExpID, FC_ExpName, FC_LOAID, FN_IDType, FC_ID,
FN_EmpStatus, FN_ExpStatus, FB_Emergency, FN_ExpLevel,
FC_MobileTel, FC_OfficeTel, FC_Sex, FD_BirthDay, FC_Polity,
FC_Education, FC_Academy, FC_SPEC,
FC_EngagedSPEC, FN_FNOY, FC_Email, FB_InService,
FC_DBSource, FC_Resume, FC_Recomendation, FD_RecomendDate,
FC_Remark, FC_Creator, FD_CreateDate, FC_Editor, FD_EditDate, FN_RecStatus)
select zjbh,xm,psbh,zjlx,sfzh,zt,3,yjzj,zjdj,stdh,bgdh,xb,
csrq,zzmm,xl,byyx,sxzy,xcszy,zynx,mail,sfzz,'外网',
gzjl,dwtjyj,lrrq,bz,lrr,lrrq,lrr,lrrq,1
from GZTEST..GZZB.T_ZJ_JBXX where zjbh=@newZjbh
--将专家回避单位记录插入专家回避单位表
insert into TB_BM_ExpEvasive(FC_ExpID, FC_InnerCorpID, FC_Creator, FD_CreateDate, FC_Editor, FD_EditDate, FN_RecStatus, FN_IsCurCorp)
select ZJBH, dbo.fun_GetIdByDwbh(DWBH), '外网', ZJLRRQ, '外网', ZJLRRQ, 0,DQDW from GZTEST..GZZB.T_ZJ_GZDW where zjbh=@newZjbh
--将专家特长记录插入专家特长表
insert into TB_BI_ExpSpec(FC_ExpID, FN_SpecID, FN_SpecLevel, FC_Creator, FD_CreateDate, FC_Editor, FD_EditDate, FN_RecStatus)
select ZJBH, TCID, ZJJB, '外网', ZJLRRQ, '外网', ZJLRRQ, 0 from GZTEST..T_ZJ_ZJTC where zjbh=@newZjbh
--将专家职业资格记录插入专家职业资格表
insert into TB_BI_ExpQualification(FC_ExpID, FC_Qualification, FC_RegID, FC_RegCorp, FC_Creator, FD_CreateDate, FC_Editor, FD_EditDate, FN_RecStatus)
select ZJBH, ZYZG, ZCZH, ZYZCDW, '外网', ZJLRRQ, '外网', ZJLRRQ, 0 from GZTEST..T_ZJ_ZYZG where zjbh=@newZjbh
--将专家技术职称记录插入专家技术职称表
insert into TB_BI_ExpTecPost(FC_ExpID, FC_TecPostName, FC_CertificateID, FC_Creator, FD_CreateDate, FC_Editor, FD_EditDate, FN_RecStatus)
select ZJBH, ZY, ZH, '外网', ZJLRRQ, '外网', ZJLRRQ, 0 from GZTEST..GZZB.T_ZJ_JSZC where zjbh=@newZjbh


--用游标去取下一条记录
fetch next from mycursor into @zjbh

end
--关闭游标
close mycusor
--撤销游标
deallocate mycursor


END



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



/*取最大专家编号函数*/

ALTER FUNCTION [dbo].[fun_GetMaxZjbh]
()
RETURNS varchar(32)
AS

BEGIN
declare @zjbh varchar(32);
declare @bh varchar(4);
declare @num int;
declare @numStr varchar(8);
set @zjbh = (select max(FC_ExpID) from TB_BI_ExpBaseInfo_History);
set @bh = substring(@zjbh, 1, 2);
set @num = substring(@zjbh,3,9);
set @num = @num + 1;
set @numStr = @num + '';
set @zjbh = @bh + @numStr;
return @zjbh;
END


/* 根据单位编号获取guid编号 */
ALTER FUNCTION [dbo].[fun_GetIdByDwbh]
(@dwbh varchar(32))
RETURNS varchar(32)
AS

BEGIN
declare @ID varchar(32);
SET @ID = (select max(FC_InnerCorpID) from TB_BI_Corpration where FC_OuterCorpID=@dwbh);

RETURN @ID;
END

/* 根据String证件类型获取Int类型 */

ALTER FUNCTION [dbo].[fun_GetIDTypeInt]
(@IDType varchar(32))
RETURNS int
AS

BEGIN
declare @IDTypeInt int;
SET @IDTypeInt =
CASE @IDType
WHEN '身份证' THEN 1
WHEN '军人证' THEN 2
WHEN '护照' THEN 3
WHEN '其它' THEN 4
ELSE 0
END;

RETURN @IDTypeInt;
END



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

MD5函数：

CREATE OR REPLACE function md5(input_string VARCHAR2) return varchar2
IS
raw_input RAW(128) := UTL_RAW.CAST_TO_RAW(input_string);
decrypted_raw RAW(2048);
error_in_input_buffer_length EXCEPTION;
BEGIN
sys.dbms_obfuscation_toolkit.MD5(input => raw_input, checksum => decrypted_raw);
return lower(rawtohex(decrypted_raw));
END;
/


==========================================================================================
身份证转换函数

CREATE OR REPLACE FUNCTION ID15To18 (SFZH varchar2) RETURN STRING IS
S1 INTEGER;
S2 INTEGER;
S3 INTEGER;
S4 INTEGER;
S5 INTEGER;
S6 INTEGER;
S7 INTEGER;
S8 INTEGER;
S9 INTEGER;
S10 INTEGER;
S11 INTEGER;
S12 INTEGER;
S13 INTEGER;
S14 INTEGER;
S15 INTEGER;
S16 INTEGER;
S17 INTEGER;
S18 INTEGER;
RESULT VARCHAR(18);
BEGIN
S1 := TO_NUMBER(SUBSTR(SFZH,1,1));
S2 := TO_NUMBER(SUBSTR(SFZH,2,1));
S3 := TO_NUMBER(SUBSTR(SFZH,3,1));
S4 := TO_NUMBER(SUBSTR(SFZH,4,1));
S5 := TO_NUMBER(SUBSTR(SFZH,5,1));
S6 := TO_NUMBER(SUBSTR(SFZH,6,1));
S7 := 1;
S8 := 9;
S9 := TO_NUMBER(SUBSTR(SFZH,7,1));
S10 := TO_NUMBER(SUBSTR(SFZH,8,1));
S11 := TO_NUMBER(SUBSTR(SFZH,9,1));
S12 := TO_NUMBER(SUBSTR(SFZH,10,1));
S13 := TO_NUMBER(SUBSTR(SFZH,11,1));
S14 := TO_NUMBER(SUBSTR(SFZH,12,1));
S15 := TO_NUMBER(SUBSTR(SFZH,13,1));
S16 := TO_NUMBER(SUBSTR(SFZH,14,1));
S17 := TO_NUMBER(SUBSTR(SFZH,15,1));

S18 := ( (S1*7) + (S2*9) + (S3*10) + (S4*5) + (S5*8) +(S6*4) + (S7*2) + (S8*1) + (S9*6) + (S10*3) + (S11*7) + (S12*9) + (S13*10) + (S14*5) + (S15*8) + (S16*4) + (S17*2) ) MOD 11;
RESULT := SUBSTR(SFZH,1,6) || '19' || SUBSTR(SFZH,7,9);

IF S18=0 THEN
RESULT :=RESULT||'1';
END IF;
IF S18=1 THEN
RESULT :=RESULT||'0';
END IF;
IF S18=2 THEN
RESULT :=RESULT||'X';
END IF;
IF S18=3 THEN
RESULT :=RESULT||'9';
END IF;
IF S18=4 THEN
RESULT :=RESULT||'8';
END IF;
IF S18=5 THEN
RESULT :=RESULT||'7';
END IF;
IF S18=6 THEN
RESULT :=RESULT||'6';
END IF;
IF S18=7 THEN
RESULT :=RESULT||'5';
END IF;
IF S18=8 THEN
RESULT :=RESULT||'4';
END IF;
IF S18=9 THEN
RESULT :=RESULT||'3';
END IF;
IF S18=10 THEN
RESULT :=RESULT||'2';
END IF;

RETURN RESULT;


END;
/




==========================================================================================
获得树节点全名函数：

CREATE OR REPLACE FUNCTION getProjectSpeciality (idVar IN varchar2)
return varchar2 is
speciality varchar2(1000) :=''; --工程特性总名称
rootmlbhVar varchar2(50) :=''; --父节点id
mcVar varchar2(100) :=''; --临时待长名称

begin --开始部分
select rootmlbh,mc into rootmlbhVar,mcVar from t_xt_gmzb where id=idVar;
speciality := mcVar;
if(rootmlbhVar!='0') then --如果父节点不为0
speciality := getProjectSpeciality(rootmlbhVar)||'>>'||speciality ; --递归调用
else --如果父节点等于0,表示已经到了最顶层
speciality := mcVar;
end if;
return speciality;

END getProjectSpeciality;
/



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


CREATE OR REPLACE PROCEDURE Insert_Bmsq(bmbh IN VARCHAR2,xmbh IN VARCHAR2,dwbh IN VARCHAR2,xmjlbh IN VARCHAR2,jsyy IN VARCHAR2,sfLock IN NUMBER,
ldrxm IN VARCHAR2,lrr IN VARCHAR2,ldrid IN VARCHAR2,bscr IN VARCHAR2,bscrdh IN VARCHAR2,sftjcl IN NUMBER,bmfs IN VARCHAR2,zwyzjg IN NUMBER,
zstbrpm IN NUMBER,sfzstbr IN NUMBER,zstbrbz IN VARCHAR2) IS
dataToStr VARCHAR2(30);

exceptionmsg EXCEPTION;

/******************************************************************************
NAME: InsertBmsq
PURPOSE:投标报名与加锁写入一个存储过程，调用事务处理

******************************************************************************/
BEGIN
--将日期转换为字符型
dataToStr:=TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss');
--保存投标报名信息
INSERT INTO T_JY_TBBMSUB (ID,XMBH,DWBH,XMJLBH,BMSJ,LRR,LRRQ,ZJLRR,ZJLRRQ,BMXMJLBH,LDRID,LDRXM,BSCR,BSCRDH,SFTJCL,SFJF,FLAG,ZGBZ,DQBZ,BMFS,ZWYZJG,ZSTBRPM,SFZSTBR,ZSTBRBZ)
VALUES(bmbh,xmbh,dwbh,xmjlbh,dataToStr,lrr,dataToStr,lrr,dataToStr,xmjlbh,ldrid,ldrxm,bscr,bscrdh,sftjcl,0,0,0,0,bmfs,zwyzjg,zstbrpm,sfzstbr,zstbrbz);
--人员加锁
IF sfLock=1 THEN
INSERT INTO T_QY_RYJSJL(ID,RYBH,XMBH,JSYY,JBR,JBRQ) VALUES ('ryjs'||SUBSTR(sys_guid(),1,12),xmjlbh,xmbh,jsyy,lrr,SYSDATE);
UPDATE T_QY_ZYJSRY SET ISLOCKED=ISLOCKED+1 WHERE RYBH=xmjlbh;
END IF;
dbms_output.put_line('执行成功!');
COMMIT;
EXCEPTION
WHEN exceptionmsg THEN
ROLLBACK;

END Insert_Bmsq;
/


==========================================================================================
java调用存储过程：

public boolean InsertBmsq(AddbmsqForm info,int sfLock){
DB db=null;
CallableStatement cstmt=null;
try{
db=DBFactory.getDBInstance();
cstmt=db.createCallableStatement("{ call INSERT_BMSQ(?,?,?,?,?,?,?,?,?,?,?,?,?) }");
cstmt.setString(1,info.getID());
cstmt.setString(2,info.getXMBH());
cstmt.setString(3,info.getDWBH());
cstmt.setString(4,info.getXMJLBH());
cstmt.setString(5,info.getLRR());
cstmt.setString(6,info.getLDRID());
cstmt.setString(7,info.getLDRXM());
cstmt.setString(8,info.getBSCR());
cstmt.setString(9,info.getBSCRDH());
cstmt.setInt(10,info.getSFTJCL());
cstmt.setString(11,info.getBMFS());
cstmt.setInt(12,info.getZWYZJG());
cstmt.setInt(13,sfLock);
cstmt.execute();
cstmt.close();
db.close();
return true;
}catch(SQLException ex){
ex.printStackTrace();
}
finally{
try{
if(cstmt!=null){
cstmt.close();
cstmt=null;
}
if (db!=null){
db.close();
db=null;
}
}catch(SQLException ex1){
ex1.printStackTrace();
}
}
return false;
}



==========================================================================================
查询语句写法：

SELECT DISTINCT A."ZZJGDM" AS ZZJGDM,A."Djbabh" AS DJBABH,A."djbalb" AS DJBALB,A."yxqq" AS YXQQ,A."yxqz" AS YXQZ,2 AS JWQYLX,A."zzzsbh" AS ZZZSBH,D.ZXDWBH AS DWBH
FROM JlqyJbqkb@jgpt A INNER JOIN T_JW_JCZLDYGX D ON D.JWZZJGDM=A."ZZJGDM" AND A."ZZJGDM" IS NOT NULL AND A."yxqz" >= SYSDATE AND D.SFYX=0
AND EXISTS(SELECT 1 FROM T_JW_QYJCZL WHERE ZZJGDM=A.ZZJGDM)
UNION ALL
SELECT DISTINCT A."ZZJGDM" AS ZZJGDM,A."Djbabh" AS DJBABH,A."djbalb" AS DJBALB,A."yxqq" AS YXQQ,A."yxqz" AS YXQZ,1 AS JWQYLX,A."zzzsbh" AS ZZZSBH,D.ZXDWBH AS DWBH
FROM SgqyJbqkb@jgpt A INNER JOIN T_JW_JCZLDYGX D ON D.JWZZJGDM=A."ZZJGDM" AND A."ZZJGDM" IS NOT NULL AND A."yxqz" >= SYSDATE AND D.SFYX=0
AND EXISTS(SELECT 1 FROM T_JW_QYJCZL WHERE ZZJGDM=A.ZZJGDM)