Oracle--pl/sql

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.