Mining Procedures , Functions created in data dictionary.

Procedures and Functions are stored in USER_SOURCE in the Data Dictionary, so If you want take a peek to know what your Procedures and Functions look like run this query below:
SQL >DESC USER_SOURCE;

Name                                             Null?             Type
----------------------------------------- -------- -------------------------
NAME                                                         VARCHAR2(30)
TYPE                                                          VARCHAR2(12)
LINE                                                           NUMBER 
TEXT                                                          VARCHAR2(4000)

          DESC USER_SOURCE;
          

If you are signed in as sysdba,You use DESC DBA_SOURCE;

Now as hr user, run this query which shows the query stored on a particular function/procedure.
SQL >select text FROM user_source WHERE name='SUM_SALS';

FUNCTION sum_sals (d employees.department_id%TYPE)
RETURN number AS
total NUMBER;
BEGIN SELECT sum(salary)
INTO total
FROM employees
WHERE department_id = d;
RETURN total;
EXCEPTION WHEN No_data_found THEN
RETURN 0;
END sum_sals;

SQL>select text FROM user_source WHERE name='GIVE_RAISES';

PROCEDURE give_raises(r NUMBER)
AS
BEGIN FOR x IN 1..11 LOOP
UPDATE employees SET salary = salary * r
WHERE employee_id = ( Select manager_id from departments WHERE department_id= x *10);
END LOOP;
EXCEPTION When no_data_found THEN
null;
END;

Notice that the name are in UPPER CAP, the first query is of TYPE FUNCTION,while the second is of TYPE PROCEDURE. Also you can edit each of the code by typing 'ed' (on only windows OS)on the SQL prompt to edit them on a text editor.

SQL > ed

Triggers: Triggers are subprograms stored in the data dictionary that reacts to some events in the database, that event might be some activity on a particular table, log on/off activity, and most importantly the end user does not know that the trigger is invoked in the background.

Below are types of triggers you can create in your Database to monitor certain activities:

  1. Table triggers
  2. Data defination triggers
  3. Logon/LogOff triggers
  4. Disabling/Enabling.

When creating Triggers use the following syntax:

CREATE [ OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} {DELETE OR INSERT OR UPDATE}
[of column_name] ON table_name
[FOR EACH ROW]
DECLARE
---variables
BEGIN
---CODE---
END;

Example: We are going to create a Trigger on the employees table,on salary column. whenever an update occurs on the employees Table, the record is sent to a log table.

A log table is a table that contains information about what user triggered an update on the employees table. Information such as employee_id,last_name,new added value,old value before change  e.t.c of the user who updated the salary column would be stored in a log table. To create triggers we must create a log table first.

SQL >CREATE TABLE sal_hist(
2       >eid NUMBER,
3       >who VARCHAR2(35),
4       >timestamp DATE,
5       >old_sal NUMBER,
6       >new_sal NUMBER);

Table created.

CREATE TABLE sal_hist(
eid NUMBER,
who VARCHAR2(35),
timestamp DATE,
old_sal NUMBER,
new_sal NUMBER);

Now we write a trigger called sal_trig, whenever salary column is updated on employees table, sal_trig trigger stores the infomation on sal_hist table.

SQL    >CREATE OR REPLACE TRIGGER sal_trig
2          >AFTER UPDATE of salary ON employees
3          >FOR EACH ROW
4          >DECLARE
5          >BEGIN
6          >INSERT INTO sal_hist
7          >VALUES (:old.employee_id, user,  sysdate, :old.salary, :new.salary);
8          >END;
9          >/

CREATE OR REPLACE TRIGGER sal_trig
AFTER UPDATE of salary ON employees
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO sal_hist
VALUES (:old.employee_id, user,  sysdate, :old.salary, :new.salary);
END;
/
           

The Image below shows that the trigger query was successful.Pl/sql statements

PL/SQL PREDEFINED DATATYPES.

A SCALAR type has no internal components. It holds a single value, such as a number or character string,and types are FLOAT, INT, INTEGER,VARCHAR2,CHAR, CHARACTER, LONG, BOOLEAN,DATE e.t.c

A COMPOSITE type has internal components that can be manipulated individually, such as the elements of an array,and types are RECORD, TABLE, VARRAY.

A REFERENCE type holds values, called pointers, that designate other program items,reference types are REF CURSOR ,REF object_type.

A LOB type(large object type) holds values, called lob locators, that specify the location of large objects, such as text blocks or graphic images, that are stored separately from other database data,LOB types are BFILE, BLOB, CLOB. NCLOB.

What is a Cursor?

A cursor is a SELECT statement that is inside your PLSQL block of code and this SELECT statement can bring back multiple records.

For example, let us query the database and select an employee who have worked for more than 20 years and increase there salary by 20% and those who have worked for more than15 years,they have an increase of 15%.

SQL  >DECLARE
1        >CURSOR emp_cur IS     
2        >SELECT employee_id, Hire_date
3        >FROM employees
4        >WHERE department_id IN (50,80)
5        >FOR UPDATE;
6        >BEGIN
7        >FOR rec IN emp_cur LOOP
8        >IF rec.hire_date < sysdate - 20 * 365 THEN
9      >UPDATE employees set salary = salary * 1.2
10      >WHERE CURRENT OF emp_cur;
11      >ELSIF rec.hire_date < sysdate - 15 * 365 THEN
12      >UPDATE employees set salary=salary*1.1
13      >WHERE CURRENT OF emp_cur;
14      >END IF;
15      >END LOOP;
16      >END;
17      >/

DECLARE
CURSOR emp_cur IS
SELECT employee_id, Hire_date
FROM employees
WHERE department_id IN (50,80)
FOR UPDATE;
BEGIN
FOR rec IN emp_cur LOOP
IF rec.hire_date < sysdate - 20 * 365 THEN
UPDATE employees set salary = salary * 1.2
WHERE CURRENT OF emp_cur;
ELSIF rec.hire_date < sysdate - 15 * 365 THEN
UPDATE employees set salary=salary*1.1
WHERE CURRENT OF emp_cur;
END IF;
END LOOP;
END;
/

The image below shows the function query was successful.

Writing pl/sql statements

So here CURSOR emp_cur increases salaries of employees who have worked for more than 20 and 15 years whom are in department 50 and 80,thereby giving multiple outcome.