create or replace procedure add_ord(
v_ordid number,
v_orddate date,
v_custid number,
v_shipdate date,
v_total number)
is
e_integrity exception;
e_shipdate  exception;
pragma exception_init(e_integrity,-2291);
begin
if v_shipdate>v_orddate then 
insert into ord values(v_ordid ,v_orddate ,v_custid ,v_shipdate 

date,v_total);
else
raise e_shipdate;
end if;
exceion 
when dup_val_on_index then
raise_application_error(-20001,'该订单已存在');
when e_integrity then 
raise_application_error(-20002,'该客户不存在');
when e_shipdate then 
raise_application_error(-20003,'交付日期不能早于预订日期');
end;
/

-------------------------------
set serveroutput on 
begin 
    dbms_output.put_line('Hello world');
end ;
/
--当使用dbms_output时,需将sql*plus的环境变量serveroutput设置为on

-------------------------------
declare 
  type c1 is ref cursor;
  emp_cursor c1;
  v_ename emp.ename%type;
  v_sal emp.sal%type;
begin 
  open emp_cursor for
    select ename,sal from emp where deptno=10;
  loop 
    fetch emp_cursor into v_ename,v_sal;
    exit when emp_cursor%notfound;
    dbms_output.put_line(v_ename);
  end loop;
  close emp_cursor;
end ;
/

------------------------------
变量 v_*
常量 c_*
游标 *_cursor
例外 e_*
表类型 *_table_type
表变量 *_table
记录类型 *_record_type
记录变量 *_record
-----------------------------
sql关键字、pl/sql关键字、数据类型  -》大写
标识符、参数、数据库对象和列       -》小写
------------------------------
只读事务:
会话A
set transaction read only ; <1>
select sal from emp where ename='SMITH';  --结果:2000  <3>
会话B
update emp set sal=3000 where ename='SMITH'; commit ; <2>
------------------------------
declare 
  type emp_record_type is record(
    ename emp.ename%type ,
    sal   emp.sal%type
  );
  emp_record emp_record_type;
begin 
  select ename ,sal into emp_record
  from emp where empno=&no;
  dbms_output.put_line('name:'||emp_record.ename);
  dbms_output.put_line('sal: '||emp_record.sal);
end;
/

---------------------------------
在PL/SQL块中直接使用select into语句是,该语句必须要返回一条数据,并且只能返回一条数据,否则

会触发PL/Sql例外或错误信息。NO_DATA_FOUND例外,TOO_MANY_ROWS例外,Where子句变量名不能与列名

相同

--------------------------------
declare 
  v_sal number(6,2);
begin 
  select sal into v_sal from emp 
  where lower(ename)=lower('&&name');
  if v_sal<2000 then 
    dbms_output.put_line('<2000');
  end if;
end;
/

--------------------------------
create table temp(cola int);
declare 
  i int:=1;
begin 
  loop
    insert into temp values(i);
    exit when i=10;
    i:=i+1;
  end loop;
end;
/

------------------------------
declare 
  i int :=1;
begin 
  while i<=10 loop
    insert into temp values(i);
    i:=i+1;
  end loop;
end ;
/
-------------------------------
for counter in [reverse]
lower_bound .. upper_bound loop
  statement1;
  statement2;
  ...
end loop;

eg:
begin 
  for i in reverse 1..10 loop 
    insert into temp values(i);
  end loop;
end;
/
------------------------------
set serveroutput on
declare 
  result int;
begin
  <<outer>>
  for i in 1..100 loop
  <<inner>>
    for j in 1..100 loop
      result := i*j;
      exit outer when result>=1000;
      exit when result=500;
    end loop inner;
    dbms_output.put_line(result);
  end loop outer;
  dbms_output.put_line(result);
end;
/

-------------------------------
type type_name is record(
  field_declaretion[,
  field_declaration]...
);
identifier type_name;
eg:
type emp_record_type is record(
  name   emp.ename%type,
  salary emp.sal%type,
  dno    emp.deptno%type
);
emp_record emp_record_type ;
...

-----------------------------
set serveroutput on 
declare
  type emp_record_type is record(
    name   emp.ename%type,
    salary emp.sal%type,
    dno    emp.deptno%type
  );
  emp_record emp_record_type;
