Listen Software Solutions "How to" Series 2: Storage Procedure
Referring to:
Refer to definition and use of cursors
Reference definition and use functions
Stored procedure
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);
--P-digit number
--S-number accuracy
Number (x);
Number;
Long;
- accommodate 32,760 byte data
Double precision;
Float;
Int;
REAL;
Date;
RAW (x)
- accommodate 32,760 byte data
Long Raw;
- accommodate 32,760 byte data
- Note the database type is long RAW
- accommodate 2 billion byte data
RECORD;
TABLE;
Varray;
LOB;
CLOB;
v_variable_c1 varchar2 (20);
- Create 20 variables
v_variable_c2 char (10);
- Creating a variable having 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 of table field type in the reference scheme
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 conditions are TRUE,
The value of v_variable_c2 is the 'Greater Than Match' loop * /
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};
