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.
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 :
- -- --> 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 "/".
- PLSQL is used only with oracle.
Top comments (0)