Oracle进阶学习

Oracle进阶学习

一、分页

1、rowid

1. rowid在创建记录时生成且不变，是物理位置的唯一标识，直接指向硬件上的存储位置

2. id是逻辑上的唯一标识，所以rowid查找速度要快于id，是目前最快的定位一条记录的方式

2、rownum

1. rownum是个伪数列（默认隐藏的数列），查询的时候除非特别指定，否则不会显示

2. rownum用于标记结果集中结果顺序的一个字段，特点是按顺序标记且连续，其主要用处是控制查询返回的行数

3. rownum关键字只能和<或者<=直接关联，如果是>或者=则需要给他起个别名

3、分页模板

--起始下标 = （当前页 -1）* pageSize + 1
--结束下标 = 当前页 * pageSize

select * from
(select rowmun rt, 表别名.* from 表名 表别名 where rownum <= 结束下标)
where rt >= 起始下标

——————————————————————————————————————————————————————————————————————————————————-------

--获取第6-10条的数据记录
select * from
(select rownum rt,e.* from scott.emp e where rownum <=10)
where rt >=6

二、视图（虚表）——超级账号有权限

--创建视图语法
create view 视图名 as <select语句>

-- 创建一个视图
create view Eiffel_Wu_emp_view
as
select * from scott.emp

-- 创建一个视图(推荐使用)
create or replace view Eiffel_Wu_emp_view
as
select * from scott.emp

--查询视图中的数据
select * from Eiffel_Wu_emp_view

--删除视图
drop view Eiffel_Wu_emp_view

三、存储过程

1、名词解释

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，是由流程控制和SQL语句书写的命名语句块，经编译和优化后存储在数据库服务器中，应用程序使用时只要调用即可，可以通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调用。

2、Oracle中的存储过程语法规则

--书写规范
create [or replace] procedure 存储过程名（参数名1 参数类型1,参数名2 参数类型2）
as
    声明变量;
begin 
    sql代码块;
end;

--普通java方法
public void Eiffel_Wu_emp(int age){
    System.out.println("age=" + age);
}

--创建一个同上类似的存储过程
create or replace procedure Eiffel_Wu_emp(a number)
as
begin
    DBMS_OUTPUT.PUT_LINE('a='||a);
end;

3、调用存储过程

--第一种方式
--exec 存储过程名()
    
exec Eiffel_Wu_emp(10);

--第二种调用方式

declare
begin
    存储过程名();
end;

declare
begin
    Eiffel_Wu_emp(50);
end;

4、带返回值的存储过程

--普通java方法
public int Eiffel_Wu_sum(int a, int b){
    int c = a + b;
    return c;
}

--给存储过程传递两个number类型的变量，计算两个变量之和并且返回

create or replace procedure Eiffel_Wu_sum(a in number, b in number, rs out number)
as
    c number;
begin
    c:=a+b;
    rs:=c;
end;

--调用带返回值存储过程
declare
    sumab number;
begin
    Eiffel_Wu_sum(100,200,sumab);
    DBMS_OUTPUT.PUT_LINE('sumab='||sumab);
end;

5、带if的存储过程

--if语法规则
if 条件 then
    条件代码块;
end if;

--------------------------传递两个number类型的参数，返回最大值--------------------------------
--普通java方法   
public int  Eiffel_Wu_max(int a, int b){
    int temp = a;
    if(a < b){
        temp = b;
    }
    return temp;
}

--存储过程
create or replace procedure Eiffel_Wu_max(a in number, b in number,rs out number)
as
    temp number;
begin
    temp:=a;
    if a < b then
        temp := b;
    end if;
    rs:=temp;
end;

--调用存储过程
declare
    rs number;
begin
    Eiffel_Wu_max(10,20,rs);
    DBMS_OUTPUT.PUT_LINE('rs='||rs);
end;

6、带if else的存储过程

--------------------------传递两个number类型的参数，返回最大值--------------------------------
--普通java方法
public int  Eiffel_Wu_max2(int a, int b){
    if(a > b){
        return b;
    }else{
        return a;
    }
}

--存储过程
create or replace procedure Eiffel_Wu_max2(a in number, b in number,rs out number)
as
begin
    if a > b then
        rs := a;
    else
        rs := b;
    end if;
end;

--调用存储过程
declare
    rs number;
begin
    Eiffel_Wu_max2(30,20,rs);
    DBMS_OUTPUT.PUT_LINE('rs='||rs);
end;   

7、带else if的存储过程

--给存储过程传递一个Number类型的变量，如果输入1则打印'一月'，输入2则打印'二月'，其他打印'三月'
--存储过程
create or replace procedure Eiffel_Wu_print(i in number)
as
begin
    if i=1 then
        DBMS_OUTPUT.PUT_LINE('一月');
    elsif i =2 then
        DBMS_OUTPUT.PUT_LINE('二月');
    else
        DBMS_OUTPUT.PUT_LINE('三月');
    end if;
end;

--存储过程调用
exec Eiffel_Wu_print(1);

8、带while循环的存储过程

-----------------------------输入一个number类型的参数，遍历此参数-----------------------------
--普通java方法
public void Eiffel_Wu_while(int i){
    int j = 1;
    while(j<=i){
        System.out.println("j=" + j);
        j++;
    }
}

--存储过程
create or replace procedure Eiffel_Wu_while(i in number)
as
    j number;
begin
    j := 1;
    while j <= i loop
        DBMS_OUTPUT.PUT_LINE('j='||j);
        j := j + 1;
    end loop;
end;

--存储过程调用
exec Eiffel_Wu_while(100);

----------------输入一个参数number,比如100，计算1-100之间内所有能被3整除的数之和-----------------
--普通java方法    
public void Eiffel_Wu_sum2(int i){
    int j = 1;
    int sum = 0;
    while(j <= i){
        if(j%3==0){
            sum += j;
        }
        j++;
    }
    return sum;
}

--存储过程
create or replace procedure Eiffel_Wu_sum2(i in number, rs out number)
as
    j number;
    sm number;
begin
    j := 1;
    sm := 0;
    while j <= i loop
        if j mod 3 = 0 then
            sm := sm + j;
        end if;
        j := j + 1;
    end loop;
    rs := sm;
end;

--调用存储过程
declare
    rs number;
begin
    Eiffel_Wu_sum2(100, rs);
    DBMS_OUTPUT.PUT_LINE('rs='||rs);
end;