存储过程基础

-- 存储过程基础语法
1.1 基本结构
CREATE OR REPLACE PROCEDURE 存储过程名字
(
参数1 IN NUMBER,
参数2 IN NUMBER
) AS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
END;
1.2 SELECT INTO STATEMENT
将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
例子：
BEGIN
SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;
...

1.3 IF 判断
IF V_TEST=1 THEN
BEGIN
do something
END;
END IF;
练习：
CREATE OR REPLACE PROCEDURE USP_LU_FAC_PRC_REPYMT_BILL_D
(
ID_IN IN TBL_LU_FAC.ID%TYPE,
RESULT_CURSOR OUT TYPES.cursor_type
)
AS
v_prc_id Tbl_Lu_Fac_Prc.Id%TYPE;
v_repymt_id Tbl_Lu_Fac_Repymt.Id%TYPE;
BEGIN

SELECT
Tbl_Lu_Fac_Prc.ID,
Tbl_Lu_Fac_Repymt.ID
INTO v_prc_id,v_repymt_id
FROM TBL_LU_FAC
LEFT JOIN Tbl_Lu_Fac_Prc ON Tbl_Lu_Fac_Prc.Lu_Fac_Id = TBL_LU_FAC.Id
LEFT JOIN Tbl_Lu_Fac_Repymt ON Tbl_Lu_Fac_Repymt.Lu_Fac_Id = TBL_LU_FAC.Id
WHERE TBL_LU_FAC.ID = ID_IN
FOR UPDATE NOWAIT;

if(v_prc_id is not null) THEN
BEGIN
DELETE FROM Tbl_Lu_Fac_Prc WHERE Tbl_Lu_Fac_Prc.ID = v_prc_id;
END;
END IF;

if(v_repymt_id is not null) THEN
BEGIN
DELETE FROM Tbl_Lu_Fac_Repymt WHERE Tbl_Lu_Fac_Repymt.ID = v_repymt_id;
END;
END IF;

DELETE FROM TBL_LU_FAC WHERE TBL_LU_FAC.ID = ID_IN;

OPEN RESULT_CURSOR FOR
SELECT 'success' AS STATUS FROM DUAL;

EXCEPTION
WHEN NO_DATA_FOUND THEN
ERR.RAISE_ERR(ERR.DELETED_BY_OTHER_USER);

WHEN ERR.RECORD_IS_LOCKED THEN
ERR.RAISE_ERR(ERR.UPDATED_BY_OTHER_USER);

WHEN OTHERS THEN
RAISE;
END;
————————————————————————————————
1.4 while 循环
WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;

1.5.变量赋值
V_TEST := 123;

1.6.用for in 使用cursor
...
IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.列名1+cur_result.列名2
END;
END LOOP;
END;
注：
有三种循环
１、loop
exit when 条件;
.............
end loop;
2、 while 条件 loop
..............
end loop;
3、for i in 1..100 loop
..........
end loop;
还有一种是用隐式游标来实现的：
for idx in (select a, b from tableA) loop
................
end loop;

1.7、数组
首先明确一个概念：Oracle中本是没有数组的概念的，数组其实就是一张表(Table),每个数组元素就是表中的一个记录。
使用数组时，用户可以使用Oracle已经定义好的数组类型，或可根据自己的需要定义数组类型。
(1)使用Oracle自带的数组类型
x array; --使用时需要需要进行初始化
e.g:
create or replace procedure test(y out array) is
x array;
begin
x := new array();
y := x;
end test;
(2)自定义的数组类型 (自定义数据类型时，建议通过创建Package的方式实现，以便于管理)
E.g (自定义使用参见标题4.2)
create or replace package myPackage is
-- Public type declarations type info is record( name varchar(20), y number);
type TestArray is table of info index by binary_integer; --此处声明了一个TestArray的类型数据，其实其为一张存储Info数据类型的Table而已，及TestArray 就是一张表，有两个字段，一个是
name，一个是y。需要注意的是此处使用了Index by binary_integer 编制该Table的索引项，也可以不写，直接写成：type TestArray is
table of info，如果不写的话使用数组时就需要进行初始化：varArray myPackage.TestArray; varArray := new myPackage.TestArray();
end TestArray;
1.8.带参数的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(变量值);
LOOP
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
do something
END LOOP;
CLOSE C_USER;

1.9.用pl/sql developer debug
连接数据库后建立一个Test WINDOW
在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试