Oracle learning transcript

Set serveroutput on OPEN Server Screen Display Information
When you compile an SQL statement on set sqlblanklines, the ignore space function is enabled.

Save c:/sqlplus_test01.txt -- save
Edit -- open notepad to edit
Get c:/sqlplus_test01.txt -- transfer the information in notepad to the cache
List -- display information in the cache
Desc deptment -- display the table definitions in the department table] Structure
L2 4 -- display the information from the second row to the fourth row
Change -- modify the content in the cache
C/N/M -- change N to M
Del 2 5 -- delete the information of rows 2 to 5
Help index -- help
? Set -- Keyword help information starting with set
Col ID format A10 heading 'department number' -- display the field name ID as a department number, character type, and length as 10
Col bytes format 999,999,999 -- display the field name bytes as the specified format

 

**************************************** ***********************
Set linesize 50
Ttitle Center "my title" Skip 1-
Left "test report" right "page "-
Format 999 SQL. PNO skip 2
Select * from Dept;

-- Output result
My title
Test Report page 1
 
Deptno dname log
---------------------------------------
10 accounting new york
20 research dalias
30 sales chicago
40 operations boston

**************************************** ***********************

Ttitle off -- disable the title
Select * from dept;

-- Output result
 
Deptno dname log
---------------------------------------
10 accounting new york
20 research dalias
30 sales chicago
40 operations boston

**************************************** ***********************
Select * from deptment;

-- Output result
Id name
------------------
01 department
02 department
03 department B
04 department B
05 C department

Break on name -- remove the duplicate value from the name field

Select * From deptment;

-- Output result
ID name
------------------
01 department
02
03 department B
04
05 C department

Comp count label "count" of id on name -- summarize grouping Fields
Select * from deptment;

-- Output result

Id name
---------------------------
01 department
02 department
------------*************
2 count
03 department B
04 department B
------------*************
2 count
05 C department
------------*************
1 count

 

**************************************** ***********************
DDL Data Definition Language
1. create
Create table abc (a varchar2 (10), B char (10 ));
2. alter
Alter table abc add c number;
Alter table abc drop column c;
3. drop
DCL Data Control Language
1. grant
Grant select on dept to tt;
Conn tt/tt11
Select * from scott. dept;
2. revoke
Revoke select on dept from tt;
Conn tt/tt11
Select * from scott. dept
DML data operation language
1. select
Select * from abc;
2. insert
Insert into abc (a, B) values ('abc', 'xy ');
Insert into abc values ('bcd', '20140901 ');
3. delete
Delete from abc where a = 'abc ';
4. update
Update abc set B = 'ttt' where a = 'abc ';

**************************************** ***********************
Common system functions
1. Character
Lenth, ltrim, replace, rtrim, substr, trim

2. Date
Sysdate, current_date, next_day
3. Conversion
To_char, to_date, to_number
4. Aggregate functions
Sum, avg, max, min, count
5. Others
User, decode, nvl

Select length ('abcdef ') from dual;

-- Output result

Length ('abcded ')
---------------------
6

Select length ('abc good ef ') from dual;

-- Output result

Length ('abcded ')
---------------------
6

Select lengthb ('abc good ef ') from dual;

-- Output result

Lengthb ('abcded ')
---------------------
7

Select ltrim ('abc good ef ') from dual;

-- Output result

