The seventh day of learning to go to Oracle



1. Previous section Review
The advanced of 2.pl/sql
View of 3.oracle
Trigger for 4.oracle
Goal:
1. master PL/SQL Advanced usage (can write paging process module, next order process module ...) ）
2. Common exceptions for Oracle are handled
3. Oracle various triggers are written
4. Understanding the concept of views and the flexibility to use views

Any computer language has a variety of control statements, and there is such a control structure in PL/SQL

After the completion of this part of the study, I hope you reach:
1) Use various if
2) Use loop structure
3) Use control statements Goto and NULL;

Conditional branching statements:
If then End If

If then else End if

If then elsif else End If

Simple conditions to judge:
Case: Write a procedure that can enter an employee name if the employee's salary is less than
2000, increase the employee's salary by 10%

Create or Replace procedure Sp_pro6 (spname varchar2) is
--Definition
V_sal Emp.sal%type;
Begin
-Execution
Select Sal into V_sal from EMP where ename=spname;
--Judgment
If V_sal < Then
Update emp Set sal=sal+sal*10 where ename = spname;
End If;
End
/

Call:
EXEC Sp_pro6 (' Scott ');

Two-condition branch: If-then-else
Write a procedure that can enter an employee name, and if the employee's subsidy is not 0, the salary is added 100
Create or Replace procedure Sp_pro6 (spname varchar2) is
--Definition
V_comm Emp.comm%type;
Begin
-Execution
Select Comm to 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
/

--Write a process that adds different wages 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
Circular statement in PL/SQL, the simplest loop statement is the Loop statement

--Write the process, you can enter a user name, and loop add 10 users to the user table,
User number increased starting 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 if you want to exit the loop
Exit then v_num=10; --equals 10 to exit the loop.
--Self-increment
V_num: = v_num+1;
End Loop;
End

exec sp_pro6 (' hello ');

Loop statement: While Loop
--Write the process, you can enter a user name, and loop add 10 users to the user table,
User number increased starting from 11
While V_num <= loop
Insert into user values (V_num,spname);
v_num:=v_num+1;
End Loop;
End

Loop statement: For Loop
The basic structure for a For loop is as follows:
Begin
For i in reverse 1.. Ten loops
Insert into user values (I, ' World Sun ')
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;--says he doesn't do anything.

Paging process:
Stored procedure with no return value
CREATE TABLE book (BookId number,bookname varchar2 (a), Publishhouse varchar2 (50));

--Writing process:
--in represents this is an input parameter, which defaults to in
--out: Represents 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

Procedure for writing Java program calls with no return value
1. Load Driver
Class.forName ("Oracle.jdbc.driver.OracleDriver");
Connection ct=drivermanager.getconnection ("Jdbc:oracle:[email protected]:1521", "Scott", "Tiger");

2. Create CallableStatement
CallableStatement cs = Ct.preparecall ("{Call Sp_pro7 (?,?,?)}");

To give? Assign value
Cs.setint (1,10);
Cs.setstring (2, "Laughter and lakes");
Cs.setstring (3, "the publishing house");

Perform
Cs.execute ();

Write a stored procedure with a return value (not a list)
Create or replace procedure Sp_pro8
(Spno in Number,spname out varchar2,spsal out number,spjob off varchar2) is
Begin
Select Ename, Sal, job into Spname, Spsal, spjob from EMP where empno=spno;
End

How Java obtains data that has a stored procedure returned

//
CallableStatement cs = Ct.preparecall ("{Call Sp_pro8 (?,?,?,?)}");

To the first one? Assign value
Cs.setint (1,7788);
To the second one? Assign value
Cs.registeroutparameter (2,oracle.jdbc.oracletypes.varchar);
Cs.registeroutparameter (3,oracle.jdbc.oracletypes.double);
Cs.registeroutparameter (4,oracle.jdbc.oracletypes.varchar);
Perform
Cs.execute ();

The return value of the zone should be noted? The order
String name=cs.getstring (2);
Double Sal =cs.getdouble (3);
String job=cs.getstring (4);

Write a procedure, enter the department number, and return all employee information for that department. At this time with general parameters is not possible, need to use the package, so to be divided into two parts
(1) Create a package, as follows:
--the process of returning a result set
--Create a package with a cursor defined in the package, type Test_cursor
Create or replace package testpackage as
Type test_cursor is REF CURSOR;
End Testpackage;

(2) Creating 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 calls:
Create CallableStatement
CallableStatement Cs=ct.preparecall ("{Call Sp_pro9 (?,?)}");

