• ← SQL-COMMANDS
• PL/SQL - Lec 14
  • General Stucture:
    • Example: Program to find the months between two predefined dates
    • User Input
    • Conditional Statements
    • Loop
  • Record Variables and Cursor
    • Defining a cursor and record variable
      • Cursor syntax
      • Record Variable syntax
      • Cursor for loop
    • Advantages of cursor
  • Procedure
    • Parameters in procedure
• PL/SQL - Lec 15
  • Creating a function:
    • Example: Count number of entries in batman table
    • Returning multiple records using a funciton
  • Nested Cursor
• PL/SQL - Lec 16
  • Triggers
    • Advantages / Use cases
    • Types
    • Example
• Lec-17 Views ➡
• Assignment-1 →
• Assignment-2 →

PL/SQL - Lec 14

• Named blocks: Procedures, Triggers, Functions
• Unnamed blocks: Blocks with declare, begin and end

General Stucture:

declare
	declaration statements
begin
	execuatable statements
exception
	to customise error msgs
end;
/

Example: Program to find the months between two predefined dates

declare
	i int := 0; 	-- ":=" is assignment operator 
	j int;

	dt date := sysdate;
	dt_1 date := '12-oct-1970';
begin
	i := months_between(dt, dt_1);
	dbms_output.put_line('Months between ' || dt || ' and ' || dt_1 || ' = ' || i);
end;
/

• ser serveroutput on - if the output doesn't show

User Input

declare
	i int := 0; 	-- ":=" is assignment operator 
	j int;

	dt date := :dt;
	dt_1 date := :dt;
begin
	i := months_between(dt, dt_1);
	dbms_output.put_line('Months between ' || dt || ' and ' || dt_1 || ' = ' || i);
end;
/

• Use ":" to take input in GUI editions
• Use "&" for command line editions

Conditional Statements

declare 
	i int;
	j int;

	dt_1 date := '1-mar-2002'
	dt_2 date := '1-feb-2003'
begin
	i = monts_between(dt_1, dt_2);
	if(i < 10) then
		dbms_output.put_line('Gap less than 10');
	elseif(i < 20) then
		dbms_output.put_line('Gap less than 20 but > 10');
	else
		dbms_output.put_line('Gap greater than 20');
end;
/

Loop

• for, reversing the range and while

declare
	i int;
begin
	for i in 1..10 loop
		dbms_output.put_line(j);
	end loop;
	
	-- to reverse the loop

	for i in reverse 1..10 loop
		dbms_output.put_line(j);
	end loop;

	-- while
	
	i := 0;
	while ( j<= 9) loop
		j := j + 1;
		dbms_output.put_line(j);
	end loop
end;
/

• TODO: how to increment by two in for loop
• No syntactical solution

Record Variables and Cursor

Defining a cursor and record variable

• Cursor attributes
  • %notfound - returns true if there are no records in the cursor
  • %found - return true if there is atleast one record in cursor
  • %isopen - returns true if cursor is open
  • %type - to define the dataype from the record
  • %rowtype - to define record

Cursor syntax

• cursor <c_name> is <select_query>;

• cursor name should be c_short_name_for_table

• General Steps:

  • declare cursor
  • open cursor (open <c_name>;)
  • fetch record in loop
  • close cursor

Record Variable syntax

• <r_name> <c_name>%rowtype; -- afaiu, declares i.e. makes the datatype same as that of the record

• Example

declare
	cursor c_batsman is select * from batsmen;
	r_batsman c_batsman%rowtype;
begin
	open c_batsman;
	loop
		if(c_batsman%notfound) then
			exit;
		end if;

		fetch c_batsman into r_batsman;
		dbms_output.put_line(r_batsman.name);
	end loop;
	close c_batsman;
end;
/

• Observe:
  • exit - works like break
  • last record appars twice - how to fix that?

Cursor for loop

• no need to open, close or write the exit condition or fetch

declare
	cursor c_batsman is select * from batsmen;
	r_batsman c_batsman%rowtype;
begin
	for r_batsman in c_batsman loop
		dbms_output.put_line(r_batsman.name);
	end loop;
end;
/

Advantages of cursor

• can get very selected records

Procedure

• same as unnamed block used above
• "create procedure <procedure_name> as" replaces declare
• execute procedure_name for executing it

Parameters in procedure

create procedure <procedure_name>(... paramslist) as

• to execute

exec <procedure_name>(... paramslist)

PL/SQL - Lec 15

Creating a function:

• Function returns one and only one value, while procedure doesn't return anything

create function <function_name>(... params list) return <return_type> as
	-- declaration statements
begin
	-- executable statemsnts with a return statement
end;
/

• can be executed using a calling block
• can be executed using select

select func_name(... params) from dual;

• dual is a virtual table of oracle

Example: Count number of entries in batman table

create function count_batsman return int as
	cursor c_batsman is select count(*) cnt from batsman;
	r_batsman c_batsman%rowtype;
begin
	open c_batsman;
	fetch c_batsman into r_batsman;
	return r_batsman.cnt;
	close c_batsman;
end;
/

• Here count is a calculated field, so we define a variable cnt to use it later as return r_batsman.cnt

Returning multiple records using a funciton

• Funciton returns a single value only
• To get multiple records we can use string concatenation
• char(10) to print / add a new line char to string

Nested Cursor

• rating schema from mids

declare
	cursor c_batsman is select * from batsman;
	r_batsman c_batsman%rowtype;

	-- parameterized constructor
	cursor c_rating(b_id batsman.batsman_id&type) is select * form rating where batsman_id=b_id;
	r_rating c_rating%rowtype;
begin
	open c_batsman;
	loop
		fetch c_batsman into r_batsman;

		-- exit condition to be returned after fetching
		
		if c_batsman%notfound the
			exit;
		end if;
		dbms_output.put_line('Rating of:' r_batsman.name);

		open c_rating(r_batsman.batsman_id);
		loop
			fetch c_rating into r_rating;
			if c_rating%notfound then
				exit;
			end if;
			dbms_output.put_line(r_batsman.rating_in||' '||r_batsman.ratings);
		end loop;
		close c_rating;
	end loop;
	close c_batsman;

• parameterised cursor
• nested cursors

PL/SQL - Lec 16

Triggers

• Based on some data manipulation(insert, delete, update), triggers gets fired automatically

Advantages / Use cases

• data validation
• delete child records when parent record is deleted
• add security constraints(ex restrictig manipulation for some time)
• autocalculation and insertion of same record in other tables also

Types

1. Statement Level Trigger
   • Gets fired only when a statement is executed
2. Row Level Trigger
   • Gets fired for each row
   • we add (for each row) while creating the table
   • We have two inbuilt variables for row level trigger
     1. :new
        • Example trigger is created on insert into emp values(1, 'abc', '12-dec-1999);
        • If we want to refer this values we can use them as :new.emp_id
     2. :old
        • This can be used when we are deleting something
        • While updating we may need both :new and :old

• also we can add before after in trigger(before insert, after insert, before delete, after delete ...)

Example

• Simple trigger to check if a field is not null

create trigger check_title before insert or update on <table_name>
for each row
begin
	if(:new.field_name is null) then
		-- in built procedure to raise error
		raise_application_error(code, 'error-msg');
	end if
end;
/

• code should not clash with in-built error messages
• start with 20000
• we can also write before or after