pl/sql-04

Variables and variable types

A variable is an area of memory used to store data, defined in the declare area of a PL/SQL block, when defining a variable, you typically specify a data type for the variable, or you can specify an initial value for the variable when you define the variable.

The type of the variable can make any SQL data type or a specific PL-SQL type have 4 types of variables

1. A scalar variable refers to a variable that holds a single value, which is the most commonly used variable for PL/SQL. The data type of a scalar variable contains numbers, characters, dates, and Boolean types.

2. The conforming variable refers to a variable that holds multiple values, and you must use the PL/SQL composite data type to define the variable.

3. The reference variable refers to the variable that holds the value pointer.

4. LOB variables refer to variables used to store large amounts of data.

Declare

V_deptname VARCHAR2 (10); --Define scalar variables

V_loopcounter Binary_integer--defining scalar variables with the PL/SQL type

--Defining record types

Type T_employee is record (EmpName varchar2, empno number (7), Job varchar2 (20));

V_employee T_employee;--A variable that defines the record type

Type csor is REF CURSOR; --Define cursor variables

V_date date NOT null default sysdate; --Define variables and set default values

Begin

Null

End;

Program Control statements

Conditional control Statement If ... then ... else | If ... then ... then ... elsif .....; else ...;

Create or Replace function getaddsalaryratiocase (p_job varchar2) return number

As V_result number (7,2);

Begin

Case P_job

When ' clerk ' then v_result: = 0.10;

When ' salesman ' then v_result: = 0.15;

When the ' MANAGER ' then v_result: = 0.20;

End case;

return v_result;

Exception when others

Then Dbms_output.put_line (' Produce an exception: ');

End;

Select Getaddsalaryratiocase (' Clerk ') from dual;

Declare

Result number (7,2);

V_job VARCHAR2 (9);

V_empno varchar2 (20);

V_ename VARCHAR2 (60);

Cursor C_emp is the Select Job, empno,ename from scott.emp for update;

Begin

Open c_emp;

Loop

Fetch c_emp into v_job,v_empno,v_ename;

Exit when C_emp%notfound;

Result: = Getaddsalaryratiocase (V_job);

Update scott.emp Set sal = Sal * (1 + result) where current of c_emp;

Dbms_output.put_line (' already for employees ' | | v_empno| | ': ' | |  v_ename| | ' A successful raise! ‘);

End Loop;

Close c_emp;

Exception when others then Dbms_output.put_line (' No Employee information found! ‘);

End

Loop control Statements

PL/SQL provides three types of loops

1, simple cycle loop ... end loop;

2. For loop for ... exit when (exit condition);

3. The while loop executes the loop only when the condition is true

Declare

V_number1 number (3);

V_number2 number (3);

Begin

For V_number1 in 1.. 9

Loop

For V_number2 in 1.. V_number1

Loop

Dbms_output.put (V_number1 | | ' * ' | | v_number2| | ' = ' | | V_number1 * v_number2 | | ‘ ‘);

End Loop;

Dbms_output.put_line (");

End Loop;

End

pl/sql-04