SQL Statements in PL/SQL: PL/SQL supports data manipulation language (DML) and transaction control language (COMMIT, ROLLBACK, SAVEPOINT). You can use DML commands to modify the data in a database table. PL/SQL dose not directly support DDL statements, such as – CREATE TABLE, ALTER TABLE, DROP TABLE. These statements are dynamic SQL statements. Dynamic statements are built as character string at run time and can contain placeholders and parameters. Therefore, you can use dynamic SQL to execute your DDL statements in PL/SQL. Use the EXECUTE IMMEDIATE statement, which takes the SQL statements as an argument to execute your DDL statements.
SELECT statement: Retrieve data from the database with SELECT statement. In SELECT statement INTO clause is required, and query must return only one row.
Syntax:
SELECT select list ....
INTO {variable_name [, variable_name] ...
| record_name}
FROM table
[WHERE condition];
Example:
SET SERVEROUTPUT ON
DECLARE
sum_sal NUMBER(10,2);
deptno NUMBER NOT NULL := 60;
BEGIN
SELECT SUM(salary) -- group function
INTO sum_sal FROM employees
WHERE department_id = deptno;
DBMS_OUTPUT.PUT_LINE ('The sum of the salary is '||sum_sal);
END;
/
If your requirement is to retrieve multiple rows and operate on the data. You can make use of explicit cursor. I discus about later.
Insert Statement:
BEGIN
INSERT INTO employees
(employee_id, first_name, last_name, email, salary)
VALUES (employees_seq.NEXTVAL, 'Ali', 'Asgor', 'abc@ca.com', 4200);
END;
/
Update Statement:
DECLARE sal emp.salary%TYPE := 8000; BEGIN UPDATE employees SET salary = sal WHERE job_id = 'ST_CLERK'; END; /
Delete Statement:
DECLARE dept emp_department_id%TYPE := 10; BEGIN DELETE FROM employees WHERE department_id = dept; END; /
Merge Statement:
DECLARE
empno emp.employee_id%TYPE := 100;
BEGIN
MERGE INTO copy_emp c
USING emp e
ON (e.employee_id = c.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
.........
c.manager_id = e.manager_id
WHEN NOT MATCHED THEN
INSERT VALUES (e.employee_id, e.first_name, ...... e.manager_id);
END;
/
SQL Cursor: A cursor is a pointer to the private memory area allocated by the oracle server. There are two type of cursor one is Implicit that’s create and manage internally by the oracle server, another is Explicit cursor that’s explicitly declared by the programer.
SQL Cursor attribute:
| Attribute | Description |
|---|---|
| SQL%FOUND | Boolean Attribute that evaluates to TRUE if the most resent SQL statement returned at least one row. |
| SQL%NOTFOUND | Boolean Attribute that evaluates to TRUE if the most recent SQL statement did not return even one row. |
| SQL%ROWCOUNT | An integer value that represents the number or rows affected by the most recent SQL statement |
Example:
VARIABLE rows_deleted VARCHAR2(30); DECLARE empno emp.employee_id%TYPE := 105; BEGIN DELETE FROM employees WHERE employee_id = empno; :rows_deleted := (SQL%ROWCOUNT || 'rows deleted'); END; / PRINT rows_deleted
SQL functions: Most of the function are valid in PL/SQL expression. But the following functions are not available in procedural statements.
- DECODE
- Group functions: AVG, MIN, MAX, COUNT, SUM, STDDEV and VARIANCE. Group functions apply to groups of a rows in a table and therefore are available only in SQL statements in a PL/SQL block.
continue…..