筆試面試-數(shù)據(jù)庫總結(jié)

數(shù)據(jù):1T-- 早期的數(shù)據(jù)-----文海量 程序幫你刪除增加(管理)----數(shù)據(jù)庫管理系 數(shù)據(jù)在文 名字員工號----表 nan----行 記二維 二維表對應(yīng)的最小單 一條記錄的最小單 程序數(shù)據(jù) netIP()net6login:openlabsqlplusopenlab/open123sqlplusscott/tiger SQL>desc表名SQL>desc ID(在表中數(shù)據(jù)的唯一編號 NOTNULL NOTNULL START_DATE(入職日期 COMMENTS(說明 MANAGER_ID( SQL>spool文件 ----退出之后不在到文如果想繼續(xù)存入敲入令和結(jié)果-----SQL>spool文件 varchar2---字符串類型 sqlAselectselect{*，from select*from表名;selectidfroms_emp;selectid,last_name,salaryfroms_emp; summit2.sqlnetsqlplusselect[distinct]{*，字段名[alias],[字段名，字段名]}from表名; selecttitlefromselectdistinct titlefroms_emp; selecttitlezhiweifrom默認(rèn)顯示成大寫------在別名上加雙引 selecttitle"zhiwei"from 把所有人的拼接之后顯selectlast_name,first_namefrom selectlast_name||first_namefrom+-*selectsalaryfroms_emp;selectsalary*12froms_emp;select fromNULL--------cNULL--------未知值不同于空格空字符串NULLNULLselectfirst_name,salary*12froms_emp; //logicerrororacle提供一個函數(shù)nvl p1p2p1p2p1NULLp2如果p1p1selectfirst_name,"yearfromselectfirst_name,"yearfroms_emp;selectfirst_name,"yearsalary"from oracleselectsysdatefrom selectsysdatefrom selectnvl(sysdate,'01-AUG-11')fromdual;selectnvl(NULL,'01-AUG-11')fromdual;Bwhere(條件限制記錄 選salary1000select*froms_empwhere selectlast_name,salaryfroms_emp; selectlast_name,salaryfroms_empwhere注意:*能代表所有的列慎用字面值------12 oracleselectlast_name||''''||first_namefroms_emp;selectlast_name||123||first_namefroms_emp; 小寫特殊字符last_namemyselectlast_name"myname"froms_emp;c中使用相同注意等號注意等于號用一個=id1selectlast_name||first_namenamefroms_empwhereid=1;sqlselectlast_name||first_namenamefroms_empwhereidlike'aa'like'aaa'true like'aab' 1wherenamelike'wang%'last_name帶aselectlast_namefroms_empwherelast_namelikeNULLismanager_idselectlast_name,manager_idfroms_empwheremanager_id isnull;between and 8001500selectlast_name,salaryfroms_empwheresalarybetween 800and1500;注意：1.in中的數(shù)據(jù)排放1 10%把概率高的放前null aandblike應(yīng)用于模糊查詢 user_tables的表 selecttable_namefromuser_tableswheretable_namelike'%\_%'escape'\';轉(zhuǎn) s_empselect*fromuser_tableswheretable_namelike'S\_EMP'escape'\'; or工資8001500selectlast_name,salaryfroms_emp selectlast_name,salaryfroms_emp salary>=800andsalary<=1500; null----isnotnull ----in(list)----notinlikebetween <>selectidfroms_empwhereid!=1;selectidfroms_empwhere注意:notin------如果有null值則不能用notin COMMISSION_PCTfroms_empwhereCOMMISSION_PCTin(10); COMMISSION_PCTfroms_empwhereCOMMISSION_PCTnotin(10);selectCOMMISSION_PCTfromwherenvl(COMMISSION_PCT,0)not4142421000提示如果想表達(dá)的更清楚可以使用小括號selectlast_name,salaryfromwheredept_id=41or(dept_id=42and----這條選擇出來的數(shù)據(jù)可能一點(diǎn)10004142selectlast_name,salaryfromwheresalary>1000and(dept_id=42ordept_id=41);selectsalary*12+100from select from Corderby(默認(rèn)的排序----自然順序字典順序升序selectlast_name,salaryfroms_emporder last_nameselectlast_name,salaryfrom orderbysalaryselectlast_name,salaryfrom orderbysalaryselectlast_name,salaryfroms_emp orderby1;selectlast_name,salaryfroms_emp orderby2desc;(1,2selecttitle,salaryfroms_emporderbytitle,salary;selecttitle,salaryfroms_emporderbytitle,salaryselecttitle,salaryfroms_emporderbytitle注意：1.orderbyselect2.注意 mission_pctfroms_emporderby Dfunction selectnvl(manager_id,0)froms_emp;selectupper('abc')fromselectinitcap('oneworldonedream')fromdual;selectconcat('o','world')fromdual;select'o'||'world'fromdual;selectsubstr('o',1,4)fromdual;substr1selectlength(last_name),last_namefromselectlast_name,substr(last_name,1,2)fromselectlast_name,substr(last_name,length(last_name)-1,2)fromselectlast_name,substr(last_name,-2,2)fromlast_name5first_nameCarmenSELECTFIRST_NAMEFROMS_EMPwhereSELECTFIRST_NAMEFROMS_EMPwhereselecttitlefroms_empwherefirst_name='Carmen';selecttitle,first_namefrom whereand1000 last_name,salaryfroms_empwheresalary>1000orderbysalarydesc,last_namedesc;id1,3,5selectlast_name,idfroms_empwhereid=1orid=3or找出last_name長度是4并且工資大于1500的員工按工資升序selectlast_name,salaryfroms_empwherelength(last_name)=4andsalary>1500 orderbysalary;selectcount(nvl(manager_id,0))fromround四舍五入round(11.4,0)--- round(11.5)---trunc截 trunc(11.5)---0selectto_char(salary,'fm$099,999,999.99')froms_emp;selectto_char(salary,'fmL099,999,999.99')from環(huán)境變 selectuserenv('language')from NLS_LANG='SIMPLIFIED shsetenvNLS_LANG'SIMPLIFIED selectlast_namefroms_empwhereid='1';selectlast_namefroms_empwhereid=1;to_char可以不跟后面的格 就是簡單的把對應(yīng)的類型加上單引last_namemanager_id first_name,manager_idfrom first_name,nvl(manager_id,'BOSS')from first_name,nvl(to_char(manager_id),'BOSS')from集 selectlast_name,dept_id,namefrom selectlast_name,dept_id,namefrom wherewhere*Emutitableselect內(nèi)連 selectlast_name,dept_id,namefrom whereselectlast_name,dept_id,namefroms_empe,s_deptdwheredept_id=d.id; last_name froms_empe,s_deptd,s_regionrwheree.dept_id=d.idandcolnamefor collast_namefor betweenlowsaland 12345 andlast_name,salaryselecte.last_name,e.salary,g.gradefroms_empe,salgradegwheree.salarybetweeng.losalands_emp121314253idmanager_id distinctm.last_name,m.id,e.manager_id s_empe,s_empmwhere8個25-8=17個普通員外連接（outerjoin）=內(nèi)連接+匹配不上 如果是外連接的語法加號對面的表中的數(shù)據(jù)全都查詢出來selectdistinctm.last_name,m.id,e.manager_id s_empe,s_empwhere distinctm.last_name,m.id,e.manager_id s_empe,s_empmwherem.id=e.manager_id(+) e.manager_idisnull;updates_empsetdept_id=nullwheredept_id=50;last_name,dept_idselectlast_name,dept_id,froms_empe,s_deptdwheree.dept_id=d.id;23顯示所有員工對應(yīng)的部門號和部門名稱如果沒有部門名稱則顯示'nodepartment'selectlast_name,dept_id,froms_empe,s_deptdwhere'nodepartment'selectlast_name,nvl(to_char(dept_id),'nodepartment')froms_empwheredept_idisselectlast_name,dept_id,nvl(,'nodepartment')froms_empe,s_deptdwheree.dept_id=d.id(+)andd.idisnull;要所有的普通員工（那些人不是） 顯示所有級別對應(yīng)的員工的last_name 'noemployee'要級別表中所有的數(shù)據(jù)那加號應(yīng)該加在那邊？selecte.last_name,e.salary,g.gradefroms_empe,salgradewheree.salarybetweeng.losalandg.hisal;selectnvl(e.last_name,'noemployee'),e.salary,g.gradefroms_empe,salgradewheree.salary(+)betweeng.losaland50updates_empsetsalary=0wheresql如下加號怎么加？selecte.first_name,e.salary,g.gradefroms_empe,salgradegwheree.salarybetweeng.losal(+)and外連接selectdistinctm.last_name,m.id,e.manager_id s_empe,s_empmwhereoracleselectdistinctm.last_name,m.id,e.manager_id s_empe,s_empmwherem.id=e.manager_id(+)ande.manager_idisnull;leftouterjoin/rightouterwhereon distinctm.last_name,m.id,e.manager_id s_emperightouterjoins_empmonwhereselectdistinctm.last_name,m.id,e.manager_id s_emperightouterjoins_empmonm.id=e.manager_idwheree.manager_idisnull;sqlselecte.first_name,e.salary,g.gradefroms_empe,salgradegwheree.salarybetweeng.losal(+)andselectfroms_empeleftouterjoinsalgradeone.salarybetweeng.losaland 等值連 between >=<=inlike外連接等值連 非等值連 between >=<=inlikesql99leftouterjoin /rightouterjoinfullouterjoin=左外+右外-重復(fù)的union/uoionall(union和并結(jié)果集時排重unionall)selectidfroms_empunionselectidfromselectidfroms_empunionallselectidfroms_emp;左外 右外selectfroms_empefullouterjoinsalgradeone.salarybetweeng.losalandg.hisal;---27selecte.first_name,e.salary,g.gradefroms_empeleftouterjoinsalgradeone.salarybetweeng.losalandg.hisalunionselecte.first_name,e.salary,g.gradefroms_emperightouterjoinsalgradeone.salarybetweeng.losalandg.hisal;26Fgroupby（分組組函數(shù)） selectsum(salary)froms_emp;selectmax(salary)froms_emp;groupbyorderdept_idselectdept_idfrom groupbyselectsalary,dept_idfrom groupbyselectsalary,dept_idfrom groupbyselectmax(salary),dept_idfrom groupbyselectsum(salary),dept_idfroms_emp groupbydept_idorderbysum(salary);selectsum(salary)s,dept_idfroms_empgroupbydept_idorderbys; selectsum(salary)s,dept_idfroms_empwheredept_id=41groupbyorderbys;selectsum(salary)s,dept_idfroms_empgroupbydept_idhaving orderbywheregroupby之前的groupbyordergroupbyselectgroupbyG 那些人是selectdistinctmanager_idfroms_emp;(得到所有的id和NULL)selectlast_name,id,manager_idfroms_empwhereidin(1,2,3,6,7,8,9,10);selectlast_name,id,manager_idfroms_empwherein(selectdistinctmanager_idfromselecttitlefroms_empselectfirst_name,titlefroms_empwheretitle=(selecttitlefroms_empwhereandhavingselectsum(salary)froms_empwheredept_id=42;selectsum(salary),dept_id froms_empgroupby sum(salary)>(selectfroms_empwherewherehavingfromselectsum(salary)s,dept_idfroms_empgroupbydept_idhaving orderbyselects,dept_id,nfrom(selectsum(salary)s,dept_id,max(name)nfroms_emp,s_deptwheredept_id=s_dept.idgroupbydept_idorderbys)where25125selectlast_name,froms_empe,s_deptd,s_regionwheree.dept_id=d.id 查詢那些人是用外連接做和子查詢(oracle/sql99)selectdistinctm.last_name,m.id,e.manager_id s_empe,s_empwherem.id=e.manager_id(+)ande.manager_idisnotselectlast_name,id,manager_idfroms_empwhereidin(select manager_idfromselectlast_name,id,manager_idfromwhereidnotin(select manager_idfromselectlast_name,id,manager_idfromwhereidnotin(select froms_empwheremanager_idisnotnotinselectdept_id,sum(salary),max()froms_empe,s_deptdwheree.dept_id=d.id groupbydept_idhavingsum(salary)>=(selectsum(salary)froms_empwheredept_id=10)anddept_id!=10;selectsum(salary)ss,dept_id froms_empgroupbydept_id ss>(selectsum(salary)froms_empwheredept_id=42); select groupby orderbydept_idselectmax(salary),min(last_name),dept_id,min() s_empe,s_deptdwheree.dept_id=d.idgroupbydept_id;selectsum(salary)s,dept_id,max(name)nfroms_emp,s_deptwheredept_id=s_dept.idgroupbydept_idorderby #唯一的*o一對 丈夫妻 一對 顧客訂 多對 學(xué)生課 一個學(xué)生可以選多門課一個課程可不可以被多學(xué)生選如果這兩個人的信息成雙入對 你需要一個人信息的時并不需另一個 可以兩張如何來表達(dá)一對多 1,2,3,4是11121314 拆1 112131411111c22334561 121314151621c222324252631現(xiàn)323334353 c 1111116222222633333361所有的字段不可再分面積(width/length)2第二范式3 1 zhangsan 2 zhangsan ( 保留字1 112 213141主鍵---------唯一標(biāo)示一條記錄的字段---------非 ---------一個表可以用多個非空唯一的字段但主鍵只有一個外鍵---------外鍵的值受限于的字段---------外鍵的值要么是的字段所擁有的值要么是E-R實(shí)體 表屬 字類 字段類主 一個員 有多個顧 主 外學(xué) 課 關(guān)系表中外鍵外鍵 寫成一個sql createtabletablename( fnamevarchar2(10),snamechar(10)insertintotablenamevalues(1,'abc','abc');select fromselect*fromtablenamewherefname='abcselect*fromtablenamewheresname='abc當(dāng)這個字段的長度是不變的號碼當(dāng)這個字段的長度是變化 都做成定長 ----存取效率不18----255--- 255 select*fromtablenamewherefname=trim('abcnumber數(shù)字類型10e-13010e127number(p,s)-----p有效位sblob-----大二進(jìn)制類型4gbclob-----大字符類型4gb droptable createtabletablename( hirdateinsertintotablenamevalues(1,'02-AUG-selectto_date('2009-08-08','yyyy-mm-dd')fromdual; hh----小時默認(rèn)是12 to_charselectto_char(start_date,'yyyy-mm-ddhh24:mi:ssday')froms_emp;selectto_char(start_date,'yyyy')fromselectlast_name,start_datefroms_empwherestart_date selectlast_name,start_datefroms_empwhereto_char(start_date,'mm')=3;selectlast_name,start_datefroms_empwhereto_char(start_date,'mm')='03';sysdate----2011-08-29trunc(sysdate,'dd')------2011-08-29selectto_char(trunc(sysdate,'dd'),'yyyy-mm-ddhh24:mi:ss')fromdual;round(sysdate,'dd')------2011-08-30selectto_char(round(sysdate,'dd'),'yyyy-mm-ddhh24:mi:ss')fromdual;selectto_char(round(sysdate,'yy'),'yyyy-mm-ddhh24:mi:ss')fromdual; 12 11months_between(d1,d2)如果正好是整月selectmonths_between(sysdate,start_date)froms_emp;selectmonths_between('28-FEB-11','30-JUN-11')fromdual;next_day(sysdate)selectto_char(next_day(sysdate,'friday'),'yyyy-mm-ddhh24:mi:ss')from1okselectto_char(sysdate+1,'yyyy-mm-ddhh24:mi:ss')fromdual;時分秒信 selectto_char(last_day(sysdate),'yyyy-mm-ddhh24:mi:ss')fromdual;91了時分秒信息但默認(rèn)顯示只有日-月的縮寫-兩位年 如何來表達(dá)關(guān)系如何來設(shè)置約束createtable字段名類型 default值,字段名類型 default值主鍵------primary外鍵------foregin非空------not唯 檢 create idnumber primarykey,tidnumberinsertintotestcreatetable idnumber,tidnumber,primarykey(id,pid)注意:notnull約束只有列級約束沒有表級約束idnumber varchar2(30) char(30)唯一salarynumber numberconstraint test_pkprimarykey, notnull, constrainttest_ukunique,salarynumbernotnullselectfromuser_constraintswherealtertabletestdropconstraintSYS_C check約束 numberconstraint test_pkprimarykey,salarynumbercheck(salary>1000)insertintotest insertintotest numberconstraint test1_pkprimarykey,salarynumber,check外鍵某張表唯一性字段 主表(父表 顧 cno--顧客編 建 createtable numberprimarykey, cnamevarchar2(30)insertintocustomercreatetable numberprimarykey, numberreferencesinsertintocordervalues(1,'001',null);insertintocordervalues(2,'001',1);insertintocordervalues(3,'001',1);1 selectfromcustomerc,corderwhere andnull事務(wù)------原子性一致性性持久性(AUCD)AB000 setsalary=salary-50000whereid=1;update set where性insertintocorder sqlserver自動提 drop createtablet(idnumberprimarykey,salarynumber); insertintotvalues(1,1000); insertintotvalues(2,1000); rollbacktoa; commitcreatesequenceselecttests.nextvalfromdual;(-pow(10,26),pow(10,27))insertintocustomercreatesequenceincrementbyn,步進(jìn)默認(rèn)是1 withn,起始值默認(rèn)1maxvaluen/nomaxvalue,nomaxvaluepow(10,27)minvaluen/nominvalue,cachen/nocache20注：如果cycle的序列到最大值后回到默認(rèn)值1createsequence incrementbystartwithcache索引從頭找到尾全表掃描 索引查每次查找的數(shù)據(jù)量2%- 主 唯一約束自動建立索create customer_indexonsetautotrace select*fromcustomer;select*fromcustomer whereid=1;select*fromcustomer wherecno='abc';create customer_index1on 視圖,分頁？rowid視圖限制數(shù)據(jù)庫的select(selectsum(salary)s,dept_idfroms_empgroupbydept_id)select*(selectsum(salary)s,dept_idfroms_empgroupbydept_id)wheres=(selectmax(ss)from(selectsum(salary)ssfroms_empgroupbydept_id));createorreplaceviewcviewasselectid,last_name froms_emp;select*from 復(fù)雜的selectsalary,last_namefroms_emporderbysalary; selectrownum,salary,last_namefromselectrownum,salary,last_namefroms_empwhere如果按工資排序后取前5selectrownum,salary,last_namefroms_empwhererownum<6orderbysalary;//error1selectsalary,last_namefroms_emporderby2rownumwhereselectrownum,salary,last_namefrom(selectsalary,last_namefroms_emporderbysalary)whererownum<11;selectrownum,salary,last_namefrom(selectsalary,last_namefroms_emporderbysalary)whererownum<11andrownum>5;//norowsselect*from(selectrownumn,salary,last_namefrom(selectsalary,last_namefroms_emporderbysalary)whererownum<11)wheren>5;select*from(selectrownumn,salary,last_namefrom(selectsalary,last_namefroms_emporderbysalary)) n>5and有一張表里面有重復(fù)的數(shù)據(jù) 12345id, name idname1.數(shù)據(jù)庫的技 ----數(shù)據(jù)庫內(nèi)部技proc/c++----針對cC++ ----oracle底層的通dbms_output.put_line('oworld');setserveroutput 賦 比 v_namevarchar2(30);selectlast_nameintov_namefroms_empwhereid=v_id; v_namevarchar2(30);selectlast_nameintov_namefroms_empwhereid=v_id;標(biāo)示 給變量游標(biāo)子程序變量的語 [CONSTANT]type[NOTNULL][:=value];const代表常量如果你想變量必須有初始值可以加notnullnull constantnumber:=10;v_nu:=100;--error 字符類 v_names_emp.last_name%type; selectlast_nameintov_namefroms_empwhereid=v_id;record isrecord( namevar_rectype_rectype_recvar_recvar_rec2type_rec;--selectid,last_nameintovar_recfrom where--selectidintovar_recfroms_empwhereid=1;//errorselectid,last_name intovar_rec.id,var_from whereid=1; isrecord( name為了寫的簡潔一 取表的一行作為類 selectid,last_nameintovar_rec.id,var_rec.last_namefroms_empwhereid=1;talbeTYPEtabletype(tableISTABLEtype（表中的值的數(shù)據(jù)類型）INDEXBYTYPEtable_charISTABLEOFchar(30)INDEXBY var_t(1):='os_empid1,3,5tableTYPEtable_empISTABLEOFs_emp%rowtypeINDEXBYBINARY_INTEGER; select*intovar_t(1)froms_empwhereid=1;select*intovar_t(2)froms_empwhereid=3;select*intovar_t(3)froms_empwhere<<outer>> dbms_output.put_line('inner:'||outer.var_n);--外部變量if 表達(dá)式thenelsif表達(dá)式thenend number;--沒有賦初始值都是null var_n1=var_n2thenelsifvar_n1>var_n2thenelsifvar_n1<var_n2thenelsifvar_n1isnullandvar_n2isnullthendbms_output.put_line('var_n1andvar_n2isnull');dbms_output.put_line('var_n1isvar_n2');endif; number;--沒有賦初始值都是null var_n1=var_n2thenelsifvar_n1>var_n2thenelsifvar_n1<var_n2thenelsifvar_n1isnullandvar_n2isnullthendbms_output.put_line('var_n1andvar_n2isnull');null;endif; 表達(dá)式thenend ifvar_n>10thenendif;endloop;s_emptableidsalarylast_nameidwhilewhile表達(dá)式loopendloop;110 whilevar_n<10loopendloop; 100條數(shù)據(jù)agefor110forain1..10loopendloop;a不用在declare中就可以直接使 goto名110 ifvar_n<11gotolabe_go;endif;--selectidfroms_empwhereid=1;insertintotestvalues('abc',sysdate);只有dml和dcl可以直接在plsql塊中使用 如selectddlsql有專門的語法a變 selectlast_name,replace(last_name,'a','y')fromidtabletypetable_t istableofs_emp%rowtypeindexby select*intovar_temp(1)froms_empwhereid=1;select*intovar_temp(2)froms_empwhereid=2;select*intovar_temp(3)froms_empwhereforain1..3loopendloop;typetable_t istableofs_emp%rowtypeindexby select*intovar_temp(1)froms_empwhereid=1;select*intovar_temp(100)froms_empwhereid=2;select*intovar_temp(205)froms_empwhereforain1..3endloop; 得到ntypetable_t istableofs_emp%rowtypeindexby select*intovar_temp(1)froms_empwhereid=1;select*intovar_temp(100)froms_empwhereid=2;select*intovar_temp(205)froms_empwhereid=3;if(var_n=var_temp.last())thenendif;endloop;如果下標(biāo)連續(xù)用其中的兩個函數(shù)typetable_t istableofs_emp%rowtypeindexby select*intovar_temp(1)froms_empwhereid=1;select*intovar_temp(2)froms_empwhereid=2;select*intovar_temp(3)froms_empwhereforainvar_temp.first()..var_temp.last()loopendloop; 100條數(shù)據(jù)agecreatetablestudent123idnumberprimarykey,namevarchar(30),age ain1..100insertintostudent123values(test_students.nextval,'abc',23);endloop;游 4 emp_cursorisselect*from fetchemp_cursorintofetchclosecursor isselect*froms_emp;--游 openemp_cursorfetchemp_cursorintovar_emp;--提取數(shù)據(jù)fetchemp_cursorintovar_emp;closeemp_cursor;--關(guān)閉游標(biāo)cursor isselect*froms_emp;--游 var_nnumber;selectcount(*)intovar_nfroms_emp;openemp_cursor;--打開游標(biāo)forain1..var_nfetchemp_cursorintovar_emp;--提取數(shù)據(jù)endcloseemp_cursor;--關(guān)閉游標(biāo)forcursor isselect*froms_emp;--游 forvarinemp_cursorloop--varendloop;如果我們使用loop 或者while 循環(huán)把游標(biāo)的數(shù)據(jù)遍歷出來cursor isselect*froms_emp;--游 openemp_cursor;--打開游標(biāo)fetchemp_cursorintovar_emp;exitwhenemp_cursor%notfound;--當(dāng)發(fā)現(xiàn)不了數(shù)據(jù)就退出endcloseemp_cursor;whilecursor isselect*froms_emp;--游 openemp_cursorfetchemp_cursorintovar_emp;--提取數(shù)據(jù)whileemp_cursor%foundloopfetchemp_cursorintovar_emp;--提取數(shù)據(jù)endcloseemp_cursor;0122 endselect*froms_empforupdate into單個變量，單個變量; intorecord變量; intorecord.idcursoremp_cursor(cpar isselect*froms_empwhereid>cpar;--游標(biāo) openemp_cursor(10)fetchemp_cursorintovar_emp;--提取數(shù)據(jù)whileemp_cursor%foundloopfetchemp_cursorintovar_emp;--提取數(shù)據(jù)endcloseemp_cursor;var_nnumber;selectid intovar_nfroms_empwhereid>5;whenTOO_MANY_ROWSthencursoremp_cursor(cpar isselect*froms_empwhereid>cpar;--游標(biāo) openemp_cursor(10)fetchemp_cursorintovar_emp;--提取數(shù)據(jù)whileemp_cursor%foundloopfetchemp_cursorintovar_emp;--提取數(shù)據(jù)endcloseemp_cursor;closewheninvalid_cursor whento_many_rows 系統(tǒng)自定義異常sql selectcount(*)into fromsql v_n>1then endif;select*intov_empfroms_emp; to_many_empsdbms_output.put_line('myexceptionto_many_emps'); selectcount(*)into fromsql v_n>1then endif;select*intov_empfroms_emp;如果你想捕獲所有的異常用othersothers應(yīng)該出現(xiàn)在異常處理的最后 selectcount(*)into fromsql v_n>1then endif;select*intov_empfroms_emp;when dbms_output.put_line('myexcepitonto_many_emps');whenothers plsq的異常處理1要異常2根據(jù)業(yè)務(wù)的不同情況拋出異3othersplsql中最重要的過程和函數(shù)createorreplaceprocedureo100forain1..100loopdbms_output.put_line('oworld');endloop;exec --調(diào)用o100這個過callcreateorreplaceprocedureo100(var_pvarchar2)forain1..100loopendloop;寫一個有名字的過程帶兩個參數(shù) age--問題關(guān)鍵在于拼接sqlcreatesequence createorreplaceaddstudent(var_namevarchar2,var_ageinsertintostudentsqlsqlsqlcreateorreplaceprocedureaddstudent(var_namevarchar2,var_agesqlstrsqlstr:='insertintostudentexecuteimmediatesqlstr;ddl31張表只有名字不同而結(jié)構(gòu)完全相同day1..day31executeimmediate'createtable(idnumberprimary 寫一個過程建立31張結(jié)構(gòu)相同表名不同的表在寫一個過程刪除這31張表createorreplaceaddstudent(var_namevarchar2,var_ageinsertintostudent參數(shù)的默認(rèn)類型是 負(fù)責(zé)輸入的參out-----------負(fù)責(zé)輸出的參數(shù)inout voidadd(int*age){}intage=0; 這個過程是得到s_emp表中記錄createorreplaceproceduregetstudentcount(var_cinoutnumber)selectcount(*)intovar_c froms_emp;out只能在別的過程或者塊中調(diào)用 number;--就是為調(diào)用得到人數(shù)的過程準(zhǔn)備的變outinout查看過程令是descin desc 名字賦值不能出現(xiàn)在位置賦值左 createorreplaceaddstudent(var_namevarchar2,var_ageinsertintostudentcall函 過這個過程是得到s_emp表中記錄createorreplacefunction selectcount(*)intovar_c froms_emp;returnvar_c;var_nnumber;var_pshowerrors寫一個過程得到s_emp寫一個函數(shù)得到s_emp createorreplacepackage pack_testis addstudent(agenumber); getempcountreturnnumber;createorreplacepackagebodypack_testis addstudent(agenumber)is getempcountreturnnumberis--函數(shù)的實(shí)現(xiàn) returnvar_c;selectpack_test.getempcountfromdualdual作業(yè):寫一個包里面有一個函數(shù)一個過程過程和函數(shù)如上一個作業(yè)中再寫一個塊測試所寫的函數(shù)和過ondeletecascadeondeletesetid=100更好的數(shù)據(jù)的完整createorreplacetriggers_student_tafterinsert onstudentdbms_output.put_line('triggercallsuccess');insertintostudentvalues(1,'aa',21); 不管成功不成功都會觸發(fā)after必須是成功執(zhí)行之后觸發(fā)delete0createorreplacetriggerafterupdate ons_empforeachrowdbms_output.put_line('triggercallsuccess');createorreplacetriggers_emp_tafterupdate ons_empdbms_output.put_line('triggercallsuccess');dmlforeachrowPRO*netIP 為了使用c/c++去數(shù)據(jù)庫要開發(fā)proc/c++的程序proc把.pc.cproc編譯gccfirst.c- #include<stdio.h>intmain(){ EXECSQLINCLUDEEXECSQLEXECSQLselectlast_nameinto:last_namefroms_empwhereid=1;EXECSQLCOMMITWORKRELEASE;}判斷一下數(shù)據(jù)庫是否成sqlcaoracle sql0是成功-1 sqlsqlca.sqlerrd[2]sql#include<stdio.h>int EXECSQLINCLUDEEXECSQLCONNECT:namepasswd;{printf("connect{printf("connect}EXECSQLselectlast_nameinto:last_namefroms_empwhereid=1;EXECSQLCOMMITWORKRELEASE;}c/c++cc++的變量sql語句中使用的變量叫sqlcc+sqlccsqlc++中宿主變量使用也是要加:定義必須在申明區(qū)EXEC BEGINDECLARESECTION;EXEC DECLARE#include<stdio.h>intmain(){EXECSQLBEGINDECLARE EXECSQL DECLARESECTION;EXECSQLINCLUDESQLCA;EXECSQLCONNECT:namepasswd;{printf("connect{printf("connect}EXECSQLselectlast_nameinto:last_namefroms_empwhereid=1;EXECSQLCOMMITWORKRELEASE;}proc指令輸出的文件名默認(rèn)總是.c 預(yù)處理時prociname=section.pconame=section.cpp g++section.cpplclntshproc中選項iname輸入的文件名 oname輸出文件名 默認(rèn)xx.pc 默認(rèn)是c風(fēng)格 c++需要用cppparse默認(rèn)是c的FULL none和partia 默認(rèn)是oracle c++推薦用ansi 用用戶名/去檢測過程是否合指示變 組函#include<stdio.h>intmain(){execsqlincludeexecsqlbegindeclarecharuserpasswd[30]="openlab/open123";charlast_name[30]; execsqlenddeclaresection;execsqlconnectexecsqlselectlast_nameinto:last_name:desc_varfroms_empwhereid=1;execsqlcommitwork}除字符串外只支持到一維數(shù)組最大到32767#include<stdio.h>intexecsqlincludeexecsqlbegindeclarecharuserpasswd[30]="openlab/open123";charlast_name[25][30];execsqlenddeclaresection;execsqlconnectexecsqlselectlast_nameinto:last_namefroms_emp;inti;}execsqlcommitwork} 如果想反映每個名字的賦值情況怎么辦？一個指示變量肯定搞不定需要n個#include<stdio.h>intmain(){execsqlincludeexecsqlbegindeclarecharuserpasswd[30]="openlab/open123";charlast_name[25][30];shortexecsqlenddeclareexecsqlconnectexecsqlselectfirst_nameinto:last_name:descfroms_emp;inti;}execsqlcommitwork}sqlcaexecsqlincludeoracaexec oracasqlorastxtf#include<stdio.h>intexecsqlincludesqlca; includeoraca;execoracleoption(oraca=yes);execsqlbegindeclarecharuserpasswd[30]="openlab/open123";charlast_name[25][30]; execsqlenddeclaresection;execsqlconnectexecsqlselectfirst_nameinto:last_name:descfroms_emp;inti;}execsqlcommitwork}plsql建立一個函數(shù)一個過程以備調(diào)createorreplace proceduregetempcount(emp_coutnumber)isselectcount(*)intoemp_cfroms_emp;createorreplace functiongetempcountfunreturnnumberisemp_cnumber;selectcount(*)intoemp_cfroms_emp;returnemp_c;plsqlexecsqlbegin在這里調(diào)用注意函數(shù)的調(diào)用 proc要加 gcccallplsql.c-lclntsh#include<stdio.h>intexecsqlincludeexecsqlbegindeclarecharuserpasswd[30]="openlab/open123"; intexecsqlenddeclaresection;execsqlconnect:userpasswd;execsqlexecuteprintf("pro:%d\nfun:%d",emp_pro,emp_fun);execsqlcommitworkrelease;}oracle charuserp[30]="openlab/open123";charuserp2[30]="scott/tiger";execsqlconnect:userp;execsqlsqlstr;execsqlexecsqlsqlstr;#include<stdio.h>intmain(){execsqlincludeexecsqlbegindeclarecharuserpasswd[30]="openlab/open123";charuserpasswd2[30]="scott/tiger";charcharexecsqlenddeclareexecsqlconnect:userpasswdexecsqlat:db1selectfirst_namefroms_empwhereid=1;execsqlconnect:userpasswdat:db2;execsqlat:db2selectfirst_namefroms_empwhereid=1;printf("pro:%d\nfun:%d",emp_pro,emp_fun);execsqlat:db1commitworkrelease;execsqlat:db2commitwork}如何數(shù)據(jù) cdtnsnames.oraCAH_6=(DESCRIPTION=(ADDRESS_LIST(ADDRESS=(PROTOCOL=TCP)(HOST=)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=)) databaselinkCONNECTTOopenlabIDENTIFIEDBYopen123Using'CAH_6';createdatabase CONNECTTOopenlabIDENTIFIEDBYopen123Using'(DESCRIPTION=(ADDRESS_LIST(ADDRESS=(PROTOCOL=TCP)(HOST=6)(PORT=)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=)注 #include<stdio.h>intmain(){execsqlincludeexecsqlbegindeclarecharuserpasswd[30]="openlab/open123";charlast_name[30];execsqlenddeclaresection;execsqlconnectexecsql selectfirst_nameinto:last_namefroms_emp@mylink3whereid=2;execsql commitworkrelease;}1.net到一個服務(wù)器這個服務(wù)器上必須裝有oracle如果登錄一個數(shù)據(jù)庫的情況下想多個就需要link 上面的是先登錄一個數(shù)據(jù)庫連接多個數(shù)據(jù)#include<stdio.h>intmain(){execsqlincludeexecsqlbegindeclarecharuserpasswd[30]="openlab/open123";charuserpasswd2[30]="scott/tiger";char execsqlenddeclareexecsqlconnect:userpasswdat:db26using'CAH_6';execsqlconnect:userpasswd2at:db20using'CAH_0';execsqlat:db26selectfirst_nameinto:last_namefroms_empwhereid=2;execsqlat:db20selectfirst_nameinto:last_namefroms_empwhereid=2;execsqlat:db26commit