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 的一個選項：

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

?

//循環獲得List中的某個PO

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

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)+ "%"); // 按時提交率

?

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

?

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的數據列

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

?

// 結束時間不為空時

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

?

了解當前各個用戶的名字及他們所進行的操作時，要用下面命令才能得到詳細的結果

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 函數來做日期轉換時，可能會直覺地采用“yyyy-MM-dd HH:mm:ss”的格式作為格式進行轉換，但是在Oracle中會引起錯誤：“ORA 01810 格式代碼出現兩次”。如：select to_date ('2005-01-01 13:14:20','yyyy-MM-dd HH24:mm:ss') from dual;原因是SQL中不區分大小寫，MM和mm被認為是相同的格式代碼，所以Oracle的SQL采用了mi代替分鐘。select to_date ('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;

2.另要以24小時的形式顯示出來要用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會顯示月份 /*mm 代表月份 2013-06-14 14:06:14*/

?

TO_DATE 格式(以時間: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 當月第幾天 顯示值:02

??????? ddd number 當年第幾天 顯示值:02

??????? dy abbreviated 當周第幾天簡寫 顯示值:星期五,若是英文版,顯示fri

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

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

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

????????????? hh two digits 12小時進制 顯示值:01

????????????? hh24 two digits 24小時進制 顯示值:13

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

????????????? mi two digits 60進制 顯示值:45

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

????????????? ss two digits 60進制 顯示值:25

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

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

????????????? WW digit 當年第幾周 顯示值:44

????????????? W digit 當月第幾周 顯示值:1

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

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

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

select sysdate 現在日期,add_months(sysdate,1) 一個月后的今天 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 /* 兩個日期之間的天數 */

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

?

// 主鍵沖突

java.sql.SQLException: 1

//外鍵沖突

java.sql.SQLException: 2292

?

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

?

values(SEQ_XS_THD.nextval,//實現數據【主鍵】自增

?

//

?

游標為應用程序提供了一種對具有多行數據的查詢結果集中每行數據進行單獨處理的方法，是設計交互式應用程序的常用編程接口

declare

??? v_age stu.age%type;

??? v_total_age stu.age%type;

??? courso cur_var is select age from stu;--創建游標begin

??? v_total_age :=0;

??? open cur_var;--打開游標

??? fetche cur_var into v_age;

??? while cur_var%found loop--遍歷游標

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

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

??? end loop;

??? close cur_var;end;

通過上面的例子，下面簡單介紹游標的屬性：

（1）%FOUND – SQL 語句影響了一行或多行時為 TRUE,即游標找到數據就返回TRUE,反之則返回FALSE

（2）%NOTFOUND – SQL 語句沒有影響任何行時為TRUE,即游標沒有找到數據就返回TRUE,反之則返回FALSE

（3）%ROWCOUNT – SQL 語句影響的行數,即返回游標所找到的數據行數(返回一個整數)

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

使用顯式游標更新行：

允許使用游標刪除或更新活動集中的行

聲明游標時必須使用 SELECT … FOR UPDATE語句

??? 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;

?

// 函數

/********************* 創建函數********************/

/*day 函數*/

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 自定義函數*/

/* 語法如下*/

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

return datatype is

begin

?

end;

/* 執行*/

var v1 varchar2(100)

exec :v1:=function_name

/*===== 不帶任何參數====*/

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參數的=====*/

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;

/* 執行：*/

SQL>var sal number

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

?

/* 帶out參數的函數*/

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;

/* 執行*/

SQL>var job varcha2(20);

SQL>var dname varchar2(20)

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

?

/*===== 帶in out 參數的函數=====*/

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;

/* 執行*/

? var result1 number;

? var result2 number;

? exec :result2:=30

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

?

/* 帶有IN參數的函*/

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無效！');

end get_empname;

?

/*1 、一個最簡單的自定義函數Fun_test1的定義*/

create or replace function Fun_test1(p_1 number) /*number 型輸入參數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 自定義函數的調用的存儲過程Pro_Fun_test1_1示例:一個輸入參數，一個輸出參數*/

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自定義函數的調用的存儲過程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;

?

//當查詢同一表的同一列查詢兩次時，按查詢結果的第一列排序則可以按一下方法，否則會報列名不確定

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

// 獲取查詢結果中指定行的數據

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

?

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

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

?

刪除表空間及其中的內容：

drop tablespace xxx including contents and datafiles;

查詢空表，避免因無數據造成空表無法導出的問題：（將查詢結果導出到Excel 然后賦值出來運行：）

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

?

導入 dmp 命令：

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

導出 dmp 命令：【非本地數據庫在 @ 后添加 ip/ 】

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

定時備份：

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

項目積累——SQL積累