Listen Software Solution "How to" Series 2: Stored Procedures

Stored Procedures | solving
Listen Software Solution "How to" Series 2: Stored Procedures



Reference:

Referencing definitions and using cursors

Reference definitions and use functions



Stored Procedures

CREATE OR REPLACE PROCEDURE {PROCEDURE name}

({argument} in {datatype}) is v_variable {datatype};

--Variable type

VARCHAR (X);

VARCHAR2 (X);

VARCHAR2;

CHAR (X);

Number (p,s);

Number of digits of--p-digits

Accuracy of--s-numbers

Number (X);

number;

LONG;

--holds 32,760 bytes of data

DOUBLE PRECISION;

FLOAT;

INT;

Real;



DATE;

RAW (X)

--holds 32,760 bytes of data

LONG RAW;

--holds 32,760 bytes of data

--Note that the database type is long RAW

--Holds 2 billion bytes of data

Record;

TABLE;

Varray;

LOB;

CLOB;

V_variable_c1 VARCHAR2 (20);

--Create a variable of 20 characters

V_VARIABLE_C2 CHAR (10);

--Create a variable with a fixed length of 10 characters

-Maximum length 255

V_VARIABLE_C3 VARCHAR2;

--Variable length cannot exceed 2000 characters

V_variable_n1 Table_name.field_name%type;

--Define Variable types for table field types in the reference scenario

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 the data structure in VB

INDEX by Binary_integer;

My_table t_my_table;

BEGIN

--Insert 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, so the first condition is false.

V_VARIABLE_N3 is initially 3, so the condition is true,

V_VARIABLE_C2 's value is ' Greater Than Match '

Cycle * *

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};