Oracle存储过程与存储函数

Oracle存储过程(无参、有参)

常识

类型定义：

1、字符串类型。如：char、nchar、varchar2、nvarchar2。 
2、数值类型。如：int、number(p,s)、integer、smallint。 
3、日期类型。如：date、interval、timestamp。 
4、PL/SQL类型。如：pls_integer、binary_integer、binary_double(10g)、binary_float(10g)、boolean。plsql类型是不能在sql环境中使用的，比如建表时。

存储：

1、变量：在sql拼接中需要使用使用3个单冒号 ，写死的值使用2个单冒号，并使用连接符进行连接
2、赋值：查询中字段赋值使用into：多个、xxx,xxx into xxx,xxx
3、变量：xxxx:=值
4、if||for判断：是否逻辑中都需要存在输出，否则编译不通过
5、EXECUTE IMMEDIATE V_SQL：
用法1
	v_sql:='select TT.ID INTO 变量 from sys_users TT';
	execute immediate v_sql;
用法2
	v_sql:='select count(*) from sys_users';
	execute immediate v_sql into v_num;
	利用as替换declare
	过程可以有参数，用参数替代 块中让用户在运行时输入的值

常用知识点

例子1：我需要查出一列数据存到数组中，并循环输出

DECLARE
 TYPE CONNENT_REC_TYPE IS RECORD(
    TABLE_NUMBER_STR CLOB
 );
 TYPE COMMENT_TYOPE IS TABLE OF CONNENT_REC_TYPE;  
 comment_tab COMMENT_TYOPE;                      
  V_SQL CLOB;
BEGIN
      V_SQL:='SELECT A.FEEDER_GROUP_CODE as TABLE_NUMBER_STR
FROM SP_SO_PLANNING_FEEDER_GROUP A
  WHERE A.FEEDER_GROUP_CODE NOT LIKE ''%-%''
  GROUP BY A.FEEDER_GROUP_CODE';
      EXECUTE IMMEDIATE V_SQL BULK COLLECT INTO comment_tab;
--循环输出
FOR k IN 1..comment_tab.count LOOP
     DBMS_OUTPUT.put_line(comment_tab(k).TABLE_NUMBER_STR);	
END LOOP;
END;

IS TABLE OF ：指定是一个集合的表的数组类型,简单的来说就是一个可以存储一列多行的数据类型。
TYPE：自定义数据类型使用该关键字
BULK COLLECT：采用bulk collect可以将查询结果一次性地加载到collections中。 而不是通过cursor一条一条地处理。

例子2：定义一串数组常量

TYPE mon IS TABLE OF VARCHAR2(5); --定义一个存储单位名称的数组
mon_list mon:=mon('01','02','03','04','05','06','07','08','09','10','11','12');--放入常量
  for i in 1..mon_list.count loop
       DBMS_OUTPUT.LINE((concat(yearBe,mon_list(i));
  end loop;

concat:只能连接两个字符，而“||”可以连接多个字符:concat(‘aa’,‘bb’) ,连接多个需要嵌套：concat(concat(‘aa’,‘bb’),‘cc’)

存储过程(无参、有参)

create or replace procedure myproc1(eno number)
	as
	declare
	i number;
	可执行部分（必需的）
	begin
	select sal into i from emp where empno=eno;
	dbms_output.put_line(i);
	异常处理部分（可选的）
	exception
	when no_data_found then
	dbms_output.put_line('没有这个员工！');
	end;

实例：接受员工号码按规则给员工增长工资

create or replace procedure upsal(eno number) as
	  dd number;
	  ss emp.sal%type;
	  eee exception;
	begin
	  dd := mydno(eno);
	  mysal(eno,ss);
	  --dd=0说明该员工并不存在
	  
	  **if dd = 0 then
	    raise eee;
	  else
	    if dd = 10 then
	      if ss * 1.1 > 5000 then
	        update emp set sal = 5000 where empno = eno;
	      else
	        update emp set sal = sal * 1.1 where empno = eno;
	      end if;
	    elsif dd = 20 then
	      if ss * 1.2 > 5000 then
	        update emp set sal = 5000 where empno = eno;
	      else
	        update emp set sal = sal * 1.2 where empno = eno;
	      end if;
	    elsif dd = 30 then
	      if ss * 1.3 > 5000 then
	        update emp set sal = 5000 where empno = eno;
	      else
	        update emp set sal = sal * 1.3 where empno = eno;
	      end if;
	    else
	      null;
	    end if;
	  end if;
	exception
	  when eee then
	    raise_application_error(-20005, '该员工并不存在！');
	end;**

	调用过程：

create or replace procedure upallsal 
	as
	cursor mycur is select * from emp;
	begin
	for ee in mycur
	loop
	upsal(ee.empno);
	end loop;
	end;

java 处理Oracle存储过程:(语法)

{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
{call <procedure-name>[(<arg1>,<arg2>, ...)]}

执行存储过程：
exec myproc1(7369);
在java应用程序中能接到 数据库 存储过程的返回值
过程的参数分三种： in || out || in out (按地址传递，可进可出)
如何调用函数

declare
	p varchar2(20);
	begin
	p:=myfunc(7777);
	dbms_output.put_line(p);
	end;

Oracle存储函数

一：返回一个字符类型的值：

create or replace function myfunc(eno number) return varchar2
	as
	i varchar2(20);
	begin
	select ename into i from emp where empno=eno;
	return i;
	exception
	when no_data_found then 
	i:='none';
	return i;
	end;

二：返回一个数值类型的值：

create or replace function myfun(eno number)
	return number 
	as
	i number;
	begin
	select sal+nvl(comm,0) into i from emp where empno=eno;
	return i;
	end;

创建包头包体的方式执行

 create or replace package mypack
		as
		type empsurcor is ref cursor;
		--如果使用游标变量作为过程的参数，类型必须是in out
		procedure queryEmpList(dno in number,rmpList in out empsurcor);
		end mypack;
		/
		--包的主体部分
		create or replace package body mypack
		as
		 procedure queryEmpList(dno in number , empList out empcursor)
		 as 
		      begin
		      		open
		      			empList for select * from emp where deptno=dno;
		      end;
		end mypack;

Oracle光标使用

1、什么是游标？用游标有什么作用？

①从表中检索出结果集，从中每次指向一条记录进行交互的机制。
②关系数据库中的操作是在完整的行集合上执行的。

作用：

①指定结果集中特定行的位置。
②基于当前的结果集位置检索一行或连续的几行。
③在结果集的当前位置修改行中的数据。
④对其他用户所做的数据更改定义不同的敏感性级别。
⑤可以以编程的方式访问数据库。

2 如何制作一个指针指向游标的首行？

open mycur

3 如何通过指针提取当前行的纪录并装入变量？

fetch mycur into eee;

4 如何让指针移动？

通过循环的方式或者条件执行的方式

5 如何关闭并释放游标？

 close mycur;

实例：

declare
	cursor mycur is select * from emp;
	eee emp%rowtype;
	begin
	open mycur;
	fetch mycur into eee;
	while mycur%found
	loop
	dbms_output.put_line(eee.ename||','||eee.job);
	fetch mycur into eee;
	end loop;
	close mycur;
	end;

Oracle存储函数与过程的区别：

prcedure过程----〉存储过程 没有返回值的函数
function函数----〉有返回值（只能是一个）
存储函数可以与存储过程互换，存储函数可以在存储过程中调用。