第一个程序
代码如下 具体的思想已经在注释里了
DECLARE
l_book_count INTEGER
GEGIN
-- set the value of l_book_count.
SELECT COUNT ( * )
INTO l_book_count
FROM books
WHERE author LIKE ' %FEUERSTEIN,STEEVEN ' ;
-- print the count of the books that author has written.
DBMS_OUTPUT.PUT_LINE (
' steven has written (or co-written) ' ||
l_book_count ||
' books ' );
-- oh,and I changed my name, so...
UPDATE books
SET author = replace (author, ' STEVEN ' , ' STEPHEN ' )
WHERE author LIKE ' %FEUERSTEIN,STEVEN ' ;
END ;
l_book_count INTEGER
GEGIN
-- set the value of l_book_count.
SELECT COUNT ( * )
INTO l_book_count
FROM books
WHERE author LIKE ' %FEUERSTEIN,STEEVEN ' ;
-- print the count of the books that author has written.
DBMS_OUTPUT.PUT_LINE (
' steven has written (or co-written) ' ||
l_book_count ||
' books ' );
-- oh,and I changed my name, so...
UPDATE books
SET author = replace (author, ' STEVEN ' , ' STEPHEN ' )
WHERE author LIKE ' %FEUERSTEIN,STEVEN ' ;
END ;
条件语句
一 if and case 语句
二 循环语句
for .....loop and while .......loop 和简单循坏
三 goto语句
pl支持goto语句,允许你无条件的调到需要该执行的地方 下面看看一个存储过程
CREATE
OR
replace
PROCEDURE
pay_out_balance (
-- parameter list of the procedure.
account_id_in in accounts.id % TYPE)
is
-- declare varial here.
l_balance_remaining Number ;
GEGIN
-- loop util the value of l_balance_remaining less than 1000
-- then exit the loop
LOOP
l_balance_remaining : = account_balance (account_id_in);
if l_balance_remaining < 1000
THEN
EXIT ;
else
apply_balance(account_id_in,l_balance_remaining);
END if ;
END if ;
END LOOP;
END pay_out_balance;
-- parameter list of the procedure.
account_id_in in accounts.id % TYPE)
is
-- declare varial here.
l_balance_remaining Number ;
GEGIN
-- loop util the value of l_balance_remaining less than 1000
-- then exit the loop
LOOP
l_balance_remaining : = account_balance (account_id_in);
if l_balance_remaining < 1000
THEN
EXIT ;
else
apply_balance(account_id_in,l_balance_remaining);
END if ;
END if ;
END LOOP;
END pay_out_balance;
下面的存储过程通过id获取用户的名字和余额,如果余额太低,就显式的抛出一个异常,阻止程序的进一步执行。
CREATE
OR
REPLACE
PROCEDURE
check_account (
account_id_in IN accounts.id % type )
IS
l_balance_remaining NUMBER ; -- remaining money
l_balance_below_minimum EXCEPTION; -- the remaining is too below the type is Exception
l_account_name accounts.name % TYPE ; -- account name
-- get account name by id
BEGIN
SELECT name
INTO l_account_name
FROM accounts
WHERE id = account_id_in;
-- calculate the remaining of money
l_balance_remaining : = account_balance (account_id_in);
-- tell account the remaining money of his
DBMS_OUTPUT.put_line(
' Balance for ' || l_account_name ||
' = ' || l_balance_remaining );
-- if the remaining below 1000 ,then raise exception here.
IF l_balance_remaining < 1000
THEN
RAISE l_balance_below_minimum;
END IF ;
Exception
WHEN NO_DATA_FOUND
THEN
-- No account found for this ID
log_error("No account exsits");
WHEN l_balance_below_minimum
THEN
log_error("Your remaining of money is too low , please add your money ....");
RAISE;
END ;
account_id_in IN accounts.id % type )
IS
l_balance_remaining NUMBER ; -- remaining money
l_balance_below_minimum EXCEPTION; -- the remaining is too below the type is Exception
l_account_name accounts.name % TYPE ; -- account name
-- get account name by id
BEGIN
SELECT name
INTO l_account_name
FROM accounts
WHERE id = account_id_in;
-- calculate the remaining of money
l_balance_remaining : = account_balance (account_id_in);
-- tell account the remaining money of his
DBMS_OUTPUT.put_line(
' Balance for ' || l_account_name ||
' = ' || l_balance_remaining );
-- if the remaining below 1000 ,then raise exception here.
IF l_balance_remaining < 1000
THEN
RAISE l_balance_below_minimum;
END IF ;
Exception
WHEN NO_DATA_FOUND
THEN
-- No account found for this ID
log_error("No account exsits");
WHEN l_balance_below_minimum
THEN
log_error("Your remaining of money is too low , please add your money ....");
RAISE;
END ;