Oracle--pl/sql

what is PL/SQL

PL/SQL is a process-procedural language (LANGUAGE/SQL). PL/SQL is an extension of the Oracle database to the statement. In the use of ordinary SQL statements to add the characteristics of programming languages, so PL/C is to organize data operations and query statements in the PL/SQL code of the process unit, through logical judgment, loop and other operations to achieve complex functions or computational programming language.

PL/SQL Syntax1) Hello,world

--Print Hello Worlddeclare  --Description section begin  --Program part  Dbms_output.put_line (' Hello world '); --dbms_output equivalent to class end;/in Java

2) Defining variable types

• Reference type variable

--Reference variable: Query and print 7839 name and salary declare  --Define variable save name and salary  --pename varchar2 (20);--How to declare a variable type 1: Directly define the type of the variable  --psal Number   ;  Pename emp.ename%type;--declaring a variable type 2 (reference variable): Same as the type of ename column in the EMP table  psal   emp.sal%type;begin  --Get 7839 of the name and salary  Select Ename,sal into Pename,psal the from EMP where empno=7839;--use into to assign a variable-  -print  dbms_output.put_line ( pename| | ' The salary is ' | | PSAL);--| | Is the string concatenation in Oracle end;/

• Record type variable

--Record type variable: Query and print 7839 of the name and salary declare  --Define the record type variable: Represents a row in the EMP table  emp_rec emp%rowtype;begin  select * Into Emp_rec from EMP where empno=7839;    Dbms_output.put_line (emp_rec.ename| | ' The salary is ' | | Emp_rec.sal); end;/

3) If statement

--Determine the number that the user entered from the keyboard--Accept keyboard input--variable num: is an address value that holds the input value on the address Accept num Prompt ' Enter a number ';d eclare   --Define the variable to save the input number  Pnum Number: = #begin  If pnum = 0 Then Dbms_output.put_line (' You have entered 0 ');     elsif pnum = 1 Then dbms_output.put_line (' You have entered 1 ');     elsif pnum = 2 Then Dbms_output.put_line (' You have entered 2 ');     else Dbms_output.put_line (' other numbers ');  End if;end;/

4) Cycle

--Print 1~10declare   --Define variable  pnum number: = 1;begin  loop    -Exit condition exit when    pnum >;        --Print    dbms_output.put_line (pnum);    --Add a    pnum: = pnum + 1;  End loop;end;/

5) Cursor

--Query and print the employee's name and salary/* Properties of the cursor:%isopen (whether open)   %rowcount (number of rows affected)             %found (whether there is a value)    %notfound (no value) */declare    -- Defining Cursors (cursors) cursor Cemp is a   select Ename,sal from emp;   Pename Emp.ename%type;   Psal   emp.sal%type;begin  --Opens the cursor open  cemp;  Loop       -Fetch the current record       cemp into pename,psal;       --exit when the record is not taken;       Exit when Cemp%notfound;              Dbms_output.put_line (pename| | ' The salary is ' | | PSAL);  End Loop;  --closing cursor close  cemp;end;/

Example: Raise wages for employees

--to raise wages for employees, President 1000 Manager 800 other 400declare   --Define cursor  cemp is select Empno,job from emp;  Pempno Emp.empno%type;  Pjob   emp.job%type;begin  --Opens the cursor open  cemp;    Loop       -Take an employee       fetch cemp into pempno,pjob;       Exit when Cemp%notfound;              --Judge Position       if Pjob = ' president ' then update EMP set sal=sal+1000 where empno=pempno;          elsif pjob = ' MANAGER ' then update emp set sal=sal+800 where empno=pempno;          Else update emp set sal=sal+400 where empno=pempno;       End If;  End Loop;  --closed cursor close  cemp;    --Submit  ----> Why?: Transaction ACID  commit;    Dbms_output.put_line (' done '); end;/

• Cursors with parameters

--Query the employee name of a department declare    --parameter   cursor Cemp (DNO number) is select ename from emp where Deptno=dno;   Pename Emp.ename%type;begin   --Argument   open Cemp (a);   Loop        fetch cemp into pename;        Exit when Cemp%notfound;                Dbms_output.put_line (pename);   End Loop;   Close cemp;end;/

6) Exceptions (exceptions)

• System exceptions

--by 0 except declare   pnum number;begin  pnum: = 1/0;  Exception when  zero_divide then Dbms_output.put_line (' 1:0 cannot do denominator ');                        Dbms_output.put_line (' 2:0 cannot do denominator ');  When Value_error and then dbms_output.put_line (' Arithmetic or conversion error ');                        When others and then Dbms_output.put_line (' other exceptions '); end;/

• Custom exceptions

--Query staff of unit 50th (No. 50th does not exist) declare   cursor cemp is  select ename from emp where deptno=50;  Pename Emp.ename%type;    --Custom Exception  No_emp_found exception;begin  open cemp;    -Take the first record  fetch cemp into pename;  If Cemp%notfound then    -throws an exception, using raise    raise No_emp_found;  End If;    --Process: Pmon process (proccesss monitor)  close cemp;exception when  No_emp_found then Dbms_output.put_line (' No Employees found ') ;  When others and then Dbms_output.put_line (' other exceptions '); end;/

