Oracle Developer Jr - READY TO HIRE! Next Step Systems US-CA-Thousand Oaks Post A Job | Post A Resume

See Also

See Also, Defining and Using Cursors
See Also, Defining and Using Functions

Stored Procedures

CREATE OR REPLACE PROCEDURE {procedure name} 
( {argument} IN {datatype} ) IS v_variable {datatype}; 
/*Variables types:
	
	VARCHAR(X);
	VARCHAR2(X);
	VARCHAR2;
	CHAR(X);

	NUMBER(P,S);   
	P-Precision is the number of digits.  
	S-Scale is the number digits to 
	the right of the decimal.
	NUMBER(X);
	NUMBER;
	LONG;          
	Holds up to 32,760 bytes of data
	DOUBLE PRECISION;
	FLOAT;
	INT;
	REAL;

	DATE;
	RAW(X) 
	Holds up to 32,760 bytes of data
	LONG RAW; 
	Holds up to 32,760 bytes of data. 
	Note the Database field type LONG RAW 
	holds up to 2 gigabytes of data.

	RECORD;
	TABLE;
	VARRAY;

	LOB;
	CLOB;

	v_variable_c1  VARCHAR2(20);            
	Creates a 20 character variable length field
	v_variable_c2  CHAR(10);		    
	Create a 10 character fixed length field 
	- max size 255 characters
	v_variable_c3  VARCHAR2;		    
	Variable length not to exceed 2000 characters

	v_variable_n1  table_name.field_name%TYPE;     
	Determine the variable type by referencing 
	its schema table-field type
	v_variable_n2  NUMBER;
	v_variable_n3  NUMBER := 3;
	v_variable_n4  NUMBER(10);
	v_variable_n5  NUMBER(10,2);
	v_variable_n6  LONG;
	v_variable_n7  FLOAT;
	v_variable_n8  REAL;

	TYPE t_my_record IS RECORD
	(
	v_variable1	VARCHAR2(8)
	,v_variable2 NUMBER(10)
	,v_variable3 DATE
	);

	my_record t_my_record;

	TYPE t_my_table is TABLE OF VARCHAR2(10)   
	Similar to an array structure 
	in Visual Basic

	INDEX BY BINARY_INTEGER;

	my_table t_my_table;

BEGIN 
	Insert your code here

	v_variable_c1 := 'Hello World';
	v_variable_n2 :=10;

	Conditional Logic

	IF v_variable_n2 = 1 THEN
		v_variable_c2 := 'Exact Match';
	ELSIF v_variable_n3 > 2 THEN
		v_variable_c2 := 'Greater Than Match';
	ELSE
		v_variable_c3 := 'None of the Above';
	END IF;

	my_record.v_variable1:='ABC';
	my_record.v_variable2:=3;
	my_record.v_variable3:=TO_DATE('11-JAN-1999','DD-MON-YYYY');

	my_table(1)='A';
	my_table(2)='B';

      
	v_variable_n2 value is 10 therefore the 
	first condition fails. v_variable_n3 is initialized
	with the value of 3, therefore, the condition is 
	true and a value of 'Greater Than Match'
	is assigned to v_variable_c2.
	

	LOOPS

	v_variable_n2:=0;
	LOOP
		v_variable_n2:=v_variable_n2+1;
		EXIT WHEN v_variable_n2 > 10;
	END LOOP;

	v_variable_n2:=0;
      WHILE v_variable_n2<10 LOOP
		v_variable_n2:=v_variable_n2+1;
	END LOOP;

	FOR v_variable_n2 in 1..10 LOOP

	END LOOP;

END {procedure name};

David Nishimoto
For Listen Software

Back to the LSS "How To" Series Main Page