oracle学习笔记3

语句块

declare
    num1 number := 20;
    num2 number := 30;
begin
    dbms_output.put_line(mod(num2, num1)); -- 10
end;

 

异常处理

%type--->该变量的数据类型与已经声明变量的类型或者表中列的类一致
%rowtype --->来表示一行数据

declare
    v_user   tb_user%rowtype; --表示表中的一行数据
begin
    select * into v_user from tb_user where user_id = 120;
    -- 数据的输出: 变量名称.列名
    -- 输出姓名 v_user.user_name
    dbms_output.put_line('编号:' || v_user.user_id || ' 姓名:' ||  v_user.user_name);
    dbms_output.put_line('没有异常'); -- 异常出现时，后面的代码无法执行
exception
    when others then-- others 类似java中的exception
    dbms_output.put_line('查询不到数据');
end;

 

条件判断

1、条件逻辑
1）
if 布尔类型 then
符合条件的代码;
end if;

2）
if 布尔类型表达式 then
符合条件的代码
else
上面条件不满足而执行的代码
end if;

3）
if 布尔类型表达式 then
符合条件的代码
elsif 布尔类型表达式 then
符合条件的代码
else
上面条件不满足而执行的代码
end if;

2、 循环
1） loop 循环 ：
loop
重复执行的代码
end loop;
如果结束循环则使用exit

2） loop endloop 循环
while 条件 loop
重复执行的代码;
end loop;

3）for 变量 in [reverse] 最小值 .. 最大值 loop
重复执行的代码
end loop;

declare
　　v_num number :=0;
　　v_num2 number :=0;
begin
　　for v_num in 1 .. 9 loop
　　　　for v_num2 in 1 .. v_num loop
　　　　　　dbms_output.put(v_num2||'*'||v_num||'='||(v_num*v_num2)||' ');
　　　　end loop;
　　　　dbms_output.put_line('');
　　end loop;
end;

 

游标：

       把select语句查询出来的结果保存在内存中，然后利用循环等手段来获取该内存中的数据，由于数据是保存在内存中，所以速度快，但是对于大批量数据来说，会占用大量的内存可能导致oracle软件的运行变得更加缓慢。游标适合保存查询量少的数据。

 

declare
　　cursor myCursor is select * from tb_user;-- 把tb_user表中的数据保存在内存中，这块内存的名称叫myCursor
　　v_user tb_user%rowtype;
begin 
　　-- 1.打开游标 open 游标名字 游标只有先打开后才能使用
　　open myCursor;
　　-- 2.从游标中提取数据
　　loop 
　　　　fetch myCursor into v_user;
　　　　if myCursor%notfound then -- 当遍历到最后一条数据时，%notfound--->false
　　　　　　exit ;-- 结束循环
　　　　end if;
　　　　--可以输出一下数据
　　　　dbms_output.put_line(v_user.user_name); 
　　end loop;
　　--3.关闭游标 close 游标名称
　　close myCursor;
end;

 

循环

declare
　　cursor myCursor is select * from tb_user;
　　v_user tb_user%rowtype;
　　begin
　　open myCursor;
　　while true loop -- 必须先进入while loop循环
　　　　fetch myCursor into v_user;
　　　　if myCursor%notfound then -- 当遍历到最后一条数据时，%notfound--->false
　　　　　　exit ;-- 结束循环
　　　　end if;
　　　　dbms_output.put_line(v_user.user_name); 
　　end loop;
　　close myCursor;
end;

 

带参数的显式游标

declare
　　cursor myCursor(v_name varchar2) is
　　select * from tb_user where user_name = v_name;
　　v_user tb_user%rowtype;
begin
　　-- 打开游标时，传入实参
　　open myCursor('张三3');
　　loop
　　-- 从游标中提取数据
　　　　fetch myCursor into v_user;
　　　　if myCursor%notfound then
　　　　　　exit;--退出循环
　　　　end if;
　　　　dbms_output.put_line(v_user.user_id);
　　end loop;
　　close myCursor;
end;

 

ref游标

declare
　　--声明ref游标的类型
　　type refCursor is ref cursor;
　　-- 声明ref游标变量
　　myCursor refCursor; -- v_name varchar2(30)
　　v_user tb_user%rowtype;
begin
　　open myCursor for select * from tb_user; -- 在打开游标时才传入select语句
　　-- ref游标与显式游标最大的不同点在于ref游标的select语句是在open时才 确定的
　　loop
　　　　fetch myCursor into v_user;
　　　　　　if myCursor%notfound then
　　　　　　　　exit;
　　　　　　end if;
　　　　dbms_output.put_line(v_user.age);
　　 end loop; 
　　　　-- 输出有多少条数据
　　 dbms_output.put_line(myCursor%rowcount);
　　　close myCursor;
end;

 

