VII. Integration of SQL Foundation and Pl-sql Foundation

--oracle database Important knowledge Point collation 2017-01-24 Soulsjie
/* Data Query statement Introduction
Introduction to Data definition statements
Introduction to Data Control statements
Introduction to Data manipulation statements */
-------------------------------
--I. Create and maintain tables
-------------------------------
--1.1 Create
CREATE TABLE Zyj_gongzi (
Id integer NOT NULL,
Gongzi number,
Fafang_date date,
Us_name varchar (256)
);
--1.2 Maintenance Table
--1.2.1 Add a field
ALTER TABLE Zyj_gongzi ADD Marsk VARCHAR (256);
--1.2.2 Modifying a field
ALTER TABLE Zyj_gongzi MODIFY Marsk VARCHAR (150);
--1.2.3 Deleting a field from a table
ALTER TABLE Zyj_gongzi DROP COLUMN Marsk;
--1.2.4 Renaming a table
ALTER TABLE Zyj_gongzi RENAME to Zyj_rice;
ALTER TABLE Zyj_rice RENAME to Zyj_gongzi;
--1.2.5 Modifying a field name in a table
ALTER TABLE Zyj_gongzi RENAME COLUMN fafang_date to Fafang_riqi;
ALTER TABLE Zyj_gongzi RENAME COLUMN Fafang_riqi to Fafang_date;
--1.2.6 adding comments to a table
COMMENT on table Zyj_gongzi is ' Soulsjie practice SQL created Payroll table ';
--1.2.7 adding comments to a field
COMMENT on COLUMN zyj_gongzi.id is ' payroll table number ';

--------------------------------------
--Ii. classification, creation and use of temporary tables
--------------------------------------

--------------------------------------
--third, delete and truncate the table
--------------------------------------
--1. Deleting a table Delete simply deleting the table data does not release the space that the table data occupies in memory
DELETE TABLE Zyj_gongzi;
--2.drop Table Delete tables structure and data
DROP TABLE Zyj_gongzi;
--3. truncating a table
TRUNCATE TABLE Zyj_gongzi;

--------------------------------------
--Four, display view table information
SELECT table_name from User_tables;
--------------------------------------

--------------------------------------
--Five, Oracle Common data types
--------------------------------------
/*
Text is used for a combination of literals or text and numbers. A maximum of 255 characters.
Memo memo is used for a larger number of text. Stores a maximum of 65,536 characters.
Note: The memo field cannot be sorted. However, they are searchable.
Byte allows a number from 0 to 255. 1 bytes
Integer allows numbers between 32,768 and 32,767. 2 bytes
Long allows all digits between 2,147,483,648 and 2,147,483,647 to be 4 bytes
Single precision floating point. Handles most decimals. 4 bytes
Double-precision floating point. Handles most decimals. 8 bytes
Currency for currency. Supports 15-bit, plus 4 decimal places.
Tip: You can choose which country's currency to use. 8 bytes
The AutoNumber AutoNumber field automatically assigns a number to each record, usually starting at 1. 4 bytes
Date/time for date and time 8 bytes
yes/no logical fields that can be displayed as yes/no, True/false, or on/off. In code, use constants True and False (equivalent to 1 and 0) Note: Null values are not allowed in the Yes/no field, 1 bits
Ole Object can store images, audio, video, or other BLOBs (Binary Large OBjects) up to 1GB
Hyperlink contains links to other files, including Web pages.
The Lookup Wizard allows you to create a list of options that can be selected from the following list. 4 bytes
*/

--------------------------------------
--VI, Pseudo-column rowid and rownum introduction and use
--------------------------------------

--------------------------------------
--seven, use of common Oracle operators and operator
--------------------------------------
SELECT 1+2 from DUAL; --' + '
SELECT 2-3 from DUAL; --‘-‘
SELECT 2*3 from DUAL; --‘*‘
SELECT 2/3 from DUAL; --‘/‘
SELECT MOD (3,2) from dual;--
DECLARE
BEGIN
Dbms_output.put_line (' ss ' | | ' AA ');--Connection character
END;

--------------------------------------
--Eight, simple query
--------------------------------------
--8.1 Date column Use
SELECT fafang_date from Zyj_gongzi;
Use of--8.2distinct
SELECT DISTINCT identi from Zyj_gongzi;
--8.3 handling NULL
--8.4 Connection string
SELECT ' A ' | | ' B ' from DUAL;
--8.5where statements
select* from Zyj_gongzi WHERE gongzi>9000;
--8.6order BY clause | asc| DESC
SELECT us_name from Zyj_gongzi ORDER by ID DESC;
SELECT us_name from Zyj_gongzi ORDER by ID ASC;
--8.7 Multi-column sorting
SELECT * from Zyj_gongzi ORDER by Fafang_date,id desc;--sorted by distribution time, sorted by ordinal size

