如果不是8.0.5或以下版本
Para_sql:='update ....';
execute immediate Para_sql;
...
Para_sql:='update ....';
execute immediate Para_sql;
...
解决方案 »
- oracle创建触发器问题,急,在线等!!!
- 请问我在删除表时提示有主外键使用中,求解决方法和批量删除方法
- 求sql,去除null,数据上移
- 在oracle develop 2000工具form build中怎样调用windows 应用程序???
- OMS 服务不能启动
- 怎么建立一个自动增长列...
- C/S查询问题,不是太难,但是我不会:(
- 弱弱的问:具体怎么导入文本数据
- 迷糊::: 为什么书上的例子都没带用户名, 我不带用户名就不行了?
- 在REDHAT9.0下安装ORACLE8.1.7时运行./runInstaller时出错,请各位高手指点一下!
- 紧急求救sql高手,分不够您说话!
- 求助 ORACLE 9I 问题!!出错信息 oracle not available
p_department in varchar2) as
v_insertSQL varchar2(1000);
begin
v_insertSQL:='insert into classes (department) values(:dp)'; --在变量前加一个":"号
execute immediate v_insertSQL using p_department;
end;
p_department in varchar2) as
v_insertSQL varchar2(1000);
begin
v_insertSQL:='insert into classes(department) values(''' ||
p_department || ''''||')';
execute immediate v_insertSQL;
end;
/
as
type cur_type is ref cursor;
c cur_type;
sql string(100);
begin
sql:='select ...';
open c for sql; ...
用dbms_sql来实现
CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS
cursor_name INTEGER;
rows_processed INTEGER;
BEGIN
cursor_name := dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursor_name, 'DELETE FROM emp WHERE sal > :x',
dbms_sql.native);
DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', salary);
rows_processed := dbms_sql.execute(cursor_name);
DBMS_SQL.close_cursor(cursor_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
DynSQL:=dbms_sql.open_cursor;
dbms_sql.parse(DynSQL,Para_strSQL,dbms_sql.native);
DynRET:=dbms_sql.execute(DynSQL);
dbms_sql.close_cursor(DynSQL);
这是8.0.5以下的版本用的,8I以后的就是用.
Para_sql:='update ....';
execute immediate Para_sql;
cursor_name INTEGER;
rows_processed INTEGER;
BEGIN
cursor_name := dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursor_name, 'DELETE FROM emp WHERE sal > :x',
dbms_sql.native);
DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', salary);
rows_processed := dbms_sql.execute(cursor_name);
DBMS_SQL.close_cursor(cursor_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
create or replace package body sp_in_basicpackages is
procedure sp_in_basicinfoget
(parindex in number ,parstr in varchar2 ,parstr1 in varchar2 ,returncur out basic_cursor) is
--声明部分
type infocursor is ref cursor;
cur infocursor;
cursor curone is
select countryid,countryname from country where countryid < 50;
sqlstr varchar2(2000);
strcountry varchar2(50);
emp integer ;
errorexcp exception;
strcountryid varchar2(20);
strcountryname varchar2(100);
strmodifier varchar2(20);
str1 varchar2(500);
str2 varchar2(500);
str3 varchar2(500);
str4 varchar2(500);
str5 varchar2(500);
str6 varchar2(100);
str7 varchar2(100);
str10 varchar2(100);
str11 varchar2(100);
str12 varchar2(500);
int8 number;
int9 number;
int13 number;
int14 number;
begin
-- 执行部分
emp := 99; if (parindex = 0) then
dbms_output.put_line('Can''t find recordsets or index is error!');
elsif parindex = 1 then--快速游标:
for curname in (select countryid,countryname from country where countryid >500) loop
dbms_output.put_line(curname.countryid || curname.countryname);
end loop;
elsif parindex = 2 then--预定义游标:当游标在声明块进行结果集合的定义时,如果程序中存在if语句,则返回时无法重新找到入口条件。
for curoneins in curone loop
strcountry := curoneins.countryname;
if strcountry = '中国' then
dbms_output.put_line(curoneins.countryid || curoneins.countryname);
exit;
end if;
end loop;
elsif parindex = 3 then--动态查询游标:
sqlstr := 'select countryid,countryname,modifier from country '||
'where countryname like ''%'||parstr||'%'''; open cur for sqlstr;
loop
fetch cur into strcountryid,strcountryname,strmodifier;
dbms_output.put_line(strcountryid||' '||strcountryname||' '||strmodifier);
exit when cur%notfound;
end loop;
close cur;
elsif parindex = 4 then
sqlstr := 'SELECT inventoryid as 物料代码, typename as 大类名称,sortname as 小类名称,c.description as 名称, '||
'c.style as 规格,c.unit as 单位, a.customsid as 海关编码,customsprice as 海关单价,weight as 重量,invusage as 用途,'||
' b.productname as 海关商品名称,b.customsunit as 法定单位,b.prferentialtax as 优惠税率,b.commantax as 普通税率 '||
'FROM in_Inventory a left join in_Customscode b on a.customsid = b.customsid '||
'join inventory c on a.inventoryid = c.inventory_id where inventoryid < ''030132'' order by inventoryid ';
open returncur for sqlstr;
/*
loop
fetch returncur into str1,str2,str3,str4,str5,str6,str7,int8,int9,str10,str11,str12,int13,int14;
--dbms_output.put_line(str1||str2||str3||str4||str5||str6||str7||int8||int9||str10||str11||str12||int13||int14);
exit when returncur%notfound;
end loop;
close returncur; */
elsif parindex = 99 then--其他游标:
return;
end if;
if emp = 999 then
raise errorexcp;
end if; --异常处理部分
exception--异常处理:
when errorexcp then
dbms_output.put_line('find a error!');
when no_data_found then
dbms_output.put_line('no data fond'); end ;
end sp_in_basicpackages;
p_department in varchar2(1000)) as
v_insertSQL varchar2(1000);
begin
v_insertSQL:='insert into classes(department) values(''' ||
p_department || ''''||')';
execute immediate v_insertSQL;
end;