时间:2021-07-01 10:21:17 帮助过:2人阅读
然后是packages bodies里面的内容:
这里面主要是对上面定义的function和procedure的实现定义,
有简单的返回, 还有游标类型的返回;
CREATE OR REPLACE PACKAGE BODY DEVICES_PKG AS FUNCTION fun_add_device(dev_id NUMBER, dev_name VARCHAR2, dev_age NUMBER) RETURN NUMBER IS BEGIN INSERT INTO devices VALUES (dev_id, dev_name, dev_age); IF SQL%FOUND THEN RETURN 1; ELSE RETURN 0; END IF; END fun_add_device; FUNCTION fun_delete_device(dev_id NUMBER) RETURN NUMBER IS BEGIN DELETE FROM devices WHERE id = dev_id; IF SQL%FOUND THEN RETURN 1; ELSE RETURN 0; END IF; END fun_delete_device; FUNCTION fun_Get_Test_Main_All RETURN MY_RESULTSET_CURSOR IS return_cursor MY_RESULTSET_CURSOR; BEGIN OPEN return_cursor FOR ‘SELECT d.id,d.name,d.age FROM devices d ORDER BY d.id ASC‘; RETURN return_cursor; END; PROCEDURE pro_select_device(dev_id NUMBER, RS out MY_RESULTSET_CURSOR) IS testCursor MY_RESULTSET_CURSOR; testRec devices%ROWTYPE; v_sql_select VARCHAR2(500); BEGIN v_sql_select := ‘select d.name, d.age, g.content from devices d, groups g where d.id =‘|| dev_id || ‘ and d.id = g.devicesid‘; OPEN RS FOR v_sql_select; testCursor := fun_Get_Test_Main_All(); LOOP FETCH testCursor INTO testRec; EXIT WHEN testCursor%NOTFOUND; DBMS_OUTPUT.put_line(‘id:‘||testRec.Id||‘,name:‘||testRec.Name||‘,age:‘||testRec.Age); END LOOP; END; END DEVICES_PKG;
这里给出其中一个调用的过程:
测试devices_pkg.pro_select_device这个存储过程,
输入dev_id为3,
结果rs为:
然后看一下DBMS输出是什么:
Oracle简单学习
标签:com values from col exit span pack tle 测试