Oracle Notes 4-pl/sql-Branches/loops/cursors/exceptions/storage/calls/triggers

I. PL/SQL (Procedure Language/sql) programming language
1. Concept
PL/SQL is an extension of the Oracle database to the statement. In the use of ordinary SQL statements to add the characteristics of the programming language, so PL-SQL to the data Operations and query statements organized in the PL/SQL code of the process unit, through logical judgment, loop and other operations to achieve complex functions or calculations. PL/SQL is available only for Oracle databases. MySQL currently does not support PL/SQL.

2. Variables and Constants
Declare Common variables:
V_name varchar2 (+): = ' Tom '; (: = is an assignment symbol; = is a comparison symbol, equivalent to = = in Java);
Declaring a reference-type variable:
V_sal emp.sal%type: = 100; The declared v_sal variable is consistent with the type of Sal field in the EMP table;
Declaring a record-type variable:
V_row Emp%rowtype; The record type variable is equivalent to the ResultSet in Java, which is used to store the data in the whole table;
Declaring constants:
V_GENDER constant Number (1) Number (1): = 1;

3. Branch statement
Syntax one: if---then---end if;
Syntax two: If---then---else---end if;
Syntax Three: if---then---elsif---then----else----End if;
Example:
-age less than 18, show minors, 18-60, show adults, more than 60 show older people
declare
     v_age number (8):=& Num
Begin
     if v_age < then
         DBMS _output.put_line (' Minors ');
     elsif v_age >= and V_age <= then
          Dbms_output.put_line (' adults ');
     Else
         dbms_output.put_line (' Seniors ' );
     End If;
End;

4. Looping statements
Syntax One: Loop---exit when----end loop;
Example:
--Number of output 1--100
Declare
V_num Number (8): = 1;
Begin
Loop
Exit when V_num > 100;
Dbms_output.put_line (V_num);
V_num: = V_num + 1;
End Loop;
End

Syntax two: while---loop----end loop;
Declare
V_num Number (8): = 1;
Begin
While V_num <= loop
Dbms_output.put_line (V_num);
V_num: = V_num + 1;
End Loop;
End

Syntax three: for---in start value ... Termination value---loop---end loop;
Declare
V_num Number (8): = 1;
Begin
For V_num in 1.. Loop
Dbms_output.put_line (V_num);
End Loop;
End

5. Cursor
Function: Used to receive multiple data results, equivalent to resultset
syntax in Java: CURSOR cursor name is SQL query statement;
Use:
     open cursor name
     loop
          FETCH cursor name into record type variable
         exit when cursor name%notfound;
             logic processing
     End Loop
     close cursor name;
Example:
-Print all information for the EMP table
DECLARE
  CURSOR c_emp is SELECT * from EMP;
  V_row Emp%rowtype;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp into V_row;
          EXIT when C_emp%notfound;
          dbms_output.put_line (v_row.ename| | ') --' | | V_row.job);
   END LOOP;
   CLOSE c_emp;
END;

--Cursors with parameters
DECLARE
CURSOR c_emp (v_no1 number, V_no2 number) is a SELECT * from emp WHERE deptno = v_no1 OR deptno = V_no2;
V_row Emp%rowtype;
BEGIN
OPEN c_emp (10,20);--Incoming department number Deptno
LOOP
FETCH c_emp into V_row;
EXIT when C_emp%notfound;
Dbms_output.put_line (v_row.ename| | ' = = ' | | V_row.job);
END LOOP;
CLOSE c_emp;
END;

6. Exceptions
Exception---when----then
--pre-defined exceptions
DECLARE
V_num number (3);
BEGIN
V_num: = 10000;
EXCEPTION
When Value_error Then
V_num: = 999;
Dbms_output.put_line (V_num);
END;

--Custom exceptions
DECLARE
V_age Number (8): = &NUM;
Exc_age EXCEPTION; --Declaring exceptions
BEGIN
IF v_age >
RAISE Exc_age;
END IF;
EXCEPTION
When Exc_age Then
Raise_application_error ( -20001, ' illegal age ');
END;

Two. Stored Procedures
Concept: A named Plsql, pre-compiled into the database
Grammar:
Create or Replace procedure stored procedure name (parameter 1 [in]/out data type)
As | Is
Begin

End
Example 1:
--A stored procedure that prints the annual salary of a specified employee
Create or Replace procedure Pro_emp_sal (v_no number) is
V_sal Number (8, 2);
Begin
Select Sal * + NVL (comm, 0) into the v_sal from emp where empno = V_no;
Dbms_output.put_line (v_sal);
End
--method One calls the stored procedure
Call Pro_emp_sal (7788);
--Method Two calls the stored procedure
Begin
Pro_emp_sal (7788);
End

