PL/SQL chapter 3rd Package example

Chapter 4 Package

/** Create a package specification **/
Create package emp_pkg is
Procedure update_sal (name varchar2, newsal number );
Function income (name varchar2) return number;
End;
/** Create a package **/
Create package body emp_pkg is
Procedure update_sal (name varchar2, newsal number)
Is
Begin
Update EMP set sal = newsal where lower (ename) = lower (name );
End;

Function income (name varchar2) return number
Is
Salary number (7,2 );
Begin
Select Sal * 12 + nvl (Comm, 0) into salary from EMP where lower (ename) = lower (name );
Return salary;
End;

End;

Select * from EMP where lower (ename) = 'Scott ';

/***** Call storage and functions ******/
Call emp_pkg.update_sal ('Scott, 1500 );

SQL> var income number
SQL> call emp_pkg.income ('Scott ') into: income;

The call is complete.

SQL> Print income;

Income
----------
36000

/************ Create a trigger **************/

The following trigger is triggered before the table tb_emp is updated to prevent table modification on Tuesday:

SQL> Create trigger update_dept before insert or update or delete
2 On Dept
3 begin
4 If (to_char (sysdate, 'dy ') = 'tues') then
5 raise_application_error (-20600, 'Do not modify dept' on Tuesday, true );
6 end if;
7 end;
8/

Trigger created

SQL> insert into dept (deptno, dname) values (90, 'luowj ');
Insert into dept (deptno, dname) values (90, 'luowj ')
*
Row 3 has an error:
ORA-20600: cannot modify dept on Tuesday
ORA-06512: In "Scott. update_dept", line 3
ORA-04088: An error occurred while executing the trigger 'Scott. update_dept'

SQL> show errors;
No error.

/************ % Type usage and function ***************/

Declare
V_ename varchar2 (5 );
V_sal number (6, 2 );
C_tax_rate constant number (3,2): = 0.03;
V_tax_sal number (6, 2 );
Begin
Select ename, Sal into v_ename, v_sal from EMP where empno = & No;
V_tax_sal: = v_sal * c_tax_rate;
Dbms_output.put_line ('employee name: '| v_ename );
Dbms_output.put_line ('employee salary: '| v_sal );
Dbms_output.put_line ('income tax: '| v_tax_sal );
End;

Select * from EMP where empno = '201312 ';
/** Varchar2 processes all characters in two bytes (generally )**/
Select * from EMP where empno = '000000'; if the NO value is set to 7744, the string buffer is too small because the ename Column
The maximum length is 10 bytes and can only store 5 Characters

The following is a solution to the above problem using the % Type attribute:
/* When the % Type attribute is used to define a variable, it determines the type and length of the new variable according to the database column or other variables */

Declare
V_ename EMP. ename % type;
V_sal EMP. Sal % type;
C_tax_rate constant number (3,2): = 0.03;
V_tax_sal v_sal % type;
Begin
Select ename, Sal into v_ename, v_sal from EMP where empno = & Eno;
V_tax_sal: = v_sal * c_tax_rate;
Dbms_output.put_line ('employee name: '| v_ename );
Dbms_output.put_line ('employee salary: '| v_sal );
Dbms_output.put_line ('income tax: '| v_tax_sal );
End;