begin 
  select ename,sal,deptno into emp_record
  from emp where empno=&no;
  dbms_output.put_line(emp_record.name);
end;
/

set serveroutput on 
declare
  type emp_record_type is record(
    name   emp.ename%type,
    salary emp.sal%type,
    dno    emp.deptno%type
  );
  emp_record emp_record_type;
begin 
  select ename,sal,deptno into emp_record.name,emp_record.salary,emp_record.dno
  from emp where empno=&no;
  dbms_output.put_line(emp_record.name);
end;
/

--------------------------------
declare 
  dept_record dept%rowtype;
begin
  dept_record.deptno:=50;
  dept_record.dname:='ZMC';
  dept_record.loc:='YIYANG';
  insert into dept values dept_record;
end;
/

declare 
  dept_record dept%rowtype;
begin
  dept_record.deptno:=60;
  dept_record.dname:='ZMC2';
  insert into dept(deptno,dname) values (dept_record.deptno,dept_record.dname);
end;
/
---------------------------------
set serveroutput on 
declare 
  cursor emp_cursor is
    select ename,sal from emp where deptno=10;
  v_ename emp.ename%type;
  v_sal   emp.sal%type;
begin
  open emp_cursor ;
  loop 
     fetch emp_cursor into v_ename,v_sal;
     exit when emp_cursor%notfound;
     dbms_output.put_line(v_ename||': '||v_sal);
  end loop;
  close emp_cursor;
end;
/
  
set serveroutput on 
declare 
  cursor emp_cursor is
    select ename from emp where deptno=10;
  type ename_table_type is table of varchar2(10);
  ename_table ename_table_type;
begin
  open emp_cursor ;
  fetch emp_cursor bulk collect  into ename_table;
  for i in 1..ename_table.count loop
     dbms_output.put_line(ename_table(i));
  end loop;
  close emp_cursor;
end;
/
  
set serveroutput on 
declare 
  type name_array_type is varray(5) of varchar2(10);
  cursor emp_cursor is select ename from emp ;
  name_array name_array_type;
  rows int:=5;
  v_count int:=0;
begin
  open emp_cursor ;
  loop
  fetch emp_cursor bulk collect  into name_array limit rows;
    dbms_output.put('雇员名:');
    for i in 1..emp_cursor%rowcount-v_count  loop
       dbms_output.put(name_array(i)||'  ');
    end loop;
    dbms_output.new_line;
    v_count:=emp_cursor%rowcount;
    exit when emp_cursor%notfound;
  end loop;
  close emp_cursor;
end;
/
  
set serveroutput on 
declare 
  type emp_cursor_type is ref cursor;
  emp_cursor emp_cursor_type;
  emp_record emp%rowtype;
begin 
  open emp_cursor for select * from emp where deptno=10;
  loop 
    fetch emp_cursor into emp_record;
    exit when emp_cursor%notfound;
    dbms_output.put_line('第'||emp_cursor%rowcount
    ||'个雇员:'||emp_record.ename);
  end loop;
  close emp_cursor;
end;
/

set serveroutput on 
declare 
  type emp_record_type is record(
     name varchar2(10),
     salary number(6,2)
  );
  type emp_cursor_type is ref cursor
    return emp_record_type;
  emp_cursor emp_cursor_type;
  emp_record emp_record_type;
begin 
  open emp_cursor for select ename,sal from emp where deptno=20;
  loop 
    fetch emp_cursor into emp_record;
    exit when emp_cursor%notfound;
    dbms_output.put_line('第'||emp_cursor%rowcount
    ||'个雇员:'||emp_record.name);
  end loop;
  close emp_cursor;
end;
/
-------------------------------------
declare 
  e_integrity exception;
  pragma exception_init(e_integrity ,-2291);
begin
  update emp set deptno=&dno where empno=&eno;
exception
  when e_integrity then
    dbms_output.put_line('该部门不存在');
end ;
/
----------------------------------
create or replace procedure out_time
is
begin 
  dbms_output.put_line(systimestamp);
end;
/

1.  SQL> setserveroutput on
    SQL> exec out_time

