Oracle 基础操作指南

基础函数

alert user scott account unlock;
select ename||sal from emp;  字符连接符
select ename from emp where lower(ename) like '_a%';
select ename,substr(ename,1,3) from emp;
select chr(65) from dual; //把一个ASCII码转为一个字符
select ascii('A') from dual;  //求一个字符的ascii码值
select round(23.635,2) from dual;//四舍五入 到小数点后2位
select to_char(sal,'$99,999.9999') from emp;
select to_char(sysdate,'YYYY-MM-DD HH:MI:SS') from dual; 日期格式化
select ename,hiredate frm emp where hiredate >to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');
select sal from emp where sal>to_number('$1,250.00','$9,999.99');
select ename,sal*12+comm form emp; --含有空值 导致整个表达式为空值
select ename,sal*12 +nvl(comm,0) from emp;
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
select to_char(avg(sal),'99999999.99')from emp;
select round(avg(sal),2) from emp;
select sum(sal) from emp;
select count(*) from emp;
select count(ename) from emp;  --ename中不是空值的行数
select deptno,avg(sal) from emp group by deptno;
select deptno,job,max(sal) from emp group by deptno,job; --根据deptno和job进行分组
select avg(sal),deptno from emp group by deptno having avg(sal)>2000;
--where -->group by -->having --->order by
select avg(sal) from emp where sal>1200 group by deptno having avg(sal)>1500 order by avg(sal) desc;


-------------------------------------------------------------------------------------------------------------------------------------
insert into dept values(50,'game','beijing');
create table emp2 as select * from emp;
create table dept2 as select * from dept;
create table salgrade as select * from salgrade2;
insert into dept2(deptno,dname)values(60,'game2');
insert into dept2 select * from dept;
-------------------------------------------------------------------------------------------------------------------------------
update emp2 set sal = sal*2 ,ename =ename||'-' where deptno =10;
-------------------------------------------------------------------------------------------------------
delete from emp2 where deptno <25;
rollback;
-------------------------------------------------------------------------------------------------------------------

事务 transaction rollback commit

当碰到create 或者 grant 会自动提交
--------------------------------------------------------------------------------------------------
create table stu (
    id number(6),
    name varchar2(20) constraint stu_name_nn  not null,--字段级约束
    sex number(1),
    age number(3),
    sdate date,
    grade number (2) default 1,
    class number(4),
    email varchar2(50) unique ,
)
desc stu;
约束
非空约束
唯一约束
主键约束
外键约束
当要求两个字段的组合不能重复 如name email
create table stu (
    id number(6) primary key,    --主键约束
    name varchar2(20) constraint stu_name_nn  not null,
    sex number(1),
    age number(3),
    sdate date,
    grade number (2) default 1,
    class number(4),
    email varchar2(50) ,
    constraint stu_name_email_uni unique(email,name)  --表级约束
    --constraint stu_id_pk primary key(id)
)
---------
外键约束
create table class(
    id number(4) primary key,
    name varchar2(20) not null
)
create table stu (
    id number(6) primary key,
    name varchar2(20) constraint stu_name_nn  not null,
    sex number(1),
    age number(3),
    sdate date,
    grade number (2) default 1,
    class number(4) references class(id),  --外键约束
    email varchar2(50) ,
    constraint stu_name_email_uni unique(email,name)
    --constraint stu_class_fk foreign key(class) references class(id)
)
----------------------------------------------------------------------------------------
alter table stu add(addr varchar2(100));
alter table stu drop(addr);
alter table stu modify(addr varchar2(150));
alter table stu drop constraint stu_class_fk;
alter table stu add constraint stu_class_fk foreign key(class) references class(id)
drop table stu;
---------------------------------------------------------------------------------------------------
select table_name from user_tables; --当前用户所拥有的表
select view_name from user_views;
select constraint_name from user_constraints;
desc dictionary;
----------------------------------------------------------------------------------------------------------------

索引 读起来更快 插进去会慢一点

desc stu;
create index idx_stu_email on stu(email);
drop   index idx_stu_email;
select index_name from user_indexes;
当为表添加主键或者唯一约束 oracle会自动在此字段

---

视图

create view v$_stu as select id,name,age from stu;
----------------------------------------------------------------------------------------------------------
序列 sequence
create table article (
    id number,
    title varchar2(1024),
    cont long
);
create sequence seq;
select  seq.nextval from dual;
insert into article values(seq.nextval,'a','b');
drop sequence seq;

---

三范式

