The seventh day of oracle learning and the seventh day of oracle Learning

The seventh day of oracle learning and the seventh day of oracle Learning
Zookeeper

1. Review in the previous section
2. Advanced PL/SQL
3. oracle View
4. oracle triggers
Objectives:
1. Master advanced PL/SQL usage (the paging Process Module and the order process module can be compiled ...)
2. Common oracle exceptions will be handled
3. Various oracle triggers will be compiled
4. understand the concept of a view and use it flexibly

Any computer language has various control statements, and such a control structure exists in PL/SQL.

After learning this part, we hope you can:
1) use various if
2) use the Loop Structure
3) use the control statements goto and null;

Condition branch statement:
If then end if

If then else end if

If then elsif else end if

Simple Condition judgment:
Case: Write a process where you can enter an employee name if the employee's salary is lower
2000 to increase the employee's salary by 10%

Create or replace procedure sp_pro6 (spName varchar2) is
-- Definition
V_sal emp. sal % type;
Begin
-- Execute
Select sal into v_sal from emp where ename = spName;
-- Judgment
If v_sal <2000 then
Update emp set sal = sal + sal * 10 where ename = spName;
End if;
End;
/

Call:
Exec sp_pro6 ('Scott ');

Dual-condition branch: if-then-else
Write a process, you can enter an employee name, if the employee's subsidy is not 0, then the salary is added to 100
Create or replace procedure sp_pro6 (spName varchar2) is
-- Definition
V_comm emp. comm % type;
Begin
-- Execute
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;
/

-- Add different salaries to employees in different positions

Create or replace procedure sp_pro6 (spNo number) is
V_job emp. job % type;
Begin
Select job into v_job from emp where empno = spNo;
If v_job = 'President 'then
Update...
Elsif
Update...
Else
Update...
End if;
End;

Loop statement-loop
The simplest loop statement in PL/SQL is the loop statement.

-- During the compilation process, you can enter the user name and add 10 users to the user table cyclically,
User number increased from 1

Create or replace procedure sp_pro6 (spName varchar2) is
V_num number: = 1;
Begin
Loop
Insert into users values (v_num, spName );
-- Determine whether to exit the loop
Exit then v_num = 10; -- if it is equal to 10, exit the loop.
-- Auto-Increment
V_num: = v_num + 1;
End loop;
End;

Exec sp_pro6 ('hello ');

Loop statement: while LOOP
-- During the writing process, you can enter the user name and add 10 users to the users table cyclically,
User number increased from 11
While v_num <= 20 loop
Insert into user values (v_num, spName );
V_num: = v_num + 1;
End loop;
End;

Loop statement: for Loop
The basic structure of the for Loop is as follows:
Begin
For I in reverse 1 .. 10 loop
Insert into user values (I, 'shanghai ')
End loop;
End;

Goto statement and null statement
Goto end_loop;

<End_loop>

Goto case:
Declare
I int: = 1;
Begin
Loop
Dbms_output.put_line ('output I = '| I)
If I = 10 then
Goto end_loop;
End if;
I: = I + 1;
End loop;
End;
/

If
...
Else
Null; -- indicates that nothing is done
 
Paging process:
Stored Procedure without return values
Create table book (bookId number, bookName varchar2 (50), publishHouse varchar2 (50 ));

-- Writing process:
-- In indicates that this is an input parameter. The default value is in.
-- Out: indicates an output parameter.
Create or replace sp_pro7 (spBookId in number, spbookName in varchar2, sppublishHouse in varchar2) is

Begin
Insert into book values (spBookId, spbookName, sppublishHouse );
End;

Compile a java program to call a process without returning values
// 1. Load the driver
Class. forName ("oracle. jdbc. driver. OracleDriver ");
Connection ct = DriverManager. getConnection ("jdbc: oracle: hin@127.0.0.1: 1521", "scott", "tiger ");

// 2. Create CallableStatement
CallableStatement cs = ct. prepareCall ("{call sp_pro7 (?,?,?)} ");

// Give? Assignment
Cs. setInt (1, 10 );
Cs. setString (2, "swordsman ");
Cs. setString (3, "People's Publishing House ");

// Execute
Cs.exe cute ();

Write stored procedures with returned values (non-list)
Create or replace procedure sp_pro8
(Spno in number, spName out varchar2, spSal out number, spJob out varchar2) is
Begin
Select ename, sal, job into spName, spSal, spJob from emp where empno = spno;
End;

How does java obtain data with returned stored procedures?

//
CallableStatement cs = ct. prepareCall ("{call sp_pro8 (?,?,?,?)} ");

// Give the first? Assignment
Cs. setInt (1,7788 );
// Give the second one? Assignment
Cs. registerOutParameter (2, oracle. jdbc. OracleTypes. VARCHAR );
Cs. registerOutParameter (3, oracle. jdbc. OracleTypes. DOUBLE );
Cs. registerOutParameter (4, oracle. jdbc. OracleTypes. VARCHAR );
// Execute
Cs.exe cute ();

// Do you need to pay attention to the return value of the partition? Order
String name = cs. getString (2 );
Double sal = cs. getDouble (3 );
String job = cs. getString (4 );

Write a process, enter the Department number, and return information about all employees of the Department. In this case, it is not possible to use a common parameter and you need to use a package. Therefore, the package must be divided into two parts:
(1) create a package as follows:
-- Return result set process
-- Create a package with a cursor defined in the package, type test_cursor
Create or replace package testpackage
Type test_cursor is ref cursor;
End testpackage;

(2) create a stored procedure
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;

