My Sql Trial Commands (1)

create table COMFORT2 ( city VARCHAR2(13) NOT NULL, Sampledate DATE NOT NULL, Noon NUMBER(3,1), MIDNIGHT NUMBER(3,1), Precipitation NUMBER );

create table COMFORT_TEST ( city VARCHAR2(13) NOT NULL, Sampledate DATE NOT NULL, Measure VARCHAR2(13), Value NUMBER(3,1) ); insert ALL into COMFORT_TEST(city, sampledate,MEasure,Val) values(city, sampledate,'NOON',Noon) into COMFORT_TEST(city, sampledate,MEasure,Val) values(city, sampledate,'MIDNIGHT',Midnight) into COMFORT_TEST(city, sampledate,MEasure,Val) values(city, sampledate,'PRECIP',Prepicipation) select city, sampledate,noon, midnight, precipatation from comfort where city='Khene'; insert into comfort values('WALPOLE',TO_DATE('06/22/2003','MM/DD/YYYY'), 86.3,72.1,NULL);

insert into comfort (sampledate,Precipatation,city,NOON,Midnight) select '22-dec-2003',Precipatation, 'WALPOLE',noon,midnight from comfort where city = 'KEENE' and sampledate='22-dec-2003';

declare pi constant Number(9,7) := 3.1415927; radius integer (5); area Number (14,2); begin radius:=3; area:=pi*power(radius,2); insert into areas values (radius,area); end;

create or replace view prac as select city, sampledate from trouble where city = 'Pleasant';

create type address_ty as object (street varchar2(50), city varchar2(25), state char(2), zip number); create table customer (cust_id number, address address_ty); THIS IS ONLY FOR 1 ROW declare pi constant Number(9,7) := 3.1415927; area Number(14,2); cursor rad_cursor is select * from radius_vals; rad_val rad_cursor%ROWTYPE; begin open rad_cursor; fetch rad_cursor into rad_val; area:=pi*power(rad_val.radius,2); insert into areas values (rad_val.radius,area); close rad_cursor; end; FOR ALL ROWS declare pi constant Number(9,7) := 3.1415927; area Number(14,2); cursor rad_cursor is select * from radius_vals; rad_val rad_cursor%ROWTYPE; begin open rad_cursor; LOOP fetch rad_cursor into rad_val; EXIT when rad_cursor%NOTFOUND; area:=pi*power(rad_val.radius,2); insert into areas values (rad_val.radius,area); END LOOP; close rad_cursor; end;

exec procedurename(param1, param2,:param3) provided param3 is an out put

create procedure calc (radius number in, area number(14,2) out ) is pi constant Number(9,7) := 3.1415927; begin area:=pi*power(rad_val.radius,2); insert into areas values (rad_val.radius,area); end; create or replace procedure calc (in radius number ) is pi constant Number(9,7) := 3.1415927; area number(14,2); begin area:=pi*power(rad_val.radius,2); insert into areas values (rad_val.radius,area); end;

create or replace procedure p12( par1 in number) begin as declare var1 number; set var1= par1+1; if var1=0 then insert into radius_vals values(143); end if; if par1=0 then update radius_vals set radius=radius+1; else update radius_vals set radius=radius+2 end if; end; exec procedurename(param1, param2,:param3) provided param3 is an out put create or replace procedure prac(pra in number) is begin select * from trouble; end;

create or replace procedure plsql18 (i_paint_id IN number, i_qty IN number) is begin declare cursor c_paint(i_paint_id IN paints.pt_id%type) is select pt_stk_on_hand from paints where pt_id = i_paint_id; l_qty

paints.pt_stk_on_hand%type;

begin if c_paint%isopen then close c_paint; end if; open c_paint(i_paint_id); fetch c_paint into l_qty; if l_qty < i_qty then null; end if; update paints set pt_stk_on_hand = pt_stk_on_hand - i_qty where pt_id = i_paint_id; end; end;

