Sql statements used in stored procedures.
By reading and spending time to understand this article a new oracle dba will
know how to write different types of sql statements inside pl/sql procedures,functions or triggers.
If you already know oracle pl/sql then go ahead and read the below article.
Otherwise you can read this link about what oracle pl/sql is.
oracle pl/sql
Example of a sql statement where we insert values into a table
by reading the values from pl/sql variables :
If you use a stored procedure and you have already filled the ref_col1 and ref_col2 variables with appropriate values , then you can insert those values into a table by using a similar syntax as below.
INSERT INTO table1 (
Col1,col2)
SELECT
Ref_col1,
ref_col2
FROM dual;
Example of a sql statement where we take a combination of 2 columns and search if they are present inside the subquery.
We also use the CAST operator to convert a pl/sql collection into a table.
SELECT IDUDCndShop
FROM UDCndShop ci
WHERE (SourceKey, IDCDDataSource) In
(SELECT /*+ cardinality(t 30)*/
SourceKey, IDCDDataSource
FROM UDStgShop si,
TABLE (CAST(tStgIdentifiers AS tShopIDs)) t
WHERE si.IDUDStgShop = t.IDShop
)
Example of a sql statement where we use below concepts
-we use a sequence to read the next value (SIDUDCndShop.NextVal)
-we use a subquery in place of a column(column idudcmpinstrument)
– we use a inline view (the full block called sp)
-using the not exists operator
-using the CAST operator.
INSERT INTO UDCndShop (
IDUDCndShop,
Idcdcndstatus,
Idcddatasource,
Sourcekey,
IDUDCmpInstrument,
IDCDCndType)
SELECT SIDUDCndShop.NextVal,
NOT_UNIFIED As Idcdcndstatus,
Idcddatasource,
Sourcekey,
(SELECT IDUDCmpInstrument
FROM UDCndInstrument i
WHERE sp.Idcddatasource = i.Idcddatasource
AND sp.Sourcekey = i.Sourcekey
) IDUDCmpInstrument,
CND_TYPE_BASE As IDCDCndType
FROM (SELECT /*+ cardinality(t 30)*/
Distinct
Idcddatasource,
Sourcekey
FROM UDStgShop sp,
TABLE (CAST(tStgIdentifiers AS tShopIDs)) t
WHERE sp.IDUDStgShop = t.IDShop
AND Not Exists
(SELECT ‘x’
FROM UDCndShop ci
WHERE sp.IDCDDataSource = ci.IDCDDataSource
AND sp.SourceKey = ci.SourceKey
)
) sp;
Example of a sql statement using these concepts
–reading values into pl/sql variables
SELECT IDUDCndShop,
IsActive,
AgeBasedCompPriority_Last
INTO l_iIDUDCndShop,
l_bWasActive,
l_nAgeBasedCompPriority_Last
FROM UDCndShop
WHERE IDCDDataSource = cData.IDCDDataSource
AND SourceKey = cData.SourceKey;
Example of a sql statement using below concepts
–using max function
–using rownum function
–reading values into pl/sql variables
SELECT Max (IDUDCndShop)
INTO l_iIDUDCndShop_Base
FROM UDCndShop
WHERE IDUDCndShop_Super = iIDUDCndShop
AND rownum = 1;
Example of a sql statement using below concepts
–updating values in a table
UPDATE UDCndShop
SET IDCDCndStatus = ‘U’,
IDCPBatch = l_rData.IDCPBatch,
—
TimestampLastUpdated = sysdate,
IDOperatorLastUpdated = user
WHERE IDUDCndShop = l_iIDUDCndShop_Base;
Example of a pl/sql block where we use below concepts
—reading values into pl/sql variables
—in case there are no values then oracle gives no data found error. so we are using the
exception handling by using the keyword exception.
BEGIN
SELECT IDDCRole
INTO l_nIDDCRole
FROM DCRole_PV
WHERE RoleCode = sRoleCode;
EXCEPTION WHEN NO_DATA_FOUND THEN
l_nIDDCRole := NULL;
END;
Example of a sql using below concepts
—reading from a sequence into a pl/sql variable
SELECT SIDUDStgShopanalytic.NEXTVAL INTO l_IDUDStgShopanalytic FROM DUAL;
Example of a sql using below concepts
—updating values in a table
—using decode to process application logic and assign the final value to the column
—using sub queries
UPDATE UDCNDRate
SET IDUDCmpRate = NULL,
IDCDCndStatus =
DECODE (IsActive,
0, DECODE (IDCDCndStatus,
CANDSTATUS_NOTUNIFIED, CANDSTATUS_NOTUNIFIED,
CANDSTATUS_COMPOUNDED),
DECODE (IDCDCndStatus,
CANDSTATUS_NOTUNIFIED, CANDSTATUS_NOTUNIFIED,
CANDSTATUS_UNIFIED)),
IDSCBatch_Comp = NULL,
IDSCBatchInstance_Comp = NULL,
TimestampLastUpdated = SYSDATE,
IDOperatorLastUpdated = USER
WHERE IDUDCmpRate IN
(SELECT /*+ cardinality(t 200) */ IDUDCmpRate
FROM UDCmpTRateCandidate t
UNION
SELECT /*+ cardinality(t 200) */ IDUDCmpRate_Old
FROM UDCmpTRateCandidate t
)
AND IDUDCndRate NOT IN
(SELECT /*+ cardinality(t 200) */ IDUDCndRate
FROM UDCmpTRateCandidate t
WHERE IsActive <> 0
AND IDUDCmpInstrument Is Not NULL
);
Example of a sql using below concepts
–using NVL function inside a sql
–reading values into pl/sql variable
SELECT Nvl (DefaultValue, l_DEFAULT_BACKWARDDAYS)
INTO l_sBackwardDays
FROM UDCleaningConfig
WHERE IDUDCleaningConfig = l_CONFIGID;
Example of a sql using below concepts
—using select for update syntax
—reading values into pl/sql variables
SELECT IsValid,
RefreshStatus
INTO l_nIsValid,
l_vRefreshStatus
FROM UDSnapshot
WHERE IDUDSnapshot = SNAPSHOT_NAME
FOR UPDATE;
Example of a sql using below concepts
—using count function inside a select statement
—reading values into pl/sql variables
—using rownum function
SELECT Count (1)
INTO l_nCountForRecalculation
FROM UDShopPrimaryTeamForRecalc
WHERE rownum <= l_nUnprocGrpCount2;
Example of a sql using below concepts
—directly inserting into a table using the select statement
INSERT INTO UDTmpShopPrimaryGrpForRecalc (IDUDShopPrimaryTeam)
SELECT IDUDShopPrimaryTeam
FROM UDShopPrimaryTeamForRecalc;
Example of a sql using below concepts
—directly deleting from a table using a select statement in the sub query
DELETE
FROM UDShopPrimaryTeamIdent
WHERE IDUDShopPrimaryTeam In
(SELECT /*+ cardinality (t 5)*/ Distinct t.IDUDShopPrimaryTeam
FROM UDTmpShopPrimaryGrpForRecalc t
);
Example of a sql using below concepts
—inserting into a table using a select statement
—using the not exists syntax
INSERT INTO UDTmpShopIdentifierForRecalc
SELECT Distinct IDCDNumberingSchm,
Identifier
FROM UDShopPrimaryTeamIdent ppgi
WHERE IDUDShopPrimaryTeam In
(SELECT /*+ cardinality (t 5)*/ Distinct t.IDUDShopPrimaryTeam
FROM UDTmpShopPrimaryGrpForRecalc t
)
AND Not Exists
(SELECT /*+ cardinality (pir 5)*/ ‘x’
FROM UDTmpShopIdentifierForRecalc pir
WHERE pir.Identifier = ppgi.Identifier
AND pir.IDCDNumberingSchm = ppgi.IDCDNumberingSchm
);
