Loading

存储过程的基本使用(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;

游标的基本使用

循环获取结果集
需求:使用临时表存储用户的信息(创建时间,用户姓名)

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'

参考资料

posted @ 2021-07-15 20:49  ^Mao^  阅读(152)  评论(0编辑  收藏  举报