刚发了个 --游标使用 declare code varchar2(20); danjia varchar2(20); cursor cur is select c.vend_code,c.licence_q from tb_ls75 c where rownum<20; begin open cur; loop fetch cur into code,danjia; DBMS_OUTPUT.put_line(code); exit when cur%notfound; end loop; close cur; end;SQL> create or replace type myvarray_list as varray(10) of varchar2(50); 2 /类型已创建。SQL> create or replace procedure show_list( 2 p_varlist in myvarray_list 3 ) 4 is 5 str varchar2(50); 6 begin 7 for x in 1..p_varlist.count loop 8 dbms_output.put_line('p_varlist('||x||')='||p_varlist(x)); 9 end loop; 10 end; 11 /过程已创建。SQL> exec show_list(myvarray_list('Oracle','DB2','Sql Server','mySql','DBA')); p_varlist(1)=Oracle p_varlist(2)=DB2 p_varlist(3)=Sql Server p_varlist(4)=mySql p_varlist(5)=DBAPL/SQL 过程已成功完成。
哦了。找到个好的PDF看看去先。都不知道游标和存储过程的语法当然看不太懂呵呵
create or replace procedure print_employee(emp_id number) is p_id EMPLOYEES.EMPLOYEE_ID%type; -- 定义变量p_id p_name EMPLOYEES.LAST_NAME%type; -- 定义变量p_name p_salary EMPLOYEES.SALARY%type;-- 定义变量p_salary cursor cur(e_id number) is -- 定义游标cur select employee_id,last_name,salary from employees where employee_id = e_id; begin open cur(emp_id); -- 打开游标 fetch cur into p_id,p_name,p_salary; -- 获取游标内容 dbms_output.put_line('Employee ID=' || p_id); -- 显示p_id dbms_output.put_line('Last name=' || p_name); -- 显示p_name dbms_output.put_line('salary=' || p_salary); -- 显示p_salary close cur; -- 关闭游标 end print_employee;
过程: create or replace procedure a( no number,age in number,name out varchar2,salary in out number) --参数默认为in,所以eno和age等同;salary为输入输出参数。 is begin select ename,esal into name,salary from emp where eno=no and eage>age and esal>salary; exception when no_data_found then raise_application_error(-20000,'该雇员不存在'); end; --在PLSQL中过程A上右键点测试,会自动出来eno,ename,eage,esal四个参数,只要填上除ename外的3个, --最后将显示出来符合条件的ename和esal,并用黄色标出游标: declare type d1 is ref cursor; d2 d1; c1 a.a1%type; c2 a.a2%type; begin open d2 for select a1,a2 from a where a1<>a2 ; loop fetch d2 into c1 ,c2; exit when d2%notfound; insert into b(b1,b2) values (c1,c2);end loop; close d2;--注意close和end loop 的位置 end; 触发器: create or replace trigger asdf before update of name on aab /*of name 表示触发器只控制name字段,若去掉意味着控制整个aab表的update*/ for each row /*有for each row 的话是行触发器,没有的话是语句触发器,具体区别自己查,个人理解为行触发器和语句触发器有点像父子关系*/ begin if to_char (sysdate,'dy','nls_date_language=AMERICAN') in ('wed','sun') /*to_char (sysdate,'dy','nls_date_language=AMERICAN') 是把今天的sysdate 转化得到今天的 星期几的英文单词的缩写 */ then raise_application_error(-20001,'今天不能更新');-- 报错 end if; end;
--游标使用
declare
code varchar2(20);
danjia varchar2(20);
cursor cur is select c.vend_code,c.licence_q from tb_ls75 c where rownum<20;
begin
open cur;
loop
fetch cur into code,danjia;
DBMS_OUTPUT.put_line(code);
exit when cur%notfound;
end loop;
close cur;
end;SQL> create or replace type myvarray_list as varray(10) of varchar2(50);
2 /类型已创建。SQL> create or replace procedure show_list(
2 p_varlist in myvarray_list
3 )
4 is
5 str varchar2(50);
6 begin
7 for x in 1..p_varlist.count loop
8 dbms_output.put_line('p_varlist('||x||')='||p_varlist(x));
9 end loop;
10 end;
11 /过程已创建。SQL> exec show_list(myvarray_list('Oracle','DB2','Sql Server','mySql','DBA'));
p_varlist(1)=Oracle
p_varlist(2)=DB2
p_varlist(3)=Sql Server
p_varlist(4)=mySql
p_varlist(5)=DBAPL/SQL 过程已成功完成。
create or replace procedure print_employee(emp_id number) is
p_id EMPLOYEES.EMPLOYEE_ID%type; -- 定义变量p_id
p_name EMPLOYEES.LAST_NAME%type; -- 定义变量p_name
p_salary EMPLOYEES.SALARY%type;-- 定义变量p_salary
cursor cur(e_id number) is -- 定义游标cur
select employee_id,last_name,salary from employees where employee_id = e_id;
begin
open cur(emp_id); -- 打开游标
fetch cur into p_id,p_name,p_salary; -- 获取游标内容
dbms_output.put_line('Employee ID=' || p_id); -- 显示p_id
dbms_output.put_line('Last name=' || p_name); -- 显示p_name
dbms_output.put_line('salary=' || p_salary); -- 显示p_salary
close cur; -- 关闭游标
end print_employee;
create or replace procedure a(
no number,age in number,name out varchar2,salary in out number)
--参数默认为in,所以eno和age等同;salary为输入输出参数。
is
begin
select ename,esal into name,salary from emp where eno=no and eage>age and esal>salary;
exception
when no_data_found then
raise_application_error(-20000,'该雇员不存在');
end;
--在PLSQL中过程A上右键点测试,会自动出来eno,ename,eage,esal四个参数,只要填上除ename外的3个,
--最后将显示出来符合条件的ename和esal,并用黄色标出游标:
declare
type d1 is ref cursor;
d2 d1;
c1 a.a1%type;
c2 a.a2%type;
begin
open d2 for
select a1,a2 from a where a1<>a2 ;
loop
fetch d2 into c1 ,c2;
exit when d2%notfound;
insert into b(b1,b2) values (c1,c2);end loop;
close d2;--注意close和end loop 的位置
end;
触发器:
create or replace trigger asdf
before update of name on aab
/*of name 表示触发器只控制name字段,若去掉意味着控制整个aab表的update*/
for each row
/*有for each row 的话是行触发器,没有的话是语句触发器,具体区别自己查,个人理解为行触发器和语句触发器有点像父子关系*/
begin
if to_char (sysdate,'dy','nls_date_language=AMERICAN') in ('wed','sun')
/*to_char (sysdate,'dy','nls_date_language=AMERICAN') 是把今天的sysdate 转化得到今天的 星期几的英文单词的缩写 */
then
raise_application_error(-20001,'今天不能更新');-- 报错
end if;
end;