PL/SQL examples

PL/SQL examples


1) A PL/SQL  block to check whether value entered by user is present in a list

set serveroutput on;

declare

x char(10);

y number(5);

z date;

BEGIN

dbms_output.put_line('ENTER A VALUE');

x := '&x';

dbms_output.put_line('ENTER another value');

y := &y;

IF x in ('2','4','hi')  then

dbms_output.put_line('FOUND');

END IF;

END;

/





2)PL/SL block  that formats numbers(floating point).

set serveroutput on;

declare

n1 number(6,2);

n2 number(6,2);

n3 number(6);

n4 number(4,-1);  --rounded of to 10

n5 number(4,-2);  -- rounded of to 100

n6 number(1,4);  -- rounded of to 1/10000

BEGIN

n1 := 2014.5;

n2 := 233.465;

n3 := 234.6;

n4 := 1657;

n5 := 1457;

n6 := 1/3845;

dbms_output.put_line('N1 = '||n1);

dbms_output.put_line('N2 = '||n2);

dbms_output.put_line('N3 = '||n3);

dbms_output.put_line('N4 = '||n4);

dbms_output.put_line('N5 = '||n5);

dbms_output.put_line('N6 = '||n6);

END;

N1 = 2014.5

N2 = 233.47

N3 = 235

N4 = 1660

N5 = 1500

N6 = .0003

PL/SQL procedure successfully completed.



3) A PL/SQL program that compares a CHAR and a VARCHAR2 variable with leading spaces.

declare

d1 varchar2(12); 

d2 char(12); -- ADDS SPACE CHARACTERs AT THE END

BEGIN

d1 := 'definitions';

d2 := 'definitions';

IF d1 = d2 then

dbms_output.put_line(' equal');

else

dbms_output.put_line(' not equal');

END IF;

END;

/

4) PL/SQl block to concatenate two variables values.
set serveroutput on;

DECLARE

x number(5);
y char(5);
z char(15);

BEGIN
x :=&x;
y :='&y';

z := x|| ' and '||y;
DBMS_OUTPUT.PUT_LINE(z);
END;
/

5)PL SQL Raise Application error example

SET SERVEROUTPUT ON;
DROP TABLE BILL_PAID;
CREATE TABLE BILL_PAID (
CUST_ID VARCHAR2(6),
BILL_DATE VARCHAR2(10),
BILL_AMOUNT NUMBER(10,4)
);


DECLARE

CUT_OF_DATE VARCHAR2(10) := '2014-01-05';
cid BILL_PAID.CUST_ID%TYPE;
DUE_DATE_EXCEEDED EXCEPTION;
PRAGMA EXCEPTION_INIT(DUE_DATE_EXCEEDED, -20006);

PROCEDURE pay_bill ( cust VARCHAR2 , dt  VARCHAR2, amount NUMBER) IS
 BEGIN
 
    COMMIT;
    IF  TO_DATE(dt,'YYYY-MM-DD')  > TO_DATE( CUT_OF_DATE,'YYYY-MM-DD' ) THEN

     RAISE_APPLICATION_ERROR (-20006,'BILL PAID AFTER DUE DATE');
  
   END IF;
   INSERT INTO BILL_PAID VALUES (cust,dt,amount);
END  pay_bill;

BEGIN
cid := 'J54677';
pay_bill(cid,'2013-12-30',1450.50);

cid := 'J54678';
pay_bill(cid,'2014-01-12',1320.00);  --will cause exception.

cid := 'J54642';
pay_bill(cid,'2014-01-05',750.50);

EXCEPTION
WHEN DUE_DATE_EXCEEDED THEN
DBMS_OUTPUT.PUT_LINE ('PENALTY CHARGED FOR CUSTOMER ' || cid);
DBMS_OUTPUT.PUT_LINE ('ERRNO = '|| SQLCODE || ' ERROR MSG IS ' || SQLERRM);
END;
/