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