SQL In PLSQL:
PLSQL supports data manipulation and transaction control statements . i.e DML and TCL .
But PLSQL directly does not support DDL and DCL such as CREATE , ALTER, DROP, RENAME, GRANT , REVOKE.
DDL and DCL can be executed inside PLSQL using dynamic SQL.
Terminate each SQL statement with semicolon (;).
SELECT
Values selected using SQL must be stored into variables using INTO.
Never use table column names as variable names which cause confusion.
If it is a scalar variable , you need to fetch only one row.
if it is a composite variable, you can fetch multiple rows.
You can also use explicit cursor to fetch multiple rows and process the data.
To retrieve data from tables use SQL as below :
eg:
DECLARE
v_employee_id employees.employee_id%type;
v_first_name employees.first_name%type;
v_salary employees.salary%type;
v_commission_pct employees.commission_pct%type;
BEGIN
SELECT employee_id, first_name, salary,commission_pct
INTO v_employee_id ,v_first_name, v_salary,v_commission_pct
FROM employees
WHERE employee_id=100;
END;
DML
You can use INSERT , UPDATE, DELETE, MERGE in PLSQL.
INSERT example :
BEGIN
INSERT INTO employees
(employee_id, first_name, last_name, email,
hire_date, job_id, salary)
VALUES(employees_seq.NEXTVAL, 'Ruth', 'Cores',
'RCORES',CURRENT_DATE, 'AD_ASST', 4000);
END;
/
UPDATE example :
DECLARE
sal_increase employees.salary%TYPE := 800;
BEGIN
UPDATE employees
SET salary = salary + sal_increase
WHERE job_id = 'ST_CLERK';
END;
/
DELETE example:
DECLARE
deptno employees.department_id%TYPE := 10;
BEGIN
DELETE FROM employees
WHERE department_id = deptno;
END;
/
