测试程序包函数示例
DECLARE
V_ID VARCHAR2(200);
V_Return SYS_REFCURSOR;
BEGIN
V_ID :='123';
V_Return := TEST_PACKAGE.FUN_GET_INFO(V_ID => V_IN_ID);
DBMS_SQL.return_result(V_Return);
END;
DBMS_SQL.return_result
可以把结果(SYS_REFCURSOR)像普通查询一样以表格形式展示;
V_IN_ID
是函数入参名;
非显式返回
函数包含一个 OUT 参数,而非直接Return
DECLARE
V_ID VARCHAR2(200);
V_Return NUMBER;
BEGIN
test_package.pro_create_action_log('DI0001','test-user',null,null,null,'的房贷发放',V_Return);
DBMS_OUTPUT.put_line(V_Return);
END;
返回值是Clob
DECLARE
V_OUT CLOB;
BEGIN
V_OUT := DMG_FULFILLMENT_PACKAGE.fun_get_action_log_helper('DI00000000000010085',null);
DBMS_OUTPUT.PUT_LINE(V_OUT);
END;
返回值是Refcursor
DECLARE
v_delivery_key_info SYS_REFCURSOR;
v_DELIVERY_ID VARCHAR2(20 BYTE);
v_ENCRYPTION_KEY VARCHAR2(512 BYTE);
v_IV VARCHAR2(512 BYTE);
v_CREATE_DATE DATE;
v_LAST_UPDATE DATE;
BEGIN
v_delivery_key_info := DMG_CORE_PACKAGE.fun_get_delivery_key_by_delivery_ids('2');
LOOP
FETCH v_delivery_key_info INTO v_DELIVERY_ID,v_ENCRYPTION_KEY,v_IV,v_CREATE_DATE,v_LAST_UPDATE;
EXIT WHEN v_delivery_key_info%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_DELIVERY_ID);
END LOOP;
CLOSE v_delivery_key_info;
END;
v_delivery_key_info 每条数据都包含
v_DELIVERY_ID VARCHAR2(20 BYTE);
v_ENCRYPTION_KEY VARCHAR2(512 BYTE);
v_IV VARCHAR2(512 BYTE);
v_CREATE_DATE DATE;
v_LAST_UPDATE DATE;
所以要创建同样数量类型的变量来接收