create or replace procedure calc is pi constant Number(9,7) := 3.1415927; area Number(14,2); cursor rad_cursor is select * from radius_vals; rad_val rad_cursor%ROWTYPE; begin open rad_cursor; LOOP fetch rad_cursor into rad_val; EXIT when rad_cursor%NOTFOUND; area:=pi*power(rad_val.radius,2); insert into areas values (rad_val.radius,area); END LOOP; close rad_cursor; end calc; / create or replace procedure p12( par1 in number) is var1 number:= par1+1; begin if var1=0 then insert into radius_vals values(143); end if; if par1=0 then update radius_vals set radius=radius+1; else update radius_vals set radius=radius+2; end if; end p12; / execute p12(-1);

create or replace procedure merge_table is (par in number(6)) begin create or replace procedure anystring( string in varchar2) as begin execute immediate (string); end; execute anystring(‘create table cd( artist varchar2(25),title varchar2(25))’); create or replace function atable (aname in number) return number is ttable number(10); begin for i in 1 .. 10 loop ttable := aname*i; insert into abt values (i,ttable); return(ttable); end loop; end; variable ta number; execute : ta := atable(9); DECLARE ta number; BEGIN ta := atable(7); DBMS_OUTPUT.PUT_LINE('TA END;

' ||ta);

create or replace function atable (aname in number) return number is ttable number(10); begin for i in 1 .. 10 loop ttable := aname*i; insert into abt values (i,ttable); end loop; return (ttable); end; set serveroutput on; CREATE TRIGGER upper_soundex BEFORE INSERT OR UPDATE OF ename, uppername on emp FOR EACH ROW

/

BEGIN :NEW.uppername := UPPER END;

(:NEW.ename);

declare v_char varchar2(90); v_num number(11,2); begin v_char:='42 is'; v_num:=to_number(substr(v_char,1,2)); :g_char:=v_char; :g_num:=v_num; end; / variable g_char varchar2 (90); variable g_num number;

declare ghar abt.i%type :=11; begin merge into ab a using abt b on (b.i=ghar) when matched then update set a.ind =b.i, a.num=b.ttable when not matched then insert values(b.i,b.ttable); end; / variable rows_d varchar2(30); declare ia number:=2; begin delete from abt where i=ia; :rows_d:=(sql%rowcount || ' row deleted.'); end;

declare cn number; i number; begin i:=0; loop insert into abt values(122,323); i:=i+1; exit when i>10; end loop; cn:= sql%rowcount; dbms_output.put_line(cn); end; declare vg char(1) := Upper('&pg'); va varchar2(20); begin va:= case vg when 'A' then 'Excellent' when 'B' then 'Very Good' when 'C' then 'Good' else 'No such grade' end; dbms_output.put_line('GRADE:' || vg || 'appraisal: ' ||va); end; / declare fl number :=1; begin loop if (fl!=6 and fl !=8) then insert into messages values(fl); end if; fl:=fl+1; exit when fl>10; end loop; select * into ab from messages where fl=1; commit; end; create table messages(rs number); select * from messages;

alter table employees add stars varchar2(100); define emp=100; declare va varchar2(50) := Null; cn number; ar number; sa number; cursor e_cursor is select empi from emp1; abh e_cursor%ROWTYPE; begin open e_cursor; <> loop fetch e_cursor into abh; ar:= abh.empi; select sal into sa from emp1 where empi=ar; select NVL(round(sa/1000),0) into cn from dual; va:=NULL; /*is assigning is important becoz if this is not done then for next employess *’s of previous employees are also added*/ <> for i in 1 .. cn loop va:= va||’*’; end loop inner; update emp1 set stars= va where empi=ar; exit when e_cursor%NOTFOUND; end loop outer; close e_cursor; end;

declare TYPE emp_table_type is TABLE OF Emp1%ROWTYPE INDEX BY BINARY_INTEGER; My_emp_table emp_table_type; V_count NUMBER(3) :=103; BEGIN FOR I IN 100 .. v_count LOOP

SELECT * INTO my_emp_table(i) FROM emp1 where empi=i; end LOOP; For I in my_emp_table.FIRST .. my_emp_table.LAST LOOP DBMS_OUTPUT.PUT_LINE(my_emp_table(i).sal); End loop; End; update emp1 set empi = 103 where empi = 143; insert into countries values(‘UK’, ‘UNITED KINGDOM’,12); insert into countries values (‘USA’,’UNITED STATES’,15); insert into countries values (‘IND’,’INDIA’,11); insert into countries values (‘AUS’, ‘AUSTRALIA’,17); DEFINE CI= ind; Declare Count_record countries%ROWTYPE; Cis Varchar2(30) := ‘&CI’; Begin Select * into count_record from countries where country_id=UPPER(CIS); DBMS_OUTPUT.PUT_LINE(‘COUNTRY ID IS ’ || Count_record.country_id || ‘ ,COUNTRY NAME IS ’ || count_record.country_name || ‘ AND REGION ID IS ’ || count_record.region_id); Commit; End; Declare type ddept_table is TABLE of Emp1.stars%TYPE INDEX BY BINARY_INTEGER; Dept_table ddept_table; Begin For I in 100.. 103 LOOP Select stars into dept_table(i) from emp1 where empi=i; End loop; For I in dept_table.first .. dept_table.last Loop DBMS_OUTPUT.PUT_LINE(dept_table(i)); End loop; End;

insert into departments values(70, ‘public relations’ , ‘UK’, 131, 2300); insert into departments values(50, ‘ART’ , ‘ABN’); update departments set name = 'executive' where num =90; Declare Type dept_table is table of departments%ROWTYPE index by binary_integer; my_dept_table dept_table; v_count number :=7; v_deptno departments.num%TYPE; begin FOR i in 1..v_count LOOP If i=1 then V_deptno:=10; Elsif i=2 then V_deptno:=20; Elsif i=3 then V_deptno:=50; Elsif i=4 then V_deptno:=60; Elsif i=5 then V_deptno:=80; Elsif i=6 then V_deptno:=90; Elsif i=7 then V_deptno:=110; End if; Select * into my_dept_table(i) from departments where num=v_deptno; End loop; DBMS_OUTPUT.PUT_LINE (‘my_dept_table.COUNT is ’ || my_dept_table.COUNT); --FOR i in 1..my_dept_table.COUNT --any command the upper or lower wil do For I in my_dept_table.first .. my_dept_table.last LOOP DBMS_OUTPUT.PUT_LINE(‘ DEPT NO IS ‘ || my_dept_table(i).num|| ‘ DEPARTMENT NAME IS ’ || my_dept_table(i).name || ‘ AND LOC IS ‘ || my_dept_table(i).loc);

End loop; Commit; End;

Define x = 5; Declare Cursor sal_cursor is Select distinct sal from emp order by sal desc; /*distinct keyword is used so that if two users have same sal they wont be repeated*/ Inter number; --My_sal sal_cursor; Begin Delete from top_dogs; Open sal_cursor; fetch sal_cursor into inter; While sal_cursor%ROWCOUNT <= &x and sal_cursor%FOUND loop fetch sal_cursor into inter; insert into top_dogs values(inter); end loop; close sal_cursor; end;

select * from top_dogs; define x =10; declare cursor my_dept is select name,sal,man from departments where num=&x; sala number; nama varchar2(50); mana number; begin open my_dept; fetch my_dept into nama, sala, mana; while my_dept%FOUND LOOP fetch my_dept into nama, sala, mana ;

if (sala<5000 AND (mana=101 OR mana =124)) THEN DBMS_OUTPUT.PUT_LINE(‘ ‘ ||nama || ‘ DUE for a raise’); Else DBMS_OUTPUT.PUT_LINE(nama || ‘ NOT DUE for a raise’); End if; fetch my_dept into nama, sala, mana ; End loop; End;

Insert into employees(name,job,hiredate,salary,dept_id,emp_id) Values(&a,&b,&c,&d,&e,&f); ALL ARE WORKING(1st is my logic) Declare Cursor my_dept is Select * from departments where num<100; Abhi my_dept%ROWTYPE; Cursor my_emp(tdep number) is Select * from employees where dept_id= tdep and emp_id<120; ghar my_emp%ROWTYPE; separator varchar2(80); Begin Separator:=’ ‘; Open my_dept; LOOP Fetch my_dept into abhi; EXIT WHEN my_dept%NOTFOUND; DBMS_OUTPUT.PUT_LINE(‘DEPARTMENT NAME IS ‘ || abhi.name || ‘ DEPARTMENT ID IS ’ || abhi.num); DBMS_OUTPUT.PUT_LINE(separator); Open my_emp(abhi.num); LOOP Fetch my_emp into ghar; EXIT WHEN my_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(‘NAME IS ‘ || ghar.name ||’ JOB IS ‘ ||ghar.job ||’ HIREDATE IS ‘ || ghar.hiredate ||’ SALARY IS ‘ || ghar.salary); END LOOP; Close my_emp; END LOOP; Close my_dept; END;

Declare Cursor my_dept is Select num from departments where num<100; Abh number; Ab number; Cursor my_emp(tdep number) is Select name from employees where dept_id= tdep and emp_id<120; Emp_rec my_emp%ROWTYPE; Begin Open my_dept; LOOP Fetch my_dept into abh; EXIT WHEN my_dept%NOTFOUND; Ab:=abh; Open my_emp(ab); LOOP Fetch my_emp into emp_rec; EXIT when my_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(‘NAME IS ‘ || emp_rec.name); End loop; Close my_emp; End loop; Close my_dept; End; Declare Cursor dept_cur is Select num from departments where num<100; Cursor emp_cursor(p_deptno number) is Select * from employees where dept_id=p_deptno and emp_id<120; Begin For dept_rec in dept_cur loop Dbms_output.put_line(‘DEPARTMENT NUMBER: ‘ || dept_rec.num); For emp_rec in emp_cursor (dept_rec.num) loop Dbms_output.put_line(emp_rec.name|| ‘ |’||emp_rec.job); End loop; End loop; End; /

WORKING IN !ST ATTEMPT declare va varchar2(100) := Null; cn number; cursor my_emp is select * from employees FOR UPDATE OF stars NOWAIT; emp_rec my_emp%ROWTYPE; begin open my_emp; LOOP Fetch my_emp into emp_rec; EXIT when my_emp%NOTFOUND; cn:= ROUND(NVL(emp_rec.salary,0)/1000); --where current of cursor; va:=NULL; for I in 1..cn LOOP va:=va||’*’; end loop; update employees set stars = va where current of my_emp; end loop; close my_emp; end; declare e_invalid_dept exception; erm varchar2(255); erc number; begin update departments set name = ‘abhi’ where num = 230; if SQL%NOTFOUND then raise e_invalid_dept; end if; commit; exception when e_invalid_dept then erc:=sqlcode; erm:=sqlerrm; dbms_output.put_line(erc ||’ ‘ ||erm); DBMS_OUTPUT.PUT_LINE('No such dept id'); end;

Declare e exception; pragma exception_init(e,-2292); begin delete from try where i = 20; if SQL%NOTFOUND then raise e; end if; commit; exception when e then dbms_output.put_line('good work'); end; Declare erm varchar2(255); erc number; e exception; begin select * into erc from try; if SQL%NOTFOUND then raise e; end if; commit; exception when e then dbms_output.put_line('good work'); when no_data_found then dbms_output.put_line(‘bad work’); when others then erc:=sqlcode; erm:=sqlerrm; dbms_output.put_line(erc ||’ ‘ ||erm); end; define p=17000; declare erm varchar2(255); more_rec exception; no_rec exception; sal number;

nam varchar2(50); begin select name,salary into nam, sal from employees where salary = &p --select name,salary into nam, sal from employees where salary = &p/0; /*the above state statement will create divide by zero error which wil lead to storage of some other error in messages table*/ INSERT INTO MESSAGES VALUES (to_char(NAM) || ‘ ‘ || to_char(SAL)); exception WHEN NO_DATA_FOUND THEN INSERT INTO MESSAGES VALUES (‘no employee exist’); WHEN TOO_MANY_ROWS THEN INSERT INTO MESSAGES VALUES (‘MORE THAN 1 EMPLOYEE HAVING SALARY ’ || to_char(SAL)); WHEN OTHERS THEN INSERT INTO MESSAGES VALUES(‘SOME OTHER ERROR’); END;

CREATE OR REPLACE PROCEDURE query_emp (p_id IN employees.emp_id%TYPE, p_name OUT employees.name%TYPE, p_salary OUT employees.salary%TYPE) is begin select name, salary into p_name, p_salary from employees where emp_id = p_id; END query_emp; Variable g_name varchar2(25); Variable g_sal number; Execute query_emp(45, :g_name, :g_sal) Print g_name; Print g_sal CREATE OR REPLACE PROCEDURE format_phone (p_phone_no IN OUT VARCHAR2) is begin p_phone_no := '(' || SUBSTR(p_phone_no,1,3) || ')' || SUBSTR(p_phone_no,4,3) || '-' || SUBSTR(p_phone_no,7); END format_phone;

VARIABLE g_phone_no VARCHAR2(15) ; begin :g_phone_no := '8006330575'; end; / execute format_phone(:g_phone_no); print g_phone_no; CREATE OR REPLACE PROCEDURE add_dept (p_name IN departments.name%TYPE DEFAULT 'University', P_loc IN departments.loc %TYPE DEFAULT ‘unknown’) as Begin INSERT INTO departments(num, name,loc,man,sal) VALUES (190, p_name, p_loc,151,23765); END add_dept; BEGIN add_dept; add_dept ('TRAINING', ‘SA’); add_dept ( p_loc => ‘NZ’, p_name =>'EDUCATION'); add_dept ( p_loc =>’ENG’) ; end; / SELECT department_id, department_name, location_id FROM departments; Create or replace procedure raise_sal(id IN number) is Begin Update employees set salary = salary*1.1 where emp_id=id; DBMS_output.put_line(‘hi I m in process_emps’); End; / CREATE OR REPLACE PROCEDURE process_emps is CURSOR emp_cursor IS SELECT emp_id from employees; Begin FOR emp_rec IN emp_cursor LOOP raise_sal(emp_rec.emp_id); DBMS_output.put_line(‘hi I m in process_emps’);

END LOOP; COMMIT; END process_emps; Sub prog cannot handle the exception but main prog can To see results delete data from table t4 Main prog CREATE OR REPLACE PROCEDURE handle_check is CURSOR emp_cursor IS SELECT emp_id from employees; Begin FOR emp_rec IN emp_cursor LOOP raise_check; DBMS_output.put_line(‘hi I m in process_emps’); End loop; Exception When no_data_found then DBMS_output.put_line(‘hi exception of process raise check handled successfully’); End; Sub prog Create or replace procedure raise_check is X number; Y number; Begin select a,b into x,y from t4; DBMS_output.put_line(‘hi I m in raise check to display no data found exception’); End; Sub prog cannot handle the exception but main prog can with another sub program being called Main prog To see results delete data from table t4 CREATE OR REPLACE PROCEDURE handle_check2 is Begin raise_check; DBMS_output.put_line(‘hi I m in process_emps’); raise_check1; Exception When no_data_found then DBMS_output.put_line(‘hi exception of process raise check handled successfully’); End;

Another subprogram Create or replace procedure raise_check1 is X number:=43; Y number:=32; Begin Insert into t4 values( x,y ); DBMS_output.put_line(‘hi I m in raise check1 for insertion of data’); End; Sub-prog with exception handler with main prog also having exception handler(actually not reqd in main prog) To see results delete data from table t4 in this case after subprog handles its exception main prog runs normally; Sub prog: Create or replace procedure raise_check_with_handler is X number; Y number; Begin select a,b into x,y from t4; DBMS_output.put_line(‘hi I m in raise check to display no data found exception’); Exception When no_data_found then DBMS_output.put_line(‘hi exception of process raise check handled by itself successfully’); End; Main program CREATE OR REPLACE PROCEDURE handle_check_5 is Begin raise_check_with_handler ; DBMS_output.put_line(‘hi I m in handle check 5’); raise_check1; Exception When no_data_found then DBMS_output.put_line(‘ exception of process raise check handled successfully’); End;