国产九区一区在线,国产一区二区精品福利地址,狠狠做深爱婷婷久久综合一区

JAVA公式解析�C�Z��

zhaonp — Wed, 02 Apr 2014 09:07:00 GMT

// 数值型
Expression exp = new Expression("(x + y)/2");
Map variables = new HashMap();
variables.put("x", new BigDecimal("4.32"));
variables.put("y", new BigDecimal("342.1"));
BigDecimal result = exp.eval(variables);
System.out.println(result);
// 布尔�?br />Expression ww = new Expression("( A && B ) || C ");
Map vs = new HashMap();
vs.put("A", new BigDecimal("0"));
vs.put("B", new BigDecimal("1"));
vs.put("C", new BigDecimal("1"));
System.out.println(ww.eval(vs));
// 布尔�?
Expression dd = new Expression(" A > 200 ");
Map as = new HashMap();
as.put("A", new BigDecimal("12000"));
System.out.println(dd.eval(as));

需要eval.jar

zhaonp 2014-04-02 17:07 发表评论

zhaonp — Sat, 01 Jan 2011 04:50:00 GMT

操作环境

服务端Oracle 11g

客户端Oracle 10g

--准备脚本

DROP TABLE EMPLOYEE_SALARY_T;
CREATE TABLE EMPLOYEE_SALARY_T(
EMPLOYEE_ID         VARCHAR2(20),
EMPLOYEE_NAME       VARCHAR2(20),
DEPARTMENT          VARCHAR2(10),
COMPANY             VARCHAR2(10),
SALARY              NUMBER(6),
SALARY_YEAR         VARCHAR2(10)
);

INSERT INTO EMPLOYEE_SALARY_T VALUES('001','znp','dep001','VisSoft',30000,'2005');
INSERT INTO EMPLOYEE_SALARY_T VALUES('001','znp','dep001','VisSoft',40000,'2006');
INSERT INTO EMPLOYEE_SALARY_T VALUES('001','znp','dep001','VisSoft',50000,'2007');
INSERT INTO EMPLOYEE_SALARY_T VALUES('001','znp','dep001','VisSoft',60000,'2008');

INSERT INTO EMPLOYEE_SALARY_T VALUES('002','zsj','dep001','AvsSoft',3000,'2005');
INSERT INTO EMPLOYEE_SALARY_T VALUES('002','zsj','dep001','AvsSoft',4000,'2006');
INSERT INTO EMPLOYEE_SALARY_T VALUES('002','zsj','dep001','AvsSoft',5000,'2007');
INSERT INTO EMPLOYEE_SALARY_T VALUES('002','zsj','dep001','AvsSoft',6000,'2008');

INSERT INTO EMPLOYEE_SALARY_T VALUES('003','ts1','dep002','VisSoft',30000,'2005');
INSERT INTO EMPLOYEE_SALARY_T VALUES('003','ts1','dep002','VisSoft',40000,'2006');
INSERT INTO EMPLOYEE_SALARY_T VALUES('003','ts1','dep002','VisSoft',50000,'2007');
INSERT INTO EMPLOYEE_SALARY_T VALUES('003','ts1','dep002','VisSoft',60000,'2008');

INSERT INTO EMPLOYEE_SALARY_T VALUES('004','ts2','dep002','AvsSoft',3000,'2005');
INSERT INTO EMPLOYEE_SALARY_T VALUES('004','ts2','dep002','AvsSoft',4000,'2006');
INSERT INTO EMPLOYEE_SALARY_T VALUES('004','ts2','dep002','AvsSoft',5000,'2007');
INSERT INTO EMPLOYEE_SALARY_T VALUES('004','ts2','dep002','AvsSoft',6000,'2008');

COMMIT;

--行��查询实现脚本
--查询含义为将09�q�dep001部门的salary预定��部门05�q��?6�q�与08�q�的��d��
--而dep002部门09�q�的salary预定��部门06�q��?7�q�与08�q�的��d��

