oracle使用笔记

oracle学习笔记

 ===============================

存储过程procedure

无参数的存储过程创建如下：

<!-- 创建格式如下 -->
create or replace procedure 存储过程名称　　<!-- 名称后面不跟括号 -->
as/is
begin
具体逻辑；
end;


<!-- 例子：创建存储过程myProcedure -->
create or replace procedure myProcedure
as
begin
update student set name = '王五' where id = 1;
end;

<!-- 调用存储过程myProcedure -->
begin
myProcedure();　　<!-- 调用无参存储过程时需要加上括号 -->
end;

查看结果
select * from student;
id | name
1  | 王五

 

有参存储过程创建如下

create or replace procedure updateById
(
v_id number,
v_name varchar2
)
as
begin
update student set name = v_name where id = v_id;
end;

<!-- 调用存储过程updateById -->
begin
updateById(1,'张三');
end;

查看结果
select * from student;
id | name
1  | 张三

 

有返回值的存储过程创建如下 

create or replace procedure findById
(
v_id number, <!-- 入参用in或者不写 -->
r_name out varchar2<!-- 出参用out -->
)
as
begin
select name into r_name from student where id = v_id;
end;

调用findById
declare name varchar2(20);
begin
findById(1,name);
dbms_output.put_line('name = '|| name);
end;

DBMS导出
name = 张三

 

oracle函数

创建格式如下,函数有返回值，存储过程无返回值

create or replace function 函数名称 return varchar2
as
begin
逻辑体;
return null;
end;

 

创建无参函数，如下

create or replace function myFunction return varchar2
as
begin
update student set name = '王五' where id = 1;
return null;
end;

<!-- 调用myFunction -->
declare 
　　name varchar2(20);
begin
　　name := myFunction();
end;


select * from student;
id  |   name
1      |   王五
2      |   赵四

 

创建无出参函数

create or replace function inFunction
(
v_id number
)return varchar2
as
t_name varchar2(20);
begin
select name into t_name from student where id = v_id;
return t_name;
end;

<!-- 调用inFunction -->
declare
　　t_name varchar2(20);
begin
　　t_name := inFunction(1);
　　dbms_output.put_line('name = ' || t_name);
end;

DBMS导出
name = 王五

 

创建出参函数

create or replace function outFunction
(
 v_id number,
 r_name out varchar2
) return number
as
begin
select name into r_name from student where id = v_id;
return v_id +1;
end;

<!-- 调用outFunction -->
declare
　　name varchar2(20);
　　id number(1);
begin
　　id := outFunction(1,name);
　　dbms_output.put_line('id = ' || id || '---> name = ' || name);
end;

DBMS导出
id = 2--->  name = 王五

 

oracle游标cursor

显性游标

定义格式
declare 
    cursor 游标名称 is select表达式;

 

创建显性游标，并使用for循环

declare
    cursor myCursor is select * from student;
begin
    for stu in myCursor loop
        dbms_output.put_line('id = ' || stu.id || '--> name = ' || stu.name );
    end loop;
end;

DBMS导出
id = 1--> name = 王五
id = 2--> name = 赵四

 

游标使用for循环，不需要手动开启和关闭游标

declare
    cursor myCursor is select * from student;
begin
    for stu in myCursor loop
        exit when myCursor%notfound;
        dbms_output.put_line('id = ' || stu.id || '-----> name = ' || stu.name);
    end loop;
end;

 

游标使用fetch遍历，需要手动开启和关闭游标

declare
    cursor fetchCursor is select * from student;
    row fetchCursor%rowtype;
begin
    if fetchCursor%isopen = false then
        open fetchCursor;
    end if;
　　 loop
    fetch fetchCursor into row;
    dbms_output.put_line('id = ' || row.id || '-------> name = ' || row.name);
　　 end loop;
    close fetchCursor;
end;

 

oracle触发器trigger

创建触发器格式如下

create (or replace) trigger 触发器名称 触发切点 触发事件
on 表明
[for each row]<!-- 行级别 -->
begin
    逻辑语句；
end;

 

为student表创建触发器：编号为1的学生不可更新数据，具体如下

create or replace trigger notUpdate before update 
on student
for each row
begin
    if :new.id = 1 then 
       RAISE_APPLICATION_ERROR(-20600,'id=1的学生信息不可修改'); 
    end if;    
end;

 

oracle自定义异常，先声明再处理

create or replace procedure myException
(
v_id number
)
as
exc exception;
begin
    if v_id = 1 then
       raise exc;
    exception when exc then
       dbms_output.put_line('id 不能为1...');
end;

绑定异常
create or replace procedure myException
(
v_id number
)
as
exc exception;
pragma exception_init(exc,-06550);
begin
    if v_id = 1 then
       raise exc;
    exception when exc then
       dbms_output.put_line('id 不能为1...');
end;

 

oracle 自定义异常，raise_application_error，如下

create or replace procedure pro2
(
v_id number
)
as
begin
    if v_id = 1 then
       raise_application_error(20000,'id 不能为1');
    end if;
end;

begin
  pro2(1);
end;

begin
pro2(1);
end;


> ORA-20000: id不能为1
ORA-06512: 在 "SASIS.PRO2", line 8
ORA-06512: 在 line 2

> 时间: 0.001s

 

 oracle条件语句if 

<!-- if判断流程如下 -->
if ... then
　　...
elsif ... then
　　...
else ....
end if;

<!--例如 -->
create or replace function ifFunction
(
    age NUMBER
)return VARCHAR2
as
begin
    if age >0 and age <18 then
        return '少年';
    elsif age < 30 then
        return '青年';
    else return '老年';
    end if;
end;

 

oracle条件控制case语句

<!-- case格式如下 -->
case 参数
　　when ... then...;
　　when ... then...;
　　else ...
end case;

<!-- 示例一 -->
create or replace function caseFunction
(
city VARCHAR2
)return VARCHAR2
as
country VARCHAR2(20);
begin
    case city
        when '北京' then country := '中国';
        when '纽约' then country := '美国';
        when '巴黎' then country := '法国';
        else country := '其他';
    end case;
    return country;
end;

<!-- 示例二 -->
create or replace function caseFunction
(
city VARCHAR2
)return VARCHAR2
as
country VARCHAR2(20);
begin
    case 
        when city = '北京' then country := '中国';
        when cigy = '纽约' then country := '美国';
        when cigy = '巴黎' then country := '法国';
        else country := '其他';
    end case;
    return country;
end;

 

oracle循环

1、简单循环loop

<!-- loop 结构如下-->
loop
　　逻辑语句;
　　exit when 条件语句;
end loop;

<!-- 示例一 -->
declare 
    i NUMBER;
begin
    i := 1;
    loop 
        i := i +1;
        exit when i >=10;
        dbms_output.put_line('i = ' || i);
    end loop;
end;

<!-- 示例二 -->
declare 
    i NUMBER;
begin
    i := 1;
    loop 
        i := i +1;
        if i >= 10 then
　　　　　　　exit;
　　　　　end if;
        dbms_output.put_line('i = ' || i);
    end loop;
end;

 

2、while循环

declare 
    i NUMBER;
begin
    i := 1;
    while i < 10 loop
        i := i +1;
        dbms_output.put_line('i = ' || i);
    end loop;
end;

 

3、for循环

declare
    i NUMBER;
begin
    for i in 1..10 loop
        dbms_output.put_line('i = ' || i);
    end loop;
end;