Oracle Learning Notes (12)

13. Stored Procedures and storage functions
1, master the stored procedure (equivalent to establish a function or method body, and then call it externally)
A subroutine stored in a database that is called by all programs is called a stored procedure or stored function.

Same point:
Programs that complete specific functions
Difference:
Whether to return a value with a return statement

(1) Creating and using stored procedures
Creating stored procedures and stored functions with the CREATE PROCEDURE command

Grammar:
Create or Replace procedure procedure name (parameter list) as PL/SQL subroutine body (description section);

Case:
(a) Print a stored procedure: print HelloWorld (no parameters)
Create or replace procedure SayHelloWorld
As
--Description section
Begin
Dbms_output.put_line (' Hello world!!! ');
End
/
SayHelloWorld in the process of the compiler to the left

Call on it:
Method One:
Execute shorthand for exec sayhelloworld ();
Method Two:
Begin
SayHelloWorld ();
SayHelloWorld ();
End
/

(b) Stored procedures with parameters
Example: For a designated employee, up to 100 dollars in wages, and print up before and after the salary
Idea: 1. Create a stored procedure with parameters
2. Raise the salary of 100 yuan for the appointed employee, and print the salary before and after the rise
Create or Replace procedure raisesalary (Eno in number)
As
--Define a variable save the salary of the rising money
Psal Emp.sal%type;
Begin
-Get the employee's salary before the rise
Select Sal into Psal from EMP where Empno=eno;

-100 Increase for employees
Update emp set sal=sal+100 where Empno=eno;
Dbms_output.put_line (' Pre-rise Salary: ' | | psal| | ' Salary after rise: ' | | (psal+100));

-Do you need a commit?
-Note: Generally not in stored procedures or stored functions, commit and rollback. The principle is not absolutely
End
/

How to Invoke:
Begin
Raisesalary (7839);
Raisesalary (7566);
Commit
End

How to Debug
Note: Remote debugging is not recommended and recommended for local debugging
Procedure--right---Compile for debugging

Set breakpoints
Select Sal into Psal from EMP where Empno=eno;
Click the Ladybug Debug
Change value ENO: = 7839; Click OK
Authorized
System Authentication Login: Sqlplus/as SYSDBA
Grant Debug CONNECT SESSION, debug any PROCEDURE to Scott;
Right-click Detection psal
Debugging
Click F8

2. Mastering the storage function
The function is a named stored program that can take parameters and return a computed value.
Functions and procedures are similar in structure, but must have a return clause that returns the value of the function.

(1) syntax for creating stored functions
Create or Replace function name (argument list)
return function value type
As
PL/SQL sub-program body;

Case: Querying the annual income of an employee
Create or Replace function Queryempincome (Eno in number)
return number
As
--Define variables to save employees ' salaries and bonuses
Psal Emp.sal%type;
Pcomm Emp.comm%type;
Begin
-Get the employee's monthly salary and bonus
Select Sal,comm to Psal,pcomm from EMP where Empno=eno;
-Direct return on annual income
Return PSAL*12+NVL (pcomm,0);
End
/

Set Linesize 200
SELECT * from EMP;

Calling a stored function

(2) in and Out parameters
In general, the difference between a stored procedure and a stored function is that the stored function can have a return value, and the stored procedure does not return a value.

Both stored procedures and stored functions can have out parameters
stored procedures and stored functions can have multiple out parameters
Stored procedures can implement return values through out parameters

When do I use a stored procedure/storage function?
Principle:
--if there is only one return value, use a stored function, otherwise, use the stored procedure.

----out parameters, query for an employee's name, salary, and position
Create or Replace procedure Queryempinform (Eno in number, pename out varchar2,psal off number,pjob out varchar2)
As
Begin
--Get the employee's name, salary and position
Select Ename,sal,empjob to Pename,psal,pjob from EMP where Empno=eno;
End
/

Thinking: (1) Querying all the information of an employee---->out parameters too much
Set Linesize 80
DESC EMP
(2) Querying all information about all employees in a department---return a collection in >out

