remainder_issue_followup

View All Scripts Login to Run Script

Script Name remainder_issue_followup
Description Shows difference when round() and trunc() are used on the m/n results.
Area SQL General / Functions
Contributor David Fitzjarrell
Created Monday June 13, 2016

Statement 1

set serveroutput on size 1000000 echo on

Unsupported Command

Statement 2

BEGIN 
   DBMS_OUTPUT.put_line (MOD (15, 2)); 
   DBMS_OUTPUT.put_line (REMAINDER (15, 2)); 
   DBMS_OUTPUT.put_line (MOD (15, 3)); 
   DBMS_OUTPUT.put_line (REMAINDER (15, 3)); 
   DBMS_OUTPUT.put_line (MOD (15, 4)); 
   DBMS_OUTPUT.put_line (REMAINDER (15, 4)); 
   DBMS_OUTPUT.put_line (MOD (15, 5)); 
   DBMS_OUTPUT.put_line (REMAINDER (15, 5)); 
   DBMS_OUTPUT.put_line (MOD (15, 6)); 
   DBMS_OUTPUT.put_line (REMAINDER (15, 6)); 
   DBMS_OUTPUT.put_line (MOD (15, 7)); 
   DBMS_OUTPUT.put_line (REMAINDER (15, 7)); 
   DBMS_OUTPUT.put_line (MOD (15, 8)); 
   DBMS_OUTPUT.put_line (REMAINDER (15, 8)); 
END;

Statement 3

create or replace function remainder_func(p_num1 in number, p_num2 in number) 
return number is 
	v_rmdr	number; 
	v_x	number:=1; 
begin 
	v_x := trunc(p_num1/p_num2, 0); 
	v_rmdr := p_num1 - (v_x * p_num2); 
	return(v_rmdr); 
end;

Function created.

Statement 4

BEGIN 
   DBMS_OUTPUT.put_line (MOD (15, 2)); 
   DBMS_OUTPUT.put_line (REMAINDER_func (15, 2)); 
   DBMS_OUTPUT.put_line (MOD (15, 3)); 
   DBMS_OUTPUT.put_line (REMAINDER_func (15, 3)); 
   DBMS_OUTPUT.put_line (MOD (15, 4)); 
   DBMS_OUTPUT.put_line (REMAINDER_func (15, 4)); 
   DBMS_OUTPUT.put_line (MOD (15, 5)); 
   DBMS_OUTPUT.put_line (REMAINDER_func (15, 5)); 
   DBMS_OUTPUT.put_line (MOD (15, 6)); 
   DBMS_OUTPUT.put_line (REMAINDER_func (15, 6)); 
   DBMS_OUTPUT.put_line (MOD (15, 7)); 
   DBMS_OUTPUT.put_line (REMAINDER_func (15, 7)); 
   DBMS_OUTPUT.put_line (MOD (15, 8)); 
   DBMS_OUTPUT.put_line (REMAINDER_func (15, 8)); 
END;

Statement 5

create or replace procedure remainder_test(p_num1 in number, p_num2 in number) 
is 
	v_t_rmdr	number; 
	v_r_rmdr	number; 
	v_tx	number:=1; 
	v_rx	number:=1; 
begin 
	v_tx := trunc(p_num1/p_num2, 0); 
	v_rx := round(p_num1/p_num2, 0); 
	v_t_rmdr := p_num1 - (v_tx * p_num2); 
	v_r_rmdr := p_num1 - (v_rx * p_num2); 
	dbms_output.put_line('Rounded:   '||v_rx||' (n*X): '||v_rx*p_num2||' Remainder: '||v_r_rmdr); 
	dbms_output.put_line('Truncated: '||v_tx||' (n*X): '||v_tx*p_num2||' Remainder: '||v_t_rmdr); 
end;

Procedure created.

Statement 6

BEGIN 
   REMAINDER_test (15, 2); 
   REMAINDER_test (15, 3); 
   REMAINDER_test (15, 4); 
   REMAINDER_test (15, 5); 
   REMAINDER_test (15, 6); 
   REMAINDER_test (15, 7); 
   REMAINDER_test (15, 8); 
END;

Rounded:   8 (n*X): 16 Remainder: -1
Truncated: 7 (n*X): 14 Remainder: 1
Rounded:   5 (n*X): 15 Remainder: 0
Truncated: 5 (n*X): 15 Remainder: 0
Rounded:   4 (n*X): 16 Remainder: -1
Truncated: 3 (n*X): 12 Remainder: 3
Rounded:   3 (n*X): 15 Remainder: 0
Truncated: 3 (n*X): 15 Remainder: 0
Rounded:   3 (n*X): 18 Remainder: -3
Truncated: 2 (n*X): 12 Remainder: 3
Rounded:   2 (n*X): 14 Remainder: 1
Truncated: 2 (n*X): 14 Remainder: 1
Rounded:   2 (n*X): 16 Remainder: -1
Truncated: 1 (n*X): 8 Remainder: 7

Statement 7
```
set echo off 
```
```
Unsupported Command
```

