select sum(njts)-sum(ysyts) from njsyqk where ygdh='888882' and ((yxbz is null) or (yxbz='1'))

select qjd_lsh,qjd_jqlx,qjd_sy,qjd_ygjs,jbxx_sszz from qjd q,ygjbxx y where q.qjd_ygbh=y.jbxx_bh and qjd_lsh=(select max(qjd_lsh) from qjd where qjd_ygbh='888882')

?

String sql = "select KS_LS_SFZH ,KS_LS_PASSWORD,KS_LS_NAME,KS_LS_SJ, KS_LS_EMAIL,decode(KS_LS_SEX,1,'男',2,'女') KS_LS_SEX,ks_ls_zcsj from ks_lsygb? where ks_lsygb.ks_ls_sfzh? in(select KS_LS_SFZH from ks_lsygb minus select ks_ksryb.ygdh? from ks_ksryb where 1=1 and length(ks_ksryb.ygdh)>6 and ks_ksryb.ksbh='"

???????????????????????????????????? + ksbh + "')? order by ks_ls_zcsj desc";

?

select KS_LS_SFZH ,KS_LS_PASSWORD,KS_LS_NAME,KS_LS_SJ, KS_LS_EMAIL, decode(KS_LS_SEX,1,' 男 ',2,' 女 ') KS_LS_SEX,ks_ls_zcsj

from ks_lsygb? where ks_lsygb.ks_ls_sfzh? in

(select KS_LS_SFZH from ks_lsygb

minus select ks_ksryb.ygdh? from ks_ksryb

where 1=1 and length(ks_ksryb.ygdh) >6 and ks_ksryb.ksbh='651')? order by ks_ls_zcsj desc

組合條件作為 OR 的一個(gè)選項(xiàng)：

and ( (yg.jbxx_name='"+cjtjPO.getXm()+"' and sjp.sjbp_ygdh=yg.jbxx_bh and sjp.sjbp_ygdh= ry.ygdh) or (ls.ks_ls_name='"+cjtjPO.getXm()+"' and sjp.sjbp_ygdh=ls.ks_ls_sfzh and sjp.sjbp_ygdh= ry.ygdh) )

?

select decode (length(Sjbp_Ygdh),6,(select jbxx_name? from ygjbxx where jbxx_bh=Sjbp_Ygdh),(select ks_ls_name from ks_lsygb where ks_ls_sfzh=Sjbp_Ygdh)) as name ,

decode(length(Sjbp_Ygdh),6,(select jbxx_bh? from ygjbxx where jbxx_bh=Sjbp_Ygdh),(select ks_ls_sfzh from ks_lsygb where ks_ls_sfzh=Sjbp_Ygdh)) as ygdh ,

decode ( length( Sjbp_Ygdh),6,(select zzjg_mc from zzjg where zzjg_bh =(select jbxx_sszz from ygjbxx where jbxx_bh = sjbp_ygdh)),(select zzjg_mc from zzjg where zzjg_bh =(select jbxx_sszz from ygjbxx where jbxx_sfzh? = sjbp_ygdh))) as sszz ,

sjbp_df , sjbp_kskssj , sjbp_dths from ks_sjbp where ((sjbp_jjsj is null) or (sjbp_pjbz is null)) and sjbp_ksbh ='1265'

?

zzjg_cc in ('1','2')

?

and substr (jbxx_sszz,1,8) = zzjg_bh

?

//循環(huán)獲得List中的某個(gè)PO

for ( int i = 0; i < bmrztjcxPOList1.size(); i++) { // 應(yīng)提交人次

BmRztjCxPO bmrztjcxPO1 = bmrztjcxPOList1. get(i) ;}

?

if (bmrztjcxPO1.getBmdh().trim().equals(

bmrztjcxPO2.getBmdh().trim())) {count2 = Integer. parseInt (bmrztjcxPO2.getAstjrcs());

break ;

???????????? }

?

bmrztjcxPO1.setAstjl(String. format ("%.2f", count2 * 100.00/ count1)+ "%"); // 按時(shí)提交率

?

