I. PL/SQL advanced-control structure
Three conditional branching statements are available in PL/SQL if-then, if--then---else, if---then---elsif---else
(1) Simple condition judgment if–then
Issue: Write a procedure that allows you to enter an employee's name and, if the employee's salary is less than 2000, increase the employee's salary by 10%.
SQL code
1. Create or Replace procedure Sp_pro6 (spname varchar2) is
2.--Definition
3. V_sal Emp.sal%type;
4. Begin
5.--Execution
6. Select Sal into V_sal from EMP where ename=spname;
7.--Judgment
8. If v_sal<2000 Then
9. Update EMP set sal=sal+sal*10% where Ename=spname;
Ten. End If;
End;
(2) Double condition branch If-then--else
Problem: Write a process that can enter an employee name, if the employee's subsidy is not 0 on the original basis to increase 100, if the subsidy is 0, set the subsidy to 200;
SQL code
1. Create or Replace procedure Sp_pro6 (spname varchar2) is
2.--Definition
3. V_comm Emp.comm%type;
4. Begin
5.--Execution
6. Select Comm to V_comm from EMP where ename=spname;
7.--Judgment
8. If V_comm<>0 Then
9. Update EMP set comm=comm+100 where Ename=spname;
Ten. Else
Update emp set comm=comm+200 where Ename=spname;
. End If;
End;
(3) Multiple conditional branching if--then--elsif--else
Issue: Write a procedure that allows you to enter an employee number, and if the employee's position is president, increase his salary by 1000, and if the employee's position is manager, increase his salary by 500, and the employee's salary in other positions increases by 200.
SQL code
1. Create or Replace procedure Sp_pro6 (spno number) is
2.--Definition
3. V_job Emp.job%type;
4. Begin
5.--Execution
6. Select job into V_job from EMP where empno=spno;
7. If v_job= ' president ' then
8. Update EMP set sal=sal+1000 where Empno=spno;
9. Elsif v_job= ' MANAGER ' then
Update emp set sal=sal+500 where Empno=spno;
One. Else
Update emp set sal=sal+200 where Empno=spno;
End If;
. end;
Second, circular statement –while cycle
The basic loop executes at least once for the loop body, whereas for a while loop, the loop body statement executes only if the condition is true, and the while loop starts with While...loop and ends with end loop.
Issue: Write a procedure that allows you to enter a user name and iterate through the addition of 10 users to the users table, with the user number increasing from 11.
SQL code
1. Create or Replace procedure Sp_pro6 (spname varchar2) is
2.--Definition: = indicates assignment
3. V_num number:=11;
4. Begin
5. While v_num<=20 loop
6.--Execution
7. Insert INTO users values (V_num,spname);
8. v_num:=v_num+1;
9. End Loop;
Ten. End;
Third, sequential control statement-goto, NULL
①goto statements
The goto statement is used to jump to a specific label to execute a statement. Note Because the use of GOTO statements increases the complexity of the program and makes the application unreadable, it is recommended that you do not use a goto statement when you do general application development.
The basic syntax is goto lable, where lable is a label name that is already defined.
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_loop>>
Dbms_output.put_line (' End of cycle ');
End
Note:--in: Indicates that this is an input parameter, which defaults to in
--out: Represents an output parameter
Iv. Oracle View
Create a View
CREATE VIEW name as SELECT statement [with Read Only]
Create or modify a view
Create or Replace view view name as SELECT statement [with Read Only]
Delete a view
Drop View Name
V. PL/SQL advanced-Exception handling
Exception
When name of exception >then
Exception handling Code >
When name of exception >then
Exception handling Code >
......
When others then
Exception handling Code >
Exception name error code description
No_data_found ORA-01403 does not return any values for the select narrative.
Too_many_rows ORA-01427 only allows a single record to be returned with a select narrative with more than one result.
Invalid_cursor ORA-01001 uses an illegal cursor operation.
Value_error ORA-06502 A numeric, data morphological conversion, string retrieval, or mandatory error.
Invalid_number ORA-01722 string-to-numeric conversion failed.
Zero_divide ORA-01476 was removed by 0.
Dup_val_on_index ORA-00001 attempts to insert a duplicate key value into an index that has a unique key value.
Case_not_found ora-xxxxx no case condition match
Cursor_not_open ora-xxxxxx Cursor Not open
Various cases:
1. Write a procedure that adds a book to the books table and requires that the procedure be called through a Java program.
--writing process
SQL code
Create or Replace procedure Sp_pro7 (Spbookid in Number,spbookname in Varchar2,sppublishhouse on VARCHAR2) is
Begin
Insert into book values (Spbookid,spbookname,sppublishhouse);
End
--Called in Java
Package Com.oracle.demo;import Java.sql.callablestatement;import Java.sql.connection;import Java.sql.DriverManager ;p Ublic class Procedure_07_test {public static void main (string[] args) {try {class.forname (" Oracle.jdbc.driver.OracleDriver "); Connection conn = Drivermanager.getconnection ("Jdbc:oracle:thin: @localhost: 1521:orcl", "Scott", "0108"); CallableStatement cs = Conn.preparecall ("{Call Sp_pro7 (?,?,?)}"); To Assignment Cs.setint (1), cs.setstring (2, "Tianlong Eight"), Cs.setstring (3, "Tsinghua University Press"),//Execution Cs.execute (); Cs.close (); Conn.close ();} catch (Exception e) {e.printstacktrace ();}}}
2. Case: Write a procedure that can enter the employee's number and return the employee's name.
Case expansion: Write a procedure that allows you to enter the employee's number and return the employee's name, salary, and position.
SQL code
--Stored procedures with input and output
Create or replace procedure Sp_pro8
(Spno in number, spname out VARCHAR2) is
Begin
Select Ename to Spname from EMP where empno=spno;
End
--Called in Java
Package Com.oracle.demo;import Java.sql.callablestatement;import Java.sql.connection;import Java.sql.DriverManager ;p Ublic class Procedure_08_test {public static void main (string[] args) {try {class.forname (" Oracle.jdbc.driver.OracleDriver "); Connection conn = Drivermanager.getconnection ("Jdbc:oracle:thin: @localhost: 1521:orcl", "Scott", "0108"); CallableStatement cs = Conn.preparecall ("{Call Sp_pro8 (?,?)}"); /To the first one? Assignment Cs.setint (1, 7788);//give the second one? Assignment Cs.registeroutparameter (2, Oracle.jdbc.OracleTypes.VARCHAR);//execute cs.execute ();//Take out the return value. The order of String name = Cs.getstring (2); SYSTEM.OUT.PRINTLN (name); Cs.close (); Conn.close ();} catch (Exception e) {e.printstacktrace ();}}}
3. Case: A stored procedure with a return value (list [result set]). Write a procedure, enter the department number, and return all employee information for that department.
Because the Oracle stored procedure does not return a value, all its return values are replaced by out parameters, and the list is no exception.
But because it is a collection, so can not use the general parameters, must use Pagkage, the steps are as follows:
① build a package.
② establish a stored procedure.
③ below to see how to call in a Java program
1. Create a package in which I define the type Test_cursor, which is a cursor. As follows:
SQL code
Create or replace package testpackage as
TYPE test_cursor is REF CURSOR;
End Testpackage;
2. Create a stored procedure. As follows:
SQL code
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 Sp_pro9;
3. How to call this procedure in a Java program
Package Com.oracle.demo;import Java.sql.callablestatement;import Java.sql.connection;import Java.sql.DriverManager Import Java.sql.resultset;public class Procedure_09_test {public static void main (string[] args) {try {class.forname (" Oracle.jdbc.driver.OracleDriver "); Connection ct = drivermanager.getconnection ("Jdbc:oracle:thin: @localhost: 1521:orcl", "Scott", "0108"); CallableStatement cs = Ct.preparecall ("{Call Sp_pro9 (?,?)}"); Cs.setint (1); Cs.registeroutparameter (2, Oracle.jdbc.OracleTypes.CURSOR); Cs.execute (); ResultSet set = (ResultSet) cs.getobject (2), while (Set.next ()) {System.out.println (Set.getint (1) + "" + set.getstring (2) );} Cs.close (); Ct.close ();} catch (Exception e) {e.printstacktrace ();}}}
4. Writing the paging process
SQL code
Select t1.*, RowNum rn from (SELECT * from emp) t1 where rownum<=10;
SELECT * FROM (select t1.*, rownum rn from (SELECT * from emp) t1 where rownum<=10) where rn>=6;
--Develop a package
--Create a package in which I define the type Test_cursor, which is a cursor. As follows:
SQL code
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,--a page of records
Pagenow in number,
Myrows out number,--total records
Mypagecount out number,--total pages
P_cursor out testpackage.test_cursor--the recordset returned
) is
--Definition section
--Define the SQL statement string
V_sql VARCHAR2 (1000);
--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 ' | | tablename| | ') 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 statement
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
--Using Java Testing
Package Com.oracle.demo;import Java.sql.callablestatement;import Java.sql.connection;import Java.sql.DriverManager Import Java.sql.resultset;public class Procedure_fenye_test {public static void main (string[] args) {try {Class.forName ("Oracle.jdbc.driver.OracleDriver"); Connection ct = drivermanager.getconnection ("Jdbc:oracle:thin: @localhost: 1521:orcl", "Scott", "0108"); CallableStatement cs = Ct.preparecall ("{Call Fenye (?,?,?,?,?,?)}"); Cs.setstring (1, "EMP"); Cs.setint (2, 5); Cs.setint (3, 2);//Total number of registered records Cs.registeroutparameter (4, Oracle.jdbc.OracleTypes.INTEGER)///Registered Total pages Cs.registeroutparameter (5, Oracle.jdbc.OracleTypes.INTEGER);// Register the returned result set Cs.registeroutparameter (6, Oracle.jdbc.OracleTypes.CURSOR); Cs.execute (); Total number of records taken out/note here, GetInt (4) in 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); System.out.println ("RowNum:" + rowNum); System.out.println ("PageCount:" + PageCount), while (Rs.next ()) {System.out.println ("Number:" +rs.getint (1) + "First Name:" +rS.getstring (2) + "Salary:" +rs.getfloat (6)); }cs.cancel (); Ct.close ();} catch (Exception e) {e.printstacktrace ();}}}
Console output:
Rownum:14
Pagecount:3
Item No: 7,698 name: BLAKE Salary: 2850.0
Item No: 7,782 name: CLARK Salary: 2450.0
Item No: 7,788 name: SCOTT Salary: 4678.0
Item No: 7,839 name: KING Salary: 6000.0
Item No: 7,844 name: TURNER Salary: 1500.0
Note here that the cursor cannot be closed while the procedure is being created, otherwise the Java test is reporting an exception and the cursor cannot be found. So, close p_cursor; this sentence should be commented out when executing the SQL statement.
-New needs, ordered according to salary from low to high, and then removed 6-10
The execution part of the process is changed as follows:
SQL code
Begin
--Executive Section
V_sql:= ' SELECT * FROM (select t1.*, rownum rn from (SELECT * from ' | | tablename| | ' ORDER by sal ' T1 where rownum<= ' | | v_end| | ') where rn>= ' | | V_begin;
Re-execute the Procedure,java without changing, running, console output:
Rownum:14
Pagecount:3
Item No: 7,934 name: MILLER Salary: 1300.0
Item No: 7,844 name: TURNER Salary: 1500.0
Item No: 7,499 name: ALLEN Salary: 1600.0
Item No: 7,782 name: CLARK Salary: 2450.0
Item No: 7,698 name: BLAKE Salary: 2850.0
(Hanshunping explanation) PL/SQL Programming (II)
