CREATE OR REPLACE PROCEDURE InsertIntoTemp AS
/* Declare variables to be used in this block. */
v_Num1 NUMBER := 5;
v_Num2 NUMBER := 6;
v_String1 VARCHAR2(50) := 'Hello World!';
v_String2 VARCHAR2(50) := '-- This message brought to you by PL/SQL!';
v_OutputStr VARCHAR2(50);
BEGIN
/* First, insert two rows into temp_table, using the values of the
variables. */
INSERT INTO temp_table (num_col, char_col)
VALUES (v_Num1, v_String1);
INSERT INTO temp_table (num_col, char_col)
VALUES (v_Num2, v_String2);
/* Now query temp_table for the two rows we just inserted, and output
them to the screen using the DBMS_OUTPUT package. */
SELECT char_col
INTO v_OutputStr
FROM temp_table
WHERE num_col = v_Num1;
DBMS_OUTPUT.PUT_LINE(v_OutputStr);
SELECT char_col
INTO v_OutputStr
FROM temp_table
WHERE num_col = v_Num2;
DBMS_OUTPUT.PUT_LINE(v_OutputStr);
END InsertIntoTemp;
/CREATE OR REPLACE TRIGGER OnlyPositive
BEFORE INSERT OR UPDATE OF num_col
ON temp_table
FOR EACH ROW
BEGIN
IF :new.num_col < 0 THEN
RAISE_APPLICATION_ERROR(-20100, 'Please insert a positive value');
END IF;
END OnlyPositive;
/
/* Declare variables to be used in this block. */
v_Num1 NUMBER := 5;
v_Num2 NUMBER := 6;
v_String1 VARCHAR2(50) := 'Hello World!';
v_String2 VARCHAR2(50) := '-- This message brought to you by PL/SQL!';
v_OutputStr VARCHAR2(50);
BEGIN
/* First, insert two rows into temp_table, using the values of the
variables. */
INSERT INTO temp_table (num_col, char_col)
VALUES (v_Num1, v_String1);
INSERT INTO temp_table (num_col, char_col)
VALUES (v_Num2, v_String2);
/* Now query temp_table for the two rows we just inserted, and output
them to the screen using the DBMS_OUTPUT package. */
SELECT char_col
INTO v_OutputStr
FROM temp_table
WHERE num_col = v_Num1;
DBMS_OUTPUT.PUT_LINE(v_OutputStr);
SELECT char_col
INTO v_OutputStr
FROM temp_table
WHERE num_col = v_Num2;
DBMS_OUTPUT.PUT_LINE(v_OutputStr);
END InsertIntoTemp;
/CREATE OR REPLACE TRIGGER OnlyPositive
BEFORE INSERT OR UPDATE OF num_col
ON temp_table
FOR EACH ROW
BEGIN
IF :new.num_col < 0 THEN
RAISE_APPLICATION_ERROR(-20100, 'Please insert a positive value');
END IF;
END OnlyPositive;
/
解决方案 »
- Oracle启动失败 help!!
- 复制问题
- oracle全文搜索引擎的问题
- sqlload 中 TRAILING NULLCOLS 能不能把NULLCOLS改成某个字符告诉oracle用 字符来区分 一条记录的结束吗?
- Oracle中如何分隔字符串?
- 有没有针对Oracle数据库的设计工具
- 怎样保证两个不同库中几个表的内容同步?
- 错误:ORA-00059 : maximum number of DB_FILES exceeded
- select * from amt2002 where code like '521._'; 对不对??
- sqoop2在连接Oracle时没有反应
- 如果我忘記了自己建立過甚麼TABLE, 但我想查自己建立過甚麼TABLE, 有甚麼COMMAND 可以助我查明呢?
- 建表时的问题?
source IN VARCHAR2,
destination IN VARCHAR2) IS
id_var NUMBER;
name_var VARCHAR2(30);
birthdate_var DATE;
source_cursor INTEGER;
destination_cursor INTEGER;
ignore INTEGER;
BEGIN
-- Prepare a cursor to select from the source table:
source_cursor := dbms_sql.open_cursor;
DBMS_SQL.PARSE(source_cursor,
'SELECT id, name, birthdate FROM ' || source,
DBMS_SQL.native);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id_var);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, name_var, 30);
DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, birthdate_var);
ignore := DBMS_SQL.EXECUTE(source_cursor);
-- Prepare a cursor to insert into the destination table:
destination_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(destination_cursor,
'INSERT INTO ' || destination ||
' VALUES (:id_bind, :name_bind, :birthdate_bind)',
DBMS_SQL.native);
-- Fetch a row from the source table and insert it into the destination table:
LOOP
IF DBMS_SQL.FETCH_ROWS(source_cursor)>0 THEN
-- get column values of the row
DBMS_SQL.COLUMN_VALUE(source_cursor, 1, id_var);
DBMS_SQL.COLUMN_VALUE(source_cursor, 2, name_var);
DBMS_SQL.COLUMN_VALUE(source_cursor, 3, birthdate_var);
-- Bind the row into the cursor that inserts into the destination table. You
-- could alter this example to require the use of dynamic SQL by inserting an
-- if condition before the bind.
DBMS_SQL.BIND_VARIABLE(destination_cursor, ':id_bind', id_var);
DBMS_SQL.BIND_VARIABLE(destination_cursor, ':name_bind', name_var);
DBMS_SQL.BIND_VARIABLE(destination_cursor, ':birthdate_bind',
birthdate_var);
ignore := DBMS_SQL.EXECUTE(destination_cursor);
ELSE
-- No more rows to copy:
EXIT;
END IF;
END LOOP;
-- Commit and close all cursors:
COMMIT;
DBMS_SQL.CLOSE_CURSOR(source_cursor);
DBMS_SQL.CLOSE_CURSOR(destination_cursor);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(source_cursor) THEN
DBMS_SQL.CLOSE_CURSOR(source_cursor);
END IF;
IF DBMS_SQL.IS_OPEN(destination_cursor) THEN
DBMS_SQL.CLOSE_CURSOR(destination_cursor);
END IF;
RAISE;
END;
/
set serveroutput on;
8i中
可以直接执行 open curname;//curname为游标名
fetch curname into blname;//blname为变量名,存放fetch出来的数据
close curname;//关闭游标最好先找本书看一下,把基础的概念,常用的语法搞清楚,
你就是发100个贴子也不如你看两天书有成效。