Oracle Database questions

Oracle Database questions

Table Structure Description:

Create table employee (id number (10) not null, -- employee id salary number (10, 2) default 0 not null, -- salary name varchar2 (24) not null -- name ); -- enable the console to output set serverout on;

1. Create a sequence seq_employee. This sequence is automatically increased every time it is obtained. It starts from 1 and does not have a maximum value. It is always accumulated without repeating.

-- Create sequencecreate sequence SEQ_EMPLOYEEminvalue 1maxvalue 9999999999999999999999999999start with 1increment by 1cache 20;

 

 

1. Write a PL/SQL block and insert 100 data entries into the table user. employee. Insert the field id in the table using the sequence seq_employee. The salary and name fields can be filled in as needed.

 

Declarei number; beginfor I in 1 .. 100 loopinsert into employee values (seq_employee.nextval, 1950 + I, 'anonymous' | to_char (I); commit; end loop; end ;/

3. Write a statement block, define an explicit cursor in the statement block, sort by id in ascending order, and print the first 10 pieces of data in the table employee.

Declare -- defines an explicit cursor, which is arranged in ascending order of IDs. cursor c is select id, salary, name from (select * from employee order by id) where rownum <11; v_record c % rowtype; beginopen c; loopfetch c into v_record; exit when c % notfound; substring (to_char (v_record.id) | ',' | to_char (v_record.salary) | ', '| v_record.name); end loop; close c; end ;/

4. Create a stored procedure p_employee, enter the employee salary range, and return the employee ID, name, and salary result set. The result set is sorted in ascending order by employee salary.

Create or replace procedure p_employee (iminsalary in number, imaxsalary in number) isbeginfor x in (select id, salary, name from (select * from employee where salary between iminsalary and imaxsalary) order by salary) loopdbms_output.put_line (to_char (x. id) | to_char (x. salary) | x. name); end loop; end;/-- call the Stored Procedure call p_employee (1951,1956); exec p_employee (1951,1952 );

5. Create the f_employee function to update the employee's salary. The employee's salary is less than 2000 and the employee's salary is increased by 5%. If the employee's salary is not changed, 0 is returned if the update is successful. Otherwise, 1 is returned.

Create or replace function f_employee return numberisbeginupdate employee set salary = salary + salary * 0.05 where salary <2000 and name like 'King % '; commit; if SQL % rowcount = 0 thenreturn 1; elsereturn 0; end if; end ;/

6. Write an anonymous statement block to execute the f_employee function and print the execution result.

declare a number;begina:=f_employee();dbms_output.put_line(to_char(a));end;/

7. Create the Stored Procedure p_create_emp, which is used to determine whether the table's employee exists. If so, delete the table.

create or replace procedure p_create_empisv_count number;beginselect count(*) into v_count from user_tables where table_name='EMPLOYEE';if v_count=0 thenreturn;elseexecute immediate 'drop table employee';end if;end;/

8. Write an anonymous statement block for executing the Stored Procedure p_create_emp.

Exec p_create_emp;