Oracle Learning Series 7

Oracle Learning Series 7************************************************************************************constraint on the associated table: forcibly delete the parent table from the associated table: DROP table tab_name cascade constraint; The constraint itself can be modified, but it is not recommended to modify the constraint Knowledge point:1to master the role and definition of the view2, mastering the use of sequences: SEQUENCE3, mastering the use of PowerDesigner design tools4, understanding synonyms, understanding user management, understanding nested tables and mutable arrays5to understand the design paradigm of the database--------------------------------------------------------1, view: Encapsulates a complex query statement syntax: CREATE VIEW vie W_name asSub-query {with CHECK option| with Read Only};//cannot update view's creation criteria, cannot change view dataCreate a view that contains 20 of all department information: CREATE VIEW VIEW_EMP20  as                          SelectEmpno,ename, Job,hiredate fromEMPwheredeptno= - ; Query view: Sele* fromView_emp20;                                Delete view: Drop view view_name;                                            Ex:drop View View_emp20; Full syntax format: Create or replace view view_name asSub-query//the system will automatically delete and rebuild the user's functionex:create or Replace view VIEW_EMP20  as                         SelectD.dname, Count (e.empno), avg (e.sal), Avg (Months_between (sysdate,e.hiredate)/ A) years fromEMP E, dept Dwheree.deptno=D.deptno GROUP by D.dname;                                 Update View: Modify the department number of 7369 in the view: Update View_emp20 Setdeptno= -                                    whereempno=7369;//Prompt for update success, but depending on the number of employees with no numbered 7369 in the chart--------------------------------------------------------sequence (emphasis) when Oracle completes the automatic sequence growth function, it can only rely on the sequence completion format: Create sequence seq_name [increment by n ] [start with n] [{maxvalue n|Nomaxvalue}] [{minvalue n|Nominvalue}] [{Cycle|Nocycle}] [{Cache n|NoCache}];                    Delete: Drop sequence Seq_name ex: Create a sequence of myseq to verify the autogrow operation: Create sequence myseq increment by2;                                            There are two actions in the sequence: Nextval: Get the next content of the sequence Currval: Get the current contents of the sequence                        Create Tables Test_seq:create table Test_seq (curr number,                                                                                            Next number); Use sequence: INSERT INTO TEST_SEQ (Curr,next) VALUES (myseq.currval,myseq.ne Xtval);--------------------------------------------------------synonym (Understanding): functionality: Allows other users to easily access ' user.table_name ' through a name. Syntax: Create synonym Syn_name forUser.tab_name;//Establishdrop synonym Syn_name; //Deleteex:create synonym Dual forsys.dual; ------------------------------------------SelectSysdate fromDual;//dual is a virtual tableConn SYS/change_on_install asSysdba; Select* fromTabwhereTname='DUAL';//This table exists under the SYS user--------------------------------------------------------User Management (Understanding): Syntax: Create user user_name identified by passwd//Create user (sys user right)[defaulttablespace Default_tablespace] [Temporary tablespace temporary_tablespace] Grant permissions 1, permissions 2, Permissions 3: to user;//authorizing a user to create sessionalter user user_name identified by passwd_new;//Modify User Passwordeg:create user Kevin identified by root;                     Grant Conncet, resource to Kevin; Role: Connect, resource//Each role has a lot of different permissionsSolution/lock User: Alter user user_name accountLock/unlock; Eg:alter User Kevin accountLock/unlock;            Authorization EMP Table: Grant permissions 1, permissions 2 on User_name.tabl_name to user_name; Give Kevin:grant the query and delete permissions of the EMP table under the Scott userSelect, delete on scott.emp to Kevin; Reclaim permissions: Revoke permissions 1, Permissions 2,.. On User.tab_name fromuser_name; Eg:revokeSelect, delete on scott.emp fromKevin; Grant/(Revoke) Permissions 1, permissions 2, permissions 3 on User.tab_name to/( from) user_name; --------------------------------------------------------database Backup and Recovery (understanding): Database backup: Exp D:\data>Exp Database recovery: Imp D:\data>Impf viewing error: Show error--------------------------------------------------------Nested Tables (understanding): Include another child table in one table//Create Project_ty typeCreate Type Project_ty as Object(proid number (4), Proname varchar ( -), prodate date); ///The last one '/' must not be less//using the PORJECT_NT typeCreate Type Project_nt asTable of Project_ty; ///The last one '/' must not be lessCREATE TABLE Department (DEPTNO number (2) Primary Key notNULL, Dname varchar2 ( -) notNULL, Projects project_nt) nested table projects store asproject_nt_tab_temp; For inserting data, you need to specify the data type for each project_ty insert into department (deptno,dname,projects) VALUES (1,'Technical Department', Project_nt (Project_ty (1001,'ERP', Sysdate), Project_ty (1002,'CRM', Sysdate), Project_ty (1003,'OA', Sysdate),)); Enquiry:Select* fromDepartment; If you need to see all the items in a department at this point, you need to query the nested table:Select* fromTableSelectProjects fromDepartmentwheredeptno=1 ) ; Updated: Update table (SelectProjects fromDepartmentwheredeptno=1) ProSetValues (PRO) =project_ty ('1001','Test Project', To_date ('2016-02-12','YYYY-MM-DD'))                    wherePro.proid=1001 ; --------------------------------------------------------variable Array (understanding): An upgraded version of a nested table that restricts the length of the contents of the internal nested table in a mutable array. //Defining TypesCreate Type Worker_info as Object(ID number, name VARCHAR2 ( -), Sex varchar2 (6)                ); ///Defining array TypesCreate Type Worker_info_list asVarray (Ten) of Worker_info; ///creating a mutable array tableCREATE TABLE Department (DEPTNO number (2) Primary Key notNULL, Dname varchar2 ( -) notNULL, workers worker_info_list); //Inserting DataINSERT INTO Department (deptno,dname,workers) VALUES ( -,'Logistics', Worker_info_list (Worker_info (1,'Dustin','F'), Worker_info (2,'Kevin','F'), Worker_info (3,'Allen','M')                                )                         );--------------------------------------------------------Database design Paradigm (understanding):1. First paradigm (ensuring that each column remains atomic)2. Second paradigm (ensure that each column in the table is related to the primary key)3. Third paradigm (ensure that each column is directly related to the primary key column, not indirectly)--------------------------------------------------------Database design Tool (FOCUS): Use of PowerDesigner tools

Oracle Learning Series 7