存储过程的基本使用(1)
存储过程作用:
- 可以像编程的方式处理查询的结果,更灵活
- 可以使用游标操作进行更复杂的操作,比如联表查询做不到返回的结果是其他表名,然后再对这些表进行操作
基本使用
创建存储过程
-- 存储过程名如果已存在则替换,如不存在则创建
create or replace procedure p_basic() is
declare
-- 变量声明位置
begin
select 123;
end;
调用存储过程
call p_basic()
变量
变量类型
CHAR、VARCHAR2、NUMBER、INTEGER、LONG、CLOB、BLOB、DATE、%TYPE、%ROWTYPE、BOOLEAN、自定义类型
变量赋值
方式1:直接法
v_flag := 'hello world';
方式2:select into
假设变量名为v_flag
select count(*) into v_flag from students;
方式3:execute immediate 变量名 into 变量名
v_sqlfalg := 'select count(*) from user_tables where table_name='''||v_tablename || '''';
execute immediate v_sqlfalg into v_flag;
注意
- 如果是以SQL的方式得到结果赋值给变量时,SQL的结果不能返回多条。如果SQL结果返回多条的话则建议将数据存储到
临时表
中
条件
IF t_age > 18 then
select '已成年';
ELSE
select '未成年';
END IF;
循环
循环的几种方式
需求:循环输出1到4
loop
create or replace procedure pro_loop is
i number;
begin
i:=0;
loop
i:=i+1;
dbms_output.put_line(i);
if(i>3) then
exit;
end if;
end loop;
end pro_loop;
while
create or replace procedure pro_while is
i number;
begin
i:= 0;
while i<5 loop
i:= i+1;
dbms_output.put_line(i);
end loop;
end pro_while;
for
方式1
create or replace procedure pro_for1 is
i number;
begin
i:= 0;
for i in 1..5 loop
dbms_output.put_line(i);
end loop;
end pro_for1;
方式2
需求:获取每行用户的数据
create or replace procedure pro_for2 is
-- 声明变量获取表中一行的数据
userRow t_user_20210716%rowtype;
-- 定义游标
cursor userRows is select * from t_user;
begin
for userRow in userRows loop
select '用户信息为:'||userRow.id||userRow.name||userRow.age;
end loop;
end pro_for2;
注意:
- for循环的第一种形式不需要手动对变量进行自增;for循环的第二种形式可以使用
for in
方式进行遍历取想要的字段; - 尽量不要在sql中使用
user
变量,因为可能是数据库中的关键字
CONTINUE
作用:当满足某个条件后跳出循环,执行下一次循环
需求:筛选出年龄小于19的用户姓名和年龄
create or replace procedure p_more19() is
declare
cursor user_data is select name,age from t_user;
t_name varchar2(10);
t_age number;
begin
open user_data;
loop
fetch user_data into t_name,t_age;
exit when user_data%NOTFOUND;
if t_age > 19 then
continue;
end if;
select t_age;
end loop;
close user_data;
end;
- 循环中的
exit
、return
和continue
,可参考:https://www.cnblogs.com/fuchangmeng/p/5593537.html
游标的基本使用
循环获取结果集
需求:使用临时表存储用户的信息(创建时间,用户姓名)
create table temp_res(
creation_time varchar2(10),
name varchar2(10)
);
create or replace procedure p_get_userinfo() is
declare
-- 1.创建游标
cursor user_data is select creation_time,name from t_user;
t_creation_time varchar2(10);
t_name varchar2(10);
begin
-- 2.打开游标
open user_data;
-- 4.开启循环,遍历获取数据
loop
-- 6. 将游标的结果赋值给变量。注意:这里的话游标获取的值有几个就需要定义几个变量
fetch user_data into t_creation_time,t_name;
-- 7.如果游标没有数据的话退出循环
exit when user_data%NOTFOUND;
-- 8.对变量进行操作,不过这里一般结合sql做一些操作,比如符合某个条件后做插入的操作等等
select t_creation_time,t_name;
-- 5.结束循环
end loop;
-- 3.关闭游标
close user_data;
end;
字符串拼接
需求:查询大于某个时间段创建的用户信息
-- 方式1:普通字符串
v_sql := 'select * from t_user where to_char(creation_time,''yyyy-MM-dd'')>''2007-01-01''';
-- 方式2:拼接变量
declare
v_creation_time varchar2(100):= '2018-03-01';
begin
v_sql := 'select * from t_user where to_char(creation_time,''yyyy-MM-dd'')>'''||v_creation_time||'''';
-- 执行sql
execute immediate v_sql;
- 字符串中如果要表示单引号,则需要使用两个
'
,这样才表示一个 - 可以使用
||
来拼接变量
其他命令
删除存储过程
drop procedure 存储过程名;
判断表名是否存在
oracle内部会自带一个表user_tables
,其中table_name
然后会记录这个库中存在哪些表。
注意:
- 表名需要大写。
- 表名中间不支持
-
select * from user_tables where table_name='T_USER'