1 for Loop
Syntax: Begin
For i in reverse 1..10 loop
Insert into users values (i, ' Obama ');
End Loop;
End
Note: The loop variable i is implicitly incremented, so you cannot see
2 Goto Statement
The goto statement is used to jump to a specific location to execute a statement. Because the goto statement reduces the readability of the program, it is generally
Using GOTO statements is not recommended
3 NULL statement
Null statements do nothing, but can increase the readability of the program
4 Create a return value is a result set of stored procedures
(1) Create a package:
sql>Create or replace package testpackage as
Type test test_cursor is REF CURSOR;
End Testpackage;
(2) Creating 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 Pagination
(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 as
Type test test_cursor is REF CURSOR;
End Testpackage2;
(3) Creating a stored procedure
sql>Create or replace procedure ProcedureName2
(TableName in Varchar2,--table name
PageSize in numbers,--number of records displayed per page
Pagenow in number,--now is the first page
PageCount out number,--Total 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 ' | | 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 cursor
Close p_cursor;
End
6 Exception Handling
(1) Pre-defined exceptions
(2) Non-pre-defined exceptions
(3) Custom exceptions
Example 1
sql>DECLARE V_name Emp.ename%type;
Begin
Select Ename to 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 number! ‘);
End
Pre-defined Exceptions
A case_not_found
The case_not_found exception is triggered when a case statement is written that does not contain the required conditional branch in the When clause (no qualifying)
b Cursor_already_open
This exception is triggered when a cursor that has already been opened is reopened
C Dup_val_on_index
This exception is triggered when duplicate values are inserted on a column corresponding to a unique index
D Invalid_cursor
This exception is triggered when an attempt is made to operate on an illegal cursor
E Invalid_number
This exception is triggered when the number entered is not valid
F too_many_rows
This exception is triggered when the return value is more than a single record
G Zero_divide
This exception is triggered when a x/0 is performed, that is, the divisor is zero operation
H Value_error
This exception is triggered when an assignment operation occurs when the length of the variable is not sufficient to store the actual data
I login--denide
This exception is triggered when a user logs on illegally
J not_logged_on
This exception is triggered if the user performs a DML operation without logging on
K Storage_Error
This exception is triggered if the memory space is exceeded
L Timeout_on_resource
This exception is triggered when an Oracle waits for a resource, if a timeout condition occurs
Custom exceptions
sql>Create or Replace procedure procedureName2 (sp_empno number) is
Myexpception Exceptiom; --Customizing an exception
Begin
Update emp Set sal = Sal * 1.2 where empno = &no;
If Sql%notfound Then
Raise Myexpception; --Trigger a custom exception
End If;
exception
When No_data_found
Then Dbms_output.put_line (' No updated data! ‘);
End
