Basic PL/SQL

At ordinary times, we usually need to write some small PL/SQL programs, triggers, functions, packages, and so on. The following is a small example, where sparrows are kept small and dirty. You can draw a line from each other.

Conn HR/hr

SQL> Create Table EMP as select * from employees;

SQL> Create Table dept as select * from orders ments

 

PL/SQL Block
Declare
V_ename varchar2 (20 );
Begin
Select first_name into v_ename from employees where employee_id = & No;
Dbms_output.put_line ('the name of employee is: '| v_ename );
Exception
When no_data_found then
Dbms_output.put_line ('Please input the right employee number ');
End;
/

Stored Procedure:
Create or replace procedure update_sal (name varchar2, newsal number)
Is
Begin
Update EMP set salary = newsal where lower (first_name) = lower (name );
Commit;
End;

Function:
Create or replace function annual_income (name varchar2)
Return number is
Annual_salary number (7, 2 );
Begin
Select salary * (12 + nvl (commission_pct, 0) into annual_salary from EMP where lower (first_name) = lower (name );
Return annual_salary;
End;
/

Function execution
VaR income number
Call annual_income ('donald ') into: income;
Print income

Package:
Main Function
Create or replace package emp_pkg is
Procedure update_sal (name varchar2, newsal number );
Function annual_income (name varchar2) return number;
End;
/

Function body:
Create or replace package body emp_pkg is
Procedure update_sal (name varchar2, newsal number) is
Begin
Update EMP set salary = newsal where lower (first_name) = lower (name );
Commit;
End;
Function annual_income (name varchar2) return number is
Annual_salary number (7, 2 );
Begin
Select salary * (12 + nvl (commission_pct, 0) into annual_salary from EMP where lower (first_name) = lower (name );
Return annual_salary;
End;
End;
/

Call example:
SQL> call emp_pkg.update_sal ('donald ', '123 ');

SQL> var income number
SQL> call emp_pkg.annual_income ('donald ') into: income;
SQL> Print income

 

Trigger
Create or replace trigger update_cascade
After update of department_id on Dept
For each row
Begin
Update EMP set department_id =: New. department_id where department_id =: Old. department_id;
End;
/

 

Use scalar Variables
SQL> set serveroutput on
Declare
V_ename varchar2 (20 );
V_sal number (8, 2 );
C_tax_rate constant number (3,2): = 0.03;
V_tax_sal number (8, 2 );

Begin
Select first_name, salary into v_ename, v_sal from EMP where employee_id = & 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 ('employee tax: '| v_tax_sal );
End;
/
The disadvantage of the above statement is that in case the field length of the database table changes, you need to modify the program code above.
Therefore, the program is poorly maintained.

We recommend that you use % type, which determines the type and length of the new variable based on the database column or other variables. The following is an example:

Declare
V_ename EMP. first_name % type;
V_sal EMP. Salary % type;
C_tax_rate constant number (3,2): = 0.03;
V_tax_sal v_sal % type;

Begin
Select first_name, salary into v_ename, v_sal from EMP where employee_id = & 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 ('employee tax: '| v_tax_sal );
End;
/

Compound variable: a variable used to store multiple variables.
It includes PL/SQL records, PL/SQL tables, nested tables, and varray.
1. PL/SQL records
Declare
Type emp_record_type is record (
Name EMP. first_name % type,
Salary EMP. Salary % type,
Title EMP. job_id % type );

Emp_record emp_record_type;
Begin
Select first_name, salary, job_id into emp_record from EMP where employee_id = 190;
Dbms_output.put_line ('first _ name: '| emp_record.name );
End;
/

2. PL/SQL table
Declare
Type ename_table_type is table of EMP. first_name % Type
Index by binary_integer;
Ename_table ename_table_type;
Begin
Select first_name into ename_table (-1) from EMP where Fig = 190;
Dbms_output.put_line ('frist _ name: '| ename_table (-1 ));
End;
/
Ename_table (-1) indicates the element of-1 in the table below.

3. nested tables
4, varry

 

Reference variable: a variable used to store the pointer of a value. When compiling PL/SQL programs, you can use two types of reference variable: cursor variable (ref cursor) and object type variable ref obj_type.
1, ref cursor
Declare
Type C1 is ref cursor;
Emp_cursor C1;
V_ename EMP. first_name % type;
V_sal EMP. Salary % type;
Begin
Open emp_cursor
Select first_name, salary from EMP where department_id = 30;
Loop
Fetch emp_cursor into v_ename, v_sal;
Exit when emp_cursor % notfound;
Dbms_output.put_line (v_ename );
End loop;
Close emp_cursor;
End;
/

2, ref obj_type

Non-PL/SQL variables: When a non-PL/SQL variable is referenced in PL/SQL, a colon (:) must be added before a non-PL/SQL variable (:)
For example:
VaR name varchar2 (10)
Begin
Select first_name into: name from EMP where employee_id = 190;
End;
/

SQL> Print Name

Name
--------------------------------
Timothy