oracle-stored procedures and exception snapping

Recently work a little busy, there is no time to study in the work time, I sometimes go to play ball at night, go home to watch TV dramas, Days are passing by. A while ago I saw that the company was useful to Oracle, and I always felt that Oracle was very heavy for me, so this weekend took some time to learn some foundation.

Creating a non-parametric stored procedure

Create procedure  is begin  Insert  into Values (ccx', '321321'); End;

Modifying a non-parametric stored procedure

Create or Replace procedure  is begin  Insert  into Values (the'ccx', '321321  ' End;

creating and modifying a stored procedure with parameters

 create  or  replace  procedure  P_mypro2 (spname varchar2 , Newsal number ) is     begin  update  emp =  newsal =  spname;  end  ;call P_mypro2 (  scott   ", "  1111  ); 

Viewing error messages
Show error;

Call a stored procedure
There are two ways to call a stored procedure, call,exec

exec is a sqlplus command and can only be used in Sqlplus. Used with set serveroutput on. Call is a SQL command that can be used by any tool.

Example: Call P_mypro1 ()

Oracle OUTPUT Statement procedure

Dbms_output is a package provided by Oracle (Java-like development package) that contains procedures that put_line is a process for dbms_output packages.

Examples of use of Oracle variables

DeclareV_enamevarchar2(5); V_sal Number(7,2);begin  SelectEname,sal intoV_ename,v_sal fromEmpwhereEmpno=7369; Dbms_output.put_line ('name'||V_ename||'Wages'||v_sal);End;

Oracle Exception Capture

DeclareV_enamevarchar2(5); V_sal Number(7,2);begin  SelectEname,sal intoV_ename,v_sal fromEmpwhereEmpno=1111; Dbms_output.put_line ('name'||V_ename||'Wages'||v_sal); Exception whenNo_data_found ThenDbms_output.put_line ('SELECT into no data found');  whennot_logged_on ThenDbms_output.put_line ('not connected to Oracle'); End;

Error number	Exception error message name	Description
ORA-0001	Dup_val_on_index	Violation of uniqueness restrictions
ORA-0051	Timeout-on-resource	A timeout occurred while waiting for the resource
ORA-0061	Transaction-backed-out	The deadlock transaction has been undone because
ORA-1001	Invalid-cursor	An attempt was made to use an invalid cursor
ORA-1012	Not-logged-on	Not connected to ORACLE
ORA-1017	Login-denied	Invalid user name / password
ORA-1403	No_data_found	SELECT into no data found
ORA-1422	Too_many_rows	SELECT into returns multiple rows
ORA-1476	Zero-divide	Tried to be 0 apart
ORA-1722	Invalid-number	Failed to convert a number
ORA-6500	Storage-error	Internal error caused by insufficient memory
ORA-6501	Program-error	Internal Error
ORA-6502	Value-error	Conversion or truncation Error
ORA-6504	Rowtype-mismatch	Host cursor variable has incompatible row type with PL/SQL variable
ORA-6511	Cursor-already-open	An attempt was made to open a cursor that is already in an open state
ORA-6530	Access-into-null	An attempt was made to assign a value to a null object's property
ORA-6531	Collection-is-null	An attempt was made to apply a collection (collection) method other than Exists to a null PL /SQL table or varray
ORA-6532	Subscript-outside-limit	References to nested or Varray indexes beyond the declared range
ORA-6533	Subscript-beyond-count	a reference to a nested or Varray index is greater than the number of elements in the collection .

To deal with this anomaly, we simply refer to the exception- handling section of the PL/SQL block, directly reference the corresponding exception case name and complete the corresponding exception error handling.

oracle-stored procedures and exception snapping