Ltrim ('
-----------
Abc good ef

Select rtrim ('abc good ef ') from dual;

-- Output result

Rtrim ('AB
----------
Abc good ef

Select trim ('abc good ef ') from dual;

-- Output result

Trim ('a
---------
Abc good ef

Select substr ('abcdefg', 2, 3) from dual;

-- Output result
Sub
-----
Bcd

Select sysdate from dual;

-- Output result

Sysdate
---------------
21-6 months-10

Select current_date from dual;

-- Output result

Current_date
---------------
21-6 months-10

Alter session set nls_date_format = 'dd-mon-yyyy hh: mi: ss ';
Select current_date from dual;

-- Output result

Current_date
-------------------
-2010 03:44:22

Select next_day (sysdate, 'weday') from dual;

-- Output result

Next_day (sysdate, 'weday ')
-------------------------------------
-2010 03:47:03

Select to_char (interval date, 'yyyy-mm-dd hh: mi: ss') from dual;

-- Output result

To_char (sysdate, 'yy
---------------------------
2010-06-21 03:54:08

Select to_date ('12-March-10') from dual;

-- Output result

To_date ('12-March-10)
---------------------------
12-3-0010 12:00:00

Select to_number ('123') from dual;

-- Output result

To_number ('123 ')
---------------------------
333

Select user from dual;

-- Output result

User
--------------------------------------------
Li

Select * from e;

-- Output result

Eid ename sex
--------------------------------------
001 Zhao 1 male
002 Qian 2 male
003 sun 3 male
003 LI 4 female
003 week 5 female

Select sum (decode (sex, 'male',) Number of men, sum (decode (sex, 'female ',) Number of women from e;

-- Output result

Men and women
-----------------------
3 2

Select * from aa;

-- Output result

A1 a2 a3
---------------------------------------------
Abc xyz aa
Bca dee aa
Abc ttt aa
CBA aa
Bbb aa
Abc ggd aa
Abc dfd aa

Select a1, nvl (a2, 'uninput') from aa;

-- Output result

A1 a2 a3
---------------------------------------------
Abc xyz aa
Bca dee aa
Abc ttt aa
CBA does not enter aa
Bbb is not input aa
Abc ggd aa
Abc dfd aa

Select * from aa where a2 is null;

-- Output result

A1 a2 a3
---------------------------------------------
CBA aa
Bbb aa

 

 

 

 

**************************************** ***********************

-- Cursor attributes
% FOUND
% ISOPEN
% NOTFOUND
% ROWCOUNT

-- Cursor and record set
**************************************** *************************************
Declare
Cursor mycur is
Select * from book;
Myrecord books % rowtype; -- defines the same type size of the record set and the books table.
Begin
Open mycur;
Fetch mycur into myrecord;
While mycur % fount loop
Dbms_output.put_line (myrecord. books_id | ',' | myrecord. books_name );
Fetch mycur into myrecord;
End loop;
Close mycur;
End;
/
-- Output result:
0001 Chinese Literature
0002, Foreign Literature
0003, English Reading
0004, architectural art
0005, computer entry
0006, Numerical Algorithm
0007, C Language
**************************************** *************************************

-- A cursor with Parameters
**************************************** *************************************
Declare
Cursor cur_para (id varchar2) is
Select books_name from books where book_id = id;
T_name books. books_name % type;
Begin
Open cur_para ('20140901 ');
Loop
Fetch cur_para into t_name;
Exit when cur_para % notfound;
Dbms_output.put_line (t_name );
End loop;
Close cur_para;
End;
/

-- Output result:
Chinese Literature

**************************************** *************************************

-- Use a cursor to modify table data
**************************************** *************************************
Declare
Cursor cur is
Select name from deptment for update;
Text varchar2 (10 );
Begin
Open cur;
Fetch cur into text;
While cur % found loop
Update deptment set name = name | '_ t' where current of cur;
Fetch cur into text;
End loop;
Close cur;
End;
/

-- Output result:
Id name
------------------
01 Department A _ t
02 department B _ t
03 C department _ t
04 D Department _ t
05 E department _ t

**************************************** *************************************

-- The cursor does not need to be opened or closed in the for loop.
**************************************** *************************************
Begin
For cur in (select name from deptment) loop
Dbms_output.put_line (cur. name );
End loop;
End;
/

-- Output result
Department A _ t
Department B _ t
Department C _ t
Department D _ t
Department E _ t

**************************************** *************************************