2.  SQL> setserveroutput on
    SQL> call out_time

--------------------------------
create or replace procedure add_employee(
  eno  number,
  name varchar2,
  sal  number,
  job  varchar2 default 'CLERK',
  dno  number
)
is 
  e_integrity exception;
  pragma exception_init(e_integrity ,-2291);
begin 
  insert into emp(empno,ename,sal,job,deptno)
  values(eno,name,sal,job,dno);
exception 
  when dup_val_on_index then
    raise_application_error(-20000,'雇员号不能重复');
  when e_integrity then
    raise_application_error(-20001,'部门号不存在');
end;
/

exec add_employee(1111,'MARY',2000,'MANAGER',10);

-----------------------------------------
create or replace procedure query_employee(
  eno    number,
  name   out varchar2,
  salary out number
)
is 
begin 
  select ename,sal into name,salary from emp 
  where empno=eno;
exception
  when no_data_found then 
    raise_application_error(-20000,'该雇员不存在');
end;
/


SQL> var name varchar2(10)
SQL> var salary number
SQL> exec query_employee(7788,:name,:salary);
PL/SQl 过程已成功完成
SQL> print name salary
name
------------
scott
   salary
------------
     3000


--------------------------------
参数传值:位置传递、名称传递、组合传递

查看子程序源码(数据字典:USER_SOURCE):
select text from user_source where name='query_employee';
--------------------------------
drop procedure query_employee;

---------------------------------
create or replace function get_user
return varchar2
is 
  v_user varchar2(100);
begin 
  select username into v_user from user_s;
  reuturn v_user;
end;
/

1.  使用变量接收函数返回值
    SQL> var v1 varchar2(100);
    SQL> exec :v1:=get_user
    SQL> print v1
2.  在Sql语句中直接调用函数
    SQL> select getuser from dual;
3.  使用包DBMS_OUTPUT调用函数
    SQL> set serveroutput on
    SQL> exec dbms_output.put_line('当前用户:'get_user);

-------------------------
create or repalce function get_sal(name in varchar2)
return number
as
  v_sal emp.sal%type;
begin 
  select sal into v_sal from emp 
  where upper(ename)=upper(name);
  return v_sal;
exception
  when no_data_found then 
    raise_appliction_error(-20000,'该雇员不存在');
end;
/

SQL> var sal number
SqL> exec :sal:=get_sal('scott');
SQL> print sal

------------------------------
函数可在以下SQL语句部分调用:
SELECT命令的选择列表;
WHERE和HAVING子句中;
CONNECT BY ,START WITH,ORDER BY以及GROUP BY子句中;
INSERT命令的VALUES子句中;
UPDATE命令的SET子句中。
注:函数在SQL中调用的限制:
SQL调用的函数只能调存储函数(服务器端),而不能调客户端的函数;
SQL调用的函数只能带输入参数(IN),而不能带输出参数(OUT)和输入输出参数(IN OUT);
SQL调用的函数只能使用SQL支持的标准数据类型,而不能是PL/Sql特有的数据类型(如BOOLEAN,TABLE和

RECORD等);
SQL调用的函数不能包含Insert,update和delete语句。

-----------------------------------
查看函数源码(数据字典:USER_SOURCE):
select text from user_source where name='query_employee';

drop function result;

----------------------------------
col object_name format a20
select object_name,created,status from user_objects
where object_type in('PROCEDURE','FUNCTION');

----------------------------------
触发器只能包含SELECT\INSERT\UPDATE和DELETE语句,而不能包含DDL语句(CREATE,ALERT,DROP)和事务控

制语句(COMMIT,ROLLBACK和SAVEPOINT)

CTEATE [OR REPLACE] TRIGGER trigger_name
timing event1[OR event2 OR event3]
ON table_name
PL/SQL block;

timing触发时机:before/after
event触发事件:insert/update/delete

create or replace trigger tr_sec_emp
before insert or update or delete on emp
begin
  if to_char(sysdate,'DY','nls_date_language=AMERICAN')
    in('SAT','SUN') then 
    raise_application_error(-20001,'不能在休息日改变雇员信息');
  end if;
end;
/