(Han shunping) pl/SQL programming (2), shunping pl

(Han shunping) pl/SQL programming (2), shunping pl
I. pl/SQL advanced-control structure
Pl/SQL provides three conditional branch statements: if -- then, if -- then --- else, if --- then --- elsif --- else
(1) simple condition judgment if-then
Question: When writing a process, you can enter an employee name. If the employee's salary is lower than 2000, the employee's salary will be increased 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. -- execute
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;
10. end if;
11. end;

(2) if-then -- else
Question: When writing a process, you can enter an employee name. If the employee's subsidy is not 0, it will increase by 100 based on the original one; if the subsidy is 0, it will 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. -- execute
6. select comm into 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;
10. else
11. update emp set comm = comm + 200 where ename = spName;
12. end if;
13. end;

(3) Multi-condition branch if -- then -- elsif -- else
Question: When writing a process, you can enter an employee number. If the employee's position is PRESIDENT, the employee's salary will be increased by 1000, if the employee is a MANAGER, his salary will be increased by 500, and the salary of other employees will be increased 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. -- execute
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
10. update emp set sal = sal + 500 where empno = spNo;
11. else
12. update emp set sal = sal + 200 where empno = spNo;
13. end if;
14. end;

Ii. Loop statement-while loop commit
The basic loop must be executed at least once. For a while loop, the loop body statement is executed only when the condition is true... start with loop and end with end loop.
Question: Please write a process. You can enter the user name and add 10 users to the users table cyclically. The user number starts from 11.
SQL code
1. create or replace procedure sp_pro6 (spName varchar2) is
2. -- Definition: = indicates a value assignment.
3. v_num number: = 11;
4. begin
5. while v_num <= 20 loop
6. -- execute
7. insert into users values (v_num, spName );
8. v_num: = v_num + 1;
9. end loop;
10. end;

Iii. Sequential Control statement-goto, null
① Goto statement
The goto statement is used to jump to a specific number to execute the statement. note that the use of the goto statement increases the complexity of the program and reduces the readability of the application. Therefore, we recommend that you do not use the goto statement for general application development.
The basic syntax is as follows: goto lable, where lable is the defined label name.
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 ('loop termination ');
End;
Note: -- in: indicates that this is an input parameter. The default value is in.
-- Out: indicates an output parameter.

Iv. oracle View
Create View
Create view name as select statement [with read only]
Create or modify a view
Create or replace view name as select statement [with read only]
Delete View
Drop view name

V. Advanced pl/SQL-Exception Handling
Exception
When <abnormal name> then
<Exception handling code>
When <abnormal name> then
<Exception handling code>
......
When others then
<Exception handling code>
Exception name Error Code Description
The NO_DATA_FOUND ORA-01403 does not return any value for the SELECT statement.
The TOO_MANY_ROWS ORA-01427 only allows the return of more than one SELECT statement of a record.
The INVALID_CURSOR ORA-01001 uses an invalid cursor operation.
The VALUE_ERROR ORA-06502 has a numeric, data form conversion, string retrieval, or mandatory error.
An error occurred while converting the INVALID_NUMBER ORA-01722 string to a numeric value.
ZERO_DIVIDE ORA-01476 is divided by zero.
The DUP_VAL_ON_INDEX ORA-00001 attempts to insert a duplicate key value to an index with a unique key value.
CASE_NOT_FOUND ORA-xxxxx does not have the case condition match

CURSOR_NOT_OPEN ORA-xxxxxx cursor not opened

Various cases:
1. Please write a process. You can add a book to the book table. You must call this process using a java program.
-- Writing Process
SQL code
Create or replace procedure sp_pro7 (spBookId in number, spbookName in varchar2, sppublishHouse in varchar2) is
Begin
Insert into book values (spBookId, spbookName, sppublishHouse );
End;
-- Call in java

package com.oracle.demo;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

public class Procedure_07_Test {
The
public static void main (String [] args) {
try {
The
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 (?,?,?)}");
The
//give? Assignment
cs.setInt (1, 10);
cs.setString (2, "Tianlong Babu");
cs.setString (3, "Tsinghua University Press");
The
//carried out
cs.execute ();
The
cs.close ();
conn.close ();
The
} catch (Exception e) {
e.printStackTrace ();
}
}
}
2. Case: Write a process where you can enter the employee's number and return the employee's name.
Case expansion: Write a process where you can enter the employee's number and return the employee's name, salary, and position.
Sql code
-There are input and output stored procedures
create or replace procedure sp_pro8
(spno in number, spName out varchar2) is
begin
select ename into spName from emp where empno = spno;
end
--Call in java
package com.oracle.demo;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

