存储过程练手（入门）

已于 2023-09-11 15:29:00 修改
阅读量204 收藏 1
点赞数
文章标签： 数据库 sql oracle
于 2023-09-06 17:05:16 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/m0_61871870/article/details/132719235
版权

存储过程练手（入门）


以下存储过程基于oracle，主要包含了存储过程中的一些常用基本语句的语法。

1、无参存储过程

CREATE OR REPLACE PROCEDURE PRO_1
AS
BEGIN		-- 执行
DBMS.OUTPUT.PUT_LINE('无参构造过程');
EXCEOPTION		-- 异常
	WHEN OTHERS THEN
	ROLLBACK;
END;

-- 调用(方式一)
BEGIN
	PRO_1();
END;

-- 调用(方式二)
BEGIN
	CALL PRO_1();
END;

DROP PROCEDURE PRO_1;

2、带参存储过程

CREATE OR REPLACE PROCEDURE PRO_2(	-- 定义参数，使用该存储过程时需要传参
	VAL IN NUMBER,
	SNAME OUT VARCHAR,
	DT_CODE IN OUT VARCHAR)
AS ICOUNT NUMBER;		--声明该存储过程中需要用到的变量。

BEGIN
	SELECT COUNT(*) INTO ICOUNT FROM D_DEPT WHERE DT_ID>VAL;
	IF ICOUNT=1 THEN
		SNAME:='MLB事业部1'||VAL;
		DT_CODE:='MLB1'||DT_CODE;
	ELSE
		SNAME:='MLB事业部2'||VAL;
		DT_NAME='MLB2'||DT_CODE;
	END IF;
	
EXCEPTION
	WHEN TOO_MANY_ROWS THEN
	DBMS_OUTPUT.PUT_LINE('返回值多于一行');
	WHEN　OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('在PRO＿PAR过程中出错！')
END;

--调用
declare
	REVAL NUMBER;
	RENAME varchar(40);
	RECODE varchar(40);
BEGIN
	REVAL:='20230822';
	RENAME:='';
	RECODE:='JUNIOR';
	-- 指定值对应变量顺序可变(关系箭头方向是形参指向实参)
	PRO_2(SNAME=>RENAME,VAL=>REVAL,DT_CODE=>RECODE);
	DBMS_OUTPUT_PUTLINE(RENAME||'  '||RECODE);
END;

3、带if,else,elseif存储过程

CREATE OR REPLACE PROCEDURE PRO_3(Y IN NUMBER)
AS
BEGIN
	IF Y=2020 THEN
		DBMS_OUTPUT.PUT_LINE('YEAR OF 2020');
	ELSIF Y=2021 THEN
		DBMS_OUTPUT.PUT_LINE('YEAR OF 2021');
	ELSE
		DBMS_OUTPUT.PUTLINE('UNKNOWN YEAR');
	END IF;
END;

-- 调用
BEGIN
	PRO_3(Y=>2021);
END;

4、带while循环的存储过程

CREATE OR REPLACE PRO_4(I IN NUMBER)
AS
	J NUMBER;
BEGIN
	J:=1;
	WHILE J<=I LOOP
		DBMS_OUTPUT.PUTLINE('J='||J);
		J:=j+1;
	END LOOP;
END;

BEGIN
PRO_4(I=>100);
END;

5、带select　into的存储过程

存储过程中在不使用cursor的前提下，使用SELECT语句时必须带着INTO（SELECT … INTO … FROM … ）

CREATE OR REPLACE PROCEDURE PRO_5(ID IN NUMBER)
AS
M VARCHAR(50);
G VARCHAR(50);
BEGIN

-- 在利用SELECT…INTO…语法时，必须先确保数据库中有该条记录，否则会报出"NO_DATA_FOUND"异常。
-- 可先利用SELECT COUNT(*) FROM 查看数据库中是否存在该记录，存在则使用SELECT…INTO。
-- 在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错。

	SELECT MONTH,ORG_OID INTO M,G FROM D_DEPT WHERE DT_ID=ID;
	DBMS_OUTPUT.PUTLINE('M='||M||'G'||G);
	EXCEPTION
	WHEN NO_DATA_FOUND THEN
		DBMS_OUTPUT.PUTLINE('数据不存在');
END;

-- 调用
BEGIN
PRO_5(ID=>21);
END;

6、带for存储过程

CREATE OR REPLACE PROCEDURE PRO_6
AS
BEGIN
	FOR D IN (SELECT * FROM D_LESSON) LOOP
		IF(D.LN_ID>10) THEN
			DBMS_OUTPUT.PUTLINE(D.LN_ID);
		END IF;
	END LOOP;
