- Enable output
1
SET SERVEROUTPUT ON
- Comments
1
2
3
4
5
6
7
-- single line comment
/*
multiple line comment
*/
- Setup tables
1
2
3
4
5
6
7
8
9
create table rahul_emp as select * from emp
create table rahul_dept as select * from dept
alter table rahul_emp add primary key(empno)
alter table rahul_dept add primary key(deptno)
alter table rahul_emp add foreign key(deptno) references rahul_dept(deptno)
- Describe the tables that we setup
1
2
3
desc rahul_emp
desc rahul_dept
- PLSQL begins
1
2
3
4
5
6
7
8
9
10
11
declare
msal number(9,2);
mename varchar2(20);
begin
select ename , sal into mename,msal from rahul_emp
where empno=7900;
dbms_output.put_line(' the salary of employee '||mename||' is '||msal);
exception
when no_data_found then
dbms_output.put_line('no such employee found');
end;
1
2
3
4
5
6
7
8
9
10
declare
dname varchar2(20);
begin
select dname into dname from rahul_dept
where deptno=40;
dbms_output.put_line(' the dept name is '||dname);
exception
when no_data_found then
dbms_output.put_line('no such employee found');
end;
- %type for type of a variable
1
2
3
4
5
6
7
8
9
10
declare
dname rahul_dept.dname%type;
begin
select dname into dname from rahul_dept
where deptno=40;
dbms_output.put_line(' the dept name is '||dname);
exception
when no_data_found then
dbms_output.put_line('no such employee found');
end;
- The above examples combined
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
declare
msal number(9,2);
mename varchar2(20);
deptno rahul_emp.deptno%type;
dcount number(4);
begin
select ename , sal,deptno into mename,msal,deptno from rahul_emp
where empno=7900;
select count(*) into dcount from rahul_dept
where deptno=deptno;
dbms_output.put_line(' the salary of employee '||mename||' is '||msal);
dbms_output.put_line(' the total department count is '||dcount);
exception
when no_data_found then
dbms_output.put_line('no such employee found');
end;