PLSQL - Day 01

Procedural Language Structured Query Language

Why we need PLSQL ?

In short it reduces the network traffic. How ?

1. Delete
2. Insert
3. Select

These each query will provide feedback from when its get executed each time like " 1 row inserted " , " 1 row deleted " all these REPLY is nothing but FEEDBACK.
This is called NETWORK TRAFFIC , if it happens 100 times means think about the TRAFFIC.
So if you write in PLSQL then it will give a feedback " ONLY 1 REPLY OR FEEDBACK ".

sql --> 100 statement = 100 feedback ;
plsql --> 100 statement = 1 feedback ;

Its also called EXTENSION TO SQL.

begin
.
.
end;
/

Print statement in PLSQL --> dbms_output.put_line('Hi'); --> this is called DBMS output --> Printing statement --> Case INSENSITIVE LANGUAGE.

begin
dbms_output.put_line('Hi');
dbms_output.put_line(123);
dbms_OUTPUT.put_Line(123);
DBMS_OUTPUT.put_Line(123);
DBMS_OUTPUT.put_Line(10+2);
end;
/

Below one will throw error ,

begin
end;
/

Below is basic block for PLSQL ,

begin
null;
end;
/

DCL commands --> Grant , Revoke --> Rule is there in PLSQL
DCL command can't be used directly within the procedure , as mentioned earlier , we need to use " KEY WORDS BEFORE THAT " followed by SINGLE QUOTES.
execute immediate 'grant ....'
These key words are called " Dynamic SQL "

begin
execute immediate 'grant select on t1 to user2';
end;
/

DDL ( Create , Alter , rename , drop ) also needs to be executed with keywords ONLY.

DRL

• Select statement
• INTO CLAUSE needs to be used.
• Here comes another hero , Variable --> Temporary space
• Variables needs to be declared with Data types.Eg., C EMPLOYEE%ROWTYPE; Syntax --> Variable_Name TABLENAME%DATATYPE.
• Also each variable will store only 1 row.
• Collection --> Day 1 we can't learn now :-)

DECLARE
C EMPLOYEE%ROWTYPE;
BEGIN
SELECT * INTO C FROM EMPLOYEE WHERE ID=100;
DBMS_OUTPUT.PUT_LINE(C.COLUMN1||C.COLUMN2);
END;
/

DBMS_OUTPUT.PUT_LINE(C.COLUMN1||C.COLUMN2); If you needs space means add single quotes like this --> DBMS_OUTPUT.PUT_LINE(C.COLUMN1||' '||C.COLUMN2);

Notes :

1. -- --> comment.
2. DCL & DDL uses keywords.
3. If you use "Select Statement" then all these error's are expected --> no data found , exact fetch issue , into clause is expected in select statement.
4. DBMS output accepts only ONE arguments or columns .
5. end statement of sql is ";".
6. end statement of plsql is "/".
7. PLSQL is used only with oracle.