PL/SQL Programming (II)

2. The first article in the previous section described the Java program call stored procedures, which are supplemented here.

   Stored procedures may have no return value, return value (not a list), return result set, and so on, the following one by one examples:

3. --Jian Drop table book;create table book (Bookid number ()  primary key, BOOKNAME VARCHAR2 (PUBLISHHOUSE VARCHAR2), Bookclass number (3)  not null); */Case   Example 16-add a book to the table books and call it with a Java program: */--stored procedure:in  represents input parameters,  out indicates output parameters,  not write default is input parameter create or  Replace procedure add_book (PROBOOKID IN NUMBER, PROBOOKNAME IN VARCHAR2,  propublishhouse in varchar2, probookclass in number)  isbegin   Insert into book values (Probookid, probookname, propublishhouse, probookclass); End;/*--java calls Public class testprocedure {public static void main (String[]  args)  {Connection conn = null; callablestatement cs = null;try {//Load Driver Class.forName ("Oracle.jdbc.driver.OracleDriver");// Get the connection conn = drivermanager.getconnection ("Jdbc:oracle:thin:@127.0.0.1:1521:orcl", " "Dog",  "dog"), Cs = conn.preparecall ("{Call add_book (?,  ?, ?)}"); /Set parameter Cs.setint (1, 1), cs.setstring (2,  "Thinking in java"), Cs.setstring (3,  "America  universe publish ");       cs.setint (4, 1);//execute Cs.execute ();}  catch  (exception e)  {e.printstacktrace ();}  finally {try {cs.close (); Conn.close ();}  catch  (sqlexception e)  {e.printstacktrace ();}}} *//* case: Stored procedure with return value (return value non-list) instance 17-Enter book number, return book name, publisher */create or replace procedure get_book ( probookid in number, probookname out varchar2, propublishhouse out  VARCHAR2)  isbegin  select bookName, publishHouse into proBookName,  Propublishhouse from book where bookid = probookid;end;/*--java Call public  Class testprocedure {public static void main (StriNg[] args)  {Connection conn = null; callablestatement cs = null;try {//Load Driver Class.forName ("Oracle.jdbc.driver.OracleDriver");// Get the connection conn = drivermanager.getconnection ("Jdbc:oracle:thin:@127.0.0.1:1521:orcl",  "Dog",  " Dog "); Cs = conn.preparecall (" {Call get_book (?,  ?, ?) ");       //Set input parameter Cs.setint (1, 1);//Set output parameter Cs.registeroutparameter (2,  Oracle.jdbc.OracleTypes.VARCHAR); Cs.registeroutparameter (3, oracle.jdbc.oracletypes.varchar);// Execute Cs.execute ();//Output return value string bookname = cs.getstring (2); String publishhouse = cs.getstring (3); System.out.println ("title:"  + bookName +  "  ---Press:"  + publishhouse);}  catch  (exception e)  {e.printstacktrace ();}  finally {try {cs.close (); Conn.close ();}  catch  (sqlexception e)  {e.printstacktrace ();}}} *//* case: Stored procedure with return value (returnValue is a list (result set) The package must be used because the result set is returned and the generic out parameter cannot be used. Example 18-Enter the book category, return the book information */--and then insert several data into the books table--insert into book values (1,  ' thinking in  Java ',  ' america universe publish ',  ' 001 '); Insert into book values (2,  ') Harry potter ',  ' titan books ltd ',  ' 001 '); Insert into book values (3,   ' the accident ',  ' Orion publishing co ',  ' 001 ');--First step: Create a Package--Define a cursor type  create  or replace package pak_resultset is  type pak_cursor_type is  ref cursor;end;--Part II: Creating a Stored Procedure Create or replace procedure pro_resultset ( Probookclass in number, pak_cursor out pak_resultset.pak_cursor_type)  isbegin  --Opening Cursors   open pak_cursor for select * from book where  Bookclass = probookclass;end;/*java Call PUBLIC CLASS TESTPROCEDURE {PUBLIC&NBsp;static void main (String[] args)  {Connection conn = null; callablestatement cs = null; resultset rs = null;try {//Load Driver Class.forName ("Oracle.jdbc.driver.OracleDriver");//GET Connection conn  = drivermanager.getconnection ("Jdbc:oracle:thin:@127.0.0.1:1521:orcl",  "dog",  "dog");// There are input parameters   and   output parameters, output parameters   is   result set Cs = conn.preparecall ("{Call pro_resultset (?,  ?)}"); Cs.setint (1, 1);//result set parameter set Cs.registeroutparameter (2, oracle.jdbc.oracletypes.cursor); Cs.execute ();// Get results rs =  (ResultSet)  cs.getobject (2);while  (Rs.next ())  {system.out.println ("Book Number:"  + rs.getint (1)  +  "  ---title:"  + rs.getstring (2)  +  "   ---Press: " + rs.getstring (3)  + "   ---Category: " + rs.getstring (4));}}  catch  (exception e)  {e.printstacktrace ();}  finally {try {cs.close(); Conn.close (); Rs.close ();}  catch  (sqlexception e)  {e.printstacktrace ();}}} */

