今天写的一个存储过程

pl/sql没有白学，效果还不错。今天写了个pl/sql的业务流程部分。

create or replace
PROCEDURE Bind(
  mac IN COMPUTER.COMP_MAC%TYPE, --'abc'
  outer_ip IN COMPUTER.COMP_OUTERIP%TYPE, --222.30.55.11
  inner_ip IN COMPUTER.COMP_INTERIP%TYPE, --'192.168.1.1'
  bar IN COMPUTER.BAR_ID%TYPE, --1
  url_ie OUT adresource.ADRES_URL%TYPE,
  url_desktop OUT adresource.ADRES_URL%TYPE
  )
AS

var NUMBER;

--Initial a cursor
CURSOR cur_url(bar_id IN AD2BAR.BAR_ID%TYPE)
  IS
  SELECT ADRES_URL
  FROM ADRESOURCE 
    LEFT JOIN ADPLAN 
    ON ADRESOURCE.ADRES_ID = ADPLAN.ADRES_ID
    LEFT JOIN AD2BAR
    ON ADPLAN.PLAN_ID = AD2BAR.PLAN_ID
  WHERE AD2BAR.BAR_ID = bar_id
    AND ADPLAN.PLAN_START < SYSDATE
    AND ADPLAN.PLAN_END > SYSDATE
  ORDER BY ADRESOURCE.ADRES_TYPE;

TYPE UrlTab IS TABLE OF adresource.ADRES_URL%TYPE;

urls URLTAB;

BEGIN

SELECT COUNT(*) INTO var
FROM computer
WHERE computer.BAR_ID=bar
  AND computer.COMP_MAC=mac
  AND computer.COMP_INTERIP=inner_ip
  AND computer.COMP_OUTERIP=outer_ip;

IF var = 0 THEN
--if the computer which contains the mac does not exist,
--delete the record which has the same mac address and 
--re-insert it with the new information

  --delete the record
  DELETE FROM COMPUTER
  WHERE COMPUTER.COMP_MAC = mac;
  --insert the record
  insert into computer( bar_id, comp_mac, comp_interip, comp_outerip, comp_time, comp_updatetime)
  values(bar,mac,inner_ip,outer_ip,systimestamp, systimestamp);
  --

  --DBMS_OUTPUT.PUT_LINE('0');--trace tool
ELSE-- This client already exists in the db.

  --update the record to notify the computer's last power on
  UPDATE COMPUTER
  SET COMP_UPDATETIME=SYSTIMESTAMP
  WHERE MAC = mac;

  --DBMS_OUTPUT.PUT_LINE('NOT 0');--trace tool
END IF;

  OPEN cur_url(bar);
  FETCH cur_url BULK COLLECT INTO urls;
  CLOSE cur_url;

  --DBMS_OUTPUT.PUT_LINE(urls(1));--trace tool
  --DBMS_OUTPUT.PUT_LINE(urls(2));--trace tool
  url_ie := urls(1);
  url_desktop := urls(2);

END;

下面是测试驱动程序。

declare
  ie varchar2(100);
  desktop varchar2(100);
begin
  bind('chang','222.30.55.10','192.168.1.2',1, ie, desktop);
  dbms_output.put_line(desktop);
  dbms_output.put_line(ie);
end;
/