--------------------------------------
--Nine, complex query
--------------------------------------
--9.1 Group Query and the use of having clauses, the HAVING clause can filter the result input
SELECT AVG (Gongzi) as average salary from Zyj_gongzi GROUP by identi;--calculates the average wage for each category of employee
--9.2 grouping function max| min| Avg| sum| Count uses
--9.3 Connection Query statement
SELECT g.us_name,b.identi_name from Zyj_gongzi g,zyj_bumen B WHERE g.identi=b.identi;
--9.4 Equality Connection Query
--9.5 Unequal Connection Query
--9.6 Self-Connection check
--9.7 Connection and outer connection difference and use
--9.8 the use of subqueries or nested queries
--9.9 the use of Set operators
SELECT * FROM (SELECT ID from Zyj_gongzi UNION select ID from zyj_bumen);--Combine all results and delete duplicate records
SELECT * FROM (select IDs from Zyj_gongzi INTERSECT select IDs from Zyj_bumen);--intersection shows the same data results
SELECT * FROM (select-ID from-Zyj_gongzi minus select ID from zyj_bumen);--difference set A minus B =

--------------------------------------
--10, common built-in functions
--------------------------------------
--10.1 instr| lower| upper| ltrim| rtrim| trim| substr| Replace use
SELECT INSTR (' HELLO ', ' H ') from Dual;--instr (String 1, String 2) returns the position of string 2 at the occurrence of string 1, where the string position is counted from 1
SELECT LOWER (' HELLO ') from DUAL; --Returns the full lowercase string of the specified string
SELECT UPPER (' Hello ') from duall;--returns the full uppercase character of the pointing string
--10.2 LTrim remove whitespace from the left of the string
--10.3 RTrim Remove the space to the right of the string
--10.4 trim to remove spaces on both sides of a string
SELECT SUBSTR (' Zheshizifuchuan ', 10,5) from dual;--to intercept 5 characters from the 10th character of a string
SELECT replace (' Hahajiejie ', ' JIE ', ' QIAN ') from dual;--replaces all JIE in the string with QIAN
--10.5 sysdate| add_months| Last_day (d) | Months_between (D1,D2) | Next_day use
SELECT sysdate from DUAL;
SELECT add_months (sysdate,1) from DUAL;
--10.6 to_char| to_date| To_number using character conversions
SELECT to_number (' 2 ')-from DUAL;
SELECT to_date (' 2018/12/26 ', ' yyyy/mm/dd ') from DUAL;

--------------------------------------
--11, data manipulation statements
--------------------------------------
The--11.1 INSERT statement uses the
INSERT All
Into Zyj_gongzi (id,gongzi,fafang_date,us_name)
VALUES (1,6300,to_date (' 2017-02-16 ', ' yyyy-mm-dd '), ' Jie ')
Into Zyj_gongzi (id,gongzi,fafang_date,us_name)
VALUES (2,6322,to_date (' 2017-03-16 ', ' yyyy-mm-dd '), ' Jies ')
Into Zyj_gongzi (id,gongzi,fafang_date,us_name)
VALUES (3,4855,to_date (' 2017-04-16 ', ' yyyy-mm-dd '), ' Tom ')
Into Zyj_gongzi (id,gongzi,fafang_date,us_name)
VALUES (4,4698,to_date (' 2017-03-16 ', ' yyyy-mm-dd '), ' Bob ')
SELECT 1 from DUAL;
The--11.2 UPDATE statement uses the
UPDATE Zyj_gongzi SET fafang_date=to_date (' 2018-02-16 ', ' yyyy-mm-dd ') WHERE id=1;
The--11.3 DELETE statement uses the
DELETE from Zyj_gongzi WHERE id=4;

--------------------------------------
--12, Transactions and constraints
--------------------------------------
--12.1 Business Introduction
--Transaction: The current session's processing of the data must commit the transaction only if the current session is valid, and this part of the data can be accessed by other sessions
--Commit and ROLLBACK TRANSACTION usage
SELECT * from Zyj_gongzi;
Delete from Zyj_gongzi WHERE id=4;--data is deleted
rollback;--Rollback of deleted operations
--Commit TRANSACTION use: Commit;
--Transactions and locks
--12.2 data Constraint type
--primary Key PRIMARY KEY constraint
--constraint Fk_waijian FOREIGN KEY (ID) REFERENCE zyj_bumen (ID);
--constraint Un_weiyi UNIQUE (ID);
--constraint Ck_jiancha CHECK (id>10);
--Definition and maintenance of data constraint
--alter TABLE zyj_gongzi Add CONSTRAINT pks_zhujian UNIQUE (us_name); Add new constraint
--alter TABLE zyj_gongzi MODIFY CONSTRAINT Pks_zhujian CHECK (id>10); modify constraint
--alter TABLE zyj_gongzi drop CONSTRAINT pks_zhujian;--Delete constraint

