Skip to content

pl/sql exercises for the new oracle dba





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

);

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.