ORACLE数据库的基本语法集锦--表createtabletest(namesvarchar2(12),datesdate,numint,doudouble);--视图createorreplaceviewvi_testasselect*fromtest;--同义词createorreplacesynonymaafordbusrcard001.aa;--存储过程createorreplaceproducedd(v_idinemployee.empoy_id%type)asbeginenddd;--函数createorreplacefunctionee(v_idinemployee%rowtype)returnvarchar(15)isvar_testvarchar2(15);beginreturnvar_test;exceptionwhenothersthenend--三种触发器的定义createorreplacetriggerffalterdeleteontestforeachrowdeclarebegindeletefromtest;ifsql%rowcount<0orsql%rowcountisnullthenrais_replaction_err(-20004,"错误")endifendcreateorreplacetriggerggalterinsertontestforeachrowdeclarebeginif:old.names=:new.namesthenraise_replaction_err(-2003,"编码重复");endifendcreateorreplacetriggerhhforupdateontestforeachrowdeclarebeginifupdatingthenif:old.names<>:new.namesthenreaise_replaction_err(-2002,"关键字不能修改")endifendifend--定义游标declarecursoraaisselectnames,numfromtest;beginforbbinaaloopifbb.names="ORACLE"thenendifendloop;end--速度优化，前一语句不后一语句的速度快几十倍selectnames,datesfromtest,bwheretest.names=b.names(+)andb.namesisnullandb.dates>date('2003-01-01','yyyy-mm-dd')selectnames,datesfromtestwherenamesnotin(selectnamesfrombwheredates>to_date('2003-01-01','yyyy-mm-dd'))--查找重复记录selectnames,numfromtestwhererowid!=(selectmax(rowid)fromtestbwhereb.names=test.namesandb.num=test.num)--查找表TEST中时间最新的前10条记录select*from(select*fromtestorderbydatesdesc)whererownum<11--序列号的产生createsequencerow_idminvalue1maxvalue9999999999999999999999startwith1incrementby1insertintotestvalues(row_id.nextval,....)存储过程1.基本结构CREATEORREPLACEPROCEDURE存储过程名字(参数1INNUMBER,参数2INNUMBER)IS变量1INTEGER:=0;变量2DATE;BEGINEND存储过程名字2.SELECTINTOSTATEMENT将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)例子：BEGINSELECTcol1,col2into变量1,变量2FROMtypestructwherexxx;EXCEPTIONWHENNO_DATA_FOUNDTHENxxxx;END;...3.IF判断IFV_TEST=1THENBEGINdosomethingEND;ENDIF;4.while循环WHILEV_TEST=1LOOPBEGINXXXXEND;ENDLOOP;5.变量赋值V_TEST:=123;6.用forin使用cursor...ISCURSORcurISSELECT*FROMxxx;BEGINFORcur_resulti