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

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

• 变量的声明应该在declare下
• begin和end中间相当于方法体
• 打印语句为 dbms_output.put_line(‘年后’);

运算符

• 关系运算符

  =  等于
  <>,!=  不等于
  <    小于
  >    大于
  <=   小于等于
  >=   大于等于
  1
  2
  3
  4
  5
  6

• 一般运算

  +加
  -减
  *乘
  /除
  :=赋值号  查询赋值语法为  select 字段名 into 变量名 from 表名
  =>关系号
  ..范围   例：1..8表示1到8
  ||字符连接
  1
  2
  3
  4
  5
  6
  7
  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
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15

变量的声明及赋值

1.基本数据类型

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

例：

v_name varchar(20);
v_age number;
1
2

符号： :=

语法：

• 直接赋值

  例：

  v_name:='zhangsna';
  1

• 语句赋值

  select 值1,值2 into 变量1，变量2 from 表

  例：

  select '张三',12 into v_name,v_age from dual
  1

2.引用数据类型

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

例：

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

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

例：

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

3.记录型变量

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

例：

v_emp emp%rowtype;
1

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

赋值：语法为

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

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

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

例：

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

流程控制

1.循环

• loop循环

  语法：

  loop
  
  exit when 退出循环的条件
  
  end loop;
  1
  2
  3
  4
  5

  例：

  v_index number :=1;
  
  begin 
  
  loop 
  --判断条件
  exit when v_index=3
  --循环输出
  dbms_output.put_line(v_index);
  --变量自增
  v_index:=v_index+1;
  end loop;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12

• while循环

  语法：

  
  while 判断条件 loop
  
  循环体
  
  end loop;
  
  1
  2
  3
  4
  5
  6
  7

  例：

  --声明变量
  i number;
  begin
  
  i:=0;
  --判断停止循环的条件
  while i<5 loop
  -- 变量自增
  i:=i+1;
  -- 循环输出
  dbms_output.put_line(i);
  
  end loop;
  
  end ;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15

• for循环

  语法：

  
  for 变量 in 循环范围 loop
  
  end loop;
  
  1
  2
  3
  4
  5

  例：

  --创建变量
  i number;
  begin
  
  i:=0;
  --循环变量 相当于 循环5次 
  for i in 1..5 loop
  
   dbms_output.put_line(i);
   
  end loop;
  
  end ;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13

• 增强for循环

  语法：

  
  for 变量 in 游标 loop
  
  end loop;
  
  1
  2
  3
  4
  5

  例：

  --创建记录型变量
  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 ;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15

• 跳出本次循环及结束循环

  跳出循环 exit

  例：

  IF i=1 THEN
    exit;
  end if;
  1
  2
  3

  跳出本次循环执行下一次循环 continue

  例：

  IF i=1 THEN
    continue;
  end if;
  1
  2
  3

2.条件分支

• 语法：

  if  条件  then 
  执行1
  
  elsif  条件 then
  
  执行2
  
  else
  
  执行3
  
  end if;
  
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13

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

游标

语法：

• 创建游标

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

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

• 打开游标

  open  游标名;
1

• 游标的取值

  fetch  游标名 into 变量名;
1

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

• 关闭游标

  close  游标名;
1

游标的属性：

注：一般使用%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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

有参数型

例：

需求：查询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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

无参数的存储过程

例：

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

参数的存储过程

例：

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;
1
2
3
4
5
6
7
8
9
10
11

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;
1
2
3
4
5
6
7
8
9
10

• 定义结束行

CREATE OR REPLACE Function     f_end_line
(
  page     In int, --页码
  rows  In int --每页行数
) Return number Is
Begin
  return (page*rows);
End f_end_line;
1
2
3
4
5
6
7
8

使用

例：

-- 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)
1
2
3
4
5
6
7
8
9

Oracle索引

创建索引

• 创建单一索引

  create index 索引名称 on 表名(列名);
  1

• 创建复合索引

  create index 索引名称 on 表名(列名1,列名2);
  1

删除索引

• 删除索引

  drop index 索引名称;
  1

查看索引

• 查询表的索引

  select * from all_indexes where table_name = '表名称';
  1

• 查询表的索引列

  select* from all_ind_columns where table_name = '表名称';
  1

根据索引查询

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

例：

--在 student 表中的name字段上建立索引

create index  IX_name  on student(name);

--根据该索引进行查询

select  * from  student where name='张三'
1
2
3
4
5
6
7