PL/SQL programming (2)

Ii. PL/SQL Process

A process is used to perform specific operations. When the process is established, you can specify the input parameter (in) or output parameter (out ). By using input parameters during the process, you can pass the data to the execution part. By using the output parameters, you can pass the data of the execution part to the application environment.

You can use the create or replace procedure command to create a process.

Instance 1

① Please write a process where you can enter the employee name and new salary to modify the employee's salary ② How to call the process? (2 methods) ---> exec, call ③ How to call the stored procedure in Java and. net Create Or Replace Procedure (NameVarchar2,NewsalNumber)Example3Is -- Place the variable part between is and begin. Begin -- Execution part -- Modify salary based on user name UpdateEMPSetSal=NewsalWhereEname=Name; End;

 

 

Iii. Functions

Function is used to return specific data (usually a value ). When creating a function, the function header must contain the return clause, and the function body must contain the data returned by the Return Statement.

You can use the create function command to create a function.

Instance 1

-- Function case -- Enter the employee's name and return the employee's annual salary Create FunctionGetyearsal(NameVarchar) Return -- Definition of Return Value Number IsYearsalNumber (7,2); Begin -- Execution part SelectSal*12 +Nvl (Comm,0)*12IntoYearsalFromEMPWhereEname=Name; -- Nvl () function indicates that if comm is null, the value is 0. ReturnYearsal; End;   To call a function in sqlplus: SQL>VaR incomeNumber SQL> callGetyearsal('Scott') Into:Income SQL>Print income

 

 

4. triggers

A trigger is a stored procedure that is implicitly executed. When defining a trigger, you must specify the trigger and trigger operations. Common trigger events include insert, update, and delete statements. The trigger operation is actually a PL/SQL block.

You can use the create trigger command to create a trigger.

Note: triggers are very useful to maintain database security and consistency.