=======================================================================
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)
分享到:
相关推荐
数据库常用名词解释数据库常用名词解释
常用化工数据库常用化工数据库
常用内存数据库介绍常用内存数据库介绍
SQL包含数据库常用的游标、存储过程等方法。内有各种实列
此压缩包是中是存放各oracle 常用问题及解决方法。每个方法用记事本记录,Oracle 数据库 触发器创建、oracle日期函数大全、oracle续列号、数据库进程查询、数据删除恢复,sql 取整、取余、等一系列解决方法。
常用数据库对比 目前,常用于OLAP分析的数据库软件有Oracle、DB2、Teradata、Greenplum、Netezza、Exdata几种。 Oracle 提起数据库,第一个想到的公司,一般都会是Oracle(甲骨文)。该公司成立于1977年,最初是一家...
数据库课件,包括ppt,一些常用oracle数据库知识,希望能够对大家就有帮助。
数据库常用名词解释.doc
西农数据库复习资料,包括常用SQL语句和基本概念,西农往届数据库试题
gp数据库日常运维sql语句笔记 数据库运维.pdf
Informix数据库常用命令介绍
ACCESS数据库常用SQL语句
SQL Server数据库常用操作,非常有用的资料。对初学者非常有用。
Oracle数据库常用数据类型.doc
人事动态及费用资料表.doc 人事管理制度样例.doc 人事考核制度.doc 人事考评表.doc 人事流动月报表.doc 人事日报表.doc 人事资料表.doc 人事资料记录表.doc 人事资料卡.doc 人事作业程序.doc 人员出勤表....
常用中文数据库检索常用中文数据库检索
常用全文数据库检索常用全文数据库检索
MySQL基础资料,sql常用的查询语句以及常用的sql命令。
常用化学化工互联网数据库简介资料.pdf