设计原则：不存在冗余数据
第一范式：1.要有主键 2.列不可分
第二范式：当一张表存在多个字段作为主键的时候，非主键的字段不能依赖于部分主键
          如：sno,cno,sname    姓名依赖于学号    部分依赖
第三范式：S1（SNO，SNAME，DNO，DNAME，DLOCATION）  DLOCATION由 DNO决定  传递函数依赖

---

PL/SQL语言

四个模块：declare  begin exception  end
-----------------------------------------------------
begin
    dbms_output.put_line('HelloWorld');
end;
/
set serveroutput on;  --内部环境变量serveroutput 默认为off
--再执行
begin
    dbms_output.put_line('HelloWorld');
end;
/
-------------------------------------------------------
declare
    v_name varchar2(20);        --变量名 以v_开头
begin
    v_name :='myname';
    dbms_output.put_line(v_name);
end;
/
-------------------------------------------------------
declare
    v_num number :=0;
begin
    v_num :=2/v_num;
    dbms_output.put_line(v_num);
exception
    when others then
        dbms_output.put_line('error');
end;
-----------------------------------------------------------
declare
    v_temp number(1);
    v_count binary_integer :=0;
    v_sal number(7,2) :=4000.00;
    v_date date := sysdate;
    v_pi constant number(3,2) :=3.14;
    v_valid boolean :=false;
    v_name varchar2(20) not null :='MyName';
begin
    dbms_output.put_line('v_temp value:'||v_temp);
end;
show user;
变量申明：
declare
    v_empno number(4);
    v_empno2 emp.empno%type;
    v_empno3 v_empno2%type;
复合变量
declare
    type type_table_emp_empno is table of emp.empno%type index by binary_integer;
    v_empnos type_table_emp_empno;
begin
    v_empnos(0) :=7369;
    v_empnos(2) :=7839;
    v_empnos(-1):=9999;
    dbms_output.put_line(v_empnos(-1));
end;
--Record变量类型
declare
    type type_record_dept is record
        (
            deptno dept.deptno%type
            dname dept.dname%type
            loc dept.loc%type     
        );
    v_temp type_record_dept;
begin
    v_temp.deptno :=50;
    v_temp.dname :='aaaa';
    v_temp.loc :='bj';
    dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
end;
--使用%rowtype声明record变量
declare
    v_temp dept%rowtype;
begin
    v_temp.deptno :=50;
    v_temp.dname :='aaaaaa';
    v_temp.loc :='bj';
    dbms_output.put_line(v_temp.deptno||' '||v_temp.dname);
end;
-------------------------------------------------
PL/SQL 中使用sql语句的话 有且只有返回一条记录
declare
    v_ename emp.ename%type;
    v_sal emp.sal%type;
begin
    select ename,sal into v_ename,v_sal from emp where empno =7369; --返回的记录数 有且只有返回一条记录
    dbms_output.put_line(v_ename ||''||v_sal);
end;
------
declare
    v_emp emp%rowtype;
begin
    select * into v_emp from emp where empno =7369;
    dbms_output.put_line(v_emp.ename);
end;
/
--------------
declare
    v_deptno dept.deptno%type :=50;
    v_dname dept.dname%type :='aaa';
    v_loc dept.loc%type :='bj';
begin
    insert into dept2 values (v_deptno,v_dname,v_loc);
    commit;
end;
------------------
declare
    v_deptno emtp2.deptno%type :=10;
    v_count number;
begin
    update emp2 set sal = sal/2 where deptno = v_deptno;
    --select deptno into v_deptno from emp2 where empno = 7369;
    --select count(*) into v_count from emp2;
    dbms_output.put_line(sql%rowcount||'条记录被影响');
    commit;
end;
-------------------------
begin
    execute immediate 'create table T(nnn varchar2(20) default "aaa" )';
end
/
----------------------
declare
    v_sal emp.sal%type;
begin
    select sal into v_sal from emp
            where empno =7369;
    if(v_sal <1200) then
        dbms_output.put_line('low');
    elsif(v_sal <2000) then
        dbms_output.put_line('middle');
    else
         dbms_output.put_line('high');
    end if;
end;
---------------------------------
循环
declare
    i binary_integer := 1;
begin
    loop
        dbms_output.put_line(i);
         i:= i+1;
         exit when (i>=11);
    end loop;
end;
-----
declare
    j binary_integer :=1;
begin
    while j<11 loop
     dbms_output.put_line(j);
        j:=j+1;
    end loop;
end;
-----
begin
    for k in 1..10 loop
        dbms_output.put_line(k);
    end loop;
    for k in reverse 1..10 loop
        dbms_output.put_line(k);
    end loop;