COMMIT;
END;

-- 调用
BEGIN
	CALL PRO_6();
END;

7、带有execute immediate（动态SQL）

CREATE OR REPLACE PROCEDURE PRO_7(V_EMPNO IN NUMBER)
AS
	V_ENAME VARCHAR(20);
	V_SAL NUMBER(6);
	v_STR VARCHAR(100);
BEGIN
	V_STR:='SELECT ENAME,SAL FROM EMP WHERE EMPNO=:1';	-- 占位符:1相当于函数的形式参数，使用：作为前缀，然后使用using将V_EMPNO在运行时替换:1
	EXECUTE IMMEDIATE V_STR -- 执行动态sql语句
	INTO V_ENAME,V_SAL	-- 将查询出的结果传给变量（输出参数列表）
	USING V_EMPNO;	-- 往SQL语句中传入的参数（绑定参数列表）
EXCEPTION
	WHEN NO_DATA_FOUND THEN
		DBMS_OUTPUT.PUTLINE('没有此员工');
END;

8、带有游标的存储过程

游标四大属性

属性返回类型作用
sql%isopen布尔型判断游标是否开启
sql%found布尔型判断游标是否获取到值
sql%notfound布尔型判断游标是否没有获取到值（常用于退出循环）
sql%rowcount整型当前成功执行的数据行数（是已执行的行数，而不是总行数）

显示游标

游标分显式和隐式的，在使用DML操作和单行SELECT操作时会自动使用隐式游标，在此不做说明，细说显示游标。

声明游标

声明游标有两种方式：

方式一：在声明变量的位置声明游标

CREATE OR REPLACE PROCEDURE PRO_8
AS
	-- 1、声明游标
	CURSOR cursor_stu_info			-- 游标名
	IS select * from stu_info;		-- 游标要执行的sql语句
	v_stu_info stu_info%ROWTYPE;	-- 游标存储结构名 数据类型（表名%ROWTYPE  -》表示该存储结构和数据表表相同）
 
BEGIN
	-- 2、开启游标
	OPEN cursor_stu_info;
	-- 3、提取（每次获取一行）
	FETCH cursor_stu_info	-- 取出游标中的一行数据
	INTO v_stu_info;		-- 将这行数据放入游标的存储结构中
	-- 输出结果
	dbms_output.putline(v_stu_info.id||':'||v_stu_info.name);
	-- 4、关闭游标
	close cursor_stu_info;
END PRO_8；

方式二：在BEGIN和END语句块内声明游标

CREATE OR REPLACE PROCEDURE PRO_8
AS
BEGIN
	DECLARE
		-- 1、声明游标
		CURSOR cursor_stu_info
		IS select * from stu_info;
		v_stu_info stu_info%ROWTYPE;
	BEGIN --在BEGIN和END语句块汇内声明游标时必须再写一个BEGIN和END语句块。
	-- 2、开启游标
	OPEN cursor_stu_info;
	-- 3、提取数据（每次获取一行）
	FETCH cursor_stu_info
	INTO v_stu_info;
	-- 输出结果
	dbms_output.putline(v_stu_info.id||':'||v_stu_info.name);
	-- 4、关闭游标
	CLOSE cursor_stu_info;
	END;
END PRO_8；
遍历游标
declare
-- 1、声明游标
 CURSOR cursor_stu_info
 is
 	SELECT * from stu_info;
 v_stu_info stu_info%ROWTYPE ;
 
BEGIN
	-- 2、开启游标
	OPEN cursor_stu_info;
	-- 3、提取游标（每次获取一行）
	FETCH cursor_stu_info
	INTO v_stu_info;
	-- 输出结果
	dbms_output.putline(v_stu_info.id||':'||v_stu_info.name);
	-- 4、关闭游标
	CLOSE cursor_stu_info;
END；
while循环遍历

上述例子只能显示出一条结果，如果要得到所有结果，则需要循环。

声明接收数据的结果集类型的方法有两种

方式一：

-- 检索EMP表中的所有JOB为MANAGER的员工信息
DECLARE 
	-- 声明游标
	CURSOR CUR_EMP(VAR_JOB IN VARCHAR(20))
	-- 此游标所使用的查询语句
	IS SELECT EMPNO,ENAME,SAL FROM EMP WHERE JOB = VAR_JOB;
	-- 声明一个RECORD类型的记录变量，用于接收查询出的数据集
	TYPE RECORD_EMP IS RECORD(
    	VAR_EMPNO EMP.EMPNO%TYPE,
        VAR_ENAME EMP.ENAME%TYPE,
        VAR_SAL EMP.SAL%TYPE);
    EMP_ROW RECORD_EMP;		-- record类型的变量名为EMP_ROW
    