--------------------------------------
--13, view index sequence
--------------------------------------
--13.1 creating maintenance and working with views
--Create a view of Personal Department attribution
CREATE VIEW Gerenguishu as SELECT g.us_name,b.identi_name from Zyj_gongzi g,zyj_bumen B WHERE g.identi=b.identi;
Use of the--13.2 view
SELECT * from Gerenguishu;
--13.3 Creating an Index
CREATE INDEX Chazhaoxinxi on Zyj_bumen (id,identi_name);
--sequence creation and use
--synonym creation

--------------------------------------
--14, PL/SQL statements
--------------------------------------
--14.1 PL/SQL statement block introduction and basic syntax
DECLARE
[Variable declaration]
Begin--Start
{function Body}
EXCEPTION
[Exception Handling]
end;--End
--14.2 Defining and using variables
DECLARE
AA integer:=12;
BB VARCHAR: = ' JIE ';
BEGIN
The value of Dbms_output.put_line (' AA ' is: ' | | AA);
The value of dbms_output.put_line (' BB ' is: ' | | BB);
END;
The--14.3 if statement uses the
DECLARE
AA integer:=1;
BEGIN
IF Aa=1 Then
Dbms_output.put_line (' value is 1 ');
Elsif aa=2 Then
Dbms_output.put_line (' value is 2 ');
ELSE
Dbms_output.put_line (' other values ');
END IF;
END;
The--14.4 Case statement uses
DECLARE
AA integer:=5;
BEGIN
Case AA
When 1 then Dbms_output.put_line (' value is 1 ');
When 2 then Dbms_output.put_line (' value is 2 ');
else Dbms_output.put_line (' other values ');
END case;
END;
--14.5 Basic Loop Statement usage
DECLARE
AA integer:=0;
BEGIN
LOOP
EXIT when aa>10;
aa:=aa+1;
Dbms_output.put_line (AA);
END LOOP;
END;
The--14.6 while Loop statement uses
DECLARE
AA integer:=0;
BEGIN
While Aa<10 LOOP
Dbms_output.put_line (AA);
aa:=aa+1;
END LOOP;
END;
The--14.7 for Loop statement uses
DECLARE
AA integer:=0;
BEGIN
For AA in 1..10 LOOP
Dbms_output.put_line (AA);
END LOOP;
END;
--14.8 cursor definition and use
DECLARE
CURSOR YB is SELECT G.id,g.gongzi from Zyj_gongzi G;
Temp yb%rowtype;--Creating a secondary row-level variable
BEGIN
IF not Yb%isopen Then
Open yb;--Opening cursors
Dbms_output.put_line (To_date (sysdate) | | Open success! ');
END IF;
Loop--Looping through data
FETCH YB into temp;
Dbms_output.put_line (' Employee ID: ' | | temp.id| | ' The wages are: ' | | Temp.gongzi);
EXIT when Yb%notfound;
END LOOP;
Close yb;--Closing Cursors
Dbms_output.put_line (sysdate| | ' Close success! ');
END;

--------------------------------------
--15, functions and procedures
--------------------------------------
--functions and stored procedure creation
--15.1 Creating a function
CREATE OR REPLACE FUNCTION F_jie (AA integer) RETURN INTEGER is
Temp INTEGER;
BEGIN
TEMP:=AA;
Dbms_output.put_line (' The number you have entered is ' | | temp);
RETURN AA;
END F_jie;
--15.2 creation Process
CREATE OR REPLACE PROCEDURE P_jie is
Cursor YB is SELECT us_name from zyj_gongzi;--creating cursors
Temp yb%rowtype;--Creating row-level variables
BEGIN
IF not Yb%isopen then OPEN YB;
END IF;
LOOP
EXIT when Yb%notfound;
FETCH YB into temp;--cursor traversal table
Dbms_output.put_line (Temp.us_name);
END LOOP;
END P_jie;
--15.3 calling function
SELECT F_jie (&ad) from dual;--function call
--15.4 Call procedure
BEGIN
P_jie;
END;
--15.5 exception handling and exception throwing
DECLARE
Temp integer:=1;
Temp2 integer:=1;
Err_info exception;--Custom exception
BEGIN
IF TEMP=TEMP2 Then
RAISE Err_info;
END IF;
EXCEPTION when Err_info Then
Dbms_output.put_line (' Warning two ID repeat! ‘);
END;

--------------------------------------
--Appendix, auxiliary table used in connection query
--------------------------------------
CREATE TABLE Zyj_bumen (
ID INTEGER PRIMARY key,--primary key
Identi VARCHAR (,--) sector identification
Identi_name VARCHAR (20)--Department name
);
COMMENT on table zyj_bumen is ' Soulsjie practice SQL created departmental table ';
--Inserting data
INSERT all into Zyj_bumen VALUES (1, ' Laoban ', ' boss Department ')
Into Zyj_bumen VALUES (2, ' Jingli ', ' manager ')
Into Zyj_bumen VALUES (3, ' yuangong ', ' staff Department ')
SELECT 1 from DUAL;
SELECT * from Zyj_bumen;

VII. Integration of SQL Foundation and Pl-sql Foundation