Oracle variable statement exercise

Oracle variable statement exercise

-- Declare A variable and assign it the value declare v_bonus number (8); beginselect id * 6 into v_bonus from A where Id = 5; DBMS_OUTPUT.PUT_LINE ('bonus '| to_char (v_bonus); end; select * from emp -- copy the type structure declare v_ename emp of a field. ename % type; v_sal emp. sal % type; c_tax_rate constant number (3,2): = 0.03; v_sal_tax v_sal % type; begin select ename, sal into v_ename, v_sal from emp where empno = & eno; v_sal_tax: = v_sal * c_tax_rate; Clerk ('employee name: '| v_ename); dbms_output.put_line ('salary:' | v_sal); dbms_output.put_line ('income tax '| v_sal_tax); end; select * from empdeclare v_emp emp % rowtype; -- same as columns in the emp table, begin select * into v_emp from emp where empno = & eno; -- assign all records in the emp table to v_emp, & indicates entering a new value dbms_output.put_line ('employee name: '| v_emp.ename); dbms_output.put_line ('salary: '| v_emp.sal); end; -- copy the data structure of several fields in a record. declare type emp_record_type is record (name emp. ename % type, salary emp. sal % type, job emp. job % type); v_emp_record emp_record_type; begin select ename, sal, job into v_emp_record from emp where empno = & eno; dbms_output_line ('employee name: '| v_emp_record.name ); begin ('salary: '| v_emp_record.salary); dbms_output.put_line ('position:' | v_emp_record.job); end; insert into emp select * from emp -- copy the table structure of the entire table, copy declare type emp_table_type is table of emp % rowtype index by binary_integer; v_emp_table emp_table_type; begin select ename, sal into v_emp_table (1) row by row ). ename, v_emp_table (1 ). sal from emp where empno = 7369; select ename, sal into v_emp_table (2 ). ename, v_emp_table (2 ). sal from emp where empno = 7370; dbms_output.put_line ('employee name: '| v_emp_table (1 ). ename | 'salary: '| v_emp_table (1 ). sal); dbms_output.put_line ('employee name: '| v_emp_table (2 ). ename | 'salary: '| v_emp_table (2 ). sal); end; -- use when loop to loop declare v_ I number: = 1; v_s number: = 0; begin loop exit when v_ I> 100; v_s: = v_s + v_ I; v_ I: = v_ I + 1; end loop; dbms_output.put_line ('integers and: '| v_s) within 100; end; -- Use A while loop; declare v_ I number: = 1; v_s number: = 0; begin while v_ I <= 100 loop v_s: = v_s + v_ I; v_ I: = v_ I + 1; end loop; dbms_output.put_line ('integers less than 100 and: '| v_s); end; -- calculate the natural number within 100. declare v_s number: = 0; begin for v_ I in 1 .. 100 loop v_s: = v_s + v_ I; end loop; dbms_output.put_line ('natural number less than 100: '| v_s); end; -- calculate the prime number declare v_m number: = 101; v_ I number; v_n number: = 0; begin while v_m <110 loop v_ I: = 2; loop exit when v_ I> v_m-1; if mod (v_m, v_ I) = 0 then v_ I: = 0; exit; end if; v_ I: = v_ I + 1; end loop; if v_ I> 0 then v_n: = v_n + 1; dbms_output.put_line ('quarter '| v_n | 'prime number is' | v_m); end if; v_m: = v_m + 2; end loop; end;