Han shunping: Oracle PL/SQL lecture notes 3

It's a bit embarrassing. It's far from taking 2nd lectures. This lecture mainly explains functions, packages, and variables.

1. Differences between functions and processes
1) function: return a specific value, usually one;
Procedure: perform specific operations
2) When creating a function:
The return clause must be included in the function header.
For example;
Return number is
Think_salary number (7, 2)
The return statement must also be included in the function body.
Return think_salary

Case 2: Enter the employee's name and return the employee's annual salary
Create Function sp_think (spname varchar2)
Return number is
Yearsal number (7,2 );
Begin
Select Sal * 12 + nvl (Comm, 0) * 12 into yearsal from EMP where ename = spname;
Return yearsal;
End;
/
Call in sqlplus:
VaR think number
Call sp_think ('Scott ') into: Think
Print think

3 packages
It is used to logically combine processes and functions. It consists of a packet header and a package body.
Baotou: process or function declaration
Package body: the process or function declared in the header.
Case:
Baotou:
Create package sp_think
Is
Procedure update_sal (name varchar2, newsal number );
Function annual_income (name varchar2) return number;
End;
/
Package body:
Create package body sp_think is
Procedure update_sal (name varchar2, newsal number)
Is
Begin
Update EMP set sal = newsal where ename = Name;
End;
Function annual_income (name varchar2)
Return number is
Annual_salary number;
Begin
Select Sal * 12 + nvl (Comm, 0) into annual_salary from EMP where ename = Name;
Return annual_salary;
End;
End;
/

4. How to call the package process or function
When calling a self-created package:
Call sp_think.update_sal ('Scott, 1500 );
When calling packages of other solutions, such as HR:
Call HR. sp_think.update_sal ('Scott, 1500 );

5 triggers
Trigger is the process of Automatic Execution
Includes:
1) events triggered, such as DML statements
2) operation triggered: PL/SQL Block
Role: Maintain database security and consistency

6 variables
1) v_ename varchar2 (10) --: = is the assigned number, such as v_sal number (6, 2): = 5.8
2) variables used to store multiple values:
2.1) PL/SQL records: similar to the structure in C Language
Case:
Declare
Type think is record (name EMP. ename % type, salary EMP. Sal % Type) -- name changes with ename
Re_think think
Begin
Select ename, Sal into think from EMP where empno7788;
Dbms_output.put_line ('employee name: '| think. Name );
End;
2.2) PL/SQL table: similar to arrays in C Language
Case:
Declare
Type think is table of EMP. ename % Type Index by binary_integer;
-- Defines a PL/SQL table type and stores EMP. ename % type. The subscript is a positive integer.
Ta_think think;
....


3) cursor variable: pointer to the result set
Case: define a PL/SQL block. You can enter a department number to display the names and salaries of all employees in the department.
Declare
Type think_cursor is ref cursor;
Water_cursor think_cursor;
V_ename EMP. ename % type;
V_sal EMP. Sal % type;
Begin
Open water_cursor for select ename, Sal from EMP where deptno = &;
Loop
Fetch water_cursor into v_ename, v_sal;
Exit when water_cursor % notfound;
Dbms_output.put_line ('name: '| v_ename | 'salary:' | v_sal)
End Loop
End;