用系统sysdba权限登录后：查询oracle实例的方法：

方法一：sql”select instance_name from v$instance“;
方法二：使用show命令”show parameter instance“。

# Oracle中如何显示当前的所有用户表 
# 显示某用户所有表(例如SCOTT,必须大写)
# select TABLE_NAME from all_tables where owner = 'SCOTT';
# 显示当前的所有用户表
select * from user_tables
# 显示当前数据库的所有表
select * from tab;
# 显示当前数据表空间名

你可以采用sys用户登陆

select     tablespace_name    from   dba_tables   where table_name = upper('你操作的表名')

显示出来的TABLESPACE_NAME就是你当前操作的表空间的名字

     select ename,sal*12 annual_sal from emp
2   where ename not like '_A%' and sal >800
3   order by sal desc;

   select hiredate from emp
   select to_char<sysdate , 'YYYY-MM-DD HH24:MI:SS'> from dual;

---求各个部门薪水最高的人的名字----

SQL> select ename,sal from emp e
  2  join (select max(sal) max_sal,deptno from emp group by deptno) t
  3  on(e.sal = t.max_sal and e.deptno = t.deptno);

----每个部门的平均薪水的等级------

SQL> select deptno,grade from
  2  (select deptno,avg(sal) avg_sal from emp group
  3  join salgrade s
  4  on(t.avg_sal between s.losal and s.hisal);

----求部门平均的薪水等级

select deptno,avg(grade) from(select deptno,ename,grade from emp e
join salgrade s
on(e.sal between s.losal and s.hisal) )t
group by deptno

---雇员中有哪些人是经理人

select ename from emp where empno in(select distinct mgr from emp)

---不准用组函数，求薪水的最高值

select distinct sal from emp where sal not in (select distinct e1.sal from emp e1
join emp e2
on(e1.sal < e2.sal))

----平均薪水最高的部门编号

select deptno,avg_sal from (select deptno,avg(sal)  avg_sal from emp
group by deptno) 
where avg_sal  =
(select max(avg_sal)from (select deptno,avg(sal)  avg_sal from(select deptno, sal from emp)
group by deptno)  )

-----平均薪水最高的部门名称

select dname from dept d
join
(select deptno,avg_sal from (select deptno,avg(sal)  avg_sal from emp
group by deptno) 
where avg_sal  =
(select max(avg_sal)from (select deptno,avg(sal)  avg_sal from(select deptno, sal from emp)
group by deptno)  )) t
on(d.deptno = t.deptno)

求平均薪水的等级最低的部门的部门名称

select t3.deptno,dname,grade,avg_sal,loc from
(
select deptno ,grade ,avg_sal from 
(select deptno,avg(sal) avg_sal from emp group by deptno) t1
join salgrade s
on(t1.avg_sal between s.losal and hisal)
where grade =
(select min(grade) from
(select deptno ,grade ,avg_sal from 
 (select deptno,avg(sal) avg_sal from emp group by deptno) t2
join salgrade s
on(t2.avg_sal between s.losal and hisal)))
) t3
join dept
on(dept.deptno = t3.deptno)

---

or:

select t.deptno,dname,avg_sal,grade from
(
 select deptno, avg_sal ,grade from
  (select deptno,avg(sal) avg_sal from emp group by deptno)  t 
   join salgrade s
   on(t.avg_sal  between s.losal and s.hisal)
) t
join dept
on(dept.deptno = t.deptno)
where grade =
(
 select min(grade) from
 (
  select deptno, avg_sal ,grade from
   (select deptno,avg(sal) avg_sal from emp group by deptno)  t 
    join salgrade s
    on(t.avg_sal  between s.losal and s.hisal)
 )
)

---

create  view v$_dname_info as 
 select deptno, avg_sal ,grade from
  (select deptno,avg(sal) avg_sal from emp group by deptno)  t 
   join salgrade s
   on(t.avg_sal  between s.losal and s.hisal);

////////////////////////////////////////////////////
select t.deptno,dname,avg_sal,grade from
(
    v$_dname_info
) t
join dept
on(dept.deptno = t.deptno)
where grade =
(
 select min(grade) from
 (
  v$_dname_info
 )
)
/////////////////////////////////////////////////////////////////////////////////////
conn sys/manager1 as sysdba

grant create table,create view to scott

conn scott/tiger

------比普通员工的最高薪水还要高的经理人

select ename from emp
where
empno in(select distinct mgr from emp where mgr is not null)
and
sal >
(
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null)
)

--------求薪水最高的前5名雇员

SQL> select ename,sal from(select ename,sal from emp order by sal desc) where ro
wnum <=5;

---------求薪水最高的第6到第10名雇员

