PL/SQL programming (2)

1 For Loop
Syntax: begin
For I in reverse 1 .. 10 loop
Insert into users values (I, 'Obama ');
End loop;
End;
Note: The Circular Variable I is implicitly added, so it cannot be seen

2 goto statements
The goto statement is used to jump to a specific position to execute the statement. Since the goto statement reduces the readability of the program
The goto statement is not recommended.

3 null statement
The null statement does not perform any operations, but can increase the readability of the program.

4. The returned value is the stored procedure of a result set.
(1) create a package:
SQL> create or replace package testpackage
Type test test_cursor is ref cursor;
End testpackage;

(2) create a stored procedure
SQL> create or replace procedure sp_procedure1
(StuNo in number, param_cursor out testpackage. test_cursor) is
Begin
Open param_cursor for select * from emp where sutno = stuNo;
End;

5 pages
(1) SQL statements
Select * from
(Select *, rownum NO from
(Select * from emp) where rownum <= 20) where rownum> = 10;

(2) create a package
Create or replace package testpackage2
Type test test_cursor is ref cursor;
End testpackage2;

(3) create a stored procedure
SQL> create or replace procedure procedureName2
(TableName in varchar2, -- table name
PageSize in number, -- number of records displayed on each page
PageNow in number, -- current page number
PageCount out number, -- total number of pages
P_cursor out testpackage2.test _ cursor) is
V_ SQL varchar2 (1000 );
V_beginNum number: = (pageNow-1) * pageSize + 1;
V_endNum number: = pageNow * pageSize;
Begin
V_ SQL: = 'select * from (select *, rownum NO from (select * from' | tableName | ')
Where rownum <= '| v_endNum |') where rownum> = '| v_beginNum;
Open p_cursor for v_ SQL;
-- Create an SQL statement
V_ SQL: = 'select count (*) from' | tableName;
-- Execute the SQL statement and save the result
Execute immediate v_ SQL into rows;
If mod (rows, pageSize) = 0
Then pageCount: = rows/pageSize;
Else
PageCount: = rows/pageSize + 1;
End if;
-- Close the cursor
Close p_cursor;
End;

6. Exception Handling
(1) pre-defined exception
(2) Non-predefined exceptions
(3) custom exceptions

Example 1
SQL> declare v_name emp. ename % type;
Begin
Select ename into v_name from emp where empno = & no;
Dbms_output.put_line ('name: '| v_name );
Exception
When no_data_found
Then dbms_output.put_line ('No! ');
End;

Pre-defined exception
A case_not_found
When compiling a case statement, if the when clause does not contain a required condition Branch (no qualified branch), The case_not_found exception is triggered.
B cursor_already_open
This exception is triggered when an opened cursor is re-opened.
C dup_val_on_index
This exception is triggered when duplicate values are inserted in columns corresponding to a unique index.
D invalid_cursor
This exception is triggered when an operation is attempted on an invalid cursor.
E invalid_number
This exception is triggered when the entered number is invalid.
F too_many_rows
This exception is triggered when more than one record is returned.
G zero_divide
This exception is triggered when x/0, that is, the divisor is zero.
H value_error
This exception is triggered when the variable length is insufficient to store actual data during the value assignment operation.
I login -- denide
This exception is triggered when a user logs on illegally.
J not_logged_on
This exception is triggered if you do not log on to DML.
K storage_error
If the memory space is exceeded, this exception is triggered.
L timeout_on_resource
This exception is triggered when Oracle waits for resources and times out.

Custom exception
SQL> create or replace procedure procedureName2 (sp_empNo number) is
MyExpception extends tiom; -- custom an exception
Begin
Update emp set sal = sal * 1.2 where empno = & no;
If SQL % notfound then
Raise MyExpception; -- triggers a custom exception
End if;
Exception
When no_data_found
Then dbms_output.put_line ('No data is updated! ');
End;