ORACLE课件 第10章 其他数据库对象

1OracleSQL2SELECTOracle3:1)2)3)4)4ppt4••••5••––––6•SELECT•SELECT7DMLDML8•SELECT.•ORDERBY,CREATE[ORREPLACE][FORCE|NOFORCE]VIEWview[(alias[,alias]...)]ASsubquery[WITHCHECKOPTION[CONSTRAINTconstraint]][WITHREADONLY[CONSTRAINTconstraint]];CREATE[ORREPLACE][FORCE|NOFORCE]VIEWview[(alias[,alias]...)]ASsubquery[WITHCHECKOPTION[CONSTRAINTconstraint]][WITHREADONLY[CONSTRAINTconstraint]];9–ORREPLACEOracle–FORCEOracle–NOFORCEOracle–view–alias–subquerySELECT–WITHCHECKOPTIONDML–constraintCHECKOPTION–WITHREADONLYDMLDML10•10-350CREATEORREPLACEVIEWsalvu50ASSELECTemployee_idID_NUMBER,last_nameNAME,salary*12ANN_SALARYFROMemployeesWHEREdepartment_id=50;CREATEORREPLACEVIEWsalvu50ASSELECTemployee_idID_NUMBER,last_nameNAME,salary*12ANN_SALARYFROMemployeesWHEREdepartment_id=50;DESCsalvu50DESCsalvu50SELECT*FROMsalvu50SELECT*FROMsalvu5011DML•EMP_DML•v_emp1•DMLCREATETABLEemp_dmlASSELECTemployee_id,last_name,salaryFROMemployeesWHEREdepartment_id=50;CREATETABLEemp_dmlASSELECTemployee_id,last_name,salaryFROMemployeesWHEREdepartment_id=50;CREATEORREPLACEVIEWv_emp1ASSELECTemployee_id,salaryFROMemp_dml;CREATEORREPLACEVIEWv_emp1ASSELECTemployee_id,salaryFROMemp_dml;UPDATEv_emp1SETsalary=salary+100;UPDATEv_emp1SETsalary=salary+100;12••GROUPBY•DISTINCT•ROWNUM13••GROUPBY•DISTINCT•ROWNUM•14••GROUPBY•DISTINCT•ROWNUM••NOTNULL15WITHCHECKOPTION•WITHCHECKOPTION“CHECK”CHECKWHEREDMLCHECK16WITHCHECKOPTION•10-6WITHCHECKOPTION•WHERE“employee_id=141”v_emp3DMLemployee_id141CREATEORREPLACEVIEWv_emp3ASSELECTemployee_id,salaryFROMemp_dmlWHEREemployee_id=141WITHCHECKOPTIONCONSTRAINTv_emp3_ck;CREATEORREPLACEVIEWv_emp3ASSELECTemployee_id,salaryFROMemp_dmlWHEREemployee_id=141WITHCHECKOPTIONCONSTRAINTv_emp3_ck;17WITHREADONLY•WITHREADONLYDML•10-7WITHREADONLY,v_emp4•UPDATEv_emp4SETsalary=salary+100;UPDATEv_emp4SETsalary=salary+100;CREATEORREPLACEVIEWv_emp4ASSELECTemployee_id,salaryFROMemp_dmlWITHREADONLYCREATEORREPLACEVIEWv_emp4ASSELECTemployee_id,salaryFROMemp_dmlWITHREADONLY18•DROPVIEWviewDROPVIEWview19•InlineView,SQLSQLFROM•10-8SELECTlast_name,department_nameFROMDEPARTMENTSa,(SELECTlast_name,department_idFROMEMPLOYEES)bWHEREa.department_id=b.department_idSELECTlast_name,department_nameFROMDEPARTMENTSa,(SELECTlast_name,department_idFROMEMPLOYEES)bWHEREa.department_id=b.department_id20Top-N•Top-NN•Top-NSELECT[],ROWNUMFROM(SELECT[]FROMORDERBYTop-N)WHEREROWNUM=N;SELECT[],ROWNUMFROM(SELECT[]FROMORDERBYTop-N)WHEREROWNUM=N;21Top-N•SQL•select*from(select*from•(select*fromemployeesorderbysalarydesc)•whererownum=*orderbysalary)•whererownum=orderbysalarydesc;22Top-N•ROWNUM•ROWNUM12••ROWNUM==Between..And23••––––––24••CREATESEQUENCECREATEANYSEQUENCE•USER_SEQUENCESCREATESEQUENCE[schema.][INCREMENTBYn][STARTWITHn][MAXVALUEn|NOMAXVALUE][MINVALUEn|NOMINVALUE][CYCLE|NOCYCLE][CACHEn|NOCACHE];CREATESEQUENCE[schema.][INCREMENTBYn][STARTWITHn][MAXVALUEn|NOMAXVALUE][MINVALUEn|NOMINVALUE][CYCLE|NOCYCLE][CACHEn|NOCACHE];25––sequence–INCREMENTBYnn1–STARTWITHnn1–MAXVALUEn–NOMAXVALUE1027-1–MINVALUEn–NOMINVALUE11026–CYCLE|NOCYCLENOCYLENOCYLE–CACHEn|NOCACHEOracleNOCACHECACHE2026•1120•INCREMENTBYnn01•STARTWITHn1•MAXVALUEnn•NOMAXVALUE1027-127•MINVALUEnn•NOMINVALUE1-1026•CYCLENOCYCLE•CACHEnn2028•10-12test_seqCREATESEQUENCEtest_seqSTARTWITH10——10INCREMENTBY2——2MAXVALUE100——100MINVALUE9——9CYCLE——21009CACHE10——10CREATESEQUENCEtest_seqSTARTWITH10——10INCREMENTBY2——2MAXVALUE100——100MINVALUE9——9CYCLE——21009CACHE10——1029nextvalcurrval•30nextvalcurrval•SELECT•INSERT•INSERT•UPDATE••••SELECT,DELETE,UPDATE•31•student_seq•student_seqstudentsid•student_seqSELECTstudent_seq.CURRVALFROMdual;SELECTstudent_seq.CURRVALFROMdual;INSERTINTOstudentVALUES(student_seq.NEXTVAL,'Scott','ComputerScience',11);INSERTINTOstudentVALUES(student_seq.NEXTVAL,'Scott','ComputerScience',11);CREATESEQUENCEstudent_seqSTARTWITH10000INCREMENTBY1;CREATESEQUENCEstudent_seqSTARTWITH10000INCREMENTBY1;32••ALTERANYSEQUENCE•STARTWITHALTERSEQUENCE[schema.][INCREMENTBYn][MAXVALUEn|NOMAXVALUE][MINVALUEn|NOMINVALUE][CYCLE|NOCYCLE][CACHEn|NOCACHE];ALTERSEQUENCE[schema.][INCREMENTBYn][MAXVALUEn|NOMAXVALUE][MINVALUEn|NOMINVALUE][CYCLE|NOCYCLE][CACHEn|NOCACHE];33••ALTERSEQUENCEtest_seqINCREMENTBY4MAXVALUE100——100200NOCACHEALTERSEQUENCEtest_seqINCREMENTBY4MAXVALUE100——100200NOCACHEALTERSEQUENCEtest_seqINCREMENTBY4——4MAXVALUE1000——1000NOCACHE——ALTERSEQUENCEtest_seqINCREMENTBY4——4MAXVALUE1000——1000NOCACHE——34•DROPANYSEQUENCE••10-15student_seqstudent_seqDROPSEQUENCEstudent_seq;DROPSEQUENCEstudent_seq;DROPSEQUENCE[schema.]DROPSEQUENCE[schema.]35•:–schema–Oracle–,I/O––Oracle3637••:PRIMARYKEYUNIQUE,Oracle.•:38•.•EMPLOYEESLAST_NAME.CREATEINDEXindexnameONtable(column[,column]...);CREATEINDEXindexnameONtable(column[,column]...);CREATEINDEXemp_last_name_idxONemployees(last_name);Indexcreated.CREATEINDEXemp_last_name_idxONemployees(last_name);Indexcreated.39••CREATETABLEe1ASSELECT*FROMemployees;INSERTINTOe1SELECT*FROMe1;--UPDATEe1SETemployee_id=ROWNUM;--employee_idcommit;--CREATETABLEe1ASSELECT*FROMemployees;INSERTINTOe1SELECT*FROMe1;--UPDATEe1SETemploye