oracle学习笔记（一）分享

1、基本语法SELECT FROM WHERE GROUP BY HAVINGORDER BYSELECT:查询的字段1、可用*表示所有字段。2、字段之间用逗号分割。3、可为字段起别名 其别名可写成SELECT AAAA。AA AS SS 或 AAAA。AA SS 可省略as4、可直接写字段值：如 SELECT AAAA。AA SS,'张三' NAME FROM 。。。FROM:查询的表名1、表名间用逗号分割2、可为表起别名 其别名可写成 FROM AAAA AS T 或 FROM AAAA T 可省略asWHERE:查询条件函数：NVL||SYSDATE3、连接连接：左联(+)自联、内联、外联4、子查询：5、合并数据：UNIONUNION ALL6、触发器：例子1：插入主键CREATE OR REPLACE TRIGGER AA_BEFORE INSERT ON AA FOR EACH ROWDECLAREBEGINSELECT AA_SEQ.NEXTVAL INTO :NEW.AA FROM DUAL;END AA_;例子2：CREATE OR REPLACE TRIGGER ADDB2B_GHDWUSERAFTER INSERT ON B2B_GHDWUSER FOR EACH ROWDECLARE V_MENUID B2B_GHJBMENU.MENUID%TYPE; _MENUID VARCHAR2(20); CURSOR INSERTUMENU IS SELECT A.MENUID FROM B2B_GHJBMENU A,XTGHDW B WHERE A.JNAM = B.JB AND B.DWBM= :NEW.GHDWBM; V_LXBM XTFBLX.LXBM%TYPE; CURSOR INSERTLXBM IS SELECT DISTINCT LXBM FROM XTFBLX;BEGIN OPEN INSERTUMENU; FETCH INSERTUMENU INTO V_MENUID,_MENUID; WHILE INSERTUMENU%FOUND LOOP INSERT INTO B2B_USERMENU (USERBM,MENUID) VALUES(:NEW.USERBM,V_MENUID); FETCH INSERTUMENU INTO V_MENUID; END LOOP; CLOSE INSERTUMENU; OPEN INSERTLXBM; FETCH INSERTLXBM INTO V_LXBM; WHILE INSERTLXBM%FOUND LOOP INSERT INTO B2B_USERXX (USERBM,LXBM) VALUES(:NEW.USERBM,V_LXBM); FETCH INSERTLXBM INTO V_LXBM; END LOOP; CLOSE INSERTLXBM;IF :OLD.SWBZ='2' AND :NEW.SWBZ = '0' THEN BEGIN SELECT TID,FUSED INTO L_ID,L_FUSED FROM RPC_CONTRAC WHERE HTBM = :NEW.HTBM AND HTLB <> '9'; IF L_FUSED=1 THEN RAISE_APPLICATION_ERROR(-20005,'不能删除!'); END IF; DELETE RPC_CONTRACENTRY WHERE TID = L_ID; DELETE RPC_CONTRAC WHERE TID = L_ID; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END IF;END ADDB2B_GHDWUSER;存储过程：例子1：CREATE OR REPLACE PROCEDURE "P_BJHHTED"-------输入、输出参数(AS_USER VARCHAR2,AD_RQ1 DATE,AD_RQ2 DATE)AS--------------------定义变量LS_GW VARCHAR2(8);LS_SPR VARCHAR2(10);LS_KS VARCHAR2(4);LD_SL1 NUMBER(14,4);LD_JE1 NUMBER(14,4);LD_SL2 NUMBER(14,4);LD_JE2 NUMBER(14,4);LD_SL3 NUMBER(14,4);LD_JE3 NUMBER(14,4);LD_SL4 NUMBER(14,4);LD_JE4 NUMBER(14,4);LD_SL5 NUMBER(14,4);LD_JE5 NUMBER(14,4);LD_SL6 NUMBER(14,4);LD_JE6 NUMBER(14,4);---------------------定义游标CURSOR C1 IS SELECT HTGW,KSJC,SPR FROM BJHHTED WHERE ZDR=AS_USER FOR UPDATE;---------------------开始执行存储过程BEGIN-------------------删除数据DELETE FROM BJHHTED WHERE ZDR=AS_USER;-------------------插入数据INSERT INTO BJHHTED(ZDR,HTGW,KSJC,SPR)SELECT DISTINCT AS_USER,HTGW,NVL(SUBSTR(HTBH,6,2),'%'),SPRMFROM CGHTWHERE QDRQ>=AD_RQ1 AND QDRQ<=AD_RQ2 AND LB='5' AND SWBZ ='2';--------------------打开游标OPEN C1;--------------------取出第一条数据FETCH C1 INTO LS_GW,LS_KS,LS_SPR;--------------------循环数据WHILE C1%FOUND LOOP ------------------查询出数据赋给变量 SELECT COUNT(HTBM), SUM(SPJE) INTO LD_SL1,LD_JE1 FROM CGHT WHERE QDRQ>=AD_RQ1 AND QDRQ<=AD_RQ2 AND LB='5' AND SWBZ='2' AND HTGW=LS_GW AND NVL(HTBH,'%') LIKE '%'||LS_KS||'%' AND SPRM=LS_SPR AND SPJE<=50000; ------------------更新表数据 UPDATE BJHHTED SET FS1=LD_SL1,JE1=LD_JE1, FS2=LD_SL2,JE2=LD_JE2, FS3=LD_SL3,JE3=LD_JE3, FS4=LD_SL4,JE4=LD_JE4, FS5=LD_SL5,JE5=LD_JE5, FS6=LD_SL6,JE6=LD_JE6 WHERE ZDR=AS_USER AND HTGW=LS_GW AND KSJC=LS_KS AND SPR=LS_SPR; ------------------取出下一条数据 FETCH C1 INTO LS_GW,LS_KS,LS_SPR;--------------------结束循环 END LOOP;--------------------关闭游标CLOSE C1;----------------------关闭存储过程END P_BJHHTED;JAVA调用存储过程1： CALLABLESTATEMENT CS = C.PREPARECALL("{CALL P_BJHHTED(?,?,?)}"); CS.SETSTRING(1,YHBM); CS.SETSTRING(2,XMBM); CS.SETSTRING(3,PHBM); CS.EXECUTE();例子2：PACKAGE：CREATE OR REPLACE PACKAGE PAK_TEMPLATE IS-- AUTHOR : LIYIMIN-- CREATED : 2008-03-27 14:41:51-- PURPOSE : -- PUBLIC TYPE DECLARATIONSTYPE RESULT_CUR IS REF CURSOR;-- PUBLIC CONSTANT DECLARATIONS--<CONSTANTNAME> CONSTANT <DATATYPE> := <VALUE>;-- PUBLIC VARIABLE DECLARATIONS--<VARIABLENAME> <DATATYPE>;-- QUALITATIVE RISKSPROCEDURE P_RESPONSE_ACTIONS(RESULT OUT RESULT_CUR,VRISKID VARCHAR2);END PAK_TEMPLATE;实体：CREATE OR REPLACE PACKAGE BODY PAK_TEMPLATE IS-- PRIVATE TYPE DECLARATIONS--TYPE <TYPENAME> IS <DATATYPE>;-- PRIVATE CONSTANT DECLARATIONS-- <CONSTANTNAME> CONSTANT <DATATYPE> := <VALUE>;-- PRIVATE VARIABLE DECLARATIONS--<VARIABLENAME> <DATATYPE>;PROCEDURE P_RESPONSE_ACTIONS(RESULT OUT RESULT_CUR,VRISKID VARCHAR2) ISBEGIN OPEN RESULT FOR SELECT B.RESPONSEIDEA NAME, A.RISKCODE RISK_ID, D.RISKDESC DESCRIPTION, E.ROMC RESPONSIBILITY, C.STARTDATE, C.ENDDATE FINISH, C.STARTDATE TARGET_START, C.ENDDATE TARGET_FINISH, C.COST MITIGATION_COST, F.ACTUALCOST ACTUAL_COST, '' TASK, G.RESPONSETYPEDESC STATUS, H.PROBABILITYCODE PROBABILITY, I.IMPACTTIMECODE SCHEDULE, J.IMPACTCOSTCODE COST, '' PERFORMANCE, B.SCORE, '' USER_TEXT1, '' USER_TEXT2, '' USER_TEXT3, '' USER_TEXT4, '' USER_TEXT5 FROM RISK_INFO A, RISK_PLAN B, RISK_PLAN_DETAIL C, RISK_INFO_NLS D, XTROLE E, RISK_AUDIT F, B_AUDIT_STATUS_NLS G, B_RISKPROBABILITY H, B_RISKIMPACTTIME I, B_RISKIMPACTCOST J WHERE A.RISKID = B.RISKID(+) AND B.PLANID = C.PLANID(+) AND A.RISKID = D.RISKID(+) AND C.RESPONSEROLE = E.ID(+) AND C.PLANDETAILID = F.PLANDETAILID(+) AND F.STATUS = G.STATUSID(+) AND B.PROBABILITYID = H.PROBABILITYID(+) AND B.IMPACTTIMEID = I.IMPACTTIMEID(+) AND B.IMPACTCOSTID = J.IMPACTCOSTID(+) AND B.SELECTEDORNOT = '0' AND TO_CHAR(A.RISKID) = VRISKID; END;END PAK_TEMPLATE;JAVA调用存储过程2： CONN = CONNMANAGER.GETCONNECTION(); STRSQL = "{CALL PAK_TEMPLATE.P_RESPONSE_ACTIONS(?,?)}"; DEBUG.PRINTLN("SCOSTSCHEDULEBEAN->GETVRESPONSEACTIONSINFO 22 SQL:" + STRSQL); CS = CONN.PREPARECALL(STRSQL); CS.REGISTEROUTPARAMETER(1, ORACLE.JDBC.ORACLETYPES.CURSOR); CS.SETSTRING(2, (STRING) MAP.GET("RISKID")); CS.EXECUTE(); RS = (RESULTSET) CS.GETOBJECT(1); WHILE (RS.NEXT()) { VRESPONSEACTIONSINFO VINFO = NEW VRESPONSEACTIONSINFO(); ACCESSBEAN.RSACCESSINFO(RS, VINFO); VLIST.GETLIST().ADD(VINFO); } RS.CLOSE(); RETURN VLIST;