function

Functions are divided into two kinds, one is the function of oracle database itself, and the other is the function written by users themselves.

Grammar for Defining Functions

create or replace function function function name
 (parameter 1 in | out | in out data type,
    .......
    Parameter 2 in | out | in out data type
as
begin 
    SQL statement
    return result;
end;

Exercise: Calculate the sum of two numbers

create or replace function sum_func
(num1 in number,num2 in number)
return NUMBER
as
begin
  return num1+num2;
exception
  when others then dbms_output.put_line('Wrong calculation');
end;

Test:

select sum_func(12,13)from dual

Exercise: Adding data to functions

create or replace function car_add_func
(car car%rowtype)
return NUMBER
as
BEGIN
  INSERT Into car values(car_seq.nextval,car.type,car.price,car.create_date,car.exhaust);
  COMMIT;
  return 1;
EXCEPTION
  when OTHERS then rollback;
  dbms_output.put_line('Add failure');
  return 0;
END;

Exercise: Calling Functions of Functions

set serveroutput on;
exec car_add_pro('Ford MengDiOu','2.0T',200000.00,'1-1 month -15');

declare car_ car%rowtype;
begin
  car_.type:='JEEP Free light';
  car_.price:=250000.00;
  car_.create_date:='25-9 Month 17';
  car_.exhaust:='2.5T';
  dbms_output.put_line('The number of rows affected is'||car_add_func(car_));
end;

view

Views are data that already exist, and new data sets are obtained through certain operation rules. This allows users to customize data sets more flexibly, while views provide a control strategy for data security.

The essence of view is the definition of relational operation.

Characteristics of Views

(Virtual tables, to simplify queries)

Advantages of views:

• Package query

  Although the database can store massive data, it is impossible to create data tables for each relationship in data table design. For example, for the student table, students'attributes include student number, name, age, address and other information; while the student achievement table only stores information such as student number, subject, grade and so on. Now we need to obtain the student name and score information, so long we need to create a relationship, which needs to include student name, subject, score and so on. However, it is inappropriate to create a new data table for the relationship and fill it with actual information for query. Because this practice has obviously caused a lot of data redundancy in the database.

  Views are the best strategy to solve this problem, because views can store query definitions (or relational operations), so once you use views to store query definitions, it's like storing a new relationship. Users can directly manipulate the relationships stored in the view as if they were facing a real data table.

• Flexible control security

1. Is a virtual table in a database
2. Results from query operations
3. Structure with common tables
4. Can't store data
5. Modifications to views will affect the actual data table

Views in oracle can be divided into four categories according to the way they are created and used: relational view, embedded view, object view and materialized view.

Adding and deleting views

Add view

create or replace view emp_dept_view
as select * from emp NATURAL JOIN dept;

Delete view

drop view emp_dept_view;

Synonyms (SYNONYM)

Syntax:

Synonym creation grammar:

create [public] synonym synonym name for user name. table name;

Delete synonyms:

drop synonym emp;

Exercise: Synonyms

synonym: An alias equivalent to an object.

--Setting synonyms can share tables that do not belong to the table space of this account, and can operate in other table spaces.
--Public synonyms can be manipulated in any table space
--Create Synonyms
--create synonym access_ for sys.ACCESS$;

--Creating Public Synonyms
create public synonym access_ for sys.ACCESS$;

--drop synonym
drop synonym access_;

--Find the contents of another table based on synonyms
select * from access_;

cursor

• Cursors are used to process multiple rows of records retrieved from the database (using select statements).

• Using cursors, the program can process and traverse the entire set of records returned one by one.

Classification of cursors

• Static cursor: The result set has been determined.
  
  • Implicit cursors: All dml statements are implicit cursors.
  • Explicit cursor: User display declaration.
• Dynamic cursor

Cursor syntax

Declare cursor:

cursor cursor_name is
<select statements>
(When usedforWhen circulating, do not useopenandcloseCursor)

Open the cursor:

open cursor_name;

The results are put into PL/SQL variables:

fetch cursor_name into list_of variables; (explicitly, open and close must be used to open and close)

Close the cursor:

close cursor_name;

Properties of cursors

PS:

Exercise: Basic Use of Cursors

set serveroutput on;
--Query all employee information and print information
declare
  CURSOR emp_info is select * from emp;
  emp_ emp%rowtype;
begin
  open emp_info;--open
  loop
    fetch emp_info into emp_;--Put cursor data(Result set)Put it in a variable.
    exit when emp_info%notfound;--End the loop when the next data does not exist
    dbms_output.put_line('The employee number is'||emp_.empno||',The employee's name is'||emp_.ename||',The basic wage is'||emp_.sal);
  end loop;
  close emp_info;--Close cursor
end;

declare 
  cursor emp_info is select * from emp;
  emp_ emp%rowtype;
begin
  for emp_ in emp_info loop
    dbms_output.put_line('The employee number is'||emp_.empno||',The employee's name is'||emp_.ename||',The basic wage is'||emp_.sal);
  end loop;
end;

Exercise: Update statement of cursor

- Write a PL/SQL program block to increase the salary of all employees whose names start with'A'or'S' by 10% of their basic salary (modifying the emp1 table)

create table emp1 as select * from emp;--Back up data from a table to a new table
set serveroutput on;
declare 
  cursor e_cur is select * from emp where ename like 'A%' or ename like 'S%' ;
  emp_ emp%rowtype;
begin
  for emp_ in e_cur loop
    emp_.sal:=emp_.sal*1.1;
    update emp1 set sal=emp_.sal where empno=emp_.empno;
  end loop;
  commit;
exception 
  WHEN others then rollback;
end;

package

A package is a pattern object that combines various logically related types, constants, variables, exceptions, and subroutines. The package usually consists of two parts: the package description and the inclusion body, but sometimes the inclusion body is not needed. Description (abbreviated as spec) is an application program interface; it declares available types, variables, constants, exceptions, cursors and subroutines, and the package part fully defines cursors and subroutines, and implements the contents of the description.

• A package is a PL/SQL structure consisting of related objects stored together.

• Custom types, variables, cursors, procedures and functions associated with logical composition.

Package composition

• Specification of Package (also known as Baotou)

  1. Used to define constants, variables, cursors, procedures, and functions for interface with programs
  2. It can be referenced in a security or called by an external program.

• Main body of package

  1. It is the implementation of package specification, including variables, cursors, procedures and functions.

  2. Content in the package cannot be invoked by an external application.

Advantages of package

Modularization, easy application design, information hiding, additional functions and good performance.

Creation of Package Specification

Specification for creating packages
--Package specification
create or replace package test_package
as
--Declare a stored procedure 
procedure add_emp_pro(emp_ emp1%rowtype);

--Declare a function 
function sum_func(num1 number,num2 number)
return number;

end test_package;

Inclusion creation

Package invocation

Exercise: The main part of the package

create or replace PACKAGE body test_package
as
-- Implementing stored procedures
PROCEDURE add_emp_pro(emp_ in emp1%rowtype)
as
begin
  dbms_output.put_line('Successfully add a data');
end;

--Implementation function 
function sum_func(num1 number,num2 number)
return NUMBER
as
begin
  return num1+num2;
end;

end test_package;

Exercise: Package invocation

set serveroutput on;

declare 
  emp_ emp1%rowtype;
begin
  emp_.empno:=9527;
  emp_.ename:='Lao Zhang';
  test_package.add_emp_pro(emp_);
end;

begin
  dbms_output.put_line('The sum of two numbers is'||test_package.sum_func(3,5));
end;