oracle pl/sql assignment

• Getting started with plsql
• Assignments model and language
• Assignments model in PL/SQL
• Bulk collect
• Collections and Records
• Exception Handling
• IF-THEN-ELSE Statement
• Object Types
• PLSQL procedure

plsql Assignments model and language Assignments model in PL/SQL

Fastest entity framework extensions.

All programming languages allow us to assign values to variables. Usually, a value is assigned to variable, standing on left side. The prototype of the overall assignment operations in any contemporary programming language looks like this:

This will assign right operand to the left operand. In PL/SQL this operation looks like this:

Left operand must be always a variable . Right operand can be value, variable or function:

When the code block is executed in SQL*Plus the following output is printed in console:

There is a feature in PL/SQL that allow us to assign "from right to the left". It's possible to do in SELECT INTO statement. Prototype of this instrunction you will find below:

This code will assign character literal to a local variable:

Asignment "from right to the left" is not a standard , but it's valuable feature for programmers and users. Generally it's used when programmer is using cursors in PL/SQL - this technique is used, when we want to return a single scalar value or set of columns in the one line of cursor from SQL cursor.

Further Reading:

• Assigning Values to Variables

Got any plsql Question?

• Advertise with us
• Privacy Policy

Get monthly updates about new articles, cheatsheets, and tricks.

Home » PL/SQL Tutorial » PL/SQL IF Statement

PL/SQL IF Statement

Summary : in this tutorial, you will learn how to use the PL/SQL IF statement to either execute or skip a sequence of statements based on a specified condition.

The IF statement allows you to either execute or skip a sequence of statements, depending on a condition. The IF statement has three forms:

IF THEN IF THEN ELSE IF THEN ELSIF

PL/SQL IF THEN statement

The following illustrates the structure of the IF THEN statement:

The condition is a Boolean expression that always evaluates to TRUE, FALSE, or NULL.

If the condition evaluates to TRUE, the statements after the THEN execute. Otherwise, the IF statement does nothing.

PL/SQL IF THEN statement example

In the following example, the statements between THEN and END IF execute because the sales revenue is greater than 100,000.

Tip # 1: Avoid clumsy IF statement

Consider the following example:

In this example, the IF statement determines whether the sales revenue is higher than the cost and updates the b_profitable variable accordingly.

This IF statement called a clumsy IF statement because you can assign the result of a Boolean expression directly to a Boolean variable as follows:

Tip #2: Avoid evaluating Boolean variables

A Boolean variable is always TRUE, FALSE, or NULL. Therefore the following comparison is unnecessary:

Instead, use:

PL/SQL IF THEN ELSE statement

The IF THEN ELSE statement has the following structure:

If the condition evaluates to TRUE, then the statements between THEN and ELSE execute. In case the condition evaluates to FALSE or NULL, the else_statements between ELSE and END IF executes.

IF THEN ELSE statement example

The following example sets the sales commission to 10% if the sales revenue is greater than 200,000. Otherwise, the sales commission is set to 5%.

PL/SQL IF THEN ELSIF statement

The following illustrates the structure of the IF THEN ELSIF statement:

In this structure, the condition between IF and THEN , which is the first condition, is always evaluated. Each other condition between ELSEIF and THEN is evaluated only if the preceding condition is FALSE. For example, the condition_2 is evaluated only if the condition_1 is false, the condition_3 is evaluated only if the condition_2 is false, and so on.

If a condition is true, other subsequent conditions are not evaluated. If no condition is true, the else_statements between the ELSE and ENDIF execute. In case you skip the ELSE clause and no condition is TRUE, then the IF THEN ELSIF does nothing

IF THEN ELSIF statement example

The following example uses the IF THEN ELSIF statement to set the sales commission based on the sales revenue.

Nested IF statement

You can nest an IF statement within another IF statement as shown below:

However, if you have too many levels of nesting, the code will be hard to read and maintain, so you should avoid nesting the IF statements.

In this tutorial, you have learned how to use the PL/SQL IF statement to either execute or skip a sequence of statements depending on a specified condition.

• SQL Worksheet SQL Worksheet
• Previous Sessions
• Previously Viewed
• Utilization
• Schema Schema
• My Scripts My Scripts
• My Tutorials My Tutorials
• Code Library Code Library

Learn and share SQL

Running on Oracle Database 19c

Featured Scripts and Tutorials

Introduction to sql.

This tutorial provides an introduction to the Structured Query Language (SQL), learn how to create tables with primary keys, columns, constraints, ind...

SQL Macros - Creating parameterised views

This tutorial explains how to create a parameterized view using SQL Macros. The examples use the built-in sales history schema so there are no setup s...

How to Find Gaps in Dates with SQL Pattern Matching

This shows you how to find gaps in rows containing start/end dates using match_recognize. You can make this a reusable SQL fragment by placing this in...

Simple Explain Plan

This script explains the plan for a query of the sh.sales and sh.products tables. Both statements must be executed at the same time in order to get t...

19C LISTAGG DISTINCT

The LISTAGG aggregate function now supports duplicate elimination by using the new DISTINCT keyword. The LISTAGG aggregate function orders the rows...

How to Make Reusable SQL Pattern Matching Clauses with SQL Macros

