PL/SQL Common instruction Collection

1.select statements

SELECT * FROM Dept D displays all fields in the dept (Alias D) Table

SELECT deptno, loc from dept display deptno and LOC field contents in Dept table

You can also use arithmetic operations with functions such as SAL+30,SAL+NVL (comm,0) in statements

2. WHERE clause

SELECT Ename,job from EM

where job= ' clerk ' displays fields for job clerk

You can also use the comparison operator in the WHERE clause >,<,<>,<=,>=

Between ... (Between two values)

In (...,....,...,) (matches the listed values)

Like (pattern that matches one character)

Is null (null value)

Connecting multiple conditional and,or,not with logical operators (priority comparison operation >not>and>or)

3. ORDER BY clause

SELECT Ename,job,deptno from EMP

Order by Deptno Sorted by DEPTNO (ASC can be hidden)

..... ... desc. .................. ....... Descending arrangement

Can be followed by multiple fields, successively arranged in order

4. Group functions and groups by clauses

SELECT Deptno, AVG (SAL) from EMP

Group BY Deptno shows the deptno and average wage in emp grouped by deptno

The group function has AVG (..) averaging sum (..) Sum count (...) Count Max (..) To find the maximum value

Min (..) Find the minimum value StdDev (..) Standard deviation Variance (..) Variance

5. HAVING clause

SELECT Deptno, Max (SAL) from EMP
GROUP by Deptno
Having Max (SAL) >2900 shows the EMP table grouped by Deptno maximum wage greater than 2900

The having function is equivalent to the WHERE function when using a grouping statement

6. Insert statement

INSERT into Dept (Deptno, Dname, loc)
VALUES (' development ', ' DETROIT ') are inserted in the Dept table to correspond to the value one by one

When inserting the entire data, it is possible to write values in the order of the following fields, in the installation table, as follows:

INSERT into Dept
VALUES (all, ' FINANCE ', null) NULL is a null value

The inserted value can be followed by the Select,where statement

7. UPDATE statement

UPDATE EMP
SET Deptno = 20
WHERE empno = 7782 modifies the Denptno in the EMP table empno equals 7782 to 20

8. Delete statement

DELETE from Dept
WHERE dname = ' development ' Delete data dname development in Dept table

Do not use the WHERE clause to delete an entire table

9, SavePoint and rollback

SavePoint A1 Storage A1 Storage points

Rollback to A1 back to A1 Storage Point State

10. Build a table

CREATE TABLE Dept 2 (deptno number (2),
Dname VARCHAR2 (14),
Loc VARCHAR2 (13)) establishes the DEPT2 table below the fields and attributes respectively

You can use the SELECT statement to select a field (where 1<>1) or data in an existing table when you build a table

11, modify the table

ALTER TABLE Dept30
Add (Job VARCHAR2 (9)) modify the Dept30 table to increase the job field

ALTER TABLE Dept30

MODIFY (ename VARCHAR2 (15)) Modify Ename property in Deptno varchar2 property

ALTER TABLE test

Rename column name to dname renaming the Name field in the Test table dname fields

12. Delete a table

drop table Dept30 Delete dept30 tables

13. Change the name of the object

RENAME Dept to Department Renaming Dept table to Department

14. All records of the interception table

TRUNCATE TABLE Department Delete all data in the department

15. Add comments

COMMENT on TABLE EMP
Is ' Employee information '

SELECT * from All_col_comments displays the annotated

User_col_comments

All_tab_comments

User_tab_comments

16. Create a constraint

CREATE TABLE EMP
(empno number (4),
Ename VARCHAR2 (10),
Deptno number (7,2) is not NULL,
CONSTRAINT EMP_EMPNO_PK
PRIMARY KEY (EMPNO)); The following two lines create the constraint primary key to Empno

CONSTRAINT dept_dname_uk Unique (dname)); Uniqueness constraint

CONSTRAINT dept_deptno_pk PRIMARY Key (DEPT_NO) PRIMARY KEY constraint

CONSTRAINT emp_deptno_fk FOREIGN KEY (DEPTNO)
REFERENCES Dept (DEPTNO)); External inspection constraints

CONSTRAINT Emp_deptno_ck
Check (DEPTNO between) Check constraint can only be between 10-99

17, plus constraints

ALTER TABLE EMP

ADD CONSTRAINT EMP_MGR_FK

FOREIGN KEY (MGR) REFERENCES EMP (empno);

18. Remove Constraints

ALTER TABLE EMP
DROP CONSTRAINT EMP_MGR_FK;

19. Invalidate the constraint

ALTER TABLE EMP
ENABLE CONSTRAINT EMP_EMPNO_PK;

20. View Constraints

SELECT * from User_constraints
WHERE table_name = ' EMP ';

21. Create a View

CREATE VIEW Empvu10
As SELECT empno, ename, Job
From EMP
WHERE Deptno = 10

22. Modify the View

CREATE OR REPLACE VIEW empvu10
(Employee_number, Employee_Name, Job_title)
As SELECT empno, ename, Job
From EMP
WHERE deptno = 10;

23. Using the WITH CHECK option clause

Using with CHECK option allows DML operations to be limited to the extent that the view contains.

CREATE OR REPLACE VIEW empvu20
As SELECT *
From EMP
WHERE Deptno = 20
With CHECK OPTION CONSTRAINT empvu20_ck;

24. Delete View

DROP view view;

25. Create serial number

CREATE SEQUENCE Dept_deptno
INCREMENT by 1
START with 91
MAXVALUE 100
NOCACHE
Nocycle;

? Nextval returns the next available serial number value
? Currval contains the current serial number value

26. Create an index

CREATE INDEX Emp_ename_idx
On EMP (ename);

27. Create synonyms

CREATE synonym D_sum

For Dept_sum_vu;

28. Grant Permissions

GRANT CREATE TABLE, create sequence, create view
to Scott;

29. Create a role

CREATE ROLE Manager

30. Change your account password

ALTER USER Scott

Identified by Lion

31. Grant Object permissions

GRANT Select
On EMP
To sue, Rich

With Grant OPTION to delegate this permission

32. Permission to retract objects

REVOKE Select, insert
On Dept
From Scott; Revoke permissions

PL/SQL Common instruction Collection