PL/SQL 存储过程

存储过程"/>

PL/SQL 存储过程

一.存储过程

1.存储过程
存储过程是一个要被预编译的代码块，一次编译，多次调用
由于调用的代码较少，减少了网络流量，降低了网络负担

2.存储过程的类型
存储过程分为有参和无参两种
有参又分为 in、out、in out 三种 分别代表传递给储存过程，从储存过程传出，以及对存储过程传入和传出
 

二.存储过程1.无参存储过程

--创建
CREATE OR REPLACE PROCEDURE SP_NO_VAR
ISBEGINDBMS_OUTPUT.PUT_LINE('LOW CHINA');
END;
--调用
CALL SP_NO_VAR();--没有参数
--存储过程相当于只是调用了一些PL/SQL语句 PL/SQL包括很多 例如游标
--存储过程也可以调用存储过程

2.有参存储过程

--创建
--根据参数的值打印员工的姓名
CREATE OR REPLACE PROCEDURE SP_VAR(V_EMPNO NUMBER)
IS
V_EMP EMP%ROWTYPE;--直接定义 不需要写DECLARE
BEGINSELECT * INTO V_EMP FROM EMP WHERE EMPNO=V_EMPNO;DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
END;
CALL SP_VAR(7369);--存储过程调用PL/SQL打印语句

2.1有参、存储过程调用存储过程

--写一个存储过程 能够通过员工的薪资输出员工薪资等级
CREATE OR REPLACE PROCEDURE SP_BD(V_SAL NUMBER)
IS
V_GRADE SALGRADE.GRADE%TYPE;
BEGIN SELECT GRADE INTO V_GRADE FROM EMP E JOIN SALGRADE S ON (E.SAL BETWEEN LOSAL AND HISAL) WHERE E.SAL=V_SAL; DBMS_OUTPUT.PUT_LINE(V_GRADE);
END;CREATE OR REPLACE PROCEDURE SP_DU(V_MGR NUMBER)
IS 
V_EMP EMP%ROWTYPE;
BEGINSP_BD(800);SELECT * INTO V_EMP FROM EMP WHERE MGR=V_MGR;DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
END;
CALL SP_DU(7902);--通过两个存储过程，让一个调用另一个--在这些存储过程的实现中，都有一个特点 参数是隐式定义的 in类型，即向存储过程输入类型
接下来我们看看out类型和in out类型

3.OUT 类型
--不是有效函数或过程名
3.1

CREATE OR REPLACE PROCEDURE SP_OUT(V_EMPNO IN NUMBER,V_SAL OUT NUMBER)--通过同时输入来获得输出
IS
BEGINSELECT SAL INTO V_SAL FROM EMP WHERE EMPNO=V_EMPNO;DBMS_OUTPUT.PUT_LINE(V_SAL);
END;CALL SP_OUT(7369,SAL);--ERRORDECLARE
SAL NUMBER(4);
BEGIN
SP_OUT(7369,SAL);--不能写CALL
END;

3.2

CREATE OR REPLACE PROCEDURE SP_AVG(V_SAL OUT NUMBER)--只输出
IS
BEGINSELECT AVG(SAL) INTO V_SAL FROM EMP ;DBMS_OUTPUT.PUT_LINE(V_SAL);
END;DECLARE
SAL NUMBER(4);
BEGIN
SP_AVG(SAL);--不能写CALL
END;

3.3 IN OUT类型--同时满足向存储过程输入和存储过程向外输出

CREATE OR REPLACE PROCEDURE SP_IN_OUT(V_MIXTURE IN OUT DEPT%ROWTYPE) ISBEGINSELECT * INTO V_MIXTURE FROM DEPT WHERE DNAME=V_MIXTURE.DNAME; DBMS_OUTPUT.PUT_LINE(V_MIXTURE.DEPTNO);END;DECLARE
V_IN DEPT%ROWTYPE;
BEGINV_IN.DNAME:=&DNAME;SP_IN_OUT(V_IN);
END;

4.存储过程调用游标

CREATE OR REPLACE PROCEDURE SP_CUR(V_EMPNO NUMBER,CUR OUT SYS_REFCURSOR) IS--游标类型的参数
--之前就说 SYS_REFCURSOR是游标的一种数据类型
--什么东西会有数据类型？：语言 语言会涉及到基础和复杂数据类型
--但是CUR也有数据类型 为SYS_REFCURSOR

BEGIN
  --游标永远无法避免涉及到 打开 遍历 关闭
  --ref由于无法使用for循环，必须手动打开和关闭游标
 OPEN CUR FOR SELECT * FROM EMP WHERE EMPNO=V_EMPNO;
  
END;
--错误：PLS-00361: 无法用 OPEN 命令打开 IN 游标 'CUR'

CREATE OR REPLACE PROCEDURE SP_CUR(V_EMPNO NUMBER,CUR OUT SYS_REFCURSOR) IS
V_EMP EMP%ROWTYPE;
BEGINOPEN CUR FOR SELECT * FROM EMP WHERE EMPNO=V_EMPNO;--存储过程可以不全部写完游标的过程：打开 遍历 关闭LOOPEXIT WHEN CUR%NOTFOUND;FETCH CUR INTO V_EMP;END LOOP;DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
CLOSE CUR;
END;DECLARE
C SYS_REFCURSOR;--游标类型要一一对应
BEGIN
SP_CUR(7369,C);
END;CREATE OR REPLACE PROCEDURE SP_C(V_EMPNO NUMBER,CUR OUT SYS_REFCURSOR) IS
BEGIN
OPEN CUR FOR SELECT * FROM EMP WHERE EMPNO=V_EMPNO;
END;DECLARE
C SYS_REFCURSOR;
V_EMP EMP%ROWTYPE;
BEGINSP_C(7369,C);
LOOPEXIT WHEN C%NOTFOUND;FETCH C INTO V_EMP;DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);END LOOP;
CLOSE C;END;