Basic Procedures
1. Simple Procedure
Description: This procedure prints a basic greeting using DBMS_OUTPUT.
CREATE OR REPLACE PROCEDURE say_hello IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, World!');
END;
BEGIN
say_hello;
END;
/2. Procedure with IN Parameter
Description: This procedure accepts a name and prints a personalized greeting.
CREATE OR REPLACE PROCEDURE greet_user(p_name IN VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END;3. Procedure with OUT Parameter
Description: This procedure fetches an employee name and returns it through an OUT parameter.
CREATE OR REPLACE PROCEDURE get_employee_name(p_emp_id IN NUMBER, p_emp_name OUT VARCHAR2) IS
BEGIN
SELECT employee_name INTO p_emp_name FROM employees WHERE employee_id = p_emp_id;
END;4. Procedure with IN OUT Parameter
Description: This procedure updates a salary value and changes the input number at the same time.
CREATE OR REPLACE PROCEDURE update_salary(p_emp_id IN NUMBER, p_increase IN OUT NUMBER) IS
BEGIN
UPDATE employees SET salary = salary + p_increase WHERE employee_id = p_emp_id;
p_increase := p_increase * 2;
END;5. Procedure with Default Parameter
Description: This procedure uses a default salary value when one is not provided.
CREATE OR REPLACE PROCEDURE add_employee(p_name IN VARCHAR2, p_salary IN NUMBER DEFAULT 50000) IS
BEGIN
INSERT INTO employees (employee_name, salary) VALUES (p_name, p_salary);
END;6. Procedure with Exception Handling
Description: This procedure handles a missing employee case with exception handling.
CREATE OR REPLACE PROCEDURE safe_delete_employee(p_emp_id IN NUMBER) IS
BEGIN
DELETE FROM employees WHERE employee_id = p_emp_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found.');
END;7. Procedure to Count Rows
Description: This procedure returns the total number of rows in the employees table.
CREATE OR REPLACE PROCEDURE count_employees(p_count OUT NUMBER) IS
BEGIN
SELECT COUNT(*) INTO p_count FROM employees;
END;8. Procedure to Create a Table
Description: This procedure creates a table dynamically using EXECUTE IMMEDIATE.
CREATE OR REPLACE PROCEDURE create_new_table(p_table_name IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ' || p_table_name || ' (id NUMBER, name VARCHAR2(50))';
END;9. Procedure to Drop a Table
Description: This procedure drops a table dynamically using EXECUTE IMMEDIATE.
CREATE OR REPLACE PROCEDURE drop_table(p_table_name IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || p_table_name;
END;10. Procedure to Merge Data
Description: This procedure merges source data into the employees table.
CREATE OR REPLACE PROCEDURE merge_employees(p_source IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'MERGE INTO employees e USING ' || p_source || ' s ON (e.employee_id = s.employee_id)
WHEN MATCHED THEN UPDATE SET e.salary = s.salary
WHEN NOT MATCHED THEN INSERT (employee_id, employee_name, salary) VALUES (s.employee_id, s.employee_name, s.salary)';
END;11. Procedure to Insert Multiple Rows
Description: This procedure inserts multiple employee rows inside a loop.
CREATE OR REPLACE PROCEDURE insert_multiple_employees IS
BEGIN
FOR i IN 1..10 LOOP
INSERT INTO employees (employee_name, salary) VALUES ('Employee ' || i, 30000 + i * 1000);
END LOOP;
END;12. Procedure to Update Records Using a Cursor
Description: This procedure updates employee salaries by looping through a cursor.
CREATE OR REPLACE PROCEDURE update_all_salaries(p_increment IN NUMBER) IS
CURSOR emp_cursor IS
SELECT employee_id FROM employees;
BEGIN
FOR emp_rec IN emp_cursor LOOP
UPDATE employees SET salary = salary + p_increment WHERE employee_id = emp_rec.employee_id;
END LOOP;
END;13. Procedure with Dynamic SQL
Description: This procedure builds a SQL statement dynamically and executes it.
CREATE OR REPLACE PROCEDURE dynamic_select(p_table_name IN VARCHAR2) IS
v_query VARCHAR2(1000);
BEGIN
v_query := 'SELECT COUNT(*) FROM ' || p_table_name;
EXECUTE IMMEDIATE v_query;
END;14. Procedure to Handle Transactions
Description: This procedure demonstrates COMMIT and ROLLBACK inside exception handling.
CREATE OR REPLACE PROCEDURE handle_transaction IS
BEGIN
INSERT INTO employees (employee_name, salary) VALUES ('John Doe', 60000);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Transaction failed, rolled back.');
END;15. Procedure to Use a Collection
Description: This procedure fetches rows through a collection-like workflow.
CREATE OR REPLACE PROCEDURE insert_collection(p_employees SYS_REFCURSOR) IS
v_employee_record employees%ROWTYPE;
BEGIN
LOOP
FETCH p_employees INTO v_employee_record;
EXIT WHEN p_employees%NOTFOUND;
INSERT INTO employees (employee_id, employee_name, salary)
VALUES (v_employee_record.employee_id, v_employee_record.employee_name, v_employee_record.salary);
END LOOP;
END;16. Procedure with PL/SQL Table
Description: This procedure uses a PL/SQL table to store employee IDs.
CREATE OR REPLACE PROCEDURE process_employee_list IS
TYPE emp_table IS TABLE OF employees.employee_id%TYPE;
v_emp_ids emp_table;
BEGIN
v_emp_ids := emp_table(101, 102, 103);
FOR i IN v_emp_ids.FIRST..v_emp_ids.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Processing employee ID: ' || v_emp_ids(i));
END LOOP;
END;17. Procedure to Retrieve Data into a Cursor Variable
Description: This procedure opens a SYS_REFCURSOR for employee data.
CREATE OR REPLACE PROCEDURE get_employee_data(p_cursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_cursor FOR
SELECT employee_id, employee_name FROM employees;
END;18. Procedure to Count Rows Based on Criteria
Description: This procedure counts employees whose salary is above a threshold.
CREATE OR REPLACE PROCEDURE count_high_salary_employees(p_threshold IN NUMBER, p_count OUT NUMBER) IS
BEGIN
SELECT COUNT(*) INTO p_count FROM employees WHERE salary > p_threshold;
END;19. Procedure to Backup a Table
Description: This procedure creates a backup table from an existing table.
CREATE OR REPLACE PROCEDURE backup_table(p_table_name IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE ' || p_table_name || '_backup AS SELECT * FROM ' || p_table_name;
END;20. Procedure to Drop a Column
Description: This procedure removes a column using dynamic SQL.
CREATE OR REPLACE PROCEDURE drop_column(p_table_name IN VARCHAR2, p_column_name IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE ' || p_table_name || ' DROP COLUMN ' || p_column_name;
END;21. Procedure to Add a Column
Description: This procedure adds a new column dynamically.
CREATE OR REPLACE PROCEDURE add_column(p_table_name IN VARCHAR2, p_column_name IN VARCHAR2, p_data_type IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE ' || p_table_name || ' ADD (' || p_column_name || ' ' || p_data_type || ')';
END;22. Procedure to Use Bulk Collect
Description: This procedure bulk collects employee rows into a collection.
CREATE OR REPLACE PROCEDURE bulk_collect_example IS
TYPE emp_table IS TABLE OF employees%ROWTYPE;
v_employees emp_table;
BEGIN
SELECT * BULK COLLECT INTO v_employees FROM employees;
FOR i IN v_employees.FIRST..v_employees.LAST LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employees(i).employee_id);
END LOOP;
END;23. Procedure to Create a Synonym
Description: This procedure creates a synonym for a table.
CREATE OR REPLACE PROCEDURE create_synonym(p_synonym_name IN VARCHAR2, p_table_name IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'CREATE SYNONYM ' || p_synonym_name || ' FOR ' || p_table_name;
END;24. Procedure to Drop a Synonym
Description: This procedure drops an existing synonym.
CREATE OR REPLACE PROCEDURE drop_synonym(p_synonym_name IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'DROP SYNONYM ' || p_synonym_name;
END;25. Procedure to Find Duplicates
Description: This procedure finds duplicate employee names.
CREATE OR REPLACE PROCEDURE find_duplicates IS
BEGIN
FOR rec IN (SELECT employee_name, COUNT(*) FROM employees GROUP BY employee_name HAVING COUNT(*) > 1) LOOP
DBMS_OUTPUT.PUT_LINE('Duplicate Name: ' || rec.employee_name);
END LOOP;
END;26. Procedure with Recursive Cursor
Description: This procedure lists managers and their subordinates using nested cursors.
CREATE OR REPLACE PROCEDURE recursive_cursor_example IS
CURSOR c_recursive IS
SELECT employee_id, manager_id FROM employees WHERE manager_id IS NULL;
CURSOR c_subordinates(p_manager_id IN NUMBER) IS
SELECT employee_id, employee_name FROM employees WHERE manager_id = p_manager_id;
BEGIN
FOR rec IN c_recursive LOOP
DBMS_OUTPUT.PUT_LINE('Manager ID: ' || rec.employee_id);
FOR sub_rec IN c_subordinates(rec.employee_id) LOOP
DBMS_OUTPUT.PUT_LINE(' Subordinate: ' || sub_rec.employee_name);
END LOOP;
END LOOP;
END;27. Procedure to Find the Highest Salary
Description: This procedure returns the maximum salary value.
CREATE OR REPLACE PROCEDURE find_highest_salary(p_highest_salary OUT NUMBER) IS
BEGIN
SELECT MAX(salary) INTO p_highest_salary FROM employees;
END;28. Procedure with CLOB Data
Description: This procedure inserts CLOB content into a table.
CREATE OR REPLACE PROCEDURE insert_clob_data(p_id IN NUMBER, p_data IN CLOB) IS
BEGIN
INSERT INTO clob_table (id, data) VALUES (p_id, p_data);
END;29. Procedure with BLOB Data
Description: This procedure inserts BLOB content into a table.
CREATE OR REPLACE PROCEDURE insert_blob_data(p_id IN NUMBER, p_data IN BLOB) IS
BEGIN
INSERT INTO blob_table (id, data) VALUES (p_id, p_data);
END;30. Procedure to Check Constraints
Description: This procedure displays constraint names for a table.
CREATE OR REPLACE PROCEDURE check_constraints(p_table_name IN VARCHAR2) IS
v_constraints VARCHAR2(4000);
BEGIN
SELECT constraint_name INTO v_constraints FROM user_constraints WHERE table_name = p_table_name;
DBMS_OUTPUT.PUT_LINE('Constraints on table ' || p_table_name || ': ' || v_constraints);
END;31. Procedure to Reset Sequence
Description: This procedure resets a sequence to start from 1.
CREATE OR REPLACE PROCEDURE reset_sequence(p_seq_name IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_seq_name || ' RESTART START WITH 1';
END;32. Procedure with a User-Defined Exception
Description: This procedure raises and handles a custom exception for low salary.
CREATE OR REPLACE PROCEDURE check_salary(p_salary IN NUMBER) IS
salary_too_low EXCEPTION;
BEGIN
IF p_salary < 20000 THEN
RAISE salary_too_low;
END IF;
EXCEPTION
WHEN salary_too_low THEN
DBMS_OUTPUT.PUT_LINE('Salary is too low.');
END;33. Procedure to Call Another Procedure
Description: This procedure calls another stored procedure.
CREATE OR REPLACE PROCEDURE call_another_procedure IS
BEGIN
say_hello; -- Calling the procedure defined earlier
END;34. Procedure with a Table Parameter
Description: This procedure processes rows passed through a table-like input.
CREATE OR REPLACE PROCEDURE update_employee_table(p_emp_table IN SYS_REFCURSOR) IS
v_emp_record employees%ROWTYPE;
BEGIN
LOOP
FETCH p_emp_table INTO v_emp_record;
EXIT WHEN p_emp_table%NOTFOUND;
UPDATE employees SET salary = salary + 1000 WHERE employee_id = v_emp_record.employee_id;
END LOOP;
END;35. Procedure to List All Procedures
Description: This procedure lists procedures available in the schema.
CREATE OR REPLACE PROCEDURE list_all_procedures IS
BEGIN
FOR rec IN (SELECT object_name FROM user_objects WHERE object_type = 'PROCEDURE') LOOP
DBMS_OUTPUT.PUT_LINE('Procedure Name: ' || rec.object_name);
END LOOP;
END;36. Procedure with FORALL and Bulk Collect
Description: This procedure bulk collects rows and updates them using FORALL.
CREATE OR REPLACE PROCEDURE bulk_collect_forall IS
TYPE emp_type IS TABLE OF employees%ROWTYPE;
v_employees emp_type;
BEGIN
SELECT * BULK COLLECT INTO v_employees FROM employees;
FORALL i IN v_employees.FIRST..v_employees.LAST
UPDATE employees SET salary = salary * 1.1 WHERE employee_id = v_employees(i).employee_id;
END;37. Procedure to Generate a Report
Description: This procedure builds a report creation statement dynamically.
CREATE OR REPLACE PROCEDURE generate_report(p_report_name IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'CREATE REPORT ' || p_report_name || ' AS SELECT * FROM employees';
END;38. Procedure to Add a Constraint
Description: This procedure adds a unique constraint dynamically.
CREATE OR REPLACE PROCEDURE add_unique_constraint(p_table_name IN VARCHAR2, p_column_name IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE ' || p_table_name || ' ADD CONSTRAINT unique_' || p_column_name || ' UNIQUE (' || p_column_name || ')';
END;39. Procedure to Remove a Constraint
Description: This procedure drops a named constraint.
CREATE OR REPLACE PROCEDURE remove_constraint(p_table_name IN VARCHAR2, p_constraint_name IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE ' || p_table_name || ' DROP CONSTRAINT ' || p_constraint_name;
END;40. Procedure to Copy Data Between Tables
Description: This procedure copies data from one table to another.
CREATE OR REPLACE PROCEDURE copy_data(p_source_table IN VARCHAR2, p_dest_table IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'INSERT INTO ' || p_dest_table || ' SELECT * FROM ' || p_source_table;
END;41. Procedure to Export Data to File
Description: This procedure demonstrates a file-oriented export workflow.
CREATE OR REPLACE PROCEDURE export_data(p_query IN VARCHAR2, p_file_path IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'SPOOL ' || p_file_path;
EXECUTE IMMEDIATE p_query;
EXECUTE IMMEDIATE 'SPOOL OFF';
END;42. Procedure to Generate a Sequence Value
Description: This procedure retrieves the next sequence value.
CREATE OR REPLACE PROCEDURE generate_sequence_value(p_seq_name IN VARCHAR2, p_value OUT NUMBER) IS
BEGIN
SELECT "seq".NEXTVAL INTO p_value FROM dual;
END;43. Procedure with an Implicit Cursor
Description: This procedure loops through query results using an implicit cursor.
CREATE OR REPLACE PROCEDURE implicit_cursor_example IS
BEGIN
FOR rec IN (SELECT * FROM employees) LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || rec.employee_id || ' Name: ' || rec.employee_name);
END LOOP;
END;44. Procedure to Create a View
Description: This procedure creates a view dynamically.
CREATE OR REPLACE PROCEDURE create_view(p_view_name IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'CREATE VIEW ' || p_view_name || ' AS SELECT * FROM employees';
END;45. Procedure to Drop a View
Description: This procedure drops a view dynamically.
CREATE OR REPLACE PROCEDURE drop_view(p_view_name IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'DROP VIEW ' || p_view_name;
END;46. Procedure to Check Table Existence
Description: This procedure checks whether a table exists in the schema.
CREATE OR REPLACE PROCEDURE check_table_exists(p_table_name IN VARCHAR2) IS
v_exists NUMBER;
BEGIN
SELECT COUNT(*) INTO v_exists FROM user_tables WHERE table_name = p_table_name;
IF v_exists > 0 THEN
DBMS_OUTPUT.PUT_LINE('Table exists.');
ELSE
DBMS_OUTPUT.PUT_LINE('Table does not exist.');
END IF;
END;47. Procedure to Handle Large Objects
Description: This procedure demonstrates working with large objects.
CREATE OR REPLACE PROCEDURE handle_large_object(p_blob BLOB) IS
BEGIN
-- Example of handling large objects
DBMS_LOB.CREATETEMPORARY(p_blob, TRUE);
END;48. Procedure to Create an Index
Description: This procedure creates an index dynamically.
CREATE OR REPLACE PROCEDURE create_index(p_index_name IN VARCHAR2, p_table_name IN VARCHAR2, p_column_name IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'CREATE INDEX ' || p_index_name || ' ON ' || p_table_name || ' (' || p_column_name || ')';
END;49. Procedure to Drop an Index
Description: This procedure drops an index dynamically.
CREATE OR REPLACE PROCEDURE drop_index(p_index_name IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'DROP INDEX ' || p_index_name;
END;50. Procedure to Create a Trigger
Description: This procedure creates a trigger dynamically.
CREATE OR REPLACE PROCEDURE create_trigger(p_trigger_name IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER ' || p_trigger_name || ' BEFORE INSERT ON employees FOR EACH ROW BEGIN NULL; END;';
END;Note: Some examples are illustrative and may need small adjustments before running in a real Oracle environment.
No comments:
Post a Comment