Example 2: Stored procedure with out parameters
CREATE OR REPLACE PROCEDURE pro_emp_sal2 (v_no number, v_yearsal out number) is
BEGIN
SELECT sal*12 + NVL (comm,0) to v_yearsal from emp WHERE empno = v_no;
END;
--only use mode two calls
DECLARE
V_sal number (8,2);
BEGIN
Pro_emp_sal2 (7788,v_sal);
Dbms_output.put_line (v_sal);
END;

Three. Storage functions
--Storage function
CREATE OR REPLACE FUNCTION fun_emp_sal (v_no number)
RETURN number
Is
V_sal number (8,2);
BEGIN
SELECT SAL*12+NVL (comm,0) to v_sal from emp WHERE empno = v_no;
RETURN v_sal;
END;
--Using Stored functions
BEGIN
Dbms_output.put_line (Fun_emp_sal (7788));
END;

Note: The difference between stored procedures and stored functions
1. Different syntax
2. Usage scenarios: General storage functions are used by stored procedures, and stored procedures typically use data interactions between projects and projects
3. Storage functions can be used directly in SQL, and stored procedures cannot
Select Ename,sal,func_emp_sal (empno) from EMP;

Four. Calling stored procedures and stored functions using JDBC
1.BaseDao for loading drives and getting connections
2.ProcedureDao for calling stored procedures
3.TestDao for testing

Example:
1.BaseDao for loading drives and getting connections
public class Basedao {
Load Driver
static{
try {
Class.forName ("Oracle.jdbc.OracleDriver");
} catch (ClassNotFoundException e) {
E.printstacktrace ();
}
}

Get connections
public static Connection Getconn () throws sqlexception{
String url= "JDBC:ORACLE:THIN:@192.168.92.8:1521:ORCL";
String user= "Qin";
String password= "Qin";
Return drivermanager.getconnection (URL, user, password);
}

public static void CloseAll (ResultSet rs,statement stmt,connection conn) {
if (rs!=null) {
try {
Rs.close ();
} catch (SQLException e) {
E.printstacktrace ();
}
}
if (stmt!=null) {
try {
Stmt.close ();
} catch (SQLException e) {
E.printstacktrace ();
}
}
if (conn!=null) {
try {
Conn.close ();
} catch (SQLException e) {
E.printstacktrace ();
}
}
}
}

2.ProcedureDao for calling stored procedures
public class Proceduredao {
public static long Getsal (long v_no) {
Connection conn = null;
CallableStatement Stmt=null;
Long yearsal=0l;

try {
Conn=basedao.getconn ();
stmt = Conn.preparecall ("Call Pro_emp_sal2 (?,?)"); /Call Stored Procedure
Stmt.setlong (1, v_no);
Stmt.registeroutparameter (2, Oracletypes.number); Specifying the data type of a parameter
Stmt.execute ();
Yearsal = Stmt.getlong (2);
} catch (SQLException e) {
E.printstacktrace ();
}
return yearsal;
}
}

3.TestDao for testing
public class Testdao {
public static void Main (string[] args) {
Cursordao.getemp (10l);
}
}

Five. Triggers
1.--creating triggers to add data-raising actions
CREATE OR REPLACE TRIGGER tri_add_emp
After
INSERT on EMP

BEGIN
Dbms_output.put_line (' added a piece of data ');
END;

--Add a piece of data to see if it triggers
INSERT into EMP (EMPNO,ENAME,DEPTNO) VALUES (1, ' Tom ', 10);

2.--system time-triggered triggers
CREATE OR REPLACE TRIGGER tri_emp
Before
DELETE or UPDATE or INSERT
On EMP
For each ROW

DECLARE
V_datestr VARCHAR2 (20);
BEGIN
SELECT to_char (sysdate, ' yyyy-mm-dd ') into the v_datestr from dual;
IF v_datestr = ' 2017-09-20 ' Then
Raise_application_error (-20002, ' system maintenance today ');
END IF;
END;

--Tests whether triggers can be triggered
INSERT into EMP (EMPNO,ENAME,DEPTNO) VALUES (3, ' Jerry ', 10);

Six. Delete Data recovery statements by mistake
CREATE TABLE Tablename_bak
As
SELECT * from TableName as of TIMESTAMP to_timestamp (' 20081126 103435 ', ' yyyymmdd Hh24miss ');

Oracle Notes 4-pl/sql-Branches/loops/cursors/exceptions/storage/calls/triggers