Oracle编程基础练习题(国外英文资料).docVIP

Oracle编程基础练习题(国外英文资料) Guanghua garden learning network For more information, please poke: /study09/ 1, output 99 multiplication table Declare I int; J int; Begin For, I, in, 1..9, loop For, J, in, 1..I, loop Dbms_output.put (i||*||j||=|| (i*j) ||); End loop; Dbms_output.put_line ( ); End loop; End; 2. The salary and position of the employee whose name is SMITH Declare V_name emp1.ename%type:=name; V_emp emp1%rowtype; Begin Select, Sal, job, into, v_emp.sal, v_emp.job, from, EMP, where, ename=upper (v_name); Dbms_output.put_line (name is:||v_name||salary:||v_emp.sal||, post is:||v_emp.job); End; 3, receive department number, output department name and location (table DEPT) Declare V_no dept1.deptno%type:=deptno; V_dept dept1%rowtype; Begin Select, DNAME, LOC, into, v_dept.DNAME, v_dept.loc, from, Dept, where, deptno=v_no; Dbms_output.put_line (` sector number is:||v_no||, department name is:||v_dept.dname|| department, geographical location is:||v_dept.loc); End; 4, receive employee number, output the employees salary and commission, without commission, use 0 instead. (implemented with%type) Declare V_no emp1.empno%type:=no; V_sal emp1.sal%type; V_comm m%type; Begin Select, Sal, NVL (comm, 0), into, v_sal, v_comm, from, emp1, where, empno=v_no; Dbms_output.put_line (number is:||v_no||, salary||v_sal||, commission||v_comm); End; 5, receive employee number, output all information of this employee, without commission, replace with 0. (implemented with%rowtype) Declare V_no emp1.empno%type:=no; V_emp emp1%rowtype; Begin Select, ename, job, Mgr, hiredate, Sal, NVL (comm, 0), deptno, into, v_emp.ename, v_emp.job, v_emp.mgr, v_emp.hiredate, v_emp.sal,, v_m, v_emp.deptno, from, emp1, where, empno=v_no; Dbms_output.put_line (number:||v_no|| Name:||v_emp.ename||position:||v_emp.job|| entry date:||boss number:||v_emp.mgr||v_emp.hiredate|| salary||v_emp.sal||Commission||v_m|| Department No.||v_emp.deptno); End; 6, receive an employee name or employee number, judge his job, accor