SQL> select ename,sal from (select ename,sal,rownum r from (select ename,sal fro
m emp order by sal desc)) where r>5 and r<11;

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
约束条件，非空(not null)，唯一(unique)， 主键(primary key)，外键(foreign key)(references class(id))

create table stu
(
id number(4),
name varchar2(5) constraint NAME_NOT_NULL not null,非空
class number(4),
email varchar(25),
constraint STU_CLASS_FOREIGN_KEY foreign key(class) references class(id),//外键
constraint PRIMARY_KEY primary key(id),主键
constraint NAME_AND_EMAIL_UNIQUE unique(email)唯一
)


create table class
(
id number(4),
constraint CLASS_PRIMARY_KEY primary key(id)
)

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

查询修改记录

 declare
    v_deptno dept.deptno%type :=50;
            v_dname dept.dname%type := 'ken';
                    v_loc dept.loc%type := 'HZ';
 begin
    insert into dept2 values(v_deptno,v_dname,v_loc);
dbms_output.put_line(sql%rowcount||'条记录被修改');
    commit;
 end;

---

emp2 中empno = 7839 如果薪水小于2500，更新乘以二，等于2500，打印出来，大于2500，则更新，除以二。

 declare
    v_sal emp2.sal%type;
    v_empno emp2.empno%type :=7839;
 begin
    select sal into v_sal from emp2
            where empno = v_empno;
    if(v_sal <2500)         then
            update emp2 set sal = sal*2 where empno = v_empno;
    elsif(v_sal =2500) then
   dbms_output.put_line(v_sal);
    else
            update emp2 set sal = sal/2 where empno = v_empno;
    end if;
                    commit;
 end;

--------loop循环
1，
 declare
    i binary_integer := 1;
 begin
    loop
            dbms_output.put_line(i);
            i := i + 1;
            exit when (i >=11);
    end loop;
 end;

2，
 declare
    i binary_integer := 1;
 begin
    while i< 11 loop
            dbms_output.put_line(i);
            i := i + 1;
    end loop;
 end;

3，
 declare
    i binary_integer := 1;
 begin
    for i in 1..10 loop
            dbms_output.put_line(i);
            i := i + 1;
    end loop;
 end;

4，
declare
   i binary_integer := 1;
begin
   for i in 1..10 loop
           dbms_output.put_line(i);
   end loop;
end;

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

错误处理
建表--- 序列号------具体处理

create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
)
;
序列号
create sequence seq_errorlog_id start with 1 increment by 1;


 declare
 v_deptno dept.deptno%type := 10;
    v_errcode number;
    v_errmsg varchar2(1024);
 begin
    delete from dept where deptno = v_deptno;
    commit;
 exception
    when others then
            rollback;
                    v_errcode := SQLCODE;
                    v_errmsg := SQLERRM;
            insert into errorlog values(seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);
                    commit;
 end;

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

游标 cursor

declare
cursor c is
   select * from emp;
   v_emp c%rowtype;
begin
   open c;
   fetch c into v_emp;--fetch完后自动定位到下条记录
   dbms_output.put_line(v_emp.ename);
   close c;
end;

//-----------------------------------------------------------------------------
循环

declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
loop
fetch c into v_emp;--fetch完后自动定位到下条记录
exit when(c%notfound);
dbms_output.put_line(v_emp.ename) ; --不能放在exit前，否则最后一条记录打印两次
end loop; 
close c;
end;
while循环
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
while(c%found)loop
dbms_output.put_line(v_emp.ename) ;
fetch c into v_emp;
end loop; 
close c;
end;

for 循环

 declare
 cursor c is
 select * from emp;
 begin
 for v_emp in c loop
 dbms_output.put_line(v_emp.ename);
 end loop;
 end;

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

------------------------存储过程--------------------
1、把过程的declare变成 create or Replace produce p is 就行。

--declare
create or replace procedure p
is
cursor c is
select * from emp2 for update;
begin
for v_emp in c loop
if(v_emp.sal <2000) then
update emp2 set sal =sal+1 where current of c ;
elsif(v_emp.sal>=2000) then
delete from emp2 where current of c; 
end if; 
end loop; 
commit; 
end; 

创建了存储过程不代表运行了存储过程；
运行此存储过程 ：
方式一 exec p；
方式二
begin
p;
end;
2、带参数的存储过程
in 相当于程序里的参数，供传入用，在存储过程不能改变其值；
out 相当于程序里的返回值，在存储过程中可以为其赋值传出；
in out 既可以当参数又可以当返回值用；
不带上述说明符默认为in类型；

下例中v_a v_b 为in类型
v_c 为out类型
v_d 为in out 类型

create or replace procedure p(v_a in number,v_b number,v_c out number,v_d in out number)
is
begin
if(v_a > v_b) then
v_c := v_a;
else
v_c := v_b;
end if; 
v_d := v_d+1;
end; 

