Oracle and SQL*Plus

在sqlplus中使用emacs编辑sql

$ORACLE_HOME/sqlplus/admin:

login.sql

define _editor='emacs -nw' //在terminal中edit
// define _editor=vim
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80

column global_name new_value gname
set termout off
select lower(user) || '@' ||
decode(global_name, 'ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM', 'ORCL.10G', 
global_name) global_name 
from global_name;
set sqlprompt '&gname>'
set termout on

In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.
The syntax for the decode function is:

decode( expression , search , result [, search , result]... [, default] )

For example:
You could use the decode function in an SQL statement as follows:

 SELECT supplier_name,
    decode(supplier_id,
 	      10000, 	'IBM',
    	      10001, 	'Microsoft',
    	      10002, 	'Hewlett Packard',
    	      'Gateway') result
    FROM suppliers;

The above decode statement is equivalent to the following IF-THEN-ELSE statement:

    IF supplier_id = 10000 
    THEN
         result := 'IBM';
    ELSIF supplier_id = 10001 
    THEN
         result := 'Microsoft';
    ELSIF supplier_id = 10002 
    THEN
         result := 'Hewlett Packard';
    ELSE
        result := 'Gateway';
    END IF;

The decode function will compare each supplier_id value, one by one.

select * from
 (select a.*, rownum rn
 from (select * from all_users) a
 )
where rn between 25 and 27

select * from
    (select a.*, rownum rn   from all_users a where rownum <=27)
 where rn >=25;