Oracle Learning (11): PLSQL programming, oracleplsql
What is the structure and composition of PL/SQL? • PL/SQL (Procedure Language/SQL) • PLSQL is an Oracle procedural extension of the SQL Language • It adds process processing statements (such as branches and loops) to the SQL command Language ), make the SQL language process.
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: Get data input from the keyboard • input from the keyboard:
Accept num prompt 'enter a number ';
• Obtain the keyboard input value:
Pnum number: = & num;
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 selectename 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 (jobcvarchar2)
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 exception • No_data_found (no data found) • Too_many_rows (select... Into statement matches multiple rows) • Zero_Divide (Division by zero) • Value_error (arithmetic or conversion error) • Timeout_on_resource (timeout occurred while waiting for resources)
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; cause exceptions in feasible statements • raise out_of; handle exceptions in the 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 on the screen
2. output to a table
Create table salcount
(Deptno number, -- department number
Sg1 int, -- Number of people under 3000
Sg2 int, -- 3000 ~ 6000 of Students
Sg3 int -- more than 6000 people
);
*/
Declare
-- Define two cursors to save the result
Cursor c1 is select distinct deptno from dept;
Cursor c2 (pdno number) is select sal from emp where deptno = pdno;
-- Define three variables to save the number of people in each department with three salary segments
Count1 NUMBER;
Count2 number;
Count3 number;
-- Record the Department number in the c1 cursor
Pdeptno dept. deptno % TYPE;
-- Record the salary value in the c2 cursor
Psal emp. sal % TYPE;
Begin
Open c1; -- open c1 to get all Department numbers
Loop
Fetch c1 into pdeptno; -- get a department number
Exit when c1 % notfound;
-- Counters are cleared
Count1: = 0;
Count2: = 0;
Count3: = 0;
-- Get all employees of the Department
Open c2 (pdeptno );
Loop
Fetch 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 statistics of this department
Insert into salcount values (pdeptno, count1, count2, count3 );
Commit;
End loop;
Close c1;
End;
/
