关于参数为BLOB的存储过程如下:
--添加客户信息
procedure addCustomer(--客户姓名
v_customer_name IN CUSTOMER.NAME%TYPE,
...
v_photo IN OUT CUSTOMER.PHOTO%TYPE,
--客户照片
...
v_id OUT CUSTOMER.ID%TYPE
--客户ID
)
IS
v_char_id customer.ID%TYPE;
v_blob_photo customer.photo%TYPE;
...
BEGIN
--生成ID
SELECT SYS_GUID() INTO v_char_id FROM DUAL;
INSERT INTO CUSTOMER
(ID,
CUSTOMER_NAME,
...
PHOTO,
...)
VALUES
(v_char_id,
v_customer_name,
...
EMPTY_BLOB(), //先插入空BLOB
...
);
IF (v_photo IS NOT NULL) THEN
SELECT PHOTO
INTO v_blob_photo
FROM CUSTOMER
WHERE CUSTOMER.ID = v_char_id
FOR UPDATE;
DBMS_LOB.OPEN(v_photo, DBMS_LOB.lob_readonly); --打开DBMS_LOB()包
DBMS_LOB.OPEN(v_blob_photo, DBMS_LOB.lob_readwrite);