Source-pl/sql from Beginner to proficient-seventh-operation data Sheet

In addition to sequences (Sequence) and synonyms (synonym), other statements are mostly used in the iterm data that they create, so this chapter does not feel difficult.

--7th Chapter start--7.1.2 Insert single line record select * from Emp;insert to EMP (empno, ename, Job, Mgr, HireDate, Sal, comm            , Deptno) VALUES (7890, ' Liu Qi ', ' deputy manager ', 7566, to_date (' 2001-08-15 ', ' yyyy-mm-dd '), 8000, 300, 20                        );             INSERT into EMP VALUES (7891, ' Liu Qi ', ' deputy manager ', 7566, to_date (' 2001-08-15 ', ' yyyy-mm-dd '), 8000, 300, 20);            --7.1.3 inserting default values and Null values--default values INSERT INTO EMP (empno, ename, Deptno) VALUES (7892, ' eight ', 20          );                    SELECT * from emp WHERE empno = 7892;            INSERT into EMP (empno, ename, Deptno) VALUES (7892, ' eight ', 20);                   INSERT into EMP (empno, ename, Deptno) VALUES (7898, ' O ' Malley ', 20);                        --Insert Null value SELECT * from EMP; Insert into Empvalues (7893, ' Ho Kiu ', null,null,null,null,null,20); insert into empvalues (7894, ' Huo X.', ', NULL, ' ', null,null,20); INSERT into EMP VALUES (7895,user,null,null,trunc (sysdate), 3000,200,20); SELECT * from emp WHERE empno = 7895;  --7.1.4 inserting multiple rows of data using a subquery DROP TABLE emp_copy; CREATE TABLE emp_copy as SELECT * from EMP WHERE 1=2;    INSERT into Emp_copy SELECT * from emp WHERE deptno = 20;        SELECT * from EMP;            INSERT into Emp_copy SELECT * from emp WHERE deptno = 20; ---Specify the column name to insert data insert into Emp_copy (empno, ename, Job, Mgr, Deptno) SELECT empno, ename, Job, Mgr, Deptno F    ROM emp WHERE deptno = 30;  --Insert multiple table data using Insert CREATE table emp_dept_10 as SELECT * from EMP WHERE 1=2;  CREATE TABLE emp_dept_20 as SELECT * from EMP WHERE 1=2;  CREATE TABLE emp_dept_30 as SELECT * from EMP WHERE 1=2;               CREATE TABLE emp_copy as SELECT * from EMP WHERE 1=2;  Insert first when deptno = 10--If department number is emp_dept_10--then insert to emp_dept_10 table when Deptno = 20--If department number is into Emp_dept_20--insert into emp_dept_20 table when deptno = 30--If the department number is and into emp_dept_30--insert INTO emp_dept_30 table ELSE--if Deptno is not 10, 20, or emp_copy--insert to Emp_copy Table SELECT * from EMP; --Query all data in the EMP table, insert into target table SELECT rowid,x.* from EMP x;select * from emp_dept_20truncate table emp_dept_10; TRUNCATE TABLE emp_dept_20; TRUNCATE TABLE emp_dept_30; TRUNCATE TABLE emp_copy; Insert first when deptno = 10--If department number is 10THEN into emp_dept_10--insert to emp_dept_10, use values to specify fields (em Pno, ename, Sal, Deptno) VALUES (empno, ename, Sal, deptno) when deptno = 20--If the department number is 20THEN into emp_dept_20--insert INTO EMP_DEP T_20, using values to specify the field (Empno, ename) VALUES (empno, ename) When deptno = 30--If the department number is 30THEN into emp_dept_30--insert INTO emp_dept_30 , use values to specify the field (Empno, ename, HireDate) VALUES (empno, ename, HireDate) ELSE--If the department number is not 10, 20, or 30INTO emp_copy--insert INTO EMP_COP Y, use values to specify the field (Empno, ename, Deptno) VALUES (empno, ename, Deptno) SELECT * from EMP;     --Specify INSERT subquery commit; SELECT * FROM emp_dept_10;--7.2.1 update single-line record select * from EMP; UPDATE EMP SET sal=3000 WHERE empno=7369; UPDATE emp SET sal=3000,comm=200,mgr=7566 WHERE empno=7369; SELECT * from EMP;                  Select AVG (y.sal) from emp y WHERE y.deptno=20;--7.2.2 update record with subquery updates emp x SET x.sal = (SELECT avg (y.sal)   From emp y where Y.deptno = x.deptno) where x.empno = 7369;   UPDATE emp SET sal = (SELECT sal from emp where empno = 7782) where empno = 7369;  SELECT * from Emp_history;    DROP TABLE emp_history;                           UPDATE emp x SET (x.sal, X.comm) = (SELECT AVG (y.sal), MAX (Y.comm) from Emp y  where Y.deptno = X.deptno) where x.empno = 7369; CREATE TABLE emp_history as SELECT * from EMP;                           UPDATE emp_history x SET (x.sal, X.comm) = (SELECT sal, comm from Emp y   where Y.empno =x.empno) where x.empno = 7369;  SELECT * from Emp_history; SELECT * from EMP; --Update updates using the form of multiple table associations (SELECT x.sal sal, Y.sal sal_history, X.comm comm, y. comm Comm_history from emp x, emp_history y WHERE x.empno = y.empno and x.empno = 7369) SET sal_histor                            y = sal, comm_history = comm; UPDATE/*+bypass_ujvc*/(SELECT x.sal sal, Y.sal sal_history, X.comm comm, Y.comm comm_history from emp x, Emp_h                            Istory y WHERE x.empno = y.empno and x.empno = 7369) SET sal_history = sal, comm_history = comm; DELETE from EMP WHERE empno=7894 inserts into Empvalues (7894, ' Huo X. ', ', DEFAULT, ' , null,null,20);--7.2.3 use merge to merge table rows select * from Emp_copy; SELECT * from EMP; MERGE into Emp_copy C--target table USING emp E--source table, can be a table, view or subquery on (c.empno = e.empno) when MATC             HED then-when matching, update operation update SET c.ename = e.ename, C.job = e.job, c.mgr = E.mgr,        C.hiredate = e.hiredate, c.sal = e.sal, C.comm = e.comm, C.deptno = E.deptno when not matched then -When not matched, enterLine Insert Action Insert VALUES (E.empno, E.ename, E.job, E.mgr, E.hiredate, E.sal, E.comm, E.deptno);         --7.3.1 Delete single-line record delete from emp WHERE empno=7903;   DELETE from dept WHERE Deptno=20;            SELECT * FROM Dept;                       --7.3.2 Delete a record using a subquery delete from the emp WHERE deptno = (SELECT deptno from Dept            WHERE dname = ' sales department ');           DELETE from Emp_copy; DELETE from EMP x where EXISTS (SELECT 1 from emp_copy where empno = X.empno );--equivalent statement delete from EMP x WHERE empno in (SELECT empno from Emp_copy WHE RE empno = x.empno);--7.3.3 use truncate to clear table data truncate tables dept; ALTER TABLE Dept ENABLE CONSTRAINT Pk_dept; ALTER TABLE emp DISABLE CONSTRAINT pk_emp; CREATE TABLE dept_copy as SELECT * from dept; TRUNCATE TABLE dept;insert to Dept SELECT * from Dept_copy;----7.5.2 Creating data series Create SEQUENCE Invoice_seQincrement by 1START with 1MAXVALUE 9999999NOCYCLE NOCACHE; SELECT Object_name,object_id,object_type from user_objects WHERE object_name = ' invoice_seq ';      SELECT Sequence_name, Min_value, Max_value, increment_by, last_number from User_sequences; SELECT Invoice_seq. Currval,invoice_seq.       Nextval from DUAL; SELECT Invoice_seq.    Currval from DUAL; SELECT Invoice_seq. Currval,invoice_seq.         Nextval from DUAL;                                       CREATE TABLE Invoice (invoice_id number PRIMARY KEY,--auto-numbered, unique, not empty vendor_id # NOT NULL, --Vendor ID Invoice_number VARCHAR2 () not NULL,--Invoice number Invo                             Ice_date Date DEFAULT sysdate,--Invoice date invoice_total number (9,2) not NULL,  --Total invoice payment_total number (9,2) DEFAULT 0 – Total Payment) SELECT * from Invoice;insert Into Invoice (invoice_id, vendor_id, Invoice_number, Invoice_total)     VALUES (Invoice_seq. Nextval, ' INV ' | | Invoice_seq.                        Currval, 100);          SELECT invoice_id, vendor_id, Invoice_number, invoice_total from invoice;              ALTER SEQUENCE invoice_seqincrement by 2ALTER SEQUENCE invoice_seq INCREMENT by 2 MAXVALUE 10                                          NOCACHE nocycle; SELECT * from SCOTT. EMP; SELECT * from EMP; SELECT userenv (' LANG ') from DUAL;  CREATE public synonym scottemp for scott.emp;  DROP public synonym scottemp;

Source-pl/sql from Beginner to proficient-seventh-operation data Sheet