PLSQL - Day 02

DDL --> Alter , truncate , Create , rename , drop --> These will auto -commit.
DCL --> Grant , Revoke
DML --> No instruction , same as SQL.
Transaction Control Language --> Commit , rollback.

What is VARIABLE ? Its like a BOX , according to size I can vary the size of it.Also USED TO STORE DATA TEMPORARILY.
It can be changed so its called as VARIABLE.
If its constant , at the beginning , IT CAN'T BE CHANGED.

begin
.
.
end:
/

--> This is called execution block / PLSQL block / anonymous block or unnamed block.

PLSQL program can be stored in OBJECT, This is called STORED PROCEDURE.

If you want to store any data in PLSQL then only way is VARIABLE. If you know this then you are the master of it.

I WANT TO STORE DATE ? How ?

declare
d date; -- declaration
begin
dbms_output.put_line(d); 
d := sysdate; -- definition
dbms_output.put_line(d);
d := d+10; -- value of variable changes here
dbms_output.put_line(d);
end;
/

:= --> Assignment operator

declare
d date;
begin
dbms_output.put_line(d); 
d := 'Oracle';
dbms_output.put_line(d);
d := d+10; 
dbms_output.put_line(d);
end;
/

Issue/Error : non-numeric character found where numeric is expected.

declare
d varchar2(3);
begin
dbms_output.put_line(d); 
d := 'Oracle';
dbms_output.put_line(d);
d := d+10; 
dbms_output.put_line(d);
end;
/

Issue/Error : Character string buffer too small

declare
d varchar2(10);
begin
dbms_output.put_line(d); 
d := 'RHEL'; -- no error here
dbms_output.put_line(d);
d := d+10;  -- error here 
dbms_output.put_line(d);
end;
/

Issue/Error : string + 10 --> character to number conversion error

declare
d varchar2(10);
begin
dbms_output.put_line(nvl(d,0)); 
d := 'RHEL'; 
dbms_output.put_line(d);
d := 'Linux';  
dbms_output.put_line(d);
end;
/

To check NULL ?

declare
d varchar2(10);
begin
dbms_output.put_line(d); 
d := 'RHEL'; 
dbms_output.put_line(d);
d := 'Linux';  
dbms_output.put_line(d);
end;
/

Can I use the same variable again ? Is it possible ?

declare
d varchar2(10);
d numbers;
begin
dbms_output.put_line(d); 
d := 'RHEL'; 
dbms_output.put_line(d);
d := 'Linux';  
dbms_output.put_line(d);
end;
/

Error / Issue : at most one declaration for D is permitted

Declared but not used ? Will it throw error ?

declare
d varchar2(10);
e numbers;
begin
dbms_output.put_line(d); 
d := 'RHEL'; 
dbms_output.put_line(d);
d := 'Linux';  
dbms_output.put_line(d);
end;
/

Error / Issue : NO ERROR WILL BE THROWN

Lets debug yesterday class :

declare
a departements%rowtype;
begin
select * into a from departments where rownum=1;
dbms_output.put_line(a.departement_name);
end;
/

declare
a departements%rowtype;
begin
select * into a from departments where rownum=1;
dbms_output.put_line(a.departement_name ||''|| a.location_id);
end;
/

Using the Column Data type and store in Variable :

declare
b locations.city%type;
begin
select city into b from locations where rownum=1;
dbms_output.put_line(b);
end;
/

Multiple column values :

declare
v1 employees.first_name%type;
v2 employees.salary%type;
v3 employees.hire_date%type;
begin
select first_name,salary,hire_date into v1,v2,v3 from employees where employee_id= 120;
dbms_output.put_line(v1||''||v2||''||v3);
end;
/

Multiple column values to ONE Value :

• record.( grouping of customized datatype )

declare
type v is record ( v1 employees.first_name%type , v2 employees.salary%type , v3 employees.hire_date%type ) ;
v4 v;
begin
select first_name,salary,hire_date into v4 from employees where employee_id= 120;
dbms_output.put_line(v4.v1||''||v4.v2||''||v4.v3);
end;
/

How to enter the value in screen ?

declare
type v is record ( v1 employees.first_name%type , v2 employees.salary%type , v3 employees.hire_date%type ) ;
v4 v;
begin
select first_name,salary,hire_date into v4 from employees where employee_id= **_&id_**;
dbms_output.put_line(v4.v1||''||v4.v2||''||v4.v3);
end;
/

IT WILL ASK FOR THE PEOPLE TO ENTER THE VALUE

NOTES:

1. Select query can be saved in Views.
2. cl scr
3. Single quotes --> it will consider as string.
4. set serveroutput on --> dbms output option will be enabled in sql
5. / --> last statement will be executed in sql
6. 1 variable = 1 value only can be store , if you wnat to store means we need to use COLLECTION.
7. Predefined datatype will store only 1 value to the variable.
8. UDT --> User Defined Type --> collection
9. nvl ???
10. If you handle VARIABLE , you are a programmer man.
11. How many variables can be declared ? Any count ? --> Nothing like that.
12. rownum = 1 ??? It will give one row.
13. Why we are writing always select query with one row output ? because variable will store only one value.
14. Prompt --> substitution variable --> &id --> shift+7
15. set verify off --> it won't display any data in the screen.
16. %type , %rowtype , %recordtype --> these are called Anchored types