Procedural Language Structured Query Language
Why we need PLSQL ?
In short it reduces the network traffic. How ?
Delete
Insert
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.
.
.
end;
/
Print statement in PLSQL –> dbms_output.put_line(‘Hi’); –> this is called DBMS output –> Printing statement –> Case INSENSITIVE LANGUAGE.
dbms_output.put_line(‘Hi’);
dbms_output.put_line(123);
dbms_OUTPUT.put_Line(123);
DBMS_OUTPUT.put_Line(123);
end;
/
Below one will throw error ,
end;
/
Below is basic block for PLSQL ,
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 ”
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 🙂
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 :
— –> comment.
DCL & DDL uses keywords.
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.
DBMS output accepts only ONE arguments or columns .
end statement of sql is “;”.
end statement of plsql is “/”.
