function

Functions can return, processes can not, processes can be out, functions can also be out, but basically no

create function Function name
(Parameters)
return Return data type
is
begin
end;

//Create a function twice, override it for the first time, and do not report an existing error. Add or replace
create or replace function Function name
(Parameters)//No restriction on number
return Return data type//Write no length
is//Define temporary variables, no can not be written
begin
end;

Define function

create or replace function f_sun
(
v_a number,v_b number
)
return number
is
begin
return nvl(v_a,0)+nvl(v_b,0);
end;

//Reference
select f_sun(3,5) from dual;
select ename,f_sun(sal,comm) from emp;

Practice

News sheets, headlines

varchar A Chinese character takes up 2 characters

nvarchar A Chinese character is one character

Headings over 15 characters display the first 12 plus...

create or replace function f_char
(
v_a nvarchar2
)
return nvarchar2
is
begin
  if length(v_a)<15 then
    return v_a;
  else
   return (substr(v_a,1,12)||'...');
  end if;
  end;
  
  select f_char('I asked about the merged data. i Meet i I love to see the sky when I go to Oh i forehead u hair') from dual;
  
  //Method 2:
  create or replace function f_char
(
v_a nvarchar2，len num
)
return nvarchar2
is
begin
  if length(v_a)<len then
    return v_a;
  else
   return (substr(v_a,1,len-3)||'...');
  end if;
  end;

process

1. No parameters

create or replace procedure p_addsal
is 
begin
 update emp set sal=sal+100;
end;

exec p_addsal;
//implement
//No restrictions on process results

2. There are parameters and results.

create or replace procedure p_getsal
(
  v_deptno in number,--//Perform required parameters
  v_maxsal out number,--//Save the results of execution
  v_minsal out number
)
is
begin
select max(sal),min(sal) into v_maxsal,v_minsal from emp where deptno=v_deptno;
end;

//Execution process
declare
v_max emp.sal%type;
v_min emp.sal%type;
begin
p_getsal(&deptno,v_max,v_min);
dbms_output.put_line('Maximum wage'||v_max||'  minimum wage'||v_min);
end;

Practice

Search the name of the highest salary and the lowest salary for the position according to the position

create or replace procedure p_getname
(
v_job in varchar2,
v_maxsalename out varchar2,
v_minsalename out varchar2
)
is 
begin
  select ename into v_maxsalename from emp where sal=(select max(sal) from emp where job=v_job);
  select ename into v_minsalename from emp where sal=(select min(sal) from emp where job=v_job);
end;
//implement
declare
v_maxsalname emp.ename%type;
v_minsalname emp.ename%type;
begin
  p_getname(&job,v_maxsalname,v_minsalname);
  dbms_output.put_line(v_maxsalname||'  '||v_minsalname);
end;

--//Set server routput on; open output

Input-output parameters

in out can be either input or output

Inquire about his salary according to the employee's number

create or reolace procedure p_getsal
(
  v_emono in out number
)
is
begin
select sal into v_empno from emp where empno=v_empvo;
end;

declare
v_empno number(10);
begin
v_emp:=7788;
p_getsal(v_empno);
dbms_output.put_line('wages'||v_empno);
end;

Return result is a cursor

There are well-defined dynamic cursors (weak type) sys_refcursor in the system

Check the employee's name, position and salary according to the department number

create or replace procedure p_getemp
(
v_deptno in number,
v_result out sys_refcursor
)
is 
begin
open v_result for select ename,job,sal from emp where deptno=v_deptno;
end;

//Calls to define cursors
declare
mycur sys_recursor;
type t_record is record
(
v_name emp.ename%type,
v_job emp.job%type,
v_sal emp.sal%type,
);
v_row t_record;
begin 
p_getemp(&deptno,mycur);
loop
fetch mycur into v_row;
exit when mycur%notfound;
dbms_output.put_line(v_row.v_name||' '||v_row.v_job||' '||v_row.v_sal);
end loop;
close mycur;
end;

Practice

Enter season to_char(q) and return the employee's name, position, department name for this season.

create or replace procedure p_getemp
(
v_jijie in varchar2,
v_result out sys_refcursor
)
is
begin
  open v_result for select ename,job,dname from emp
  inner join dept on dept.deptno=emp.deptno where hiredate in(
  select hiredate from emp where decode(to_char(hiredate,'q'),1,'spring',2,'summer',3,'autumn',4,'winter')=v_jijie);
  -----------------------------------to_char(hiredate,'q')=instr('Spring, summer, autumn and winter','spring');
end;

