policy_type := select text from policy_tbl where code='TYPE';
policy_no := select text type from policy_tbl where code='NUMBER';
This does not look like PL/SQL code. PL/SQL uses INTO clause to assign scalar value from SELECT statement to a variable. Anyway remember one thing. I/O is inversely proportional to performance. Low I/O high performance. So scan the table only once. Bulk operation is unnecessary in your case. You can just do this.
select max(decode(code, 'TYPE', text))
, max(decode(code, 'NUMBER', text))
Here it is possible to avoid 'if' or 'case' statement or any other method for tunning.
That code doesn't need 'tuned'; it needs to be thrown away.
You are trying to use PL/SQL when it is likely that SQL will do whatever it is you are needing to do.
Start over and tell us what PROBLEM you are trying to solve. Tell us what RESULT you need.
Oracle 11 G.
Need to pull the data from one table , the table is Key-Value pair table .or entity table . In which you store all the column names in one column and its value/values in another columns .
The composite primary key is present in the table .
Result: Get the "Value" from the table , I am creating a procedure with primary keys as input and the out parameter will be a Type which will hold the values.
Table structure as below . (Please ignore the names language)
NUM_POLIZA VARCHAR2(13 BYTE)
COD_CAMPO VARCHAR2(30 BYTE)
VAL_CAMPO VARCHAR2(80 BYTE),
VAL_COR_CAMPO VARCHAR2(10 BYTE),
TXT_CAMPO VARCHAR2(80 BYTE),
MCA_BAJA_RIESGO VARCHAR2(1 BYTE)
MCA_VIGENTE VARCHAR2(1 BYTE)
MCA_VIGENTE_APLI VARCHAR2(1 BYTE)
Primary Key : NUM_POLIZA, COD_CIA, NUM_SPTO, NUM_APLI, NUM_SPTO_APLI, NUM_RIESGO, COD_CAMPO, NUM_PERIODO .
COD_campo column is storing the column names .
VAL_CAMPO and txt_campo column is storing its corresponding value.
Rows in the table looks like
PRIMARY_KEY_COLUMNS COD_CAMPO VAL_CAMPO TXT_CAMPO OTHER COLUMS
<Primary key columns values > <POLICY_NAME> < XXXXX > < ZZZZZZ >
<Primary key columns values > <POLICY_CODE> <1234 > < SSAS>
<Primary key columns values > <POLICY_SECTOR> <64789 > <WWWW>
*<> are just to differentiate.
Each Row value I want to assign it to attribute variable of a TYPE (POLICY_OBJECT).and that type would be the OUT parameter for the Procedure .
The Primary key values can be the INPUT parameter for the Procedure.
PROCEDURE p_tunning (p_auto_policy_obj IN OUT auto_policy_obj )
TYPE type_a20 IS TABLE OF a2000020%Rowtype INDEX BY PLS_INTEGER;
BULK COLLECT INTO table_a20
FROM (SELECT COD_CIA,
MAX(num_spto) OVER(PARTITION BY cod_cia, num_poliza, cod_campo) AS max_endrstm
WHERE <CONDITIONS> )
FOR i IN 1 .. table_a20.count
WHEN 'POLICY_NAME' THEN
p_auto_policy.policy_type_code := table_a20(i).val_campo;
p_auto_policy.policy_type_name := table_a20(i).txt_campo;
WHEN 'POLICY_SECTOR' THEN
p_auto_policy.policy_sector_code := table_a20(i).val_campo;
p_auto_policy.policy_sector_name := table_a20(i).txt_campo;
< MORE SIMILAR CASE STATEMENT AS ABOVE AROUND 70 -80 FOR EACH VAL CAMPO AND assignment to the variables >
the problem here is case statements is taking some time to execute and i want to avoid the case .
can we use index by varchar cod_campo , and loop in and assign the values ? no case statement would require. this is just
a thought , i am not sure how to implement it , any more suggestions please.
Similar things can be achieved by Select statements as well using decode, or write a function which takes as input the val_campo and a select statement in that function selecting the val_campo,txt_campo.
but the same function needs to be called for each assignment (70-80) times, I tried this approach and it takes time as 70-80 time I/O .
Kindly suggest . Thank you!