PLSQL tips for beginner

NOTE:

IF YOU KNOWN THE MS SQL SERVER YOU CAN EASILY GRAPS THE PLSQL SYNTAX. THERE IS NO MORE MAJOR DIFFERENT BOTH OF THEM. FEW THING YOU NEED TO REMEMBER WHEN YOU WORKING WITH PLSQL.

1. DON'T FORGATE TO END STATEMENT WITH SEMICOLUMN (;), BECAUSE SEMICOLUMN (;) TERMINATE THE QUERY LINES. IN MS SQL SERVER SEMICOLUMN (;) IS OPTIONAL EXCEPT FEW CASES.

2. ASSIGNING VALUE TO VARIABLE

MS SQL SERVER

SET @MSG = 'HELLO'

PLSQL

V_MSG:= 'HELLO';

3. CONCANATING THE VARIABLE RESULT

MS SQL SERVER

SET @MSG = @VARIABLE1 + '~' + @VARIABLE2 + '~' + @VARIABLE3

PLSQL

V_MSG:= VARIABLE1 || '~' || VARIABLE2 || '~' || VARIABLE3;

4. VIEW/PRINT THE VARIABLE RESULT

MS SQL SERVER

SET @MSG = 'HELLO'

PRINT @MSG

SELECT @MSG

PLSQL

V_MSG:= 'HELLO';

DBMS_OUTPUT.PUT_LINE (V_MSG);

5. PROCEDURE PARAMETER

MS SQL SERVER

--WITHOUT PARAMETER

CREATE PROCEDURE USP_GET_TESTDATA

AS

BEGIN

    SELECT *

    FROM TABLENAME

END

--WITH PARAMETER

CREATE PROCEDURE USP_GET_TESTDATA

@ID INT

AS

BEGIN

    SELECT *

    FROM TABLENAME WHERE COLUMN1 = @ID

END

PLSQL

NOTE:

YOU NEED TO PASS ATLEAT ONE PARAMETER WITH OUT PARAMETER, TO GET THE RESULT SET.

CREATE OR REPLACE PROCEDURE USP_GET_TESTDATA

(

    P_OUT OUT SYS_REFCURSOR    

)

AS

BEGIN

    OPEN P_OUT FOR

    SELECT *

     FROM TABLENAME;

END USP_GET_TESTDATA;

6. IF CONDITIION

MS SQL SERVER

IF @RESULT = 'P'

     SET @MSG = 'PASS'

ELSE

     SET @MSG = 'FAIL'

END

PLSQL

IF V_RESULT = 'P' THEN

     V_MSG:= 'PASS';

ELSE

     V_MSG:= 'FAIL';

END IF;

7. SET VALUE INTO LOCAL VARIABLE

MS SQL SERVER

SELECT @LOCALVARIABLE1 = COLUMN1, @LOCALVARIABLE2 = COLUMN2, @LOCALVARIABLE3 = COLUMN3

FROM TABLENAME   

WHERE COLUMN1 = 'RAM'

PLSQL

 SELECT COLUMN1, COLUMN2, COLUMN3

    INTO LOCALVARIABLE1, LOCALVARIABLE2, LOCALVARIABLE3

  FROM TABLENAME

WHERE COLUMN1 = 'RAM';

8. CASE SENSITIVE

MS SQL SERVER

MS SQL SERVER IS NOT CASE SENSITIVE.

PLSQL

PLSQL IS PARTIALLY CASE SENSITIVE. IT IS SPECIALY CASE SENSITIVE WHEN YOU USING WHERE CONDITION.

NOTE:

I HAVE STARTED WORKING ON PLSQL SERVER APPROX., BEFORE I WORKED ON MS SQL SERVER. WHEN I STARTED WORKING ON SAME I HAVE FACED SYNTAX DIFFICULTIES AND MORE. SO, I WORKED ON FEW DIFFICULTIES GIVEN ABOVE AND UNDERSTAND IT.

BELOW IS THE STATEMENT TO TEST LOCAL VARIABLE, PRINT LOCAL VARIABLE VALUE AND SELECT THE SYSTEM FUNCTION ETC TO UNDERSTAND THE PLSQL ENVIORNMENT.

9. TO TEST STATEMENT

MS SQL SERVER

BEGIN

            --LOCAL VARIABLE HERE

            DECLARE @V_DATE CHAR(2)

            DECLARE @V_MONTH CHAR(2)

            DECLARE @V_YEAR CHAR(4)

            DECLARE @V_FULLDATE VARCHAR(10)

            --TEST STATEMENT HERE

            SET @V_DATE = DATEPART(dd,GETDATE())

            SET @V_MONTH = DATEPART(mm,GETDATE())

            SET @V_YEAR = DATEPART(yyyy,GETDATE())

            SET @V_FULLDATE = @V_DATE + '/' + @V_MONTH + '/' + @V_YEAR

            PRINT @V_FULLDATE

END;

PLSQL

DECLARE

            --LOCAL VARIABLE HERE

            V_DATE NUMBER (4);

            V_MONTH NUMBER (4);

            V_YEAR NUMBER (4);

            V_FULLDATE VARCHAR (10);

