PL/SQL

Cursors :(SQL statement in PL/SQL block)

Implicit cursors :

created or started by Oracle

4 steps

create

open

fetch the data

close

4 attributes :

SQL%FOUND

SQL%NOTFOUND

SQL%ROWCOUNT

SQL%ISOPEN

Explicit cursors : normally for more than one row/records

we will give the name(C1) of cursor

C1%FOUND

C1%NOTFOUND

C1%ROWCOUNT

C1%ISOPEN

eg : create a cursor to store all the col/rows from emp table

1. create the cursor

cursor C1 is select * from emp;

2. open the cursor

open C1;

3. fetch the data from the cursor into a variable

abc emp%rowtype;(all columns)

abc1 C1%rowtype;(some solumns)

fetch C1 into abc;

all the records (loop)

loop

fetch C1 into abc;

exit when C1%NOTFOUND;

end loop

4.close or stop the cursor

close C1;

eg :

declare

cursor C2 is select ename, empno from emp;

a1 C2%rowtype;

begin

open C2;

fetch C2 into a1;

loop

DBMS_OUTPUT.put_line(a1.ename);

exit when C2%NOTFOUND;

end loop;

close C2;

end;

Stored Procedure & Functions

Function : always return something

eg : SUM(), AVG(), SUBSTRING(), LENGTH();

Procedure : may or may not return something

eg : commit, rollback, savepoint;

run a PL/SQL block

parameter : arguments

eg :

cursor C1(emp_no number)

a1 IN number, a2 OUT number, a3 IN OUT number

IN : readonly (pass value inside the function, cannot change the value)

eg : constant variable

OUT : write only (cannot pass the value, from inside the function or procedure, get the data or value outside, can change the data)

IN OUT : read and write

a procedure can be thought of a PL/SQL or SQL command, like commit.

create a stored procedure

CREATE [OR REPLACE] PROCEDURE proc_name [(argument[IN|OUT|INOUT] type [DEFAULT value]

...

argument [IN|OUT|IN OUT] type]

IS|AS

<declarative section>

begin

<procedure body>

[EXCEPTION

<exception handlre>]

end;

note : always use CREATE OR REPLACE

actual & formal parameters

exception section

procedure body

eg :

create or replace procedure p1

(a1 IN number, a2 OUT varchar2, a3 IN OUT varchar2)

IS

--> create variables

--> cursor

begin

--> SQL commands

exception

when

end;

/

note : not give the size of the type.

run or execute : (in 1.SQL*PLUS or in 2.PL/SQL block)

1.

exec p1(10, ABC, ‘DDD‘);

2.

begin

var1 := p1(2, aa, ‘AAA‘);

end;

eg :

create or replace procedure EmpInfo(p_empno IN emp.empno%type, p_ename OUR emp.ename%type, p_job OUT emp.job%type, p_deptno OUT emp.deptno%type)

is

cursor emp_info_cur(p_empno emp.empno5type)

is

select ename, job, deptno from emp where empno = p_empno;

r_emp_info emp_info_cur%rowtype;

begin

open emp_info_cur(p_empno);

fetch emp_into_cur into r_emp_info;

close emp_info_cur;

p_ename := r_emp_info.ename;

p_job := r_emp_info.job;

p_deptno := r_emp_info.deptno;

end;

create a stored function

CREATE [OR REPLACE] FUNCTION func_name [(argument [IN|OUT|IN OUT] type [DEFAULT value]

...

argument [IN|OUT|IN OUT] type]

RETURN return_type

IS|AS

<declarative section>

BEGIN

<function body>

[EXCEPTION

<exception handlers>]

END;

note : the function body should include at least one RETURN statement, RETURN expression;)

eg :

create or replace function f1

(a1 IN number, a2 OUT varchar2, a3 OUT number)

return number

is

num1 number;

begin

num1 := 10;

return num1;

exception

when NO_DATA_FOUND then

end;

method to execute the function :

1. variable_name := function_name;

2. select function_name from dual;

3. DBMS_OUTPUT.put_line(Function_name);

1 & 3 --> PL/SQL block

2 --> SQL*PLUS

eg :

select SUM(1,2) from dual;

eg :

1. display the salary from the emp table for the empname. --> give a fixed name or user input.

create or replace function f1(emp_name IN varchar2)

return number

is

a emp.sal%type;

begin

select sal into a from emp

where ename=‘FORD‘;

return a;

end;

how to run or execute the function :

select f1(‘FORD‘) from dual;

OR

declare

b number;

begin

b := f1(‘KING‘);

DBMS_OUTPUT.put_line(b);

end;

2. input the name & display the job.

create or display function f2(emp_job IN varchar2)

return varchar

is

job emp.job%type;

begin

select job into job from emp

where ename = ‘&name‘;

return job;

end;

3. input name & display job and salary.

create or replace function f3(emp_name IN varchar2, emp_job OUT varchar2)

return number

is

f_sal emp.sal%type;

begin

select job, sal into f_job, sal from emp where ename = name;

return f_sal;

end;

execute the function :

declare

f_sal number;

f_job varchar2;

begin

f_sal := f2(‘FORD‘, f_job);

DBMS_OUTPUT.put_line(f_sal || ‘ ‘ || f_job); 

end;

4. display empno, name, sal, job & input empno.

display OUT as RETURN, input IN

create or replace function f4

(empno1 IN emp.empno%type, 

salary OUT emp.sal%type, job1 OUT emp.job%type)

return varchar2

is

name emp.ename%type;

begin

select ename, job, sal 

into name, job1, salary 

from emp where empno = empno1;

return name;

EXCEPTION

when NO_DATA_FOUND then

DBMS_OUTPUT.put_line(‘NO EMPLOYEE‘);

end;

execute the function :

declare

empno12 emp.empno%type := &empno12;

ename1 emp.ename%typt;

job12 emp.job%type;

salary1 emp.sal%type;

begin

ename1 := f4(empno12, salary1, job12);

DBMS_OUTPUT.put_line(empno12 || ‘ ‘ || ename1 || ‘ ‘ || salary1 || ‘ ‘ || job12);

end;

drop function function_name;

user_objects : all your functions, procedures, packages, triggers

5. default value

select f1(7333) from dua;

select f1 from dual;

eg :

create or replace function f5(n number default 1234)

return number

is

salary emp.sal%type;

begin

select sal into salary from emp where empno = n;

return salary;

end;

select f5(7354) from dual;

select f5 from dual;