Oracel存储过程,分页函数定义，索引建立

梦里梦外; 2024-04-06 11:55 32阅读 0赞

## Oracel存储过程 ##

### 基础语法 ###

create or replace procedure 11
    
    (Name in out type, Name in out type, ...)
    is
    begin
      
    end 11;
    
    
    
    
      -- 语法为 变量名  数据类型（长度）;
      v_name varchar2(36);
      -- 声明时赋值
      v_age number := 12;
      
      
    begin
      -- 此处编写逻辑
      -- 打印语句如下
      dbms_output.put_line('年后');
      -- 拼接数据
      dbms_output.put_line('年后' || v_name);
    end;

*  变量的声明应该在declare下
 *  begin和end中间相当于方法体
 *  打印语句为 dbms\_output.put\_line(‘年后’);

### 运算符 ###

*  关系运算符
    
        =  等于
        <>,!=  不等于
        <    小于
        >    大于
        <=   小于等于
        >=   大于等于
 *  一般运算
    
        +加
        -减
        *乘
        /除
        :=赋值号  查询赋值语法为  select 字段名 into 变量名 from 表名
        =>关系号
        ..范围   例：1..8表示1到8
        ||字符连接
 *  逻辑运算
    
        is null空值
        between  在两值之间                例：5 BETWEEN 1 AND 10
        NOT BETWEEN 不在两值之间            例： 5 NOT BETWEEN 1 AND 10
        IN           在集合中               例： 5 IN (1,2,3,4)
        NOT IN       不在集合中              例： 5 NOT IN (1,2,3,4) True
        =             等于                   例：2=3 False
        <>, !=       不等于                   例：2<>3 False
        <=>          严格比较两个NULL值是否相等  例： NULL<=>NULL True
        LIKE          简单模式匹配		     例： "Guy Harrison" LIKE "Guy%" True
        REGEXP       正则式匹配                例："Guy Harrison" REGEXP "[Gg]reg" False
        IS NULL      为空                     例：0 IS NULL False
        IS NOT NULL 不为空                    例： 0 IS NOT NULL True
        and  逻辑与
        or   逻辑或
        not  逻辑非

### 变量的声明及赋值 ###

*1.基本数据类型*

声明：语法为 变量名 数据类型（长度）;

例：

v_name varchar(20);
    v_age number;

符号： :=

语法：

*  直接赋值
    
    例：
    
        v_name:='zhangsna';
 *  语句赋值
    
    select 值1,值2 into 变量1，变量2 from 表
    
    例：
    
        select '张三',12 into v_name,v_age from dual

2.引用数据类型

声明： 语法为 变量名 表名.列名%TYPE

例：

v_name student.name%TYPE;
    v_age  student.age%TYPE;

赋值：语法为 select 值1，值2 into 变量1，变量2 from 表名

例：

select name,age  into v_name,v_age  from  student  where id='1'

3.记录型变量

声明： 语法为 变量名称 表名%ROWTYPE

例：

v_emp emp%rowtype;

注：上述声明过程的含义表示接收emp表中的一整行记录

赋值：语法为

select  *   into  v_emp from emp  where ID='1';

`注：因为此处声明的是记录型变量，所以在赋值的时候应该是赋值的是一整张表中的一整行记录，所以应该在查询的时候查询的是该表得全部字段。`

获取： 语法为 变量名称.表中的字段名称；

例：

dbms_output.put_line(‘输出ID为1的员工姓名：’|| v_emp.name);
    dbms_output.put_line(‘输出ID为1的员工年龄：’|| v_emp.age);

### 流程控制 ###

#### 1.循环 ####

*  loop循环
    
    语法：
    
        loop
        
        exit when 退出循环的条件
        
        end loop;
    
    例：
    
        v_index number :=1;
        
        begin 
        
        loop 
        --判断条件
        exit when v_index=3
        --循环输出
        dbms_output.put_line(v_index);
        --变量自增
        v_index:=v_index+1;
        end loop;
 *  while循环
    
    语法：
    
        while 判断条件 loop
        
        循环体
        
        end loop;
    
    例：
    
        --声明变量
        i number;
        begin
        
        i:=0;
        --判断停止循环的条件
        while i<5 loop
        -- 变量自增
        i:=i+1;
        -- 循环输出
        dbms_output.put_line(i);
        
        end loop;
        
        end ;
 *  for循环
    
    语法：
    
        for 变量 in 循环范围 loop
        
        end loop;
    
    例：
    
        --创建变量
        i number;
        begin
        
        i:=0;
        --循环变量 相当于 循环5次 
        for i in 1..5 loop
        
         dbms_output.put_line(i);
         
        end loop;
        
        end ;
 *  增强for循环
    
    语法：
    
        for 变量 in 游标 loop
        
        end loop;
    
    例：
    
        --创建记录型变量
        userRow t_user%rowtype;
        -- 给变量赋值
        cursor userRows is
        select * from t_user;
        
        begin
        --循环变量
        for userRow in userRows loop
        
           dbms_output.put_line(userRow.Id||','||userRow.Name||','||userRows%rowcount);
           
        end loop;
        
        end ;
 *  跳出本次循环及结束循环
    
    跳出循环 exit
    
    例：
    
        IF i=1 THEN
          exit;
        end if;
    
    跳出本次循环执行下一次循环 continue
    
    例：
    
        IF i=1 THEN
          continue;
        end if;

