Two-sample T-Tests with SAS

 

SQL Queries to Retrieve Data

What makes PL/SQL so powerful for transaction processing is its seamless integration of SQL commands within PL/SQL code. In fact, the variables, constants, and operators in PL/SQL blocks typically serve secondary roles. 

SQL SELECT statements retrieve data from an Oracle database. Notably, when embedded within a PL/SQL block, they require the INTO clause; the INTO clause specifies the identifier to which PL/SQL stores the retrieved value. For example:

DECLARE
    v_name VARCHAR2(50);
    v_salary EMPLOYEES.SALARY%TYPE;
BEGIN
    SELECT last_name, salary
    INTO v_name, v_salary
    FROM employees 
    WHERE employee_id = 100;
    
    DBMS_OUTPUT.PUT_LINE('The salary of ' || v_name || ' is: ' || v_salary);
END;

This code block declares two variables: v_name and v_salary. Then in the executable section, the SELECT statement selects two column values in a single row. In the SELECT clause, you can specify columns, SQL functions, and/or expressions. For example, 

Here, the INTO clause assigns the two retrieved data values into the declared PL/SQL variables. On the retrieved data values, you can perform procedural data processing. For example, in the code block above, the variables are passed into DBMS_OUTPUT.PUT_LINE() function as arguments. We can have one column or one expression. Or multiple. Or we can have functions. 

Let's see what happens if we omit the INTO clause:

As we can see, we had an error: "not enough values". If we have "3" columns or expressions in our select statement, we need to have exactly "3" variables in the "into" clause. Now. Let’s concatenate first_name and last_name columns with a space and send them into our v_name variable.

Now let’s change the places of v_name and v_salary and try to run again. We have an error. PL/SQL numeric or value error. Character to number conversion error. That means, we tried to assign a text into a number variable. So what we learn in here is, the order of the data types in select and into clauses are important. They must match with each other.

However, there is a pretty important issue. Your code must return only and only "1" row! If your query returns more than one row, you will get a runtime error and this is not good. Runtime errors are very bad things that you will face. So be careful about that kind of errors. You can handle that kind of errors when you learn exceptions.

Here, the important point is that when selecting data from a table and assigning it to variables, you use the INTO clause in the SELECT statement. Depending on the columns selected, you must ensure that the order, number, and data types of the variables match accordingly.

Additionally, when declaring variables to match the types of employee names and department names in the employee table, it can be cumbersome to find the corresponding variable types as the number of variables increases. In such cases, using the %TYPE keyword automatically fetches the column type for the respective variable.

Using the %TYPE keyword eliminates the hassle of manually finding variable types and reduces the risk of incorrectly declaring data types, making it a very useful feature. Within PL/SQL, besides SELECT statements, you can also use INSERT, UPDATE, MERGE, and DELETE statements.

DML and TCL Statements

You can also add DML statements to insert, update, and/or delete data values stored in the database. For example:

In SQL, the TCL statements are used to manage the execution and outcome of a series of SQL statements to ensure data consistency within a database. A transaction starts with the execution of a DML statement and ends with a commit, rollback, or in case of a system failure. These transactions are managed by the SQL engine which are forwarded by PL/SQL engine.

A transaction starts when you run a DML command and ends with a commit, rollback or any system failure. A transaction occurs in SQL engine. But, your PL/SQL codes run in PL/SQL engine. There can be many DML commands and many commits, rollbacks in a PL/SQL code. So there can be multiple transactions in one PL/SQL command.

Besides, if a block ends, that does not mean that your transaction is ended. This is important. A transaction ends with a commit, rollback or a system failure. Otherwise, it can live with many blocks.

Here, you can embed SQL queries^[3] to interact with the database server, enabling data retrieval and manipulations that fulfill your program's objectives. 

For example

You cannot use DDL commands directly. In a PL/SQL code, you can have SQL queries to get some data, or DML commands to insert, update or delete some data from the database.

But, you cannot directly do any DDL or DCL operations with PL/SQL. DDL means, for example creating a table, or altering it etc. DCL operations are transaction control statements like granting, revoking, etc.

These are the subjects you should already know from your SQL lessons. (Notice: If you couldn't remember, you can visit our SQL courses) So, you cannot do DDL and DCL commands directly. But, you can do that with using Dynamic SQL, that you will see in our further lessons.

I needed to tell you that, because, if you try to run a DDL or DCL commands, you will have an error. Just don’t do that for now. 

We use select statement to get data from the database. But, there is one difference. In PL/SQL, our aim is to store some data into some variables and use them. Then, may be store them back into the database with the new values.

Declare v_name varchar2(), let's say 50 and v_salary

It's employees.salary%type.

As I mentioned before, use %type attribute as much as you can.

I used varchar2 to show that any of them is valid.

Naming Conventions & Ambiguities. We need to be careful on naming our variables when we use our variables in where clauses. Our variable names should be different than our column names. Besides, do not try to use a function name etc. Try to write different names. If not, you may have different errors.

Let’s check them in examples:

Now, let’s change the name of our v_salary to salary, like the column name in employees table. But before that, let's change our employee_id is equal to 130 for example. But, what if we change all salary keywords with sysdate for example?