4. Exception handling for

5. PL/sql:

   Oracle divides exceptions into: pre-defined exceptions, non-predefined exceptions, and custom exceptions three. The first two are used to deal with Oracle errors, and the resulting Oracle errors implicitly trigger the corresponding exceptions, and the custom exception is not associated with Oracle's error, which is an exception defined by the developer for a specific case.

   A. pre-defined exception: for handling common Oracle errors; non-predefined exceptions are used to handle exceptions that cannot be handled by predefined exceptions; custom exceptions are used to process     Other cases unrelated to the oraccle error.

       There are approximately 20 predefined exceptions for Oracle, and some of the common predefined exceptions are described below:

6. /* pre-defined exception: no_data_found,  before in the first article, no longer repeat *//* predefined exceptions:  case_not_found*/create or replace  procedure pro_caseexception (Employnum number)  is  v_sal emp.sal%type;begin   select sal into v_sal from emp where empno =  employnum;  case    when v_sal < 1000 then       update emp set sal = sal + 200 where  empno = employnum;    when v_sal < 2000 then       update emp set sal = sal + 100 where  empno = employnum;      --If you do not specify the default condition, and do not conform to the above two cases, you may be given an error, You can specify the exception  end case;  exception when case_not_found then  below    dbms_output.put_line (' case not matched '); end;/* pre-defined exception:  cursor_already_open*/declare  cursor emp_cursor  is select ename, sal from emp;begin  open emp_cursor;   for emp_record1 in emp_cursor loop    dbms_output.put_line (Emp_ Record1.ename);   end loop;  exception when cursor_already_open then     dbms_output.put_line (' cursor has been opened! '); end;/* pre-defined exception: dup_val_on_index   the exception is implicitly triggered when a duplicate value is inserted on the column corresponding to the unique index */begin  insert into  dept values (10,  ' technical department ',  ' Shanghai ');     exception when dup_ Val_on_index then    dbms_output.put_line (' duplicated deptno! '); end;/* pre-defined exception: invalid_cursor   This exception is triggered when an attempt is made to perform an operation on an illegal cursor (the cursor does not open or close a cursor that is not open) */declare   cursor emp_cursor is select ename, sal from emp;  emp_record emp_cursor%rowtype;begin  --open emp_cursor;   fetch emp_cursor into emp_record;    dbms_output.put_line (Emp_ Record.ename);   close emp_cursor;    exception when invalid_ Cursor then    dbms_output.put_line (' cursor has been closed! '); end;/* pre-defined exception: invalid_number   This exception is triggered when the data entered is incorrect */begin  update emp set  sal=sal +  ' abc ';    exception when invalid_number then     dbms_output.put_line (' number error! '); end;/* pre-defined exception: too_many_rows   when a select into  statement is executed, the exception is triggered if more than one row is returned */declare   v_ename emp.ename%type;begin  select ename into v_ename from emp;     exception when too_many_rows then  &nbSp; dbms_output.put_line (' return too many rows! '); end;/* pre-defined exception: zero_divide   the exception is triggered when the 2/0  statement is executed */declare  c_num number :=  2;begin  c_num := c_num/0;    exception when zero_ Divide then    dbms_output.put_line (' zero cannot be divide! '); end;/* pre-defined exception: value_error   when performing an assignment operation, the exception is triggered if the length of the variable is not sufficient to accommodate the actual data */declare  v_ename  VARCHAR2 (5); begin  select ename into v_ename from emp where empno  = 7654;    exception when value_error then     dbms_output.put_line (' value is too large for v_ename! '); end;/* other pre-defined exceptions:login_denide ---  user illegally logged on, the exception is triggered not_logged_on ---  The user does not log on to perform DML operations, This exception will be triggered storage_error ---  out of memory space or memory corruption, which will trigger the exception timeout_on_resource ---  If Oracle is waiting forResource time-out, the exception is triggered */ 

   B. custom exceptions : There is no association with Oracle's errors, it is an exception defined by the developer for a specific situation.

8. /* Custom exception: */create or replace procedure test_modifiedexception (Employeeno number) is--Define an exception Myex Exception;begin update  EMP Set sal = sal + empno = Employeeno; --sql%notfound indicates no update--raise Myex;  Trigger Myex exception if Sql%notfound then raise Myex;  End If; Exception when Myex then Dbms_output.put_line (' Myex have been occured! '); End

10. View: A view is a virtual table whose contents are defined by a query . As with a real table, a view contains column and row data with names. However, the view does not exist in the database as a stored set of data values. row and column data is derived from the table referenced by the query for the custom view, and is generated dynamically when the view is referenced. Views and views can be federated queries.

    Differences between views and tables:

1. The table requires disk space, and the view does not require

2. Views cannot be indexed, so queries are slower than tables

3. Using an attempt to simplify complex queries

4. Views for improved security (different users query different content views, separate permissions)

Grammar:

Creating a View: Createor replace view viewName as select ... [with Read Only]

Delete view:drop view viewName

PL/SQL Programming (II)