Oracle Plsql:

Plsql:
Symbol:
Assignment value:: =

Basic example:

         set serveroutput on   // sets the output to open.         Declare          -- description section                  begin          -- program          dbms_output.put_line ('  HelloWorld');        Exception                end;         /

Reference variable:
--Query 7839 's name and salary

--Check 7839 's name and salarySetServeroutput on Declare--Define variables Save name and salary--pename VARCHAR2 ( -); --psal number; Pename Emp.ename%type; Reference-type variable psal emp.sal%type; Begin--get name and salarySelectEname,sal into Pename,psal fromEmpwhereempno=7839; Dbms_output.put_line (Pename||''s salary is'||psal);        End /

Record type variable (represents a row, multiple columns, similar arrays)
--Query 7839 's name and salary

        Set serveroutput on        declare          -- defines the record type variable: Represents a line          emp_rec emp%rowtype;        Begin          Selectfromwhere empno=7839;          Dbms_output.put_line (Emp_rec.ename| | ' 's salary is '| | emp_rec.sal);        End;         /

If statement
--Determine the number the user has entered from the keyboard

        SetServeroutput on--receive keyboard input--num: Address value, where the input value is saved on the address accept NUM prompt'Please enter a number';//Accept CommandDeclare--define the variable to hold the input number Pnum numbers:= #//remove the numbers from the address use &beginifPnum =0Then Dbms_output.put_line ('you entered the 0'); elsif Pnum=1Then Dbms_output.put_line ('you entered the 1'); elsif Pnum=2Then Dbms_output.put_line ('you entered the 2'); ElseDbms_output.put_line ('Other numbers'); Endif;        End /

Loop statements (there are three ways, one for example here):
--Print 1~10

        Set serveroutput on        declare          pnum number:1;        Begin          Loop            - exit            condition ten;                        Dbms_output.put_line (pnum);             -- plus one            pnum:1;          End Loop;        End;         /

Cursor: Cursors (quite a resultset, result set):
1. Properties of the cursor:
%isopen%rowcount (number of rows affected)
%found%notfound
2. By default, only 300 cursors can be opened in a session
Show parameter cursor//view parameters---> Back two it's all here.
Modified: Alter System/session set open_cursors=400;

3. Cursor_sharing---> Performance optimization
EXACT (default), force (lift performance), SIMILAR

Example:
--Query and print employee's name and salary (without parameters)

            SetServeroutput on Declare--Define cursor Cemp is SelectEname,sal fromEmp//define a cursorPename emp.ename%type; Psal emp.sal%type; Begin--opens the cursor open cemp; //Open a cursorLoop--take a record fetch cemp into pename,psal; //Take out a record--Exit Criteria--exit when the record is not taken; Exit when Cemp%NotFound; Dbms_output.put_line (Pename||''s salary is'||psal);                            End Loop; --close cursor close cemp; //Close Cursorend; /

Example two (with parameters):
--Query the employee name of a department

            Set serveroutput on            declare              wasselectfromwhere deptno=dno;              Pename emp.ename%type;            Begin              Open Cemp (a);     // Pass the reference               Loop                fetch cemp into pename;                Exit when Cemp%notfound;                                Dbms_output.put_line (pename);              End Loop;              Close Cemp;            End;             /

Exception (Exception)
System Exceptions:
No_data_found (no data found)
Too_many_rows (SELECT INTO statement matches multiple rows)
Zero_divide (divide by 0)
Value_error (arithmetic or conversion exception)
Timeout_on_resource (Waiting for resource timeout)

--by 0 except
　　　　

                SetServeroutput on declare pnum number; Begin Pnum:=1/0; Exception when Zero_divide then Dbms_output.put_line ('1:0 cannot do the denominator'); Dbms_output.put_line ('2:0 cannot do the denominator'); When Value_error then Dbms_output.put_line ('arithmetic or conversion errors'); When others then Dbms_output.put_line ('Other Exceptions');                End /

Custom Exceptions:
--Check the name of the employee in Department No. 50th

                Setserveroutput on DECLARE cursor cemp is SelectEname fromEmpwheredeptno= -; Pename Emp.ename%type; --Custom Exception No_emp_found exception; //define a variable of an exception typebegin open Cemp; --take the first record fetch cemp into pename; ifcemp%NotFound Then--throws exception raise No_emp_found; //Throw ExceptionsEndif; --pmon:process Monitor Close cemp; Exception//general processing is saved to a tableWhen No_emp_found then Dbms_output.put_line ('No employees found'); When others then Dbms_output.put_line ('Other Exceptions');                End /

Oracle Plsql: