What is Static OR Embedded SQL?
Embedded or Static SQL is those SQL statements that are fixed and can't be changed at runtime in an application. These statements are compiled at the compile-time only. The benefit of using this statement is that you know the path of execution of statements because you have the SQL statements with you, so you can optimize your SQL query and can execute the query in the best and fastest possible way.
- The way of accessing the data is predefined and these static SQL statements are generally used on those databases that are uniformly distributed.
- These statements
are hardcoded in the application, so if you want to build some application
in which you need some dynamic or run-time SQL statements, you may go with
the Dynamic SQL statement.
- Embedded SQL
applications are portable and can be placed in remote database components.
You can compile the application in one location and run the package on a
different component.
- These statements are hardcoded in the application, so if you want to build some application in which you need some dynamic or run-time SQL statements, you may go with the Dynamic SQL statement.
- Embedded SQL applications are portable and can be placed in remote database components. You can compile the application in one location and run the package on a different component.
Example
DECLARE l_count INTEGER;
BEGIN
SELECT COUNT(*) INTO l_count
FROM user_objects;
DBMS_OUTPUT.PUT_LINE ('Object count = ' || l_count);
END;
In the following example, a PL/SQL anonymous block declares three PL/SQL variables and uses them in the static SQL statements INSERT, UPDATE, DELETE. The block also uses the static SQL statement COMMIT.
DROP TABLE employees_temp;
CREATE TABLE employees_temp AS
SELECT employee_id, first_name, last_name
FROM employees;
DECLARE
emp_id employees_temp.employee_id%TYPE := 299;
emp_first_name employees_temp.first_name%TYPE:= 'Bob';
emp_last_name employees_temp.last_name%TYPE := 'Ross';
BEGIN
INSERT INTO employees_temp (employee_id, first_name, last_name)
VALUES (emp_id, emp_first_name, emp_last_name);
UPDATE employees_temp
SET first_name = 'Robert' WHERE employee_id = emp_id;
DELETE FROM employees_temp
WHERE employee_id = emp_id
RETURNING first_name, last_name
INTO emp_first_name, emp_last_name;
COMMIT;
DBMS_OUTPUT.PUT_LINE (emp_first_name || ' ' || emp_last_name);
END;
/
Result
Robert Ross
Recommended resources for extended reading
Click on the topic name below to navigate to other pages
Great!! :D
ReplyDelete