Quick PL/SQL Variables, Cursor ... Loops

1. RUNTIME STRING/NUM PRINTING

SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE('ORANGE');
END;
/

2. TIMESTAMP EXAMPLE

SET SERVEROUTPUT ON
DECLARE
V_DATE TIMESTAMP;

BEGIN
SELECT SYSTIMESTAMP-1/24 INTO V_DATE FROM DUAL;
DBMS_OUTPUT.PUT_LINE('ONE HR. AGO :: ' || V_DATE);
END;
/

3. TIMESTAMP EXAMPLE WITH EXCEPTION HANDELING

SET SERVEROUTPUT ON
DECLARE
VDATE TIMESTAMP;

BEGIN
SELECT SYSTIMESTAMP INTO VDATE FROM DUAL;
DBMS_OUTPUT.PUT_LINE(VDATE);

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('ERROR');
END;
/

4. LOOP & SERIES PRINTING

SET SERVEROUTPUT ON
DECLARE
X NUMBER:=1;
BEGIN

LOOP
EXIT WHEN X>10;
DBMS_OUTPUT.PUT_LINE(X);
X:=X+1;
END LOOP;
END;
/


5. TABLE JOINING & CURSORING

CREATE TABLE AUTHORS (
ID NUMBER PRIMARY KEY,
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50)
);

CREATE TABLE BOOKS (
ISBN CHAR(10) PRIMARY KEY,
CATEGORY VARCHAR2(20),
TITLE VARCHAR2(100),
NUM_PAGE NUMBER,
PRICE NUMBER,
COPYRIGHT NUMBER(4),
AUTHOR1 NUMBER CONSTRAINT BOOKS_AUTHOR1 REFERENCES AUTHORS(ID)
);


INSERT INTO AUTHORS
(ID,FIRST_NAME,LAST_NAME)
VALUES(2,'DEVA','DUTTA');

INSERT INTO BOOKS
(ISBN,CATEGORY,TITLE,NUM_PAGE,PRICE,COPYRIGHT,AUTHOR1)
VALUES('CDW5815564','MATHEMATICS','VECTORS',350,180,1300,2);

SELECT B.TITLE,A.FIRST_NAME || A.LAST_NAME AS AUTHOR,B.PRICE FROM AUTHORS A, BOOKS B WHERE B.AUTHOR1=A.ID;

1. INSERTING RECORDS BY LOOP

SET SERVEROUTPUT ON
DECLARE
X NUMBER:=3;
BEGIN

LOOP
EXIT WHEN X>10;

INSERT INTO AUTHORS(ID) VALUES(X);
X:=X+1;
END LOOP;
END;
/

SELECT * FROM AUTHORS;

2. CURSOR EXMP>> {EXAMPLE OF A CURSOR}

SET SERVEROUTPUT ON
DECLARE
V_TITLE BOOKS.TITLE%TYPE;
V_FIRST_NAME AUTHORS.FIRST_NAME%TYPE;

CURSOR BOOK_CUR IS
SELECT B.TITLE,A.FIRST_NAME FROM AUTHORS A, BOOKS B WHERE A.ID=B.AUTHOR1;

BEGIN
DBMS_OUTPUT.ENABLE(1000000);
OPEN BOOK_CUR;

LOOP
FETCH BOOK_CUR INTO V_TITLE,V_FIRST_NAME;
EXIT WHEN BOOK_CUR%NOTFOUND;

IF V_FIRST_NAME='ADDITYA'
THEN
DBMS_OUTPUT.PUT_LINE('MR. ADDITYA ROY COLLECTION' || V_TITLE);
ELSE
DBMS_OUTPUT.PUT_LINE('GLOBAL COLLECTION' || V_TITLE);
END IF;
END LOOP;

CLOSE BOOK_CUR;

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('SORRY ONE INTERNAL ERROR!');
END;
/


6. CUSTOMIZED UPDATION ON PL/SQL


SET SERVEROUTPUT ON;
DECLARE
  V_PRICE BOOKS.PRICE%TYPE;
  V_ISBN BOOKS.ISBN%TYPE:='CDW5815564';
BEGIN
  SELECT PRICE INTO V_PRICE FROM BOOKS WHERE ISBN= V_ISBN;
  DBMS_OUTPUT.PUT_LINE('THE ORIGINAL PRICE FOR THE ISBN (CDW5815564) WAS ' || V_PRICE);

  V_PRICE:=ROUND(V_PRICE+(V_PRICE*9/100),0);

  UPDATE BOOKS SET PRICE=V_PRICE WHERE ISBN= V_ISBN;

  SELECT PRICE INTO V_PRICE FROM BOOKS WHERE ISBN= V_ISBN;
  DBMS_OUTPUT.PUT_LINE('THE LATEST PRICE FOR THE ISBN (CDW5815564) IS ' || V_PRICE);

  EXCEPTION
    WHEN OTHERS 
    THEN
      DBMS_OUTPUT.PUT_LINE('SORRY! FETCHED DATA IS MISSING ON DB!');
END;


7. FOR LOOP

SET SERVEROUTPUT ON;
DECLARE
  X NUMBER:=1;
  BEGIN
    FOR X IN 1 .. 20
    LOOP
      DBMS_OUTPUT.PUT_LINE(X);
    END LOOP;
  END;

8. WHILE LOOP

SET SERVEROUTPUT ON;
DECLARE
  X NUMBER:=1;
  BEGIN
    WHILE X<=20
    LOOP
      DBMS_OUTPUT.PUT_LINE(X);
      X:=X+1;
    END LOOP;
  END;


9. PASSING PARA MITERS in PROCEDURE 


CREATE OR REPLACE PROCEDURE XSUMM(X NUMBER,Y NUMBER)
AS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('SUMMATION IS :: '||TO_CHAR(X+Y));
  END;
/

***************EXECUTION OF PROCEDURE****************
SET SERVEROUTPUT ON;
BEGIN
XSUMM(5,6);
END;
/


10. ADDING NEW ROW BY A PROCEDURE

CREATE OR REPLACE PROCEDURE INSERT_BOOK(vISBN BOOKS.ISBN%TYPE,vCATEGORY BOOKS.CATEGORY%TYPE,vTITLE BOOKS.TITLE%TYPE,vNUM_PAGE BOOKS.NUM_PAGE%TYPE,vPRICE NUMBER,vCOPYRIGHT NUMBER,vAUTHOR1 NUMBER)

IS

BEGIN

INSERT INTO BOOKS
(ISBN,CATEGORY,TITLE,NUM_PAGE,PRICE,COPYRIGHT,AUTHOR1)
VALUES(vISBN,vCATEGORY,vTITLE,vNUM_PAGE,vPRICE,vCOPYRIGHT,vAUTHOR1);

DBMS_OUTPUT.PUT_LINE('ONE NEW RECORD ADDED.');


EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('ONE INTERNAL ERROR.');
END;
/

***************EXECUTING***************************

SET SERVEROUTPUT ON;
BEGIN
INSERT_BOOK('4WE3455','SCIENCE','PHYSICS ON THE ROOF',155,266,2622,2);
END;
/