| import java.sql.* ; |
| import java.math.* ; |
| |
| public class Payroll |
| { |
| // The ADJUSTSALARY procedure accepts an employee number and a percentage |
| // value and updates the employee's salary in the database based on that |
| // percentage. This method also returns the updated salary to an output |
| // parameter. |
| // |
| // See http://trafodion.apache.org/docs/spj_guide/index.html#adjustsalary-procedure |
| // for more documentation. |
| public static void adjustSalary( BigDecimal empNum |
| , double percent |
| , BigDecimal[] newSalary |
| ) throws SQLException |
| { |
| Connection conn = |
| DriverManager.getConnection( "jdbc:default:connection" ) ; |
| |
| PreparedStatement setSalary = |
| conn.prepareStatement( "UPDATE trafodion.persnl.employee " |
| + "SET salary = salary * (1 + (? / 100)) " |
| + "WHERE empnum = ?" |
| ) ; |
| |
| PreparedStatement getSalary = |
| conn.prepareStatement( "SELECT salary " |
| + "FROM trafodion.persnl.employee " |
| + "WHERE empnum = ?" |
| ) ; |
| |
| setSalary.setDouble( 1, percent ) ; |
| setSalary.setBigDecimal( 2, empNum ) ; |
| setSalary.executeUpdate() ; |
| |
| getSalary.setBigDecimal( 1, empNum ) ; |
| ResultSet rs = getSalary.executeQuery() ; |
| rs.next() ; |
| |
| newSalary[0] = rs.getBigDecimal( 1 ) ; |
| |
| rs.close() ; |
| conn.close() ; |
| |
| } |
| |
| // The EMPLOYEEJOB procedure accepts an employee number and returns a job |
| // code or null value to an output parameter. |
| // |
| // See http://trafodion.apache.org/docs/spj_guide/index.html#employeejob-procedure |
| // for more documentation. |
| public static void employeeJob( int empNum |
| , java.lang.Integer[] jobCode |
| ) throws SQLException |
| { |
| Connection conn = |
| DriverManager.getConnection( "jdbc:default:connection" ) ; |
| |
| PreparedStatement getJobcode = |
| conn.prepareStatement( "SELECT jobcode " |
| + "FROM trafodion.persnl.employee " |
| + "WHERE empnum = ?" |
| ) ; |
| |
| getJobcode.setInt( 1, empNum ) ; |
| ResultSet rs = getJobcode.executeQuery() ; |
| rs.next() ; |
| |
| int num = rs.getInt(1) ; |
| if ( rs.wasNull() ) |
| jobCode[0] = null ; |
| else |
| jobCode[0] = new Integer(num) ; |
| |
| rs.close() ; |
| conn.close() ; |
| |
| } |
| |
| // The PROJECTTEAM procedure accepts a project code and returns the |
| // employee number, first name, last name, and location of the employees |
| // assigned to that project. |
| // |
| // See http://trafodion.apache.org/docs/spj_guide/index.html#projectteam-procedure |
| // for more documentation. |
| public static void projectTeam( int projectCode |
| , ResultSet[] members |
| ) throws SQLException |
| { |
| Connection conn = |
| DriverManager.getConnection( "jdbc:default:connection" ) ; |
| |
| PreparedStatement getMembers = |
| conn.prepareStatement( "SELECT E.empnum, E.first_name, E.last_name, D.location " |
| + "FROM trafodion.persnl.employee E, trafodion.persnl.dept D, trafodion.persnl.project P " |
| + "WHERE P.projcode = ? " |
| + " AND P.empnum = E.empnum " |
| + " AND E.deptnum = D.deptnum " |
| ) ; |
| |
| getMembers.setInt( 1, projectCode ) ; |
| members[0] = getMembers.executeQuery() ; |
| |
| } |
| |
| // The TOPSALESREPS procedure accepts a number representing the fiscal |
| // quarter (1, 2, 3, and 4, with each number representing a range of |
| // months) and returns the employee number, first name, last name, and sale |
| // figures of the top five sales representatives who had the highest sales |
| // (unit_price * qty_ordered) that quarter. |
| // |
| // See http://trafodion.apache.org/docs/spj_guide/index.html#topsalesreps-procedure |
| // for more documentation. |
| public static void topSalesReps( int whichQuarter |
| , ResultSet[] topReps |
| ) throws SQLException |
| { |
| if ( whichQuarter < 1 || whichQuarter > 4 ) |
| { |
| throw new SQLException ( "Invalid value for quarter. " |
| + "Retry the CALL statement " |
| + "using a number from 1 to 4 " |
| + "to represent the quarter." |
| , "38001" |
| ) ; |
| } |
| |
| Connection conn = |
| DriverManager.getConnection( "jdbc:default:connection" ) ; |
| |
| PreparedStatement getTopReps = |
| conn.prepareStatement( "SELECT [first 5] e.empnum, e.first_name, " |
| + "e.last_name, totals.total " |
| + "FROM trafodion.persnl.employee e, " |
| + " ( SELECT o.salesrep, " |
| + " SUM( od.unit_price * od.qty_ordered ) as total " |
| + " FROM trafodion.sales.orders o, trafodion.sales.odetail od " |
| + " WHERE o.ordernum = od.ordernum " |
| + " AND QUARTER( o.order_date ) = ? " |
| + " GROUP BY o.salesrep " |
| + " ) totals " |
| + "WHERE e.empnum = totals.salesrep " |
| + "ORDER BY totals.total DESCENDING " |
| ) ; |
| |
| getTopReps.setInt( 1, whichQuarter ) ; |
| topReps[0] = getTopReps.executeQuery() ; |
| |
| } |
| } |