public class Procedure_08_Test {
The
public static void main (String [] args) {
The
try {
The
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 (?,?)}");
The
// For the first one? Assignment
cs.setInt (1, 7788);
// For the second one? Assignment
cs.registerOutParameter (2, oracle.jdbc.OracleTypes.VARCHAR);
//carried out   
cs.execute ();
The
// Retrieve the return value, pay attention? order of
String name = cs.getString (2);
System.out.println (name);

cs.close ();
conn.close ();
The
} catch (Exception e) {
e.printStackTrace ();
}
}
}
3. Case: a stored procedure with a return value (list [result set]). Write a process, enter the department number, and return all employee information of the department.
Since the Oracle stored procedure has no return value, all its return values are replaced by out parameters, and the list is no exception.
But because it is a collection, you can not use general parameters, you must use pagkage, the steps are as follows:
① Build a package.
② Establish a stored procedure.
③Let's see how to call it in 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. Establish 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 the process in the 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 {
The
public static void main (String [] args) {
The
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 (?,?)}");
The
cs.setInt (1, 10);
cs.registerOutParameter (2, oracle.jdbc.OracleTypes.CURSOR);
The
cs.execute ();
The
ResultSet set = (ResultSet) cs.getObject (2);
while (set.next ()) {
System.out.println (set.getInt (1) + "" + set.getString (2));
The
}
The
cs.close ();
ct.close ();
The
} catch (Exception e) {
e.printStackTrace ();
}
}
}
4. Write 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 this package, I define the type test_cursor, is a cursor. as follows:
Sql code
create or replace package testpackage as
TYPE test_cursor is ref cursor;
end testpackage;
--Start the process of writing paging
create or replace procedure fenye
(tableName in varchar2,
pagesize in number,-One page shows the number of records
pageNow in number,
myrows out number,-total number of records
myPageCount out number,-total number of pages
p_cursor out testpackage.test_cursor--returned recordset
) is
--Define part
--Define the SQL statement string
v_sql varchar2 (1000);
--Define two integers
v_begin number: = (pageNow-1) * pagesize + 1;
v_end number: = pageNow * pagesize;
begin
--Execution
v_sql: = 'select * from (select t1. *, rownum rn from (select * from' || tableName || ') t1 where rownum <=' || v_end || ') where rn> =' || v_begin;
-Associate the cursor with sql
open p_cursor for v_sql;
-Calculate myrows and myPageCount
--Organize a sql statement
v_sql: = 'select count (*) from' || tableName;
--Execute sql 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;
--Use java test
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 {
The
public static void main (String [] args) {
The
try {
The
Class.forName ("oracle.jdbc.driver.OracleDriver");
Connection ct = DriverManager.getConnection ("jdbc: oracle: thin: @localhost: 1521: orcl",
"scott", "0108");
CallableStatement cs = ct.prepareCall ("{call fenye (?,?,?,?,?,?)}");
The
cs.setString (1, "emp");
cs.setInt (2, 5);
cs.setInt (3, 2);
// Total number of registration records
cs.registerOutParameter (4, oracle.jdbc.OracleTypes.INTEGER);
// Total number of registration pages
cs.registerOutParameter (5, oracle.jdbc.OracleTypes.INTEGER);
// Register the returned result set
cs.registerOutParameter (6, oracle.jdbc.OracleTypes.CURSOR);
The
cs.execute ();
The
// Remove the total number of records / Note here, getInt (
4) Medium 4 is determined by the position of this parameter
int rowNum = cs.getInt (4);
int pageCount = cs.getInt (5);
ResultSet rs = (ResultSet) cs.getObject (6);
The
System.out.println ("rowNum:" + rowNum);
System.out.println ("pageCount:" + pageCount);
The
while (rs.next ()) {
System.out.println ("Number:" + rs.getInt (1) + "Name:" + rs.getString (2) + "Salary:" + rs.getFloat (6));
}
The
cs.cancel ();
ct.close ();
The
} catch (Exception e) {
e.printStackTrace ();
}
}
}
Console output:

rowNum: 14
pageCount: 3
Number: 7698 Name: BLAKE Salary: 2850.0
Number: 7782 Name: CLARK Salary: 2450.0
Number: 7788 Name: SCOTT Salary: 4678.0
Number: 7839 Name: KING Salary: 6000.0
Number: 7844 Name: TURNER Salary: 1500.0
Note here that the cursor cannot be closed during the creation process, otherwise the java test reports an exception, indicating that the cursor cannot be found. Therefore, close p_cursor; this sentence should be commented out when executing the sql statement.
--New needs, ask to be sorted according to salary from low to high, and then take out 6-10
Make changes to the execution part of the process as follows:
Sql code
begin
--Execution
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, without changing java, run, console output:
rowNum: 14
pageCount: 3
Number: 7934 Name: MILLER Salary: 1300.0
Number: 7844 Name: TURNER Salary: 1500.0
Number: 7499 Name: ALLEN Salary: 1600.0
Number: 7782 Name: CLARK Salary: 2450.0
Number: 7698 Name: BLAKE Salary: 2850.0

Two simple questions about PL / SQL programming in oracle ?;
first question
SET SERVEROUT ON
DECLARE
V_FLAG BOOLEAN;
BEGIN
FOR I IN 2 .. 100 LOOP
V_FLAG: = TRUE;
FOR J IN 2 .. I-1 LOOP
IF MOD (I, J) = 0 THEN
V_FLAG: = FALSE;
END IF;
END LOOP;
IF V_FLAG = TRUE THEN
DBMS_OUTPUT.PUT_LINE (I);
END IF;
END LOOP;
END;
/

Second, what do you do if you want to enter February 29, 2008? Not 5 years ago
 
Which version of pl sql in Han Shunping oracle

Check if the oracle instance status is open. If it is not in the open status, such as nomount and mount status, ordinary users will be prompted "Oracle initialization or shutdown process" when connecting