BEGIN

            --TEST STATEMENT HERE

            SELECT TO_CHAR (SYSDATE,'DD') INTO V_DATE FROM DUAL;

            SELECT TO_CHAR (SYSDATE,'MM') INTO V_MONTH FROM DUAL;

            SELECT TO_CHAR (SYSDATE,'YYYY') INTO V_YEAR FROM DUAL;

            V_FULLDATE:= V_DATE || '/' || V_MONTH || '/' || V_YEAR;

            DBMS_OUTPUT.PUT_LINE ('DATE' || V_FULLDATE);

END;

10. CONVERT TO DATE

SELECT TO_DATE (SYSDATE,'DD/MM/YYYY') FROM DUAL;

SELECT TRUNC (SYSDATE) FROM DUAL;

11. CONVERT TO CHAR

SELECT TO_CHAR (SYSDATE,'DD/MM/YYYY') FROM DUAL;

12. GET CURRENT DATE, MONTH AND YEAR

SELECT TO_CHAR (SYSDATE,'DD') FROM DUAL;

SELECT TO_CHAR (SYSDATE,'MM') FROM DUAL;

SELECT TO_CHAR (SYSDATE,'YYYY') FROM DUAL;

13. SELECT LOCAL VARIABLE

SELECT COLUMN1, COLUMN2, COLUMN3

   INTO LOCALVARIABLE1, LOCALVARIABLE2, LOCALVARIABLE3

 FROM TABLENAME;

14. GET RECORD SET USING STORE PROCEDURE

CREATE OR REPLACE PROCEDURE USP_GET_TESTDATA

(

    P_OUT OUT SYS_REFCURSOR    

)

AS

BEGIN

    OPEN P_OUT FOR

    SELECT *

     FROM TABLENAME;

END USP_GET_TESTDATA;

15. IF CONDITION

DECLARE

            --LOCAL VARIABLE HERE

            V_RESULT VARCHAR2 (1);

            V_MSG VARCHAR2 (10);

BEGIN

            --TEST STATEMENT HERE

            V_RESULT:= 'P';

            IF V_RESULT = 'P' THEN

                        V_MSG:= 'PASS';

            ELSE

                        V_MSG:= 'FAIL';

            END IF;

            DBMS_OUTPUT.PUT_LINE ('RESULT' || V_MSG);

END;

16. LOOP CONDITION

DECLARE

            --LOCAL VARIABLE HERE

            V_LOOP NUMBER (4);

            V_COUNT NUMBER (4);

BEGIN

            --TEST STATEMENT HERE

            V_COUNT:= 10;

            FOR V_LOOP IN 1..V_COUNT LOOP

DBMS_OUTPUT.PUT_LINE ('V_LOOP ' || V_LOOP || ' V_COUNT ' || V_COUNT);

            END LOOP;

            --1..V_COUNT

            /*IT MEANS LOOP START FROM 1 AND RUN TILL 10*/

END;

17. RANK FUNCTION

MS SQL SERVER

CREATE TABLE #RankTable(Alphabet CHAR(1));

INSERT INTO #RankTable VALUES ('A');

INSERT INTO #RankTable VALUES ('A');

INSERT INTO #RankTable VALUES ('A');

INSERT INTO #RankTable VALUES ('B');

INSERT INTO #RankTable VALUES ('C');

INSERT INTO #RankTable VALUES ('C');

INSERT INTO #RankTable VALUES ('D');

INSERT INTO #RankTable VALUES ('E');

SELECT Alphabet,

       ROW_NUMBER() OVER(ORDER BY Alphabet) ROW_NUMBER,

       RANK() OVER(ORDER BY Alphabet) RANK,

       DENSE_RANK() OVER(ORDER BY Alphabet) DENSE_RANK

  FROM #RankTable

 ORDER BY Alphabet;

DROP TABLE #RankTable

PLSQL

CREATE TABLE RankTable(Alphabet CHAR(1));

INSERT INTO RankTable VALUES ('A');

INSERT INTO RankTable VALUES ('A');

INSERT INTO RankTable VALUES ('A');

INSERT INTO RankTable VALUES ('B');

INSERT INTO RankTable VALUES ('C');

INSERT INTO RankTable VALUES ('C');

INSERT INTO RankTable VALUES ('D');

INSERT INTO RankTable VALUES ('E');

SELECT Alphabet,

       ROW_NUMBER() OVER(ORDER BY Alphabet) ROW_NUMBER,

       RANK() OVER(ORDER BY Alphabet) RANK,

       DENSE_RANK() OVER(ORDER BY Alphabet) DENSE_RANK

  FROM RankTable

 ORDER BY Alphabet;

DROP TABLE RankTable;

ROW_NUMBER () - Returns the sequential numbers.

RANK () - Returns the rank of each row in a result set based on specified criteria. If two or more records duplicate, they should be given the same rank. However, the next rank number will be addition of the rank of the records. 

DENSE_RANK () - The function is used where sequential ranking values need to be given based on a specified criteria. If two or more records duplicate, they should be given the same rank. However, the next rank number would be assigned the next rank value.