end;
--------------------------------------------------------------------------------------------------------------------
--错误处理
declare
    v_temp number(4);
begin
    select empno into v_temp from emp where deptno =10;
exception
    when too_many_rows then
        dbms_output.put_line('太多记录');
    when others then
        dbms_output.put_line('error');
end;
declare
    v_temp number(4);
begin
    select empno into v_temp from emp where empno = 2222;
exception
    when no_data_found then
        dbms_output.put_line('没数据');
end;
create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
);
----------sequence
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;
------------------------------------------------------------------------------------------------------------------------------------------
游标：
declare
    cursor c is
        select * from emp;
    v_emp c%rowtype;
begin
    open c;
        fetch c into v_emp;
        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;
        exit when (c%notfound);
        dbms_output.put_line(v_emp.ename);
    end loop;
    close c;
end;
declare
    cursor c is
        select * from emp;
    v_emp emp%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;
declare
    cursor c is
        select * from emp;
begin
    for v_emp in c loop
        dbms_output.put_line(v_emp.ename);
    end loop;
end;
--带参数的游标
declare
    cursor c(v_deptno emp.deptno%type,v_job emp.job%type)
    is
        select ename,sal from emp where deptno =v.deptno and job = v_job;
        --v_temp c%rowtype;
    begin
        for v_temp in c(30,'CLERK') loop
            dbms_output.put_line(v_temp.ename);
        end loop;
    end;
--可更新的游标
declare
    cursor c
    is
        select * from emp2 for update;
    --v_temp c%rowtype;
begin
    for v_temp in c loop
        if(v_temp.sal<2000) then
            update emp2 set sal = sal*2 where current of c;
        elsif(v_temp.sal = 5000) then
            delete from emp2 where current of c;
        end if;
    end loop;
    commit;
end;
------------------------------------
存储过程
create or replace procedure p
is
    cursor c is
        select * from emp2 for update;
    --v_temp c%rowtype;
begin
    for v_temp in c loop
        if(v_temp.deptno=10) then
            update emp2 set sal = sal+10 where current of c;
        elsif(v_temp.deptno=20) then
            update emp2 set sal = sal+20 where current of c;
        else
            update emp2 set sal = sal+50 where current of c;
        end if;
    end loop;
    commit;
end;
/
执行
1.exec p;
2.begin
    p;
  end;
--带参数的存储过程
create or replace procedure p
    (v_a in number,v_b number,v_ret out number,v_temp in out number)
is
begin
    if(v_a > v_b)then
        v_ret := v_a;
    else
        v_ret := v_b;
    end if;
    v_temp := v_temp +1;
end;
调用：
declare
    v_a number := 3;
    v_b number := 4;
    v_ret number;
    v_temp number :=5;
begin
    p(v_a,v_b,v_ret,v_temp);
    dbms_output.put_line(v_ret);
    dbms_output.put_line(v_temp);
end;
--show error;
------------------------------------------------------------
函数：
create or replace function sal_tax
    (v_sal number)
    return number
is
begin
    if(v_sal < 2000)then
        return 0.10;
    elsif(v_sal < 2750) then
        return 0.15;
    else
        return 0.20;
    end if;
end;
/
调用函数：
------------------------------------------------------------------------
触发器：
create table emp2_log
(
    uname varchar2(20),
    action varchar2(10),
    atime date
);
create or replace trigger trig
    after insert or delete or update on emp2  --for each row (加不加的区别）)
begin
    if inserting then
        insert into emp2_log valuses(USER,'insert',sysdate);
    elsif updating then
        insert into emp2_log values (USER,'update',sysdate);
    elsif deleting then
        insert into emp2_log values (USER,'delete',sysdate);
    end if;
end;
update emp2 set sal = sal*2 where deptno = 30;
select * from emp2_log;
update dept set deptno = 99 where deptno =10;
drop trigger trig;
create or replace trigger trig
    after update on dept
    for each row
begin
    update emp set deptno = :NEW.deptno where deptno = :OLD.deptno;
end;
select * from emp;
rollback;
--------------------------------------------------
--树状结构的存储与展示
drop table article;
create table article
(
    id number primary key;
    cont varchar2(4000),
    pid number,
    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);
commit;
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_preStr varchar2(1024):='';
begin
    for i in 1..v_level loop
     v_preStr := v_preStr||'**';
    end loop;
    for v_article in c loop
      dbms_output.put_line(v_preStr||v_article.cont);
      if(v_article.isleaf = 0 ) then
        p(v_article.id,v_level+1);
      end if;
    end loop;
end;
exec p(0,0);
set serveroutput on;