oracle学习笔记(动态SQL)

最新推荐文章于 2024-10-16 17:36:30 发布

menglikanhua

最新推荐文章于 2024-10-16 17:36:30 发布

阅读量452

点赞数

文章标签： sql oracle integer character date buffer

-- 动态 SQL
-- 使用 dbms_sql 包
-- 步骤:
1)打开游标:dbms_sql.open_cursor, 返回游标 ID 号
2)语法分析:dbms_sql.parse( i_c INTEGER,i_statement IN VARCHAR2,i_language_flag IN INTEGER),
i_c打开的光标 ID,i_statement 等待分析的 SQL 语句,i_language_flag,语言版本,取值:6,7,dbms_sql.native,按连接的数据库版本运行
3)绑定输入变量:dbms_sql.bind_variable,注意:DDL,ALTER session语句不能绑定变量,绑定变量前的冒号可有可无
绑定数字型的变量:bind_variable(i_c IN INTEGER,i_name IN VARCHAR2,i_value IN NUMBER);
绑定varchar2型的变量:bind_variable(i_c IN INTEGER,i_name IN VARCHAR2,i_value IN VARCHAR2);
bind_variable(i_c IN INTEGER,i_name IN VARCHAR2,i_value IN VARCHAR2,o_value_size OUT INTEGER);
绑定char型的变量:bind_variable_char(i_c IN INTEGER,i_name IN VARCHAR2,i_value IN VARCHAR2[,o_value_size OUT INTEGER]);
绑定日期型的变量:bind_variable(i_c IN INTEGER,i_name IN VARCHAR2,i_value IN DATE);
绑定raw型的变量:bind_variable_raw(i_c IN INTEGER,i_name IN VARCHAR2,i_value IN RAW[,o_value_size OUT INTEGER])
绑定log型的变量:
4)执行语句:dbms_sql.EXECUTE(i_c IN INTEGER) 返回处理的行数,
5)关闭游标:dbms_sql.close_cursor(i_c IN INTEGER)

DECLARE
v_sql VARCHAR2(200);
v_age INTEGER;
v_emp_no CHAR(5);
v_id INTEGER;
v_row INTEGER;
BEGIN
v_sql :='update emp_m set emp_age = :age where emp_no = :emp_no';
v_age := 25;
v_emp_no := '00001';
v_id := dbms_sql.open_cursor;
dbms_sql.parse(v_id,v_sql,dbms_sql.native);
dbms_sql.bind_variable(v_id,':age',v_Age);
dbms_sql.bind_variable(v_id,':emp_no',v_emp_no);
v_row := dbms_sql.execute(v_id);
dbms_sql.close_cursor(v_id);
dbms_output.put_line('v_row = '||to_char(v_row));
END;
--执行查询语句
-- 步骤:
1)打开游标:dbms_sql.open_cursor, 返回游标 ID 号
2)语法分析:dbms_sql.parse( i_c INTEGER,i_statement IN VARCHAR2,i_language_flag IN INTEGER),
i_c打开的光标 ID,i_statement 等待分析的 SQL 语句,i_language_flag,语言版本,取值:6,7,dbms_sql.native,按连接的数据库版本运行
3)绑定输入变量:dbms_sql.bind_variable,注意:DDL,ALTER SESSION 语句不能绑定变量,绑定变量前的冒号可有可无
4)定义选择列表项:dbms_sql.define_column
定义类型为number的:dbms_sql.define_column(i_c IN INTEGER,i_position IN INTEGER,i_column IN NUMBER)
定义类型为varchar2的:dbms_sql.define_column(i_c IN INTEGER,i_position IN INTEGER,i_column IN VARCHAR2,i_column_size IN INTEGER)
定义类型为char的:dbms_sql.define_column_char(i_c IN INTEGER,i_position IN INTEGER,i_column IN CHAR,i_column_size IN INTEGER)
定义类型为date的:dbms_sql.define_column_date(i_c IN INTEGER,i_position IN INTEGER,i_column IN DATE)
定义类型为raw的:dbms_sql.define_column_raw(i_c IN INTEGER,i_position IN INTEGER,i_column IN RAW,i_column_size IN INTEGER)
i_position 选择列表中项的相对位置,第一个是 1 ,i_column 定义输出变量的类型和长度的变量,i_size 定义输出变量的最大长度
5)执行语句:dbms_sql.EXECUTE(i_c IN INTEGER) 返回处理的行数
6)取回查询到的行:dbms_sql.fetch_rows(i_c IN INTEGER),返回值为 0 表示结束,dbms_sql.EXECUTE 和 dbms_sql.fetch_rows 组合成一个语句:dbms_sql.execute_and_fetch(i_c IN INTEGER,i_exact IN BOOLEAN DEFAULT FALSE) RETURN INTEGER
i_exact 如果为真,则在查询是返回一行以上是引发异常too_many_rows,但是程序仍然可以执行fetch和检索操作
返回值表示到目前为止取回的行数
7)把查询到的结果返回到PL/SQL变量中:dbms_sql.COLUMN_VALUE
NUMBER 类型:dbms_sql.COLUMN_VALUE(i_c IN INTEGER,i_position IN INTEGER,o_value OUT NUMBER)
dbms_sql.COLUMN_VALUE(i_c IN INTEGER,i_position IN INTEGER,o_value OUT NUMBER,o_column_error OUT NUMBER,o_actual_lenth OUT INTEGER)
VARCHAR2 类型:dbms_sql.COLUMN_VALUE(i_c IN INTEGER,i_position IN INTEGER,o_value OUT VARCHAR2)
dbms_sql.COLUMN_VALUE(i_c IN INTEGER,i_position IN INTEGER,o_value OUT VARCHAR2,o_column_error OUT NUMBER,o_actual_lenth OUT INTEGER)
CHAR 类型:dbms_sql.COLUMN_VALUE(i_c IN INTEGER,i_position IN INTEGER,o_value OUT CHAR)
dbms_sql.COLUMN_VALUE(i_c IN INTEGER,i_position IN INTEGER,o_value OUT CHAR,o_column_error OUT NUMBER,o_actual_lenth OUT INTEGER)
DATE 类型:dbms_sql.COLUMN_VALUE(i_c IN INTEGER,i_position IN INTEGER,o_value OUT DATE)
dbms_sql.COLUMN_VALUE(i_c IN INTEGER,i_position IN INTEGER,o_value OUT DATE,o_column_error OUT NUMBER,o_actual_lenth OUT INTEGER)
RAW 类型:dbms_sql.COLUMN_VALUE(i_c IN INTEGER,i_position IN INTEGER,o_value OUT RAW)
dbms_sql.COLUMN_VALUE(i_c IN INTEGER,i_position IN INTEGER,o_value OUT RAW,o_column_error OUT NUMBER,o_actual_lenth OUT INTEGER)
8)关闭游标:dbms_sql.close_cursor(i_c IN INTEGER)

