PLSQL processes, functions, packages, and triggers

1. Process
A process is used to perform a specific operation. When a process is established, you can specify either an input parameter (in) or an output parameter (out). By using an input parameter during the process, data can be transmitted to the execution part. By using output parameters, data of the execution part can be transferred to the application environment. You can use the create procedure command in sqlplus to create a process.
Example:
(1) Please consider preparing a process where you can enter the employee name, new salary, and modify the employee's salary.
(2). There are two methods to call the process; Exec call
(3). How to call a stored procedure in a Java program
The Code is as follows:

Create procedure sp_pro3 (spname varchar2, newsal number) is <br/> -- Do not write it as number (3, 2), indicating that the type is okay and no size is required. Just like the parameters used in Java Writing Method <br/> SQL code <br/> begin <br/> -- execution part, modify the salary based on the user name <br/> Update EMP set sal = newsal where ename = spname; <br/> end; <br/>/</P> <p> begin <br/> -- execution part, modify the salary based on the user name <br/> Update EMP set sal = newsal where ename = spname; <br/> end; </P> <p>

Java program calls a stored procedure
// Demonstrate the case of JAVA Program Calling Oracle Stored Procedure <br/> JAVA code <br/> Import Java. SQL. *; <br/> public class testoraclepro {<br/> Public static void main (string [] ARGs) {</P> <p> try {<br/> // 1. load the driver <br/> class. forname ("oracle. JDBC. driver. oracledriver "); <br/> // 2. obtain the connection <br/> connection Ct = drivermanager. getconnection ("JDBC: oracle: thin@127.0.0.1: 1521: myora1", "Scott", "m123"); </P> <p> // 3. create callablestatement <br/> callablestatem Ent cs = CT. preparecall ("{call sp_pro3 (?,?)} "); <Br/> // 4.? Assign value <br/> CS. setstring (1, "Smith"); <br/> CS. setint (2, 10); <br/> // 5. run <br/> cs.exe cute (); <br/> // close <br/> CS. close (); <br/> CT. close (); <br/>}catch (exception e) {<br/> E. printstacktrace (); <br/>}< br/>}

2. Functions
A function is used to return specific data. When a function is created, the return clause must be included in the function header. The function must contain the data returned by the Return Statement. We can use create function to create a function. The actual case is as follows:
-- Enter the employee's name and return the employee's annual salary <br/> Create Function annual_incomec (name varchar2) <br/> return number is <br/> annual_salazy number ); <br/> begin <br/> -- execution part <br/> select Sal * 12 + nvl (Comm, 0) into annual_salazy from EMP where ename = Name; <br/> return annual_salazy; <br/> end; <br/>

If a compilation error occurs during function creation, run the show error command to display the error.
Call functions in sqlplus
VaR income number
Call annual_incomec ('Scott ') into: income;
Print income
VaR income number
Call annual_incomec ('Scott ') into: income;
Print income
We can also call this function in a Java program.
Select annual_income ('Scott ') from dual;
In this way, the returned results can be obtained through RS. getint (l.

3. Package
A package is used to logically combine processes and functions. It consists of a package specification and a package body.
(1). You can use the create package command to create a package.
The specification of the package only contains the description of the process and function, but there is no implementation code of the process and function. The package body is used to implement procedures and functions in the package specification.
-- Create a package sp_package <br/> -- declare that the package has a process update_sal <br/> -- declare that the package has a function annual_income <br/> SQL code <br/> Create package sp_package is <br/> procedure update_sal (name varchar2, newsal number); <br/> function annual_income (name varchar2) return number; <br/> end; </P> <p> Create package sp_package is <br/> procedure update_sal (name varchar2, newsal number); <br/> function annual_income (name varchar2) return number; <br/> end; </P> <p>

(2) You can use the create package body command to create a package body.
-- Implement the package body for the sp_package <br/> Create or replace package body sp_package is <br/> procedure update_sal (name varchar2, newsal number) <br/> is <br/> begin <br/> Update EMP set sal = newsal where ename = Name; <br/> end; <br/> function annual_income (name varchar2) return number is <br/> annual_salary number; <br/> begin <br/> select Sal * 12 + nvl (Comm, 0) into annual_salary from EMP <br/> where ename = Name; <br/> return annual_salary; <br/> end; <br/>

(3). How to call the package process or function
When calling a package process or function, the package name must be included before the process and function. to access a package of another solution, you must add the solution name before the package name.
Call sp_package.update_sal ('Scott, 1500 );
4. triggers
A trigger is a stored procedure that is implicitly executed. When defining a trigger, you must specify the trigger event and trigger operation. Common trigger events include the insert, update, and delete statements. The trigger operation is actually a PL/SQL block. You can use create trigger to create a trigger.
We will introduce the use of triggers in detail later, because triggers are very useful and maintain database security and consistency.