Author: Kentpeng  

For reprinting, please specify the source: http://www.cnblogs.com/xieyupeng/p/7476717.html 

1. Cursor (Cursor)

If you have the concept of collections in your java program, then you will also use multiple records in pl/sql, at which point we will use cursors, which can store multiple pieces of data returned by queries.

Grammar:

CURSOR cursor name [(parameter name data type, parameter name data type,...)] IS SELECT statement;

For example: cursor c1 is select ed ename from emp;

Steps to use cursors:

• Open cursor: (Openc1; (Open cursor to execute query)
• Take a row of cursor values: fetch c1 into pjob; (Take a row into a variable)
• Close cursor: * close c1; (Close cursor to release resources)
• Cursor end mode exit when c1%notfound
• Note: The pjob above must match the job column type in the emp table:

Definition: pjob emp.empjob%type;

Cursor property:%isopen%rowcount (number of rows affected)%found%notfound

Example 1: Use a cursor to output employee numbers and names in the emp table

declare

  cursor pc is

    select * from emp;

  pemp emp%rowtype;

begin

  open pc;

  loop

    fetch pc

      into pemp;

    exit when pc%notfound;

    dbms_output.put_line(pemp.empno || ' ' || pemp.ename);

  end loop;

  close pc;

end;

Example 2: Write a PL/SQL program to raise salaries for employees with department number 10.

declare

  cursor pc(dno myemp.deptno%type) is

    select empno from myemp where deptno = dno;

  pno myemp.empno%type;

begin

  open pc(20);

  loop

    fetch pc

      into pno;

    exit when pc%notfound;

    update myemp t set t.sal = t.sal + 1000 where t.empno = pno;

  end loop;

  close pc;

end;

2. Exceptions

Exceptions are a function provided by programming languages to enhance the robustness and fault tolerance of programs.

System Definition Exception

no_data_found (no data found)

too_many_rows (select...into statement matches multiple lines)

zero_divide (divide by zero)

value_error (arithmetic or conversion error)

timeout_on_resource (timeout occurs while waiting for a resource)

Example 1: Write a plsql program that excepts 0

declare

  pnum number;

begin

  pnum := 1 / 0;

exception

  when zero_divide then

    dbms_output.put_line('Divide by 0');

  when value_error then

    dbms_output.put_line('Numeric conversion error');

  when others then

    dbms_output.put_line('Other Errors');

end;

Users can also customize exceptions to define them in declarations

DECLARE

My_job   char(10);

v_sal   emp.sal%type;

No_data    exception;

cursor c1 is select distinct job from emp    order by job;

If we encounter an exception we throw raise no_data;

Example 2: Query employees whose department number is 50

declare

  no_emp_found exception;

  cursor pemp is

    select t.ename from emp t where t.deptno = 50;

  pename emp.ename%type;

begin

  open pemp;

  fetch pemp

    into pename;

  if pemp%notfound then

    raise no_emp_found;

  end if;

  close pemp;

exception

  when no_emp_found then

    dbms_output.put_line('No employees found');

  when others then

    dbms_output.put_line('Other Errors');

end;

3. Stored Procedures

Stored Procedure is a set of SQL statements that are compiled to perform a specific function in a large database system and are executed by the user by specifying the name of the stored procedure and giving parameters if the stored procedure has parameters.Stored procedures are an important object in a database, and should be used by any well-designed database application.

Create stored procedure syntax:

create [or replace] PROCEDURE Procedure name[(Parameter Name in/out data type)]  

AS

begin

        PLSQL Subprogram body;

End;

perhaps

create [or replace] PROCEDURE Procedure name[(Parameter Name in/out data type)]  

is

begin

        PLSQL Subprogram body;

End  Procedure name;

Example 1: Increase the specified employee by 100 salaries and print out the pre- and post-increase salaries

Analysis: We need to use stored procedures with parameters

create or replace procedure addSal1(eno in number) is

  pemp myemp%rowtype;

begin

  select * into pemp from myemp where empno = eno;

  update myemp set sal = sal + 100 where empno = eno;

  dbms_output.put_line('Before wage increase' || pemp.sal || 'After wage increase' || (pemp.sal + 100));

end addSal1;

call

begin

  -- Call the procedure

  addsal1(eno => 7902);     

  commit;

end;

4. Storage Functions

create or replace function function name (Name in type, Name out type,...) return data type is

  Result variable data type;

begin

  

  Return (result variable);

end [function name];

Differences between stored procedures and stored functions

Generally speaking, the difference between a procedure and a function is that a function can have a return value; a procedure does not have a return value.(

Procedures and functions can, however, specify one or more output parameters through out.We can use the out parameter to return multiple values in processes and functions.

Example: Use a storage function to query the annual salary of a specified employee

create or replace function empincome(eno in emp.empno%type) return number is

  psal  emp.sal%type;

  pcomm emp.comm%type;

begin

  select t.sal into psal from emp t where t.empno = eno;

  return psal * 12 + nvl(pcomm, 0);

end;

Replace the above example with a stored procedure

create or replace procedure empincomep(eno in emp.empno%type, income out number) is

  psal emp.sal%type;

  pcomm emp.comm%type;

begin

  select t.sal, t.comm into psal, pcomm from emp t where t.empno = eno;

  income := psal*12+nvl(pcomm,0);

end empincomep;

Call:

declare

  income number;

begin

  empincomep(7369, income);

  dbms_output.put_line(income);

end;

5. Triggers

A database trigger is a stored PL/SQL program associated with a table.Oracle automatically executes the sequence of statements defined in a trigger whenever a specific data action statement (Insert,update,delete) is issued on a specified table.(

1. Trigger action

l. Data Confirmation

l. Implement complex security checks

l. Auditing, tracking data operations on tables, etc.

l. Backup and synchronization of data

2. Type of trigger

Statement-level trigger: Executes the specified action statement before or after the action, regardless of how many lines the statement affects.(

Row-level trigger (FOR EACH ROW): Every record that triggers the action of a statement is triggered.Use old and new pseudo-record variables in the row-level toucher to identify the state of values.(

Grammar:

CREATE  [or REPLACE] TRIGGER  Trigger Name

   {BEFORE | AFTER}

   {DELETE | INSERT | UPDATE [OF Column Name]}

   ON  Table Name

   [FOR EACH ROW [WHEN(condition) ] ]

declare

    ......

begin

   PLSQL block 

End Trigger Name

Example: After inserting an employee, print a sentence "A new employee inserts successfully"

create or replace trigger testTrigger

  after insert on person  

declare

  -- local variables here

begin

  dbms_output.put_line('An employee was inserted');

end testTrigger;

Example: Employees cannot be inserted during breaks

create or replace trigger validInsertPerson

  before insert on person

 

declare

  weekend varchar2(10);

begin

  select to_char(sysdate, 'day') into weekend from dual;

  if weekend in ('Monday') then

    raise_application_error(-20001, 'Cannot insert employee at illegal time');

  end if;

end validInsertPerson;

Error when inserting

Trigger statements and values of pseudo record variables in triggers

Example: Determine that the value of an employee's salary after an increase must be greater than that before the increase

create or replace trigger addsal4p

  before update of sal on myemp

  for each row

begin

  if :old.sal >= :new.sal then

    raise_application_error(-20002, 'Wages before increases must not be greater than those after increases');

  end if;

end;

call

update myemp t set t.sal = t.sal - 1;

3. Practical application of triggers

Requirements: Use sequences, triggers to simulate self-increasing effects in mysql

1. Create Sequence

1. Establishing tables

Copy the code as follows:

create table user  (   

    id   number(6) not null,   

    name   varchar2(30)   not null primary key  

)  

2. Establish Sequence SEQUENCE

The code is as follows:

create sequence user_seq increment by 1 start with 1 minvalue 1 maxvalue 9999999999999 nocache order;

2. Create self-increasing triggers

Analysis: Create a before insert trigger based on this table that uses the newly created SEQUENCE.

The code is as follows:

create or replace trigger user_trigger   

before insert on user  

for each row   

begin  

      select   user_seq.nextval  into:new.id from sys.dual ;   

end;  

3. Test results

insert into itcastuser(name) values('aa');

commit;

insert into itcastuser(name) values('bb');

commit;

5.Java code access to Oracle objects

1. jar package for Java connection to oracle

The jar package can be found in the oracle installation directory of xp in the virtual machine: ojdbc14.jar

2. Database Connection String

String driver="oracle.jdbc.OracleDriver";

String url="jdbc:oracle:thin:@192.168.56.10:1521:orcl";

String username="scott";

String password="tiger";

Test code:

3. Calls to Implement Procedures and Functions

1. Call Procedure

1. Process Definition

--The process of calculating annual pay

create or replace procedure proc_countyearsal(eno in number,esal out number)

as

begin

   select sal*12+nvl(comm,0) into esal from emp where empno=eno;

end;

 

--call

declare

   esal number;

begin

   proc_countyearsal(7839,esal);

   dbms_output.put_line(esal);

end;

2. Procedure Calls

    @Test
    public void testProcedure01() {

        String driver = "oracle.jdbc.OracleDriver";

        String url = "jdbc:oracle:thin:@192.168.56.10:1521:orcl";

        String username = "scott";

        String password = "tiger";

        try {

            Class.forName(driver);

            Connection con = DriverManager.getConnection(url, username, password);

            CallableStatement callSt = con.prepareCall("{call proc_countyearsal(?,?)}");

            callSt.setInt(1, 7839);

            callSt.registerOutParameter(2, OracleTypes.NUMBER);

            callSt.execute();

            System.out.println(callSt.getObject(2));

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

2. Call functions

1. Function Definition

--Functions for Statistical Annual Pay

create or replace function fun_countyearsal(eno in number)

return number

as

   esal number:=0;

begin

  select sal*12+nvl(comm,0) into esal from emp where empno=eno;

  return esal;

end;

 

--call

declare

   esal number;

begin

   esal:=fun_countyearsal(7839);

   dbms_output.put_line(esal);

end;

2. Function Calls

    @Test
    public void testFunction01() {

        String driver = "oracle.jdbc.OracleDriver";

        String url = "jdbc:oracle:thin:@192.168.56.10:1521:orcl";

        String username = "scott";

        String password = "tiger";

        try {

            Class.forName(driver);

            Connection con = DriverManager.getConnection(url, username, password);

            CallableStatement callSt = con.prepareCall("{?= call fun_countyearsal(?)}");

            callSt.registerOutParameter(1, OracleTypes.NUMBER);

            callSt.setInt(2, 7839);

            callSt.execute();

            System.out.println(callSt.getObject(1));

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

4. java tests for cursor references

1. Define procedures and return reference cursors

--Define the process

create or replace procedure proc_cursor_ref(dno in number,empList out sys_refcursor)

as

begin

  open empList for select * from emp where deptno = dno;

end;

 

--pl/sql Called in

declare

  mycursor_c sys_refcursor;

  myempc emp%rowtype;

begin

  proc_cursor_ref(20,mycursor_c);

  

  loop

    fetch mycursor_c into myempc;

    exit when mycursor_c%notfound;

    dbms_output.put_line(myempc.empno||','||myempc.ename);

  end loop;

  close mycursor_c;

end;

2.java code calls out parameters of cursor type

    @Test
    public void testFunction() {

        String driver = "oracle.jdbc.OracleDriver";

        String url = "jdbc:oracle:thin:@192.168.56.10:1521:orcl";

        String username = "scott";

        String password = "tiger";

        try {

            Class.forName(driver);

            Connection con = DriverManager.getConnection(url, username, password);

            CallableStatement callSt = con.prepareCall("{call proc_cursor_ref (?,?)}");

            callSt.setInt(1, 20);

            callSt.registerOutParameter(2, OracleTypes.CURSOR);

            callSt.execute();

            ResultSet rs = ((OracleCallableStatement) callSt).getCursor(2);

            while (rs.next()) {

                System.out.println(rs.getObject(1) + "," + rs.getObject(2));

            }

        } catch (Exception e) {

            e.printStackTrace();

        }

    }