(3) Accessing stored procedures and stored functions in the application
(a) access to stored procedures
public class Jdbcutils {
private static String URL = "Jdbc:oracle:thin: @localhost: 1521:ORCL";
private static String username = "Scott";
private static String password = "Tiger";
private static Connection Conn;

public static Connection getconnection () {
conn = null;
try {
conn = drivermanager.getconnection (URL, username, password);
} catch (Exception e) {
E.printstacktrace ();
throw new RuntimeException ("Oracle Connection Acquisition failed");
}
Return conn;
}

Close the Connection object
public static void Close (Connection conn) {
IF (conn! = null) {
try {
Conn.close ();
} catch (Exception e) {
E.printstacktrace ();
throw new RuntimeException ("Oracle connection Shutdown failed");
}
}
}

public static void Close (Statement stmt) {
if (stmt! = null) {
try {
Stmt.close ();
} catch (Exception e) {
E.printstacktrace ();
throw new RuntimeException ("Oracle connection Shutdown failed");
}
}
}

public static void Close (ResultSet rs) {
if (rs! = null) {
try {
Rs.close ();
} catch (Exception e) {
E.printstacktrace ();
throw new RuntimeException ("Oracle connection Shutdown failed");
}
}
}
}

public class TestProcedure {

@Test
public void TestProcedure () {
String sql = "{call Queryempinform (?,?,?,?)}";
Connection conn = null;
CallableStatement call = null;

try {
Get a connection
conn = Jdbcutils.getconnection ();
Create a statement from a connection
Call = Conn.preparecall (SQL);

For the in parameter, assign the value
Call.setint (1, 7839);
For out parameters, declare
Call.registeroutparameter (2, Oracletypes.varchar);
Call.registeroutparameter (3, Oracletypes.number);
Call.registeroutparameter (4, Oracletypes.varchar);

Execute call
Call.execute ();

Remove results
String name =call.getstring (2);
Double Sal =call.getdouble (3);
String Job =call.getstring (4);
System.out.println (name+ "\ t" +sal+ "\ T" +job);

} catch (Exception e) {
E.printstacktrace ();
}finally{
Jdbcutils.close (conn);
Jdbcutils.close (call);
}
}

}
(b) access to storage functions
public class TestFunction {

@Test
public void TestFunction () {
String sql = "{? =call queryempincome (?)}";
Connection conn = null;
CallableStatement call = null;

try {
Get a connection
conn = Jdbcutils.getconnection ();
Create a statement from a connection
Call = Conn.preparecall (SQL);

For output parameters, declare
Call.registeroutparameter (1, oracletypes.number);

assigning values to parameters
Call.setint (2, 7839);

Execute call
Call.execute ();

Remove results

Double income = call.getdouble (1);

Print result set
SYSTEM.OUT.PRINTLN ("Annual income:" + income);

} catch (Exception e) {
E.printstacktrace ();
} finally {
Jdbcutils.close (conn);
Jdbcutils.close (call);
}
}
}

(4) using the cursor in out parameters

(a) Declaration of the package structure
(b) Baotou
(c) Package body

Case: Querying all the information for all employees in a department

Baotou:
Create or replace package MyPackage as
--type Custom Type Empcursor
Type empcursor is REF CURSOR;
Procedure Queryemplist (DNO in number,emplist out empcursor);
End MyPackage;
The package body needs to implement all the methods of life in Baotou *******

Operation:
Package-----> Right---> New package---> Package name setting mypackage---> Determining---> Compiling--->ctril+s execution

Inclusion
Create or replace package body mypackage as

Procedure Queryemplist (DNO in Number,emplist out Empcursor) as
Begin

Open Emplist for SELECT * from EMP where deptno =dno;
End Queryemplist;
End MyPackage;

Operation:
Select Mypackage--> Right--Create principal---> Compile--->ctril+s execution

To view a package:
Desc MyPackage

Note: You need to bring the package name to access

(5) Accessing stored procedures in the package in the app
public class Testmypackage {
@Test
public void Testmypackage () {
String sql = "{call Mypackage.queryemplist (?,?)}";
Connection conn = null;
CallableStatement call = null;
ResultSet rs = null;

try {
Get a connection
conn = Jdbcutils.getconnection ();
Create a statement from a connection
Call = Conn.preparecall (SQL);

assigning values to parameters
Call.setint (1, 10);

For output parameters, declare
Call.registeroutparameter (2, oracletypes.cursor);

Execute call
Call.execute ();

Remove all employee information from the department

rs = ((oraclecallablestatement) call). GetCursor (2);
while (Rs.next ()) {
The employee's employee number/salary/position
int empno =rs.getint ("empno");
String name =rs.getstring ("ename");
Double salary = rs.getdouble ("Sal");
String job = rs.getstring ("Empjob");
System.out.println (empno + "\ t" +name+ "\ T" +salary+ "\ T" +job);

}

} catch (Exception e) {
E.printstacktrace ();
} finally {
Jdbcutils.close (conn);
Jdbcutils.close (call);
Jdbcutils.close (RS);
}
}
}

Oracle Learning Notes (12)