An overview of how to combine SQL pattern matching with SQL macros to create reusable code fragments.

1,933,375 scripts, 5,889 likes, 1,078 published scripts, 9,134 new scripts created in the last 7 days.

• Administering Oracle Fusion Analytics Warehouse
• Manage Users, Groups, Application Roles, and Data Access

Manage Data Access through Security Assignments

As a security administrator, you need to map data security assignments to users to enable data level access.

Use the Security Assignments tab on the Security page to search for the currently set up data security assignments. You may either search for all records or narrow your search to a specific security context, security value, or user. You can remove a security assignment that you had set up or add new security assignments to a user.

Create a Security Assignment

Delete a security assignment, remove users from a security assignment, manage users for a security assignment, set exclusion rules for security assignments, update security assignments automatically.

Use these instructions to create a security assignment in a specific security context.

• Sign in to your service.
• In Oracle Fusion Analytics Warehouse Console , click Security under Service Administration . You see the Security page.
• On the Security page, click the Security Assignments tab. You see all users who have been granted the security assignments in a specific security context.
• Click New Assignment .
• In New Security Assignment, under Select Security Assignments , select a security context, and then search for a security value or select from the displayed list.Move the selected security assignments to the column on the right.
• Under Select Users , search for a user and select the user and move the user to the column on the right. Users are filtered based on the role associated with that context.
• Click Add to Cart and then click View Cart .
• In Security Assignments, click Apply Assignments . You can grant this security assignment to other users as required. Bulk assignments may take some time to process. See the Security Activity tab for details.

Use these instructions to delete a security assignment. When you delete a security assignment, Oracle Fusion Analytics Warehouse removes all users associated with the security assignment.

• On the Security page, click the Security Assignments tab.
• Select a security assignment from the displayed list of assignments or search for a security assignment and select it.
• Click Delete Assignment .

You can revoke the security assignment granted to one or more users.

• In the security assignment details region, select the users from the displayed list of users or search for and select the users.
• Click Remove User .
• In Revoke User Assignment, click Revoke Assignment .

As a security administrator, you can manage users for existing data security assignments. In the Manage Users dialog, you can revoke users for an existing assignment or add new users for that assignment.

• In the security assignment details region, click Manage Users .
• Under Add User , search for a user and select the user.
• Under User , click the Delete icon to revoke the user from the assignment.
• Click Save .

You can set up data security to exclude access for specific users within a security context for specific security assignments.

For example, you can grant access to all security assignments but the business unit ABC. This enables you to have a single rule for a single user within a security context. You can also remove the indirectly derived security assignments of the specific user. Ensure that the users for whom you want to exclude assignments are members of a group related to the security context. You can automate the application of the security exclusion rules by downloading the DataSecurityExclusionAssignments_csv.zip, making changes, and then uploading it; see Download and Upload Data Security Exclusion Rules .

• In Oracle Fusion Analytics Warehouse Console , click Security under Service Administration.
• On the Security page, click Security Assignments , and then click Exclusion Rules .

As a security administrator, automate the updating of security assignments to effectively manage the regular security assignment changes in your organization.

If you want to automate the insertion and deletion of data in the format of USERNAME, SEC_OBJ_CODE, SEC_OBJ_MEMBER_VAL, Operation (to add or to remove the mapping), then configure the changes in the security assignments to be updated automatically and regularly.

To ensure that the changes in security assignment are updated automatically, you must create a table for the OAX_USER schema in Oracle Autonomous Data Warehouse associated with your Oracle Fusion Analytics Warehouse instance. Ensure that you name the table "CUSTOMER_FAW_CONTENT_AUTOSYNC_ASSIGNMENT". You must seed data into this table regularly with the timestamp in universal time (UTC) format in the "CREATION_DATE" column of the table. The CREATION_DATE column ensures that the same records aren't processed repeatedly and no record is missed. Oracle Fusion Analytics Warehouse periodically scans the synonym (2 hours once), pick up the values, and based on the "CREATION_DATE" criteria, populates the FAW_CONTENT_AUTOSYNC_ASSIGNMENT table in the OAX$INFRA schema in Oracle Autonomous Data Warehouse . Later, Oracle Fusion Analytics Warehouse processes the data and uploads the security assignments as per the FAW_CONTENT_AUTOSYNC_ASSIGNMENT table.

• In Oracle Autonomous Data Warehouse associated with your Oracle Fusion Analytics Warehouse instance, create the CUSTOMER_FAW_CONTENT_AUTOSYNC_ASSIGNMENT table in OAX_USER schema using the following script: CREATE TABLE CUSTOMER_FAW_CONTENT_AUTOSYNC_ASSIGNMENT ( "USERNAME" VARCHAR2(256 CHAR), "SEC_OBJ_CODE" VARCHAR2(256 CHAR), "SEC_OBJ_MEMBER_VAL" VARCHAR2(4000 CHAR), "OPERATION_TYPE" VARCHAR2(65 CHAR), "CREATION_DATE" TIMESTAMP(6) ); -- Grant access from the schema OAX_USER GRANT SELECT ON CUSTOMER_FAW_CONTENT_AUTOSYNC_ASSIGNMENT TO OAX$INFRA; COMMIT;