SELECT COMPANY,DEPARTMENT,SALARYS FROM EMPLOYEE_SALARY_T
MODEL
     RETURN UPDATED ROWS        --是否昄��基本查询�?如果有该行则不显�C�基本查询行�Q�只昄��查询计算�l�果�?br />      PARTITION BY (COMPANY)                   --分区�Q�对行计��结果的分区�Q�本例中以company列做为分区项
     DIMENSION BY (DEPARTMENT,SALARY_YEAR )   --军_��两个计算的维度，当前表示�W�一�l�度为部门，�W�二�l�度��资年�?br />      MEASURES (SALARY SALARYS)                --我的理解是规则指向列�Q�即对哪一列进行规则计��或规则改变
     RULES (
SALARYS['dep001', '2009'] = SALARYS['dep001', '2005'] + SALARYS['dep001', '2006']+ SALARYS['dep001', '2008'],
SALARYS['dep002', '2009'] = SALARYS['dep002', '2006'] + SALARYS['dep002', '2007']+ SALARYS['dep002', '2008']
);

��x��如果用列�U�查询时实现的效果（^_^ 是不是比较地不麻烦）

PS:把COMPANY的��g��DEPARTMENT的��D��成同步，即DEPARTMENT�?01时公司全部�ؓAvsSoft,DEPARTMENT�?02时公司全部�ؓVisSoft。看看结果如何？

元旦快乐

zhaonp 2011-01-01 12:50 发表评论

关于自治事务

zhaonp — Fri, 31 Dec 2010 04:00:00 GMT

操作环境 oracle11g

CREATE TABLE EMPLOYEE_T
(
EMPLOYEE_ID VARCHAR2(20),
EMPLOYEE_NAME VARCHAR2(20)
);

在存储过�E�中�Q�尤其是一�l�相互调用的存储�q�程中如果要为其中的每个存储�q�程记录执行日志时会存在比较�ȝ��的问题。即在操作出现异常时如何记录相关异常日志(�q�个时候的日志应该才是最重要的吧>_
自治事务相当于与当前事务�q�行的另一个事务，其提交与否�ƈ不媄响当前主要事务的提交与回滚，通常定义在函��C��存储�q�程之中方式如下

CREATE OR REPLACE PROCEDURE SP_EMPLOYEE_AUTONOMOUS
IS

     PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

        INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('007','TS5');
        INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('008','TS6');

        COMMIT;

END SP_EMPLOYEE_AUTONOMOUS;

配合实现方式�?br />

CREATE OR REPLACE PROCEDURE SP_EMPLOYEE
IS

BEGIN

    INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('003','TS1');
    INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('004','TS2');
    INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('005','TS3');
    INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('006','TS4');

    SP_EMPLOYEE_AUTONOMOUS();

    INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('009','TS7');
    INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('0010','TS8');

    ROLLBACK;

END SP_EMPLOYEE;

则执行的�l�果则只插入007�?08两条记录

PS:一�U�错误的实现方式�Q�如�?br />

CREATE OR REPLACE PROCEDURE SP_EMPLOYEE_AUTONOMOUS
IS

BEGIN

INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('001','TS1');
INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('002','TS2');

        PRAGMA AUTONOMOUS_TRANSACTION;

        INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('007','TS5');
        INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('008','TS6');

        COMMIT;

        INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('003','TS7');
        INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME)VALUES('004','TS8');

        ROLLBACK;


END SP_EMPLOYEE_AUTONOMOUS;

此时�~�译与执行SP_EMPLOYEE_AUTONOMOUS且不报错�Q�但是执行的�l�果��~~~~~~~~~~~~~~~~~ ^_^

zhaonp 2010-12-31 12:00 发表评论

zhaonp — Fri, 31 Dec 2010 01:51:00 GMT

执行环境 Oracle11g

动态SQL是存储过�E�及函数中常用的实现查询操作手段�Q�以下记录几�U�常用�Ş式以供自�?br />
CREATE TABLE EMPLOYEE_T(
EMPLOYEE_ID VARCHAR2(20) NOT NULL,
EMPLOYEE_NAME VARCHAR2(20)
);

1、直接执行动态SQL
CREATE OR REPLACE PROCEDURE SP_DEAL
IS

vs_sql VARCHAR2(4000);

BEGIN

    vs_sql := 'UPDATE EMPLOYEE_T SET EMPLOYEE_NAME='||CHR(39)||'zsj'||CHR(39)||' WHERE EMPLOYEE_ID = 001';

    EXECUTE IMMEDIATE vs_sql;

    COMMIT;