Statement 8

set serveroutput on size 1000000 echo on

Unsupported Command

Statement 9
```
set echo off 
```
```
Unsupported Command
```

Statement 10

set serveroutput on size 1000000 echo on

Unsupported Command

Statement 11

BEGIN 
   DBMS_OUTPUT.put_line (MOD (15, 2)); 
   DBMS_OUTPUT.put_line (REMAINDER (15, 2)); 
   DBMS_OUTPUT.put_line (MOD (15, 3)); 
   DBMS_OUTPUT.put_line (REMAINDER (15, 3)); 
   DBMS_OUTPUT.put_line (MOD (15, 4)); 
   DBMS_OUTPUT.put_line (REMAINDER (15, 4)); 
   DBMS_OUTPUT.put_line (MOD (15, 5)); 
   DBMS_OUTPUT.put_line (REMAINDER (15, 5)); 
   DBMS_OUTPUT.put_line (MOD (15, 6)); 
   DBMS_OUTPUT.put_line (REMAINDER (15, 6)); 
   DBMS_OUTPUT.put_line (MOD (15, 7)); 
   DBMS_OUTPUT.put_line (REMAINDER (15, 7)); 
   DBMS_OUTPUT.put_line (MOD (15, 8)); 
   DBMS_OUTPUT.put_line (REMAINDER (15, 8)); 
END;

Statement 12

create or replace function remainder_func(p_num1 in number, p_num2 in number) 
return number is 
	v_rmdr	number; 
	v_x	number:=1; 
begin 
	v_x := trunc(p_num1/p_num2, 0); 
	v_rmdr := p_num1 - (v_x * p_num2); 
	return(v_rmdr); 
end;

Function created.

Statement 13

BEGIN 
   DBMS_OUTPUT.put_line (MOD (15, 2)); 
   DBMS_OUTPUT.put_line (REMAINDER_func (15, 2)); 
   DBMS_OUTPUT.put_line (MOD (15, 3)); 
   DBMS_OUTPUT.put_line (REMAINDER_func (15, 3)); 
   DBMS_OUTPUT.put_line (MOD (15, 4)); 
   DBMS_OUTPUT.put_line (REMAINDER_func (15, 4)); 
   DBMS_OUTPUT.put_line (MOD (15, 5)); 
   DBMS_OUTPUT.put_line (REMAINDER_func (15, 5)); 
   DBMS_OUTPUT.put_line (MOD (15, 6)); 
   DBMS_OUTPUT.put_line (REMAINDER_func (15, 6)); 
   DBMS_OUTPUT.put_line (MOD (15, 7)); 
   DBMS_OUTPUT.put_line (REMAINDER_func (15, 7)); 
   DBMS_OUTPUT.put_line (MOD (15, 8)); 
   DBMS_OUTPUT.put_line (REMAINDER_func (15, 8)); 
END;

Statement 14

create or replace procedure remainder_test(p_num1 in number, p_num2 in number) 
is 
	v_t_rmdr	number; 
	v_r_rmdr	number; 
	v_tx	number:=1; 
	v_rx	number:=1; 
begin 
	v_tx := trunc(p_num1/p_num2, 0); 
	v_rx := round(p_num1/p_num2, 0); 
	v_t_rmdr := p_num1 - (v_tx * p_num2); 
	v_r_rmdr := p_num1 - (v_rx * p_num2); 
	dbms_output.put_line('m/n Rounded:   '||v_rx||' (n*X): '||v_rx*p_num2||' Remainder: '||v_r_rmdr); 
	dbms_output.put_line('m/n Truncated: '||v_tx||' (n*X): '||v_tx*p_num2||' Remainder: '||v_t_rmdr); 
end;

Procedure created.

Statement 15

BEGIN 
   REMAINDER_test (15, 2); 
   REMAINDER_test (15, 3); 
   REMAINDER_test (15, 4); 
   REMAINDER_test (15, 5); 
   REMAINDER_test (15, 6); 
   REMAINDER_test (15, 7); 
   REMAINDER_test (15, 8); 
END;

m/n Rounded:   8 (n*X): 16 Remainder: -1
m/n Truncated: 7 (n*X): 14 Remainder: 1
m/n Rounded:   5 (n*X): 15 Remainder: 0
m/n Truncated: 5 (n*X): 15 Remainder: 0
m/n Rounded:   4 (n*X): 16 Remainder: -1
m/n Truncated: 3 (n*X): 12 Remainder: 3
m/n Rounded:   3 (n*X): 15 Remainder: 0
m/n Truncated: 3 (n*X): 15 Remainder: 0
m/n Rounded:   3 (n*X): 18 Remainder: -3
m/n Truncated: 2 (n*X): 12 Remainder: 3
m/n Rounded:   2 (n*X): 14 Remainder: 1
m/n Truncated: 2 (n*X): 14 Remainder: 1
m/n Rounded:   2 (n*X): 16 Remainder: -1
m/n Truncated: 1 (n*X): 8 Remainder: 7

Statement 16
```
set echo off 
```
```
Unsupported Command
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database