Oracle常用语句&函数

DENSE_RANK () OVER (ORDER BY 字段 )
RANK () OVER (ORDER BY 字段 )


1、用户解锁
alter user 用户名 account unlock;


2、用户修改密码
alter user 用户名 identified by 密码;


3、网页登陆
http://IP:1158/em


4、cmd登陆
sqlplus 用户名/密码;
show user;


5、查看一个表的结构
desc 表名;


6、查询每个人的年薪
select ename,sal*12 salofyear from emp;


注意:null参与数学运算的时候结果还是null


7、通过dual表进行查询
--查询系统时间
select sysdate  from dual;
显示'的时候需要''表示一个'


8、-- 内连接
select emp2.ename as 员工名字 ,dept2.dname as 所在部门 from emp2 ,dept2 where emp2.deptno=dept2.deptno;
-- 外左连接
select emp2.ename as 员工名字 ,dept2.dname as 所在部门 from emp2 left join dept2 on emp2.deptno=dept2.deptno;
-- 外右连接
select emp2.ename as 员工名字 ,dept2.dname as 所在部门 from emp2 right join dept2 on emp2.deptno=dept2.deptno;


9、--分页查询(4,6) rownum
select * from (select e.*,rownum rn  from


    (select * from emp2 order by sal desc ) e
     where rownum <=6
) where rn>=4;


10、--外键
alter table 表名 add constraint 外键名 foreign key(字段) references 表名(字段)
（可加 on delete cascade或 on update cascade 表示 主键表中的外键值删除或更新，子健表中的值也会删除或更新）


11 --建表
create table account(


id number primary key，--主键
name varchar2(20) default '默认值' NOT NULL（非空）,


money number(20,2)check (money in(100,1000)) --只能写入100或1000
);




12-- 随机查询一条数据
select *from ( select *  from emp order by dbms_random.value)where rownum=1;


13
select*from emp2 where sal>2000 intersect select * from emp2 where job='MANAGER'--交集查询
select*from emp2 where sal>2000 union select * from emp2 where job='MANAGER'--并集不重复查询
select*from emp2 where sal>2000 minus select * from emp2 where job='MANAGER'--差集集查询


14、保存点
select * from emp;
savepoint a;
delete from emp where empno=7369;
savepoint b;
delete from emp where empno=7499;
savepoint c;
delete from emp where empno=7521;
--commit;
rollback to a;




15-创建用户
connect system/bchy;
--创建表空间
create tablespace bchy datafile 'd:\bchy.dbf' size 5m autoextend on next 10m maxsize 500m;
--创建用户,赋予角色
drop user wangcai cascade;
create user wangcai identified by wangcai default tablespace bchy;
grant connect,resource to wangcai;
connect wangcai/wangcai;
--创建表,默认在bchy这个命名空间
create table test(
       id number,
       name char(1000)
) /*tablespace bchy*/;
--快速插入语句测试
insert into test values(1,'呵呵');
insert into test(id,name)
select * from test;
--扩展表空间
alter tablespace bchy add datafile 'd:\bchy.dbf' size 20m;
alter tablespace bchy 'd:\bchy.dbf' resize 20m;
alter tablespace bchy 'd:\bchy.dbf' autoextend on next 10m maxsize 500m;


38、导入导出
--导出
 exp
--创建用户
 create user wangcai identified by wangcai
 default tablespace users
 quota 10m on users;
--分配权限
 grant create session ,create table,create view to wangcai;
--导入
 imp


39、备份表
 create table emp2 
 as
 select * from emp;




43、修改表
 a、添加列
alter table student add(addr nvarchar2(50));
 b、删除列
alter table student drop(addr);
 c、修改列
alter table student modify(addr nvarchar2(100));
44、回滚
rollback;


45、查询某个表的约束
select constraint_name from all_constraints
where table_name='STUDENT' and owner='WANGCAI';


select constraint_name from user_constraints;