EXCEPTION

    WHEN OTHERS THEN

        ROLLBACK;

end SP_DEAL;

2、执行有输出�l�果的动态SQL
CREATE OR REPLACE PROCEDURE SP_DEAL_OUT
IS

vn_num NUMBER(2);
vs_sql VARCHAR2(4000);

BEGIN

    vs_sql := 'SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEE_T';

    EXECUTE IMMEDIATE vs_sql INTO vn_num;

EXCEPTION

    WHEN OTHERS THEN

        ROLLBACK;

end SP_DEAL_OUT;

3、执行有输入参数与输出结果的动态SQL --select时��?br /> CREATE OR REPLACE PROCEDURE SP_DEAL_IN_OUT
IS

vn_num                    NUMBER(2);
vs_sql                    VARCHAR2(4000);
vs_employee_id            EMPLOYEE_T.EMPLOYEE_ID%TYPE;
vs_employee_name          EMPLOYEE_T.EMPLOYEE_NAME%TYPE;
vs_out_employee_id        EMPLOYEE_T.EMPLOYEE_ID%TYPE;
vs_msg                    VARCHAR2(4000);

BEGIN

    vs_employee_id := '001';

    vs_employee_name := 'znp';

    vs_sql := 'INSERT INTO EMPLOYEE_T(EMPLOYEE_ID,EMPLOYEE_NAME) VALUES (:1,:2) RETURNING EMPLOYEE_ID INTO :3';

    EXECUTE IMMEDIATE vs_sql USING vs_employee_id,vs_employee_name RETURN INTO vs_out_employee_id ;

    COMMIT;

EXCEPTION

    WHEN OTHERS THEN

        vs_msg :=SQLERRM;

        ROLLBACK;

end SP_DEAL_IN_OUT;

1、通过动态SQL直接提取查询�l�果,�q�回查询�l�果�?/p>

CREATE OR REPLACE PROCEDURE SP_EMPLOYEE(
cur OUT SYS_REFCURSOR
)
IS

vs_sql VARCHAR2(4000);

BEGIN

    vs_sql := 'SELECT EMPLOYEE_ID,EMPLOYEE_NAME FROM EMPLOYEE_T ';

    OPEN cur FOR vs_sql;

end SP_EMPLOYEE;

2、通过动态SQL提取查询�l�果�?通过昑ּ�游标方式�q�行处理
CREATE OR REPLACE PROCEDURE SP_EMPLOYEE
IS

   vs_sql                    VARCHAR2(4000);
   vs_id                     EMPLOYEE_T.EMPLOYEE_ID%TYPE;
   vs_name                   EMPLOYEE_T.EMPLOYEE_NAME%TYPE;

   TYPE cur_cursor IS REF CURSOR;
   cur            cur_cursor;

BEGIN

    vs_sql := 'SELECT EMPLOYEE_ID,EMPLOYEE_NAME FROM EMPLOYEE_T ';

    OPEN cur FOR vs_sql;

    LOOP

        FETCH cur INTO vs_id,vs_name;

            UPDATE EMPLOYEE_T
                   SET EMPLOYEE_NAME = vs_name
                   WHERE EMPLOYEE_ID = vs_id;

            EXIT WHEN cur%NOTFOUND;

    END LOOP;

    CLOSE cur;

    COMMIT;

EXCEPTION

    WHEN OTHERS THEN

        ROLLBACK;

end SP_EMPLOYEE;

3、通过动态SQL直接调用存储�q�程
CREATE OR REPLACE PROCEDURE SP_DEAL(
is_name VARCHAR2
)
IS

vs_sql VARCHAR2(1000); --动态SQL描述

BEGIN

    --拼接动态调用哪一个存储过�E?指定三个调用形参)
    vs_sql := 'BEGIN SP_'||is_name||'_BACKUP(:V1,:V2,:V3,:v4); END;';

    --执行动态SQL,同时指定一个入参与两个出差
    EXECUTE IMMEDIATE vs_sql USING IN is_id, IN is_para,OUT on_flag,OUT os_msg;

