oracle学习心得

1、创建序列语句--CreatesequencecreatesequenceGLOBAL_PKIDminvalue100000000001maxvalue999999999999startwith100000006008incrementby1nocache;2、创建方法语句CREATEORREPLACEFUNCTIONGET_GLOBAL_PKIDRETURNVARCHAR2ISRESULTVARCHAR2(24);BEGINSELECTglobal_PKID.NextvalINTORESULTFROMDUAL;RETURN(RESULT);ENDGET_GLOBAL_PKID;3、分割年月方法SELECTD.mark_name,D.TIMES,D.VALUE,D.ljz,D.PARENT_ID,D.PARENT_NAME,CASEWHEN(VALUEISNULLANDljzISNULLANDLENGTH(TIMES)8)THENSUBSTR(D.TIMES,'6','2')WHENVALUEISNULLTHENSUBSTR(D.TIMES,'8','2')WHENVALUEISNOTNULLTHENSUBSTR(D.TIMES,'6','2')ENDMONTHFROMztyxD4、逐行相减存储过程CREATEORREPLACEPROCEDURECALC_SMONTH_V(T_NAMEINVARCHAR2,ITEM_IDINVARCHAR2,YEARSINVARCHAR2,MONTHSINVARCHAR2,SMONTH_VOUTVARCHAR2)ISBEGINV_SQLVARCHAR2(4000);--动态SQL语句V_SUB_SQLV_SQL:=SELECT(SELECTT.T_VALUEFROMDEMO_TGRTWHERET.M_ID='1000001374'ANDT.YEARS='2006'ANDT.MONTHS='3')-(SELECTTT.T_VALUEFROMDEMO_TGRTTWHERETT.M_ID='1000001374'ANDTT.YEARS='2006'ANDTT.MONTHS='2')monthsFROMDUALEXECUTEIMMEDIATEV_SQLINTOSMONTH_VFROMDUAL;SELECTENDCALC_SMONTH_V;5、逐行相减函数CREATEORREPLACEFUNCTIONGET_VALUE(T_NAMEINVARCHAR2,ITEM_IDINVARCHAR2,YEARSINVARCHAR2,MONTHSINVARCHAR2)RETURNVARCHAR2ISRESULTVARCHAR2(256);V_SQLVARCHAR2(4000);V_MONTHSINTEGER(8);BEGINV_MONTHS:=TO_NUMBER(MONTHS)-1;V_SQL:='SELECT(SELECTT.LJZFROM'||T_NAME||'TWHERET.MARK_ID='||ITEM_ID||'ANDT.YEAR='||YEARS||'ANDT.MONTH='||MONTHS||')-(SELECTTT.LJZFROM'||T_NAME||'TTWHERETT.MARK_ID='||ITEM_ID||'ANDTT.YEAR='||YEARS||'ANDTT.MONTH='||V_MONTHS||')FROMDUAL';EXECUTEIMMEDIATEV_SQLINTORESULT;RETURN(RESULT);ENDGET_VALUE;6、查询英文表名、注释和数据条数的语句SELECTa.table_name,a.comments,b.num_rowsFROMUser_Tab_Commentsa,user_tablesbWHEREa.table_name=b.table_nameORDERBYa.table_name7、游标+存储过程批量修改表的数据CREATEORREPLACEPROCEDUREUPDATE_DATEISCURSORUSER_RSISSELECTTABLE_NAMEFROMUSER_TABLESORDERBYTABLE_NAME;CURSORTABLE_RS(TAB_NAMEVARCHAR2)ISSELECTCOUNT(*)FROMUSER_TAB_COLUMNSCOLWHERECOL.TABLE_NAME=TAB_NAMEANDCOL.COLUMN_NAME='CN_ITEM';V_SQLVARCHAR2(2000);V_TEMPNUMBER;--临时变量BEGINFORUSER_RECINUSER_RSLOOPOPENTABLE_RS(USER_REC.TABLE_NAME);LOOPFETCHTABLE_RSINTOV_TEMP;EXITWHENTABLE_RS%NOTFOUND;IF1=V_TEMPTHENV_SQL:='UPDATE'||USER_REC.TABLE_NAME||'SETCN_ITEM=REPLACE(CN_ITEM,'''','''')';EXECUTEIMMEDIATEV_SQL;DBMS_OUTPUT.PUT_LINE('1');ELSEV_SQL:='UPDATE'||USER_REC.TABLE_NAME||'SETNAME=REPLACE(NAME,'''',''''),PARENT_NAME=REPLACE(PARENT_NAME,'''','''')';EXECUTEIMMEDIATEV_SQL;DBMS_OUTPUT.PUT_LINE('2');ENDIF;ENDLOOP;CLOSETABLE_RS;ENDLOOP;END;8.地税行业排名创建视图语句CREATEORREPLACEVIEWV_BG_DSSRFHYFSZ_ITEM_DIMASSELECTPKID,ITEMID,YEARS,TAX,ACCOUNTS,UNIT,TIMESTAMPS,MONTHSFROMBG_DSSRFHYFSZ_FACTSTWHERET.ITEMIDIN(SELECTD2.NAMEFROMBG_DSSRFHYFSZ_ITEM_DIMD2WHERED2.PARENTNAMEIN(SELECTD3.NAMEFROMBG_DSSRFHYFSZ_ITEM_DIMD3WHERED3.PARENTNAME='合计'));排名语句SELECTSUM(accounts)ASACCOUNT_S,MAX(itemid)ASITEMIDFROMv_bg_dssrfhyfsz_item_dimWHEREYEARS=2010ANDMONTHS=3GROUPBYitemidORDERBYACCOUNT_S9.运用动态生成游标的存储过程CREATEORREPLACEPROCEDUREP_PM(V_YEARINVARCHAR2,V_MONTHINVARCHAR2)ISV_SQLVARCHAR2(2000);V_ITEMVARCHAR2(255);V_VALVARCHAR2(255);V_CUR_SQLVARCHAR2(2000);typecur_typeisrefcursor;--定义一个游标类型CURcur_type;--定义一个游标变量BEGINV_CUR_SQL:='SELECTITEMIDFROMV_BG_DSSRFHYFSZ_ITEM_DIM_TWHEREYEARS='||V_YEAR||'ANDMONTHS='||V_MONTH||'ORDERBYACCOUNTSDESC';OPENCURFORV_CUR_SQL;V_VAL:=1;LOOPFETCHCURINTOV_ITEM;EXITWHENCUR%NOTFOUND;V_SQL:='UPDATEbg_dssrfhyfsz_factsFSETPM='||V_VAL||'WHEREF.YEARS='||V_YEAR||'ANDF.MONTHS='||V_MONTH||'ANDTAX=''合计''ANDF.ITEMID='''||V_ITEM||'''';--DBMS_OUTPUT.PUT_LINE(FRACT_ROW.CREATED);EXECUTEIMMEDIATEV_SQL;V_VAL:=V_VAL+1;COMMIT;ENDLOOP;CLOSECUR;ENDP_PM;10.根据一个表的内容区更新另一张表的数据UPDATEBG_DSYYSFSMFQYLX_FACTSASETA.TAX=(SELECTB.NAMEFROMBG_DSYYSFSMFQYLX_TAX_DIMBWHEREA.TAX=B.TAX),ENTTYPE=(SELECTC.NAMEFROMBG_DSYYSFSMFQYLX_ENTTYPE_DIMCWHEREA.ENTTYPE=C.ENTTYPE)11.oracle中用for循环调用存储过程BEGINFORyearsIN2009..2010LOOPFORmonthsIN1..12LOOPP_PM(years,months);ENDLOOP;ENDLOOP;END;12.讲一个表的数据插入到另一张表INSERTINTOBG_DSSRFHYFSZ_FACTS(PKID,ITEMID,YEARS,MONTHS,TAX,UNIT,ACCOUNTS)(SELECTGLOBAL_PKID.NEXTVAL,ITEMID,YEARS,MONTHS,'合计',UNIT,ACCOUNTSFROMV_BG_DSSRFHYFSZ_ITEM_DIM_T)13.ORALCE删除重复数据的方法语句a.查找重复记录的方法selectname,age,count(*)fromtestgroupbyname,agehavingcount(*)1b.删除重复记录的方法deletetestwhererowidnotin(selectmin(rowid)fromtestgroupbyname,age);14.oracle的替换字符函数A．replace（’abcdef’,’ab’,’cd’）将字符串abcdef中的ab替换成cd，针对字符串的操作B．translate（char,from,to）返回将出现在from中的每个字符替换为to中的相应字符以后的字符串。若from比to字符串长，那么在from中比to中多出的字符将会被删除。三个参数中有一个是空，返回值也将是空值。15.oracle的rank()和dense_rank()函数A.rank()的使用Rank（）不仅有分析的功能，而且还有求合计的功能;语法为：selecta.*,rank()over(partitionbycol2orderbycol1)rankfromtablea;说明：列出col2分组后根据col1排序，并生成数字列。例子：查询每门功课前几天名的分数Select*from(selectrank()over(partitionby科目orderby分数desc)rank,a.*froma)twheret.rk=3;例子：合计功能-计算出数值（4，1）在orderbycol1，col2排序下的排序值，也就是col1=4，col2=1在排序后的位置Selectrank(4,1)withingroup(orderbycol1,col2)rankfromtable;16.oracle的rowtype和type的区别Rowtype：记录每行的信息Type：记录某一列的信息例：createtablestudent(idvarchar2(3),namevarchar2(32));declaret_recordstudent%rowtype;c_recordstudent.name%type;beginselect*intot_recordfromstudentwhereid='1';selectnameintoc_recordfromstudentwhereid='1'