Free Quote

Month: July 2013

Function to display reverse string of the given string in PL/SQL

Leave a Comment / Database, Function, PL/SQL, SQL / By

CREATE OR REPLACE FUNCTION rev_str_f(str VARCHAR2) RETURN VARCHAR2ISv_str VARCHAR2(10);   –you can specify your own sizev_str_rev VARCHAR2(10); –specify same size as v_strBEGINv_str := ‘&string’;DBMS_OUTPUT.PUT_LINE(‘Given String: ‘||v_str);FOR i IN 1..LENGTH(v_str) LOOPv_str_rev := v_str_rev||SUBSTR(v_str,-i,1);DBMS_OUTPUT.PUT_LINE(v_str_rev);END LOOP;DBMS_OUTPUT.PUT_LINE(‘The reverse String for given ‘||v_str||’ is: ‘||v_str_rev);RETURN v_str_rev;END rev_str_f;

Difference between REPLACE and TRANSLATE in SQL?

Leave a Comment / SQL / By

Replace will replace the character or string with the given character/string for every occurrence of the character/string.Translate will replace the character with another character given not by the string/multiple characters Case 1: SELECT REPLACE(‘Jack and Jue’,’J’,’Bl’) replace_str,TRANSLATE(‘Jack and Jue’,’J’,’Bl’) translate_strFROM DUAL; Case 2: SELECT REPLACE(‘Jack and Jue’,’J’,”) replace_str,TRANSLATE(‘Jack and Jue’,’J’,”) translate_strFROM DUAL;

Procedure to display the departments, which are having total salary greater than?

Leave a Comment / PL/SQL, Procedure, SQL / By

Procedure without Parameters: CREATE OR REPLACE PROCEDURE dept_sal_pISCURSOR C1 ISselect d.department_name,e.tot_sal from(SELECT department_id,sum(salary) tot_salFROM employeesGROUP BY department_idHAVING SUM(salary) > 50000) e,departments dwhere e.department_id=d.department_id;v_id departments.department_name%TYPE;v_sal employees.salary%type;BEGINOPEN C1;DBMS_OUTPUT.PUT_LINE(‘Departments having salary greater than 50K are :’);LOOPFETCH C1 INTO v_id,v_sal;exit when C1%notfound;DBMS_OUTPUT.PUT_LINE(v_id||’ ‘||v_sal);END LOOP;if c1%rowcount=0 then DBMS_OUTPUT.PUT_LINE(‘No Departments found’);end if;CLOSE C1;END dept_sal_p; Procedure with Parameter as total salary limit: …

Procedure to display the departments, which are having total salary greater than? Read More »

Scroll to Top