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