NEWS: Total Picture Views: 147060 | Total Article Views: 155609 | Top 5 Most Popular Article: 1. Am I Stuck Algorithm with Becker Robot | 2. How to read a JSON and work with it | 3. PickUpThings with Becker Robot | 4. Encrypted Data GUI (Download .Zip) | 5. Create Wall and Things with Becker

PLSQL Example 1 SQL

Description:

In this example we will be using Oracle to program in within the database server.

Code:

Example 1: 

    declare
    	v_deptno dept.deptno%TYPE;
    	v_dname dept.dname%TYPE;
    	v_loc dept.loc%TYPE;
    begin
    	select *
    	into v_deptno, v_dname, v_loc
    	from dept
    	where deptno = 50;
   	dbms_output.put_line(v_deptno||' '||v_dname||' '||v_loc);
   exception
   	when no_data_found then
   		dbms_output.put_line('There is no matching row');
  end;

Example 2:

  declare
  v_nameA emp.sal%TYPE;
  v_nameB emp.sal%TYPE;
  begin
  select sal
  	into v_nameA
  	from emp
  	where  ename ='SMITH';
  	select sal
   	into v_nameB
   	from emp
   	where ename ='BLAKE';
   		if v_nameA > v_nameB then
   			dbms_output.put_line('Name A make more money');
   		else
   			dbms_output.put_line('Name B make more money');
   		end if;
 	end;

Example 3:

    declare
    v_counter BINARY_INTEGER := 1;
    begin
    	loop
    		insert into temp_table
    			values(v_counter, 'Loop Index');
    		v_counter := v_counter +1;
    		if v_counter > 20 then
    			exit;
  		end if;
   	end loop;
  end;

Example 4 (Do While):

    declare
    	v_counter BINARY_INTEGER := 1;
    begin
    	loop
    		insert into temp_table
    			values(v_counter, 'Loop Index');
    			v_counter := v_counter +1;
	    		exit when v_counter > 20;
    	end loop;
 		commit; 
   end;

Example 5 (While Loop):
   declare 
    	v_counter BINARY_INTEGER :=1;
    begin
    	while v_counter <=10 loop
    		insert into temp_table
    			values(v_counter, 'Loop Index');
    		v_counter := v_counter +1;
    	end loop;
    	commit;
     end;
 

Example 6 (For Loop):

	begin
    	for v_counter in 1..20 loop
    		insert into temp_table
    			values(v_counter, 'Loop Index');
    	end loop;
       commit;
    end;

Example 7 (Reverse For Loop):

	begin
    	for v_counter in reverse 1..20 loop
    		insert into temp_table
    			values(v_counter, 'Loop Index');
    	end loop;
       commit;
    end;
   

Example 8 (Cursor)**

     declare
    	v_deptno dept.deptno%TYPE;
    	v_dname dept.dname%TYPE;
    	v_loc dept.loc%TYPE;
    	cursor c_dept is
    		select *
    		from dept;
     begin
    	open c_dept;
   	loop
   		fetch c_dept into v_deptno, v_dname, v_loc;
   		if c_dept%NOTFOUND then
   			exit;
   		end if;
   		dbms_output.put_line(v_deptno||' '||v_dname||' '||v_loc);
   	end loop;
   	close c_dept;
     end;

Example 9:

     declare
     	v_ename emp.ename%TYPE;
    	v_sal emp.sal%TYPE;
    	v_deptno emp.deptno%TYPE;
    	cursor c_emp IS
    		select ename, sal, deptno
    		from emp
    		where job = 'MANAGER';
     begin
  	  	open c_emp;
 	  	loop
   			fetch c_emp into v_ename, v_sal, v_deptno;
 	 		if c_emp%NOTFOUND then
   				exit;
   			end if;
   			dbms_output.put_line(v_ename||' '||v_sal||' '||v_deptno);
   		end loop;
   		close c_emp;
 	end;


Example 10 (Input):
    declare
    	v_ename emp.ename%TYPE;
    	v_sal emp.sal%TYPE;
    	v_deptno emp.deptno%TYPE;
    	cursor c_emp IS
    		select ename, sal, deptno
    		from emp
    		where job = ‘&job_desc’;
    begin
   	open c_emp;
   	loop
   		fetch c_emp into v_ename, v_sal, v_deptno;
   		if c_emp%NOTFOUND then
   			exit;
   		end if;
   		dbms_output.put_line(v_ename||' '||v_sal||' '||v_deptno);
   	end loop;
   	close c_emp;
 	end;

Example 11:

     declare
    	v_empno emp.empno%TYPE;
    	v_ename emp.ename%TYPE;
    	v_job emp.job%TYPE;
    	v_mgr emp.mgr%TYPE;
    	v_hiredate emp.hiredate%TYPE;
    	v_sal emp.sal%TYPE;
    	v_comm emp.comm%TYPE;
    	v_deptno emp.deptno%TYPE;
   	cursor c_emp is
   		select *
   		from emp
   		where job = '&emp_job';
   begin
   	open c_emp;
   	loop
  		fetch c_emp into v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno;
   	if c_emp%NOTFOUND then
   		exit;
   	end if;
   		insert into employees
   			values(v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno);
   	end loop
 	  	commit;
 	 end;
Attachments: None

Tags: Orcale PLSQL For Loop Print Insert Select Cursor

Total Views: 814

My name is Jean-Mathieu

I created this website so other people could enjoy finding useful stuff easier. If you have any question do not hesitate to contact me.

jean8mathieuCreated on 02/11/15 and updated on 02/25/15


affiliate_link

Disclosure: We are a website that needs compensation to operate like any other website on the internet.
We may receive consideration for our reviews but we are totally unbiased and do not accept paid reviews or fake reviews claiming to be something they are not.