46、创建索引
create index index_student_email on student(email);
select index_name from user_indexes;


47、PL_SQL
a、输出
begin
dbms_output.put_line('''HelloWorld''');
end;
/

b、定义变量


变量声明的规则:
1、变量名不能使用保留字、关键字,如from,select等
2、第一个字符必须是字母
3、变量名最多包含30个字符
4、不要与数据库的表或者列同名
5、每一行只能声明一个变量
常用变量类型:
1、binary_integer:整数，主要用来计数而不是用来表示字段类型
2、number:数字类型
3、char:定长字符串
4、varchar2:变长字符串
5、date:日期
6、long:长字符串，最长2GB
7、boolean:布尔类型，可以取值为true,false和null值


注意：bollean类型不能直接打印输出



declare
v_name varchar2(20);
v_age number(2);
v_sex varchar2(2):='男';
begin
v_name:='d';
v_age:=12;
dbms_output.put_line(v_name);
dbms_output.put_line(v_age);
dbms_output.put_line(v_sex);
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 := 'zhangsan';
begin
  dbms_output.put_line('v_temp value:'|| v_temp);
    dbms_output.put_line('v_count value:'|| v_count);
end;


c、捕捉异常
declare
v_num number :=0;
begin
v_num := 2/v_num;
dbms_output.put_line(v_num);
exception
when no_data_found then
dbms_output.put_line('no_data_found ');
when others then
dbms_output.put_line('error');
end;


d、table,相当于数组(了解)


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):=1234;
    v_empnos(-1):=45;
    v_empnos(-2):=1212;
    v_empnos(2):=34;
    dbms_output.put_line( v_empnos(0));
    dbms_output.put_line( v_empnos(-1));
    dbms_output.put_line( v_empnos(-2));
    dbms_output.put_line( v_empnos(2));
end;


e、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:='abc';
    v_temp.loc :='china';
    dbms_output.put_line(v_temp.deptno||' '||v_temp.dname||' '||v_temp.loc);


         end;


f、使用rowtype声明record变量
declare
    v_temp dept%rowtype;
begin
    v_temp.deptno:=50;
    v_temp.dname:='abc';
    v_temp.loc :='china';
    dbms_output.put_line(v_temp.deptno||' '||v_temp.dname||' '||v_temp.loc);


end;


g、变量的定义及通过查询赋值
必须唯一赋值，查询结果不能为null
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;



h、使用rowtype声明record变量及通过查询赋值


declare
    v_emp emp%rowtype;
begin
    select * into v_emp from emp where empno=7369;
    dbms_output.put_line(v_emp.ename||' '||v_emp.empno);
end;
 
i、查询受影响的行数
只有修改数据的时候才有意义


begin
    delete from emp2;
    dbms_output.put_line(sql%rowcount||'受影响');
end;

j、在pl_sql里使用create时使用execute immediate
 
         begin
    execute immediate 'create table b(name nvarchar2(10) default ''abc'')';
end;
 
k、选择结构
 
declare
    v_sal emp.sal%type;
begin
    select sal into v_sal from emp where empno = 7369;
    if(v_sal<1000) then
    dbms_output.put_line('苦逼');
    elsif(v_sal<8000) then
    dbms_output.put_line('屌丝');
    else
    dbms_output.put_line('高大上');
    end if;
end;


L、循环结构
1、类似java中的do while
declare
    i binary_integer:=1;
begin
    loop
            dbms_output.put_line(i);
            i:=i+1;
            exit when(i>=10);
    end loop;
end;
2、类似java中的while
declare
    i binary_integer:=1;
begin
    while(i<11) loop
            dbms_output.put_line(i);
            i:=i+1;
    end loop;
end;


3、类似java中的for
begin
    for i in 100..10 loop
            dbms_output.put_line(i);
    end loop;
end;


begin
    for i in reverse 100..10 loop
            dbms_output.put_line(i);
    end loop;
