OraclePL/SQL programming basic instance 2

If loop control statement

If -- then endif

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

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

-- Write a process and enter an employee name. If the employee's salary is lower than 2000, the employee will be increased by 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;
-- Judgment
If v_sal <2000 then
Update emp set sal = sal * 1.1 where ename = spName;
End if;
End;
-- ======#### Write a process in case s33, you can enter an employee name. If the employee's subsidy is not 0, it will be increased by 100 on the basis of the original employee, add 200 if it is 0.
Create or replace procedure sp_pro7 (spName varchar2) is
V_comm emp. comm % type;
Begin
Select comm into 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;

---- ========= Loop end loop
----- When writing a case, you can enter the user name and add 10 users to the users table. The user number is increased from 1.
-- Create tables
Create table users1 (uid1 number, uname varchar2 (40 ));

Create or replace procedure sp_pro8 (spName varchar2) is
-- Define 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;
-- Auto-Increment
V_num: = v_num + 1;
End loop;
End;

---------------- === While... loop end loop

---- === Add 10 users from 11
Create or replace procedure sp_pro8 (spName varchar2) is
-- Define variables
V_num number: = 11;
Begin
While v_num <= 20
Loop
-- Execute
Insert into users1 values (v_num, spName );
-- Auto-Increment
V_num: = v_num + 1;
End loop;
End;
---------------------
Begin for I in reverse 1 .. 10 loop
Insert into users1 values (v_num, spName );
End loop;
End;

----------------- Sequence control statement goto null
Goto label

<Label>

----- = ----------- Process of returning the result set ---- ======
--- 1. ---- create a package and define a type test_cursor in the package as a cursor.
Create or replace package testpackage
Type test_cursor is ref cursor;
End testpackage;
----- 2. creation 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
--- 1. Create Callablestatement cs = ct. prepareCall ([call sp_pro9 (?,?)]);
---- Cs. setInt (1, 10 );
---- Cs. registerOutParameter (2, oracle. jdbc. OracleTypes. CURSOR );
-- --Cs.exe cute ();
-- Obtain 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 already exists
Invaild_cursor performs operations on an invalid cursor, for example, starting from an unopened cursor or closing a unopened cursor.
Invalid_number
When too_many_rows select into, more than one row is returned.
Zero_divide 2/0
The length of the value_error variable is insufficient 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 triggers myex
If SQL % notfound then
Raise myex;
End if;
Exception
When myex then
Dbms_output.put_line ('no user updated ');

End;

----------------------------- View ---------------
-- Indexes cannot be added to a view.
Create view myview as select * from emp where sal <1000;
Select * from myview;