典型pl/sql块

深碍√TFBOYSˉ_ 2022-08-06 12:25 68阅读 0赞

典型pl/sql结构

\-----------------------------------------------------

DECLARE  
   v\_rate NUMBER;  
   v\_threshold NUMBER := 100.00;  
   v\_increase\_flag BOOLEAN := FALSE;

BEGIN

SELECT rate  
   INTO   v\_rate  
   FROM   rate\_tab  
   WHERE  rate\_type = 'Monthly'  
   AND  effective\_date = TRUNC(SYSDATE);

IF (v\_rate < v\_threshold) THEN  
   v\_increase\_flag := TRUE;  
END IF;

IF v\_increase\_flag THEN  
UPDATE rate\_tab  
   SET rate := rate + 2.5\*rate  
   WHERE  rate\_type = 'Monthly'  
   AND  effective\_date = TRUNC(SYSDATE);  
COMMIT;  
END IF;

EXCEPTION WHEN NO\_DATA\_FOUND THEN  
   dbms\_output.put\_line('Rate not available, contact FS');  
WHEN OTHERS THEN  
   dbms\_output.put\_line('Server Error as follows, contact MIS ');  
   dbms\_output.put\_line(SQLERRM);

END;

存储过程

\------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE p\_process\_rate(  
ip\_rate\_type IN rate\_tab.rate\_type%T  
ip\_effective\_date IN rate\_tab.rate\_t  
ip\_threshold IN NUMBER,  
op\_rate OUT NUMBER,  
op\_success OUT VARCHAR2,  
op\_errmsg OUT VARCHAR2)

IS  
v\_rate NUMBER;  
v\_increase\_flag BOOLEAN := FALSE;

BEGIN

SELECT rate  
INTO   v\_rate  
FROM   rate\_tab  
WHERE  rate\_type = ip\_rate\_type  
AND  effective\_date = ip\_effective\_date;  
IF (v\_rate < ip\_threshold) THEN  
v\_increase\_flag := TRUE;  
END IF;  
IF v\_increase\_flag THEN  
UPDATE rate\_tab  
SET rate := rate + 2.5\*rate  
WHERE  rate\_type = ip\_rate\_type  
AND  effective\_date = ip\_effective\_date  
RETURNING rate INTO v\_rate;  
COMMIT;  
END IF;  
op\_rate := v\_rate;  
op\_success := 'Y';  
EXCEPTION WHEN NO\_DATA\_FOUND THEN  
op\_rate := NULL;  
op\_success := 'N';  
op\_errmsg := 'Rate not available, contact FS';  
WHEN OTHERS THEN  
op\_rate := NULL;  
op\_success := 'N';

op\_errmsg := 'Server Error as follows, contact MIS '  
||CHR(10)||CHR(13)||  
SQLERRM;  
END;

pl/sql中存储过程调用

\--------------------------------------------------------------------------------------------

DECLARE

v\_rate NUMBER;  
v\_success\_flag VARCHAR2(1);  
v\_errmsg VARCHAR2(1000);

BEGIN

p\_process\_rate('Monthly',TRUNC(SYSDATE), 100.00,  
v\_rate, v\_success\_flag, v\_errmsg);

IF (v\_success\_flag != 'Y') THEN  
   dbms\_output.put\_line('Procedure p\_process\_rate failed  
with error(s) as follows');  
   dbms\_output.put\_line(v\_errmsg);  
END IF;

END;