Java program call:
// Create CallableStatement
CallableStatement cs = ct. prepareCall ("{call sp_pro9 (?,?)} ");

// Give? Assignment
Cs. SetInt (1, 10 );
Cs. registerOutParameter (2, oracle. jdbc. OracleTypes. CURSOR );

// Execute
Cs.exe cute ();

ResultSet rs = (ResultSet) cs. getObject (2 );
While (rs. next ())
{
System. out. println (rs. getInt (1) + "" + ra. getString (2 ));
}

Compile paging Process
Input Table Name, number of records displayed per page, current page, total number of returned records, total number of pages
-- Oracle paging:
Select t1. *, rownum rn from (select * from emp) t1 where rownum <= 10;

-- When paging, you can use the following SQL statement as a template
Select * from (select t1. *, rownum rn from (select * from emp order by sal) t1 where rownum <= 10) where rn> = 6;

-- Develop a package
Create or replace package testpackage
Type test_cursor is ref cursor;
End testpackage;

-- Start the paging Process
Create or replace procedure fenye (tableName in varchar2,
Pagesize in number, -- number of records displayed on a page
PageNow in number, -- page number
Myrows out number, -- total number of records
MyPageCount out number, -- total number of pages
P_cursor out testpackage. test_cursor) is
-- Definition
-- Define the SQL statement string
V_ SQL varchar2 (1024 );
-- Define two integers
V_begin number: = (pageNow-1) * pagesize + 1;
V_end number: = pageNow * pagesize;
Begin
-- Execution part
V_ SQL: = 'select * from (select t1. *, rownum rn from (select * from' | tbaleName | ') t1 where rownum <=' | v_end | ') where rn> = '| v_begin;
-- Associate a cursor with an SQL statement
Open p_cursor for v_ SQL;
-- Calculate myrows and myPageCount
-- Organize an SQL statement
V_ SQL: = 'select count (*) from' | tableName;
-- Execute the SQL statement and assign the returned value to myrows.
Execute immediate v_ SQL into myrows;
-- Calculate myPageCount
If mod (myrows, Pagesize) = 0 then
MyPageCount: = myrows/Pagesize;
Else
MyPageCount: = myrows/Pagesize + 1
End if;
-- Close the cursor
-- Close p_cursor;
End;
/

Java program to verify the correctness of the display in the paging Process
// Test page

// Load the driver
Class. forName ("oracle. jdbc. driver. OracleDriver ");
Connection ct = DriverManager. getConnection ("...");

CallableStatement cs = ct. prepareCall ("{call fenye (?,?,?,?,?,?)} ");

Cs. setString (1, "emp ");
Cs. setInt (2, 5 );
Cs. setInt (3, 1 );

Cs. registerOutParameter (4, 127e. jdbc. OracleTypes. INTEGER );

Cs. registerOutParameter (5, oracle. jdbc. OrcleTYpes. INTEGER );

Cs. registerOutParameter (5, oracle. jdbc. OrcleTYpes. CURSOR );

Cs.exe cute ();

// Get the total number of records/Note that getInt (4), 4 of which is determined by the location of this parameter

Int rowNum = cs. getInt (4 );
Int pageCount = cs. getINt (5 );
ResultSet rs = (ResultSet) cs. getObject (6 );

While (rs. next ())
{
...
}

-- New requirements are sorted by salary from low to high

Advanced PL/SQL-Exception Handling
Exception category
Exception Transfer
-- Exception cases
Write a block:
Declare
-- Definition
V_ename emp. ename % type;
Begin
--
Select ename into v_name from emp where empno = & gno;
Dbms. output. put_line (v_ename );
Exception
When no_data_found then
Dbms. output. put_line ('no number ');
End;

Handle pre-defined exceptions:
PL/SQL provides 20 predefined exceptions:

Case_no_found

Case when... end case

Dup_val_on_index
This exception is triggered when an operation is attempted on an invalid cursor.
For example, if you try to extract data from a non-open cursor or close a non-open cursor
Trigger this exception

Invalid_number
This exception is triggered when the input data is incorrect.

For example:

Too_many_rows
When the select into statement is executed, if more than one row is returned, this exception is triggered.
Zero_divide
Value_error
When assigning values, if the variable length is insufficient to accommodate actual data
Process custom exceptions
Both pre-defined and custom exceptions are related to oracle errors, and

-- Custom exceptions
Create or replace procedure ex_test (spNo number)
Is
-- Define an exception
Myex exception;
Begin
-- Update user sal
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;
/

Exec ex_test (56 );
Oracle View
Introduction:
A view is a virtual table whose content is defined by a query. Like a real table, a view contains a series of Columns with names.
And row data. However, a view does not exist in the database as a data value set.
 
For example, two emp tables and dept tables
1. If you want to display the names of employees and their departments, you must use two tables?
2. Assume that the Administrator creates a user and now only wants this user to query the employees of sal <1000?
 
Differences between views and tables:
1. The table needs to occupy disk space, and the view does not need
2. The view does not have an index and the table has an index. Therefore, the View query speed is slower than that of the table.
3. view can simplify complex queries
4. view helps improve security
Create View:
-- Map employees of sal <1000 in the emp table to this view)
Create view myView as select * from emp where sal <1000;
-- Once a view is created, it can be used as a normal table.
-- To simplify the operation, use a view to display the employee ID, name, and department name, which is a readable view.
Create view myView1 as select emp. empno, emp. ename, dept. dname from emp, dept where emp. deptno = dept. deptno with read only;
 
Note: complex Union queries can be performed between views.
 
Modify View:
 
Delete View: