Skip to main content

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;
/




Labels:

Comments

Post a Comment

Popular posts from this blog

Background Process in ORACLE Database

Background Process in ORACLE Database Database Writer Process (DBWn) Log Writer Process (LGWR) Checkpoint Process (CKPT) System Monitor Process (SMON) Process Monitor Process (PMON) Recoverer Process (RECO) Job Queue Processes Archiver Processes (ARCn) Queue Monitor Processes (QMNn) Database Writer Process (DBWn) The database writer process (DBWn) writes the contents of buffers to datafiles. The DBWn processes are responsible for writing modified (dirty) buffers in the database buffer cache to disk. Although one database writer process (DBW0) is adequate for most systems, you can configure additional processes (DBW1 through DBW9 and DBWa through DBWj) to improve write performance if your system modifies data heavily. These additional DBWn processes are not useful on uniprocessor systems. When a buffer in the database buffer cache is modified, it is marked dirty. A cold buffer is a buffer that has not been recently used according to the least recently used (LRU) algorith...
Post a Comment
Read more

Using GREP in UNIX

How To Use grep Command In Linux / UNIX by  VIVEK GITE  on  AUGUST 2, 2007  ·  147 COMMENTS H ow do I use grep command in Linux? grep command searches the given file for lines containing a match to the given strings or words. By default, grep prints the matching lines. Use grep to search for lines of text that match one or many regular expressions, and outputs only the matching lines. The name, "grep", derives from the command used to perform a similar operation, using the Unix/Linux text editor ed: g/re/p grep command syntax grep 'word' filename grep 'string1 string2' filename cat otherfile | grep 'something' command | grep 'something' Use grep to search file Search /etc/passwd for boo user: $ grep boo /etc/passwd You can force grep to ignore word case i.e match boo, Boo, BOO and all other combination with -i option: $ grep -i "boo" /etc/passwd Use grep recursively You can search recursively i.e. read all files under each ...
Post a Comment
Read more

Searching BOOLEAN on the INTERNET

A Primer in Boolean Logic The Internet is a vast computer database. As such, its contents must be searched according to the rules of computer database searching. Much database searching is based on the principles of Boolean logic. Boolean logic refers to the logical relationship among search terms, and is named for the British-born Irish mathematician George Boole. On Internet search engines, the options for constructing logical relationships among search terms often modify the traditional practice of Boolean searching. This will be covered in the section below, Boolean Searching on the Internet. Boolean logic consists of three logical operators: OR AND NOT Each operator can be visually described by using Venn diagrams, as shown below. OR logic college OR university Question: I would like information about college. In this search, we will retrieve records in which AT LEAST ONE of the search terms is present. We are searching on the term...
Post a Comment
Read more