DECLARE
v_sql VARCHAR2(200);
v_id INTEGER;
v_emp_name emp_m.emp_name%TYPE;
v_emp_sex emp_m.emp_sex%TYPE;
v_emp_age emp_m.emp_age%TYPE;
v_emp_birthday emp_m.emp_birthday%TYPE;
v_row INTEGER;
BEGIN
v_sql := 'select emp_name,emp_sex,emp_age,emp_birthday from emp_m where emp_no = :emp_no';
v_id := dbms_sql.open_cursor;
dbms_sql.parse(v_id,v_Sql,dbms_sql.native);
dbms_sql.bind_variable(v_id,':emp_no','00001');
dbms_sql.define_column(v_id,1,v_emp_name,10);
dbms_sql.define_column_char(v_id,2,v_emp_sex,1);
dbms_sql.define_column(v_id,3,v_emp_age);
dbms_sql.define_column(v_id,4,v_emp_birthday);
v_row := dbms_sql.execute(v_id);
LOOP
IF dbms_sql.fetch_rows(v_id) = 0 THEN
EXIT;
END IF;
dbms_sql.COLUMN_VALUE(v_id,1,v_emp_name);
dbms_sql.column_value_char(v_id,2,v_emp_sex);
dbms_sql.column_value(v_id,3,v_emp_age);
dbms_sql.column_value(v_id,4,v_emp_birthday);
dbms_output.put_line('emp_name='||v_emp_name||',emp_sex='||v_emp_sex||',emp_age='||to_char(v_emp_age)||',emp_birthday='||to_char(v_emp_birthday,'yyyy/mm/dd'));
END LOOP;
dbms_sql.close_cursor(v_id);
END;

两个写数据的存储过程

dbms_lob.append 和 dbms_lob.write
append存储过程用于将一个大对象添加到另一个大对象中，此时是将源大对象的内容全部添加过去。append存储过程的语法如下：
dbms_lob.append(
dest_lob in out nocopy blob,
src_lob in blob);

dbms_lob.append(
dest_lob in out nocopy clob character set any_cs,
src_lob in clob character set dest_lob%charset);
其中，各个参数的含义如下：
dest_lob是被源lob添加到的目标lob的定位器
src_lob是源lob的定位器
any_cs用来指定字符集。

write存储过程
write存储过程能够将数据写入大型对象中。写的位置是从大型对象开始处的某个绝对偏移地址，数据从缓冲区参数被写入。写操作将覆盖已经在大型对象偏移地址处存在的任何长度为指定的数据。如果输入数多于在缓冲区的数据，将产生一个错误。如果输入数量小于在缓冲区的数据，那么只有缓冲区的数据字节活字符被写给大型对象。
write存储过程的语法如下：
dbms_lob.write(
lob_loc in out nocopy blob,
amount in binary_integer,
offset in integer,
buffer in raw);

dbms_lob.write(
lob_loc in out nocopy clob character set any_cs,
amount in binary_integer,
offset in integer,
buffer in varchar2 character set lob_loc%charset);

其中各个参数的含义如下：
lob_loc是要操作的大型对象定位器。
amount是要写道大型对象中去的字节数量。
offset是指定将数据写入到大型对象什么位置的偏移地址。
buffer是写入到大型对象的数据缓冲区。
any_cs指定要使用的字符集。

示例：
declare
source_lob clob;
dest_lob clob;
write_amount integer:=18;
writing_position integer;
buffer varchar2(20) := 'Added text to clob';
begin
select clob_locator into dest_lob from mylobs where lob_index = 4 for update;
select clob_locator into source_lob from mylobs where lob_index = 1;
dbms_lob.append(dest_lob, source_lob);
commit;

select clob_locator into dest_lob from mylobs where lob_index = 5 for update;
writing_position := dbms_lob.getlength(dest_lob) + 1;
dbms_lob.write(dest_lob, write_amount, writing_position, buffer);
commit;
end;
/