Oracle Learning (11): PLSQL Programming

Oracle Learning (11): PLSQL Programming
What is the structure and composition of PL/SQL ?? PL/SQL (Procedure Language/SQL )? Is PLSQL a procedural extension of Oracle's SQL language? It refers to the addition of process processing statements (such as branches and loops) in the SQL command language, so that the SQL language has the process processing capability.

SQL advantages: Interactive non-procedural; strong data control functions; simple automatic navigation statements; easy to use for debugging.
Combining the data manipulation capability of SQL with the data processing capability of process language makes PLSQL process-oriented, but simpler, more efficient, more flexible, and more practical than process language.
Commonly used combined languages: lPlsql (oracle) and SQL Server)


Program Structure of PL/SQL

Declare

Description (variable description, cursor declaration, exception description 〕

Begin

Statement sequence (DML statement 〕...

Exception

Exception Handling statement

End;

/

Description of variables and constants





L description of the variable (char, varchar2, date, number, boolean, long) Basic Data Type Variable
1. Basic data type
Number Type
Int Integer type
Pls_integer Integer type. An error occurs when overflow occurs.
Binary_integer integer, indicating a signed integer
Char fixed-length character type, up to 255 characters
Varchar2 variable-length character type, up to 2000 characters
Long variable-length memory type, up to 2 GB
Date type
Boolean (TRUE, FALSE, NULL)
The data types used in PL/SQL are exactly the same as those used in Oracle databases,
Some have different meanings.
2. Definition of basic data type variables
Variable name type identifier [not null]: = value;
Declare
Age number (3): = 26; -- the length is 3 and the initial value is 26.
Begin
Commit;
End;
The syntax format for defining constants is as follows:
Constant name constant type identifier [not null]: = value;
Declare
Pi constant number (9): = 3.1415926; -- it is a numeric constant of pi. The length is 9 and the initial value is 3.1415926.
Begin
Commit;
End;

If statement

1. IF condition THEN Statement 1;

Statement 2;

End if;

2. IF condition THEN statement sequence 1;

ESLE statement sequence 2;

End if;

 

3. IF condition THEN statement;

ELSIF statement THEN statement;

ELSE statement;

End if;

TIPS: How do I obtain data input from the keyboard? Input from the keyboard:

Accept num prompt 'enter a number ';

? Obtain the keyboard input value:

Pnum number: = #

Statement

1. WHILE total <= 25000 LOOP

...

Total: = total + salary;

End loop;

2. Loop

EXIT [when condition];

......

End loop

3. for I IN 1 .. 3 LOOP

Statement sequence;

End loop;

 

Cursor = ResultSet indicates the Cursor Syntax:

1. Define the cursor

CURSOR name [(Parameter Name Data Type [, parameter name data type]...)]

 

Is select statement;

Stores multiple rows of data returned by a query.

For example:

Cursorc1 is select ename from emp;

2. Open the cursor: openc1; (open the cursor to execute the query)
3. Set the value of a row of cursor: fetch c1 into pjob; (take a row to the variable)
4. close the cursor: close c1; (close the cursor to release resources)
Note: The above pjob must be of the same type as the job column in the emp table :? Definition: pjobemp. empjob % type;
Example

 

Parameter-based cursor

 

Definition Statement:

 

Cursor c2 (jobc varchar2)

Is

Select ename, salfrom emp

Where job = jobc;

 

Execute the statement:

 

Open c2 ('cler ');

 

Exception Handling in Oracle

 

L exceptions are a function provided by the programming language to enhance program robustness and fault tolerance.

 

System definition exceptions? No_data_found (no data found )? Too_many_rows (select... The into statement matches multiple rows )? Zero_Divide (Division by zero )? Value_error (arithmetic or conversion error )? Timeout_on_resource (timeout occurred while waiting for the resource)

 

User-defined exceptions and handling exceptions

DECLARE

My_job char (10 );

V_sal emp. sal % type;

No_data exception;

Cursor c1 is select distinct jobfrom emp order by job;

Begin

Open c1;

Fetch c1 into v_job;

IF c1 % notFOUND then raiseno_data;

End if;

...

EXCEPTION

WHEN no_data THEN insert into empvalues ('data is not obtained by the fetch statement or the data has been processed ');

END;

Define exceptions in the declare section? Out_of exception; does it cause exceptions in feasible statements? Raise out_of; handling exceptions in Exception section? When Out_of then...


Use = to assign values.

Lvar1: = 'this is a argument ';
Lemp_rec.sal: = sal * 2 + nvl (comm, 0 );
Lsum_sal: = sum_sal + v_sal;

Use into to assign lFETCH c1 INTO e_eno, e_sal;



The commit statement l ends the current transaction, so that all modifications executed by the current transaction are permanent.
After executing the DML statement, do not forget to add commit to the Code for submission!

Two annotation formats:

-- This is a comment

Or

/* This is a comment */

Example 1 shows the employee's long salary. Starting from the minimum wage, the length of each person is 10%, but the total salary cannot exceed 50 thousand yuan. Please calculate the number of people with long salary and the total salary after long salary, and output the number of long salary and total salary.
Possible SQL statements :? Select empno, sal from emp order by sal ;? Select sum (sal) into s_sal from emp;


Answer:
/*
Long salary for employees. Starting from the minimum wage, the length of each person is 10%, but the total wage cannot exceed 0.5 million yuan,
Calculate the number of people with long salary and the total salary after long salary, and output the number of people with long salary and total salary.


Write SQL statements that may be used first
Select empno, sal from emp order by sal;
Select sum (sal) from emp;
*/


Set serveroutput on


Declare
Cursor c1 is select empno, sal from emp order by sal;
SalTotal NUMBER; -- record the total salary
EmpCount NUMBER: = 0; -- NUMBER of people who raise their salaries

Pempno emp. empno % TYPE; -- Record employee ID
Psal emp. sal % type; -- Record the employee's salary
Begin
-- Get the current total salary
Select sum (sal) into salTotal from emp;
-- Open the cursor
Open c1;
-- Execution cycle
While salTotal <= 50000
Loop
Fetch c1 into pempno, psal; -- retrieve a record
Exit when c1 % notfound;
Update emp set sal = sal * 1.1 where empno = pempno; -- execute salary increase
-- Record the total amount after salary increase
SalTotal: = salTotal + psal * 0.1;
-- Record the number of people who raise the salary
EmpCount: = empCount + 1;
End loop;
Close c1;
Commit;

Dbms_output.put_line ('salary increase: '| empCount | 'total salary:' | salTotal );
End;
/




Example 2l compile a program in PL/SQL language to achieve segmentation by department (more than 6000, (3000,), less than RMB) count the number of employees in each wage segment and the total wages of each department (the total wages do not include bonuses). See the following format:

Departments less than 3000 number 3000-6000 more than 6000 of total wages

10 2 1 0 8750

20 3 2 0 10875

30 6 0 9400

 

L tip: you can create a new table to save data.

 

Createtable msg1

(Deptno number,

Emp_num1 number,

Emp_num2 number,

Emp_num3 number,

Sum_salnumber );

/*
Compile a program in PL/SQL language to calculate the number of employees in each wage segment by Department segment (more than 6000, (3000,), and less than RMB,
And the total salary of each Department (the total salary does not include the bonus)


Write the query statements that may be used first
A = select distinct deptno from dept;
Select sal from emp where deptno = a value in;


Result output:

1. Directly output to the screen 2. output to a table create table salcount (deptno number, -- department number sg1 int, -- number of people below 3000 sg2 int, -- 3000 ~ 6000 sg3 int -- 6000 or more); */declare -- defines two cursor c1 is select distinct deptno from dept; cursor c2 (pdno number) is select sal from emp where deptno = pdno; -- defines three variables used to save the NUMBER of people in each department's three wage segments count1 number; count2 number; count3 NUMBER; -- Record the Department number pdeptno dept in the c1 cursor. deptno % TYPE; -- Record the salary value psal emp in the c2 cursor. sal % TYPE; beginopen c1; -- open c1 to obtain all Department numbers loopfetch c1 into pdeptno; -- take a department number exit when c1 % notfound; -- the counter is cleared count1: = 0; count2: = 0; count3: = 0; -- get all employees in the department open c2 (pdeptno); loopfetch c2 into psal; -- get the employee's salary exit when c2 % notfound; if psal <= 3000 then count1: = count1 + 1; elsif psal> 3000 and psal <= 6000 then count2: = count2 + 1; else count3: = count3 + 1; end if; end loop; close c2; -- save the statistical results of this department insert into salcount values (pdeptno, count1, count2, count3); commit; end loop; close c1; end;

/