1. Simple Procedure with No Parameters
CREATE OR REPLACE PROCEDURE greet
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, World!');
END greet;
2. Procedure with IN Parameter
CREATE OR REPLACE PROCEDURE greet_name(p_name IN VARCHAR2)
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END greet_name;
3. Procedure with IN and OUT Parameters
CREATE OR REPLACE PROCEDURE add_numbers(p_num1 IN NUMBER, p_num2 IN NUMBER, p_sum OUT NUMBER)
AS
BEGIN
p_sum := p_num1 + p_num2;
END add_numbers;
4. Procedure with IN, OUT, and IN OUT Parameters
CREATE OR REPLACE PROCEDURE calculate_area(p_length IN NUMBER, p_width IN NUMBER, p_area OUT NUMBER, p_unit IN OUT VARCHAR2)
AS
BEGIN
p_area := p_length * p_width;
p_unit := 'square ' || p_unit;
END calculate_area;
5. Procedure with Default Parameter Values
CREATE OR REPLACE PROCEDURE greet_with_default(p_name IN VARCHAR2 DEFAULT 'Guest')
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END greet_with_default;
6. Procedure with Named Notation
CREATE OR REPLACE PROCEDURE multiply_numbers(p_num1 IN NUMBER, p_num2 IN NUMBER, p_product OUT NUMBER)
AS
BEGIN
p_product := p_num1 * p_num2;
END multiply_numbers;
7. Procedure with Positional Notation
BEGIN
multiply_numbers(5, 10, :product);
END;
8. Procedure to Insert into a Table
CREATE OR REPLACE PROCEDURE insert_employee(p_emp_id IN NUMBER, p_emp_name IN VARCHAR2)
AS
BEGIN
INSERT INTO employees(emp_id, emp_name) VALUES (p_emp_id, p_emp_name);
END insert_employee;
9. Procedure to Update a Table
CREATE OR REPLACE PROCEDURE update_employee_name(p_emp_id IN NUMBER, p_emp_name IN VARCHAR2)
AS
BEGIN
UPDATE employees SET emp_name = p_emp_name WHERE emp_id = p_emp_id;
END update_employee_name;
10. Procedure to Delete from a Table
CREATE OR REPLACE PROCEDURE delete_employee(p_emp_id IN NUMBER)
AS
BEGIN
DELETE FROM employees WHERE emp_id = p_emp_id;
END delete_employee;
11. Procedure with Exception Handling
CREATE OR REPLACE PROCEDURE safe_divide(p_num1 IN NUMBER, p_num2 IN NUMBER, p_result OUT NUMBER)
AS
BEGIN
p_result := p_num1 / p_num2;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Cannot divide by zero');
END safe_divide;
12. Procedure with CURSOR FOR LOOP
CREATE OR REPLACE PROCEDURE list_employees
AS
CURSOR emp_cursor IS SELECT emp_id, emp_name FROM employees;
BEGIN
FOR emp_record IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.emp_id || ', Name: ' || emp_record.emp_name);
END LOOP;
END list_employees;
13. Procedure with Explicit Cursor
CREATE OR REPLACE PROCEDURE fetch_employee(p_emp_id IN NUMBER, p_emp_name OUT VARCHAR2)
AS
CURSOR emp_cursor IS SELECT emp_name FROM employees WHERE emp_id = p_emp_id;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO emp_record;
p_emp_name := emp_record.emp_name;
CLOSE emp_cursor;
END fetch_employee;
14. Procedure with Cursor and Parameters
CREATE OR REPLACE PROCEDURE fetch_employee_by_dept(p_dept_id IN NUMBER)
AS
CURSOR emp_cursor IS SELECT emp_id, emp_name FROM employees WHERE dept_id = p_dept_id;
BEGIN
FOR emp_record IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.emp_id || ', Name: ' || emp_record.emp_name);
END LOOP;
END fetch_employee_by_dept;
15. Procedure with Dynamic SQL
CREATE OR REPLACE PROCEDURE execute_dynamic_sql(p_table_name IN VARCHAR2)
AS
l_sql VARCHAR2(200);
BEGIN
l_sql := 'SELECT COUNT(*) FROM ' || p_table_name;
EXECUTE IMMEDIATE l_sql INTO :row_count;
END execute_dynamic_sql;
16. Procedure to Create a Table Dynamically
CREATE OR REPLACE PROCEDURE create_table_dynamic(p_table_name IN VARCHAR2)
AS
l_sql VARCHAR2(200);
BEGIN
l_sql := 'CREATE TABLE ' || p_table_name || ' (id NUMBER, name VARCHAR2(50))';
EXECUTE IMMEDIATE l_sql;
END create_table_dynamic;
17. Procedure with Bulk Collect
CREATE OR REPLACE PROCEDURE bulk_collect_employees(p_emp_ids OUT SYS.ODCINUMBERLIST)
AS
TYPE emp_table IS TABLE OF employees.emp_id%TYPE;
l_emp_ids emp_table;
BEGIN
SELECT emp_id BULK COLLECT INTO l_emp_ids FROM employees;
p_emp_ids := l_emp_ids;
END bulk_collect_employees;
18. Procedure to Handle Null Values
CREATE OR REPLACE PROCEDURE check_null_value(p_value IN VARCHAR2)
AS
BEGIN
IF p_value IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Value is NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('Value: ' || p_value);
END IF;
END check_null_value;
19. Procedure to Reverse a String
CREATE OR REPLACE PROCEDURE reverse_string(p_input IN VARCHAR2, p_output OUT VARCHAR2)
AS
BEGIN
p_output := REVERSE(p_input);
END reverse_string;
20. Procedure to Generate Fibonacci Sequence
CREATE OR REPLACE PROCEDURE fibonacci_sequence(p_n IN NUMBER, p_result OUT VARCHAR2)
AS
v_a NUMBER := 0;
v_b NUMBER := 1;
v_fib NUMBER;
v_seq VARCHAR2(100) := '0, 1';
BEGIN
FOR i IN 3..p_n LOOP
v_fib := v_a + v_b;
v_seq := v_seq || ', ' || v_fib;
v_a := v_b;
v_b := v_fib;
END LOOP;
p_result := v_seq;
END fibonacci_sequence;
21. Procedure to Calculate Factorial
CREATE OR REPLACE PROCEDURE calculate_factorial(p_num IN NUMBER, p_fact OUT NUMBER)
AS
v_fact NUMBER := 1;
BEGIN
FOR i IN 1..p_num LOOP
v_fact := v_fact * i;
END LOOP;
p_fact := v_fact;
END calculate_factorial;
22. Procedure with PL/SQL Table
CREATE OR REPLACE PROCEDURE process_table
AS
TYPE num_table IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
v_nums num_table;
BEGIN
v_nums(1) := 10;
v_nums(2) := 20;
v_nums(3) := 30;
FOR i IN 1..v_nums.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || v_nums(i));
END LOOP;
END process_table;
23. Procedure to Calculate Sum of Array Elements
CREATE OR REPLACE PROCEDURE sum_array_elements(p_nums IN SYS.ODCINUMBERLIST, p_sum OUT NUMBER)
AS
v_sum NUMBER := 0;
BEGIN
FOR i IN 1..p_nums.COUNT LOOP
v_sum := v_sum + p_nums(i);
END LOOP;
p_sum := v_sum;
END sum_array_elements;
24. Procedure to Find Maximum Value in Array
CREATE OR REPLACE PROCEDURE find_max_in_array(p_nums IN SYS.ODCINUMBERLIST, p_max OUT NUMBER)
AS
v_max NUMBER := p_nums(1);
BEGIN
FOR i IN 2..
p_nums.COUNT LOOP
IF p_nums(i) > v_max THEN
v_max := p_nums(i);
END IF;
END LOOP;
p_max := v_max;
END find_max_in_array;
25. Procedure to Sort Array
CREATE OR REPLACE PROCEDURE sort_array(p_nums IN OUT SYS.ODCINUMBERLIST)
AS
BEGIN
FOR i IN 1..p_nums.COUNT LOOP
FOR j IN i+1..p_nums.COUNT LOOP
IF p_nums(i) > p_nums(j) THEN
p_nums(i) := p_nums(i) + p_nums(j);
p_nums(j) := p_nums(i) - p_nums(j);
p_nums(i) := p_nums(i) - p_nums(j);
END IF;
END LOOP;
END LOOP;
END sort_array;
26. Procedure to Count Words in a String
CREATE OR REPLACE PROCEDURE count_words(p_str IN VARCHAR2, p_word_count OUT NUMBER)
AS
BEGIN
p_word_count := REGEXP_COUNT(p_str, '\S+');
END count_words;
27. Procedure to Reverse an Array
CREATE OR REPLACE PROCEDURE reverse_array(p_nums IN OUT SYS.ODCINUMBERLIST)
AS
v_temp NUMBER;
BEGIN
FOR i IN 1..p_nums.COUNT/2 LOOP
v_temp := p_nums(i);
p_nums(i) := p_nums(p_nums.COUNT - i + 1);
p_nums(p_nums.COUNT - i + 1) := v_temp;
END LOOP;
END reverse_array;
28. Procedure to Calculate GCD
CREATE OR REPLACE PROCEDURE calculate_gcd(p_num1 IN NUMBER, p_num2 IN NUMBER, p_gcd OUT NUMBER)
AS
BEGIN
WHILE p_num2 != 0 LOOP
p_gcd := p_num1 MOD p_num2;
p_num1 := p_num2;
p_num2 := p_gcd;
END LOOP;
p_gcd := p_num1;
END calculate_gcd;
29. Procedure to Calculate LCM
CREATE OR REPLACE PROCEDURE calculate_lcm(p_num1 IN NUMBER, p_num2 IN NUMBER, p_lcm OUT NUMBER)
AS
v_gcd NUMBER;
BEGIN
calculate_gcd(p_num1, p_num2, v_gcd);
p_lcm := (p_num1 * p_num2) / v_gcd;
END calculate_lcm;
30. Procedure to Generate Prime Numbers
CREATE OR REPLACE PROCEDURE generate_primes(p_n IN NUMBER, p_primes OUT SYS.ODCINUMBERLIST)
AS
v_count NUMBER := 0;
BEGIN
FOR i IN 2..p_n LOOP
IF MOD(i, 2) = 0 THEN
CONTINUE;
END IF;
v_count := 0;
FOR j IN 2..SQRT(i) LOOP
IF MOD(i, j) = 0 THEN
v_count := 1;
EXIT;
END IF;
END LOOP;
IF v_count = 0 THEN
p_primes.EXTEND;
p_primes(p_primes.COUNT) := i;
END IF;
END LOOP;
END generate_primes;
31. Procedure to Convert Celsius to Fahrenheit
CREATE OR REPLACE PROCEDURE celsius_to_fahrenheit(p_celsius IN NUMBER, p_fahrenheit OUT NUMBER)
AS
BEGIN
p_fahrenheit := (p_celsius * 9/5) + 32;
END celsius_to_fahrenheit;
32. Procedure to Convert Fahrenheit to Celsius
CREATE OR REPLACE PROCEDURE fahrenheit_to_celsius(p_fahrenheit IN NUMBER, p_celsius OUT NUMBER)
AS
BEGIN
p_celsius := (p_fahrenheit - 32) * 5/9;
END fahrenheit_to_celsius;
33. Procedure to Find Sum of Digits
CREATE OR REPLACE PROCEDURE sum_of_digits(p_num IN NUMBER, p_sum OUT NUMBER)
AS
v_digit NUMBER;
BEGIN
p_sum := 0;
WHILE p_num > 0 LOOP
v_digit := p_num MOD 10;
p_sum := p_sum + v_digit;
p_num := p_num / 10;
END LOOP;
END sum_of_digits;
34. Procedure to Find Product of Digits
CREATE OR REPLACE PROCEDURE product_of_digits(p_num IN NUMBER, p_product OUT NUMBER)
AS
v_digit NUMBER;
BEGIN
p_product := 1;
WHILE p_num > 0 LOOP
v_digit := p_num MOD 10;
p_product := p_product * v_digit;
p_num := p_num / 10;
END LOOP;
END product_of_digits;
35. Procedure to Find the Nth Fibonacci Number
CREATE OR REPLACE PROCEDURE nth_fibonacci(p_n IN NUMBER, p_fib OUT NUMBER)
AS
v_a NUMBER := 0;
v_b NUMBER := 1;
v_temp NUMBER;
BEGIN
IF p_n = 1 THEN
p_fib := v_a;
ELSIF p_n = 2 THEN
p_fib := v_b;
ELSE
FOR i IN 3..p_n LOOP
v_temp := v_a + v_b;
v_a := v_b;
v_b := v_temp;
END LOOP;
p_fib := v_b;
END IF;
END nth_fibonacci;
36. Procedure to Reverse a Number
CREATE OR REPLACE PROCEDURE reverse_number(p_num IN NUMBER, p_reverse OUT NUMBER)
AS
v_digit NUMBER;
BEGIN
p_reverse := 0;
WHILE p_num > 0 LOOP
v_digit := p_num MOD 10;
p_reverse := (p_reverse * 10) + v_digit;
p_num := p_num / 10;
END LOOP;
END reverse_number;
37. Procedure to Check Palindrome Number
CREATE OR REPLACE PROCEDURE check_palindrome_number(p_num IN NUMBER, p_is_palindrome OUT BOOLEAN)
AS
v_reverse NUMBER;
BEGIN
reverse_number(p_num, v_reverse);
IF v_reverse = p_num THEN
p_is_palindrome := TRUE;
ELSE
p_is_palindrome := FALSE;
END IF;
END check_palindrome_number;
38. Procedure to Calculate Power of a Number
CREATE OR REPLACE PROCEDURE power_of_number(p_base IN NUMBER, p_exponent IN NUMBER, p_power OUT NUMBER)
AS
v_result NUMBER := 1;
BEGIN
FOR i IN 1..p_exponent LOOP
v_result := v_result * p_base;
END LOOP;
p_power := v_result;
END power_of_number;
39. Procedure to Check Armstrong Number
CREATE OR REPLACE PROCEDURE check_armstrong_number(p_num IN NUMBER, p_is_armstrong OUT BOOLEAN)
AS
v_sum NUMBER := 0;
v_digit NUMBER;
v_temp NUMBER := p_num;
v_len NUMBER := LENGTH(TO_CHAR(p_num));
BEGIN
WHILE v_temp > 0 LOOP
v_digit := v_temp MOD 10;
v_sum := v_sum + POWER(v_digit, v_len);
v_temp := v_temp / 10;
END LOOP;
IF v_sum = p_num THEN
p_is_armstrong := TRUE;
ELSE
p_is_armstrong := FALSE;
END IF;
END check_armstrong_number;
40. Procedure to Check Perfect Number
CREATE OR REPLACE PROCEDURE check_perfect_number(p_num IN NUMBER, p_is_perfect OUT BOOLEAN)
AS
v_sum NUMBER := 0;
BEGIN
FOR i IN 1..p_num/2 LOOP
IF p_num MOD i = 0 THEN
v_sum := v_sum + i;
END IF;
END LOOP;
IF v_sum = p_num THEN
p_is_perfect := TRUE;
ELSE
p_is_perfect := FALSE;
END IF;
END check_perfect_number;
41. Procedure to Calculate Simple Interest
CREATE OR REPLACE PROCEDURE calculate_simple_interest(p_principal IN NUMBER, p_rate IN NUMBER, p_time IN NUMBER, p_si OUT NUMBER)
AS
BEGIN
p_si := (p_principal * p_rate * p_time) / 100;
END calculate_simple_interest;
42. Procedure to Calculate Compound Interest
CREATE OR REPLACE PROCEDURE calculate_compound_interest
(p_principal IN NUMBER, p_rate IN NUMBER, p_time IN NUMBER, p_ci OUT NUMBER)
AS
BEGIN
p_ci := p_principal * POWER((1 + p_rate / 100), p_time) - p_principal;
END calculate_compound_interest;
43. Procedure to Calculate Area of Circle
CREATE OR REPLACE PROCEDURE calculate_area_of_circle(p_radius IN NUMBER, p_area OUT NUMBER)
AS
BEGIN
p_area := 3.14 * p_radius * p_radius;
END calculate_area_of_circle;
44. Procedure to Calculate Perimeter of Circle
CREATE OR REPLACE PROCEDURE calculate_perimeter_of_circle(p_radius IN NUMBER, p_perimeter OUT NUMBER)
AS
BEGIN
p_perimeter := 2 * 3.14 * p_radius;
END calculate_perimeter_of_circle;
45. Procedure to Calculate Area of Rectangle
CREATE OR REPLACE PROCEDURE calculate_area_of_rectangle(p_length IN NUMBER, p_breadth IN NUMBER, p_area OUT NUMBER)
AS
BEGIN
p_area := p_length * p_breadth;
END calculate_area_of_rectangle;
46. Procedure to Calculate Perimeter of Rectangle
CREATE OR REPLACE PROCEDURE calculate_perimeter_of_rectangle(p_length IN NUMBER, p_breadth IN NUMBER, p_perimeter OUT NUMBER)
AS
BEGIN
p_perimeter := 2 * (p_length + p_breadth);
END calculate_perimeter_of_rectangle;
47. Procedure to Calculate Area of Triangle
CREATE OR REPLACE PROCEDURE calculate_area_of_triangle(p_base IN NUMBER, p_height IN NUMBER, p_area OUT NUMBER)
AS
BEGIN
p_area := 0.5 * p_base * p_height;
END calculate_area_of_triangle;
48. Procedure to Calculate Perimeter of Triangle
CREATE OR REPLACE PROCEDURE calculate_perimeter_of_triangle(p_a IN NUMBER, p_b IN NUMBER, p_c IN NUMBER, p_perimeter OUT NUMBER)
AS
BEGIN
p_perimeter := p_a + p_b + p_c;
END calculate_perimeter_of_triangle;
49. Procedure to Calculate Area of Square
CREATE OR REPLACE PROCEDURE calculate_area_of_square(p_side IN NUMBER, p_area OUT NUMBER)
AS
BEGIN
p_area := p_side * p_side;
END calculate_area_of_square;
50. Procedure to Calculate Perimeter of Square
CREATE OR REPLACE PROCEDURE calculate_perimeter_of_square(p_side IN NUMBER, p_perimeter OUT NUMBER)
AS
BEGIN
p_perimeter := 4 * p_side;
END calculate_perimeter_of_square;
51. Procedure to Check Leap Year
CREATE OR REPLACE PROCEDURE check_leap_year(p_year IN NUMBER, p_is_leap OUT BOOLEAN)
AS
BEGIN
IF MOD(p_year, 4) = 0 AND (MOD(p_year, 100) != 0 OR MOD(p_year, 400) = 0) THEN
p_is_leap := TRUE;
ELSE
p_is_leap := FALSE;
END IF;
END check_leap_year;
52. Procedure to Calculate Sum of First N Natural Numbers
CREATE OR REPLACE PROCEDURE sum_of_natural_numbers(p_n IN NUMBER, p_sum OUT NUMBER)
AS
BEGIN
p_sum := (p_n * (p_n + 1)) / 2;
END sum_of_natural_numbers;
53. Procedure to Calculate Sum of Squares of First N Natural Numbers
CREATE OR REPLACE PROCEDURE sum_of_squares(p_n IN NUMBER, p_sum OUT NUMBER)
AS
BEGIN
p_sum := (p_n * (p_n + 1) * (2 * p_n + 1)) / 6;
END sum_of_squares;
54. Procedure to Calculate Sum of Cubes of First N Natural Numbers
CREATE OR REPLACE PROCEDURE sum_of_cubes(p_n IN NUMBER, p_sum OUT NUMBER)
AS
BEGIN
p_sum := POWER((p_n * (p_n + 1) / 2), 2);
END sum_of_cubes;
55. Procedure to Convert Binary to Decimal
CREATE OR REPLACE PROCEDURE binary_to_decimal(p_binary IN VARCHAR2, p_decimal OUT NUMBER)
AS
v_len NUMBER := LENGTH(p_binary);
v_decimal NUMBER := 0;
BEGIN
FOR i IN 1..v_len LOOP
IF SUBSTR(p_binary, v_len - i + 1, 1) = '1' THEN
v_decimal := v_decimal + POWER(2, i-1);
END IF;
END LOOP;
p_decimal := v_decimal;
END binary_to_decimal;
56. Procedure to Convert Decimal to Binary
CREATE OR REPLACE PROCEDURE decimal_to_binary(p_decimal IN NUMBER, p_binary OUT VARCHAR2)
AS
v_remainder NUMBER;
BEGIN
p_binary := '';
WHILE p_decimal > 0 LOOP
v_remainder := MOD(p_decimal, 2);
p_binary := TO_CHAR(v_remainder) || p_binary;
p_decimal := p_decimal / 2;
END LOOP;
END decimal_to_binary;
57. Procedure to Check Strong Number
CREATE OR REPLACE PROCEDURE check_strong_number(p_num IN NUMBER, p_is_strong OUT BOOLEAN)
AS
v_temp NUMBER := p_num;
v_sum NUMBER := 0;
v_fact NUMBER;
v_digit NUMBER;
BEGIN
WHILE v_temp > 0 LOOP
v_digit := v_temp MOD 10;
v_fact := 1;
FOR i IN 1..v_digit LOOP
v_fact := v_fact * i;
END LOOP;
v_sum := v_sum + v_fact;
v_temp := v_temp / 10;
END LOOP;
IF v_sum = p_num THEN
p_is_strong := TRUE;
ELSE
p_is_strong := FALSE;
END IF;
END check_strong_number;
58. Procedure to Check Automorphic Number
CREATE OR REPLACE PROCEDURE check_automorphic_number(p_num IN NUMBER, p_is_automorphic OUT BOOLEAN)
AS
v_square NUMBER := p_num * p_num;
BEGIN
IF SUBSTR(TO_CHAR(v_square), -LENGTH(TO_CHAR(p_num))) = TO_CHAR(p_num) THEN
p_is_automorphic := TRUE;
ELSE
p_is_automorphic := FALSE;
END IF;
END check_automorphic_number;
59. Procedure to Check Harshad Number
CREATE OR REPLACE PROCEDURE check_harshad_number(p_num IN NUMBER, p_is_harshad OUT BOOLEAN)
AS
v_sum NUMBER := 0;
v_digit NUMBER;
v_temp NUMBER := p_num;
BEGIN
WHILE v_temp > 0 LOOP
v_digit := v_temp MOD 10;
v_sum := v_sum + v_digit;
v_temp := v_temp / 10;
END LOOP;
IF p_num MOD v_sum = 0 THEN
p_is_harshad := TRUE;
ELSE
p_is_harshad := FALSE;
END IF;
END check_harshad_number;
60. Procedure to Check Narcissistic Number
CREATE OR REPLACE PROCEDURE check_narcissistic_number(p_num IN NUMBER, p_is_narcissistic OUT BOOLEAN)
AS
v_temp NUMBER := p_num;
v_sum NUMBER := 0;
v_digit NUMBER;
v_len NUMBER := LENGTH(TO_CHAR(p_num));
BEGIN
WHILE v_temp > 0 LOOP
v_digit := v_temp MOD 10;
v_sum := v_sum + POWER(v_digit, v_len);
v_temp := v_temp / 10;
END LOOP;
IF v_sum = p_num THEN
p_is_narcissistic := TRUE;
ELSE
p_is_narcissistic := FALSE;
END IF;
END check_narcissistic_number;
61. Procedure to Check Happy Number
CREATE OR REPLACE PROCEDURE check_happy_number(p_num IN NUMBER, p_is_happy OUT BOOLEAN)
AS
v_sum NUMBER;
v_digit NUMBER;
BEGIN
LOOP
v_sum := 0;
WHILE p_num > 0 LOOP
v_digit := p_num MOD 10;
v_sum := v_sum + (v_digit * v_digit);
p_num := p_num / 10;
END LOOP;
IF v_sum = 1 THEN
p_is_happy
:= TRUE;
EXIT;
ELSIF v_sum = 4 THEN
p_is_happy := FALSE;
EXIT;
ELSE
p_num := v_sum;
END IF;
END LOOP;
END check_happy_number;
62. Procedure to Check Abundant Number
CREATE OR REPLACE PROCEDURE check_abundant_number(p_num IN NUMBER, p_is_abundant OUT BOOLEAN)
AS
v_sum NUMBER := 0;
BEGIN
FOR i IN 1..p_num/2 LOOP
IF p_num MOD i = 0 THEN
v_sum := v_sum + i;
END IF;
END LOOP;
IF v_sum > p_num THEN
p_is_abundant := TRUE;
ELSE
p_is_abundant := FALSE;
END IF;
END check_abundant_number;
63. Procedure to Check Perfect Square
CREATE OR REPLACE PROCEDURE check_perfect_square(p_num IN NUMBER, p_is_perfect_square OUT BOOLEAN)
AS
v_sqrt NUMBER;
BEGIN
v_sqrt := SQRT(p_num);
IF v_sqrt = TRUNC(v_sqrt) THEN
p_is_perfect_square := TRUE;
ELSE
p_is_perfect_square := FALSE;
END IF;
END check_perfect_square;
64. Procedure to Check Perfect Cube
CREATE OR REPLACE PROCEDURE check_perfect_cube(p_num IN NUMBER, p_is_perfect_cube OUT BOOLEAN)
AS
v_cube_root NUMBER;
BEGIN
v_cube_root := ROUND(EXP(LN(p_num) / 3));
IF v_cube_root * v_cube_root * v_cube_root = p_num THEN
p_is_perfect_cube := TRUE;
ELSE
p_is_perfect_cube := FALSE;
END IF;
END check_perfect_cube;
65. Procedure to Check Pythagorean Triplet
CREATE OR REPLACE PROCEDURE check_pythagorean_triplet(p_a IN NUMBER, p_b IN NUMBER, p_c IN NUMBER, p_is_triplet OUT BOOLEAN)
AS
BEGIN
IF p_a * p_a + p_b * p_b = p_c * p_c THEN
p_is_triplet := TRUE;
ELSE
p_is_triplet := FALSE;
END IF;
END check_pythagorean_triplet;
66. Procedure to Check Amicable Numbers
CREATE OR REPLACE PROCEDURE check_amicable_numbers(p_a IN NUMBER, p_b IN NUMBER, p_are_amicable OUT BOOLEAN)
AS
v_sum_a NUMBER := 0;
v_sum_b NUMBER := 0;
BEGIN
FOR i IN 1..p_a-1 LOOP
IF p_a MOD i = 0 THEN
v_sum_a := v_sum_a + i;
END IF;
END LOOP;
FOR i IN 1..p_b-1 LOOP
IF p_b MOD i = 0 THEN
v_sum_b := v_sum_b + i;
END LOOP;
IF v_sum_a = p_b AND v_sum_b = p_a THEN
p_are_amicable := TRUE;
ELSE
p_are_amicable := FALSE;
END IF;
END check_amicable_numbers;
67. Procedure to Generate Floyds Triangle
CREATE OR REPLACE PROCEDURE floyds_triangle(p_rows IN NUMBER)
AS
v_num NUMBER := 1;
BEGIN
FOR i IN 1..p_rows LOOP
FOR j IN 1..i LOOP
DBMS_OUTPUT.PUT(v_num || ' ');
v_num := v_num + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
END floyds_triangle;
68. Procedure to Generate Pascals Triangle
CREATE OR REPLACE PROCEDURE pascals_triangle(p_rows IN NUMBER)
AS
TYPE row_array IS VARRAY(100) OF NUMBER;
v_array row_array;
BEGIN
v_array := row_array();
v_array.EXTEND(p_rows + 1);
v_array(1) := 1;
FOR i IN 1..p_rows LOOP
FOR j IN REVERSE 2..i+1 LOOP
v_array(j) := v_array(j) + v_array(j-1);
END LOOP;
FOR j IN 1..i+1 LOOP
DBMS_OUTPUT.PUT(v_array(j) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
END pascals_triangle;
69. Procedure to Generate Arithmetic Progression
CREATE OR REPLACE PROCEDURE generate_arithmetic_progression(p_a IN NUMBER, p_d IN NUMBER, p_n IN NUMBER, p_ap OUT SYS.ODCINUMBERLIST)
AS
v_term NUMBER;
BEGIN
FOR i IN 0..p_n-1 LOOP
v_term := p_a + i * p_d;
p_ap.EXTEND;
p_ap(p_ap.COUNT) := v_term;
END LOOP;
END generate_arithmetic_progression;
70. Procedure to Generate Geometric Progression
CREATE OR REPLACE PROCEDURE generate_geometric_progression(p_a IN NUMBER, p_r IN NUMBER, p_n IN NUMBER, p_gp OUT SYS.ODCINUMBERLIST)
AS
v_term NUMBER;
BEGIN
FOR i IN 0..p_n-1 LOOP
v_term := p_a * POWER(p_r, i);
p_gp.EXTEND;
p_gp(p_gp.COUNT) := v_term;
END LOOP;
END generate_geometric_progression;
71. Procedure to Calculate HCF
CREATE OR REPLACE PROCEDURE calculate_hcf(p_num1 IN NUMBER, p_num2 IN NUMBER, p_hcf OUT NUMBER)
AS
BEGIN
WHILE p_num2 != 0 LOOP
p_hcf := p_num1 MOD p_num2;
p_num1 := p_num2;
p_num2 := p_hcf;
END LOOP;
p_hcf := p_num1;
END calculate_hcf;
72. Procedure to Calculate LCM
CREATE OR REPLACE PROCEDURE calculate_lcm(p_num1 IN NUMBER, p_num2 IN NUMBER, p_lcm OUT NUMBER)
AS
v_hcf NUMBER;
BEGIN
calculate_hcf(p_num1, p_num2, v_hcf);
p_lcm := (p_num1 * p_num2) / v_hcf;
END calculate_lcm;
73. Procedure to Print Multiplication Table
CREATE OR REPLACE PROCEDURE print_multiplication_table(p_num IN NUMBER)
AS
BEGIN
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(p_num || ' * ' || i || ' = ' || p_num * i);
END LOOP;
END print_multiplication_table;
74. Procedure to Calculate Nth Prime Number
CREATE OR REPLACE PROCEDURE nth_prime(p_n IN NUMBER, p_prime OUT NUMBER)
AS
v_count NUMBER := 0;
v_num NUMBER := 2;
v_is_prime BOOLEAN;
BEGIN
LOOP
v_is_prime := TRUE;
FOR i IN 2..SQRT(v_num) LOOP
IF v_num MOD i = 0 THEN
v_is_prime := FALSE;
EXIT;
END IF;
END LOOP;
IF v_is_prime THEN
v_count := v_count + 1;
END IF;
IF v_count = p_n THEN
p_prime := v_num;
EXIT;
END IF;
v_num := v_num + 1;
END LOOP;
END nth_prime;
75. Procedure to Check Perfect Number in a Range
CREATE OR REPLACE PROCEDURE check_perfect_number_in_range(p_start IN NUMBER, p_end IN NUMBER, p_result OUT SYS.ODCINUMBERLIST)
AS
v_sum NUMBER;
BEGIN
FOR v_num IN p_start..p_end LOOP
v_sum := 0;
FOR i IN 1..v_num/2 LOOP
IF v_num MOD i = 0 THEN
v_sum := v_sum + i;
END IF;
END LOOP;
IF v_sum = v_num THEN
p_result.EXTEND;
p_result(p_result.COUNT) := v_num;
END IF;
END LOOP;
END check_perfect_number_in_range;
76. Procedure to Check Armstrong Number in a Range
CREATE OR REPLACE PROCEDURE check_armstrong_number_in_range(p_start IN NUMBER, p_end IN NUMBER, p_result OUT SYS.ODCINUMBERLIST)
AS
v_sum NUMBER;
v_digit NUMBER;
v_temp NUMBER;
v_len NUMBER;
BEGIN
FOR v_num IN p_start
..p_end LOOP
v_temp := v_num;
v_sum := 0;
v_len := LENGTH(TO_CHAR(v_num));
WHILE v_temp > 0 LOOP
v_digit := v_temp MOD 10;
v_sum := v_sum + POWER(v_digit, v_len);
v_temp := v_temp / 10;
END LOOP;
IF v_sum = v_num THEN
p_result.EXTEND;
p_result(p_result.COUNT) := v_num;
END IF;
END LOOP;
END check_armstrong_number_in_range;
77. Procedure to Print Right Triangle Pattern
CREATE OR REPLACE PROCEDURE print_right_triangle(p_rows IN NUMBER)
AS
BEGIN
FOR i IN 1..p_rows LOOP
FOR j IN 1..i LOOP
DBMS_OUTPUT.PUT('*');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
END print_right_triangle;
78. Procedure to Print Inverted Triangle Pattern
CREATE OR REPLACE PROCEDURE print_inverted_triangle(p_rows IN NUMBER)
AS
BEGIN
FOR i IN REVERSE 1..p_rows LOOP
FOR j IN 1..i LOOP
DBMS_OUTPUT.PUT('*');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
END print_inverted_triangle;
79. Procedure to Print Pyramid Pattern
CREATE OR REPLACE PROCEDURE print_pyramid(p_rows IN NUMBER)
AS
BEGIN
FOR i IN 1..p_rows LOOP
FOR j IN 1..p_rows-i LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;
FOR j IN 1..(2*i-1) LOOP
DBMS_OUTPUT.PUT('*');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
END print_pyramid;
80. Procedure to Print Diamond Pattern
CREATE OR REPLACE PROCEDURE print_diamond(p_rows IN NUMBER)
AS
BEGIN
FOR i IN 1..p_rows LOOP
FOR j IN 1..p_rows-i LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;
FOR j IN 1..(2*i-1) LOOP
DBMS_OUTPUT.PUT('*');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
FOR i IN REVERSE 1..p_rows-1 LOOP
FOR j IN 1..p_rows-i LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;
FOR j IN 1..(2*i-1) LOOP
DBMS_OUTPUT.PUT('*');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
END print_diamond;
81. Procedure to Check Krishnamurthy Number
CREATE OR REPLACE PROCEDURE check_krishnamurthy_number(p_num IN NUMBER, p_is_krishnamurthy OUT BOOLEAN)
AS
v_sum NUMBER := 0;
v_digit NUMBER;
v_fact NUMBER;
BEGIN
FOR i IN 1..LENGTH(TO_CHAR(p_num)) LOOP
v_digit := p_num MOD 10;
v_fact := 1;
FOR j IN 1..v_digit LOOP
v_fact := v_fact * j;
END LOOP;
v_sum := v_sum + v_fact;
p_num := p_num / 10;
END LOOP;
IF v_sum = p_num THEN
p_is_krishnamurthy := TRUE;
ELSE
p_is_krishnamurthy := FALSE;
END IF;
END check_krishnamurthy_number;
82. Procedure to Check Special Number
CREATE OR REPLACE PROCEDURE check_special_number(p_num IN NUMBER, p_is_special OUT BOOLEAN)
AS
v_sum NUMBER := 0;
v_product NUMBER := 1;
v_digit NUMBER;
BEGIN
WHILE p_num > 0 LOOP
v_digit := p_num MOD 10;
v_sum := v_sum + v_digit;
v_product := v_product * v_digit;
p_num := p_num / 10;
END LOOP;
IF v_sum + v_product = p_num THEN
p_is_special := TRUE;
ELSE
p_is_special := FALSE;
END IF;
END check_special_number;
83. Procedure to Calculate Sum of Odd Numbers
CREATE OR REPLACE PROCEDURE sum_of_odd_numbers(p_n IN NUMBER, p_sum OUT NUMBER)
AS
BEGIN
p_sum := 0;
FOR i IN 1..p_n LOOP
p_sum := p_sum + (2 * i - 1);
END LOOP;
END sum_of_odd_numbers;
84. Procedure to Calculate Sum of Even Numbers
CREATE OR REPLACE PROCEDURE sum_of_even_numbers(p_n IN NUMBER, p_sum OUT NUMBER)
AS
BEGIN
p_sum := 0;
FOR i IN 1..p_n LOOP
p_sum := p_sum + (2 * i);
END LOOP;
END sum_of_even_numbers;
85. Procedure to Print Hollow Square Pattern
CREATE OR REPLACE PROCEDURE print_hollow_square(p_size IN NUMBER)
AS
BEGIN
FOR i IN 1..p_size LOOP
FOR j IN 1..p_size LOOP
IF i = 1 OR i = p_size OR j = 1 OR j = p_size THEN
DBMS_OUTPUT.PUT('*');
ELSE
DBMS_OUTPUT.PUT(' ');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
END print_hollow_square;
86. Procedure to Convert Decimal to Hexadecimal
CREATE OR REPLACE PROCEDURE decimal_to_hexadecimal(p_decimal IN NUMBER, p_hexadecimal OUT VARCHAR2)
AS
v_remainder NUMBER;
BEGIN
p_hexadecimal := '';
WHILE p_decimal > 0 LOOP
v_remainder := MOD(p_decimal, 16);
p_hexadecimal := CASE
WHEN v_remainder < 10 THEN TO_CHAR(v_remainder)
ELSE CHR(55 + v_remainder)
END || p_hexadecimal;
p_decimal := p_decimal / 16;
END LOOP;
END decimal_to_hexadecimal;
87. Procedure to Convert Hexadecimal to Decimal
CREATE OR REPLACE PROCEDURE hexadecimal_to_decimal(p_hexadecimal IN VARCHAR2, p_decimal OUT NUMBER)
AS
v_len NUMBER := LENGTH(p_hexadecimal);
v_decimal NUMBER := 0;
v_digit NUMBER;
BEGIN
FOR i IN 1..v_len LOOP
v_digit := CASE
WHEN SUBSTR(p_hexadecimal, i, 1) BETWEEN '0' AND '9' THEN TO_NUMBER(SUBSTR(p_hexadecimal, i, 1))
ELSE ASCII(SUBSTR(p_hexadecimal, i, 1)) - 55
END;
v_decimal := v_decimal + v_digit * POWER(16, v_len - i);
END LOOP;
p_decimal := v_decimal;
END hexadecimal_to_decimal;
88. Procedure to Convert Binary to Octal
CREATE OR REPLACE PROCEDURE binary_to_octal(p_binary IN VARCHAR2, p_octal OUT VARCHAR2)
AS
v_decimal NUMBER;
BEGIN
binary_to_decimal(p_binary, v_decimal);
decimal_to_octal(v_decimal, p_octal);
END binary_to_octal;
89. Procedure to Convert Octal to Binary
CREATE OR REPLACE PROCEDURE octal_to_binary(p_octal IN VARCHAR2, p_binary OUT VARCHAR2)
AS
v_decimal NUMBER;
BEGIN
octal_to_decimal(p_octal, v_decimal);
decimal_to_binary(v_decimal, p_binary);
END octal_to_binary;
90. Procedure to Convert Decimal to Octal
CREATE OR REPLACE PROCEDURE decimal_to_octal(p_decimal IN NUMBER, p_octal OUT VARCHAR2)
AS
v_remainder NUMBER;
BEGIN
p_octal := '';
WHILE p_decimal > 0 LOOP
v_remainder := MOD(p_decimal, 8);
p_octal := TO_CHAR(v_remainder) || p_octal;
p_decimal := p_decimal / 8;
END LOOP;
END decimal_to_octal;
91. Procedure to Convert Octal to Decimal
CREATE OR REPLACE PROCEDURE octal_to_decimal(p_octal IN VARCHAR2, p_decimal OUT NUMBER)
AS
v_len NUMBER := LENGTH(p_octal);
v_decimal NUMBER := 0;
v_digit NUMBER;
BEGIN
FOR i IN 1..v_len LOOP
v_digit := TO_NUMBER(SUBSTR(p_octal, i, 1));
v_decimal := v_decimal + v_digit * POWER(8, v_len - i);
END LOOP;
p_decimal
:= v_decimal;
END octal_to_decimal;
92. Procedure to Check Prime Number
CREATE OR REPLACE PROCEDURE check_prime_number(p_num IN NUMBER, p_is_prime OUT BOOLEAN)
AS
BEGIN
p_is_prime := TRUE;
FOR i IN 2..SQRT(p_num) LOOP
IF p_num MOD i = 0 THEN
p_is_prime := FALSE;
EXIT;
END IF;
END LOOP;
END check_prime_number;
93. Procedure to Find Factorial of a Number
CREATE OR REPLACE PROCEDURE find_factorial(p_num IN NUMBER, p_factorial OUT NUMBER)
AS
v_fact NUMBER := 1;
BEGIN
FOR i IN 1..p_num LOOP
v_fact := v_fact * i;
END LOOP;
p_factorial := v_fact;
END find_factorial;
94. Procedure to Swap Two Numbers
CREATE OR REPLACE PROCEDURE swap_numbers(p_num1 IN OUT NUMBER, p_num2 IN OUT NUMBER)
AS
v_temp NUMBER;
BEGIN
v_temp := p_num1;
p_num1 := p_num2;
p_num2 := v_temp;
END swap_numbers;
95. Procedure to Calculate Sum of First N Primes
CREATE OR REPLACE PROCEDURE sum_of_first_n_primes(p_n IN NUMBER, p_sum OUT NUMBER)
AS
v_sum NUMBER := 0;
v_count NUMBER := 0;
v_num NUMBER := 2;
v_is_prime BOOLEAN;
BEGIN
LOOP
v_is_prime := TRUE;
FOR i IN 2..SQRT(v_num) LOOP
IF v_num MOD i = 0 THEN
v_is_prime := FALSE;
EXIT;
END IF;
END LOOP;
IF v_is_prime THEN
v_sum := v_sum + v_num;
v_count := v_count + 1;
END IF;
EXIT WHEN v_count = p_n;
v_num := v_num + 1;
END LOOP;
p_sum := v_sum;
END sum_of_first_n_primes;
96. Procedure to Generate Fibonacci Series
CREATE OR REPLACE PROCEDURE generate_fibonacci_series(p_n IN NUMBER, p_series OUT SYS.ODCINUMBERLIST)
AS
v_a NUMBER := 0;
v_b NUMBER := 1;
v_fib NUMBER;
BEGIN
p_series.EXTEND;
p_series(p_series.COUNT) := v_a;
p_series.EXTEND;
p_series(p_series.COUNT) := v_b;
FOR i IN 3..p_n LOOP
v_fib := v_a + v_b;
p_series.EXTEND;
p_series(p_series.COUNT) := v_fib;
v_a := v_b;
v_b := v_fib;
END LOOP;
END generate_fibonacci_series;
97. Procedure to Reverse an Integer
CREATE OR REPLACE PROCEDURE reverse_integer(p_num IN NUMBER, p_reverse OUT NUMBER)
AS
v_digit NUMBER;
BEGIN
p_reverse := 0;
WHILE p_num > 0 LOOP
v_digit := p_num MOD 10;
p_reverse := p_reverse * 10 + v_digit;
p_num := p_num / 10;
END LOOP;
END reverse_integer;
98. Procedure to Print Pascals Triangle
CREATE OR REPLACE PROCEDURE print_pascals_triangle(p_rows IN NUMBER)
AS
TYPE row_array IS VARRAY(100) OF NUMBER;
v_array row_array := row_array();
BEGIN
v_array.EXTEND(p_rows + 1);
v_array(1) := 1;
FOR i IN 1..p_rows LOOP
FOR j IN REVERSE 2..i+1 LOOP
v_array(j) := v_array(j) + v_array(j-1);
END LOOP;
FOR j IN 1..i+1 LOOP
DBMS_OUTPUT.PUT(v_array(j) || ' ');
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
END print_pascals_triangle;
99. Procedure to Print Floyds Triangle
CREATE OR REPLACE PROCEDURE print_floyds_triangle(p_rows IN NUMBER)
AS
v_num NUMBER := 1;
BEGIN
FOR i IN 1..p_rows LOOP
FOR j IN 1..i LOOP
DBMS_OUTPUT.PUT(v_num || ' ');
v_num := v_num + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
END LOOP;
END print_floyds_triangle;
100. Procedure to Calculate Sum of First N Odd Numbers
CREATE OR REPLACE PROCEDURE sum_of_first_n_odd_numbers(p_n IN NUMBER, p_sum OUT NUMBER)
AS
BEGIN
p_sum := 0;
FOR i IN 1..p_n LOOP
p_sum := p_sum + (2 * i - 1);
END LOOP;
END sum_of_first_n_odd_numbers;
No comments:
Post a Comment