If loop control statement
If--then endif
If----then----else endif
If-----then--elsif then----else endif
--Write a process that can enter an employee name, assuming that the employee's salary is less than 2000 add 10%
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;
--Inference
If v_sal<2000 Then
Update emp Set sal=sal*1.1 where ename =spname;
End If;
End
--======### #案例s33 Write a process. Be able to enter an employee name, assuming that the employee's subsidy is not 0, add 100 on the original basis. Assuming it's 0, add 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;
--Inference
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 username and add 10 users to the users table when the user number is added 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);
--Infer 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 started with 10 users from 11
Create or Replace procedure Sp_pro8 (spname varchar2) is
--Defining variables
V_num number: = 11;
Begin
While V_num<=20
Loop
--run
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);
--run--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 repeated
Invaild_cursor run operation on an illegal cursor for example, to take a value from a cursor that is not open or to close 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
-----Define your own 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 updates, no matter what the user ');
End
-----------------------------View---------------
--Views cannot be indexed
CREATE VIEW MyView as SELECT * from EMP where sal<1000;
SELECT * from MyView;
Oracle PL/SQL Programming Foundation Example 2
