Oracle Learning Summary 5-stored procedures, stored functions, triggers

Two stored procedures and stored functions: procedure

1.hello World

Create or Replace procedure  is begin   dbms_output.put_line ('helloworld'); End Hello_world;

2. - Raise the salary for the specified employee and print the salary before the rise (stored procedure)

Create or Replace procedureRaisesalary (Enoinch  Number) ispsal emp.sal%type;begin  SelectSal intoPsal fromEmpwhereEmpno=Eno; UpdateEmpSetSal=Sal+ - whereEmpno=Eno; Dbms_output.put_line ('before the rise:'||Psal||'after the rise:'||(psal+ -));EndRaisesalary;

3. - - Query The annual income of an employee (storage function)

 Create or Replace functionQueryempincome (Enoinch  Number)return  Number  isIncome Number;begin  SelectSal* A+NVL (Comm,0) intoIncome fromEmpwhereEmpno=Eno; returnincome;EndQueryempincome;

4. - - Check the name of an employee salary and position

Create or Replace procedure inch  Number varchar2  Number varchar2  is begin       Select  into  from where empno=end queryempinformation;

5. - - java call to query the annual income of an employee (store function call)

//Store function Call@Test Public voidtestfunction () {String SQL= "{? =call queryempincome (?)}"; Connection Conn=NULL; CallableStatement Call=NULL; Try{conn=jdbcutils.getconnection (); Pager=conn.preparecall (SQL); //return Value DeclarationCall.registeroutparameter (1, Oracletypes.number); //for the in parameter, assign the valueCall.setint (2,7839); //ExecutionCall.execute (); //Output                       DoubleIncome=call.getdouble (1);        System.out.println (income); } Catch(Exception e) {//TODO auto-generated Catch blockE.printstacktrace (); }finally{jdbcutils.release (conn, call,NULL); }    }

6. - - Query the name of an employee the Java call for salary and position (stored procedure Call)

@Test Public voidTestProcedure () {String SQL= "{call Queryempinformation (?,?,?,?)}"; Connection Conn=NULL; CallableStatement Call=NULL; Try{conn=jdbcutils.getconnection (); Pager=conn.preparecall (SQL); //for the in parameter, assign the valueCall.setint (1,7839); //for Out parameters, declareCall.registeroutparameter (2, Oracletypes.varchar); Call.registeroutparameter (3, Oracletypes.number); Call.registeroutparameter (4, Oracletypes.varchar); //ExecutionCall.execute (); //OutputString name=call.getstring (2); DoubleSal=call.getdouble (3); String Job=call.getstring (4); SYSTEM.OUT.PRINTLN (Name+ "---" +sal+ "---" +job); } Catch(Exception e) {//TODO auto-generated Catch blockE.printstacktrace (); }finally{jdbcutils.release (conn, call,NULL); }    }

7. Return multiple columns-using the cursor implementation. Build the structure of the package and the body in the plsql. And then received by ResultSet in Java.

In Plsql:

Package:

Create or Replace  is is cursor;                  procedure inch  Number  end MyPackage;

Body:

Create or ReplacePackage Body MyPackage is       procedureQueryemplist (DNOinch  Number, emplist out Empcursor) as                 begin                         OpenEmplist for Select *  fromEmpwhereDeptno=DNO; End;EndMyPackage;

Java calls:

//Store function call that returns the cursor type of the@Test Public voidtestcursor () {String SQL= "{call mypackage. Queryemplist (?,?)} "; Connection Conn=NULL; CallableStatement Call=NULL; ResultSet RS=NULL; Try{conn=jdbcutils.getconnection (); Pager=conn.preparecall (SQL); //for the in parameter, assign the valueCall.setint (1,20); //for Out parameters, declareCall.registeroutparameter (2, Oracletypes.cursor); //ExecutionCall.execute (); //Remove ResultsRS= ((oraclecallablestatement) call). GetCursor (2);  while(Rs.next ()) {//Take out an employeeString name= Rs.getstring ("ename"); DoubleSal = rs.getdouble ("Sal"); SYSTEM.OUT.PRINTLN (Name+ "\ T" +Sal); }        } Catch(Exception e) {e.printstacktrace (); }finally{jdbcutils.release (conn, call, RS); }                 }

Three Trigger (Trigger)

1. Trigger when inserting an employee

Create or Replace Trigger Firsttrigger   Insert  on EMP Declare begin   dbms_output.put_line (' Insert new employee successfully '); End Firsttrigger;

2. - - not allowed to insert data during non-working hours

Create or Replace TriggerSecurityemp beforeInsert  onEMPDeclare begin  ifTo_char (Sysdate,' Day')inch('Saturday','Sunday')     orTo_number (To_char (Sysdate,'hh24')) not between 9  and  -  ThenRaise_application_error (-20001,'prohibit inserting new employees during non-working hours'); End if; EndSecurityemp;

3. - no pay cuts allowed

Create or Replace TriggerChecksalary beforeUpdate  onEMP forEach rowDeclarebegin  if: New.sal<: Old.sal ThenRaise_application_error (-20002,'A rising salary cannot be less than a pre-rising salary. Before the rise:'||: Old.sal||'after the rise:'||: New.sal); End if; EndChecksalary;

Oracle Learning Summary 5-stored procedures, stored functions, triggers