--------------------存储过程:把一些经常用到的操作封装起来,存在数据库里面,便于后续的多次调用
存储过程一般用来做数据同步
--语法结构:
CREATE OR REPLACE PROCEDURE 存储过程名[(参数1 [参数类型] 数据类型[,参数2 参数类型 数据类型,....])]
IS --/ AS
--声明部分
BEGIN
--逻辑体
END;
--开发规范:存储过程名以 SP_ 开头
--需要注意的是:
1、CREATE OR REPLACE(创建或者替换/更新)的依据就是存储过程名
意思就是:当过程名不存在的时候,就是创建,当存在的时候,就是更新这个存储过程
2、存储过程就是将一些功能存在数据库里,供有权限的用户来调用
3、存储过程中不要写接收用户的输入,如果需要接收用户输入,那么在调用的时候,以参数的形式传值进去
4、如果需要声明/定义,那么在 IS / AS 下面,没有DECLARE
--比如:用存储过程实现,打印部门10的员工的工号和姓名
--写sql
SELECT EMPNO,ENAME
FROM EMP
WHERE DEPTNO = 10;
--实现打印的功能
DECLARE
CURSOR C_DEPT IS
SELECT EMPNO,ENAME
FROM EMP
WHERE DEPTNO = 10;
BEGIN
FOR X IN C_DEPT LOOP
DBMS_OUTPUT.PUT_LINE('工号:'||X.EMPNO||' 姓名:'||X.ENAME);
END LOOP;
END;
--套存储过程, 把上面这个打印部门10的员工的工号和姓名的操作/功能封装起来
CREATE OR REPLACE PROCEDURE SP_DEPT10
IS
CURSOR C_DEPT IS
SELECT EMPNO,ENAME
FROM EMP
WHERE DEPTNO = 10;
BEGIN
FOR X IN C_DEPT LOOP
DBMS_OUTPUT.PUT_LINE('工号:'||X.EMPNO||' 姓名:'||X.ENAME);
END LOOP;
END;
---调用存储过程的语法(就是写一个plsql代码块,以过程名的形式直接调用)
[DECLARE
--声明
]
BEGIN
--逻辑体
--按需多次调用
存储过程名;
存储过程名;
.
.
END;
--比如:调用上面的打印部门10的员工信息的存储过程
BEGIN
SP_DEPT10;
END;
--小练习一把:创建一个sp,打印部门20的员工的工号,姓名,部门编号,部门名称,并成功调用
CREATE OR REPLACE PROCEDURE SP_DEPT20 IS
CURSOR C_DEPT IS
SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E
JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE E.DEPTNO = 20;
BEGIN
FOR X IN C_DEPT LOOP
DBMS_OUTPUT.PUT_LINE('工号:' || X.EMPNO || ' 姓名:' || X.ENAME || ' 部门编号:' ||
X.DEPTNO || ' 部门名称:' || X.DNAME);
END LOOP;
END;
--写完一个sp后,先检查一下是否有问题(红色X),如果有,说明里面写的有问题,
--可以右键这个sp,进行编辑,在里面修改问题,修改完成后,重新执行
--测试一下/调用:两种方式
1、右键存储过程名,选择 “测试”
2、自己写一个代码块调用一下这个sp
BEGIN
SP_DEPT20;
END;
--在调用sp的时候,照样可以写其他的逻辑
--比如:
DECLARE
X NUMBER := &输入一个数;
BEGIN
IF X < 1024 THEN
SP_DEPT20;
ELSE
DBMS_OUTPUT.PUT_LINE(X);
END IF;
END;
---------------------------带参数的存储过程:存储过程名[(参数1 [参数类型] 数据类型[,参数2 参数类型 数据类型,....])]
--需要注意的是:
1、参数起到占位置的作用(在你需要一个值,但是又不确定到底是啥的时候,先用参数把这个位置给占着,等要用的时候,再给它一个具体的值)
2、sp中,如果需要参数,那么不能省略(无法用变量替代),因为它是外部调用该sp传值的唯一路径
3、参数只需要定义类型,不需要定义长度
4、参数可以定义多个,每个参数的类型可以彼此不同
5、定义了几个参数,那么在传值的时候,就要传多少个
--比如:穿件一个sp,打印某个部门的员工的工号,姓名,并成功调用
CREATE OR REPLACE PROCEDURE SP_DEPT(P_DEPTNO NUMBER)
IS
--V_DEPTNO NUMBER ;
CURSOR C_DEPT
IS
SELECT EMPNO,ENAME
FROM EMP
WHERE DEPTNO = P_DEPTNO;
BEGIN
FOR X IN C_DEPT LOOP
DBMS_OUTPUT.PUT_LINE('工号:'||X.EMPNO||' 姓名:'||X.ENAME);
END LOOP;
END;
--调用
BEGIN
SP_DEPT(30);
END;
---小练习一把:创建一个sp,打印某个工资等级的员工的工号,姓名,工资等级
CREATE OR REPLACE PROCEDURE SP_GRADE(P_GRADE NUMBER)
IS
CURSOR C_GRADE(P_NO NUMBER)
IS
SELECT E.EMPNO,E.ENAME
FROM EMP E JOIN SALGRADE S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL
WHERE S.GRADE = P_NO;
BEGIN
FOR X IN C_GRADE(P_GRADE) LOOP
DBMS_OUTPUT.PUT_LINE('工号:'||X.EMPNO||' 姓名:'||X.ENAME||' 工资等级:'||P_GRADE);
END LOOP;
END;
--测试一下
BEGIN
SP_GRADE(1);
END;
--还可以通过变量将值传入(传一个变量)
DECLARE
V_GRADE NUMBER(1) := &输入一个工资等级;
BEGIN
SP_GRADE(V_GRADE);
END;
--删除存储过程
DROP PROCEDURE 过程名;
--比如 :
DROP PROCEDURE SP_DEPT;