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 游标名;
游标的属性:
游标的属性 | 返回值类型 | 说明 |
---|---|---|
%rowcount | 整型 | 获得fetch语句返回的数据行数 |
%found | 布尔 | 最近的fetch语句返回一行数据则为true,否则为false |
%notfound | 布尔 | 最近的fetch语句返回一行数据则为false,否则为true |
%isopen | 布尔 | 游标已经打开时值为true,否则为false |
注:一般使用%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='张三'