#### 2.条件分支 ####

*  语法：
    
        if  条件  then 
        执行1
        
        elsif  条件 then
        
        执行2
        
        else
        
        执行3
        
        end if;

`注：这里的else if 的写法是 elsif`

### 游标 ###

语法：

*  创建游标

cursor   (参数列表)  is 查询语句

`注：这里的参数列表是用来在赋值给查询语句中的条件使用的，若查询语句中没有条件，则不用在参数列表中传递参数`

*  打开游标

open  游标名;

*  游标的取值

fetch  游标名 into 变量名;

`注：这一步相当于把一个游标中的值拿出来赋值给了一个变量`

*  关闭游标

close  游标名;

游标的属性：

<table> 
 <thead> 
  <tr> 
   <th>游标的属性</th> 
   <th>返回值类型</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>%rowcount</td> 
   <td>整型</td> 
   <td>获得fetch语句返回的数据行数</td> 
  </tr> 
  <tr> 
   <td>%found</td> 
   <td>布尔</td> 
   <td>最近的fetch语句返回一行数据则为true，否则为false</td> 
  </tr> 
  <tr> 
   <td>%notfound</td> 
   <td>布尔</td> 
   <td>最近的fetch语句返回一行数据则为false，否则为true</td> 
  </tr> 
  <tr> 
   <td>%isopen</td> 
   <td>布尔</td> 
   <td>游标已经打开时值为true，否则为false</td> 
  </tr> 
 </tbody> 
</table>

`注：一般使用%notfound 来判断跳出游标的循环`

#### 无参数型 ####

例：

需求：查询emp表中所有员工的姓名和年龄

--创建游标
    cursor  c_emp is select name,age from emp;
    --声明变量
    v_name emp.name%type;
    v_age  emp.age%type;
    begin
    --打开游标
    open c_emp;
    --遍历游标
    loop 
    --获取游标中的数据
    fetch c_emp  into v_name,v_age; 
    --退出循环条件
    exit when c_emp%notfound;
    --循环输出
    dbms_output.put_line('v_name'||v_name||'_____v_age'||v_age);
    --结束循环
    end loop;
    --关闭游标
    close c_emp;
    
    end;

#### 有参数型 ####

例：

需求：查询emp表中ID为1的员工的姓名和年龄

--创建游标,并声明参数列表
    cursor  c_emp(v_ID emp.ID%type) is select name,age from emp where ID =v_ID;
    
    
    --声明变量
    v_name emp.name%type;
    v_age  emp.age%type;
    begin
    --打开游标时，将参数传递进去
    open c_emp(10);
    
    
    --遍历游标
    loop 
    --获取游标中的数据
    fetch c_emp  into v_name,v_age; 
    --退出循环条件
    exit when c_emp%notfound;
    --循环输出
    dbms_output.put_line('v_name'||v_name||'_____v_age'||v_age);
    --结束循环
    end loop;
    --关闭游标
    close c_emp;
    
    end;

### 无参数的存储过程 ###

例：

CREATE OR REPLACE procedure P_Save  is
    --此处申明变量
    begin
      --此处执行逻辑
    end P_Save;

### 参数的存储过程 ###

例：

CREATE OR REPLACE procedure P_Save (
        p_id IN varchar2,-- in 表示输出  
        p_name  out  varchar2  -- out 表示这个参数会作为返回值返回
    ) Is
    
    
      v_name varchar2(36);
    
    begin
      
    end P_Save;

## Oracle分页 ##

*  定义起始页码函数

CREATE OR REPLACE Function     f_start_line
    
      (
        page In int, --页码
        rows In int --每页行数
      )
      Return number Is
      Begin
        return (page - 1) * rows + 1;
      End f_start_line;

*  定义结束行

CREATE OR REPLACE Function     f_end_line
    (
      page     In int, --页码
      rows  In int --每页行数
    ) Return number Is
    Begin
      return (page*rows);
    End f_end_line;

使用

例：

-- f_start_line(页码，每页展示数)
    -- f_end_line(页码，每页展示数)
    select * from (
    	 
            select rownum rowno,f.* from (
            select ID,姓名 from 用户信息 where 姓名 is not null
            ) f
    				
    ) n where n.rowno Between f_start_line(1,100) And f_end_line(1,100)

## Oracle索引 ##

### 创建索引 ###

*  创建单一索引
    
        create index 索引名称 on 表名(列名);
 *  创建复合索引
    
        create index 索引名称 on 表名(列名1,列名2);

### 删除索引 ###

*  删除索引
    
        drop index 索引名称;

### 查看索引 ###

*  查询表的索引
    
        select * from all_indexes where table_name = '表名称';
 *  查询表的索引列
    
        select* from all_ind_columns where table_name = '表名称';

### 根据索引查询 ###

select * from  表名 where 建立过索引的列，列名 =‘’;

例：

--在 student 表中的name字段上建立索引
    
    create index  IX_name  on student(name);
    
    --根据该索引进行查询
    
    select  * from  student where name='张三'