Oracle Database comprehensive 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 console 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 sequence
Create sequence SEQ_EMPLOYEE
Minvalue 1
Max value 9999999999999999999999999999
Start with 1
Increment by 1
Cache 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.
Declare
I number;
Begin
For I in 100
Loop
Insert 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
-- Define an explicit cursor 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;
Begin
Open c;
Loop
Fetch c into v_record;
Exit when c % notfound;
Dbms_output.put_line (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)
Is
Begin
For x in (select id, salary, name from (select * from employee where salary between iminsalary and imaxsalary) order by salary)
Loop
Dbms_output.put_line (to_char (x. id) | to_char (x. salary) | x. name );
End loop;
End;
/
-- Call a 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 number
Is
Begin
Update employee set salary = salary + salary * 0.05 where salary <2000 and name like 'King % ';
Commit;
If SQL % rowcount = 0 then
Return 1;
Else
Return 0;
End if;
End;
/
6. Write an anonymous statement block to execute the f_employee function and print the execution result.
Declare a number;
Begin
A: = f_employee ();
Dbms_output.put_line (to_char ());
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_emp
Is
V_count number;
Begin
Select count (*) into v_count from user_tables where table_name = 'employe ';
If v_count = 0 then
Return;
Else
Execute 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;