Oracle Learning (12): stored procedures, functions and triggers, and oracle stored procedures
Stored Procedures and functions
L The subroutines stored in the database for all user programs to call are stored procedures and stored functions.
Note: When stored procedures and stored functions declare variables, they use as instead of declare. stored procedures differ from stored functions. stored procedures do not return values. Stored functions return values.
CREATE a stored PROCEDURE l use the create procedure command to CREATE a stored PROCEDURE
L Syntax:
Create [or replace] PROCEDURE process name (parameter list)
AS
PLSQL subprogram body;
Simple Example of creating a stored procedure /*
The first stored procedure: Hello World
Call the stored procedure:
1. exec sayHello ();
2. begin
SayHello ();
End;
/
*/
Create or replace procedure sayHello
As
-- Variable description
Begin
Dbms_output.put_line ('Hello World ');
End;
/
Call Stored Procedure
Method 1
Set serveroutput on
Begin
Raisesalary (7369 );
End;
/
Method 2
Set serveroutput on
Exec raisesalary (7369 );
Stored Procedure (pay-as-you-go instance) instance 1
Pay 10% yuan for the specified employee on the basis of the original salary, and print the salary before and after the salary increase
Code: **************************************** **************************************** ******************
/*
Pay 10% yuan for the specified employee on the basis of the original salary, and print the salary before and after the salary increase
Possible SQL statements
Update emp set sal = sal * 1.1 where empno = empid;
*/
Create or replace procedure raiseSalary (empid in number)
As
PSal emp. sal % type; -- save the current employee salary
Begin
-- Query the employee's salary
Select sal into pSal from emp where empno = empid;
-- Raise the employee's salary
Update emp set sal = sal * 1.1 where empno = empid;
-- Print the salary before and after the increase
Dbms_output.put_line ('employee ID: '| empid | 'before salary increase:' | psal | 'after salary increase '| psal * 1.1 );
End;
/
**************************************** **************************************** ******************
Instance 2: increase the salary of the specified quota for the specified employee (multiple parameters are passed)
Code: **************************************** **************************************** ******************
Create or replace procedure raiseSalary2 (empid in number, rate in NUMBER)
As
PSal emp. sal % type; -- save the current employee salary
Begin
-- Query the employee's salary
Select sal into pSal from emp where empno = empid;
-- Raise the employee's salary
Update emp set sal = sal * rate where empno = empid;
-- Print the salary before and after the increase
Dbms_output.put_line ('employee No.: '| empid | 'before salary increase:' | psal | 'after salary increase '| psal * rate );
End;
/
**************************************** **************************************** ******************
The storage Function l Function is a named storage program. It can contain parameters and return a calculated value. The structure of a function is similar to that of a process, but a RETURN clause must be provided to RETURN the function value. Function Description specifies the function name, result value type, and parameter type.
L syntax for creating a storage function: l
CREATE [or replace] FUNCTION name (parameter list)
RETURN function value type
AS
PLSQL subprogram body;
Example of a storage function: query the annual income of an employee.
Code:
**************************************** ************************************
/*
Query the total income of an employee.
*/
Create or replace function queryEmpSalary (empid in number)
RETURN NUMBER
As
PSal number; -- defines the variable to save the employee's salary
PComm number; -- defines variables to save employee bonuses
Begin
Select sal, comm into pSal, pcomm from emp where empno = empid;
Return psal * 12 + nvl (pcomm, 0 );
End;
/
**************************************** ************************************
Storage function call 1
Declare
V_salnumber;
Begin
V_sal: = queryEmpSalary (7934 );
Dbms_output.put_line ('salary is: '| v_sal );
End;
/
Call 2
Begin
Dbms_output.put_line ('salesaryis: '| queryEmpSalary (7934 ));
End;
In and out in processes and functions
L in general, the difference between a process and a function is that a function can return a value, but a process does not return a value.
L However, both the process and function can specify one or more output parameters through out. We can use the out parameter to return multiple values in the process and function.
Stored Procedure example with out function
/*
Out parameter: query the name, monthly salary, and position of an employee.
*/
Create or replace procedure queryEmpInfo (eno in number,
Pename out varchar2,
Psal out number,
Pjob out varchar2)
As
Begin
Select ename, sal, empjob into pename, psal, pjob from emp where empno = eno;
End;
/
Use the cursor l in the out parameter to declare the package structure first
L then create a package
Access the out parameter of the cursor type in Java
Code implementation:
Package demo. test; import java. SQL. callableStatement; import java. SQL. connection; import java. SQL. resultSet; import oracle. jdbc. oracleTypes; import oracle. jdbc. oracleCallableStatement; import org. junit. test; import demo. utils. JDBCUtills;/** Statement <PreparedStatement <CallableStatement */public class TestOracle {/** create or replaceprocedure queryEmpInfo (eno in number, pename out varchar2, psal out Number, pjob out varchar2) * // @ Testpublic void testProcedure () {// stored procedure Test Case // {call procedure-name (?? /)} String SQL = "{call queryEmpInfo (?,?,?,?)} "; Connection conn = null; CallableStatement call = null; try {conn = JDBCUtills. getConnection (); call = conn. prepareCall (SQL); // Set value to paramcall. setInt (1, 7839); // declare out parametercall. registerOutParameter (2, OracleTypes. VARCHAR); call. registerOutParameter (3, OracleTypes. NUMBER); call. registerOutParameter (4, OracleTypes. VARCHAR); // runcall.exe cute (); // get returned valuesString name = call. getStr Ing (2); double sal = call. getDouble (3); String job = call. getString (4); System. out. println (name); System. out. println (sal); System. out. println (job);} catch (Exception ex) {ex. printStackTrace ();} finally {JDBCUtills. release (conn, call, null) ;}/ ** create or replacefunction queryEmpIncome (eno in number) return number */@ Testpublic void testFunction () {// store function test cases //{? = Call procedure-name (?? /)} String SQL = "{? = Call queryEmpIncome (?)} "; Connection conn = null; CallableStatement call = null; try {conn = JDBCUtills. getConnection (); call = conn. prepareCall (SQL); call. registerOutParameter (1, OracleTypes. NUMBER); call. setInt (2, 783920.call.exe cute (); double income = call. getDouble (1); System. out. println (income);} catch (Exception ex) {ex. printStackTrace ();} finally {JDBCUtills. release (conn, call, null) ;}@ Testpublic void testCursor () {// access cursor test case St Ring SQL = "{call MYPACKAGE. queryEmpList (?,?)} "; // Note: {} Connection conn = null; CallableStatement call = null; ResultSet rs = null; try {conn = JDBCUtills. getConnection (); call = conn. prepareCall (SQL); call. setInt (1, 20); call. registerOutParameter (2, oracletypes.cursorrentcall.exe cute (); rs = (OracleCallableStatement) call ). getCursor (2); while (rs. next () {String name = rs. getString ("ename"); double sal = rs. getDouble ("sal"); System. out. println (name + "" + sal) ;}} catch (Exception ex) {ex. printStackTrace ();} finally {JDBCUtills. release (conn, call, rs );}}}
Calling stored procedures in Java
Call storage functions in Java
When should I use stored procedures/stored functions?
Principle: if there is only one return value, use the stored function; otherwise, use the stored procedure.
Trigger trigger definition database trigger is a PL/SQL program associated with the table and stored. When a specific data operation statement (Insert, update, delete) is issued on a specified table, Oracle automatically executes the sequence of statements defined in the trigger.
Trigger type statement-Level Trigger • execute one or more operations before or after the specified operation statement operation, regardless of the number of rows affected by this statement.
ROW-Level Trigger (for each row) • EACH record that triggers a statement is triggered. Use old and new pseudo-record variables in row-level triggers to identify the status of values.
Create a trigger
CREATE [or REPLACE] TRIGGER name
{BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF column name]}
ON Table Name
[For each row [WHEN (condition)]
PLSQL Block
Simple trigger instance
/*
First trigger: update
*/
Create or replace trigger sayHello
After update
On emp
Begin
Dbms_output.put_line ('Hello World ');
End;
/
Value of the row-Level Trigger trigger statement and pseudo-record variable
Trigger Application Scenario 1 (statement trigger )/*
Trigger Application Scenario 1: implement complex security checks
Restrict non-working hours to insert data to the database emp
1. Weekend: Saturday to_char (sysdate, 'day ')
2. <9 or> 18: to_number (to_char (sysdate, 'hh24 '))
*/
Create or replace trigger securityEmp
Before insert
On emp
Begin
If to_char (sysdate, 'day') in ('wedned', 'satur', 'sunday ')
Or to_number (to_char (sysdate, 'hh24') not between 9 and 18 then
Raise_application_error (-20001, 'Data cannot be inserted during non-work Time ');
End if;
End;
/
Scenario 2 (Row-Level Trigger)
/*
Trigger Application Scenario 2: confirm data
Pay-as-you-go cannot increase or decrease
Two pseudo-record variables of a row-Level Trigger: old,: new
*/
Create or replace trigger checksal
Before update
On emp
For each row
Begin
If: old. sal>: new. sal then
Raise_application_error (-20002, 'salary after increase cannot be less than before increase. After increase: '|: new. sal |' before increase: '|: old. sal );
End if;
End;
/
Trigger exercises limit each department to recruit only five employees. If the number of employees exceeds the plan, an error message is returned.
**************************************** *************************************
/*
Exercise: only five employees are allowed to be recruited in each department. If the number of employees exceeds the plan, an error message is returned.
*/
Create or replace trigger limitEmpCount
Before insert on emp
For each row
Declare
PCount number; -- save the number of employees in each department
Begin
Select count (*) into pcount from emp where deptno =: new. deptno;
If pcount> 5 then raise_application_error (-20004, 'department: '|: new. deptno | 'five Employees ');
End if;
End;