declare
mycur sys_refcursor;
type t_record is record
(
v_ename emp.ename%type,
v_job emp.job%type,
v_dname dept.dname%type
);---A Type
v_row t_record;--Variables can be defined by type
begin
  p_getemp(&v_jijie,mycur);
  loop
    fetch mycur into v_row;
    exit when mycur%notfound;
    dbms_output.put_line(v_row.v_ename||' '||v_row.v_job||' '||v_row.v_dname);
  end loop;
  close mycur;
  end;

trigger

Row level

Table level

According to the time, it can be divided into: before,after.

                       insert,update,delete

Cannot be invoked after triggering

Automatic triggering after satisfying certain conditions

Create or replace trigger < trigger name >

Raise an exception: raise_application_error (- 20000 to 2000)

Special variables

new - ----------------------------------------------------------------------------------------------------------------------------------------

old - ----------------------------------------------- Refers to the data before the modification of the previous column deletion.

update is used only for a column of data, of column name on table name

insert,delete updates a row of data, directly, on table name

of column name not written represents all columns for the entire table

Only No. 15 is allowed to modify wages

create trigger tr_before_update_emp_sal
before update of sal on emp
for each row   --//There are several rows of data, trigger several times, without this sentence, trigger only once
declare
begin
 if to_number(to_char(sysdate,'dd'))<>15 then
 --//Causing anomalies
 raise_application_error(-20001,'No wage changes are allowed today');
 end if;
end;

update emp set sal=sal+100;

Practice

Account Statement

Account, password, username, opening date, balance

10086, 123456, China Mobile, 2019-7-10,10000

Transaction sheet

Account, transaction time, type, amount, notes

10086, 2019-7-12, deposit, 500, pocket money

create table account
(accid number(10) not null,
 password varchar2(20) not null,
 username varchar2(20) not null,
 opendate date not null,
 balance number(10,2)
)
create table trade
(
  accid number(10) not null,
  tradedate date not null,
  tradetype varchar(10) not null,
  money number(10,2) not null,
  remark number(20)
)
insert into account values(10086,'123456','China Mobile',sysdate,10000);

create or replace trigger tr_after_insert_trade
after insert on trade
for each row
declare
begin
if :new.tradetype='Deposit' then
 update account set balance=balance+:new.money where accid=:new.accid;
 else
  update account set balance=balance-:new.money where accid=:new.accid;
  end if;
end;

insert into trade values(10086,sysdate,'Deposit',100,null);

Create a trigger that can have multiple actions

craete or replace trigger tr_test
after insert or delete or update on account
for each row
declare
begin
if inserting then
  dbms_output.put_line('Data being added:'||:new.accid);
elsif deleting then
  dbms_output.put_line('Data being added:'||:old.accid);
elsif updating then
  dbms_output.put_line('Data being added:'||:old.accid);
end if;
end;

Disable a trigger

Alter trigger < trigger name > disable

Re-enable triggers

Disable all triggers

data packet

CREATE [OR REPLACE] PACKAGE <Packet Name> AS

           --Public Types and Object Statements

           --Subprogram description

  END;

//create package
create or replace package mypackage
is 
type t_record is record
(
  ename varchar2(50),
  job varchar2(50),
  sal number(10,2)
);
type t_cursor is ref cursor return t_record; --//cursor type
function sum(v_a number,v_b number)return number;
procedure p_getemp(v_deptno in number,v_cursor out t_cursor);
end;

//Create a package implementation
create or replace package body mypackage
is 
function sum(v_a number,v_b number)return number
is 
begin
return v_a+v_b;
end;
procedure p_getemp(v_deptno in number,v_cursor out t_cursor)
is
begin
open v_cursor for select ename,job,sal from emp where deptno=v_deptno;
end;
end;

view

Virtual Query

Views themselves do not save data

Only select is supported

Create Views
create or replacce view v_emp
as
select * from emp;

select * from v_emp;
//Equivalent to
select * from
(
  select * from emp
);

eg:

create or replacce view v_emp
as
select * from emp
inner join dept on dept.deptno=emp.deptno
inner join salgrade on emp.sal between losal and hisal 

Alternative triggers

Synonym

create synonym mm for emp;

emp can be replaced by mm

Indexes

Speed up queries (at least 40W rows of data are effective)

Most of them are used for database optimization

create index i_1 on emp(empno asc);

regular expression

regexp_like ''

select * from emp where regexp_like(ename ,'\d{1,10}');

At least one, and at most 10 in the middle section, \ d number

exception handling

exception

when <> then

Execute sql statement dynamically

declare
v_sql varchar2(100);
v_ename varchar2(50);
begin
v_sql:='select ename from emp where empno=7788';

execute immediate v_sql into v_ename;
dbms_output.put_line('  ');
end;