PL/SQL Programming of Oracle Learning Notes

Definition of 1.pl/sql:

PL/SQL (Procedure language/sql)

Plsql is Oracle's procedural extension of SQL language

Refers to the addition of process-processing statements (such as branching, loops, and so on) in the SQL command language, enabling the SQL language to have process processing power.

The data manipulation ability of SQL language is combined with the data processing ability of process language, which makes plsql more simple, efficient, flexible and practical than the process language.

Plsql (Oracle), Transact-SQL SQL Server

2. Starter Program--Print Hello world;

Set Serveroutput on;--declares a variable if there is no declare--to start begindbms_output.put_line (' Hello world! '); --end must be preceded by a semicolon end;/

Operation Result:

Note: If you want to output information on the screen, you need to turn the serveroutput switch on:

3. Declaring variables

1) Way One:

Set Serveroutput On;declare  -declares a generic variable, you can specify the initial value, note that you want to use: =  vname emp.ename%type;--and the types of variables in the database are consistent  Vsal Number: =0;--is declared as type "begin"-   -assigns the result of the query to the variable  select Ename,sal to Vname,vsal from the EMP where empno=7839;  --Show Results  dbms_output.put_line (vname| | ') The salary is ' | | Vsal); end;/

Operation Result:

2) mode two

Set Serveroutput on;accept empno prompt ' Please enter employee number ';d eclare  --Declare record variable  rec_emp emp%rowtype;begin  -- Assign the result of the query to the variable  select * into  rec_emp from EMP where empno=&empno;  Dbms_output.put_line (rec_emp.ename| | ' The salary is ' | | Rec_emp.sal); end;/

Operation Result:

4.if

Set serveroutput on;accept num prompt ' Please input number ';d eclare    --Define variable    vnum numbers;    Vstr VARCHAR2 (5); Begin  Vnum:= #  If vnum>0 then vstr:= ' positive number ';  elsif vnum<0 then vstr:= ' negative ';  else vstr:= ' 0 ';  End If;  Dbms_output.put_line (VSTR); end;/

5. Cycle

1) Cycle mode one

Set Serveroutput onbegin for   i in 1..10  loop    --loop body content    dbms_output.put_line (i);  End loop;end;/

2) Cycle mode Two (this way more commonly used)

Set serveroutput on;declarevnum number: =1;--initialize variable begin  Loop    --Exit loop condition exit when    vnum>10;    Dbms_output.put_line (vnum);    --Add a    vnum:=vnum+1;  End loop;--Loop end;/

3) Circulation mode three:

Set serveroutput on;declarevnum number: =1;begin  -When the condition is set, the loop body is executed while  vnum<10  loop    dbms_ Output.put_line (vnum);    --Add a    vnum:=vnum+1;  End loop;end;/

Operation Result:

6. Cursor

Set Serveroutput on;/* Requirements: Displays the total number of employees and the names and wages of all employees * Select Ename,sal from Emp;*/declare   --declaration cursors Cursor  c_emp is select Ename,sal from EMP;  --Define the variable  vcount number;  VName Emp.ename%type;  Vsal number;begin  --Query and display the total number of employees  select COUNT (*) into the vcount from EMP;  Dbms_output.put_line (' Total number of employees: ' | | Vcount);  --To use the cursor requires opening the cursor open  c_emp;  --Loop loops    --First take out a row of records    fetch c_emp into vname,vsal;    --Specify the conditions for exiting the loop exit when    C_emp%notfound;    Dbms_output.put_line (vname| | ' The wages are ' | | Vsal);  End Loop;    --closing cursor close  c_emp;end;/

Operation Result:

Note: The results of the query here are consistent with the results of the CMD command line query

7. Cursor Application case: raise salary for employees

Set Serveroutput on--requirements: According to the employee's work wage increase, President (President) 1000, Manager (manager) 800 Other employees 400--analysis: Take out all employees, each employee on the line to judge, what conditions, How much is the wage--select empno,empjob from emp;--update emp set sal=sal+? where Empno=?declare  --Defines a cursor (a collection of all employees)  cursor  c_emp is select Empno,empjob from emp;  --Define the variables associated with the cursor  vempno emp.empno%type;  Vempjob emp.empjob%type;begin  open c_emp;    Loop      -Get data      fetch c_emp into vempno,vempjob;      Exit when c_emp%notfound;--specifies the condition to exit the loop-      -determine what the situation is,      if vempjob= ' president ' then update EMP set  sal= sal+1000 where Empno=vempno;      elsif vempjob= ' MANAGER ' then update emp set  sal=sal+800 where empno=vempno;      Else update emp set  sal=sal+400 where empno=vempno;      End if;--ends if judgment end    loop;  Close c_emp;  Commit --Commit the transaction  dbms_output.put_line (' execution complete '); end;/

Operation Result:

8. Cursors with parameters

Set Serveroutput on;--Requirements: Displays the total number of employees in department 10th, and the name and payroll information for each employee declare  --defines the cursor  c_emp (DNO number) is select Ename, Sal from EMP where Deptno=dno;  --Define the variables associated with the cursor  vename emp.ename%type;  Vsal Emp.sal%type;  Vdeptno Emp.deptno%type;  Vcount Number;begin  --Get query conditions  vdeptno:=&input_deptno;  --Displays the total number of employees in the Department  Select COUNT (*) into the vcount from EMP where Deptno=vdeptno;  Dbms_output.put_line (' Total number of employees: ' | | Vcount);  --Opens the cursor open  c_emp (vdeptno);    Loop      fetch c_emp into vename,vsal;      Exit when C_emp%notfound;      Dbms_output.put_line (vename| | ' The wages are ' | | Vsal);    End Loop;  Close c_emp;--the cursor end;/

Operation Result:

9. Exceptions

Set Serveroutput ondeclare   vnum number;begin   vnum:=1/0;  --handling Exception  exception when    zero_divide then Dbms_output.put_line (' exception: by 0 '); end;/

Operation Result:

10. Custom Exceptions

Set Serveroutput on;--Custom Exception declare  -declares an exception type  my_exception exception; begin   -Throws an exception  raise My_ Exception;  Exception    --Exception handling when    My_exception and then Dbms_output.put_line (' Custom exception '); end;

Operation Result:

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

PL/SQL Programming of Oracle Learning Notes