【Oracle】第18天 存储过程和函数

存储过程

定义

存储过程是一个有名字的plsql代码块
他没有返回值但是有输出参数，（可以将数据传给外部程序）参数类型有三种： IN,OUT,INOUT
存储过程创建之后会保存到数据库中
当数据库启动时，自动的加载到内存中（一次创建，多次使用）

创建的语法

CREATE [OR REPLACE]  PROCEDURE 存储过程名[(参数名 IN|OUT|INOUT 数据)]

IS|AS
BEGIN 

END;

IN:输入参数，只能读不能修改，他是默认的参数类型，关键字可以省略，可以以任意三种方式传参
OUT:输出参数，作用：将程序的处理结果传给外部程序，它只能以传变量的方式传参
INOUT:结合IN和OUT参数，它也只能以传变量的方式传参。

CREATE OR REPLACE PROCEDURE P1
IS 
BEGIN
	FOR I IN 1..9 LOOP
		FOR J IN 1..I LOOP
			DBMS_OUTPUT.PUT(J||'*'||I||'='||(I*J)||' ');
			IF I*J <10 THEN 
				DBMS_OUTPUT.PUT(' ')
			END IF;
		END LOOP;
		DBMS_OUTPUT.NEW_LINE();
	END LOOP;
END 

举例：写一个储存过程，传入两个参数，一个参数表示部门编号，一个参数数字

存储过程的调用

1.在代码块中调用

DECLARE

BEGIN
	存储过程名（参数，）；
END;

2.使用CALL命令调用

DECLARE

BEGIN
	CALL 存储过程名(参数);
END;

3.使用EXEC命令调用

DECLARE

BEGIN
	EXEC 存储过程名（参数）;--只能在sqlplus中使用
END;

不可以在存储过程中修改IN类型参数的值

举例：写一个存储过程，传入两个参数，一个参数表示部门编号，一个参数是输出查询到的部门的员工人数合计数

CREATE OR REPLACE PROCEDURE P3(DNO NUMBER,N OUT NUMBER)
IS
BEGIN
	SELECT COUNT(1) INTO N FROM EMP WHERE DEPTNO=DNO;
END;

调用：

DECLARE
	--声明一个变量保存员工人数
	C NUMBER;
BEGIN
	--调用存储过程P3
	P3(20,C)
	DBMS_OUTPUT.PUT_LINE(C);
END;

举例：结合in和out参数，只以传变量的方式传参

CREATE OR REPLACE PROCEDURE P4(N IN OUT NUMBER)
IS
BEGIN
	SELECT COUNT(1) INTO N FROM EMP WHERE DEPTNO=N;
END;

调用：

DECLARE
	--声明一个变量保存员工人数
	V NUMBER:=&部门编号;
BEGIN
	--调用存储过程
	P4(V)
	DBMS_OUTPUT.PUT_LINE(V);
END;

举例：写一个存储过程，传入一个部门编号，将部门下的员工信息以游标返回

CREATE OR REPLACE PROCEDURE P5(DNO NUMBER,V OUT SYS_REFCURSOR);--这里为什么用系统游标呢？
IS
BEGIN
	OPEN V FOR SELECT * FROM EMP WHERE DEPTNO=DNO;--打开游标
END;

调用上述存储过程P5

DECLARE	
	CUR SYS_REFCURSOR;
	V EMP%ROWTYPE;
BEGIN
	P5(10,CUR);
	LOOP
		FETCH CUR INTO V;
		EXIT WHEN CUR%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE(V.EMPNO||','||V.ENAME||','||V.JOB||','||V.MGR||','||V.HIREDATE||','||V.SAL||','||V.COMM||','||V.DEPTNO);
	END LOOP;
	CLOSE CUR;
END;

层次查询

SELECT 列名 FROM 表名 START WITH 条件 CONNECT BY PRIOR 连接条件;

举例：

SELECT EMP.*,LEVEL FROM EMP START WITH MGR IS NULL CONNECT BY PRIOR EMPNO=MGR;

→

将一个表中全部的人的层级按照职位高低排序叫做层级查询
层级查询需要用到的 伪列叫做 LEVEL

函数

定义

函数也是一个有名字的plsql代码块，他一般来完成一个功能和计算过程。函数必须调用时使用返回值，参数分为输入参数和输入输出参数，

注意：

1.函数中不允许使用临时表
2.在一些情况下不能使用dml语句
3.函数可以在代码块中调用，也可以在sql语句中使用

语法

CREATE [OR REPLACE] FUNCTION 函数名[(参数 IN|OUT|IN OUT 数据类型,...)] RETURN 返回值类型
IS
BEGIN
	RETURN语句
END;

举例：写一个函数，计算一个数的阶乘，并返回阶乘结果

CREATE OR REPLACE FUNCTION FN1(N NUMBER)
RETURN