Example 1: Statistics on the number of employees entering the year (using PL/SQL)

/*1, SQL statement Select To_char (hiredate, ' yyyy ') from EMP,---> Collection---> Cursor---> Loop---> Exit: Notfound2, Variable: (*) initial value (*) how the final  The number of people who get into the year count80 numbers: = 0;count81 #: = 0;count82 #: = 0;count87 #: = 0;*/declare--Define cursor cursors Cemp   is select To_char (hiredate, ' yyyy ') from EMP;      Phiredate VARCHAR2 (4);  --Number of count80 numbers per year: = 0;  Count81 number: = 0;  count82 number: = 0;  count87 Number: = 0;begin--Opens the cursor open cemp;    Loop-Take the entry year of an employee fetch cemp into phiredate;        Exit when Cemp%notfound;       --Determine which year it is if phiredate = ' 1980 ' then count80:=count80+1;       elsif phiredate = ' 1981 ' then count81:=count81+1;       elsif phiredate = ' 1982 ' then count82:=count82+1;     else count87:=count87+1;  End If;    End Loop;    --closed cursor close cemp; --Output Dbms_output.put_line (' total: ' | |  (count80+count81+count82+count87));  Dbms_output.put_line (' 1980: ' | | count80);  Dbms_output.put_line (' 1981: ' | | count81);  Dbms_output.put_line (' 1982: ' | | count82); dbms_output.put_lIne (' 1987: ' | | count87); end;/ 

Example 2: For employees to raise wages, the lowest total wages began to rise, no one rose 10%, but the gross salary does not exceed million, please calculate the number of long wages and long wages after the payroll.

/*1, SQL statement selet empno,sal from emp order by SAL,---> Cursor---> Loop---> Exit: 1. Total >5w 2. NOTFOUND2, Variable: (*) initial value (*) The number of people who will eventually get a raise: countemp numbers: = 0; Gross salary after rise: Saltotal, (1) Select sum (SAL) into Saltotal from E    MP; (2) after the rise = before the rise + Sal *0.1 Exercise: Number of people: 8 total: 50205.325*/declare cursor cemp is select Empno,sal from emp order by Sal;    Pempno Emp.empno%type;        Psal Emp.sal%type;    -Salary Increase: countemp number: = 0;        --The total wage after the rise: saltotal Number;begin--Get the initial value of the payroll, select SUM (SAL) into the saltotal from EMP;    Open cemp; Loop--1.         Total >5w exit when Saltotal > 50000;         --Take an employee fetch cemp into pempno,psal; --2.                  NotFound exit when Cemp%notfound;         --Raise the wage update EMP set sal=sal*1.1 where Empno=pempno;         --Number +1 countemp: = Countemp +1;    --after the rise = before the rise + sal *0.1 saltotal: = saltotal + psal * 0.1;    End Loop;        Close Cemp;    Commit Dbms_output.put_line (' Number of people: ' | | Countemp| | ' Total: ' | | Saltotal); end;/

Example 3:

/*1, SQL Statement Department: SELECT Deptno from dept; ---> Employee's salary in the cursor department: Select Sal from emp where deptno=?? ---> Cursors with parameters 2, variable: (*) initial value (*) How to get the number of each segment count1 numbers; Count2 number; COUNT3 number; Total Payroll saltotal Number: = 0; (1) Select sum (SAL) into the saltotal from EMP where deptno=??  (2) Cumulative */declare--Department cursor cdept is select Deptno from dept;    Pdeptno Dept.deptno%type;  --Employee's salary in Department cursor cemp (DNO number) is a select Sal from EMP where Deptno=dno;    Psal Emp.sal%type; -Number of count1 per paragraph; Count2 number;  COUNT3 number;  --department's total wage saltotal Number: = 0;begin--Department open cdept;       Loop-Fetch a department fetch cdept into Pdeptno;              Exit when Cdept%notfound; --Initialization of count1:=0; count2:=0;       count3:=0;              --Get the total payroll of the Department select SUM (SAL) into the saltotal from EMP where Deptno=pdeptno;       --Take the department's Middle employee salary open cemp (PDEPTNO);            Loop-Fetch an employee's salary by fetching Cemp into psal;                        Exit when Cemp%notfound; --Judging if psal &Lt               Then count1:=count1+1;               elsif psal >=3000 and psal<6000 then count2:=count2+1;            else count3:=count3+1;       End If;       End Loop;       Close Cemp;  --Save results INSERT into MSG values (PDEPTNO,COUNT1,COUNT2,COUNT3,NVL (saltotal,0));  End Loop;    Close cdept;  Commit  Dbms_output.put_line (' Finish '); end;/

Oracle--pl/sql