1. Simple Procedure
CREATE OR REPLACE PROCEDURE say_hello IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, World!');
END;
BEGIN
say_hello;
END;
/
2. Procedure with IN Parameter
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
CREATE OR REPLACE PROCEDURE call_another_procedure IS
BEGIN
say_hello; -- Calling the procedure defined earlier
END;
34. Procedure with a Table Parameter
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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;
No comments:
Post a Comment