PL/SQL (Procedural Language/Structured Query Language) is Oracle's procedural language extension to SQL. It is used to develop stored procedures, functions, triggers, and packages that can be used to extend the scope and size of applications and to create powerful business logic on the Oracle platform.
PL/SQL applications are used in a variety of industries, ranging from financial to customer service, to automate various processes, such as data entry and retrieval, decision-making, and data analysis.
PL/SQL code blocks must be closed with a period (.)
All PL/SQL program units must begin and end with a keyword such as FUNCTION, PROCEDURE, or BEGIN.
All SQL commands must end in a semi-colon (;).
All PL/SQL declarations must end with a semi-colon (;).
Every PL/SQL program unit must contain at least one executable statement.
Every PL/SQL statement must contain a keyword.
PL/SQL keywords must be written in uppercase.
All variables must be declared before they are used.
PL/SQL Statements must be well-formed and must adhere to a logical structure.
All comments must begin with the /* and end with the */.
Do not use input parameters through the body of the rule. This is a bad style of code, and it is inconvenient during refactoring
Always initialize a local variable!
Do not reuse a local variable for different situations. For example:
SELECT COL_CASEID INTO v_result FROM TBL_CASE;
SELECT COL_CASEID INTO v_caseId FROM TBL_CASE;
Rule name should follow UPPER CASE PREFIX (DCM, CLB, SMPL, QA) with underscore with words in camel case.
The name of the rule should reflect a rule functionality.
Do not use numbers inside a rule name. DCM_Rule12, SMPL_Rule5 - are bad styles.
Each rule should contain a detailed description explaining rule's purpose and rule implementation comments.
This function copies cache data into cache tables and back by Case Id
The currently proposed naming convention is UpperCamelCase.
The argument is that AppBase capitalizes all SQL output parameters to UPPERCASE.
Rule placeholders should use the UPPER_UNDERSCORE_CASE naming convention.
The internal variable names should be next: v_<entityNameOrSomething>. ie, first goes prefix that reflects "variable," the following word describes an attribute name or calculated value, logical purpose, etc.
Variable type should follow: UPPER CASE;
the common structure of rule/function
This is just a recommendation.
The common structure of the rule/function should be as next.
Try to handle errors as much as possible.
Try to avoid using numbers as part of the variable name.
v_result15. Give your variables the context to improve code readability.
Add comments. Use single comments for 1 row and multiple for many rows. Do not use a single comment for multiple.
Do not write a SQL code "too wide". Max of length is 150 symbols.
Add empty rows between logical operations (code blocks).
v_resultas a result of function execution. This is good for debugging and fast refactoring.
Do not use many variables with the same name, such as,
v_res, v_res1, v_resultOfFunc, etc.
Below is an example of a PL/SQL application that can be used to create a function:
PL/SQL Rule Example
DECLARE --INPUT v_TaskId NVARCHAR2(255); --INTERNAL v_ignore INTEGER; v_CaseId INTEGER; v_CBD NVARCHAR2(255); v_ResolutionCode NVARCHAR2(255); v_ResolutionId INTEGER; v_targetStateCode NVARCHAR2(255); --LOGGING v_message NCLOB; --temp variables for returns v_tempErrMsg NCLOB; v_tempErrCd INTEGER; v_tempSccss NCLOB; BEGIN --GET CASE ID v_Taskid := :TaskId; v_CaseId := f_DCM_getCaseIdByTaskId(v_Taskid); --GET CRN DATA BEGIN SELECT COL_CDB INTO v_CBD FROM TBL_CDM_CS_CASE_DATA_MODEL WHERE COL_CS_CASE_DATA_MODELCASE = v_CaseId; EXCEPTION WHEN OTHERS THEN v_CBD := NULL; END; v_message := f_UTIL_addToMessage(originalMsg => v_message, newMsg => 'CBD values was ' || NVL(v_CBD, '-NULL-')); --CALCULATE CORRECT RESOLUTION ID BEGIN IF v_CBD IS NULL THEN v_ResolutionCode := 'TASK_MOVE_TO_IDENTIFY'; ELSE v_ResolutionCode := 'TASK_MOVE_TO_INVESTIGATE'; END IF; SELECT COL_ID INTO v_ResolutionId FROM TBL_STP_RESOLUTIONCODE WHERE UPPER(COL_CODE) = v_ResolutionCode; EXCEPTION WHEN OTHERS THEN v_tempErrCd := 101; v_tempErrMsg := 'Could not find Resolution Code with code ' || v_ResolutionCode; GOTO cleanup; END; v_message := f_UTIL_addToMessage(originalMsg => v_message, newMsg => 'Resolution ID for ' || v_ResolutionCode || ' is ' || TO_CHAR(v_ResolutionId)); --ROUTE TASK TO NEW STATE v_targetStateCode := 'root_TSK_Status_CLOSED_DEFAULT'; v_ignore := f_DCM_taskTransitionManualFn(CUSTOMDATA => null, ErrorCode => v_tempErrCd, ErrorMessage => v_tempErrMsg, ResolutionId => v_ResolutionId, Target => v_targetStateCode, TaskId => v_Taskid, WorkbasketId => null); IF v_tempErrCd > 0 THEN GOTO cleanup; ELSE v_message := f_UTIL_addToMessage(originalMsg => v_message, newMsg => 'SUCCESS: routed Task to new state'); END IF; --CREATE HISTORY RECORD FOR SUCCESS v_ignore := f_HIST_createHistoryFn( AdditionalInfo => v_message, IsSystem=>0, Message=> 'Succesfully routed Task with ' || 'CUST_routeByCBD', MessageCode => NULL, TargetID => v_Taskid, TargetType=>'TASK' ); --RETURN RESULTS :ERRORCODE := 0; :ERRORMESSAGE := NULL; :SUCCESSRESPONSE := v_message; RETURN; --ERROR BLOCK <<cleanup>> v_message := f_UTIL_addToMessage(originalMsg => v_message, newMsg => 'WARNING: Issue routing Task'); v_message := f_UTIL_addToMessage(originalMsg => v_message, newMsg => 'ERROR CODE: ' || v_tempErrCd); v_message := f_UTIL_addToMessage(originalMsg => v_message, newMsg => 'ERROR MESSAGE: ' || v_tempErrMsg); v_ignore := f_HIST_createHistoryFn( AdditionalInfo => v_message, IsSystem=>0, Message=> NULL, MessageCode => 'GenericEventFailure', TargetID => v_Taskid, TargetType=>'TASK' ); :ERRORCODE := v_tempErrCd; :ERRORMESSAGE := v_message; :SUCCESSRESPONSE := NULL; RETURN; END;