BEGIN
	-- 开启游标
	OPEN CUR_EMP('MANAGER');
	
	-- 首先需要将游标指向查询结果集中的第一个数据
	FETCH CUR_EMP
	INTO EMP_ROW;
	
	-- 如果结果集中有多条数据，则需要循环
	-- 先判断目前游标是否获取到值
	WHILE CUR_EMP%FOUND LOOP
		DBMS_OUTPUT.PUTLINE('编号:'||EMP_ROW.VAR_EMPNO||',姓名'||CUR_ROW.VAR_ENAME||',薪水'||EMP_ROW.VAR_SAL);
	-- 输出完一条数据以后立即让游标指向下一条数据
	FETCH CUR_EMP
	INTO EMP_ROW;
	END LOOP;
	-- 关闭游标
	CLOSE CUR_EMP;
END;

方式二：

-- 检索EMP表中的所有JOB为MANAGER的员工信息
DECLARE
	CURSOR CUR_EMP(VAR_JOB IN VARCHAR(20))IS
	-- 使用FETCH+%ROWTYPE查询，这里的查询字段必须和表中字段顺序及数量一致
	SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM EMP;
	VAR_EMP_TYPE EMP%ROWTYPE;

BEGIN
	OPEN CUR_EMP;
	FETCH CUR_EMP
	INTO VAR_EMP_TYPE;
	WHILE CUR_EMP%FOUND LOOP
	DBMS_OUTPUT.PUT_LINE('编号:'||VAR_EMP_TYPE.EMPNO||',姓名'||VAR_EMP_TYPE.ENAME||',薪水'||VAR_EMP_TYPE.SAL);
	FETCH CUR_EMP
	INTO VAR_EMP_TYPE;
	END LOOP;
	CLOSE CUR_EMP;
END;
for循环遍历游标

上述是使用while循环实现结果集遍历的，不过我们也可以使用for循环遍历，并且有以下优点：

  • 自动打开、关闭游标
  • 当执行完最后一条数据时，自动跳出循环，无需写exit
-- 检索EMP表中的所有JOB为MANAGER的员工信息
DECLARE
	CURSOR CUR_EMP(VAR_JOB IN VARCHAR(20)) IS
	-- 使用FETCH+%ROWTYPE查询，这里的查询字段必须和表中字段顺序及数量一致
	SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM EMP;
	VAR_EMP_TYPE EMP%ROWTYPE;
	
BEGIN
	-- 每次从CUR_EMP中取出一个结果VAR_EMP_TYPE
	FOR VAR_EMP_TYPE IN CUR_EMP LOOP
	DBMS_OUTPUT.PUT_LINE('编号:'||VAR_EMP_TYPE.EMPNO||',姓名'||VAR_EMP_TYPE.ENAME||',薪水'||VAR_EMP_TYPE.SAL);
	END LOOP;
END;
fetch循环遍历
-- 检索EMP表中的所有JOB为MANAGER的员工信息
DECLARE
	CURSOR CUR_EMP(VAR_JOB IN VARCHAR(20)) IS
	-- 使用FETCH+%ROWTYPE查询，这里的查询字段必须和表中字段顺序及数量一致
	SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM EMP;
	VAR_EMP_TYPE EMP%ROWTYPE;
	
BEGIN
	OPEN CUR_EMP;
	LOOP
		FETCH CUR_EMP INTO CUR;
		EXIT WHEN CUR_EMP%NOTFOUND;
		DBMS_OUTPUT.PUT_LINE('编号:'||VAR_EMP_TYPE.EMPNO||',姓名'||VAR_EMP_TYPE.ENAME||',薪水'||VAR_EMP_TYPE.SAL);
	END LOOP;
	CLOSE CUR_EMP;
END;

9、其他注意事项

  1. 在利用select…into…语法时，必须先确保数据库中有该条记录，否则会报出”no data found”异常。

    可以在该语法之前，先利用select count(*) from 查看数据库中是否存在该记录，如果存在，再利用select…into…

  2. 在oracle中，数据表别名不能加as

  3. 如果在存储过程中，使用如下语句：

select sum(vcount) into fcount from A where bid= 'xxxxxx';

如果A表中不存在bid=”xxxxxx”的记录，则fcount=null(即使fcount定义时设置了默认值，如：fcount number(8):=0依然无效，fcount还是会变成null)，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：

if fcount is null then
   fcount:=0;
end if;
wenyihuila
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值