Oracle Learning Summary 5 (cursors, triggers, procedures, methods)

1: Catch exception thrown by Plsql

Declare
V_ename Emp.ename%type;
Begin
Select Ename to V_ename from EMP where empno=10;
exception
When Too_many_rows Then
Dbms_output.put_line (' Too many values ');
When others then
Dbms_output.put_line (' error ');
End
/

2: Cursors

Declare
Cursor C is
SELECT * from EMP;
V_emp C%rowtype;
Begin
Open C;
Loop
Fetch C into v_emp;
Exit when (C%notfound);
Dbms_output.put_line (V_emp.ename);
End Loop;
Close C;
End
/

Declare
Cursor C is
SELECT * from EMP;
V_emp C%rowtype;
Begin
Open C;
Fetch C into v_emp;
while (C%found) loop
Dbms_output.put_line (V_emp.ename);
Fetch C into v_emp;
End Loop;
Close C;
End
/

For loop:

Declare
Cursor C is
SELECT * from EMP;
V_emp C%rowtype;
Begin
For v_emp in C loop
Dbms_output.put_line (V_emp.ename);
End Loop;
End
/

--Cursors with parameters

Declare
Cursor C (V_deptno emp.deptno%type) is
SELECT * from emp where deptno = V_deptno;
V_emp C%rowtype;
Begin
For v_emp in C (' Ten ') loop
Dbms_output.put_line (V_emp.ename);
End Loop;
End
/

3:procedure Stored Procedures

Create or Replace procedure P (v_a in Number,v_b number,v_retu out number,v_temp on out number)
Is
Begin
if (V_a>v_b) then
V_retu: = v_a;
Else
V_retu: =v_b;
End If;
V_temp: = v_temp+1;
End
/

Call stored procedure p:

Declare
V_a Number: = 3;
V_b Number: = 4;
V_retu number;
V_temp Number: = 5;
Begin
P (v_a,v_b,v_retu,v_temp);
Dbms_output.put_line (V_retu);
Dbms_output.put_line (v_temp);
End
/

4: function

Create or Replace function Sal_tax (SAL number)
return number
Is
Begin
if (sal>3000) then
return 0.1;
elsif (sal>4000) Then
return 0.2;
Else
return 0.3;
End If;
End
/

5: Trigger
Create or Replace trigger trig
After insert or update or delete on EMP for each row
Begin
If inserting then
INSERT into Emp_log values (USER, ' Insert ', sysdate);
elsif updating Then
INSERT into Emp_log values (USER, ' Update ', sysdate);
elsif deleting Then
INSERT into Emp_log values (USER, ' delete ', sysdate);
End If;
End
/

Oracle Learning Summary 5 (cursors, triggers, procedures, methods)