if (!bmrztjcxPOList.isEmpty()) { // 有查詢記錄才計(jì)算合計(jì)值

?

rs.getDate("rztjb_gzrq").toString().equals(rs2.getDate("rzp_gzrq").toString())

?

SELECT zzjg_bh,zzjg_cc,

CASE WHEN zzjg_cc=3 THEN '|-'||(select zzjg_mc from zzjg a where a.zzjg_bh=z.zzjg_bh)

WHEN zzjg_cc=4 THEN '|--'||(select zzjg_mc from zzjg a where a.zzjg_bh=z.zzjg_bh)

ELSE zzjg_mc END case,'','true'

FROM zzjg z,ygjbxx where

( (substr(zzjg_bh,1,8) =substr(jbxx_sszz,1,8)) or (jbxx_sszz like '000302%' and zzjg_bh = '000302'))

and jbxx_bh= '888882' and zzjg_kybz='1' order by zzjg_bh

?

select * from zzjg? where length(zzjg_bh) =6? order by zzjg_bh asc

?

SELECT id,id,mc,'','true' from kp_jzqk order by 1 [c1] ?

?

select Xmbh,lxsj, (' &lt ; '||trim(Xmbh)||' &gt ;'||Xmmc) [c2] ?,'','true'

from kp_xmzd where Xmjl=? order by lxsj desc

//插入字段類型為date的數(shù)據(jù)列

pstmt.setDate(4, "".equals(xmzd.getLxsjStr()) ? null : java.sql.Date. valueOf (xmzd.getLxsjStr()));

to_char(qjd_lsh)

?

update lcyj set spyj = ?,yy = ?,spsj = sysdate where qjdh = ? and ygbh = ? and sid = ?");

?

pstmt = conn.prepareStatement("insert into gzjl_rzp(rzp_ygdh,rzp_bmdh,rzp_gzrq,rzp_tjsj) values (?,?,?, to_timestamp(?,'yyyy-mm-dd hh24:mi:ss.ff') )");

??????? for (String day : nday){

????????? pstmt.setString(1, ygdh);

????????? pstmt.setString(2, sszz);

????????? pstmt.setDate(3, java.sql.Date. valueOf (day) );

????????? pstmt.setString(4, (day+" 23:59:59") );

????????? pstmt.addBatch();

??????? }

select gzjl_lxb.gzlx_mc,sum(gzjl_rzd.rzd_gzl) as gzl from gzjl_rzd inner join gzjl_lxb on gzjl_rzd.rzd_gzlx = gzjl_lxb.gzlx_bh inner join gzjl_rzp on gzjl_rzd.rzd_rzxh = gzjl_rzp.rzp_rzxh where gzjl_rzp.rzp_gzrq >= to_date('"+ksrq+"','yyyy-mm-dd') and gzjl_rzp.rzp_gzrq <= to_date('"+jsrq+"','yyyy-mm-dd') and gzjl_rzd.rzd_xmbh = '"+xmbh+"' group by gzjl_lxb.gzlx_mc order by gzl desc

?

// 結(jié)束時(shí)間不為空時(shí)

if (!"".equals(hkxxglQO.getJssj()) && null != hkxxglQO.getJssj()) {

sql.append(" and ls.jhk_cjsj <=to_date('" + hkxxglQO.getJssj() + " 23:59:59','yyyy-mm-dd hh24:mi:ss') " );

?

year(to_date(‘2013-6-9’,'yyyy-mm-dd')//返回2013，同樣可以將year改為month、day

?

了解當(dāng)前各個(gè)用戶的名字及他們所進(jìn)行的操作時(shí)，要用下面命令才能得到詳細(xì)的結(jié)果

select sid,serial#,username,

DECODE (command,

0,’None’,

2,’Insert’,

3,’Select’,

6,’Update’,

7,’Delete’,

8,’Drop’,

‘Other’) cmmand

from v$session where username is not null;

?

1. 在使用Oracle的 to_date 函數(shù)來(lái)做日期轉(zhuǎn)換時(shí)，可能會(huì)直覺(jué)地采用“yyyy-MM-dd HH:mm:ss”的格式作為格式進(jìn)行轉(zhuǎn)換，但是在Oracle中會(huì)引起錯(cuò)誤：“ORA 01810 格式代碼出現(xiàn)兩次”。如：select to_date ('2005-01-01 13:14:20','yyyy-MM-dd HH24:mm:ss') from dual;原因是SQL中不區(qū)分大小寫，MM和mm被認(rèn)為是相同的格式代碼，所以O(shè)racle的SQL采用了mi代替分鐘。select to_date ('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;

2.另要以24小時(shí)的形式顯示出來(lái)要用HH24

select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;//mi是分鐘 /*mi 代表分鐘 2013-06-14 14:36:13*/

select to_char(sysdate,'yyyy-MM-dd HH24:mm:ss') from dual;//mm會(huì)顯示月份 /*mm 代表月份 2013-06-14 14:06:14*/

?

TO_DATE 格式(以時(shí)間:2007-11-02 13:45:25為例)

??????? Year:

??????? yy two digits 兩位年 顯示值:07

??????? yyy three digits 三位年 顯示值:007

??????? yyyy four digits 四位年 顯示值:2007

??????? Month:

??????? mm number 兩位月 顯示值:11

??????? mon abbreviated 字符集表示 顯示值:11月,若是英文版,顯示nov

??????? month spelled out 字符集表示 顯示值:11月,若是英文版,顯示november

??????? Day:

??????? dd number 當(dāng)月第幾天 顯示值:02

??????? ddd number 當(dāng)年第幾天 顯示值:02

??????? dy abbreviated 當(dāng)周第幾天簡(jiǎn)寫 顯示值:星期五,若是英文版,顯示fri

??????? day spelled out 當(dāng)周第幾天全寫 顯示值:星期五,若是英文版,顯示friday

??????? ddspth spelled out, ordinal twelfth

? ????????????Hour:

????????????? hh two digits 12小時(shí)進(jìn)制 顯示值:01

????????????? hh24 two digits 24小時(shí)進(jìn)制 顯示值:13

????????????? Minute:

????????????? mi two digits 60進(jìn)制 顯示值:45

????????????? Second:

????????????? ss two digits 60進(jìn)制 顯示值:25

????????????? 其它

?????????? ???Q digit 季度 顯示值:4

????????????? WW digit 當(dāng)年第幾周 顯示值:44

????????????? W digit 當(dāng)月第幾周 顯示值:1

??????? 24小時(shí)格式下時(shí)間范圍為： 0:00:00 - 23:59:59....

??????? 12小時(shí)格式下時(shí)間范圍為： 1:00:00 - 12:59:59 ....

select floor(sysdate-to_date('20120614','yyyymmdd')) from dual /* 兩個(gè)日期之間的天數(shù) */

select sysdate 現(xiàn)在日期,add_months(sysdate,1) 一個(gè)月后的今天 from dual

select to_date(sysdate,'yyyy-mm-dd'), add_months(to_date(sysdate,'yyyy-mm-dd'),1) from dual

select add_months(to_date('2000-01-31','YYYY-MM-DD'),1) from dual /*2000-2-29*/

select sysdate from dual

select to_char(6666,'$9,999.99') from dual /* $6,666.00*/

select to_date('2013-11-01 19:25:34','YYYY-MM-DD HH24:MI:SS') from dual

select to_date ('2005-01-01 13:14:2','yyyy-MM-dd HH24:mi:ss') from dual /*2005-1-1 13:14:02*/

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual /*mi 代表分鐘 2013-06-14 14:36:13*/

select to_char(sysdate,'yyyy-mm-dd hh24:mm:ss') from dual /*mm 代表月份 2013-06-14 14:06:14*/

select to_char(sysdate,'yy-mm-dd') from dual /*13-06-14*/

select to_char(sysdate,'yyyy-mon-dd-day') from dual /*2013-6 月 -14- 星期五 */

select to_char(sysdate,'yyyy-month-dd-day') from dual /*2013-6 月 -14- 星期五 */

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual /*2013-06-14 17:01:09*/

select to_char(sysdate,'yyyy') from dual /*2013*/

select to_char(to_date('2013-06-14','yyyy-mm-dd'),'day') from dual /* 星期五 */

select floor(sysdate-to_date('20120614','yyyymmdd')) from dual /* 兩個(gè)日期之間的天數(shù) */

select floor(sysdate-to_date('2012-06-14','yyyy-mm-dd')) from dual /* 兩個(gè)日期之間的天數(shù) */

?

// 主鍵沖突

java.sql.SQLException: 1

//外鍵沖突

java.sql.SQLException: 2292

?

Select A.* from A,B where A.city = b.city

?

values(SEQ_XS_THD.nextval,//實(shí)現(xiàn)數(shù)據(jù)【主鍵】自增

?

//

?

游標(biāo)為應(yīng)用程序提供了一種對(duì)具有多行數(shù)據(jù)的查詢結(jié)果集中每行數(shù)據(jù)進(jìn)行單獨(dú)處理的方法，是設(shè)計(jì)交互式應(yīng)用程序的常用編程接口

declare

??? v_age stu.age%type;

??? v_total_age stu.age%type;

??? courso cur_var is select age from stu;--創(chuàng)建游標(biāo)begin

??? v_total_age :=0;

??? open cur_var;--打開游標(biāo)

??? fetche cur_var into v_age;

??? while cur_var%found loop--遍歷游標(biāo)

??????? v_total_age :=v_total_age+v_age;

??????? fetch cur_var into v_age;

??? end loop;

??? close cur_var;end;

通過(guò)上面的例子，下面簡(jiǎn)單介紹游標(biāo)的屬性：

（1）%FOUND – SQL 語(yǔ)句影響了一行或多行時(shí)為 TRUE,即游標(biāo)找到數(shù)據(jù)就返回TRUE,反之則返回FALSE

（2）%NOTFOUND – SQL 語(yǔ)句沒(méi)有影響任何行時(shí)為TRUE,即游標(biāo)沒(méi)有找到數(shù)據(jù)就返回TRUE,反之則返回FALSE

（3）%ROWCOUNT – SQL 語(yǔ)句影響的行數(shù),即返回游標(biāo)所找到的數(shù)據(jù)行數(shù)(返回一個(gè)整數(shù))

（4）%ISOPEN? - 游標(biāo)是否打開，即游標(biāo)打開為TRUE,反之則為FALSE

使用顯式游標(biāo)更新行：

允許使用游標(biāo)刪除或更新活動(dòng)集中的行

聲明游標(biāo)時(shí)必須使用 SELECT … FOR UPDATE語(yǔ)句

??? CURSOR <cursor_name> IS

?? SELECT statement FOR UPDATE;

?UPDATE <table_name>

?SET <set_clause>

?WHERE CURRENT OF <cursor_name>例子：

DECLARE

? new_price NUMBER;

? CURSOR cur_toy IS

??? SELECT toyprice FROM toys WHERE toyprice<100

??? FOR UPDATE OF toyprice;

BEGIN

? OPEN cur_toy;

? LOOP

??? FETCH cur_toy INTO new_price;

??? EXIT WHEN cur_toy%NOTFOUND;

??? UPDATE toys

??? SET toyprice = 1.1*new_price

??? WHERE CURRENT OF cur_toy;

? END LOOP;

? CLOSE cur_toy;

? COMMIT;

END;

?

// 函數(shù)

/********************* 創(chuàng)建函數(shù)********************/

/*day 函數(shù)*/

create or replace function day(date1 date)

return integer is

?begin

? return to_number(to_char(date1,'dd'));

end day;

/* 月份*/

create or replace function month(date1 date)

return integer is

?begin

? return to_number(to_char(date1,'mm'));

end month;

/* 年*/

create or replace function year(date1 date)

return integer is

?begin

? return to_number(to_char(date1,'yyyy'));

end year;

/**/

create or replace function weekday(date1 date)

return integer is

begin

? return to_number(to_char(date1,'D'))-1;

end weekday;

/*Oracle 自定義函數(shù)*/

/* 語(yǔ)法如下*/

create or replace function function_name(argu1 [model] datatype1,agru2 [mode2] datatype2)

return datatype is

begin

?

end;

/* 執(zhí)行*/

var v1 varchar2(100)

exec :v1:=function_name

/*===== 不帶任何參數(shù)====*/

create or replace function get_user

return varchar2 is Result varchar2(50);

begin

? select username into Result from user_users;

? return (Result);

end get_user;

?

?

/*====== 帶in參數(shù)的=====*/

create or replace function get_sal(empname in varchar2)

return number is

Result number;

begin

? select sal into Result from emp where ename=empname;

? return (Result);

end get_sal;

/* 執(zhí)行：*/

SQL>var sal number

SQL>exec :sal:=get_sal('scott');

?

/* 帶out參數(shù)的函數(shù)*/

create or replace function get_info(e_name varchar2,job out varchar2)

return number is

Result number;

begin

? select sal,job into Result,job from emp where ename=e_name;

? return (Result);

end get_info;

/* 執(zhí)行*/

SQL>var job varcha2(20);

SQL>var dname varchar2(20)

SQL>exec :dname:=get_info('scott',:job)

?

/*===== 帶in out 參數(shù)的函數(shù)=====*/

create or replace function result(num1 number,num2 in out number)

return number is

v_result number(6);

v_remainder number;

begin

? v_reuslt:=num1/num2;

? v_remainder:=mod(num1,num2);

? num2:=v_remainder;

? return (v_result);

Exception when zero_divide then

? raise_application_error(-20000,' 不能除0');

end result;

/* 執(zhí)行*/

? var result1 number;

? var result2 number;

? exec :result2:=30

? exec :result1:=result(100,:result2) eg:

?

/* 帶有IN參數(shù)的函*/

create or replace function get_empname(v_id in number)

return varchar2 is v_name varchar2(50);

begin

? select name into v_name from employee where id=v_id;

? reutrn v_name;

? ?exception

??? when no_data_found then

????? raise_application_error(-20001,' 你輸入的ID無(wú)效！');

end get_empname;

?

/*1 、一個(gè)最簡(jiǎn)單的自定義函數(shù)Fun_test1的定義*/

create or replace function Fun_test1(p_1 number) /*number 型輸入?yún)?shù)p_1*/

return number is /* 返回值也是number型*/

begin

? if p_1>0 then return 1;

? elsif p_1=0 then return 0;

? else return -1;

? end if;

end;

?

/*2 、Fun_test1 自定義函數(shù)的調(diào)用的存儲(chǔ)過(guò)程Pro_Fun_test1_1示例:一個(gè)輸入?yún)?shù)，一個(gè)輸出參數(shù)*/

create or replace procedure Pro_Fun_test1_1(p1_in number,p2_out out number)

as begin p2_out:=Fun_test1(p1_in);

end Pro_Fun_test1_1;

?

/*3 、Fun_test1自定義函數(shù)的調(diào)用的存儲(chǔ)過(guò)程Pro_Fun_test1_2示例*/

create or replace procedure Pro_Fun_test1_2(p1_in in number,p2_out out number)

as t_1 number;

begin

? select Fun_test1(p1_in)+100 into p2_out from bill_org where org_ID=1;

end Pro_Fun_test1_2;

?

//當(dāng)查詢同一表的同一列查詢兩次時(shí)，按查詢結(jié)果的第一列排序則可以按一下方法，否則會(huì)報(bào)列名不確定

SELECT zqmx_bh,zqmx_bh,zqmx_mc FROM xmgl_smzqmx order by 1

// 一次向表中插入多條記錄

insert into fy_jhk_lcyj(Jhk_jhkh,Jhk_lcsx,Jhk_spjs,Jhk_spr,Jhk_splx,Jhk_spyj,Jhk_spsj) select '110','0','05','888882','0','',sysdate? from dual union all select '110','1','02','','','',null? from dual

?

//返回年 如‘2013’

select year(sysdate) from dual

//返回年組：如今年為2013則以下SQL返回：2010、2011、2012、2013、2014

select year,year,year from

(

select to_number(to_char(sysdate,'yyyy')) year from dual

union

select to_number(to_char(sysdate,'yyyy'))-rownum year from dual connect by level<=3

union

select to_number(to_char(sysdate,'yyyy'))+rownum year from dual connect by level<=1

)

?

select jcfl_bh,jcfl_mc,('&lt'||trim(jcfl_bh)||'&gt'||jcfl_mc) from xm_jcflzd

// 獲取查詢結(jié)果中指定行的數(shù)據(jù)

select ztbg_id from (select rownum r,ztbg_id from xm_ztbg? where ztbg_xmid='3840' order by ztbg_id desc) where r=2 //獲取第二行的數(shù)據(jù)

?

select substr(sszzbh,2,4) from xs_ryjg//從第二位開始截取四位，2并非索引

substr(sszzbh,0,4)和substr(sszzbh,1,4)是相同的都是從第一位開始截取4位

?

刪除表空間及其中的內(nèi)容：

drop tablespace xxx including contents and datafiles;

查詢空表，避免因無(wú)數(shù)據(jù)造成空表無(wú)法導(dǎo)出的問(wèn)題：（將查詢結(jié)果導(dǎo)出到Excel 然后賦值出來(lái)運(yùn)行：）

select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0

?

導(dǎo)入 dmp 命令：

imp xxfbgswl/xxfbgswl@zbx25 ? file=e:\gswl20130424.dmp? fromuser=gswl touser=xxfbgswl log=e:xxfblog.log? rows=y

導(dǎo)出 dmp 命令：【非本地?cái)?shù)據(jù)庫(kù)在 @ 后添加 ip/ 】

exp gswleq/gswleq@dlzbx file=D:\gswl0619.dmp log=gswl.log owner=(gswleq) rows=y

定時(shí)備份：

e:

cd app\cdns\product\11.2.0\dbhome_1\BIN

EXP ds/ds@cvdns171 file=e:\dnsbackup\%date:~0,10%.dmp log=e:\dnsbackup\%date:~0,10%.log compress=n buffer=8092 consistent=y direct=n constraints=y feedback=10000 grants=y record=y indexes=y triggers=y rows=y

項(xiàng)目積累——SQL積累