EXCEPTION

    WHEN OTHERS THEN

        on_flag := -1;

        os_msg := 'SP_'||is_name||':'||SUBSTR(SQLERRM,0,200);

END SP_DOUBT_BACKUP;

4、�ؓ动态SQL指定入参

zhaonp 2010-12-31 09:51 发表评论

解析IN函数使用的字�W�串

zhaonp — Thu, 30 Dec 2010 08:12:00 GMT

操作环境
Server Oracle 11g
Client Oracle 10g

vs_string 输入参数格式�?info1,info22,info333,info4444',�?,"做间隔符,每变量长度不��定

vn_num vs_string 中合�q�拼接的变量数量

SELECT SUBSTR(vs_string,
              DECODE(LEVEL,1,0,(INSTR(vs_string,',',1,LEVEL-1)))+1,
              DECODE(LEVEL,vn_num,LENGTH(vs_string),INSTR(vs_string,',',1,DECODE(LEVEL,1,1,LEVEL))-1)-
              (DECODE(LEVEL,1,0,(INSTR(vs_string,',',1,LEVEL-1)))+1)+1)
              FROM DUAL
              CONNECT BY LEVEL <= (SELECT REGEXP_COUNT(vs_string,',',1,'i')+1 FROM DUAL);

在oracle �?IN 函数只能�Ҏ��询的�l�果集或明确的参数集合进行多行查询，对于一个参数无论其表现形式只能�q�行一个参数的判断查询

�?/p>

EMPLOYEE_T 表中存在如下数据

EMPLOYEE_ID        EMPLOYEE_NAME
--------------------------------
001                 znp
002                 zsj
003                 zhsj

SELECT EMPLOYEE_ID,EMPLOYEE_NAME FROM EMPLOYEE_T WHERE EMPLOYEE_ID IN ('001','002')的查询结果如�?/p>

EMPLOYEE_ID        EMPLOYEE_NAME
--------------------------------
001                 znp
002                 zsj

SELECT EMPLOYEE_ID,EMPLOYEE_NAME FROM EMPLOYEE_T WHERE EMPLOYEE_ID IN ('001,002')的查询结果如�?/p>

EMPLOYEE_ID EMPLOYEE_NAME
--------------------------------

�?SELECT * FROM EMPLOYEE_T WHERE EMPLOYEE_ID IN ('001,002') �?"'001,002'" 只能做�ؓ一个入参而不是两个入�?参数��gؓ
'001,002' ,故查不到合适的记录

在存储过�E�中对于以上的入参可以采取两�U�办法进�?/p>

1、通过拼接动态SQL�q�行查询

vs_sql := 'SELECT EMPLOYEE_ID,EMPLOYEE_NAME FROM EMPLOYEE_T WHERE EMPLOYEE_ID IN ('||CHR(39)||'001'||CHR(39)||','||CHR(39)||'002'||CHR(39)||');

OPEN _cur FOR vs_sql;

