Collation of Oracle commands and related functions

If you are confused about Oracle commands and functions, you can use the following articles to learn about its practical application and functions. The following is a detailed introduction of the article.

Oracle commands and some functions

Oracle online log file recovery

SQL * PLUS command SQL programming manual

Oracle9i analysis functions

Set linesize 500

Set pagesize 100

Run or r or (Symbol) indicates that the previous command is re-executed.

 
 

  
  
alter session set NLS_date_format='YYYY-MM-DD';  
 
 

Modify a user's session to modify the Date Format

The default date format is: 'dd-MON-YY'

 
 

  
  
SQL> insert into scott.emp(empno,ename,sal) 
values(&employeeid,'&name',&employeesal); 
 
 

Enter the value of employeeid: 9001

Enter the value of name: jiake02

The value of employeesal is 4000.

Original Value 2: values (& employeeid, '& name', & employeesal)

New Value 2: values (9001, 'jiake02', 4000)

During Oracle commands and some functions, this command can be used multiple times, for example, by symbol/and then press enter, you can repeatedly input values.

Use subqueries in insert statements. For example

 
 

  
  
insert into managers(id,name,salary,hiredate)  
  
  
select empno,ename,sa,hiredate  
  
  
from emp  
  
  
where job='manager'; 
 
 

02

1. Each SQL statement in Oracle is a transaction. When commit or rollback is used to end the transaction

2. savepoint update_dept; set the save point;

You can use the rollback to update_dept; Method to Solve the Problem of step-by-step processing or selective execution;

Orcale connects two strings and uses two |, while SQL uses &

% Represents Multiple Arbitrary characters, and _ represents one arbitrary character.

Is null is used to determine whether it IS NULL

Iii. lesson 03

SQL functions:

Single Row function: ----> General/Character/Number/Date/Conversion

1. Character Functions

In Oracle commands and some functions, convert the function ----> LOWER select lower (ename) from scott. emp;

/UPPER select * from scott. emp where ename = upper ('King ');

The first letter of/INTCAP is in upper case, and the other letters are in lower case.

SQL> select initcap (ename) from scott. emp;

INITCAP (EN

Smith

Character manipulation functions ------> CONCAT

Select empno, concat (ename, job) from scott. emp; <=> select ename | job from scott. emp;

Empno concat (ENAME, JOB)

7369 SMITHCLERK

7499 ALLENSALESMAN

/LENGTH

1. select empno, ename, length (ename) from scott. emp;

Empno ename length (ENAME)

The above content is an introduction to Oracle commands and some functions. I hope you will get something better.