SQL> set serveroutput on/off
SQL> set echo on/off --if you want to see the original source from the file, use the SQL*Plus command SET ECHO ON.
SQL> @abc.pkg --調用腳本
@@符號 --解決調用不在同一路徑
--顯示設置
show all
--定義變量:
- --you can use the DEFINE command:
SQL> DEFINE x = "the answer is 42"
--To view the value of x, specify:
SQL> DEFINE x
DEFINE X = "the answer is 42" (CHAR)
SQL> SELECT '&x' FROM DUAL; --引用時加單引號
old 1: SELECT '&x' FROM DUAL
new 1: SELECT 'the answer is 42' FROM DUAL
'THEANSWERIS42'
----------------
the answer is 42
2.--bind variables
SQL> variable x varchar2(10) --定義
SQL> begin
2 :x := 'hullo';
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> print :x
X
--------------------------------
hullo
SQL> SELECT :x, '&x' FROM DUAL;
old 1: SELECT :x, '&x' FROM DUAL
new 1: SELECT :x, 'the answer is 42' FROM DUAL
:X 'THEANSWERIS42'
-------------------------------- ----------------
hullo the answer is 42
--Saving output to a file
spool report.txt
spool off
--定義編輯器:
SQL> DEFINE _EDITOR=/bin/vi
SQL> edit
SQL> EDIT abc.pkg
--SQL/PLUS主要命令:
L 顯示緩衝區命令
n 設置第n行為當前行
del 刪除當前行
C/old/new
n text:把text作為第n行的內容。
I 在當前行之後插入一行，要想在第一行之前插入一個新行，使用0命令（即0 text)
$ORACLE_HOME/sqlplus/admin/glogin.sql腳本，設置全局sqlplus環境。
當前目錄下的login.sql配置當前環境。
--Error Handling in SQL*Plus
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
--Creating a Stored Program
--to use the SQL*Plus SHOW ERRORS command, abbreviated as SHO ERR:
SQL> SHO ERR
Errors for FUNCTION WORDCOUNT:
LINE/COL ERROR
-------- ----------------------------------------------
14/13 PLS-00201: identifier 'WORDS' must be declared
14/13 PL/SQL: Statement ignored
21/4 PL/SQL: Statement ignored
21/11 PLS-00201: identifier 'WORDS' must be declared
SQL> SHOW ERRORS category [schema.]object
SQL> SHOW ERRORS FUNCTION wordcount
--Executing a Stored Program
BEGIN
--invoke wordcount() function
DBMS_OUTPUT.PUT_LINE('There are ' || wordcount(CHR(9)) || ' words in a tab');
END;
/
SELECT isbn, wordcount(description) FROM books;
VARIABLE words NUMBER
CALL wordcount('some text') INTO :words;
PRINT :words
--Showing Stored Programs
--to see a complete list of your programs (and tables, indexes, etc.), query the
USER_OBJECTS view,
SELECT * FROM USER_OBJECTS;
SQL> DESCRIBE wordcount
FUNCTION wordcount RETURNS BINARY_INTEGER
--Managing Grants and Synonyms for Stored Programs
GRANT EXECUTE ON wordcount TO scott;
REVOKE EXECUTE ON wordcount FROM scott;
--To view a list of privileges you have granted to other users and roles, you can query the USER_TAB_PRIVS_MADE:
SQL> SELECT table_name, grantee, privilege
FROM USER_TAB_PRIVS_MADE
WHERE table_name = 'WORDCOUNT';
--Hiding the Source Code of a Stored Program --p44
--Language Fundamentals
--struture
图片.png
Anonymous Blocks
Named Blocks
PROCEDURE [schema.]name [ ( parameter [, parameter ... ] ) ]
[AUTHID {DEFINER | CURRENT_USER}]
A function header has similar syntax, but includes the RETURN keyword:
FUNCTION [schema.]name [ ( parameter [, parameter ... ] ) ]
RETURN return_datatype
[AUTHID {DEFINER | CURRENT_USER}]
[DETERMINISTIC]
[PARALLEL ENABLE ...]
[PIPELINED [USING...] | AGGREGATE USING...]
Nested Blocks
图片.png
You can nest anonymous blocks within anonymous blocks to more than one level
--Initially the code is using the emp_name_ix index, but when I set NLS_COMP=LINGUISTIC and NLS_SORT=BINARY_CI to enable case insensitivity I stop using the
index and start doing full table scans instead—oops! One solution is to create a functionbased,case-insensitive index, like this:
CREATE INDEX last_name_ci ON EMPLOYEES (NLSSORT(last_name, 'NLS_SORT=BINARY_CI'))
BEGIN
DBMS_OUTPUT.PUT_LINE('Session Timezone='||SESSIONTIMEZONE);
DBMS_OUTPUT.PUT_LINE('Session Timestamp='||CURRENT_TIMESTAMP);
DBMS_OUTPUT.PUT_LINE('DB Server Timestamp='||SYSTIMESTAMP);
DBMS_OUTPUT.PUT_LINE('DB Timezone='||DBTIMEZONE);
EXECUTE IMMEDIATE 'ALTER SESSION SET TIME_ZONE=DBTIMEZONE';
DBMS_OUTPUT.PUT_LINE('DB Timestamp='||CURRENT_TIMESTAMP);
-- Revert session time zone to local setting
EXECUTE IMMEDIATE 'ALTER SESSION SET TIME_ZONE=LOCAL';
END;
Scope --Page 58
when you run a procedure such as this (order_id is the primary key of the
orders table):
PROCEDURE remove_order (order_id IN NUMBER)
IS
BEGIN
DELETE orders WHERE order_id = order_id; -- Oops!
END;
This code will delete everything in the orders table regardless of the order_id that you
pass in. The reason: SQL’s name resolution matches first on column names rather than
on PL/SQL identifiers. The WHERE clause “order_id = order_id” is always true, so poof
goes your data. One way to fix it would be:
PROCEDURE remove_order (order_id IN NUMBER)
IS
BEGIN
DELETE orders WHERE order_id = remove_order.order_id;
END;
The PL/SQL Character Set
you would write two single quotes next to each other if you wanted the string to contain a single quote in that position. The following table offers some examples.
'There''s no business like show business.' There's no business like show business.
'"Hound of the Baskervilles"' "Hound of the Baskervilles"
'''' '
'''hello''' 'hello'
'''''' ''
In an attempt to simplify this type of construct,Oracle Database 10g introduced user-defined delimiters. Start the literal with “q” to mark your delimiter, and surround your delimited expression with single quotes. The following table shows this feature in action.
q' ( There's no business like show business.) ' There's no business like show busi
ness.
q' { "Hound of the Baskervilles" } ' "Hound of the Baskervilles"
q' [ ' ] ' '
q' !'hello' ! ' 'hello'
q' |'' | ' ''
Exception
DECLARE
no_such_sequence EXCEPTION;
PRAGMA EXCEPTION_INIT (no_such_sequence, −2289);
BEGIN
...
EXCEPTION
WHEN no_such_sequence
THEN
q$error_manager.raise_error ('Sequence not defined');
END;
