ORACLE数据库的基本语法集锦

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%rowcount0orsql%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=ORACLEthenendifendloop;end--速度优化，前一语句不后一语句的速度快几十倍selectnames,datesfromtest,bwheretest.names=b.names(+)andb.namesisnullandb.datesdate('2003-01-01','yyyy-mm-dd')selectnames,datesfromtestwherenamesnotin(selectnamesfrombwheredatesto_date('2003-01-01','yyyy-mm-dd'))--查找重复记录selectnames,numfromtestwhererowid!=(selectmax(rowid)fromtestbwhereb.names=test.namesandb.num=test.num)--查找表TEST中时间最新的前10条记录select*from(select*fromtestorderbydatesdesc)whererownum11--序列号的产生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_resultincurLOOPBEGINV_SUM:=cur_result.列名1+cur_result.列名2END;ENDLOOP;END;7.带参数的cursorCURSORC_USER(C_IDNUMBER)ISSELECTNAMEFROMUSERWHERETYPEID=C_ID;OPENC_USER(变量值);LOOPFETCHC_USERINTOV_NAME;EXITFETCHC_USER%NOTFOUND;dosomethingENDLOOP;CLOSEC_USER;存储过程的一些相关问题详细讲解有关Oracle存储过程的相关问题：1.在Oracle数据库中，数据表别名是不能加as的，例如：selecta.appnamefromappinfoa；——正确selecta.appnamefromappinfoasa；——错误注释：这可能是为了防止和Oracle数据库中的存储过程中的关键字as冲突的问题。2.在存储过程中，select某一字段时，后面必须紧跟into，假如select整个记录，利用游标的话就另当别论了。selectaf.keynodeintoknfromAPPFOUNDATIONafwhereaf.appid=aidandaf.foundationid=fid;--有into，正确编译selectaf.keynodefromAPPFOUNDATIONafwhereaf.appid=aidandaf.foundationid=fid;--没有into，编译报错，提示：CompilationError:PLS-00428:anINTOclauseisexpectedinthisSELECTstatement3.在我们利用select……into……语法时，必须先确保数据库中有该条记录，否则会报出nodatafound异常。可以在该语法之前，先利用selectcount（*）from查看数据库中是否存在该记录，如果存在，再利用select……into……4.请注意，在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行的阶段会报错。selectkeynodeintoknfromAPPFOUNDATIONwhereappid=aidandfoundationid=fid;--正确运行selectaf.keynodeintoknfromAPPFOUNDATIONafwhereaf.appid=appidandaf.foundationid=foundationid;--运行阶段报错，提示ORA-01422:exactfetchreturnsmorethanrequestednumberofrows5.在存储过程中，关于出现null的问题假如有一个表X，定义如下：createtableX(idvarchar2(50)primarykeynotnull,vcountnumber(8)notnull,bidvarchar2(50)notnull--外键);假如在存储过程中，使用如下语句：selectsum（vcount）intofcountfromXwherebid='xxxxxx'；如果X表中不存在bid=xxxxxx的记录，则fcount=null（即使fcount定义时设置了默认值，例如：fcountnumber（8）：=0依然无效，fcount还是会变成null），这样以后使用fcount时就可能会出现问题，所以在这里我们最好先判断一下：iffcountisnullthenfcount:=0;endif;6.Hibernate调用Oracle的存储过程this.pnumberManager.getHibernateTemplate().execute(newHibernateCallback()...{publicObjectdoInHibernate(Sessionsession)throwsHibernateException,SQLException...{CallableStatementcs=session.connection().prepareCall({callmodifyapppnumber_remain(?)});cs.setString(1,foundationid);cs.execute();returnnull;}});/**数据定义语句(只操作表的结构)*/--创建表--创建部门表createtabledepartment_13(department_idnumber(6),department_namevarchar(25),manager_idnumber(6),location_idnumber(4));--使用子查询创建表createtabledepartment_13_tempasselectdepartment_id,department_namefromdepartment_13;--修改表--增加字段altertabledepartment_13_tempadd(manager_idnumber(6));--删除字段altertabledepartment_13_tempdropcolumnmanager_id;--修改字段名称altertable表名renamecolumn原列名to新列名;--修改字段类型altertabledepartment_13_tempmodify(manager_idvarchar(6));--修改字段大小altertabledepartment_13_tempmodify(manager_idnumber(4));--删除表droptabledepartment_13_temp;--数据字典表selecttable_namefromdba_tables;selecttable_namefromuser_tables;--察看用户拥有的数据库对象类型selectdistinctobject_typefromuser_objects;--约束--创建非空约束(同时也是列级约束)createtabledepartment_13(department_idnumber(6)constraintdept_13_idnotnull,department_namevarchar(25),manager_idnumber(6),location_idnumber(4));--创建唯一性约束(同时也是表级约束)createtabledepartment_13(department_idnumber(6),department_namevarchar(25),manager_idnumber(6),location_idnumber(4),constraintdep_id_13_uniunique(department_id));--创建主键约束createtabledepartment_13(department_idnumber(6),department_namevarchar(25),manager_idnumber(6),location_idnumber(4),constraintdep_id_13_priprimarykey(department_id));--创建