Oracle_ Stored Procedure Example _1

--Example of cursor if,for
Create or replace procedure peace_if
is
cursor Var_c was 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);
Else if temp.course_name= ' db ' then
Dbms_output.put_line (' db ');
else
Dbms_output.put_line (' Fengla Fengla ');
End If;
End Loop;
End;
--About cursors, for,case example
Create or replace procedure PEACE_CASE1
is
cursor Var_c was 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 ');
When 5 then
Dbms_output.put_line (' haha5 ');
else
dbms_output.put_line (' haha ');
End case;
End Loop;
End;

-Example of cursor For,case 2
Create or replace procedure Peace_case2
is
cursor Var_c was select * from test_case;< Br>begin
for temp in Var_c loop
Case
If temp.vol=1 then
dbms_output.put_line (' haha1 ');
When temp.vol=2 and then
Dbms_output.put_line (' haha2 ');
When Temp.vol=3 and then
Dbms_output.put_line (' haha3 ');
When temp.vol=4 and then
Dbms_output.put_line (' haha4 ');
else
Dbms_output.put_line (' haha5 ');
End case;
End Loop;
End;
--Example of a For loop
--to_char (); Implementing other data types convert to character type
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;

--About loop loop
Create or replace procedure Loop_cur
is
stu_name varchar2 (+);
Course_name varchar2 (100);
Cursor Var_c is select * from grade;
Begin
Open var_c;
Loop
Fetch var_c into stu_name,course_name;
Exit when Var_c%notfound;
Dbms_output.put_line (stu_name| | ' ' | | Course_name);
End Loop;
Close var_c;
End;
-Examples of exception handling
Create or replace procedure Peace_exp (in1 in varchar2)
is
C_n varchar2 (+);
Begin
Select Course_name to 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
and
Dbms_output.put_line (' more ');
End;

--Exception Handling Example 2
Create or Replace procedure Peace_insert (C_n in VARCHAR2)
Is
Error EXCEPTION;
Begin
If c_n = ' OK ' Then
Insert into Grade (Course_name) values (c_n);
elsif c_n= ' NG ' then
Insert into Grade (Course_name) values (c_n);
Raise error;
Else
Dbms_output.put_line (' C_n ' | | ' ' | | C_n);
End If;
Commit
exception
When the error then
Rollback
Dbms_output.put_line (' error ');
End

--Defining Baotou
Create or Replace package peace_pkg
As
function Test1 (in1 in VARCHAR2)
return number;
Procedure Test2 (in2 in varchar2);
End peace_pkg;
--Defining 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;

Oracle_ Stored Procedure Example _1