declare
　　type refCursor is ref cursor return tb_user%rowtype; -- 声明一个ref游标类型,并指定返回类型
　　myCursor refCursor;
　　v_user tb_user%rowtype;
begin
　　open myCursor for select * from tb_user;
　　loop
　　　　fetch myCursor into v_user;
　　　　if myCursor%notfound then
　　　　　　exit;
　　　　end if;
　　　　dbms_output.put_line(v_user.age);
　　end loop; 
　　close myCursor;
end;

 

存储过程：

入参

create or replace procedure pro2(v_name in varchar2) is
　　v_age number; 
begin
　　select age into v_age from tb_user where user_name=v_name;
　　dbms_output.put_line(v_name||'的年龄'||v_age);
exception
　　when others then
　　dbms_output.put_line('查询出错!');
end;

出参

create or replace procedure pro3(v_count out number) is
begin
　　select count(*) into v_count from tb_user;--查询用户的总记录数
　　-- count(*)的效率和count(user_id)的效率是一样的
end;

declare
　　v_count number;
begin
　　pro3(v_count);
　　dbms_output.put_line(v_count); 
end;

 in out --- 同一变量即是输入参数又是输出参数

-- 输入一个数字，获取其倍数
create or replace procedure pro4(v_num in out number) is
begin
　　v_num := v_num*2;
end;

declare
　　num number := 10;
begin
　　pro4(num);
　　dbms_output.put_line(num);
end;

 

create or replace procedure pro5(v_name in varchar2) is
　　cursor myCursor(v_name2 varchar2) is
　　select * from tb_user where user_name like v_name2;
　　v_user tb_user%rowtype;
begin
　　open myCursor(v_name);
　　loop
　　　　fetch myCursor into v_user;
　　　　if myCursor%notfound then
　　　　　　exit;
　　　　end if;
　　　　dbms_output.put_line('编号:'||v_user.user_id||' 姓名:'||v_user.user_name);
　　end loop;
　　close myCursor;
end;

declare
　　begin
　　pro5('%张%');
　　end;

 

函数---自定义函数
-- 函数的定义和存储过程的定义是类似的，使用函数时,就不能使用out和 in out模式了

-- 自定义具有加法功能的函数，传入一个数字，加上100，返回其结果
create or replace function addNum(v_num in number) 
return number -- 函数的返回类型
is
-- 局部变量
begin
return v_num+100;
end;

-- 使用
select addnum(1000) from dual;
select * from tb_user where age > addnum(100);
select user_name,addnum(age) from tb_user;

-- 用户输入两个数字，返回值较大的数字
create or replace function compare(v_num1 in number,v_num2 in number)
return number
is
begin
if v_num1 > v_num2 then
return v_num1;
else
return v_num2;
end if;
end;

 

 

包

包的使用: 包中变量的使用: 包名.变量名称
-- 存储过程: 包名.存储过程名称[(参数)]
-- 函数: 包名.函数名称();

 

-- 第一步: 创建包头
create package package3 is
-- 声明
procedure pro1; -- 声明一个存储过程
end;

-- 第二步：创建包体
create or replace package body package3 is
procedure pro1 is -- 存储过程的实现
v_name2 varchar2(30);
begin
v_name2 :='张三';
dbms_output.put_line(v_name2);
end;
begin
null; -- 不想写代码，直接使用null值替代
end;

declare
begin
package3.pro1; -- 调用包中的存储过程!!
end;

 

-- 1.建包头
create or replace package package5 is
function multi(v_num1 in number,v_num2 in number)return number; --函数的声明
end; 

-- 2.建包体,写函数的实现！！
create or replace package body package5 is
function multi(v_num1 in number ,v_num2 in number) return number is
begin
return v_num1*v_num2;
end; 
begin
null;
end;

-- 调用包中的函数
declare
v_result number :=0;
begin
v_result := package5.multi(100,100);
dbms_output.put_line(v_result);
end;

 

-- 在包中写游标
create or replace package package6 is
-- 声明一个显示游标
cursor myCursor is select * from tb_user;
end;

create or replace package body package6 is
v_user tb_user%rowtype;
begin
open myCursor;
loop
fetch myCursor into v_user;
if myCursor%notfound then
exit;
end if;
dbms_output.put_line(v_user.user_name);
end loop;
close myCursor;
end;