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;