Oracle Series: (PLSQL)

Prepare the article

Col empno for 9999; Col ename for A10; Col job for A10; Col Mgr for 9999; Col hiredate for A12; Col Sal for 9999; Col Comm for 9999; Col deptno for 99; Col Tname for A40; Set pagesize 80;

1. SQL vs Plsql

What is SQL99?

(1) is a rule that operates on all relational databases

(2) is the fourth generation language

(3) is a structured query language

(4) only if a legitimate order is issued, a corresponding result is displayed.

Features of SQL

(1) strong interactivity, non-process

(2) Strong database manipulation, just send commands, no need to focus on how to achieve

(3) When multi-table operation, automatic navigation is simple, for example:

Select Emp.empno,emp.sal,dept.dname from emp,dept where Emp.deptno = Dept.deptno

(4) Easy to debug, error prompt, directly when

(5) SQL highlights results

What is Plsql?

is dedicated to Oracle server, on the basis of SQL, added some procedural control statements, called Plsql

The process consists of: type definition, judgment, loop, cursor, exception or exception handling ...

Plsql emphasizing the process

Why do you use Plsql

Because SQL is the fourth-generation imperative language and cannot display the process-oriented business, a procedural programming language is used to compensate for the shortcomings of SQL.

SQL and Plsql are not substitution relationships, they are bridging relationships

The complete composition of the Plsql program is as follows:

[DECLARE] variable declaration;    variable declaration; begin DML/TCL Operation; DML/TCL operation;    [Exception] exception handling; exception handling; end;/

Note: In the plsql program , thenumber indicates the end of each statement /Indicates the end of the entire Plsql program

The tools for writing Plsql are:

(1) Sqlplus tools

(2) Sqldeveloper tools

(3) Third party tools (Plsql & others)

What is the difference between plsql and SQL execution :

(1) SQL is executed in a single

(2) Plsql is the whole execution, not single execution, the entire plsql end with/, where each statement ends with;

2. Plsql type

Write a plsql program, output "Hello World" string, Syntax: Dbms_output.put_line (' string required to output ');

Begin--Output the string dbms_output.put_line (' Hello hello ') to the Sqlplus client tool; end;/

Attention:

Dbms_output is an output object in Oracle

Put_Line is a method of the above object that outputs a string to wrap

Set display Plsql program execution results, by default, does not display the execution result of Plsql program, Syntax: Set serveroutput on/off;

Set serveroutput on;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/87/1C/wKiom1fUkinCpbvTAABVv3ShYF8827.jpg "title=" 001. JPG "alt=" wkiom1fukincpbvtaabvv3shyf8827.jpg "/>

Use basic type variables, constants and annotations to 10+100 and

Declare--Define variable MySum number (3): = 0;    Tip VARCHAR2 (10): = ' result is '; Begin/* Business algorithm */mysum: = 10 + 100; /* Output to Controller */dbms_output.put_line (Tip | | mysum); end;/

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/87/1A/wKioL1fUk3zTGG2rAABFWF9neIg042.jpg "title=" 002. JPG "alt=" wkiol1fuk3ztgg2raabfwf9neig042.jpg "/>

Output number No. 7369 employee name and salary, in the following format: Number No. 7369 The name of the employee is Smith and the salary is 800. Syntax: Use the table name. Field%type

