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