Oracle简单学习

然后是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   测试