Declare-Define two variables, name and salary Pename Emp.ename%type respectively;    Psal emp.sal%type;begin--sql statement--select ename,sal from emp where empno = 7369;    --plsql statement, put the value of ename into the pename variable, the value of Sal into the PSAL variable in the Select Ename,sal into Pename,psal from emp where empno = 7369; --Output Dbms_output.put_line (' No. No. 7369 employee's name is ' | | pename| | ', Salary is ' | |    PSAL); end;/

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/87/1C/wKiom1fUlIvjU5bSAABhI0WVgfE232.jpg "title=" 003. JPG "alt=" wkiom1fulivju5bsaabhi0wvgfe232.jpg "/>

Output number No. 7788 employee name and salary, in the following format: Number No. 7788 The name of the employee is Smith, the salary is 3000, syntax: Use the table name%rowtype

DECLARE Emp_record emp%rowtype;begin SELECT * into Emp_record from emp where empno = 7788; Dbms_output.put_line (' The name of Employee No. No. 7788 is ' | | emp_record.ename| | ', Salary is ' | | Emp_record.sal); end;/

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/87/1C/wKiom1fUlo3yDZVGAABom-tuo3c402.jpg "title=" 004. JPG "alt=" wkiom1fulo3ydzvgaabom-tuo3c402.jpg "/>

When to use%type, when to use%rowtype?

When you define a variable, the type of the variable is the same as the type of a field in the table, and you can use the%type

When you define a variable that is exactly the same as the entire table structure, you can use%rowtype, at which point the variable name can be used to take the corresponding value in the value variable.

In the project, common%type

3. Plsql Judgment

Use If-else-end if to show today's Day of the week, "weekday" or "Day Off"

DECLARE Pday VARCHAR2 (ten); Begin select To_char (sysdate, ' Day ') to pday from dual; Dbms_output.put_line (' Today is ' | |    Pday);    If Pday in (' Saturday ', ' Sunday ') then Dbms_output.put_line (' rest day ');    else Dbms_output.put_line (' weekday '); End if;end;/

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M00/87/1A/wKioL1fUmN6zvWOUAABxKO1wkQk816.jpg "title=" 005. JPG "alt=" wkiol1fumn6zvwouaabxko1wkqk816.jpg "/>

Receive values from the keyboard, use If-elsif-else-end if to display "age<16", "age<30", "age<60", "age<80"

Declare age number (3): = &age;begin If < and then Dbms_output.put_line (' You are not an adult ');    Elsif Age < Dbms_output.put_line (' You young people ');    Elsif Age < Dbms_output.put_line (' You fight people ');    Elsif Age < Dbms_output.put_line (' you enjoy people ');    else Dbms_output.put_line (' not finished again '); End if;end;/

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/87/1C/wKiom1fUmlujds98AAB0ewN2Zgs788.jpg "title=" 006. JPG "alt=" wkiom1fumlujds98aab0ewn2zgs788.jpg "/>

4. Plsql Cycle

Using the loop loop to display 1-10

Declare i number (2): = 1;begin loop--when i>10 exits the loop exit when i>10;        --The value of the output I dbms_output.put_line (i);      --Variable self plus i: = i + 1; End loop;end;/

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/87/1A/wKioL1fUm0qzuTEuAABFsdlqIWc599.jpg "title=" 007. JPG "alt=" wkiol1fum0qzuteuaabfsdlqiwc599.jpg "/>

Using the while loop to display 1-10

Declare i number (2): = 1;begin while i<11 loop dbms_output.put_line (i);    I: = i + 1; End loop;end;/

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/87/1A/wKioL1fUm-7RjeLvAABCpMHB7sI558.jpg "title=" 008. JPG "alt=" wkiol1fum-7rjelvaabcpmhb7si558.jpg "/>

Insert 999 records into the EMP table using the while loop

Declare i number (4): = 1;begin while (i < n) loop insert into EMP (empno,ename) VALUES (i, ' haha ');    I: = i + 1;   End Loop; end;/

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/87/1C/wKiom1fUnwCzZ9KhAABJzcaUWq4357.jpg "title=" 009. JPG "alt=" wkiom1funwczz9khaabjzcauwq4357.jpg "/>

Delete 999 records from the EMP table using the while loop

Declare i number (4): = 1;begin while i<1000 loop delete from emp where empno = i;    I: = i + 1; End loop;end;/

displaying 20-30 with a For loop

Declare i number (2): = 20;begin for I in 20..    Loop Dbms_output.put_line (i); End loop;end;/

5. Plsql Cursors

What is cursor/cursor/cursor

Similar to the functionality of the ResultSet object in JDBC, gets the contents of each record from top to bottom

Use the cursor cursor with no parameters to query the names and wages of all employees "if you need to traverse multiple records, use cursors, no records found using Cemp%notfound"

declare    --defining Cursors     cursor  cemp is select ename,sal from emp;    --Defining Variables      vename emp.ename%type;    vsal   emp.sal%type;begin     --Open the cursor when the cursor is positioned before the first record     open cemp;    --loop      loop       --move cursor down one time         fetch cemp into vename,vsal;        --exit Loop, Exits the loop when a record is not found after the cursor is moved down one time        exit when cemp%notfound;        --output Results        dbms_output.put_line (vename| | ' --------' | | vsal);     end loop;    --Close cursor     close  cemp;end;/

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/87/1A/wKioL1fUoTjD7VBBAACxqVXKSps634.jpg "title=" 010. JPG "alt=" wkiol1fuotjd7vbbaacxqvxksps634.jpg "/>

Use the Cursors cursor to query the employee name and salary in department number 10th

DECLARE CURSOR cemp (Pdeptno emp.deptno%type) is a select ename,sal from emp where Deptno=pdeptno;    Pename Emp.ename%type; Psal Emp.sal%type;    Begin open Cemp (&DEPTNO);         Loop fetch cemp into pename,psal;        Exit when Cemp%notfound; Dbms_output.put_line (pename| | ' The salary is ' | |    PSAL);    End Loop; Close cemp;end;/

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M01/87/1C/wKiom1fUpTTgyfD_AACqLI4V2Jo010.jpg "style=" float: none; "title=" 011.jpg "alt=" Wkiom1fupttgyfd_aacqli4v2jo010.jpg "/>

The dynamic diagram is as follows:

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/87/1C/wKiom1fUpTXjzUiQAAFhr8AArGw798.gif "style=" float: none; "title=" 011.gif "alt=" Wkiom1fuptxjzuiqaafhr8aargw798.gif "/>

Use the cursors cursor, the real salary increase for employees, analyst Rose 1000,manager 800, the other Rose 400, asked to show the number, name, position, salary

declare    cursor cemp is select empno,ename,job,sal from  emp;    pempno emp.empno%type;    pename emp.ename%type;     pjob   emp.job%type;    psal    emp.sal%type;begin    open cemp;    loop         fetch cemp into pempno,pename,pjob,psal;         --Loop exit condition Be sure to write         exit when cemp% notfound;        if pjob= ' ANALYST '  then             update emp set sal = sal +  1000 where empno = pempno;        elsif  pjob= ' MANAGER '  THEN &Nbsp;          update emp set sal =  sal + 800 where empno = pempno;         Else     update emp set sal = sal + 400 where  empno = pempno;        end if;     end loop;    commit;    close cemp;end;/

6. Plsql exception

Use the Oracle system built-in exception to demonstrate 0 exceptions to "zero_divide"

DECLARE myresult number;begin myresult: = 1/0;         Dbms_output.put_line (myresult); exception when Zero_divide then Dbms_output.put_line (' divisor cannot be 0 ');  Delete from EMP; end;/

Use the Oracle system built-in exceptions to query employee names in department 100th, demonstrating no data found "No_data_found"

DECLARE pename varchar2 (); Begin select Ename into Pename from emp where deptno = 100;         Dbms_output.put_line (pename); exception when No_data_found then Dbms_output.put_line (' No employee of the department '); INSERT into EMP (empno,ename) VALUES (1111, ' ERROR '); end;/

Use the user-defined exception, use cursor cursors, query the employee name of the 10/20/30/100 department, demo no data found "Nohave_emp_found"

Declare  cursor cemp (Pdeptno number)  is select ename from emp  where deptno=pdeptno;  nohave_emp_found exception;  pename emp.ename% type;begin  --the cursor is opened,   open cemp (&XX);  --  moves the cursor down before the first record. Point to first record   fetch cemp into pename;  --  judge    if cemp% notfound then     --  Throw Exception      raise nohave_emp _found;  else     --  value of output variable Pename        Dbms_output.put_line (pename);     --  cycle      loop        --  moves the cursor down once, pointing to the second record        fetch  cemp into pename;       --  If no record is found, exit        &nBsp;exit when cemp%notfound;       dbms_output.put_line (Pename);      end loop;  end if;    close cemp; Exception  when nohave_emp_found then    dbms_output.put_line (' No staff in this department '); end;/

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/87/1D/wKiom1fUrIaxnpGaAADT7DYjcEw819.jpg "title=" 012. JPG "alt=" wkiom1furiaxnpgaaadt7dyjcew819.jpg "/>

Oracle Series: (PLSQL)