create [or replace] procedure 过程名[(参数列表)]  
as
        PLSQL程序体；【begin…end;/】

CREATE [OR REPLACE] FUNCTION 函数名【(参数列表) 】
 RETURN  返回值类型
AS
PLSQL子程序体；
【begin…end;/】

CREATE OR REPLACE PROCEDURE hello
AS
  BEGIN
    dbms_output.put_line('hello world');
  END;

BEGIN
  hello();
END;

CREATE or REPLACE PROCEDURE bb(pempno in NUMBER)
  AS
  BEGIN
    UPDATE EMP
    SET sal = sal * 1.2
    WHERE empno = pempno;
  END;

BEGIN
    bb(7369);
  END;

CREATE OR REPLACE PROCEDURE find(pempno IN NUMBER, psal OUT VARCHAR2, pename OUT VARCHAR2, pjob OUT VARCHAR2)
AS
  BEGIN
    SELECT
      ename,
      sal,
      job
    INTO pename, psal, pjob
    FROM emp
    WHERE empno = pempno;
  END;

DECLARE
  psal   emp.sal%TYPE;
  pename emp.ename%TYPE;
  pjob   emp.job%TYPE;
BEGIN
  find(7369, psal, pename, pjob);
  dbms_output.put_line(psal || pename || pjob);
END;/

CREATE OR REPLACE FUNCTION findEmpIncome(pempno IN NUMBER)
  --这里指定的是返回值类型
  RETURN NUMBER
AS
  income NUMBER;
  BEGIN
    SELECT sal * 12
    INTO income
    FROM emp
    WHERE empno = pempno;
    /*在PLSQL中一定要有return语句*/
    RETURN income;
  END;

DECLARE
  income number;
BEGIN
  income := findEmpIncome(7369);
  dbms_output.put_line(income);
END;/

[2017-07-11 13:58:14] [65000][6550] ORA-06550: 第 4 行, 第 10 列: 
PLS-00103: 出现符号 "="在需要下列之一时：
 := . ( @ % ;
ORA-06550: 第 4 行, 第 31 列: 
PLS-00103: 出现符号 ";"在需要下列之一时：
 . ( ) , * % & -
   + / at mod remainder rem <an exponent (**)> and or ||
   multiset
ORA-06550: 第 7 行, 第 4 列: 
PLS-00103: 出现符号 "end-of-file"在需要下列之一时：
 end
   not pragma final instantiable order overriding static member
   constructor map

CREATE OR REPLACE FUNCTION findEmpNameAndJobAndSal(pempno IN NUMBER, pjob OUT VARCHAR2, income OUT NUMBER)
  --这里指定的是返回值类型
  RETURN VARCHAR
AS
  /*查询出来的字段与列名相同，就使用列名相同的类型就行了。*/
  pename emp.ename%TYPE;
  BEGIN
    SELECT
      sal,
      ename,
      job
    INTO income, pename, pjob
    FROM emp
    WHERE empno = pempno;
    /*在PLSQL中一定要有return语句*/
    RETURN pename;
  END;

DECLARE
  /*输出的字段与列名的类型是相同的。*/
  income emp.sal%TYPE;
  pjob   emp.job%TYPE;
  pename emp.ename%TYPE;
BEGIN
  pename := findEmpNameAndJobAndSal(7369, pjob, income);
  dbms_output.put_line(pename || pjob || income);
END;/

CREATE  [or REPLACE] TRIGGER  触发器名
   {BEFORE | AFTER}
   { INSERT | DELETE|-----语句级
      UPDATE OF 列名}----行级
   ON  表名
    -- 遍历每一行记录
   [FOR EACH ROW]
   PLSQL 块【declare…begin…end;/】

CREATE OR REPLACE TRIGGER insertempTiriger
BEFORE
INSERT
  ON EMP
  BEGIN
    dbms_output.put_line('helloword');
  END;

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (1, '2', '3', 4, NULL, NULL, NULL, 10);

CREATE OR REPLACE TRIGGER securityTrigger
BEFORE
INSERT
  ON EMP
  DECLARE
    pday  VARCHAR2(10);
    ptime NUMBER;
  BEGIN
    /*得到星期几*/
    SELECT to_char(sysdate, 'day')
    INTO pday
    FROM dual;
    /*得到时间*/
    SELECT to_char(sysdate, 'hh24')
    INTO ptime
    FROM dual;
    IF pday IN ('星期六', '星期日') OR ptime NOT BETWEEN 7 AND 23
    THEN
      RAISE_APPLICATION_ERROR('-20000', '非工作事件，请工作时间再来！');
    END IF;
  END;

CREATE OR REPLACE TRIGGER checkSalTrigger
BEFORE
UPDATE OF sal
  ON EMP
FOR EACH ROW
  BEGIN
    IF :new.sal <= :old.sal
    THEN
      RAISE_APPLICATION_ERROR('-20001', '你涨的工资也太少了把！！！！');
    END IF;
  END;