oracle存储过程
游标方法1
declare cursor cjob is select * from user_tables@linkcwpt;
crow cjob%rowtype;
begin
for crow in cjob loop
dbms_output.put_line('ww');
end loop;
end;
游标方法2
declare cursor cjob is select * from user_tables@linkcwpt;
crow cjob%rowtype;
begin
open cjob;
loop
fetch cjob into crow;
exit when cjob%notfound;
dbms_output.put_line('ww');
end loop;
end;
补充
open mycursor; --打开游标
if mycursor%isopen then
if mycursor%found then --游标的found属性判断是否有记录
游标方法3
赋值sqlstr := 'create table 'crow.table_name;
查询用户下所有表导入
dbms_output.enable(buffer_size=>null);--无法读取很长的字符串，设置读取不限制
declare cursor cjob is select table_name from user_tables@linkcwpt;
crow cjob%rowtype;
sqlstr varchar2(255):='';
tname varchar(250);
begin
dbms_output.enable(buffer_size=>null);--
for crow in cjob loop
tname := crow.table_name;
sqlstr := 'create table '||crow.table_name||' select * from '||crow.table_name||'@linkcwpt;';
--dbms_output.put_line(crow.table_name);
dbms_output.put_line(sqlstr);
--execute immediate 'create table '||crow.table_name||' select * from '||crow.table_name||'@linkcwpt';
end loop;
end;
2.增加数据 （alter system set "_system_trig_enabled"=TRUE;）
declare
v_1 varchar2(8);
v_2 varchar2(10);
str varchar2(250);
BEGIN
dbms_output.enable(buffer_size=>null);
v_1:='sdfsdfsd';
v_2:='werwerwer';
str := 'INSERT INTO CF_ZJ_CDHPTZ (F_PJH ,F_YWDJH) VALUES (:1, :2)';
EXECUTE IMMEDIATE str USING v_1, v_2;
commit;
end;
查询数据： (OK)
Declare
CurName integer;
ret integer;
V_1 varchar2(10);
V_2 number;
begin
CurName:=dbms_sql.open_cursor;
DBMS_SQL.PARSE(CurName, 'select ''test'' ,783426 from dual', DBMS_SQL.native);
DBMS_SQL.DEFINE_COLUMN(CurName, 1, V_1,10);
DBMS_SQL.DEFINE_COLUMN(CurName, 2, v_2);
ret := DBMS_SQL.EXECUTE(CurName);
IF DBMS_SQL.FETCH_ROWS(CurName)>0 THEN
DBMS_SQL.COLUMN_VALUE(CurName, 1, v_1);
DBMS_SQL.COLUMN_VALUE(CurName, 2, v_2);
dbms_output.put_line('第一个值:'||v_1);
dbms_output.put_line('第二个值:'||to_char(v_2));
end if;
Dbms_sql.close_cursor(CurName);
end ;
查询
DECLARE
TYPE mycursor IS REF CURSOR;
cursor_1 mycursor;
r_1 em_cdhp_pdshlcb%ROWTYPE;
str VARCHAR2(100);
v_mc em_cdhp_pdshlcb.f_shrmc%type;
v_lb em_cdhp_pdshlcb.f_shlb%type;
BEGIN
str := 'select f_shrmc,f_shlb from em_cdhp_pdshlcb';
OPEN cursor_1 FOR str;
LOOP
FETCH cursor_1 INTO v_mc,v_lb;
EXIT WHEN cursor_1%NOTFOUND;
dbms_output.put_line(v_mc);
END LOOP;
CLOSE cursor_1;
END;
不显示 set serveroutput on;
查询数据： (OK)
DECLARE
str varchar2(500);
c_1 varchar2(10);
r_1 em_cdhp_pdshlcb%rowtype;
begin
c_1:='1';
str:='select * from em_cdhp_pdshlcb where f_shjs=:c and ROWNUM=1';
execute immediate str into r_1 using c_1;
DBMS_OUTPUT.PUT_LINE(R_1.f_shbmbh||R_1.f_shrbh);
end ;
建表，删表，更新记录等操作，基本类似，我举一个建表的例子： （OK）
Declare
curRow integer;
ret INTEGER;
begin
curRow:=dbms_sql.open_cursor;
DBMS_SQL.PARSE(curRow, 'create table test_2(name varchar2(8))', DBMS_SQL.native);
ret := DBMS_SQL.EXECUTE(curRow);
DBMS_SQL.CLOSE_CURSOR(curRow);
END;
3返回结果集（OK）
CREATE OR REPLACE package pkg_test as
/* 定义ref cursor类型
不加return类型，为弱类型，允许动态sql查询，
否则为强类型，无法使用动态sql查询;
*/
type myrctype is ref cursor;
--函数申明
function get(intID number) return myrctype;
end pkg_test;
CREATE OR REPLACE package body pkg_test as--函数体
function get(intID number) return myrctype is
rc myrctype; --定义ref cursor变量
sqlstr varchar2(500);
begin
if intID=0 then
--静态测试，直接用select语句直接返回结果
open rc for select * from em_cdhp_pdkzsh;
else
--动态sql赋值，用:w_id来申明该变量从外部获得
sqlstr := 'select * from em_cdhp_pdkzsh';
--动态测试，用sqlstr字符串返回结果，用using关键词传递参数
open rc for sqlstr using intid;
end if;
return rc;
end get;
end pkg_test;
-------------------------已验试-------------------------
--程序包
create or replace package pack_1 as
n number;
procedure p_1;
function f_1 return number;
end;
--创建数据包主体:
create or replace package body pack_1 as
procedure p_1 is
r plm_basicinfo%rowtype;
begin
select * into r from plm_basicinfo where objid=2000;
dbms_output.put_line(r.objid||' '||r.productname||' '||r.producttype);
end;
FUNCTION f_1 RETURN number is
r plm_basicinfo%rowtype;
begin
select * into r from plm_basicinfo where objid=2000;
return r.objid;
end;
end;
declare
n number;
begin
n:=&请输入员工号;
pack_1.n:=n;
pack_1.p_1;
n:=pack_1.f_1;
dbms_output.put_line('薪水为 '||n);
end;
create or replace package p_view_param is
function set_param(paramVal VARCHAR2) return VARCHAR2;
function get_param(paramVal VARCHAR2) return VARCHAR2;
end p_view_param;
create or replace package body p_view_param is
-- Param
function set_param(paramVal VARCHAR2) return VARCHAR2 is
paramValue VARCHAR2(200);
begin
paramValue:=paramVal;
return paramValue;
end;
function get_param(paramVal VARCHAR2) return VARCHAR2 is
begin
dbms_output.put_line('paramValue'||paramValue);
return paramVal;
end;
END p_view_param;
SELECT p_view_param.set_param('sss') as temp FROM dual;
SELECT p_view_param.get_param(p_view_param.set_param('sss')) as temp FROM dual;
1》FORM级
方法一：
FORMS_DDL：支持pl/sql和部分ddl
建表：Forms_DDL('create table temp(n NUMBER)');
删表：Forms_DDL('drop table temp');
删除记录：Forms_DDL('delete from temp');
执行PL/SQL：Forms_ddl('begin '||过程名||'; End;');
**注意判断是否执行成功，通过FORM_SUCCESS or FORM_FAILURE来判断。
2》table级别
方法一：dbms_sql.
3》pl/sql级别
我仅仅用于查询。 （OK）
例：
DECLARE
TYPE mycursor IS REF CURSOR;
cursor_1 mycursor;
r_1 em_cdhp_pdkzsh%ROWTYPE;
str VARCHAR2(100);
BEGIN
str := 'select * from em_cdhp_pdkzsh';
OPEN cursor_1 FOR str;
LOOP
FETCH cursor_1 INTO r_1;
EXIT WHEN cursor_1%NOTFOUND;
dbms_output.put_line(r_1.f_sfsh);
END LOOP;
CLOSE cursor_1;
END;
END
定义数组
DECLARE
TYPE arry_var IS VARRAY(6) OF VARCHAR2(10);
arry_name arry_var;
BEGIN
arry_name := arry_var('tom', 'jim','tim');
dbms_output.put_line(arry_name(1));
dbms_output.put_line(arry_name(2));
END;
DECLARE
-- declare fixed array
TYPE arry_num IS VARRAY(10) OF NUMBER;
arry_top arry_num;
BEGIN
-- init array
arry_top := arry_num(1,2,3);
dbms_output.put_line(arry_top(1));
dbms_output.put_line(arry_top(2));
END;
存储过程
create or replace Procedure autoDelGps as
begin
delete from crm_account where createtimestamp<sysdate-5;
end autoDelGps;
begin
--dbms_job.run(44); --启动JOB
dbms_job.broken(44,false,sysdate); --停止JOB
end;
sqlldr加载txt文件方式
load data CHARACTERSET UTF8 infile 'C:/Users/sxshe/Desktop/temp.txt' append into table GMTABLE when (1:3)!="END" (
TABLE_NAME position(1:25) char(25) "trim(:TABLE_NAME)"--过滤字段中的空格
sqlldr amldb/amldb@127.0.0.1:1521/orcl control=E:\CUSTSHA.ctl log=E:\ll.log skip=1
fields terminated by ','
将一个值赋给另一个变量
execute immediate sqlStr using out r;
创建dbms_job
declare
job number;
begin
sys.dbms_job.submit(job => job,
what => 'updateFundDataBase();',
next_date => to_date('24-10-2019 13:57:43', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'sysdate+86400');
commit;
end;
每天凌晨一点
TRUNC(sysdate) + 1 +1/24
每天早上8点
trunc(sysdate+1) + (8*60+10)/24*60
创建job
BEGIN
dbms_scheduler.create_job(
job_name => 'RMAN_BACKUP',
job_type => 'EXECUTABLE',
job_action
=> 'D:\Oracle\product\11.2.0\dbhome_1\BIN\rman.exe target /
cmdfile=D:\backup\task\RMAN_Backup.txt log="D:\backup\rman_backup.log"',
start_date => sysdate,
repeat_interval=>'FREQ=DAILY;INTERVAL=1;BYHOUR=12,18;',
enabled => TRUE,
auto_drop => FALSE,
comments => 'RMAN backup');
END;
------
declare job number;
begin
sys.dbms_job.submit(job => job,
what => 'updateFundDataBase();',
next_date => to_date('14-11-2019 01:00:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'TRUNC(sysdate) + 1 +1/24');
commit;
end;
本地磁盘输出日志文件
declare
vFileName varchar2(50);
OutputFile UTL_FILE.FILE_TYPE;
begin
-- Test statements here
select 'updatefundation-'||to_char(sysdate,'yyyy-mm-dd')||'.log' into vFileName from dual;
OutputFile := utl_file.fopen('ORACLED',vFileName,'a');
utl_file.put_line(OutputFile,to_char(sysdate,'yyyy-mm-dd hh24:mm:ss')||'共更新了- -条',true);
utl_file.fflush(OutputFile);
utl_file.fclose(OutputFile);
end;
游标遍历异常捕捉（ocursor out sys_refcursor,viewname in varchar2 default 'v_tmp'）
my_error exception;
for crow in cjob loop
begin
if crow.entityname is null
then
raise my_error;
end if;
update t set (t.a,t.b) = (a,b)
execute immediate v_sql;
exception
when my_error then
dbms_output.put_line(crow.entityname||'对应表不存在');
rollback;
when others then
dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_STACK || DBMS_UTILITY.FORMAT_CALL_STACK||sqlerrm);
end;
end loop;
执行存储过程以调用者权限执行（有了定义者的权限）
authid current_user/definer
debug权限
grant debug any procedure, debug connect session to scott
