Multiple examples of Oracle stored procedures

Transferred from: http://www.jb51.net/article/19541.htm


An example of a cursor if,for
Create or replace procedure peace_if
Is
Cursor Var_c is select * from grade;
Begin
For temp in Var_c loop
If Temp.course_name = ' OS ' Then
Dbms_output.put_line (' stu_name = ' | | Temp.stu_name);
elsif temp.course_name = ' DB ' Then
Dbms_output.put_line (' DB ');
Else
Dbms_output.put_line (' Feng La feng la ');
End If;
End Loop;
End
---Example of a cursor for,case 1
Create or replace procedure Peace_case1
Is
Cursor Var_c is select * from Test_case;
Begin
For temp in Var_c loop
Case Temp.vol
When 1 Then
Dbms_output.put_line (' haha1 ');
When 2 Then
Dbms_output.put_line (' haha2 ');
When 3 Then
Dbms_output.put_line (' haha3 ');
When 4 Then
Dbms_output.put_line (' haha4 ');
Else
Dbms_output.put_line (' Qita ');
End case;
End Loop;
End
---Example of a cursor For,case 2
Create or replace procedure Peace_case2
Is
Cursor Var_c is select * from Test_case;
Begin
For temp in Var_c loop
Case
When Temp.vol=1 Then
Dbms_output.put_line (' haha1 ');
When temp.vol=2 Then
Dbms_output.put_line (' haha2 ');
When Temp.vol=3 Then
Dbms_output.put_line (' haha3 ');
When Temp.vol=4 Then
Dbms_output.put_line (' haha4 ');
Else
Dbms_output.put_line (' Qita ');
End case;
End Loop;
End
---Example of a For loop
Create or replace procedure Peace_for
Is
SUM1 number: = 0;
Temp VARCHAR2 (500);
Begin
For I in 1..9 loop
Temp: = ';
for j in 1. I
Loop
SUM1: = i * j;
Temp: = temp| | To_char (i) | | ' * ' || To_char (j) | | | To_char (sum1) | | ';
End Loop;
Dbms_output.put_line (temp);
End Loop;
End
---Examples of loop loops
Create or replace procedure Peace_loop
Is
SUM1 number: = 0;
Temp number: =0;
Begin
Loop
Exit when temp >= 10;
SUM1: = sum1+temp;
Temp: = Temp +1;
End Loop;
Dbms_output.put_line (SUM1);
End

---Examples of cursors and loop loops
Create or replace procedure Loop_cur
Is
Stu_name varchar2 (100);
Course_name varchar2 (100);
Cursor Var_cur is select * from grade;
Begin
Open var_cur;
Loop
Fetch var_cur into stu_name,course_name;
Exit when Var_cur%notfound;
Dbms_output.put_line (stu_name| | course_name);
End Loop;
Close var_cur;
End
---Examples of exception handling
Create or Replace procedure Peace_exp (in1 in VARCHAR2)
Is
C_n varchar2 (100);
Begin
Select Course_name into C_n from grade where stu_name = in1;
Dbms_output.put_line (C_n);
exception
When No_data_found
Then
Dbms_output.put_line (' Try ');
When Too_many_rows
Then
Dbms_output.put_line (' more ');
End

---Example of exception handling 2
Create or Replace procedure Peace_insert (C_n in VARCHAR2)
Is
Error EXCEPTION;
Begin
If c_n = ' OK '
Then
Insert into course (Course_name) values (c_n);
elsif c_n = ' NG ' Then
Insert into course (Course_name) values (c_n);
Raise error;
Else
Dbms_output.put_line (' c_n ' | | | | c_n);
End If;
Commit
exception
When error Then
Rollback
Dbms_output.put_line (' Erro ');
End
---An example of a package that defines a package
Create or Replace package peace_pkg
As
function Test1 (in1 in VARCHAR2)
return number;
Procedure Test2 (in2 in varchar2);
End peace_pkg;
---An example of a package to define the package body
Create or replace package body peace_pkg
As
function Test1 (in1 in VARCHAR2)
return number
As
Temp number;
Begin
Temp: = 0;
return temp;
End
Procedure Test2 (in2 in VARCHAR2)
Is
Begin
Dbms_output.put_line (in2);
End
End peace_pkg;
Detailed Source reference: http://www.jb51.net/article/19541.htm