Oracle PL/SQL Programming Foundation Example 2

If loop control statement


If--then endif


If----then----else endif


If-----then--elsif then----else endif


--Write a procedure that allows you to enter an employee's name and increase it by 10% if the employee's salary is less than 2000
Create or Replace procedure Sp_pro6 (spname varchar2) is
V_sal emp.sal%type;
Begin
Select Sal into V_sal from EMP where ename =spname;
--Judgment
If v_sal<2000 Then
Update emp Set sal=sal*1.1 where ename =spname;
End If;
End
--======### #案例s33 Write a procedure that can enter an employee's name if the employee's subsidy is not 0 on the original basis of 100 increase, if it is 0 plus 200
Create or Replace procedure Sp_pro7 (spname varchar2) is
V_comm Emp.comm%type;
Begin
Select Comm to V_comm from EMP where ename =spname;
--Judgment
If V_comm<>0 Then
Update emp Set comm=comm+100 where ename =spname;
Else
Update emp Set comm=comm+200 where ename =spname;
End If;
End








----======== loops Loop End loop
-----Case Write a procedure to enter a user name and add 10 users to the users table when the user number is increased from one to the other
--Build a table
CREATE TABLE users1 (Uid1 number,uname varchar2 (40));

Create or Replace procedure Sp_pro8 (spname varchar2) is
--Defining variables
V_num number: = 1;
Begin
Loop
INSERT into users1 values (v_num,spname);
--Determine whether to exit the loop
Exit when v_num = 10;
--Self-increment
v_num:=v_num+1;
End Loop;
End


----------------===while ... loop end loop


----= = = Case added 10 users starting from 11
Create or Replace procedure Sp_pro8 (spname varchar2) is
--Defining variables
V_num number: = 11;
Begin
While V_num<=20
Loop
-Execution
INSERT into users1 values (v_num,spname);
--Self-increment
v_num:=v_num+1;
End Loop;
End
---------------------for
Begin for I in reverse 1.. Ten loops
INSERT into users1 values (v_num,spname);
End Loop;
End


-----------------Order control Statement goto NULL
Goto Label

<<label>>


-----=-----------The process of returning a result set----=======
---1.----Create a package define a type in the package test_cursor is a cursor
Create or replace package testpackage as
Type test_cursor is REF CURSOR;
End Testpackage;
-----2. Creating a Process
Create or Replace procedure Sp_pro9 (Spno in Number,p_cursor out testpackage.test_cursor)
Is
Begin
Open P_cursor for SELECT * from EMP where deptno=spno;
End



-----3. How to call in Java
---1. Create CallableStatement CS =ct.preparecall ([Call Sp_pro9 (?,?)]);
----Cs.setint (1,10);
----Cs.registeroutparameter (2,oracle.jdbc.oracletypes.cursor);
--Implementation of--cs.execute ();
--Get the result set
/*resultset rs= (ResultSet) Cs.getobject (2);

while (Rs.next ()) {
....
}*/




---------------------Exception Handling---------
Case_not_found
Data_not_found
Cursor_already_open
Dup_val_on_index Unique Index Duplicates
Invaild_cursor perform operations on illegal cursors such as fetching a value from a cursor that is not open or closing a cursor that is not open
Invalid_number
Too_many_rows SELECT INTO when returning more than one line
Zero_divide 2/0
Value_error variable length is not sufficient to accommodate the actual length

-----Custom Exceptions
Create or Replace procedure Ex_test (spno number)
Is
--Define an exception
Myex exception;
Begin
Update emp set sal=sal+1000 where Empno=spno;
--sql%notfound indicates no update
--raise Myex Trigger Myex
If Sql%notfound Then
Raise Myex;
End If;
exception
When Myex Then
Dbms_output.put_line (' No user update ');

End

-----------------------------View---------------
--View Cannot add index
CREATE VIEW MyView as SELECT * from EMP where sal<1000;
SELECT * from MyView;