Oracle Database comprehensive questions, oracle Database questions

Source: Internet
Author: User

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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.