1. What is PL/SQL ?
video 08_01 PL/SQL
PL/SQL - Programming Language/Structured Query Language
Programmatic extensions to SQL
Third Language (3GL)
- Variables, conditional logic, iteration
- Appropriate for data applications
- Block structured
- Oracle specific
- Persistent code
- Block structured
> Anonymous Block
set serveroutput on
DECLARE
lv_variable VARCHAR2(20) := ‘Hello World’;
BEGIN
dbms_output.put_line (lv_variable);
END;
/
> Named Program Unit
Create PROCEDURE hello_there
(p_name IN VHARCHAR2)
IS
BEGIN
dbms_output.put_line (‘Hello there, ’ || p_name);
END;
/
execute hello_there(‘FRED’);
2. Anonymous Block
video 08_02 PL/SQL - Anonymous Block
- Syntax
SET SERVEROUTPUT ON
DECLARE
-- variable declaration, data type, value
-- cursor definitions
BEGIN
- statements
END;
set serveroutput on
declare
lv_name VARCHAR2(20);
lv_job VARCHAR2(20);
lv_hiredate DATE;
CURSOR emp_cursor is
select ename, job, hiredate from emp;
BEGIN
OPEN emp_cursor;
LOOP
fetch emp_cursor into lv_name, lv_job, lv_hiredate;
exit when emp_cursor%NOTFOUND;
dbms_output.put_line ('Employee ' || lv_name || ' Works as a '|| lv_job || ' and was hired on ' || lv_hiredate);
END LOOP;
END;
3. Procedures
video 08_03 PL/SQL - Procedure
set serveroutput on
create or replace procedure employee_report
(p_exclude_emp IN varchar2)
is
lv_name VARCHAR2(20);
lv_job VARCHAR2(20);
lv_hiredate DATE;
CURSOR emp_cursor is
select ename, job, hiredate from emp
where ename <> p_exclude_emp;
BEGIN
OPEN emp_cursor;
LOOP
fetch emp_cursor into lv_name, lv_job, lv_hiredate;
exit when emp_cursor%NOTFOUND;
dbms_output.put_line ('Employee ' || lv_name || ' Works as a '|| lv_job || ' and was hired on ' || lv_hiredate);
END LOOP;
END;
exec employee_report ('ALLEN');
4. Functions
video 08_04 PL/SQL - Functions
CREATE OR REPLACE FUNCTION give_raise
(p_ename in varchar2)
RETURN number
IS
lv_name varchar2(20);
lv_new_sal number;
begin
select sal + sal*.10 lv_new_sal into lv_new_sal
from emp
where ename = p_ename;
return lv_new_sal;
end;
select give_raise('ALLEN') from dual;
(p_ename in varchar2)
RETURN number
IS
lv_name varchar2(20);
lv_new_sal number;
begin
select sal + sal*.10 lv_new_sal into lv_new_sal
from emp
where ename = p_ename;
return lv_new_sal;
end;
select give_raise('ALLEN') from dual;
5. Packages
=============================================================
A. PL/SQL Basics
B. Triggers, procedures, functions
C. Packages
=============================================================
https://www.youtube.com/watch?v=7lTV4POTkWY&list=PL57FE464C5CC88C68
-- OracleCoach PLSQL Video 04 - 0401.sql
set serveroutput on
begin
dbms_output.put_line('Hello World!');
end;
/
-- OracleCoach PLSQL Video 04 - 0402.sql
set serveroutput on
declare
ename varchar2(10) :='SID';
begin
dbms_output.put_line(ename);
end;
/
-- OracleCoach PLSQL Video 04 - 0403.sql
set serveroutput on
declare
vsalary number(10) :=4500;
begin
dbms_output.put_line(to_char(vsalary));
end;
/
-- OracleCoach PLSQL Video 04 - 0404.sql
set serveroutput on
declare
ename varchar2(10) :='SID';
begin
dbms_output.put_line('The Name is: ' || ename);
end;
/
-- OracleCoach PLSQL Video 04 - 0405.sql
set serveroutput on
declare
vsalary number(10,2) :=4500;
begin
dbms_output.put_line('The Salary is ' || to_char(vsalary));
end;
/
-- OracleCoach PLSQL Video 04 - 0406.sql
set serveroutput on
declare
vsalary number(10);
begin
vsalary := 3900;
dbms_output.put_line('The Salary is ' || to_char(vsalary));
end;
/
-- OracleCoach PLSQL Video 05 - 0501.sql
set serveroutput on
begin
dbms_output.put_line('Hello World!');
end;
/
-- OracleCoach PLSQL Video 05 - 0502.sql
set serveroutput on
declare
vname varchar2(10):='SID';
begin
dbms_output.put_line(vname);
end;
/
-- OracleCoach PLSQL Video 05 - 0503.sql
set serveroutput on
declare
n1 number;
begin
n1:=0;
dbms_output.put_line('The first value of n1 is ' || to_char(n1));
n1:=20;
dbms_output.put_line('The second value of n1 is ' || to_char(n1));
n1:=n1+2;
dbms_output.put_line('Incrementing the value of n1 by 2 ' || to_char(n1));
n1:=3*5+4;
dbms_output.put_line('n1 taking the result of the arithemtic is : ' || to_char(n1));
end;
/
-- OracleCoach PLSQL Video 06 - 0601.sql
set serveroutput on
declare
n1 number:=25;
v1 varchar2(5):='HEY';
begin
dbms_output.put_line(n1);
dbms_output.put_line(v1);
end;
/
-- OracleCoach PLSQL Video 06 - 0602.sql
set serveroutput on
declare
n1 constant number:=25;
v1 varchar2(5) not null :='HEY';
begin
dbms_output.put_line(to_char(n1));
dbms_output.put_line('The value of V1 is ' || to_char(v1));
end;
/
-- OracleCoach PLSQL Video 06 - 0603.sql
set serveroutput on
declare
n1 number(4);
begin
n1:=5;
dbms_output.put_line(to_char(n1));
n1:=n1+4;
dbms_output.put_line('The Value of N1 Incremented by 4 is ' || to_char(n1));
end;
/
-- OracleCoach PLSQL Video 06 - 0604.sql
set serveroutput on
declare
n1 number (4);
n2 number (3);
result number;
begin
n1:=7;
n2:=5;
result:=n1+n2;
dbms_output.put_line('The Sum is ' || to_char(result));
result:=n1-n2;
dbms_output.put_line('The difference is ' || to_char(result));
end;
/
-- OracleCoach PLSQL Video 07 - 0701.sql
set serveroutput on
declare
vsalary number(10,2);
begin
select salary into vsalary
from employees where first_name='Lex';
dbms_output.put_line('The salary of Lex is: ' || to_char(vsalary));
end;
/
-- OracleCoach PLSQL Video 07 - 0702.sql
set serveroutput on
declare
vsalary number(10,2);
vdept number(4);
begin
select salary, department_id into vsalary, vdept
from employees where first_name='Lex';
dbmsOracle startup, shutdown, the installation file (Low)_output.put_line('The salary of Lex is: ' || to_char(vsalary));
dbms_output.put_line('The department of Lex is: ' || to_char(vdept));
end;
/
-- OracleCoach PLSQL Video 07 - 0703.sql
set serveroutput on
declare
vjob varchar2(20);
vemail varchar2(25);
vhiredate date;
begin
select job_id,email,hire_date into vjob,vemail, vhiredate
from employees where employee_id='155';
dbms_output.put_line('The employee 150 holds the job of: ' || vjob);
dbms_output.put_line('The email address of employee 150 is: ' || vemail);
dbms_output.put_line('The employee 150 was hired on ' || vhiredate);
end;
/
-- OracleCoach PLSQL Video 08 - 0801.sql
set serveroutput on
declare
vlname varchar2(25);
vsalary number(10,2);
begin
select salary, last_name into vsalary, vlname from employees
where first_name='Lex';
dbms_output.put_line('Lex''s last name is ' || vlname || ' and earns the salary ' || to_char(vsalary));
end;
/
-- OracleCoach PLSQL Video 10 - 1001.sql
SQL> create table t1
2 ( c1 number,
3 c2 varchar2(10),
4 c3 date);
-- OracleCoach PLSQL Video 10 - 1002.sql
set serveroutput on
declare
a number :=2;
d date := sysdate;
begin
insert into t1 values (1,'zz', '29-apr-68');
insert into t1 values (a,'YY', '20-NOV-97');
insert into t1 (c1,c3) values (3,d);
end;
/
-- OracleCoach PLSQL Video 10 - 1003.sql
set serveroutput on
declare
nd date := '02-feb-72';
begin
update t1 set c2 ='AA' where c1=3;
update t1 set c3 =nd where c1=3;
end;
/
-- OracleCoach PLSQL Video 10 - 1004.sql
set serveroutput on
declare
rm number :=3;
begin
delete from t1 where c1=2;
delete from t1 where c1=rm;
end;
/
-- OracleCoach PLSQL Video 12 - 1201.sql
set serveroutput on
declare
vsalary number (10,2);
begin
select salary into vsalary from employees where first_name='Neena';
if vsalary > 24000 then
dbms_output.put_line('Earns more than Steven');
elsif
vsalary > 17000 then
dbms_output.put_line('Earns more than Lex');
else
dbms_output.put_line('Lowest salary in the department');
end if;
end;
/
-- OracleCoach PLSQL Video 13 - 1301.sql
SQL> update employees set salary=4000 where last_name='Seo';
set serveroutput on
declare
vsalary number(10,2);
begin
select salary into vsalary from employees where last_name='Seo';
case vsalary
when 6000 then
dbms_output.put_line('Earns a salary of 6000');
when 4000 then
dbms_output.put_line('Earns a salary of 4000');
when 2000 then
dbms_output.put_line('Earns a salary of 2000');
else
dbms_output.put_line('None of the above salary values');
end case;
end;
/
-- OracleCoach PLSQL Video 14 - 1401.sql
SQL> update employees set salary=18000 where first_name='Neena';
declare
vsalary number(10,2);
begin
select salary into vsalary from employees where first_name='Neena';
case vsalary
when 18000 then
dbms_output.put_line('Earns 18000');
when 15000 then
dbms_output.put_line('Earns 15000');
when 12000 then
dbms_output.put_line('Earns 12000');
else
dbms_output.put_line('none of the above vaules');
end case;
end;
/
-- OracleCoach PLSQL Video 14 - 1402.sql
set serveroutput on
declare
vsalary number(10,2);
vjob varchar2(40);
vhire_date date;
begin
select salary, job_id, hire_date into vsalary, vjob, vhire_date from employees
where first_name='Neena';
case
when vsalary between 10000 and 15000 then
dbms_output.put_line('between 10k and 15k');
when vjob in ('AD_VP','AD_PRES') then
dbms_output.put_line('in the admin department');
when vhire_date > '1-jan-00' then
dbms_output.put_line('hired in last 11 years');
else
dbms_output.put_line('none of the above vaules');
end case;
end;
/
-- OracleCoach PLSQL Video 15 - 1501.sql
set serveroutput on
declare
c1 number:=0;
begin
loop
dbms_output.put_line('Hello World!');
c1:=c1+1;
exit when c1=5;
end loop;
end;
/
-- OracleCoach PLSQL Video 15 - 1502.sql
set serveroutput on
declare
c1 number:=5;
begin
loop
dbms_output.put_line('Hello World!');
c1:=c1+1;
exit when c1=8;
end loop;
end;
/
-- OracleCoach PLSQL Video 16 - 1601.sql
set serveroutput on
declare
c1 number :=0;
begin
loop
dbms_output.put_line('hello');
c1:=c1+1;
if c1=5 then
exit ;
end if;
end loop;
end;
/
-- OracleCoach PLSQL Video 16 - 1602.sql
set serveroutput on
declare
c1 number:=1;
total number:=0;
begin
loop
total:=total+c1;
c1:=c1+1;
if c1=6 then
exit ;
end if;
end loop;
dbms_output.put_line('the sum of the first five numbers is ' || to_char(total));
end;
/
-- OracleCoach PLSQL Video 17 - 1701.sql
set serveroutput on
declare
c1 number:=1;
begin
while (c1<=5)
loop
dbms_output.put_line('Hello');
c1:=c1+1;
end loop;
end;
/
-- OracleCoach PLSQL Video 17 - 1702.sql
set serveroutput on
declare
c1 number:=10;
begin
while (c1<13)
loop
dbms_output.put_line('Hello');
c1:=c1+1;
end loop;
end;
/
-- OracleCoach PLSQL Video 18 - 1801.sql
set serveroutput on
declare
n1 number := 6;
begin
while (n1 <= 10)
loop
dbms_output.put_line ('hello');
n1:=n1+1;
end loop;
end;
/
-- OracleCoach PLSQL Video 18 - 1802.sql
set serveroutput on
declare
n1 number := 1;
total number := 0;
begin
while (n1 <= 5)
loop
total:=total+n1;
n1:=n1+1;
end loop;
dbms_output.put_line ('The sum of 1,2,3,4 and 5 is ' || to_char(total));
end;
/
-- OracleCoach PLSQL Video 19 - 1901.sql
set serveroutput on
begin
for c1 in 1..5
loop
dbms_output.put_line(c1);
end loop;
end;
/
-- OracleCoach PLSQL Video 19 - 1902.sql
set serveroutput on
begin
for c1 in reverse 1..5
loop
dbms_output.put_line(c1);
end loop;
end;
/
-- OracleCoach PLSQL Video 19 - 1903.sql
set serveroutput on
begin
for c1 in 10..13
loop
dbms_output.put_line('Hello');
end loop;
end;
/
-- OracleCoach PLSQL Video 20 - 2001.sql
set serveroutput on
begin
for x in 1..5
loop
dbms_output.put_line(x);
end loop;
end;
/
-- OracleCoach PLSQL Video 20 - 2002.sql
set serveroutput on
begin
for x in 1..5
loop
dbms_output.put_line(x);
end loop;
end;
-- OracleCoach PLSQL Video 21 - 2101.sql
set serveroutput on
begin
delete from employees where employee_id = 112;
if SQL%NOTFOUND then
dbms_output.put_line('The row to be deleted was not found');
else
dbms_output.put_line('The row was deleted');
end if;
end;
/
-- OracleCoach PLSQL Video 21 - 2102.sql
set serveroutput on
begin
delete from employees where employee_id = 157;
if SQL%FOUND then
dbms_output.put_line('The row was deleted');
else
dbms_output.put_line('The row to be deleted was not found');
end if;
end;
/
-- OracleCoach PLSQL Video 21 - 2103.sql
set serveroutput on
begin
delete from employees where employee_id = 182;
if SQL%NOTFOUND then
dbms_output.put_line('The row to be deleted was not found');
else
dbms_output.put_line(SQL%ROWCOUNT || ' row(s) deleted');
end if;
end;
/
-- OracleCoach PLSQL Video 21 - 2104.sql
set serveroutput on
begin
update employees set salary=salary+500 where department_id=100;
if SQL%NOTFOUND then
dbms_output.put_line('The department to be updated was not found');
else
dbms_output.put_line(SQL%ROWCOUNT || ' row(s) updated');
end if;
end;
/
-- OracleCoach PLSQL Video 23 - 2301.sql
set serveroutput on
declare
vename varchar2(45);
cursor c1 is select last_name from employees where department_id=60;
begin
open c1;
loop
fetch c1 into vename;
exit when C1%NOTFOUND;
dbms_output.put_line(vename);
end loop;
close c1;
end;
/
-- OracleCoach PLSQL Video 23 - 2302.sql
set serveroutput on
declare
vename varchar2(45);
cursor c1 is select last_name from employees where department_id=60;
begin
if not c1%isopen then open c1; end if;
loop
fetch c1 into vename;
if c1%found then
dbms_output.put_line(vename);
else
exit;
end if;
end loop;
close c1;
end;
/
-- OracleCoach PLSQL Video 23 - 2303.sql
set serveroutput on
declare
vename varchar2(45);
vsal number(10,2);
cursor c1 is select last_name,salary from employees where salary > 9000;
begin
open c1;
loop
fetch c1 into vename,vsal;
exit when C1%NOTFOUND;
dbms_output.put_line('Row Number ' || c1%rowcount || ' ' || vename || ' : ' || vsal);
end loop;
close c1;
end;
/
--OracleCoach PLSQL Video 25 - 2501.sql
set serveroutput on
declare
vename varchar2(45);
begin
select first_name into vename from employees where employee_id=110;
dbms_output.put_line(vename);
end;
/
--OracleCoach PLSQL Video 25 - 2501.sql
set serveroutput on
declare
vemp121 employees%ROWTYPE;
begin
select * into vemp121 from employees where employee_id=121;
dbms_output.put_line(vemp121.first_name);
dbms_output.put_line(vemp121.last_name);
dbms_output.put_line(vemp121.email);
dbms_output.put_line(vemp121.salary);
dbms_output.put_line(vemp121.department_id);
end;
/
No comments:
Post a Comment