2、将拼接串�{为结果集输出至IN函数�?使用如下脚本

vs_string 输入参数格式�?info1,info22,info333,info4444',�?,"做间隔符,每变量长度不��定

vn_num vs_string 中合�q�拼接的变量数量

SELECT EMPLOYEE_ID,EMPLOYEE_NAME FROM EMPLOYEE_T
                                 WHERE EMPLOYEE_ID IN
                                 (
                                      SELECT SUBSTR(vs_string,
                                             DECODE(LEVEL,1,0,(INSTR(vs_string,',',1,LEVEL-1)))+1,
                                             DECODE(LEVEL,vn_num,LENGTH(vs_string),INSTR(vs_string,',',1,DECODE(LEVEL,1,1,LEVEL))-1)-
                                             (DECODE(LEVEL,1,0,(INSTR(vs_string,',',1,LEVEL-1)))+1)+1)
                                             FROM DUAL
                                             CONNECT BY LEVEL <= (SELECT REGEXP_COUNT(vs_string,',',1,'i')+1 FROM DUAL)
                                 );

zhaonp 2010-12-30 16:12 发表评论

Oracle sqllder 数据导入

zhaonp — Fri, 24 Dec 2010 01:49:00 GMT

操作环境

客户�?Oracle 10g

服务�?Oracle 11g

1、数据准�?br />
1.1、在sqlplus下将相关表将相关待导入数据导��Zؓtxt文�g

--sqlplus脚本
Set linesize 3000    --每行�?000
Set pagesize 0       --指定不分��?br /> Set heading off      --不输出标题行
Set feedback off     --不输出反馈信�?br /> Set echo off         --不显�C�命令本�w?br /> Set termout off      --不显�C�终端信�?br /> Set trimout On       --截断�l�端昄��I�格
Set trimspool On     --截断输出��x��志空�?br /> spool c:\a.txt       --指定屏幕输出到相��x��?br /> spool off            --关闭指定输出

以上脚本控制sqlplus控制台输出打印效�? 同时指定屏幕的打印输出将转录入到何文件中

实际脚本

sqlplus zhsj/zhsj@zhsj
Set linesize 3000
Set pagesize 0
Set heading off
Set feedback off
Set echo off
Set termout off
Set trimout On
Set trimspool On
spool c:\a.txt
select employee_id||'|'||employee_name||'|'||employee_salary from t_employee;
spool off

以上会将t_employee表中�?nbsp;employee_id、employee_name、employee_salary 三列数据以如下�Ş式写入c:\a.txt 文�g�?br />
1|znp|80000.00
2|zhsj|40000.00
3|xxx|30000.00

数据准备完成后编写sqlldr要用到的ctl文�g�Q�用于将txt文�g中的数据导入到数据库中，脚本如下 �Q?-部分实际脚本中须删险)

-- a.ctl
Load data   --加蝲数据
infile a.txt    --加蝲数据文�g名称(现在是相对�\�?可以写成�l�对路径c:\bak.txt)
badfile a.bad                   --��p�|数据写入文�g
append                     -- append �q�加 truncate truncate delete 删除
into table t_employee_bak    --插入操作�?br /> FIELDS TERMINATED BY '|'                 --�Ҏ��指定标识�W�隔断各字段�?OPTIONALLY ENCLOSED BY '"' �q��o掉指定的标识�W?�?�{?br /> (employee_id,                                 --指定插入字段及数�?br /> employee_name,
employee_salary)

准备完成后，在cmd下输�?br />
sqlldr -userid zhsj/zhsj@zhsj control=c:\a.ctl

执行�Q�在t_employee_bak表中可以见到a.txt中的数据已导�?br />
在执行过�E�如果出现数据没有导入的情况�Q�可能原因会有以下几�U�情�?br />
1、在导出的数据文件中存在同时打印输出的命令行�Q�将命��o行删除后重新保存文�g卛_��数据正常插入到数据库中
2、虽然文本中已是正确的数据但仍然无法导入,此时在执行的SQL脚本中增加一个间隔符的输出，�?br />
select employee_id||'|'||employee_name||'|'||employee_salary from t_employee;

改�ؓ

select employee_id||'|'||employee_name||'|'||employee_salary||'|' from t_employee;

此时��会正常导入�Q�这是因为列值存在空数据��D��间隔计算问题

以上为txt格式数据文�g导出

对于异质数据库，可以采用sqldeveloper�{�工具将相关数据库中数据以csv格式导出�Q�此时在�~�写执行脚本旉��变更如下

-- a.ctl
Load data   --加蝲数据
infile a.txt    --加蝲数据文�g名称(现在是相对�\�?可以写成�l�对路径c:\bak.txt)
badfile a.bad                   --��p�|数据写入文�g
append                           -- append �q�加 truncate truncate delete 删除
into table t_employee_bak    --插入操作�?br /> FIELDS TERMINATED BY ','   OPTIONALLY ENCLOSED BY '"'   --�Ҏ��指定标识�W�隔断各字段�?OPTIONALLY ENCLOSED BY '"' �q��o掉指定的标识�W?�?�{?br /> (employee_id,                                 --指定插入字段及数�?br /> employee_name,
employee_salary)

因�ؓcsv格式文�g在用txt打开可以看到数据文�g格式�?1","znp","80000.00"�Q�需要将' " '标识�W�去掉，才可以正常导�?br />
大数据量时csv格式文�g会比txt格式文�g�?倍左叻I��且导入时可能出现问题�Q�所以尽量采用txt文�g方式导入

zhaonp 2010-12-24 09:49 发表评论