To give? Assign value
Cs. Setint (1,10);
Cs.registeroutparameter (2,oracle.jdbc.oracletypes.cursor);

Perform
Cs.execute ();

ResultSet rs = (ResultSet) cs.getobject (2);
while (Rs.next ())
{
System.out.println (Rs.getint (1) + "" +ra.getstring (2));
}

Writing a paging process
Enter table name, show number of records per page, current page, return total number of records, total 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 * to emp ORDER by Sal) T1 where rownum<=10) where rn>=6;

--Develop a package
Create or replace package testpackage as
Type test_cursor is REF CURSOR;
End Testpackage;

--Begin the process of writing pagination
Create or Replace procedure Fenye (TableName in Varchar2,
PageSize in number,--how many records are displayed on a page
Pagenow in number,--first page
Myrows out number,--Total records
Mypagecount out number,--total pages
P_cursor out Testpackage.test_cursor) is
--Definition section
--Define the SQL statement string
V_sql VARCHAR2 (1024);
--Definition of two integers
V_begin Number: = (pageNow-1) *pagesize+1;
V_end number: = Pagenow*pagesize;
Begin
--Executive Section
V_sql: = ' select * FROM (select T1.*,rownum rn from (SELECT * from ' | | Tbalename | | ') T1 where rownum<= ' | | V_end | | ') where rn>= ' | | V_begin;
--Associating cursors with SQL
Open p_cursor for V_sql;
--Calculate myrows and Mypagecount
--Organize an SQL
V_sql: = ' SELECT count (*) from ' | | TableName;
--Executes the SQL and assigns the returned value to the Myrows
Execute immediate v_sql into myrows;
--Calculation Mypagecount
If mod (myrows,pagesize) =0 Then
Mypagecount:=myrows/pagesize;
Else
Mypagecount:=myrows/pagesize+1
End If;
--Close cursor
--close P_cursor;
End
/

Java program to verify the correctness of the paging process display
Test paging

Load 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,orace.jdbc.oracletypes.integer);

Cs.registeroutparameter (5,oracle.jdbc.orcletypes.integer);

Cs.registeroutparameter (5,oracle.jdbc.orcletypes.cursor);

Cs.execute ();

Get Total Records/note here that GetInt (4), where 4, is determined by the position of the parameter

int rowNum = Cs.getint (4);
int pagecount = Cs.getint (5);
ResultSet rs = (ResultSet) cs.getobject (6);

while (Rs.next ())
{
...
}

-New requirements, sorted by salary from low to high

Advanced--Exception handling for PL/SQL
Classification of exceptions
Exception Pass
--Exception cases
Write a block:
Declare
--Definition
V_ename Emp.ename%type;
Begin
--
Select Ename to 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

To process a predefined exception:
PL/SQL provides 20 pre-defined exceptions:

Case_no_found

Case when ... when ... end case

Dup_val_on_index
This exception is triggered when an operation is attempted on an illegal cursor
For example, attempting to extract data from a cursor that is not open or to close a cursor that is not open will
Trigger the exception

Invalid_number
This exception is triggered when the input data is incorrect

Like what:

Too_many_rows
When a SELECT INTO statement is executed, the exception is triggered if more than one row is returned
Zero_divide
Value_error
When performing an assignment operation, if the length of the variable is not sufficient to accommodate the actual data
Working with custom exceptions
Pre-defined exceptions and custom exceptions are related to Oracle errors, and

--Custom exception
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 here indicates that there is no update
--raise Myex; trigger 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
Introduced:
A view is a virtual table whose contents are defined by a query, like a real table, that contains a series of columns with names
and rows of data. However, views do not exist in the database as stored data value sets

For example, two-sheet EMP tables and Dept tables
1. If you want to display the names of individual employees and the name of his department, you must use two tables?
2. Suppose the administrator creates a user and now only wants the user to query the sal<1000 employees?

Differences between views and tables:
1. The table requires disk space, and the view does not require
2. The view is not indexed and the table is indexed, so the view query is slower than the table
3. Use views to simplify complex queries
4. Use views for improved security
To create a view:
--Mapping the sal<1000 employees of the EMP table to the view
CREATE VIEW MyView as SELECT * from EMP where sal<1000;
--Once the view is created, it can be used as a normal table.
--To simplify operations, display employee numbers, names, and department names in a single view, and be readable views
CREATE View MyView1 as select Emp.empno,emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno with Read only;

Note: Complex federated queries can be made between views and views

To modify a view:

To delete a view:

The seventh day of learning to go to Oracle