Interacting with Oracle Server

****************************************

USED TABLE IN EXAMPLES

*****************************************

CREATE TABLE STUD(NAME VARCHAR2(30),ROLL NUMBER(5),DOB DATE,PRIMARY KEY(ROLL));
--------------------------------------------------------

***************************

SELECT STATEMENT IN PL/SQL

***************************

SET SERVEROUTPUT ON
DECLARE
NAME VARCHAR2(30);
ID NUMBER(5);
BEGIN
SELECT ENAME,EMPNO
INTO NAME,ID
FROM EMP
WHERE SAL=5000;
DBMS_OUTPUT.PUT_LINE('ENAME->'||NAME||'***** EMPLOYEE NUMBER->'||ID);
END;
--------------------------------------------------------

***************************

ANOTHER EXAMPLE USING %TYPE ATTRIBUTE

***************************

SET SERVEROUTPUT ON
DECLARE
NAME DEPT.DNAME%TYPE;
ID DEPT.DEPTNO%TYPE;
BEGIN
SELECT DNAME,DEPTNO
INTO NAME,ID
FROM DEPT
WHERE LOC='DALLAS';
DBMS_OUTPUT.PUT_LINE('DEPARTMENT NAME->'||NAME);
DBMS_OUTPUT.PUT_LINE('DEPARTMENT NUMBER->'||ID);

END;
--------------------------------------------------------

***************************

INSERTING DATA THROUGH PL/SQL

***************************

DECLARE
STNAME VARCHAR2(30):='ARKA';
STROLL NUMBER(5):=12;
STDOB DATE:='16-MAY-1985';
BEGIN
INSERT INTO STUD
VALUES(STNAME,STROLL,STDOB);
END;
--------------------------------------------------------

***************************

UPDATING DATA THROUGH PL/SQL

***************************

DECLARE
STROLL NUMBER(5):=20;
BEGIN
UPDATE STUD
SET ROLL = STROLL
WHERE NAME='ARKA';
END;
--------------------------------------------------------

***************************

DELETING DATA THROUGH PL/SQL

***************************

DECLARE
STROLL NUMBER(5):=20;
BEGIN
DELETE FROM STUD
WHERE ROLL=STROLL;
EMP;
--------------------------------------------------------