oracle中getnum,学习笔记:开发DBA在工作当中 pl/sql 常用语法汇总案例

天萃荷净

开发DBA在工作当中 pl/sql 常用语法汇总案例

1、procedure语法分析案例

CREATE [ OR REPLACE] PROCEDURE [schema.]procedure_name

[parameter_lister]

{AS|IS}

declaration_section

BEGIN

executable_section

[EXCEPTION

exception_section]

END [procedure_name]

--举例

create or replace

procedure dos_fx

is

cursor c1 is

select get from dos_gj;

begin

for c2 in c1 loop

insert into dos_gj_1(ip,gettime,get)values(REGEXP_SUBSTR( c2.get, '(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])(\.(\d{1,2}|1\d\d|2[0-4]\d|25[0-5])){3} '),

REGEXP_SUBSTR( c2.get, '^(\d{4})-(\d{2})-(\d{2}) (\d{2}:\d{2}:\d{2})'),c2.get);

end loop;

commit;

end;

2、function语法分析案例

CREATE [ OR REPLACE] FINCTION [schema.]function_name

[parameter_list]

RETURN returning_datatype

{AS|IS}

declaration_section

BEGIN

executable_section

[EXCEPTION]

exception_section

END [procedure_name]

--举例

CREATE OR REPLACE function fn_md5(input_string VARCHAR2) return varchar2

IS

raw_input RAW(128) := UTL_RAW.CAST_TO_RAW(input_string);

decrypted_raw RAW(2048);

error_in_input_buffer_length EXCEPTION;

BEGIN

sys.dbms_obfuscation_toolkit.MD5(input => raw_input,checksum => decrypted_raw);

return rawtohex(decrypted_raw);

END;

3、package语法分析案例

--header

CREATE [OR REPLACE] PACKAGE package_name

{AS|IS}

public_variable_declarations |

public_type_declarations |

public_exception_declarations |

public_cursor_declarations |

function_declarations |

procedure_specifications

END [package_name]

--body

CREATE [OR REPLACE] PACKAGE BODY package_name

{AS|IS}

private_variable_declarations |

private_type_declarations |

private_exception_declarations |

private_cursor_declarations |

function_declarations |

procedure_specifications

END [package_name]

--举例

--header

create or replace

package pk_t1

as

procedure get_num(getnum in number,aname varchar2);

end pk_t1;

--body

create or replace

package body pk_t1

as

procedure get_num(getnum in number,aname varchar2)

is

begin

insert into shell_1 values(getnum,aname);

end;

end pk_t1;

4、trigger语法分析案例

CREATE [OR REPLACE] TRIGGER trigger_name

{before|after|instead of} event

ON {table_or_view_name|DATABASE}

[FOR EACH ROW[WHEN condition]]

trigger_body

--举例

create or replace trigger add_shell

before update

on shell_1 for each row

declare

begin

if :OLD.name!=:new.name then

:new.name:='ggggg';

end if;

end;

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:开发DBA在工作当中 pl/sql 常用语法汇总案例