數據庫教學第7天課件

7PLSQL.DEV1、無論是3個表，4個表，表查詢2N-1N-1ORACLEoracle,不適用其他數據庫SQL>descSQL>desc NullableDefaultNVARCHAR2(50)YRUXUE_TIMEDATE ZIXUNSHI_IDINTEGERYYselectround(345.567,2)fromdual; selectround(345.567,2)fromdual; selectsysdatefromselectsysdatefromselectsysdate,study.*fromselectstud_id,count(*)考試門數,sysdatefromstudygroupby select'testBeijin'原有字符串,re 符串fromdual;--tAstBAijinselect*fromselectuser_id,name,password,re ce(password,'li','AB') ssword,user_typefromuser_regsubstr(string,index,length)index1開始ltrim(strselectselectsubstr('testBeiJing',4,3)fromselectupper('testBeijing'),lower('testBeijing')fromselectLtrim('TestBeijing','')fromdual;--selectRtrim('','fromselecttrim('',fromADD_MONTHS(d,n)n selectselectsysdatefromselectto_char(sysdate,'dd-mm-yyyy')fromselectto_char(sysdate,'yyyy')fromdual;--返回年序號2013selectto_char(sysdate,'mmfromdual;--返回月序號，10selectto_char(sysdate,'ddfromdual;--返回日子序號，9selectt.*,to_char(ruxue_time,'yyyy')fromstudentt四、pl/sql語言設計要點３、sqlwindowoutputSQL>setserverouton;SQLdeclareSQL>setserverouton;SQLdeclare i3 6end;8 PL/SQLproceduresuccessfullycompletedi:=157;--i:=157;-- end;--i五、pl/sqlpl/sql每個變量賦值時變量sqlwindowcommanddeclare namevarchar2(20);ageinteger;birthdaydate;declare namevarchar2(20);ageinteger;birthdaydate;SQL>setserverouton;SQLdeclare namenameagebirthday5 17李海平testpl/sql代碼 １、采用pl/sql語言編寫這些方法，就稱為一個 過２這些方法，就稱為一個 過３、 處理一些計算，統(tǒng)計功能，將結果自動轉換某個數據表 處理一些計算，統(tǒng)計功能，將結果自動轉換某個數據表 八、過程的工作框架１、過程，也稱為過程4個十一 關于分布式事十二 事務處理示例如updatestudysetscore=30.5updatestudysetscore=30.5wherestud_id=10001andupdatestudysetscore=146wherestud_id=10001andwhenothersthen十三 關于數據庫主鍵與索引的關 １、完成有技術分析 關于E－R圖PowerDesignerPDM圖，CDMPowerDesignerPDM圖，CDME-R 4create明細IDcreate明細IDvarchar(20)primaryint,成本價int,零售價int)createcreatetable訂單表訂單 intprimary總金 送貨地址)createcreatetable訂單明細表訂單明細 intprimaryIDintnotIDvarchar(20)notforeignforeignID)明細foreignkey(IDreferences訂單表()４、表，updatet1set購書金額=購書數量*(select明細where 訂單表t1set總金額=(selectsum(購書金額)from訂單明細表t2wheret2.id=t1.ID)十八 ORACLE數據備份與恢expstudsys/orcl@orcl owner=studsysexpstudsys/orcl@orcl owner=studsysfile=oracle-bak-2013-10-9.dmplog=oracle-bak-2013-imps