oracle函数学习笔记

1. 变量

1.1 普通变量

直接赋值：v_name:='dy'

​ declare 里可以声明变量并赋值：v_name varchar(20) := 'dy';

语句赋值： select ... into ...

1.2 引用型变量

​ (声明的变量不用制定大小，而是根据所查表中字段决定)

表名.列名%TYPE: vname emp.ename%type; 即变量vname使用ename这个字段的字段类型和大小

1.3 记录型变量（接受表中的一整行记录）

表名%ROWTYPE: v_name emp%ROWTYPE;

declare
 v_emp emp%ROWTYPE
begin
 select * into v_emp from emp where empid=1100;
 dbms_output.put_line('name:'||v_emp.ename||'salary:'||v_emp.sal);
end;

2.流程控制

2.1 条件分支

begin
	if... then...
	elsif ... then ...
	else ...
	end if;
end;

​ 例子：判断表中记录是否超过20条，10-20条，10条以下

declare
	v_count number;
begin
	select count(*) into v_count from emp;
	
	if v_count >20 then 
		dbms_output.put_line('超过了20条，数量为：'||v_count);
	elsif v_count>=10 then
		dbms_output.put_line('10-20条，数量为：'||v_count);
	else
		dbms_output.put_line('10条以下，数量为：'||v_count);
	end if;
end;

2.2循环

​ oracle中有三种循环方式，loop,for,while

2.2.1 LOOP

begin
	LOOP
		EXIT WHEN 退出循环条件
	END LOOP;
END;

例子：打印数字1-10

declare
	v_num number :=1;	
begin
	loop
	exit when v_num=10;
	dbms_output.put_line(v_num);
	v_mum:=v_num+1;
	end loop;
end;

3.游标

3.1语法

声明：cursor 游标名（参数） is 查询语句

打开：open 游标名

读取：fetch 游标名 into 变量列表

关闭：close 游标名

3.2属性

属性	返回值的类型	说明
%ROWCOUNT	整型	获得FETCH语句返回的数据行数
%FOUND	布尔	最近的FETCH语句返回一行数据则为真，否则为假
%NOTFOUND	布尔	与%FOUND返回值相反
%ISOPEN	布尔	游标已经打开时为真，否则为假

其中%NOTFOUND是在游标中找不到元素的时候返回TRUE,通常用来判断退出循环

例子：用游标来查询emp表中所有员工的姓名和工资，并依次打印出来

declare
	cursor c_emp is
		select ename,sal from emp;
	v_name emp.ename%TYPE;
	v_sal emp.sal%TYPE;
	
begin
	--打开游标
	open c_emp;
	
	--遍历游标
	loop
	
		--获取游标中的数据
		fetch c_emp into v_name，v_sal；
	
		--退出循环的条件
		exit when c_emp%NOTFOUND;
	
		--如果游标中有数据就打印
		dbms_output.put_line(v_ename||' - '||v_sal);
	
	end loop;
	
	--关闭游标
	close c_emp;
end;

3.3 带参数的游标

例子：使用游标查询并打印某部门的员工的姓名和薪资，部门编号运行时手动输入

declare
	cursor c_emp(v_deptno emp.deptno%TYPE) is
		select ename,sal from emp where deptno=v_deptno;
	
	
	v_name emp.ename%TYPE;
	v_sal emp.sal%TYPE;
	
begin
	--打开游标(第10号部门)
	open c_emp(10);
	
	--遍历游标
	loop
	
		--获取游标中的数据
		fetch c_emp into v_name，v_sal；
	
		--退出循环的条件
		exit when c_emp%NOTFOUND;
	
		--如果游标中有数据就打印
		dbms_output.put_line(v_ename||' - '||v_sal);
	
	end loop;
	
	--关闭游标
	close c_emp;
end;

4. 存储过程---------没有declare关键字，declare用在语句块中

4.1 语法

create or replace procedure 名称（参数） is
begin

end(名称);

4.2 分类

根据参数类型分为不带参数的，带输入参数的，带输入输出参数(返回值)的

4.2.1 无参存储

create or replace procedure p_hello as
	begin
		dbms_output.put_line('hello');
	end p_hello;

通过plsql调用存储过程：

begin

	p_hello;
	p_hello;
	p_hello;

end;

SQL窗口：exec p_hello；

4.2.2 有输入参数存储

例子：查询并打印某个员工（如777号）的姓名，薪水，要求调用的时候传入编号，自动打印

create or replace procedure p_1(i_empno in emp.empno%TYPE) is

	--声明变量接受查询结果
	v_name emp.ename%TYPE;
	v_sal emp.sal%TYPE;
	
	begin
	
		select ename,sal into v_name,v_sal from emp where empno=i_empno;
		dbms_output.put_line('姓名'||v_name||',薪水:'||v_sal);
	end p_1;

4.2.3 有输出参数存储

例子：输入员工号查询777号员工的信息，要求将薪水作为返回值输出，给调用的程序使用

create or replace procedure p_2(i_empno in emp.empno%TYPE,o_sal out emp.sal%TYPE) is

begin
	select sal into o_sal from emp where empno=i_empno;
end;

用一个函数去调用存储过程，输出值。也可用JAVA程序调用p_2获取值。

declare
	v_sal emp.sal%TYPE;
begin
	p_2(777,v_sal);
	dbms_output.put_line(v_sal);
end;