end;

m、错误处理


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=1111;
exception
    when no_data_found then
            dbms_output.put_line('没数据');
    when others then
            dbms_output.put_line('error');
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 errorlog.errcode%type;
    v_errmsg errorlog.errmsg%type;
    v_errdate errorlog.errdate%type;
  begin
    delete from dept where deptno = v_deptno;
    commit;
  exception
    when others then
            rollback;
            v_errcode := sqlcode;
            v_errmsg := sqlerrm;
            v_errdate := sysdate;
            insert into errorlog values(seq_errorlog_id.nextval,v_errcode,v_errmsg,v_errdate);
            commit;
  end;


n、游标


游标的属性:


set serveroutput on;
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.empno||' '||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.empno||' '||v_emp.ename);
            end loop;
    close c;
end;








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.empno||' '||v_emp.ename);
                    fetch c into v_emp;
            end loop;
    close c;
end;






 
declare
    cursor c is select * from emp;
--v_emp%rowtype --不用声明
begin
    for v_emp in c loop
            dbms_output.put_line(v_emp.empno||' '||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_jo


  begin
    for v_temp in c(30,'SALESMAN') loop
            dbms_output.put_line(v_temp.sal||' '||v_temp.ename);
    end loop;
  end;






--可更改的游标(不常用)
declare
cursor c is select * from emp2 for update;
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;
 


--引用游标
declare
        type ref_cursor is ref cursor;        --定义引用游标类型
        c ref_cursor;                         --定义变量
        v_deptno emp.deptno%type;
        v_ename emp.ename%type;
        v_sal emp.sal%type;
begin
        v_deptno:=10;                         --为变量赋值
        open c for select ename,sal from emp where deptno = v_deptno;--将游标指向集合
        loop                                 --循环
            fetch c into v_ename,v_sal;
            exit when(c%notfound);
            dbms_output.put_line(v_ename||':'||v_sal);
        end loop;
        close c;
end;
/



o、存储过程


--创建无参数存储过程
create or replace procedure p
is
cursor c is select * from emp2 for update;
begin
for v_emp 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+30 where current of c;
end if;
end loop;
commit;
end;
--执行1
exec p;
--执行2
begin
p;
end;
--显示错误
show error;



--创建带参数的存储过程
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 := 10;
v_b number := 100;
v_ret number;
v_temp number := 1;
begin
p(v_a,v_b,v_ret,v_temp);
  dbms_output.put_line(v_ret);
  dbms_output.put_line(v_temp);
end;
 




p、函数


create or replace function 名字(
        参数1  类型,
        参数2 类型
)
        return number
is
        变量1 类型;
        变量2 类型;
begin
exception
end;




--创建函数
create or replace function sal_tax
(v_sal number)
return number
is
begin
if(v_sal < 2000) then
return 0.1;
elsif(v_sal < 2750) then
return 0.15;
else
return 0.20;
end if;
end;
--调用函数
select lower(ename),sal_tax(sal) from emp;






--创建函数
create or replace function f_1(
        v_empno number
)
return number
is
        v_sal emp.sal%type;
begin
        select sal into v_sal  from emp where empno = v_empno;
        return v_sal;
end;
--调用函数
select empno,f_1(empno) from emp;




q、触发器
参考网址：http://blog.csdn.net/indexman/article/details/8023740/


create or replace trigger trig
    after insert or delete or update on emp2
--before insert or delete or update on emp2
--after insert or delete or update on emp2 for each row
begin
    if inserting then
            dbms_output.put_line('insertint......');
    elsif updating then
            dbms_output.put_line('updating......');
    elsif deleting then
            dbms_output.put_line('deleting......');
    end if;
end;
 




create  table emp2_log
  (
    uname nvarchar2(50),
    action varchar2(50),
    time 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 values(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;



--建立一个触发器, 当职工表 emp2 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。
create table emp_log
as
select * from emp2 where 1=2;


select * from emp_log;


create or replace trigger t_1
before delete on emp2 for each row
begin
  insert into emp_log(deptno,empno,ename,job,mgr,sal,comm,hiredate )
  values(:old.deptno,:old.empno,:old.ename,:old.job,:old.mgr,:old.sal,:old.comm,:old.hiredate );
end;
/
select * from emp2;
delete from emp2;
--建立一个触发器，修改dept2中编号10修改成11的之后，emp2引用的10也修改成11
create or replace trigger t_2
after update of deptno on dept2 for each row
begin
  update emp2 set deptno = :new.deptno  where  deptno = :old.deptno;
end;
/


update dept2 
set deptno = 11
where deptno = 10;


select * from emp2;
select * from dept2;


--包
包用于在逻辑上组合过程和函数和游标，它由包规范和包体两部分组成


--创建包头
create or replace package p1
is
       procedure pro(v_empno in number,v_sal out number);
       function fun(v_empno number) return number;
end;
/
--创建包体
create or replace package body p1
is
       procedure pro(v_empno in number,v_sal out number)
       is
       begin
           select  sal into v_sal from emp where empno = v_empno;
       end;
       function fun(v_empno number) return number
       is
             v_sal emp.sal%type;
       begin
             select  sal into v_sal from emp where empno = v_empno;
             return v_sal;
       end;
end;
/
--调用包里的存储过程,"包名.过程的名字"
declare
       v_empno emp.empno%type;
       v_sal emp.sal%type;
begin
       v_empno:=7566;
       p1.pro(v_empno,v_sal);
       dbms_output.put_line('v_sal:'||v_sal);
end;
/
--调用包里的函数，"包名.函数名"
declare
       v_empno emp.empno%type;
       v_sal emp.sal%type;
begin
       v_empno:=7566;
       v_sal:=p1.fun(v_empno);
       dbms_output.put_line('v_sal:'||v_sal);
end;
/


/****************创建存储过程，将引用型游标作为输出类型案例*****************/


--创建包头
create or replace package p2 is
       type ref_cursor is ref cursor;             --引用游标类型
end;
/
--创建过程
create or replace procedure p(
      v_deptno in number,
      v_c out p2.ref_cursor
)
is
begin
     open v_c for select ename,sal from emp where deptno = v_deptno;
end;
/
--调用测试
declare
      v_deptno emp.deptno%type:=10;
      v_c  p2.ref_cursor;
      v_ename emp.ename%type;
      v_sal emp.sal%type;
begin
      p(v_deptno,v_c);
      fetch v_c into v_ename,v_sal;
      dbms_output.put_line(v_ename||':'||v_sal);
end;
/


/***分页的存储过程***/
--创建包头
create or replace package package_page is
       type ref_cursor is ref cursor;              --引用游标类型
end;
/
--创建存储过程
create or replace procedure proc_page(
       v_pageNow       number,                        --当前页码
       v_pageSize      number,                        --每页显示的条数
       v_pageResource  varchar2,                      --查询的内容
       
       v_pageCount out number,                        --总页数
       v_pagRows    out number,                       --总记录数
       v_c out package_page.ref_cursor                --每页显示的数据集
)
is
       v_lt number(20);                               --小于的值
       v_mt number(20);                               --大于的值
begin
        /*计算总记录数*/
        execute immediate 'select count(*) from ('|| v_pageResource ||')' into v_pagRows;
        /*根据总记录数和每页显示的条数可以计算出总页数*/
        v_pageCount := ceil(v_pagRows/v_pageSize);
        /*计算小于和大于*/
        v_lt := v_pageNow*v_pageSize+1;
        v_mt := (v_pageNow-1)*v_pageSize;
        /*求数据集*/
        open v_c for 'select * from(select t.*,rownum rn from('
                 ||v_pageResource||') t where rownum<'
                 ||v_lt||') where rn>'||v_mt;
end;
/