---> 调试时：
可以在命令窗口调试，出错时 用show errors 显示出错信息；
可以在plDv中调试；

---> 运行时：
可以在命令窗口运行：

declare
v_a number:=3;
v_b number:=4;
v_c number;
v_d number:=5;
begin
p(v_a,v_b,v_c,v_d);
dbms_output.put_line(v_c);
dbms_output.put_line(v_d); 
end;

可以在plDv中调试；

------------------函数-------------------
1、它有返回值

create or replace function tax_tag(sal number)return number --计算税率
is
begin
if(sal > 1000)then
return 0.1;
elsif(sal>=2000)then
return 0.15;
else
return 0.2;
end if;
end; 

select ename, tax_tag(sal) from emp ；-- 直接用函数tax_tag

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

触发器 trigger

create table emp2_log
(
uname varchar2(20),
action varchar2(20),
atime varchar2(20)
)


 create or replace trigger trig
after insert or update or delete on emp for each row
begin
if inserting then
  insert into emp2_log values(USER,'insert',to_char(sysdate,'YYYY-MM-DD HH:MI:SS'));
  elsif updating then
    insert into emp2_log values(USER,'update',to_char(sysdate,'YYYY-MM-DD HH:MI:SS'));
 elsif deleting then
     insert into emp2_log values(USER,'delete',to_char(sysdate,'YYYY-MM-DD HH:MI:SS'));
  end if;
 end;

 update emp set sal = sal*1 where deptno = 30;


select * from emp2_log;

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

-------------------------recursion 通过递归写树--------------------
-----》创建表并插入记录

create table article (
id number primary key,
cont varchar2(4000),--文章内容
pid number, --父类id
isleaf number(1),--0代表非叶子节点，1代表叶子节点
alevel number(2) --等级
)
insert into article values(1,'蚂蚁大战大象',0,0,0);
insert into article values(2,'大象被打趴下',1,0,1);
insert into article values(3,'蚂蚁也不好过',2,1,2);
insert into article values(4,'瞎说',2,0,2);
insert into article values(5,'没有瞎说',4,1,3);
insert into article values(6,'怎么可能',1,0,1);
insert into article values(7,'怎么没有可能',6,1,2);
insert into article values(8,'可能行很大的',6,1,2);
insert into article values(9,'大象进医院了',2,0,2);
insert into article values(10,'蚂蚁是护士',9,1,3);

----------》存储过程

create or replace procedure p (v_pid article.pid%type,v_level binary_integer) is
cursor c is select * from article where pid = v_pid;
v_perStr varchar2(2000):=' ';
begin
for i in 1..v_level loop
v_perStr := v_perStr||'***';
end loop; 
for v_article in c loop
dbms_output.put_line(v_perStr||v_article.cont);
if (v_article.isleaf = 0)then
p(v_article.id,v_level + 1);
end if; 
end loop;
end;

----------》输出结果
蚂蚁大战大象
大象被打趴下
蚂蚁也不好过
瞎说
******没有瞎说
******大象进医院了
*********蚂蚁是护士
怎么可能
怎么没有可能
***可能行很大的
/////////////////////////////////////////////////////////////////////////////////////
树状存储EMP员工表

create or replace procedure pp(v_mgr emp.mgr%type,v_level binary_integer) is
 cursor c is select * from emp where mgr = v_mgr;
v_perStr varchar2(2000):=' ';
begin
for i in 1..v_level loop
v_perStr := v_perStr||'-----';
end loop; 
 for v_emp in c loop
 dbms_output.put_line(v_perStr||v_emp.ename);
 pp(v_emp.empno,v_level + 1);
 end loop;
 end;

结果：
JONES
*****SCOTT
**********ADAMS
*****FORD
**********SMITH
BLAKE
*****ALLEN
*****WARD
*****MARTIN
*****TURNER
*****JAMES
CLARK
*****MILLER
---------------------------------------------------------------------------------------------------------

 declare
        clzcflag number(02);
        v_sal varchar2(2000);
     begin
        SELECT t.cl_zcflag into clzcflag FROM Cl_Gs t where cl_id =  '0017C4F0F038_2400';
        
        if(clzcflag =0)        then
           SELECT t.cl_zc.getclobval() into v_sal FROM Cl_Gs t  WHERE Cl_Ssk = '1014' and Cl_Yl1 = 0 ;
     --dbms_output.put_line(v_sal);
      else
        SELECT t.cl_zc.getclobval() into v_sal FROM Cl_Gs t where cl_id =  '0017C4F0F038_2400';
      --  dbms_output.put_line(v_sal);      
      end if;
      
   end;